P3D — B3-F1a Soft Gate Full-Scan Query (rev3)

Mode: COMPILE-ONLY. NOT executed. Rev: 3 (rev3 fixes #1 & #3 applied: SIBLING_OUT_OF_SCOPE global; strict zero-arg trigger function resolution). Date: 2026-05-13. Read-only SELECT. No DDL. No DML.

Purpose

Read-only companion to the soft gate. Emits exactly the same 11 gaps as fn_collection_onboarding_soft_gate() would emit, computed for every row of collection_registry, without any mutation. Use after install to seed an initial backlog (via a separate, explicitly approved INSERT step), or after policy changes to see what the gate would log.

Scope alignment with the function (rev3)

Check	Universe	Severity	Excludes birth_registry?
1. MISSING_COVERAGE_POLICY	all registry rows	critical	yes
2. MISSING_SCOPE_POLICY	rows with non-null coverage_status	warning	yes
3. UNCLASSIFIED_COVERAGE_POLICY	rows where coverage_status='UNCLASSIFIED_NEW'	warning	yes
4. DEFERRED_WITHOUT_OWNER	rows where coverage_status='BIRTH_DEFERRED_NEEDS_REVIEW'	warning	yes
5. SCOPE_MISMATCH	rows where coverage_status='BIRTH_REQUIRED'	warning	yes
6. EXEMPT_WITHOUT_REASON	rows where coverage_status LIKE 'BIRTH_EXEMPT_%'	warning	yes
7. MISSING_PHYSICAL_TABLE	BIRTH_REQUIRED ∩ IN_SCOPE	critical	yes
8. MISSING_SPECIES_MAPPING	BIRTH_REQUIRED ∩ IN_SCOPE	critical	yes
9. MISSING_ACCEPTED_BIRTH_TRIGGER	BIRTH_REQUIRED ∩ IN_SCOPE	critical	yes
10. BIRTH_REGISTRY_RECURSIVE_RISK	only birth_registry	critical	n/a (target row)
11. SIBLING_OUT_OF_SCOPE (rev3)	GLOBAL — every registry row with a physical table	critical	yes

Subsets are derived live (no static governed-role list). birth_registry is included only for check 10.

Function resolution (rev3 — strict)

• Both contract and sibling lookups require: n.nspname='public', p.proname=<name>, p.pronargs=0, p.prorettype='pg_catalog.trigger'::regtype.
• The query collects all matching OIDs into arrays. If contract has 0 matches the recursive-risk and contract-trigger checks return false (no rows). If contract has >1 matches the contract OID expression is replaced by NULL via a guard — runtime equivalent to "ambiguous → block"; for the read-only scan, ambiguity is surfaced via the policy_health row at the end.
• LIMIT 1 is not used anywhere on function resolution.

---

Full-scan SQL

WITH
ctx AS (
  SELECT
    (
      SELECT array_agg(p.oid)
      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
    ) AS contract_oids,
    (
      SELECT NULLIF(value,'')::jsonb
      FROM public.dot_config
      WHERE key='policy.birth_trigger.accepted_sibling_scope'
    ) AS policy
),
ctx_resolved AS (
  SELECT
    CASE
      WHEN ctx.contract_oids IS NULL                         THEN NULL
      WHEN array_length(ctx.contract_oids, 1) = 1            THEN ctx.contract_oids[1]
      ELSE NULL
    END AS contract_oid,
    CASE
      WHEN ctx.contract_oids IS NULL                         THEN 'MISSING'
      WHEN array_length(ctx.contract_oids, 1) = 1            THEN 'OK'
      ELSE 'AMBIGUOUS'
    END AS contract_status,
    ctx.policy
  FROM ctx
),
sibling_entries AS (
  SELECT
    (e.value ->> 'function')                          AS fname,
    COALESCE(e.value -> 'collections', '[]'::jsonb)   AS colls_json
  FROM ctx_resolved
  LEFT JOIN LATERAL jsonb_array_elements(
    COALESCE(ctx_resolved.policy -> 'entries', '[]'::jsonb)
  ) e ON true
  WHERE e.value IS NOT NULL
),
sibling_resolved AS (
  -- one row per entry: resolved oid OR null + status MISSING / AMBIGUOUS / OK
  SELECT
    se.fname,
    se.colls_json,
    (
      SELECT array_agg(p.oid)
      FROM pg_proc p
      JOIN pg_namespace n ON n.oid = p.pronamespace
      WHERE n.nspname='public'
        AND p.proname = se.fname
        AND p.pronargs = 0
        AND p.prorettype = 'pg_catalog.trigger'::regtype
    ) AS oids
  FROM sibling_entries se
),
sibling_with_status AS (
  SELECT
    sr.fname,
    sr.colls_json,
    sr.oids,
    CASE
      WHEN sr.oids IS NULL OR array_length(sr.oids, 1) IS NULL THEN
        CASE WHEN EXISTS (
          SELECT 1 FROM pg_trigger t
          JOIN pg_proc p ON p.oid = t.tgfoid
          WHERE NOT t.tgisinternal AND p.proname = sr.fname
        ) THEN 'MISSING_BLOCK' ELSE 'MISSING_SKIP' END
      WHEN array_length(sr.oids, 1) > 1 THEN 'AMBIGUOUS'
      ELSE 'OK'
    END AS status
  FROM sibling_resolved sr
),
sibling_oids_cte AS (
  SELECT COALESCE(
    (SELECT array_agg(DISTINCT u.oid)
     FROM sibling_with_status sws,
          LATERAL unnest(COALESCE(sws.oids, ARRAY[]::oid[])) AS u(oid)
     WHERE sws.status = 'OK'),
    ARRAY[]::oid[]
  ) AS oids
),
sibling_collections_cte AS (
  SELECT COALESCE(
    (SELECT array_agg(DISTINCT c.coll)
     FROM sibling_with_status sws,
          LATERAL jsonb_array_elements_text(sws.colls_json) AS c(coll)
     WHERE sws.status = 'OK'),
    ARRAY[]::text[]
  ) AS colls
),
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 (
  SELECT
    r.id,
    COALESCE(BOOL_OR(
      cr.contract_oid IS NOT NULL
      AND t.tgfoid = cr.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(
      array_length(so.oids, 1) IS NOT NULL
      AND t.tgfoid = ANY(so.oids)
      AND (t.tgtype & 1) = 1
      AND (t.tgtype & 2) = 0
      AND (t.tgtype & 4) = 4
    ), false) AS has_sibling_air
  FROM reg r
  CROSS JOIN ctx_resolved cr
  CROSS JOIN sibling_oids_cte so
  LEFT JOIN pg_class     c ON c.relname = r.collection_name
  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace AND n.nspname = 'public'
  LEFT JOIN pg_trigger   t ON t.tgrelid = c.oid AND NOT t.tgisinternal AND t.tgenabled <> 'D'
  WHERE r.has_table
  GROUP BY r.id
),
br_recursive AS (
  SELECT EXISTS (
    SELECT 1
    FROM pg_trigger t
    JOIN pg_class     c ON c.oid = t.tgrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    CROSS JOIN ctx_resolved cr
    CROSS JOIN sibling_oids_cte so
    WHERE n.nspname='public'
      AND c.relname='birth_registry'
      AND NOT t.tgisinternal
      AND t.tgenabled <> 'D'
      AND ( (cr.contract_oid IS NOT NULL AND t.tgfoid = cr.contract_oid)
            OR (array_length(so.oids, 1) IS NOT NULL AND t.tgfoid = ANY(so.oids)) )
      AND (t.tgtype & 1) = 1
      AND (t.tgtype & 2) = 0
      AND (t.tgtype & 4) = 4
  ) AS has_recursive_risk
)
SELECT * FROM (
  -- 1. MISSING_COVERAGE_POLICY
  SELECT
    r.code AS entity_code,
    r.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.',
           r.code, r.collection_name) AS description
  FROM reg r
  WHERE r.collection_name <> 'birth_registry'
    AND r.coverage_status IS NULL

  UNION ALL

  -- 2. MISSING_SCOPE_POLICY
  SELECT r.code, r.collection_name, 'MISSING_SCOPE_POLICY', 'warning',
         format('collection_registry row %s (collection_name=%s) has coverage_status=%s but NULL coverage_scope_status.',
                r.code, r.collection_name, r.coverage_status)
  FROM reg r
  WHERE r.collection_name <> 'birth_registry'
    AND r.coverage_status IS NOT NULL
    AND r.coverage_scope_status IS NULL

  UNION ALL

  -- 3. UNCLASSIFIED_COVERAGE_POLICY
  SELECT r.code, r.collection_name, 'UNCLASSIFIED_COVERAGE_POLICY', 'warning',
         format('collection_registry row %s (collection_name=%s) has coverage_status=UNCLASSIFIED_NEW.',
                r.code, r.collection_name)
  FROM reg r
  WHERE r.collection_name <> 'birth_registry'
    AND r.coverage_status = 'UNCLASSIFIED_NEW'

  UNION ALL

  -- 4. DEFERRED_WITHOUT_OWNER
  SELECT r.code, r.collection_name, 'DEFERRED_WITHOUT_OWNER', 'warning',
         format('collection_registry row %s (collection_name=%s) is BIRTH_DEFERRED_NEEDS_REVIEW but coverage_review_owner is empty.',
                r.code, r.collection_name)
  FROM reg r
  WHERE r.collection_name <> 'birth_registry'
    AND r.coverage_status = 'BIRTH_DEFERRED_NEEDS_REVIEW'
    AND (r.coverage_review_owner IS NULL OR length(trim(r.coverage_review_owner)) = 0)

  UNION ALL

  -- 5. SCOPE_MISMATCH
  SELECT r.code, r.collection_name, 'SCOPE_MISMATCH', 'warning',
         format('collection_registry row %s (collection_name=%s) is BIRTH_REQUIRED but coverage_scope_status=%s.',
                r.code, r.collection_name, COALESCE(r.coverage_scope_status,'NULL'))
  FROM reg r
  WHERE r.collection_name <> 'birth_registry'
    AND r.coverage_status = 'BIRTH_REQUIRED'
    AND r.coverage_scope_status IS DISTINCT FROM 'IN_SCOPE'

  UNION ALL

  -- 6. EXEMPT_WITHOUT_REASON
  SELECT r.code, r.collection_name, 'EXEMPT_WITHOUT_REASON', 'warning',
         format('collection_registry row %s (collection_name=%s) has coverage_status=%s but coverage_exemption_reason is empty.',
                r.code, r.collection_name, r.coverage_status)
  FROM reg r
  WHERE r.collection_name <> 'birth_registry'
    AND r.coverage_status LIKE 'BIRTH_EXEMPT_%'
    AND (r.coverage_exemption_reason IS NULL OR length(trim(r.coverage_exemption_reason)) = 0)

  UNION ALL

  -- 7. MISSING_PHYSICAL_TABLE  (BIRTH_REQUIRED ∩ IN_SCOPE)
  SELECT r.code, r.collection_name, '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.',
                r.code, r.collection_name)
  FROM reg r
  WHERE r.collection_name <> 'birth_registry'
    AND r.coverage_status='BIRTH_REQUIRED'
    AND r.coverage_scope_status='IN_SCOPE'
    AND NOT r.has_table

  UNION ALL

  -- 8. MISSING_SPECIES_MAPPING  (BIRTH_REQUIRED ∩ IN_SCOPE)
  SELECT r.code, r.collection_name, '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.',
                r.code, r.collection_name)
  FROM reg r
  WHERE r.collection_name <> 'birth_registry'
    AND r.coverage_status='BIRTH_REQUIRED'
    AND r.coverage_scope_status='IN_SCOPE'
    AND NOT r.has_species

  UNION ALL

  -- 9. MISSING_ACCEPTED_BIRTH_TRIGGER  (BIRTH_REQUIRED ∩ IN_SCOPE)
  SELECT r.code, r.collection_name, '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).',
                r.code, r.collection_name)
  FROM reg r
  LEFT JOIN trg_eval te                  ON te.id = r.id
  CROSS JOIN sibling_collections_cte sc
  WHERE r.collection_name <> 'birth_registry'
    AND r.coverage_status='BIRTH_REQUIRED'
    AND r.coverage_scope_status='IN_SCOPE'
    AND (
      NOT r.has_table
      OR NOT (
        COALESCE(te.has_contract_air, false)
        OR (COALESCE(te.has_sibling_air, false) AND r.collection_name = ANY(sc.colls))
      )
    )

  UNION ALL

  -- 10. BIRTH_REGISTRY_RECURSIVE_RISK  (target row only)
  SELECT
    'birth_registry'::varchar(255)              AS entity_code,
    'birth_registry'::varchar(255)              AS collection_name,
    'BIRTH_REGISTRY_RECURSIVE_RISK'::text       AS gap_type,
    'critical'::text                            AS severity,
    'birth_registry has an accepted-birth-style AFTER INSERT ROW trigger attached to itself; this would recurse into the birth pipeline.'::text AS description
  FROM br_recursive
  WHERE br_recursive.has_recursive_risk

  UNION ALL

  -- 11. SIBLING_OUT_OF_SCOPE  (rev3 fix #1: GLOBAL — any non-birth_registry row with physical table)
  SELECT r.code, r.collection_name, '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.',
                r.code, r.collection_name)
  FROM reg r
  JOIN trg_eval te                       ON te.id = r.id
  CROSS JOIN sibling_collections_cte sc
  WHERE r.collection_name <> 'birth_registry'
    AND r.has_table
    AND te.has_sibling_air
    AND NOT (r.collection_name = ANY(sc.colls))
) gaps
ORDER BY
  CASE severity WHEN 'critical' THEN 0 WHEN 'warning' THEN 1 ELSE 2 END,
  gap_type,
  collection_name;

Out-of-band: function resolution health

The strict-resolution status of contract and sibling functions is itself an operational concern. A separate SELECT (read-only) surfaces it for the reviewer:

WITH
ctx AS (
  SELECT
    (
      SELECT array_agg(p.oid)
      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
    ) AS contract_oids,
    (
      SELECT NULLIF(value,'')::jsonb
      FROM public.dot_config
      WHERE key='policy.birth_trigger.accepted_sibling_scope'
    ) AS policy
)
SELECT
  'contract'::text AS kind,
  'fn_birth_registry_auto'::text AS fname,
  CASE
    WHEN ctx.contract_oids IS NULL                          THEN 'MISSING'
    WHEN array_length(ctx.contract_oids, 1) = 1             THEN 'OK'
    ELSE 'AMBIGUOUS'
  END AS status
FROM ctx

UNION ALL

SELECT
  'sibling'::text AS kind,
  (e.value ->> 'function')::text AS fname,
  CASE
    WHEN sr.oids IS NULL OR array_length(sr.oids, 1) IS NULL THEN
      CASE WHEN EXISTS (
        SELECT 1 FROM pg_trigger t
        JOIN pg_proc p ON p.oid = t.tgfoid
        WHERE NOT t.tgisinternal AND p.proname = (e.value ->> 'function')
      ) THEN 'MISSING_BLOCK' ELSE 'MISSING_SKIP' END
    WHEN array_length(sr.oids, 1) > 1 THEN 'AMBIGUOUS'
    ELSE 'OK'
  END AS status
FROM ctx,
LATERAL jsonb_array_elements(COALESCE(ctx.policy -> 'entries', '[]'::jsonb)) AS e,
LATERAL (
  SELECT array_agg(p.oid) AS oids
  FROM pg_proc p
  JOIN pg_namespace n ON n.oid = p.pronamespace
  WHERE n.nspname='public'
    AND p.proname = (e.value ->> 'function')
    AND p.pronargs = 0
    AND p.prorettype = 'pg_catalog.trigger'::regtype
) sr;

Read-only verification helpers

Count by severity (wrap the full-scan query above as q):

-- SELECT severity, count(*) FROM (<full-scan>) q GROUP BY severity ORDER BY 1;

Count by gap_type:

-- SELECT gap_type, severity, count(*) FROM (<full-scan>) q GROUP BY gap_type, severity ORDER BY 2,1;

Notes for GPT reviewer (rev3)

1. Same 11 checks, severity matrix, and BIRTH_REQUIRED ∩ IN_SCOPE scoping for checks 7–9 as the gate function.
2. rev3 fix #1: SIBLING_OUT_OF_SCOPE is GLOBAL — runs against every non-birth_registry registry row that has a physical table, regardless of coverage_status / coverage_scope_status.
3. rev3 fix #3: Strict zero-arg trigger function resolution for contract and sibling functions (pronargs=0 + prorettype='pg_catalog.trigger'::regtype); ambiguous (>1) and missing (=0) cases never silently pick one — surfaced via the function-resolution-health SELECT.
4. Sibling policy/OIDs/collections resolved live from dot_config['policy.birth_trigger.accepted_sibling_scope']; supports multiple entries; no embedded list.
5. Trigger detection uses tgfoid and tgtype bits (ROW, AFTER, INSERT); excludes disabled triggers (tgenabled='D'); NOT t.tgisinternal. No MAX(proname).
6. Query is read-only SELECT — no INSERT/UPDATE/DELETE/DDL.