03 — Workstream B: Composite-Unique birth_registry Migration Rehearsal

Result: COMPOSITE_READY. No transition period required. The constraint is already satisfied by 100% of live data; only two function call sites depend on the old constraint, and both are patched in Workstream C.

The defect (confirmed live)

birth_registry has UNIQUE (entity_code) alone. Five pivots — PIV-101 / 103 / 104 / 105 / 106 — already hold their codes in collection pivot_results, so they cannot be born in pivot_definitions: the ON CONFLICT (entity_code) backstop in fn_birth_registry_auto silently swallows the insert. These are exactly the 5 unborn pivot_definitions rows (the residual of the 80→59 orphan reduction).

Feasibility (live probes, doc 01 §3)

Probe	Value	Implication
codes in >1 collection today	0	composite migration introduces no conflict
(entity_code,collection_name) dup rows	0 / 1,126,728	composite unique builds cleanly over all live rows
null collection_name	0	no NULL semantics to resolve
FK refs to birth_registry	0	dropping UNIQUE(entity_code) breaks no foreign key
fns referencing ON CONFLICT (entity_code)	2	fn_birth_registry_auto, fn_birth_registry_auto_id

Rehearsed semantics (temp model br_demo)

#	Step	Result
B.1	Under UNIQUE(entity_code): insert (PIV-101, pivot_results) then (PIV-101, pivot_definitions)	2nd blocked — reproduces the live defect (PASS)
B.2	Drop single-col unique, add UNIQUE(entity_code, collection_name), retry	both collections now hold PIV-101 (PASS)
B.3	Insert a genuine duplicate (PIV-101, pivot_definitions) again	still blocked — composite preserves real integrity (PASS)

Decision: COMPOSITE_READY

The existing UNIQUE(entity_code) can be dropped (no FK depends on it; 0 cross-collection codes; the only logic that referenced it is the two functions, both patched). It must be replaced by UNIQUE(entity_code, collection_name).

Exact migration order (for apply, NOT executed)

To avoid a long ACCESS EXCLUSIVE lock on the 1.12M-row hot table, build the index concurrently first:

-- 1. Build composite unique index without a long lock (outside a txn block):
CREATE UNIQUE INDEX CONCURRENTLY birth_registry_code_collection_uq
  ON public.birth_registry (entity_code, collection_name);
-- 2. Promote it to a constraint (brief lock):
ALTER TABLE public.birth_registry
  ADD CONSTRAINT birth_registry_code_collection_uq
  UNIQUE USING INDEX birth_registry_code_collection_uq;
-- 3. Patch the two functions' conflict target (doc 04) — MUST precede step 4.
-- 4. Drop the obsolete single-column unique:
ALTER TABLE public.birth_registry
  DROP CONSTRAINT birth_registry_entity_code_unique;

Exact rollback order

ALTER TABLE public.birth_registry
  ADD CONSTRAINT birth_registry_entity_code_unique UNIQUE (entity_code);  -- FAILS if a 2nd-collection code was already born
-- restore both functions to single-col ON CONFLICT (doc 04 rollback)
ALTER TABLE public.birth_registry DROP CONSTRAINT birth_registry_code_collection_uq;

⚠ Rollback hazard (must be in the apply packet)

Once the 5 pivots are born under the composite unique, re-adding UNIQUE(entity_code) will fail (PIV-101 now exists in two collections). Therefore the composite migration is a one-way door the moment any second-collection code is born. Recommended apply sequence: apply composite + function patch + drop single-col first and verify, then birth the 5 pivots as a separate, individually reversible step. Do not bundle them.

Affected functions/views

• fn_birth_registry_auto — conflict target (doc 04).
• fn_birth_registry_auto_id — conflict target + single-column EXISTS guard should become composite-aware (doc 04).
• No views reference the constraint name; idx_birth_collection already indexes collection_name.