P3D — B3-F1b Soft Gate Execution Report (PASS)

Date: 2026-05-13 (UTC) Mode: PG DDL — reviewed soft-gate CREATE statements only Prompt: knowledge/dev/laws/dieu44-trien-khai/prompts/p3d-birth-system-b3f1b-soft-gate-execution-prompt-DRAFT.md (rev6) Approval: knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b3f1b-execution-prompt-and-b3f1c-note-approved-2026-05-13.md Reviewed SQL: rev3 artifacts (function / trigger / rollback / full-scan-query) Result: PASS — all 3 reviewed CREATE statements committed; in-transaction and post-commit verification both passed.

1. Scope executed

Three CREATE statements, executed verbatim from reviewed rev3 artifacts inside one transaction:

CREATE FUNCTION public.fn_b3f1_log_collection_onboarding_gap(text, text, text, text) RETURNS void (helper)
CREATE FUNCTION public.fn_collection_onboarding_soft_gate() RETURNS trigger (gate function)
CREATE TRIGGER trg_collection_onboarding_soft_gate AFTER INSERT OR UPDATE OF (5 cols) ON public.collection_registry FOR EACH ROW EXECUTE FUNCTION public.fn_collection_onboarding_soft_gate() (gate trigger)

No CREATE OR REPLACE, no COMMENT ON, no ALTER, no DML, no smoke test.

2. Environment

Item	Value
Host	VPS `38.242.240.89`
Container	`postgres`
DB	`directus`
PG	16.13 (Debian 16.13-1.pgdg13+1)
Schema	`public`
Local SQL file (host)	`/tmp/b3f1b_exec.sql` (md5 `f1ea899cf46bbff77b84f7dcbbbd6f01`, 557 lines)
Container SQL file	`/tmp/b3f1b_exec.sql` (docker cp from host)
Execution log	`/opt/incomex/logs/b3f1b-soft-gate-exec-20260513T030315Z.log` (VPS)

3. Artifact verification (Phase 1 / Gate 2)

All 4 rev3 artifacts re-read from KB and verified:

Artifact	CREATE FUNCTION only	CREATE TRIGGER only	No `CREATE OR REPLACE`	No `COMMENT ON`	Names match
`…b3f1a-soft-gate-compiled-function.sql.md` (rev3)	yes (helper + gate)	n/a	yes	yes	yes
`…b3f1a-soft-gate-compiled-trigger.sql.md` (rev3)	n/a	yes	yes	yes	yes
`…b3f1a-soft-gate-rollback.sql.md` (rev3)	n/a (DROPs only)	n/a (DROPs only)	yes	yes	yes
`…b3f1a-soft-gate-full-scan-query.sql.md` (rev3)	n/a (read-only SELECTs)	n/a	yes	yes	yes

Helper name = fn_b3f1_log_collection_onboarding_gap. Gate function name = fn_collection_onboarding_soft_gate. Trigger name = trg_collection_onboarding_soft_gate on public.collection_registry. All match scope constants.

4. Preflight conflict check (Phase 2A / Gate 5)

Run immediately before CREATE (live PG, this session):

Object	hits
`fn_b3f1_log_collection_onboarding_gap(text,text,text,text)`	0
`fn_collection_onboarding_soft_gate()`	0
`trg_collection_onboarding_soft_gate` on `public.collection_registry`	0

All absent. No BLOCKED_EXISTING_CONFLICT.

5. Preflight dependency check (Phase 2B / Gate 6)

Check	Result
`public.system_issues` shape (required 11 cols present: entity_type, entity_code, issue_type, severity, status, title, description, source, sub_class, coalesce_key, detected_at)	PASS (11 hits)
`dot_config['policy.birth_trigger.accepted_sibling_scope']` exists	PASS
Sibling policy parses as JSON (entries length=1)	PASS
Sibling policy entry function	`fn_birth_registry_auto_id`
Sibling policy collections (derived from `dot_config`, NOT embedded)	`["governance_relations","law_dot_enforcement","law_jurisdiction"]`
`fn_birth_registry_auto` strict zero-arg trigger resolution	1 match (oid 39232)
`fn_birth_registry_auto_id` strict zero-arg trigger resolution	1 match (oid 66750)
Live sibling AFTER INSERT ROW trigger usage	3 collections: `governance_relations`, `law_dot_enforcement`, `law_jurisdiction`
`out_of_scope_sibling_count`	0
`collection_registry` 5 governance cols present	PASS (governance_role, coverage_status, coverage_scope_status, coverage_exemption_reason, coverage_review_owner)

sibling_scope_derived_from_dotconfig=true.

6. Execution transaction (Phase 4)

Single transaction. psql -v ON_ERROR_STOP=1 -f /tmp/b3f1b_exec.sql.

BEGIN
DO                                 -- advisory lock acquired (pg_try_advisory_xact_lock=true)
CREATE FUNCTION                    -- helper
CREATE FUNCTION                    -- gate
WARNING:  [TRIGGER-GUARD] DDL detected: CREATE TRIGGER on trg_collection_onboarding_soft_gate on public.collection_registry
CREATE TRIGGER                     -- gate trigger
NOTICE:   VERIFY_OK: helper(oid=223128), gate(oid=223129), trigger(tgfoid=223129) all confirmed in-transaction.
DO                                 -- verification block passed
COMMIT

Notes:

Advisory lock key: hashtext('p3d_birth_b3f1b_soft_gate_execution'). Acquired got_lock=true.
[TRIGGER-GUARD] is an informational WARNING emitted by an existing event-trigger-style DDL guard on the database; it does not abort the DDL and is unrelated to the soft gate being installed.
One prior aborted attempt (md5 9509230d…) failed inside the verification DO block only because of a verification-side mistake in my scaffolding (treating pg_get_function_identity_arguments as name-stripped on this PG build, which it is not). The reviewed SQL itself was NOT modified between attempts; the failed attempt was rolled back cleanly — confirmed by a subsequent absence check showing 0/0/0 before re-run.

reviewed_sql_executed_without_modification=true.

7. In-transaction verification (Phase 4D / Gate 8)

All assertions inside the same transaction, before COMMIT:

Assertion	Expected	Observed
helper count	1	1
helper pronargs	4	4
helper argtypes (normalized)	`text, text, text, text`	`text, text, text, text`
helper rettype	`void`	`void`
gate count	1	1
gate pronargs	0	0
gate rettype	`trigger`	`trigger`
trigger count on `collection_registry`	1	1
trigger AFTER (`tgtype & 2 = 0`)	true	true (tgtype=21)
trigger ROW (`tgtype & 1 = 1`)	true	true
trigger includes INSERT (`tgtype & 4 = 4`)	true	true
trigger includes UPDATE (`tgtype & 16 = 16`)	true	true
trigger enabled (`tgenabled <> 'D'`)	true	`tgenabled='O'`
`t.tgfoid` matches gate OID	true	223129 = 223129
trigger column scope (5 expected, exact set)	`{governance_role, coverage_status, coverage_scope_status, coverage_exemption_reason, coverage_review_owner}`	exact match

in_transaction_verified=true. Transaction committed.

8. Post-commit verification (Phase 5)

Independent psql session, read-only checks:

object   = helper
oid      = 223128
pronargs = 4
rettype  = void
argtypes = text, text, text, text

object   = gate
oid      = 223129
pronargs = 0
rettype  = trigger

object     = trigger
tgname     = trg_collection_onboarding_soft_gate
tgtype     = 21
tgenabled  = O
tgfoid     = 223129
triggerdef = 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 fn_collection_onboarding_soft_gate()
columns    = {governance_role, coverage_status, coverage_scope_status, coverage_exemption_reason, coverage_review_owner}

post_commit_verified=true. pg_get_triggerdef output matches reviewed rev3 trigger artifact exactly.

9. Rollback SQL (logged, NOT executed)

From reviewed …b3f1a-soft-gate-rollback.sql.md (rev3). To revert B3-F1b, run as one transaction:

-- 1. Drop trigger first (uses the function)
DROP TRIGGER IF EXISTS trg_collection_onboarding_soft_gate
  ON public.collection_registry;

-- 2. Drop the gate function
DROP FUNCTION IF EXISTS public.fn_collection_onboarding_soft_gate();

-- 3. Drop the task-specific helper (signature pinned)
DROP FUNCTION IF EXISTS public.fn_b3f1_log_collection_onboarding_gap(text, text, text, text);

rollback_sql_logged=true. Rollback was NOT executed by this run.

10. Hard-boundary compliance

Boundary	Compliance
Reviewed SQL executed without modification	✅
No `CREATE OR REPLACE`	✅ (`create_or_replace_used=false`)
No `COMMENT ON`	✅ (`comment_on_executed=false`)
No `ALTER`	✅
No `DROP` (other than rollback prep, which was not executed)	✅
No `INSERT`/`UPDATE`/`DELETE` on any table	✅ (`dml_performed=false`)
No DML smoke test	✅ (`smoke_test_performed=false`)
No `system_issues` writes by prompt	✅
No `system_health_checks` writes	✅
No hard gate / Phase 5C2 / UI cutover	✅

11. Final response fields

b3f1b_status=PASS
helper_created=true
gate_function_created=true
gate_trigger_created=true
create_or_replace_used=false
comment_on_executed=false
dml_performed=false
smoke_test_performed=false
advisory_lock_acquired=true
in_transaction_verified=true
post_commit_verified=true
rollback_sql_logged=true
sibling_scope_derived_from_dotconfig=true
out_of_scope_sibling_count=0
reviewed_sql_executed_without_modification=true
report_path=knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-b3f1b-soft-gate-execution-report.md
log_path=vps:/opt/incomex/logs/b3f1b-soft-gate-exec-20260513T030315Z.log
next_recommended_action=GPT_REVIEW_B3F1B_THEN_B3F1C_AUTOMATION

12. Caveats

B3-F1b installs a real-time soft gate on collection_registry mutations only. It does NOT catch direct PG DDL drift (e.g. someone creating a table without a collection_registry row, or hand-attaching a rogue trigger). Full-scan coverage is the B3-F1c / B3-HC automation track.
The soft gate body wraps all checks in EXCEPTION WHEN OTHERS THEN RETURN NEW; — by design the gate cannot block writes. Drift evidence flows via public.system_issues rows with source='dot-soft-gate-collection-onboarding' and issue_type='collection_onboarding_gap'.
Live sibling policy currently has 1 entry only (fn_birth_registry_auto_id → 3 collections). The gate function reads dot_config at runtime, so policy edits take effect without redeployment.