P3D — B3-F1a Soft Gate Shape Probe + SQL Artifact — Agent Prompt (DRAFT rev3)

Date: 2026-05-13 Rev: 3 (4 GPT blocking issues: sibling scope global, no COMMENT ON, function resolution strict, helper no catch) Self-contained. Mode: READ-ONLY + COMPILE-ONLY. NO DDL. NO mutation.

---

Mission

Re-probe live PG. Recompile all 4 SQL artifacts fixing 4 remaining blocking issues. Store in KB. Do NOT execute.

---

Scope constants

gate_function_name = fn_collection_onboarding_soft_gate
gate_helper_name = fn_b3f1_log_collection_onboarding_gap
gate_trigger_name = trg_collection_onboarding_soft_gate
gate_trigger_target_family = collection_registry
sibling_policy_key = policy.birth_trigger.accepted_sibling_scope
contract_function_name = fn_birth_registry_auto
exempt_collection = birth_registry (exempt from onboarding checks AFTER recursive risk check)
issue_category = collection_onboarding_gap
db_schema = DISCOVER

Contract identifiers above are allowed as reviewed policy/contract names. Runtime OIDs and sibling scope must be resolved live from PG; do not hardcode OIDs or sibling collection lists.

---

Hard boundaries

ZERO DDL.
ZERO CREATE FUNCTION.
ZERO CREATE TRIGGER.
ZERO INSERT/UPDATE/DELETE in PG.
ZERO system_issues writes.
ZERO system_health_checks writes.
READ-ONLY SELECTs only against pg_catalog, information_schema, collection_registry, dot_config, species_collection_map, system_issues, and system_health_checks if present.
KB artifact/report upload is required.

---

Phase 0–3 — Discovery + live re-probe

Re-probe live state. Do not rely on rev1/rev2 probe results, prior artifacts, or chat summaries.

Required live probes:

• system_issues table shape, constraints, indexes, live status semantics, insert guard requirements.
• system_health_checks presence/shape if present.
• collection_registry required columns and check constraints.
• dot_config key policy.birth_trigger.accepted_sibling_scope, parsed as JSON object with version, source_review, and entries.
• species_collection_map collection key column.
• contract function by strict trigger-function signature.
• sibling functions from dot_config.entries[*].function by strict trigger-function signature.
• conflict status for gate function, helper function, and gate trigger.

---

Phase 4 — Compile corrections (4 fixes)

Fix 1: SIBLING_OUT_OF_SCOPE = GLOBAL scope

REV2 (wrong): checked only within BIRTH_REQUIRED ∩ IN_SCOPE block
REV3 (correct): check on EVERY collection_registry row that has a physical table
  Exception: birth_registry uses BIRTH_REGISTRY_RECURSIVE_RISK path instead

Logic:
  FOR EACH collection_registry row (except birth_registry):
    IF physical table exists:
      Scan pg_trigger for sibling OID triggers (AFTER INSERT ROW)
      IF sibling trigger found AND collection NOT in sibling policy collections:
        → SIBLING_OUT_OF_SCOPE (CRITICAL)

  This runs BEFORE the BIRTH_REQUIRED ∩ IN_SCOPE block, not inside it.
  A DEFERRED collection with rogue sibling trigger = still flagged.
  An EXEMPT collection with rogue sibling trigger = still flagged.

Keep scoped to BIRTH_REQUIRED ∩ IN_SCOPE:

• MISSING_PHYSICAL_TABLE
• MISSING_SPECIES_MAPPING
• MISSING_ACCEPTED_BIRTH_TRIGGER

Fix 2: Remove COMMENT ON from executable artifacts

REV2: included COMMENT ON FUNCTION + COMMENT ON TRIGGER in compiled SQL
REV3: remove all COMMENT ON from executable SQL artifacts
  If comments desired → separate optional metadata artifact, not part of B3-F1b execution

Fix 3: Strict function OID resolution

REV2: WHERE proname = '...' LIMIT 1
REV3:
  SELECT oid FROM pg_proc p
  JOIN pg_namespace n ON n.oid = p.pronamespace
  WHERE n.nspname = <resolved_schema>
    AND p.proname = '<function_name>'
    AND p.pronargs = 0
    AND p.prorettype = 'pg_catalog.trigger'::regtype;

  0 matches → function missing → BLOCK for contract; for a sibling policy entry, report missing sibling function and exclude that entry only if the policy entry has no live trigger usage; otherwise BLOCK because policy points to an active but unresolved contract sibling
  1 match → RESOLVED
  >1 matches → AMBIGUOUS → BLOCK, report ambiguity, do NOT silently pick one

  Apply to:
  - fn_birth_registry_auto (contract)
  - each sibling function from dot_config entries

Fix 4: Helper exception handling

REV2: helper has EXCEPTION WHEN OTHERS THEN RETURN; + outer gate has EXCEPTION
REV3: REMOVE helper-level EXCEPTION block entirely
  Helper raises normally if system_issues INSERT fails
  Outer gate function catches all: EXCEPTION WHEN OTHERS THEN RETURN NEW
  Result: single exception boundary, no silent loss of issue logging
  If system_issues is broken, outer gate swallows → write proceeds → B3-HC/full-scan detects no-op

---

Phase 5–8 — Compile trigger, rollback, full-scan, store artifacts (same structure as rev2)

Full-scan rev3 corrections:

- SIBLING_OUT_OF_SCOPE: global over ALL registry rows with physical table (except birth_registry recursive path)
- MISSING_ACCEPTED_BIRTH_TRIGGER: BIRTH_REQUIRED ∩ IN_SCOPE only
- Function resolution: pronargs=0 + prorettype=trigger, no LIMIT 1
- No COMMENT ON in executable

Rollback unchanged: drop only trg + fn_gate + fn_helper. No B3-A, no policy, no issues.

---

Artifact paths (overwrite rev2)

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

---

Final response

b3f1a_recompile_status=PASS|PARTIAL|BLOCKED
sibling_out_of_scope_global=true|false
comment_on_removed=true|false
function_resolution_strict=true|false
helper_exception_removed=true|false
all_4_rev3_fixes_applied=true|false
artifacts_stored_in_kb=true|false
strict_function_resolution_used=true|false
no_comment_on_in_executable_sql=true|false
no_embedded_sibling_collection_list=true|false
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

---

B3-F1a rev3 | 4 fixes | Compile-only | 2026-05-13