05 — Birth Gate Hardening Plan (P3)

File: sql/03_birth_gate_hardening.sql (sha256 da5581e2…d14b63). Author-mode, STAGED.

Current gate behaviour (verified-live, verbatim original kept below for rollback)

fn_birth_gate() (BEFORE-INSERT trigger): reads kill-switch GUC app.bypass_birth_gate (='true'/'1' → RETURN NEW); extracts code/name/_dot_origin; skips when code NULL/empty; runs fn_pre_birth_check (5 checks: collection∈meta_catalog managed; _dot_origin non-empty; code ^[A-Z]+-[0-9]+$; name not dup; code not dup); mode = GUC app.birth_gate_mode else warning → only RAISE WARNING. blocking → RAISE EXCEPTION.

Trigger placement: gate exists only on collection_registry + dot_tools. Missing on pivot_definitions, entity_species, dot_iu_command_catalog.

Ownership / bypass surface (G7): dot_tools, pivot_definitions, dot_iu_command_catalog, birth_registry, collection_registry, entity_species all owned by app-role directus → the app role can DISABLE TRIGGER, SET app.bypass_birth_gate, or DML directly. governance_object_ownership owned by workflow_admin.

Staged hardening (in the file)

• Stage 0 — birth_gate_policy table: per-collection mode ∈ off|warning|blocking + bypass_allowed. Replaces the single global GUC with collection-granular control. Seeded all critical families at warning (no behaviour change vs today), collection_registry with bypass_allowed=false.
• Stage 1 — hardened fn_birth_gate (CREATE OR REPLACE, backward compatible): resolve mode from policy → GUC → warning; the kill-switch GUC is honoured only when bypass_allowed=true for that collection (closes G7 for no-bypass criticals); null-code still legacy-safe except blocking+no-bypass.
• Stage 1b — attach gate to pivot_definitions + entity_species (BEFORE INSERT, warning) so every future row is checked. dot_iu_command_catalog deferred until it's onboarded (else every row warns on check-1).
• Stage 2 — flip to blocking (commented, DO-NOT-RUN): only after P4 backlog worked; pre-req v_birth_orphan(critical,active)=0 and v_birth_phantom(REAL_MISSING)=0.

Why not enable global blocking now

82/147 managed collections have zero birth and 80 critical rows are unborn; global blocking would break legacy inserts. The macro forbids it and so does prudence. Hardening is report-only/observe (Stage 1) until backlog is classified.

Rollback

Drop the two new triggers + birth_gate_policy; restore the original fn_birth_gate body (verbatim above) via CREATE OR REPLACE. Full teardown in 99_rollback_all.sql.

Original fn_birth_gate body (for restore)

Kept verbatim in sql/03_birth_gate_hardening.sql header comment and in audit doc 02. Behaviour = advisory warning, global GUC bypass, null-code skip.