GPT Review — B3-F1a Compiled SQL — BLOCKED, Revision Required — 2026-05-13

Scope reviewed

Reviewed Agent B3-F1a report and SQL artifacts:

• knowledge/dev/laws/dieu44-trien-khai/reports/p3d-birth-system-b3f1a-soft-gate-shape-probe-and-sql-artifact-report.md
• knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-compiled-function.sql.md
• knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-compiled-trigger.sql.md
• knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-rollback.sql.md
• knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-system-b3f1a-soft-gate-full-scan-query.sql.md

Verdict

Status: BLOCKED_REVISION_REQUIRED

B3-F1a shape probe itself is useful and confirms system_issues is viable, but the compiled SQL artifacts are not approved for B3-F1b execution. The function/full-scan logic must be revised and recompiled.

Blocking issue 1 — CREATE OR REPLACE violates absent→create / exists-different→BLOCKED rule

Compiled function uses:

CREATE OR REPLACE FUNCTION public.fn_collection_onboarding_soft_gate()
CREATE OR REPLACE FUNCTION public._log_onboarding_gap(...)

B3-F1b must not use CREATE OR REPLACE. Required behavior:

• if absent → CREATE FUNCTION;
• if exists with expected definition → OK / no-op or blocked for explicit review;
• if exists different → BLOCKED;
• never silently replace.

Blocking issue 2 — helper function not included in conflict model

The compiled SQL creates helper public._log_onboarding_gap(...), but B3-F1a conflict check only reported:

• fn_collection_onboarding_soft_gate
• trg_collection_onboarding_soft_gate

Required:

• Probe helper name/signature conflict explicitly.
• Prefer namespaced task-specific helper name, e.g. fn_b3f1_log_collection_onboarding_gap, to avoid generic _log_onboarding_gap collision.
• Rollback must drop only helper created by B3-F1b and must not drop a pre-existing/reused helper.

Blocking issue 3 — severity mapping is wrong

Compiled helper inserts:

severity = 'warning'

for every gap. This loses CRITICAL vs WARN semantics.

Required:

• Function/helper must accept severity as a parameter or compute severity per gap.
• CRITICAL examples: missing physical table, missing species mapping, missing accepted birth trigger, sibling out of scope, birth_registry recursive risk, missing coverage policy where applicable.
• WARN examples: exempt without reason, deferred without owner, unclassified coverage policy, scope-policy warnings where applicable.

Blocking issue 4 — BIRTH_REQUIRED / IN_SCOPE scoping is wrong

Compiled logic checks physical/species/trigger for:

governed-like OR BIRTH_REQUIRED

It does not respect the established B3-F0 rule: physical table/species/trigger checks apply to BIRTH_REQUIRED ∩ IN_SCOPE.

Required:

• Use coverage_status='BIRTH_REQUIRED' AND coverage_scope_status='IN_SCOPE' for table/species/trigger requirements.
• Governed-like rows with NULL/unknown policy should trigger policy gaps, not physical/species/trigger gaps before policy is known.

Blocking issue 5 — missing required policy checks

Compiled function omits several B3-F0 checks:

• MISSING_SCOPE_POLICY
• UNCLASSIFIED_COVERAGE_POLICY
• DEFERRED_WITHOUT_OWNER
• SCOPE_MISMATCH

Required:

• Include all B3-F0 soft-gate checks or explicitly document why any check is intentionally scheduled-only and not trigger-based.

Blocking issue 6 — birth_registry recursive risk is not checked

Compiled function short-circuits:

IF v_table_name = 'birth_registry' THEN RETURN NEW;

That means it cannot log birth_registry recursive risk if the row changes or if a contract/sibling trigger appears on birth_registry. The full-scan query also excludes birth_registry, so it also misses this check.

Required:

• Do not blindly skip birth_registry before checking whether it has accepted birth trigger(s).
• Preserve exemption behavior, but still detect and log/report recursive risk if an accepted birth trigger exists.

Blocking issue 7 — accepted trigger detection is unsafe with multiple triggers

Compiled logic uses:

COUNT(*) > 0, MAX(p.proname)
WHERE p.proname IN ('fn_birth_registry_auto','fn_birth_registry_auto_id')

Issues:

• Multiple triggers can exist; MAX(p.proname) can hide contract+sibling combinations.
• It filters to accepted names first, making birth_trigger_not_accepted effectively unreachable.
• It does not independently detect out-of-scope sibling if an accepted contract trigger also exists.
• It does not require row-level trigger (tgtype & 1).

Required:

• Compute separate booleans:
  • has_contract_after_insert_row
  • has_sibling_after_insert_row
  • has_out_of_scope_sibling_after_insert_row
  • has_any_after_insert_birth_like_trigger if applicable
• Validate using tgfoid resolved from live pronames, not MAX(proname).
• Include t.tgtype & 1 <> 0 for row-level trigger.

Blocking issue 8 — compiled function embeds contract function names in runtime logic without policy separation

fn_birth_registry_auto and fn_birth_registry_auto_id may be acceptable contract identifiers, but the compiled function must be clear that:

• contract function is resolved live;
• sibling functions come from dot_config policy entries;
• no sibling collection list is embedded.

The current function embeds both function names in IN (...); acceptable only if contract identifier policy is explicitly documented. Revised SQL should reduce hardcoded sibling assumptions by iterating over policy entries and resolving pronames.

Blocking issue 9 — full-scan query repeats the same logic defects

The full-scan artifact:

• excludes birth_registry, so misses recursive-risk detection;
• applies checks to must_be_onboarded rather than strictly BIRTH_REQUIRED ∩ IN_SCOPE where appropriate;
• uses filtered birth_trg_fn and cannot report unaccepted trigger functions reliably;
• does not include all required B3-F0 policy checks.

The full-scan query must be recompiled in sync with the corrected function logic.

Blocking issue 10 — rollback must match revised artifact set

Rollback currently drops generic helper _log_onboarding_gap. If helper is renamed or if existing-helper conflict rules change, rollback must be revised accordingly.

Accepted parts

The following B3-F1a findings are accepted:

• system_issues_shape_sufficient=true.
• system_health_checks_present=true.
• Status semantics are live-supported: open, resolved, archived observed and constrained.
• dot_config sibling policy exists and parses.
• Gate function/trigger names were absent.
• No DDL/DML was executed.

Required revision path

Opus must revise B3-F1a compiled SQL artifacts, not execute B3-F1b.

Recommended next step:

• Opus drafts a corrected B3-F1a-rev2 compile directive for Agent, or directly revises the compile prompt, requiring Agent to regenerate all four artifacts and report.

Governance status

b3f1a_compiled_sql_review_status=BLOCKED_REVISION_REQUIRED
b3f1b_execution_allowed=false
create_function_allowed=false
create_trigger_allowed=false
pg_mutation_allowed=false
hard_gate_allowed=false
phase5c2_migration_allowed=false
next_recommended_action=OPUS_REVISE_B3F1A_COMPILE_PROMPT_AND_RECOMPILE_ARTIFACTS