P3D — Birth System B3-F1 Soft Gate Implementation — Agent Prompt (DRAFT)

Date: 2026-05-12 Self-contained. Scope: shape probe + soft gate installation. NO RAISE EXCEPTION. NO write blocking.

---

Mission

Phase A: Verify system_issues schema supports gate writes. Phase B: Install PG-native soft gate (function + trigger) on collection_registry that logs onboarding issues without blocking.

---

Scope constants

gate_function_name = fn_collection_onboarding_soft_gate (NEW — verify absent)
gate_trigger_name = trg_collection_onboarding_soft_gate (NEW — verify absent)
gate_trigger_target = collection_registry
gate_trigger_timing = AFTER INSERT OR UPDATE
sibling_policy_key = policy.birth_trigger.accepted_sibling_scope
contract_function_name = fn_birth_registry_auto (resolve OID live)
exempt_collection = birth_registry
issue_category = BIRTH_ONBOARDING
db_schema = DISCOVER

---

Hard boundaries

DDL: CREATE FUNCTION + CREATE TRIGGER only (soft gate artifacts).
No CREATE OR REPLACE — absent→create, exists-same→OK, exists-different→BLOCKED.
No RAISE EXCEPTION in gate function.
No write blocking behavior.
No existing trigger/function modification.
No B3-A trigger changes. No sibling policy changes.
No duplicate cleanup. No description_policy cleanup.
No Phase 5C2. No UI cutover.

---

PHASE A — Shape probe (read-only)

A1. Environment + DB discovery (self-contained)

A2. system_issues concept resolution

Resolve from information_schema:

concept_id	table	semantic_hint	on_unresolved
si_pk	system_issues	Primary key	ABORT
si_category	system_issues	Issue category text	ABORT
si_severity	system_issues	Severity level	ABORT
si_entity_ref	system_issues	Entity reference	ABORT
si_description	system_issues	Description text	ABORT
si_status	system_issues	Issue status	ABORT

A3. collection_registry concept resolution

Resolve: cr_collection_key, cr_governance_role, cr_coverage_status, cr_coverage_scope_status, cr_coverage_exemption_reason.

A4. dot_config concept resolution

Resolve: config_key, config_value.

A5. Gate artifact conflict check

fn_collection_onboarding_soft_gate: absent → OK, exists → check definition
trg_collection_onboarding_soft_gate on collection_registry: absent → OK, exists → check binding

If Phase A any ABORT → STOP, report SCHEMA_MISMATCH. Do not proceed to Phase B.

---

PHASE B — Soft gate installation

B1. Function behavioral contract

Agent creates PL/pgSQL function that:

Trigger context: AFTER INSERT OR UPDATE on collection_registry. Receives NEW row.

Behavior:

1. Read governance_role from NEW (resolved concept)
2. Determine if governance_role is "governed-like": read distinct governance_role values from collection_registry WHERE coverage_status = 'BIRTH_REQUIRED' to build the set. Do NOT hardcode role list.
3. If NEW collection's governance_role is in governed-like set OR coverage_status = 'BIRTH_REQUIRED': a. Check physical table exists (information_schema.tables) b. Check species_collection_map row exists c. Check birth trigger exists with accepted function OID:
   • Resolve contract function OID by proname
   • Read sibling policy from dot_config, parse JSON, resolve sibling OIDs by proname
   • Check pg_trigger for any accepted OID binding d. Check if collection = exempt_collection AND has contract trigger → recursive risk e. Check if trigger uses sibling OID but collection NOT in sibling policy → out of scope
4. For coverage validation (all governance roles): a. Check coverage_status IS NOT NULL b. If EXEMPT: check exemption_reason IS NOT NULL
5. For each failed check: check dedup (no open issue for same entity+category), then INSERT system_issues row with appropriate severity. No RAISE EXCEPTION.
6. RETURN NEW (always — never block)

Agent derives actual PL/pgSQL from resolved concepts. Agent logs compiled function body in report.

B2. Trigger creation

CREATE TRIGGER trg_collection_onboarding_soft_gate
  AFTER INSERT OR UPDATE OF <cr_governance_role>, <cr_coverage_status>
  ON <resolved_schema>.<collection_registry>
  FOR EACH ROW EXECUTE FUNCTION fn_collection_onboarding_soft_gate();

Note: trigger fires on governance_role OR coverage_status change (both relevant to gate logic).

B3. Transaction

BEGIN;
-- Advisory lock
-- CREATE FUNCTION (if absent)
-- CREATE TRIGGER (if absent)
-- Verify both exist in pg_catalog
-- Test: simulate by reading current state (no actual INSERT/UPDATE to collection_registry)
COMMIT;

B4. Companion: scheduled full-scan query

Agent produces standalone SQL query that performs all 10 checks across ALL governed collections (same as B3-F0 dry-run logic). Saved in report as birth_onboarding_full_scan_query. Not installed as function/trigger — just a query for ad-hoc or pg_cron use.

---

Rollback

DROP TRIGGER IF EXISTS trg_collection_onboarding_soft_gate ON <collection_registry>;
DROP FUNCTION IF EXISTS fn_collection_onboarding_soft_gate();

Do NOT touch B3-A triggers, sibling policy, or any existing artifacts.

---

Report path

knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-b3f1-soft-gate-implementation-report.md

Final response

b3f1_status=PASS|PARTIAL|BLOCKED|SCHEMA_MISMATCH
shape_probe_pass=true|false
gate_function_created=true|false
gate_trigger_created=true|false
full_scan_query_produced=true|false
raise_exception_used=false
write_blocking_used=false
compiled_function_logged=true|false
compiled_sql_logged=true|false
rollback_sql_logged=true|false
no_existing_artifact_modified=true
report_path=<above>
next_recommended_action=GPT_REVIEW_B3F1_THEN_MILESTONE_ASSESSMENT

---

B3-F1 Soft Gate | DRAFT | Shape probe + behavioral contract | 2026-05-12