10 — Workstream I: Apply Packet & Owner-Approval Checklist

This packet is NOT executed by this macro. It is the precise, ordered, reversible plan for a future owner-approved apply macro. Every step lists its rollback.

Backup commands (run first)

ssh contabo 'docker exec postgres pg_dump -U workflow_admin -d directus \
  -t public.birth_registry -t public.dot_tools --schema-only' > birth_dot_schema_pre.sql
ssh contabo "docker exec postgres psql -U workflow_admin -d directus -At -c \
  \"select proname, md5(pg_get_functiondef(oid)) from pg_proc \
    where proname in ('fn_birth_registry_auto','fn_birth_registry_auto_id','fn_birth_gate')\"" \
  > fn_hash_pre.txt
# full DB backup per standard runbook before any constraint change on a 1.12M-row table

Apply order (strict)

#	Step	DDL/DML scope	Rollback
1	Create permit table birth_admission_permit + 2 unique indexes	additive table, 0 existing rows touched	DROP TABLE birth_admission_permit;
2	Build composite index CONCURRENTLY on birth_registry(entity_code, collection_name)	new index, no row change; no long lock	DROP INDEX CONCURRENTLY birth_registry_code_collection_uq;
3	Promote to constraint ADD CONSTRAINT … UNIQUE USING INDEX	brief lock	ALTER TABLE … DROP CONSTRAINT …
4	Patch fn_birth_registry_auto conflict target → (entity_code, collection_name)	OID-stable body swap (166 triggers unaffected)	CREATE OR REPLACE back to entry body (md5 1f729b35…)
5	Patch fn_birth_registry_auto_id conflict target + composite EXISTS guard	OID-stable body swap	restore entry body (md5 3f3515de…)
6	Smoke test in a txn (BEGIN…ROLLBACK): one managed insert births correctly under composite	none committed	n/a
7	Drop single-col unique birth_registry_entity_code_unique	brief lock; ⚠ one-way once 2nd-collection codes are born	ADD CONSTRAINT … UNIQUE (entity_code) — fails if any cross-collection code already born
8	Birth the 5 pivots (PIV-101/103/104/105/106) as a separate reversible DML step	5 INSERT-via-trigger rows	DELETE FROM birth_registry WHERE entity_code IN (…) AND collection_name='pivot_definitions'
9	Create permit gate fn_dot_tools_permit_gate + trigger on dot_tools (policy-flag form)	additive trigger	DROP TRIGGER … ; DROP FUNCTION …
10	Create deferred finalize constraint trigger on dot_tools	additive constraint trigger	DROP TRIGGER … ; DROP FUNCTION …
11	Set family flag birth_admission_required=true for dot_tools only	1 config row	unset flag
12	Capture drift baselines (doc 09)	additive baseline rows	drop baseline rows

Steps 1–6 fully reversible, no one-way doors. Step 7 is the first irreversible-once-used door (after step 8 births a second-collection code). Stop and get explicit owner sign-off between step 6 and step 7.

Expected object list after apply

• New table: birth_admission_permit (+ uq_permit_idem, uq_permit_active)
• New constraint: birth_registry_code_collection_uq; dropped: birth_registry_entity_code_unique
• Patched (OID-stable): fn_birth_registry_auto, fn_birth_registry_auto_id
• New: fn_dot_tools_permit_gate + trigger; fn_dt_finalize + constraint trigger (dot_tools only)
• 5 new birth_registry rows (pivots); 1 registry/config flag
• Drift baselines + 3 read-only permit views (doc 07)

Affected functions / triggers

• 166 triggers calling fn_birth_registry_auto — unchanged binding (OID stable).
• dot_tools trigger set gains 2 (gate + finalize); existing 13 unchanged.

Test plan (post-apply)

1. Composite constraint present; single-col absent.
2. New-code dot_tools insert (with permit) → born once.
3. Cross-collection code → now born in own collection.
4. No-permit / expired-permit dot_tools insert → blocked.
5. Valid permit → row + permit FINALIZED + birth linked.
6. 166 trigger bindings intact.
7. Other families unaffected (no permit required where flag unset).
8. RP guard still returns BLOCK dims (or fewer after pivot birth: orphan 59→54).

Owner-approval checklist (decision points)

• Approve permit table birth_admission_permit (additive, reversible)?
• Approve composite unique (entity_code, collection_name) + drop of UNIQUE(entity_code) (acknowledge step-7 one-way door)?
• Approve the two function patches (OID-stable)?
• Approve birthing the 5 pivots as a separate reversible step?
• Approve pilot dot_tools permit-blocking (gate + finalize, family-flagged, no global flip)?
• Approve registry-driven code rule to replace hardcoded ^[A-Z]+-[0-9]+$ (else dot_tools 47% rejection persists)?
• Approve governance handoff activation (requires OSPA ≥ 1 seed)? — separable; birth does not need it.
• Approve drift monitor + break-glass ledger wiring into the fail-closed guard?

Credential / authority requirements

• dot-dot-register registrar credentials — ABSENT. Required to register the dot-birth-admit DOT.
• Owner DDL approval — required for steps 1–11.
• Human OSPA ≥ 1 — required only for governance activation (doc 08), not for birth.
• External scheduler — pg_cron absent; permit-expiry sweep + drift comparator + handoff consumer need an external scheduler (cron/systemd timer calling psql).