pramaan-functions · Story-3056 · EPIC-3034
The matter menu leads with your working set, not the firm’s oldest matters
M-001 — Test matter codex-screenshot
and M-002 — Pramaan Demo Matter 02 — test junk at
the top, the counsel’s actual matters buried. Founder verdict:
“M-01 and M-02 don’t make any sense.”
The two defects
Both lived in the SECURITY DEFINER reader
business.read_channel_binding_contexts (migration 0213):
- Oldest-first.
ORDER BY m.matter_numberascending means the firm’s first-ever matters lead every menu, forever. - Firm-wide scope for admins.
can_user_read_matterreturns true for anyfirm_admin/firm_editoracross every firm matter — correct for access, wrong for a 10-row phone menu that led with the two test matters.
The fix — a fallback ladder, no new table
The signal already exists: agents.chat_session carries
firm_id · user_id · matter_id ·
last_active_at, and Mira’s WhatsApp turns land in the same
table as app chat. “Matters this counsel has talked to Mira
about” is just a query. The reader now orders each binding’s
accessible matters by a three-rung ladder, each rung topping up the next:
| # | If… | Show |
|---|---|---|
| 1 | the counsel has chat sessions with matters | those matters, newest last_active_at first |
| 2 | no chat history | matters they are assigned to (business.matter_assignments) |
| 3 | admin/editor assigned to none | the firm’s matters, newest first — never oldest |
The whole ladder is one ORDER BY, index-friendly against the
existing ix_chat_session_list:
ORDER BY
b.binding_id,
(SELECT max(cs.last_active_at) FROM agents.chat_session cs
WHERE cs.firm_id = b.firm_id AND cs.user_id = b.user_id
AND cs.matter_id = m.id AND cs.deleted_at IS NULL) DESC NULLS LAST,
CASE WHEN EXISTS (SELECT 1 FROM business.matter_assignments ma
WHERE ma.firm_id = b.firm_id AND ma.matter_id = m.id
AND ma.user_id = b.user_id) THEN m.matter_number END ASC NULLS LAST,
m.matter_number DESC NULLS LAST,
m.id
The single CASE term does the rung 2/3 split: an assigned
matter sorts on its matter_number ascending (the counsel’s
own case-list order), an unassigned matter sorts NULL — so every
assigned matter precedes every unassigned one, and the unassigned tail falls
to matter_number DESC (the firm’s matters newest-first).
m.id is the final, unique tiebreak, so the order is TOTAL: no two
rows compare equal, and the menu recomputes identically next turn —
a numbered reply still means the same matter (the stateless-menu contract, the
CC-04 acceptance test). Chat matters with a deleted_at stamp and
sessions with a NULL matter_id are excluded.
Access is unchanged — this changes what is DISPLAYED, never what is permitted
The can_user_read_matter LEFT JOIN gate is byte-identical. A
non-admin still sees only assigned matters; chatting about a matter they
cannot read does not surface it (proved by
test_access_unchanged_chatted_but_unreadable_matter_is_never_offered).
Recency reorders the accessible set; it never widens it.
The RLS trap this Story had to clear
agents.chat_session is FORCE ROW LEVEL SECURITY
keyed firm_id = app.current_firm_id_or_null() (migration 0044).
The channel reader runs cross-firm with the firm GUC empty. On RDS the
SECURITY DEFINER owner is not a superuser, so it does not bypass
RLS — the chat join would silently return zero rows and rung 1
would never fire, green in CI yet dead on staging.
business.matters is only ENABLE (not FORCE) RLS, so the same
owner already bypasses it — which is why the old reader worked at all.
Migration 0224 closes the gap with one narrowly-scoped policy
on agents.chat_session — a SELECT policy that admits the
read only when the firm GUC is empty (the channel-reader context, never a
normal app session) and the row is the principal’s own
session. It extends the reader’s rights explicitly; it does not widen
the function broadly.
Because the test-DB migration role is a superuser, a test that
calls the reader would be green regardless of the policy. So the proof
reads agents.chat_session directly as
pramaan_app (a NOBYPASSRLS role — the faithful
stand-in for the RDS owner). Dropping the policy turns that test red:
assert await _visible(firm_guc="", user_guc=str(user_id)) == 1 # policy admits assert await _visible(firm_guc="", user_guc="") == 0 # no principal → hidden assert await _visible(firm_guc=str(uuid4()), user_guc="") == 0 # firm isolation holds
Also: the cap is 10, not 9
A WhatsApp interactive list allows 10 rows, so MENU_CAP = 9
wasted a slot. It is now 10; the “…and N more — reply
with the matter code” overflow line still appears beyond that.
Verification
tests/integration/test_channel_matter_recency.py— all three rungs, the full-ladder top-up, the access-unchanged proof, the soft-delete exclusion, and the RDS-faithful RLS policy proof.- Migration applies, round-trips (
downgrade/upgrade), andalembic checkreports no drift. - Planted-negative confirmed: dropping
channel_reader_self_select_policyturns the policy proof red (no green counted until it was red).