P3D — B3-F1a Soft Gate Compiled Trigger (rev3)

Mode: COMPILE-ONLY. NOT executed. Rev: 3 (rev3 fix #2 applied: COMMENT ON removed from executable SQL). Date: 2026-05-13. Live re-probe: PG 16, db directus, schema public.

Conflict status (live, 2026-05-13)

trg_collection_onboarding_soft_gate on public.collection_registry is absent (pg_trigger returned 0 rows for this name). At B3-F1b time, if a trigger of this name already exists → BLOCKED_EXISTING_CONFLICT (no silent replace).

Existing triggers on collection_registry (live)

For reviewer awareness only — none of these names collide with the gate trigger name:

tgname	function	tgtype	tgenabled
birth_trigger_collection_registry	fn_birth_registry_auto	5 (AFTER INSERT ROW)	O
trg_birth_collection_registry	fn_birth_registry_auto	5 (AFTER INSERT ROW)	O
trg_auto_code_collection_registry	gen_code_collection_registry	7 (BEFORE INSERT ROW)	O
trg_before_birth_gate_collection_registry	fn_birth_gate	7 (BEFORE INSERT ROW)	O
trg_count_collection_registry	refresh_registry_count	60	D
trg_desc_guard_collection_registry	fn_description_birth_guard	23	O
trg_desc_provenance_collection_registry	fn_auto_label_provenance	5	O
trg_label_assign_collection_registry	fn_auto_label_assignment	21	O
trg_refresh_collection_count	fn_refresh_collection_count	12	O
trg_refresh_orphan_col	trg_fn_refresh_orphan_col	12	O
trg_validate_dot_origin_collection_registry	fn_validate_dot_origin	23	O

Column selectivity

The trigger fires only when policy-relevant columns change, to avoid spurious fires on unrelated edits (field_count, description, etc.):

column	reason
governance_role	derives the managed policy universe
coverage_status	drives policy + scope gate checks
coverage_scope_status	drives BIRTH_REQUIRED ∩ IN_SCOPE checks
coverage_exemption_reason	drives EXEMPT_WITHOUT_REASON
coverage_review_owner	drives DEFERRED_WITHOUT_OWNER

INSERT fires unconditionally so newly inserted rows always pass through the gate.

---

Trigger — CREATE TRIGGER only (no REPLACE; no COMMENT ON)

CREATE TRIGGER trg_collection_onboarding_soft_gate
AFTER INSERT OR UPDATE OF
    governance_role,
    coverage_status,
    coverage_scope_status,
    coverage_exemption_reason,
    coverage_review_owner
ON public.collection_registry
FOR EACH ROW
EXECUTE FUNCTION public.fn_collection_onboarding_soft_gate();

---

Notes for GPT reviewer (rev3)

1. CREATE TRIGGER only — PostgreSQL has no CREATE OR REPLACE TRIGGER for plain CREATE TRIGGER; pre-existence at install time must be treated as BLOCKED_EXISTING_CONFLICT.
2. Trigger is AFTER and FOR EACH ROW.
3. INSERT is unconditional; UPDATE is column-scoped to the 5 policy-relevant columns.
4. The trigger's function body never raises (single outer EXCEPTION boundary), so this trigger cannot block writes to collection_registry.
5. rev3 fix #2: COMMENT ON TRIGGER removed from this executable artifact. Optional metadata comment must be packaged separately if desired.
6. B3-F1b execution must verify public.fn_collection_onboarding_soft_gate() is installed before creating this trigger, and must also verify the gate function passes the strict zero-arg trigger-function checks (pronargs=0, prorettype='pg_catalog.trigger'::regtype).