P3D — B3-F1b Soft Gate Execution — Agent Prompt (DRAFT)

Date: 2026-05-13 Self-contained. Scope: execute reviewed rev3 SQL only. Two-pass compliant: SQL reviewed by GPT before this execution step.

---

Mission

Execute 3 CREATE statements from reviewed rev3 artifacts: helper function, gate function, trigger on collection_registry. Nothing else.

---

Scope constants

helper_function = fn_b3f1_log_collection_onboarding_gap(text,text,text,text)
gate_function = fn_collection_onboarding_soft_gate()
gate_trigger = trg_collection_onboarding_soft_gate ON collection_registry
approved_artifacts_revision = rev3
sibling_policy_key = policy.birth_trigger.accepted_sibling_scope
db_schema = DISCOVER

---

Hard boundaries

DDL allowed: CREATE FUNCTION (2) + CREATE TRIGGER (1) only.
Execute ONLY reviewed SQL from rev3 artifacts. Do not regenerate, rewrite, optimize, or modify the reviewed SQL during execution.
No CREATE OR REPLACE. No COMMENT ON. No ALTER. No DROP (except rollback prep).
No DML (no INSERT/UPDATE/DELETE on any table).
No DML smoke test.
No hard gate. No RAISE EXCEPTION behavior added.
No system_issues writes by prompt. No system_health_checks writes.
No Phase 5C2. No UI cutover.

---

Phase 0 — Environment + DB discovery (self-contained)

---

Phase 1 — Load reviewed artifacts from KB

Read all 4 rev3 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

Verify each artifact contains:

• CREATE FUNCTION only (not CREATE OR REPLACE)
• No COMMENT ON
• Correct function/trigger names matching scope constants
• If any artifact fails verification → BLOCKED_ARTIFACT_MISMATCH

---

Phase 2 — Preflight dependency checks

2A. Conflict check (all 3 must be absent)

fn_b3f1_log_collection_onboarding_gap(text,text,text,text) → absent
fn_collection_onboarding_soft_gate() → absent
trg_collection_onboarding_soft_gate on collection_registry → absent
If ANY exists → BLOCKED_EXISTING_CONFLICT

2B. Dependency check

system_issues table exists + shape sufficient (resolve si_pk, si_category, si_severity, etc.)
dot_config sibling policy key exists + parses as JSON
fn_birth_registry_auto strict resolution (pronargs=0, prorettype=trigger) → 1 match
fn_birth_registry_auto_id strict resolution → 1 match
Live sibling trigger usage must match the sibling policy loaded from dot_config, with out_of_scope_sibling_count=0. Do not embed or assume a fixed sibling collection list; derive the approved set from policy.birth_trigger.accepted_sibling_scope.
collection_registry exists with 5 coverage columns
If ANY fails → BLOCKED_DEPENDENCY

---

Phase 3 — Execution log

RUN_STARTED_AT=$(date -u +"%Y-%m-%dT%H:%M:%SZ")

Log: artifact verification, conflict checks, dependency checks, compiled SQL, rollback SQL.

---

Phase 4 — Execution transaction

BEGIN;

-- Advisory lock
SELECT pg_try_advisory_xact_lock(hashtext('p3d_birth_b3f1b_soft_gate_execution')) AS got_lock;
-- If got_lock=false → ROLLBACK, report LOCK_BUSY, do not continue.

-- 4A. CREATE helper function (from reviewed artifact)
-- Execute exact SQL from compiled-function.sql.md helper section

-- 4B. CREATE gate function (from reviewed artifact)
-- Execute exact SQL from compiled-function.sql.md gate section

-- 4C. CREATE trigger (from reviewed artifact)
-- Execute exact SQL from compiled-trigger.sql.md

-- 4D. In-transaction verification
--   Helper: exists, exact signature (text,text,text,text), returns void
--   Gate: exists, pronargs=0, prorettype=trigger
--   Trigger: exists on collection_registry, AFTER, INSERT OR UPDATE, FOR EACH ROW, enabled, tgfoid = gate function OID

-- ALL verified → COMMIT
-- ANY fail → ROLLBACK
COMMIT;

---

Phase 5 — Post-commit verification

Repeat object checks from 4D outside transaction. Confirm all 3 artifacts persist.

---

Phase 6 — KB report + rollback keys

Report path: reports/p3d-birth-system-b3f1b-soft-gate-execution-report.md

Rollback SQL from reviewed rollback artifact (logged, not executed).

---

Final response

b3f1b_status=PASS|PARTIAL|BLOCKED|LOCK_BUSY|ARTIFACT_MISMATCH|EXISTING_CONFLICT|DEPENDENCY_FAIL
helper_created=true|false
gate_function_created=true|false
gate_trigger_created=true|false
create_or_replace_used=false
comment_on_executed=false
dml_performed=false
smoke_test_performed=false
advisory_lock_acquired=true|false
in_transaction_verified=true|false
post_commit_verified=true|false
rollback_sql_logged=true|false
sibling_scope_derived_from_dotconfig=true|false
out_of_scope_sibling_count=0
reviewed_sql_executed_without_modification=true|false
report_path=<above>
log_path=<path>
next_recommended_action=GPT_REVIEW_B3F1B_THEN_B3F1C_AUTOMATION

---

B3-F1b Execution | DRAFT | Reviewed SQL only | 2026-05-13