P3D — Birth System B3-F1 Soft Gate Design (revised)

Date: 2026-05-12 Rev: 2 (two-pass compliant, trigger coverage expanded, dedup from live shape)

---

Structure: B3-F1a (compile) → GPT review → B3-F1b (execute)

Step	Mode	Allowed
B3-F1a	READ-ONLY + COMPILE SQL artifacts to KB	No DDL, no mutation
GPT review	Review compiled function/trigger/rollback	—
B3-F1b	Execute reviewed SQL	CREATE FUNCTION + CREATE TRIGGER only

---

A. Strategy: SHAPE_PROBE_FIRST → HYBRID (unchanged)

Trigger on collection_registry for real-time. Companion full-scan for drift.

B. Issue storage: system_issues (verify live in B3-F1a)

B3-F1a probe determines actual columns. Design adapts to live shape.

C. Trigger event coverage (EXPANDED)

AFTER INSERT OR UPDATE OF
  <cr_governance_role>,
  <cr_coverage_status>,
  <cr_coverage_scope_status>,
  <cr_coverage_exemption_reason>,
  <cr_coverage_review_owner>
ON collection_registry

All 5 policy-relevant columns. Any change → gate re-evaluates.

D. Dedup strategy (from live shape)

B3-F1a probes system_issues for:

• Unique constraints / composite unique indexes
• Available columns for structured dedup key

Preferred: dedup by (entity_ref + category + status='open'). If no suitable structured key → propose (entity_ref + category) check before INSERT. If system_issues lacks entity_ref → BLOCKED.

E. Gate behavior (unchanged)

Soft only. No RAISE EXCEPTION. Always RETURN NEW. Log issues only.

F. Anti-hardcode (unchanged)

All live-derived. Sibling policy from dot_config. OIDs by proname.

G. SQL artifact storage

B3-F1a produces 4 KB artifacts:

artifacts/p3d-birth-system-b3f1a-soft-gate-compiled-function.sql.md
artifacts/p3d-birth-system-b3f1a-soft-gate-compiled-trigger.sql.md
artifacts/p3d-birth-system-b3f1a-soft-gate-rollback.sql.md
artifacts/p3d-birth-system-b3f1a-soft-gate-full-scan-query.sql.md

H. Rollback (unchanged)

DROP trigger + function only. Do NOT touch B3-A artifacts.

---

B3-F1 Design revised | Two-pass compliant | 2026-05-12