KB-C2F8 rev 4
P3D — B3-F1c Full-Scan Function SQL Rev3 (compile-only, revised per GPT review round 2)
23 min read Revision 4
p3dbirth-systemb3f1cfull-scanfunctioncompile-onlyrev3
P3D — B3-F1c Full-Scan Function SQL Rev3 (compile-only, revised per GPT review round 2)
Mode: COMPILE-ONLY. NOT executed. Rev: 3 Date: 2026-05-13 Revision trigger round 1: GPT review
gpt-review-b3f1c-a-full-scan-compiled-artifacts-blocked-2026-05-13.md— 6 blocking issues (all fixed in rev2). Revision trigger round 2: GPT reviewgpt-review-b3f1c-revised-artifacts-blocked-schema-json-2026-05-13.md— 2 new blocking issues + 1 recommendation. Source-of-logic: rev3 gate logic (GPT-approved), with dependency-failure pre-check, schema-locked catalog lookups, safe JSON parse, and duplicate key hardening.
Deployment target declaration (Issue 3 fix — unchanged from rev2)
deployment_schema = public
deployment_schema_type = REVIEWED_DEPLOYMENT_CONSTANT
schema_agnostic = false
This function is compiled against public schema as a reviewed deployment target. All public. qualifications and 'public'::regnamespace references are intentional deployment constants, not runtime schema discovery.
Changes from Rev2
| Issue | Fix applied |
|---|---|
| New-1. pg_class schema-lock | All pg_class joins now use c.relnamespace = 'public'::regnamespace directly. Removed separate pg_namespace LEFT JOIN that was filtering after the fact. Applied to both trg_eval and br_recursive CTEs. |
| New-2. Safe JSON parse | Sibling policy value parsed inside BEGIN...EXCEPTION WHEN OTHERS block. Malformed JSON → SIBLING_POLICY_MALFORMED_JSON dependency failure, not uncaught exception. |
| New-3. Duplicate policy key | Policy key count checked before parse. 0 → MISSING. >1 → DUPLICATE_KEY. 1 → parse. |
Pre-conditions verified (probe results, unchanged)
schema = public (deployment target)
fn_birth_onboarding_full_scan exists = false -- no conflict
fn_b3f1_log_collection_onboarding_gap(text,text,text,text) exists = true
fn_collection_onboarding_soft_gate() returns trigger exists = true
dot_config['policy.birth_trigger.accepted_sibling_scope'] loads (280 bytes)
collection_registry rows = 166 (evidence snapshot, not gate)
Function SQL (DO NOT EXECUTE in this run)
-- B3-F1c full-scan function Rev3. COMPILE-ONLY artifact. Execution gated by GPT review.
-- Deployment target: public schema (reviewed constant).
-- Rev1 fixes: no COMMENT ON, dependency-fail pre-check, schema declared.
-- Rev2→3 fixes: schema-locked pg_class, safe JSON parse, duplicate key hardening.
CREATE FUNCTION public.fn_birth_onboarding_full_scan()
RETURNS jsonb
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, pg_catalog
AS $$
DECLARE
v_total_checked integer := 0;
v_critical_count integer := 0;
v_warn_count integer := 0;
v_compliant_count integer := 0;
v_gap_collections text[] := ARRAY[]::text[];
v_scanned_at timestamptz := clock_timestamp();
v_run_id text := 'b3f1c-full-scan-'
|| to_char(v_scanned_at AT TIME ZONE 'UTC', 'YYYYMMDD"T"HH24MISSMS"Z"');
-- Dependency resolution variables
v_contract_oids oid[];
v_contract_oid oid;
v_policy_count integer;
v_policy_value_raw text;
v_policy_raw jsonb;
v_policy_entries jsonb;
v_dep_failures text[] := ARRAY[]::text[];
v_sib_fname text;
v_sib_oids oid[];
v_sib_colls_arr text[];
v_sibling_ok_oids oid[] := ARRAY[]::oid[];
v_sibling_ok_colls text[] := ARRAY[]::text[];
v_sib_entry jsonb;
v_sib_colls jsonb;
r record;
BEGIN
-- =====================================================================
-- PHASE 0: DEPENDENCY VALIDATION
-- Before scanning any collection row, validate scanner infrastructure.
-- If any dependency fails, return immediately with status='dependency_fail'.
-- =====================================================================
-- 0a. Contract function: must resolve to exactly one zero-arg trigger function
SELECT array_agg(p.oid)
INTO v_contract_oids
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
AND p.proname = 'fn_birth_registry_auto'
AND p.pronargs = 0
AND p.prorettype = 'pg_catalog.trigger'::regtype;
IF v_contract_oids IS NULL OR array_length(v_contract_oids, 1) IS NULL THEN
v_dep_failures := v_dep_failures || 'CONTRACT_FUNCTION_MISSING: fn_birth_registry_auto not found as zero-arg trigger function in public schema';
ELSIF array_length(v_contract_oids, 1) > 1 THEN
v_dep_failures := v_dep_failures || format('CONTRACT_FUNCTION_AMBIGUOUS: fn_birth_registry_auto resolves to %s candidates (expected exactly 1)', array_length(v_contract_oids, 1));
ELSE
v_contract_oid := v_contract_oids[1];
END IF;
-- 0b. Sibling policy: count keys first (duplicate key hardening — New-3)
SELECT count(*)
INTO v_policy_count
FROM public.dot_config
WHERE key = 'policy.birth_trigger.accepted_sibling_scope';
IF v_policy_count = 0 THEN
v_dep_failures := v_dep_failures || 'SIBLING_POLICY_MISSING: dot_config key policy.birth_trigger.accepted_sibling_scope not found';
ELSIF v_policy_count > 1 THEN
v_dep_failures := v_dep_failures || format('SIBLING_POLICY_DUPLICATE_KEY: dot_config has %s rows for policy.birth_trigger.accepted_sibling_scope (expected exactly 1)', v_policy_count);
ELSE
-- Exactly 1 row. Read raw text value first, then safe-parse as JSON (New-2).
SELECT value
INTO v_policy_value_raw
FROM public.dot_config
WHERE key = 'policy.birth_trigger.accepted_sibling_scope';
IF v_policy_value_raw IS NULL OR length(trim(v_policy_value_raw)) = 0 THEN
v_dep_failures := v_dep_failures || 'SIBLING_POLICY_EMPTY: dot_config key exists but value is NULL or empty';
ELSE
-- Safe JSON parse: catch malformed JSON instead of raising
BEGIN
v_policy_raw := v_policy_value_raw::jsonb;
EXCEPTION WHEN OTHERS THEN
v_dep_failures := v_dep_failures || format('SIBLING_POLICY_MALFORMED_JSON: value cannot be parsed as JSONB: %s', SQLERRM);
v_policy_raw := NULL;
END;
IF v_policy_raw IS NOT NULL THEN
v_policy_entries := v_policy_raw -> 'entries';
IF v_policy_entries IS NULL OR jsonb_typeof(v_policy_entries) <> 'array' THEN
v_dep_failures := v_dep_failures || 'SIBLING_POLICY_MALFORMED: policy parses as JSONB but entries is not a JSON array';
END IF;
END IF;
END IF;
END IF;
-- 0c. Early exit on critical dependencies (contract + policy)
IF array_length(v_dep_failures, 1) IS NOT NULL THEN
SELECT count(*) INTO v_total_checked FROM public.collection_registry;
RETURN jsonb_build_object(
'run_id', v_run_id,
'scanned_at', v_scanned_at,
'status', 'dependency_fail',
'total_registered', v_total_checked,
'total_scanned', 0,
'critical_count', 0,
'warn_count', 0,
'compliant_count', 0,
'gap_collections', '[]'::jsonb,
'dependency_failures', to_jsonb(v_dep_failures)
);
END IF;
-- 0d. Sibling function resolution: each entry must resolve unambiguously
IF v_policy_entries IS NOT NULL AND jsonb_array_length(v_policy_entries) > 0 THEN
FOR v_sib_entry IN SELECT * FROM jsonb_array_elements(v_policy_entries)
LOOP
v_sib_fname := v_sib_entry ->> 'function';
v_sib_colls := COALESCE(v_sib_entry -> 'collections', '[]'::jsonb);
IF v_sib_fname IS NULL OR length(trim(v_sib_fname)) = 0 THEN
v_dep_failures := v_dep_failures || 'SIBLING_ENTRY_NO_FUNCTION: policy entry has no function name';
CONTINUE;
END IF;
SELECT array_agg(p.oid)
INTO v_sib_oids
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
AND p.proname = v_sib_fname
AND p.pronargs = 0
AND p.prorettype = 'pg_catalog.trigger'::regtype;
IF v_sib_oids IS NULL OR array_length(v_sib_oids, 1) IS NULL THEN
IF EXISTS (
SELECT 1 FROM pg_trigger t
JOIN pg_proc p ON p.oid = t.tgfoid
WHERE NOT t.tgisinternal AND p.proname = v_sib_fname
) THEN
v_dep_failures := v_dep_failures || format('SIBLING_FUNCTION_MISSING_BUT_LIVE: %s is referenced by live triggers but does not resolve as zero-arg trigger function', v_sib_fname);
END IF;
ELSIF array_length(v_sib_oids, 1) > 1 THEN
v_dep_failures := v_dep_failures || format('SIBLING_FUNCTION_AMBIGUOUS: %s resolves to %s candidates (expected exactly 1)', v_sib_fname, array_length(v_sib_oids, 1));
ELSE
v_sibling_ok_oids := v_sibling_ok_oids || v_sib_oids[1];
SELECT array_agg(c.coll)
INTO v_sib_colls_arr
FROM jsonb_array_elements_text(v_sib_colls) AS c(coll);
IF v_sib_colls_arr IS NOT NULL THEN
v_sibling_ok_colls := v_sibling_ok_colls || v_sib_colls_arr;
END IF;
END IF;
END LOOP;
END IF;
-- 0e. Check if sibling resolution produced any failures
IF array_length(v_dep_failures, 1) IS NOT NULL THEN
SELECT count(*) INTO v_total_checked FROM public.collection_registry;
RETURN jsonb_build_object(
'run_id', v_run_id,
'scanned_at', v_scanned_at,
'status', 'dependency_fail',
'total_registered', v_total_checked,
'total_scanned', 0,
'critical_count', 0,
'warn_count', 0,
'compliant_count', 0,
'gap_collections', '[]'::jsonb,
'dependency_failures', to_jsonb(v_dep_failures)
);
END IF;
-- 0f. Deduplicate resolved sibling arrays
SELECT array_agg(DISTINCT u) INTO v_sibling_ok_colls
FROM unnest(v_sibling_ok_colls) AS u;
SELECT array_agg(DISTINCT u) INTO v_sibling_ok_oids
FROM unnest(v_sibling_ok_oids) AS u;
-- =====================================================================
-- PHASE 1: COLLECTION SCAN
-- Dependencies validated. Scan each collection_registry row.
-- All pg_class lookups are schema-locked to 'public'::regnamespace (New-1).
-- =====================================================================
SELECT count(*) INTO v_total_checked FROM public.collection_registry;
FOR r IN
WITH
reg AS (
SELECT
cr.id,
cr.code,
cr.collection_name,
cr.coverage_status,
cr.coverage_scope_status,
cr.coverage_exemption_reason,
cr.coverage_review_owner,
EXISTS (
SELECT 1
FROM information_schema.tables t
WHERE t.table_schema = 'public'
AND t.table_name = cr.collection_name
AND t.table_type = 'BASE TABLE'
) AS has_table,
EXISTS (
SELECT 1
FROM public.species_collection_map m
WHERE m.collection_name = cr.collection_name
) AS has_species
FROM public.collection_registry cr
),
trg_eval AS (
-- New-1 fix: pg_class joined with schema-lock via relnamespace = 'public'::regnamespace
-- No separate pg_namespace LEFT JOIN needed.
SELECT
r2.id,
COALESCE(BOOL_OR(
t.tgfoid = v_contract_oid
AND (t.tgtype & 1) = 1
AND (t.tgtype & 2) = 0
AND (t.tgtype & 4) = 4
), false) AS has_contract_air,
COALESCE(BOOL_OR(
t.tgfoid = ANY(v_sibling_ok_oids)
AND (t.tgtype & 1) = 1
AND (t.tgtype & 2) = 0
AND (t.tgtype & 4) = 4
), false) AS has_sibling_air
FROM reg r2
LEFT JOIN pg_class c
ON c.relname = r2.collection_name
AND c.relnamespace = 'public'::regnamespace
LEFT JOIN pg_trigger t
ON t.tgrelid = c.oid
AND NOT t.tgisinternal
AND t.tgenabled <> 'D'
WHERE r2.has_table
GROUP BY r2.id
),
br_recursive AS (
-- New-1 fix: pg_class schema-locked for birth_registry lookup too
SELECT EXISTS (
SELECT 1
FROM pg_class c
JOIN pg_trigger t
ON t.tgrelid = c.oid
AND NOT t.tgisinternal
AND t.tgenabled <> 'D'
WHERE c.relname = 'birth_registry'
AND c.relnamespace = 'public'::regnamespace
AND ( t.tgfoid = v_contract_oid
OR t.tgfoid = ANY(v_sibling_ok_oids) )
AND (t.tgtype & 1) = 1
AND (t.tgtype & 2) = 0
AND (t.tgtype & 4) = 4
) AS has_recursive_risk
),
gaps AS (
-- 1. MISSING_COVERAGE_POLICY
SELECT
r3.code::text AS entity_code,
r3.collection_name::text AS collection_name,
'MISSING_COVERAGE_POLICY'::text AS gap_type,
'critical'::text AS severity,
format('collection_registry row %s (collection_name=%s) has NULL coverage_status.',
r3.code, r3.collection_name) AS description
FROM reg r3
WHERE r3.collection_name <> 'birth_registry'
AND r3.coverage_status IS NULL
UNION ALL
-- 2. MISSING_SCOPE_POLICY
SELECT r3.code::text, r3.collection_name::text, 'MISSING_SCOPE_POLICY', 'warning',
format('collection_registry row %s (collection_name=%s) has coverage_status=%s but NULL coverage_scope_status.',
r3.code, r3.collection_name, r3.coverage_status)
FROM reg r3
WHERE r3.collection_name <> 'birth_registry'
AND r3.coverage_status IS NOT NULL
AND r3.coverage_scope_status IS NULL
UNION ALL
-- 3. UNCLASSIFIED_COVERAGE_POLICY
SELECT r3.code::text, r3.collection_name::text, 'UNCLASSIFIED_COVERAGE_POLICY', 'warning',
format('collection_registry row %s (collection_name=%s) has coverage_status=UNCLASSIFIED_NEW.',
r3.code, r3.collection_name)
FROM reg r3
WHERE r3.collection_name <> 'birth_registry'
AND r3.coverage_status = 'UNCLASSIFIED_NEW'
UNION ALL
-- 4. DEFERRED_WITHOUT_OWNER
SELECT r3.code::text, r3.collection_name::text, 'DEFERRED_WITHOUT_OWNER', 'warning',
format('collection_registry row %s (collection_name=%s) is BIRTH_DEFERRED_NEEDS_REVIEW but coverage_review_owner is empty.',
r3.code, r3.collection_name)
FROM reg r3
WHERE r3.collection_name <> 'birth_registry'
AND r3.coverage_status = 'BIRTH_DEFERRED_NEEDS_REVIEW'
AND (r3.coverage_review_owner IS NULL OR length(trim(r3.coverage_review_owner)) = 0)
UNION ALL
-- 5. SCOPE_MISMATCH
SELECT r3.code::text, r3.collection_name::text, 'SCOPE_MISMATCH', 'warning',
format('collection_registry row %s (collection_name=%s) is BIRTH_REQUIRED but coverage_scope_status=%s.',
r3.code, r3.collection_name, COALESCE(r3.coverage_scope_status, 'NULL'))
FROM reg r3
WHERE r3.collection_name <> 'birth_registry'
AND r3.coverage_status = 'BIRTH_REQUIRED'
AND r3.coverage_scope_status IS DISTINCT FROM 'IN_SCOPE'
UNION ALL
-- 6. EXEMPT_WITHOUT_REASON
SELECT r3.code::text, r3.collection_name::text, 'EXEMPT_WITHOUT_REASON', 'warning',
format('collection_registry row %s (collection_name=%s) has coverage_status=%s but coverage_exemption_reason is empty.',
r3.code, r3.collection_name, r3.coverage_status)
FROM reg r3
WHERE r3.collection_name <> 'birth_registry'
AND r3.coverage_status LIKE 'BIRTH_EXEMPT_%'
AND (r3.coverage_exemption_reason IS NULL OR length(trim(r3.coverage_exemption_reason)) = 0)
UNION ALL
-- 7. MISSING_PHYSICAL_TABLE (BIRTH_REQUIRED ∩ IN_SCOPE)
SELECT r3.code::text, r3.collection_name::text, 'MISSING_PHYSICAL_TABLE', 'critical',
format('collection_registry row %s (collection_name=%s) is BIRTH_REQUIRED ∩ IN_SCOPE but no BASE TABLE exists in public schema.',
r3.code, r3.collection_name)
FROM reg r3
WHERE r3.collection_name <> 'birth_registry'
AND r3.coverage_status = 'BIRTH_REQUIRED'
AND r3.coverage_scope_status = 'IN_SCOPE'
AND NOT r3.has_table
UNION ALL
-- 8. MISSING_SPECIES_MAPPING (BIRTH_REQUIRED ∩ IN_SCOPE)
SELECT r3.code::text, r3.collection_name::text, 'MISSING_SPECIES_MAPPING', 'critical',
format('collection_registry row %s (collection_name=%s) is BIRTH_REQUIRED ∩ IN_SCOPE but no species_collection_map row references it.',
r3.code, r3.collection_name)
FROM reg r3
WHERE r3.collection_name <> 'birth_registry'
AND r3.coverage_status = 'BIRTH_REQUIRED'
AND r3.coverage_scope_status = 'IN_SCOPE'
AND NOT r3.has_species
UNION ALL
-- 9. MISSING_ACCEPTED_BIRTH_TRIGGER (BIRTH_REQUIRED ∩ IN_SCOPE)
SELECT r3.code::text, r3.collection_name::text, 'MISSING_ACCEPTED_BIRTH_TRIGGER', 'critical',
format('collection_registry row %s (collection_name=%s) is BIRTH_REQUIRED ∩ IN_SCOPE but lacks an accepted AFTER INSERT ROW birth trigger (contract or in-policy sibling).',
r3.code, r3.collection_name)
FROM reg r3
LEFT JOIN trg_eval te ON te.id = r3.id
WHERE r3.collection_name <> 'birth_registry'
AND r3.coverage_status = 'BIRTH_REQUIRED'
AND r3.coverage_scope_status = 'IN_SCOPE'
AND (
NOT r3.has_table
OR NOT (
COALESCE(te.has_contract_air, false)
OR (COALESCE(te.has_sibling_air, false) AND r3.collection_name = ANY(v_sibling_ok_colls))
)
)
UNION ALL
-- 10. BIRTH_REGISTRY_RECURSIVE_RISK
SELECT
'birth_registry'::text, 'birth_registry'::text,
'BIRTH_REGISTRY_RECURSIVE_RISK', 'critical',
'birth_registry has an accepted-birth-style AFTER INSERT ROW trigger attached to itself; this would recurse into the birth pipeline.'
FROM br_recursive
WHERE br_recursive.has_recursive_risk
UNION ALL
-- 11. SIBLING_OUT_OF_SCOPE (rev3 fix #1: GLOBAL)
SELECT r3.code::text, r3.collection_name::text, 'SIBLING_OUT_OF_SCOPE', 'critical',
format('collection_registry row %s (collection_name=%s) has an AFTER INSERT ROW sibling-birth trigger but is not in sibling policy scope.',
r3.code, r3.collection_name)
FROM reg r3
JOIN trg_eval te ON te.id = r3.id
WHERE r3.collection_name <> 'birth_registry'
AND r3.has_table
AND te.has_sibling_air
AND NOT (r3.collection_name = ANY(v_sibling_ok_colls))
)
SELECT entity_code, collection_name, gap_type, severity, description
FROM gaps
ORDER BY
CASE severity WHEN 'critical' THEN 0 WHEN 'warning' THEN 1 ELSE 2 END,
gap_type,
collection_name
LOOP
PERFORM public.fn_b3f1_log_collection_onboarding_gap(
r.entity_code,
r.gap_type,
r.severity,
r.description
);
IF r.severity = 'critical' THEN
v_critical_count := v_critical_count + 1;
ELSIF r.severity = 'warning' THEN
v_warn_count := v_warn_count + 1;
END IF;
IF NOT (r.collection_name = ANY(v_gap_collections)) THEN
v_gap_collections := v_gap_collections || r.collection_name;
END IF;
END LOOP;
IF array_length(v_gap_collections, 1) IS NULL THEN
v_compliant_count := v_total_checked;
ELSE
v_compliant_count := GREATEST(v_total_checked - array_length(v_gap_collections, 1), 0);
END IF;
RETURN jsonb_build_object(
'run_id', v_run_id,
'scanned_at', v_scanned_at,
'status', 'complete',
'total_checked', v_total_checked,
'critical_count', v_critical_count,
'warn_count', v_warn_count,
'compliant_count', v_compliant_count,
'gap_collections', to_jsonb(v_gap_collections)
);
END;
$$;
Cumulative fix summary (Rev1 → Rev3)
| Rev | Issue | Fix |
|---|---|---|
| 2 | COMMENT ON FUNCTION | Removed |
| 2 | Dependency failure behavior | PHASE 0 pre-check, status='dependency_fail' return |
| 2 | Schema public declaration | Deployment target header |
| 3 | pg_class schema-lock | c.relnamespace = 'public'::regnamespace in trg_eval + br_recursive |
| 3 | Safe JSON parse | BEGIN...EXCEPTION WHEN OTHERS around ::jsonb cast |
| 3 | Duplicate policy key | COUNT before SELECT INTO: 0=MISSING, >1=DUPLICATE_KEY, 1=parse |
Anti-hardcode guarantees (unchanged)
- Sibling functions and their in-scope collections are read live from
public.dot_config['policy.birth_trigger.accepted_sibling_scope']. - Trigger function OIDs are resolved via strict (
pronargs=0,prorettype=trigger,nspname='public'); ambiguous → dependency failure. - No embedded sibling/collection list, no hardcoded OID, no governed-role list, no snapshot count.
birth_registryexcluded from row-level checks 1–9 and 11; included only for check 10 (recursive risk).
Dependency failure behavior (comprehensive)
Three-layer validation BEFORE entering collection scan:
- Contract function — must resolve to exactly 1 zero-arg trigger function.
- Sibling policy — must exist as exactly 1 row, non-empty, valid JSON, with
entriesarray.- 0 rows → MISSING
-
1 rows → DUPLICATE_KEY
- empty value → EMPTY
- malformed JSON → MALFORMED_JSON (caught, not raised)
- missing/non-array entries → MALFORMED
- Sibling function resolution — each entry unambiguous. Ambiguous = scan-level failure. Missing but not on live triggers = skipped. Missing but on live triggers = failure.
Idempotency notes (unchanged)
Mutation bounded to helper calls. Helper de-duplicates by issue identity.
Execute-time guards (B3-F1c-b precondition checklist)
- No
public.fn_birth_onboarding_full_scaninpg_proc. - Helper + gate still present.
dot_configpolicy key loads as valid JSONB with entries array.- Scheduler decision reviewed separately.
Out-of-scope (unchanged)
No pg_cron, no cron.schedule, no system_health_checks INSERT, no COMMENT ON, no Directus Flow DML.
B3-F1c Full-Scan Function Rev3 | COMPILE-ONLY | 2026-05-13