Story 1878: one document, many marks — the exhibit marking junction

Why this exists (the catastrophe it prevents). An exhibit number is not a property of a document — it is an event that happens to a document inside a proceeding. The same PDF can be tendered by the claimant as C-9 and by the respondent as R-4 in the very same hearing; the next session re-tenders the same C-9 — it is the same mark, not a new one. If you store the exhibit number as a scalar column on documents, all of that collapses: one document gets one number, the second side’s mark overwrites the first, and a re-tender looks like a duplicate. Worse, three things that must never happen become one careless UPDATE away: an AI agent stamps a document “admitted” (a tribunal act only a human may perform); a privileged work-product document gets exhibit-marked and is thereby waived into the record; or firm A’s marks leak into firm B’s view. This Story builds the junction table that makes a marking a first-class row, and — because this is an evidentiary table — pushes every one of those “never” rules down into the database itself, where no service bug can route around them.

The analogy to hold the whole way down: a court exhibit register. The register is a ledger with one line per mark: this document, marked as this number, by this party series, in this proceeding, on this date, by this person. A single document can appear on several lines (each side marks it). The clerk writes the line; only the judge can write “admitted” in the status column; and a sealed/privileged document is never allowed onto the register at all. The junction table is that ledger, and the DB constraints are the rules the clerk physically cannot break.

🛰 Satellite view: a marking is a row, not a column

Migration 0141_document_exhibit_marking (revises the current head e9c4a7b1d602, the 0140 annexure columns from Story-1877) creates net-new documents.document_exhibit_marking — one row per marking event. Story-1877 freed the mis-named exhibit_label column on documents; this Story is where the exhibit identity actually moves to. The annexure label stays a scalar on the document (it is 1:1 with the page); the exhibit mark becomes a child row because it is many-per-document.

Column group	What it holds	Why
`firm_id`, `matter_id`, `document_id`, `proceeding_id`	The four anchors of a mark. `matter_id` is carried explicitly (markings are matter-scoped, A1/DR-3).	A mark is meaningless without “whose, which case, which document, which proceeding”.
`hearing_session_id` (nullable, unused)	A deliberately empty v1 seam.	DR-8: the real `hearing_session` entity is deferred to v2; markings scope by (matter, proceeding) today. Building the column now avoids a later table rewrite.
`exhibit_label` + `exhibit_label_normalized`	The editable display string (“C-1”, “Ex.P-1”, “CB/1”) and its STORED generated fold.	DR-6: counsel edits the display; the database derives the case/whitespace/unicode-folded key that uniqueness runs on.
`sort_key`	Stable display order.	DR-7: order is independent of the label, so renumbering the display never reshuffles the register.
`exhibit_party_role` + `exhibit_party_role_canonical`	Free display label (Claimant/Plaintiff/Prosecution) + controlled axis (`initiating_party`…).	D4: the display is counsel’s words; the canonical token is the stable party-series the uniqueness key groups on. No DB CHECK on either — the vocabulary is app-owned and may grow (e.g. a joint_party token for jointly-numbered CB/1 bundles).
`lifecycle_status`, `source`, `agent_run_id`, `recorded_by_user_id`	marked/admitted/rejected; human/agent; Blake provenance; the counsel who recorded it.	DR-1: the authority axis. These four columns are what let the DB enforce “only a human admits”.

✈️ Airplane view: the four “never” rules, enforced at the database

A service-layer convention (“remember not to mark privileged docs”) is one forgotten if away from a privilege waiver. For an evidentiary table the panel (PANEL-SYNTHESIS-DOC-003) ruled every existential rule must live in the schema. Each maps to a concrete DB object:

Rule	Mechanism	What it physically prevents
DR-1 only a human admits/rejects	CHECK `lifecycle_status = 'marked' OR source = 'human'`	An agent run can record a `marked` mark but the DB rejects any agent row whose status is `admitted`/`rejected` — a tribunal act it may never assert.
DR-2 privileged docs are unmarkable	BEFORE INSERT/UPDATE trigger joining `documents.privilege_flag`	If the referenced document is privileged, the insert RAISES. There is no marking path that waives work-product, even via a buggy service.
DR-3 matter / firm isolation	Firm RLS `firm_isolation_policy` + firm-leading composite FKs + the same trigger pinning the doc’s (firm, matter)	A mark can only reference a matter/proceeding in its own firm, and its firm/matter must match the document’s — cross-firm and cross-matter spoofing are both blocked.
DR-5/DR-6 one mark per number per series	UNIQUE (firm, matter, proceeding, role_canonical, label_normalized)	A re-tender of “C-1” (or “c-1”, or “ C-1 ”) in the same series is the SAME row — a duplicate is rejected, not silently doubled.

🔬 Microscopic view: the decisions that matter

1. The normalized key is a STORED generated column, not app code

DR-6 wants case + whitespace + unicode-NFKC folding so “C-1”, “c-1” and “ C-1 ” collapse to one identity. The fold could live in the service — but then a second writer (the agent rail, a future importer) that forgets to fold defeats the uniqueness guarantee. Instead Postgres derives it, unbypassably, from the display label:

  exhibit_label_normalized text
    GENERATED ALWAYS AS (lower(normalize(btrim(exhibit_label), NFKC))) STORED

All three functions are IMMUTABLE, so a STORED generated column is legal, and the unique constraint runs on the derived value. The entity mirrors this with sa.Computed(...) so alembic check stays green. This mirrors the dedup intent of the document_tags lower(tag) unique index, made stronger by being a column the key can name directly.

2. One trigger does privilege backstop and tenant-consistency

The DR-2 trigger already has to SELECT the document to read privilege_flag. While it is there it reads firm_id and matter_id too, and pins them to the new row — closing the DR-3 cross-matter hole in the same round-trip. And because the SELECT runs under the caller’s RLS context, a cross-firm document_id is simply invisible → NOT FOUND → rejected:

  SELECT privilege_flag, firm_id, matter_id INTO ...
    FROM documents.documents WHERE id = NEW.document_id;
  IF NOT FOUND THEN  RAISE ... ;            -- unknown / cross-firm doc
  IF doc_privilege IS TRUE THEN  RAISE ... ; -- DR-2 waiver guard
  IF doc_firm_id <> NEW.firm_id OR doc_matter_id <> NEW.matter_id THEN RAISE ...; -- DR-3

The RAISEs use ERRCODE = '23514' with a named CONSTRAINT, so they surface to SQLAlchemy as an IntegrityError the service can map — the same shape a CHECK violation has.

3. No DB CHECK on the party-role columns — on purpose

lifecycle_status and source are closed sets, so they carry CHECKs. The party role is different: the display label is free counsel text, and even the canonical axis is expected to grow (the panel flagged jointly-numbered CB/1 bundles needing a joint_party token, A8). A DB CHECK there would force a migration for every new token, so the role vocabulary is app-validated (D4) and the DTO enum (ExhibitPartyRoleCanonical) is the contract surface. The uniqueness key still uses the canonical column — it just is not constrained to a frozen list.

4. Firm-leading composite FKs, the repo’s tenant-integrity pattern

(firm_id, matter_id) → business.matters(firm_id, id) and (firm_id, proceeding_id) → business.proceedings(firm_id, id) are composite on purpose: they make it impossible to reference a matter or proceeding belonging to another firm, matching the existing matter-children convention in the codebase. document_id is a single FK (its (firm, matter) consistency is the trigger’s job); all FKs are ondelete RESTRICT — an evidentiary mark must not vanish because a parent row was deleted.

How it was proven

Migration round-trip on the isolated Docker test DB (make test-db, Postgres 16 on :55432): alembic upgrade head → downgrade base → upgrade head all clean; single head b7e2a9c4f013 on top of e9c4a7b1d602.
No drift: alembic check reports “No new upgrade operations detected” — the SQLModel entity (composite FKs, CHECKs, the Computed column, the unique constraint, indexes, table comment) matches the migration exactly.
System-boundary RLS/trigger test (tests/integration/test_document_exhibit_marking_rls.py, run as pramaan_app against the real DB, seeding from the empty first-run state): proves (a) a marking on a privileged document is rejected; (b) admitted with source='agent' is rejected; (c) a case/whitespace duplicate (“ c-1 ” vs “C-1”) in the same (matter, proceeding, role) is rejected; (d) a second firm reads zero of the first firm’s marks; plus DL-04: the same document carries C-1 (claimant) and R-4 (respondent) independently, and the generated exhibit_label_normalized folds “C-1” → “c-1”.
Static CI guard (tests/unit/test_document_exhibit_marking_migration.py): pins every existential gate into the migration source and asserts the entity’s normalize expression is byte-identical to the migration’s — so the gates cannot silently regress even where CI lacks a Postgres URL.
Verify: ruff clean on changed files; make openapi produced no diff (this Story adds the table + a read DTO but wires no route yet, so the OpenAPI mirror is unchanged); document-domain unit suite — 290 passed.

One-sentence recall: an exhibit mark is now its own row — one per (matter, proceeding, party-series, normalized label) — so both sides can mark the same document and re-tenders reuse the same line, while the database itself (CHECK, trigger, RLS, composite FKs, unique key) makes it physically impossible for an agent to admit evidence, for a privileged document to be marked, or for one firm to see another’s register.