P3D — B3-F1a Soft Gate Shape Probe + SQL Artifact Report (rev3)

Date: 2026-05-13 Mode executed: READ-ONLY + COMPILE-ONLY Prompt: knowledge/dev/laws/dieu44-trien-khai/prompts/p3d-birth-system-b3f1a-soft-gate-shape-probe-and-sql-artifact-prompt-DRAFT.md revision 17 GPT approval: knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b3f1a-rev3-prompt-approved-for-agent-recompile-2026-05-13.md Prior blocking review: knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b3f1a-recompiled-sql-rev2-blocked-2026-05-13.md

Summary

All four B3-F1a soft-gate SQL artifacts were recompiled for rev3 against a fresh live PG re-probe. Rev1/rev2 probe results were not relied on. The four rev2 blocking issues are now addressed:

1. SIBLING_OUT_OF_SCOPE is GLOBAL.
2. COMMENT ON statements removed from executable SQL.
3. Function resolution is strict (zero-arg trigger function).
4. Helper EXCEPTION block removed; outer gate is the single boundary.

No DDL was executed. No PG mutation was performed. No system_issues or system_health_checks rows were written. No function execution. No trigger installation.

Live re-probe (2026-05-13)

Probe	Result
DB / schema	directus / public (PG 16)
system_issues shape	28 columns; severity check `(critical
system_health_checks	present (13 columns)
collection_registry	35 columns; 166 rows; coverage_status check ∈ {BIRTH_REQUIRED, BIRTH_EXEMPT_*, BIRTH_DEFERRED_NEEDS_REVIEW, UNCLASSIFIED_NEW}; coverage_scope_status check ∈ {IN_SCOPE, USER_EXCLUDED, FUTURE_SCOPE, ORPHAN_REGISTRY}; 159 rows have a physical BASE TABLE in public
coverage distribution	BIRTH_REQUIRED ∩ IN_SCOPE = 72; BIRTH_EXEMPT_* (IN_SCOPE) = 36; BIRTH_DEFERRED_NEEDS_REVIEW = 58 (3 IN_SCOPE / 17 FUTURE_SCOPE / 7 ORPHAN_REGISTRY / 31 USER_EXCLUDED)
species_collection_map	collection-key column = collection_name
dot_config['policy.birth_trigger.accepted_sibling_scope']	version=1; 1 entry — function fn_birth_registry_auto_id, collections [governance_relations, law_dot_enforcement, law_jurisdiction]
Contract fn_birth_registry_auto strict resolution	1 match (oid 39232, pronargs=0, prorettype=trigger) → OK
Sibling fn_birth_registry_auto_id strict resolution	1 match (oid 66750, pronargs=0, prorettype=trigger) → OK
Sibling live trigger usage	exactly the 3 collections in policy; no rogue out-of-policy usage
birth_registry triggers	3 triggers (trg_birth_auto_certify, trg_count_birth_registry disabled, trg_birth_change_flag_matrix); none reference contract or sibling OID → no current recursive risk
Conflict on gate function/helper/trigger names	none — safe to CREATE at B3-F1b time
fn_guard_system_issues_insert()	present (zero-arg)

Rev3 corrections — application

Fix 1 — SIBLING_OUT_OF_SCOPE global

• Function: a dedicated SIBLING_OUT_OF_SCOPE block now runs before the BIRTH_REQUIRED ∩ IN_SCOPE branch and after the birth_registry recursive-risk early return. Any non-birth_registry registry row whose physical table carries an AFTER INSERT ROW sibling-OID trigger and which is not in the sibling policy collection list is flagged. Coverage status is irrelevant.
• Full-scan query: SIBLING_OUT_OF_SCOPE UNION branch is no longer guarded by coverage_status='BIRTH_REQUIRED' AND coverage_scope_status='IN_SCOPE'. It is guarded only by r.has_table and r.collection_name <> 'birth_registry'.

Kept scoped to BIRTH_REQUIRED ∩ IN_SCOPE: MISSING_PHYSICAL_TABLE, MISSING_SPECIES_MAPPING, MISSING_ACCEPTED_BIRTH_TRIGGER.

Fix 2 — COMMENT ON removed

• compiled-function rev3: COMMENT ON FUNCTION statements removed from both helper and gate.
• compiled-trigger rev3: COMMENT ON TRIGGER statement removed.
• No COMMENT ON appears anywhere in the executable artifacts.

Fix 3 — Strict function resolution

• Both runtime gate code and full-scan query resolve contract and sibling functions only on n.nspname='public', p.proname=<name>, p.pronargs=0, p.prorettype='pg_catalog.trigger'::regtype.
• Both use array_agg(p.oid) (not LIMIT 1) and explicitly:
  • 0 matches → contract: RAISE (caught by outer EXCEPTION) so gate aborts row safely; sibling entry: RAISE if any live trigger uses a function of that name; else silently skip the entry.
  • 1 match → resolved.

  • 1 matches → RAISE (caught by outer EXCEPTION). Full-scan surfaces this as AMBIGUOUS via the function-resolution-health SELECT.

• No silent LIMIT 1 anywhere.

Fix 4 — Helper EXCEPTION removed

• fn_b3f1_log_collection_onboarding_gap no longer has an EXCEPTION WHEN OTHERS THEN RETURN; block.
• The outer gate function retains the single EXCEPTION WHEN OTHERS THEN RETURN NEW boundary.
• Failures of system_issues insert (guard rejection, dedup race, constraint, etc.) propagate from the helper to the gate's outer catch — gate still never blocks the write to collection_registry, but failure is no longer absorbed by an inner catch that hides everything.

Other rev3 directives — application

Directive	Where applied
Trigger validation by tgfoid, (tgtype & 1)=1, (tgtype & 2)=0, (tgtype & 4)=4, tgenabled <> 'D', NOT t.tgisinternal	function (recursive-risk + global SIBLING + check 9) and full-scan (br_recursive + trg_eval)
Full-scan logic mirrors function logic	rev3 fix #1 + #3 mirrored in full-scan
Rollback names match revised function/helper/trigger	rollback rev3 lists trg_collection_onboarding_soft_gate, fn_collection_onboarding_soft_gate(), fn_b3f1_log_collection_onboarding_gap(text,text,text,text) only
Runtime gate must not embed sibling collection list	Sibling collection set built at runtime from dot_config JSON; no literal collection name in the function body except 'birth_registry' (which is the documented exempt collection, not a sibling)
Sibling scope read from dot_config['policy.birth_trigger.accepted_sibling_scope']	SELECT NULLIF(value,'')::jsonb FROM public.dot_config WHERE key='...'
Runtime supports multiple sibling policy entries	FOR v_entry IN SELECT * FROM jsonb_array_elements(...) loop appends to v_sibling_oids and v_sibling_collections arrays
Re-probe live; do not rely on rev1/rev2 probe results	All probes re-executed on 2026-05-13 (see live re-probe table above)
Store all four artifacts in KB	done — see "Artifacts" below

Artifacts

• knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-compiled-function.sql.md (rev3)
• knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-compiled-trigger.sql.md (rev3)
• knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-rollback.sql.md (rev3)
• knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-full-scan-query.sql.md (rev3)

Hard boundaries — verification

Boundary	Status
No DDL executed	yes
No CREATE FUNCTION executed	yes
No CREATE TRIGGER executed	yes
No INSERT/UPDATE/DELETE in PG	yes
No system_issues writes	yes
No system_health_checks writes	yes
No function execution	yes
No trigger installation	yes
No hard gate	yes
No Phase 5C2	yes
No UI cutover	yes
KB artifact/report upload performed	yes

Final fields

b3f1a_recompile_status=PASS
sibling_out_of_scope_global=true
comment_on_removed=true
function_resolution_strict=true
helper_exception_removed=true
all_4_rev3_fixes_applied=true
artifacts_stored_in_kb=true
strict_function_resolution_used=true
no_comment_on_in_executable_sql=true
no_embedded_sibling_collection_list=true
no_ddl_executed=true
no_pg_mutation_performed=true
report_path=knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-b3f1a-soft-gate-shape-probe-and-sql-artifact-report.md
next_recommended_action=GPT_REVIEW_REV3_SQL_THEN_B3F1B