06 — Workstream E: DEFERRABLE Finalize-at-Commit Rehearsal

Result: PROVEN. The "permit finalized only if the row exists and the transaction succeeds" invariant was rehearsed using the live trg_iu_birth_gate_layer2 constraint-trigger pattern as the reference: a CREATE CONSTRAINT TRIGGER … DEFERRABLE INITIALLY DEFERRED that re-checks state at the commit boundary.

Why deferred finalize, and how it was tested without committing

The finalize must fire at the commit boundary so it observes the final state of the transaction. Testing true COMMIT would mutate production (forbidden). The deferred trigger's firing was instead forced mid-transaction with SET CONSTRAINTS ALL IMMEDIATE, which runs all pending deferred constraint triggers now, exactly as at commit — then the whole transaction was ROLLBACKed. This proves commit-boundary semantics with no commit.

Finalize design (temp model on dt_copy)

CREATE FUNCTION fn_dt_finalize() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  IF NOT EXISTS (SELECT 1 FROM dot_tools WHERE code=NEW.code) THEN
    RAISE EXCEPTION 'FINALIZE: row for % vanished before commit boundary', NEW.code; END IF;
  UPDATE birth_admission_permit
     SET status='FINALIZED', finalized_birth_id=NEW.id
   WHERE collection_name='dot_tools' AND entity_code=NEW.code AND status='CONSUMED';
  IF NOT FOUND THEN RAISE EXCEPTION 'FINALIZE: no CONSUMED permit to finalize for %', NEW.code; END IF;
  RETURN NULL;
END $$;
CREATE CONSTRAINT TRIGGER trg_dt_finalize AFTER INSERT ON dot_tools
  DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE FUNCTION fn_dt_finalize();

Test battery & live results

#	Scenario	Expected	Result
E.1	Valid permit → insert row → SET CONSTRAINTS ALL IMMEDIATE	permit CONSUMED→FINALIZED, finalized_birth_id linked	PASS — status FINALIZED, finalized=t
E.2	Insert row, then DELETE it, then force constraints	finalize must FAIL	PASS — row for DOT-VANISH vanished before commit boundary

End-to-end chain across D+E: REQUESTED → (gate) CONSUMED → (deferred boundary) FINALIZED with birth id recorded. A failed insert (no row at boundary) never reaches FINALIZED → the permit stays CONSUMED/visible for the scanner.

Where finalization writes

Recommended = mark the existing birth row finalized, not create a new one. The AFTER-INSERT birth trigger (fn_birth_registry_auto) already creates the birth_registry BORN row in the same transaction. The deferred finalize should confirm that birth (e.g. set certified=true / inspect_gate=now() / a finalized_at) and stamp finalized_birth_id on the permit — not insert a second birth row. Matches the layer2 pattern (re-read + validate, never re-create).

Failure modes covered

• Insert fails → txn aborts → finalize never runs → permit not finalized. ✔
• Row inserted then removed in same txn → finalize raises at boundary → txn aborts. ✔ (E.2)
• Expired permit → gate already blocked the insert (doc 05 D.2). ✔
• Double finalize → second pass finds no status='CONSUMED' → NOT FOUND raise. ✔ (idempotent-by-status)

Production-apply note on true COMMIT

The rehearsal proved the logic via SET CONSTRAINTS IMMEDIATE + rollback. The only thing a true commit additionally exercises is ordering relative to other deferred constraints — covered by Postgres semantics and the live precedent (trg_iu_birth_gate_layer2 runs deferred in prod today). No isolated-schema commit was needed.

Decision

FINALIZE_TRIGGER = PROVEN. Reuses an in-production pattern, enforces row-exists + single-consume + birth-link at the commit boundary, fully reversible. Gated by owner DDL approval. For the pilot, attach dot_tools-only (or policy-flag driven), idle for all other families.