P3D — B3-F1a Soft Gate Compiled Function (rev3)

Mode: COMPILE-ONLY. NOT executed. To be reviewed by GPT before B3-F1b execution. Rev: 3 (4 GPT rev2 blockers fixed: SIBLING_OUT_OF_SCOPE global, COMMENT ON removed from executable SQL, strict zero-arg trigger function resolution, helper EXCEPTION removed). Date: 2026-05-13. Live re-probe: PG 16, db directus, schema public. Did not rely on rev1/rev2 probe results.

Live re-probe results (2026-05-13)

Item	Result
Schema	public
system_issues shape	28 cols, severity check `(critical
system_health_checks	present (13 cols)
collection_registry	35 cols, 166 rows, coverage_status ∈ {BIRTH_REQUIRED, BIRTH_EXEMPT_*, BIRTH_DEFERRED_NEEDS_REVIEW, UNCLASSIFIED_NEW}, coverage_scope_status ∈ {IN_SCOPE, USER_EXCLUDED, FUTURE_SCOPE, ORPHAN_REGISTRY}, 159 with physical table
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) → RESOLVED
Sibling fn_birth_registry_auto_id strict resolution	1 match (oid 66750, pronargs=0, prorettype=trigger) → RESOLVED
Sibling live trigger usage	exactly the 3 collections in the policy entry; no excess; no rogue usage on out-of-policy tables
birth_registry table 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
fn_guard_system_issues_insert()	present (zero-arg) — accepts inserts only with permitted source

Conflict status (live, 2026-05-13)

object	exists?	action for B3-F1b
public.fn_collection_onboarding_soft_gate()	NO	safe to CREATE FUNCTION
public.fn_b3f1_log_collection_onboarding_gap(text,text,text,text)	NO	safe to CREATE FUNCTION
public.trg_collection_onboarding_soft_gate ON collection_registry	NO	safe to CREATE TRIGGER

If any of the above exists at B3-F1b time → BLOCKED_EXISTING_CONFLICT (no silent replace).

Severity matrix

Gap	Severity
MISSING_COVERAGE_POLICY	critical
MISSING_PHYSICAL_TABLE	critical
MISSING_SPECIES_MAPPING	critical
MISSING_ACCEPTED_BIRTH_TRIGGER	critical
SIBLING_OUT_OF_SCOPE	critical
BIRTH_REGISTRY_RECURSIVE_RISK	critical
MISSING_SCOPE_POLICY	warning
UNCLASSIFIED_COVERAGE_POLICY	warning
DEFERRED_WITHOUT_OWNER	warning
SCOPE_MISMATCH	warning
EXEMPT_WITHOUT_REASON	warning

Scope (rev3)

• GLOBAL (every collection_registry row except birth_registry):
  • SIBLING_OUT_OF_SCOPE (rev3 fix #1 — was previously nested in BIRTH_REQUIRED ∩ IN_SCOPE)
• Coverage/scope/exemption policy checks (1–6) apply to every row except birth_registry.
• BIRTH_REQUIRED ∩ IN_SCOPE only:
  • MISSING_PHYSICAL_TABLE
  • MISSING_SPECIES_MAPPING
  • MISSING_ACCEPTED_BIRTH_TRIGGER
• birth_registry row branch runs only the BIRTH_REGISTRY_RECURSIVE_RISK check (10), then returns; it skips all other onboarding checks.

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
• 0 match → contract: BLOCK (compile and runtime); sibling entry: skip if no live trigger usage, else outer-EXCEPTION-swallow.
• 1 match → resolved.

• 1 match → ambiguity; never silently picks one. Runtime raises (caught by outer EXCEPTION); compile-time would have already BLOCKED.

• LIMIT 1 is not used anywhere on function resolution.

Trigger detection (rev3)

• tgfoid matched against resolved OIDs.
• (tgtype & 1) = 1 — ROW.
• (tgtype & 2) = 0 — AFTER (BEFORE bit clear).
• (tgtype & 4) = 4 — INSERT.
• tgenabled <> 'D' — exclude disabled.
• NOT t.tgisinternal — exclude internal.

Helper exception (rev3 — fix #4)

• Helper has no EXCEPTION block. If system_issues insert fails (e.g. guard rejects, dedup race, constraint), helper raises normally.
• Outer gate function alone catches: EXCEPTION WHEN OTHERS THEN RETURN NEW.
• Result: single exception boundary; failures are not silently lost inside the helper.

COMMENT ON (rev3 — fix #2)

• All COMMENT ON FUNCTION and COMMENT ON TRIGGER statements are removed from this executable artifact.
• B3-F1b execution boundary covers only CREATE FUNCTION + CREATE TRIGGER. Optional metadata comments must be packaged separately if desired (not included here).

---

Helper — fn_b3f1_log_collection_onboarding_gap (CREATE FUNCTION only)

CREATE FUNCTION public.fn_b3f1_log_collection_onboarding_gap(
  p_entity_ref text,
  p_gap_type   text,
  p_severity   text,
  p_description text
) RETURNS void
LANGUAGE plpgsql
AS $body$
BEGIN
  IF EXISTS (
    SELECT 1
    FROM public.system_issues
    WHERE entity_type = 'collection_registry'
      AND entity_code = p_entity_ref
      AND issue_type  = 'collection_onboarding_gap'
      AND sub_class   = p_gap_type
      AND status      = 'open'
  ) THEN
    RETURN;
  END IF;

  INSERT INTO public.system_issues (
    entity_type,
    entity_code,
    issue_type,
    severity,
    status,
    title,
    description,
    source,
    sub_class,
    coalesce_key,
    detected_at
  )
  VALUES (
    'collection_registry',
    p_entity_ref,
    'collection_onboarding_gap',
    p_severity,
    'open',
    format('[%s] %s on %s', p_gap_type, p_severity, p_entity_ref),
    p_description,
    'dot-soft-gate-collection-onboarding',
    p_gap_type,
    'collection_registry:' || p_entity_ref || ':' || p_gap_type,
    CURRENT_TIMESTAMP
  );
END;
$body$;

---

Gate function — fn_collection_onboarding_soft_gate (CREATE FUNCTION only)

CREATE FUNCTION public.fn_collection_onboarding_soft_gate()
RETURNS trigger
LANGUAGE plpgsql
AS $body$
DECLARE
  v_row                          public.collection_registry%ROWTYPE;
  v_contract_oids                oid[]  := ARRAY[]::oid[];
  v_contract_oid                 oid;
  v_policy                       jsonb;
  v_entry                        jsonb;
  v_fname                        text;
  v_fn_oids                      oid[];
  v_sibling_oids                 oid[]  := ARRAY[]::oid[];
  v_sibling_collections          text[] := ARRAY[]::text[];
  v_entry_collections            text[];
  v_has_recursive_risk           boolean;
  v_table_exists                 boolean;
  v_species_exists               boolean;
  v_has_contract_air             boolean := false;
  v_has_sibling_air              boolean := false;
  v_has_out_of_scope_sibling     boolean := false;
  v_has_accepted_birth_trigger   boolean := false;
  v_entry_has_live_usage         boolean;
BEGIN
  -- Re-entrancy guard: soft gate fires only at top of trigger stack
  IF pg_trigger_depth() > 1 THEN
    RETURN NEW;
  END IF;

  v_row := NEW;

  ----------------------------------------------------------------
  -- Strict contract OID resolution (rev3)
  --   schema=public, pronargs=0, prorettype=trigger
  --   no LIMIT 1; ambiguity raises (outer EXCEPTION catches)
  ----------------------------------------------------------------
  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) = 0 THEN
    RAISE EXCEPTION
      'b3f1a soft gate: contract function public.fn_birth_registry_auto() not resolvable as zero-arg trigger function';
  ELSIF array_length(v_contract_oids, 1) > 1 THEN
    RAISE EXCEPTION
      'b3f1a soft gate: contract function public.fn_birth_registry_auto() ambiguous (% candidates)',
      array_length(v_contract_oids, 1);
  ELSE
    v_contract_oid := v_contract_oids[1];
  END IF;

  ----------------------------------------------------------------
  -- Strict sibling policy resolution (rev3)
  --   read entries from dot_config; for each entry resolve function
  --   strictly; ambiguity raises; missing function with live usage raises;
  --   missing function with no live usage is skipped silently.
  --   Runtime supports multiple entries.
  ----------------------------------------------------------------
  SELECT NULLIF(value, '')::jsonb
    INTO v_policy
  FROM public.dot_config
  WHERE key = 'policy.birth_trigger.accepted_sibling_scope';

  IF v_policy IS NOT NULL THEN
    FOR v_entry IN
      SELECT * FROM jsonb_array_elements(COALESCE(v_policy -> 'entries', '[]'::jsonb))
    LOOP
      v_fname := v_entry ->> 'function';
      v_entry_collections := ARRAY(
        SELECT jsonb_array_elements_text(COALESCE(v_entry -> 'collections', '[]'::jsonb))
      );

      IF v_fname IS NULL OR length(trim(v_fname)) = 0 THEN
        CONTINUE;
      END IF;

      SELECT array_agg(p.oid)
        INTO v_fn_oids
      FROM pg_proc p
      JOIN pg_namespace n ON n.oid = p.pronamespace
      WHERE n.nspname  = 'public'
        AND p.proname  = v_fname
        AND p.pronargs = 0
        AND p.prorettype = 'pg_catalog.trigger'::regtype;

      IF v_fn_oids IS NULL OR array_length(v_fn_oids, 1) = 0 THEN
        -- Function missing. If any live trigger anywhere uses a function
        -- of this name → block (outer EXCEPTION will swallow).
        SELECT EXISTS (
          SELECT 1
          FROM pg_trigger t
          JOIN pg_proc      p ON p.oid = t.tgfoid
          JOIN pg_namespace n ON n.oid = p.pronamespace
          WHERE NOT t.tgisinternal
            AND p.proname = v_fname
        ) INTO v_entry_has_live_usage;

        IF v_entry_has_live_usage THEN
          RAISE EXCEPTION
            'b3f1a soft gate: sibling policy function %.%() not resolvable as zero-arg trigger function but live trigger usage exists',
            'public', v_fname;
        ELSE
          -- silent skip of this entry
          CONTINUE;
        END IF;
      ELSIF array_length(v_fn_oids, 1) > 1 THEN
        RAISE EXCEPTION
          'b3f1a soft gate: sibling policy function %.%() ambiguous (% candidates)',
          'public', v_fname, array_length(v_fn_oids, 1);
      END IF;

      v_sibling_oids := v_sibling_oids || v_fn_oids;
      IF v_entry_collections IS NOT NULL THEN
        v_sibling_collections := v_sibling_collections || v_entry_collections;
      END IF;
    END LOOP;
  END IF;

  ----------------------------------------------------------------
  -- Check 10: BIRTH_REGISTRY_RECURSIVE_RISK (target row only)
  --   birth_registry path: check, log if found, RETURN NEW.
  ----------------------------------------------------------------
  IF v_row.collection_name = 'birth_registry' THEN
    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
      WHERE n.nspname = 'public'
        AND c.relname = 'birth_registry'
        AND NOT t.tgisinternal
        AND t.tgenabled <> 'D'
        AND ( t.tgfoid = v_contract_oid
              OR (array_length(v_sibling_oids, 1) IS NOT NULL AND t.tgfoid = ANY(v_sibling_oids)) )
        AND (t.tgtype & 1) = 1
        AND (t.tgtype & 2) = 0
        AND (t.tgtype & 4) = 4
    ) INTO v_has_recursive_risk;

    IF COALESCE(v_has_recursive_risk, false) THEN
      PERFORM public.fn_b3f1_log_collection_onboarding_gap(
        v_row.code,
        '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.'
      );
    END IF;

    RETURN NEW;
  END IF;

  ----------------------------------------------------------------
  -- GLOBAL Check 11: SIBLING_OUT_OF_SCOPE (rev3 fix #1)
  --   Applies to EVERY non-birth_registry row that has a physical table
  --   in public schema, regardless of coverage_status / coverage_scope_status.
  --   Detects an AFTER INSERT ROW sibling-OID trigger on a table that is
  --   NOT in the sibling policy collection list.
  ----------------------------------------------------------------
  SELECT EXISTS (
    SELECT 1
    FROM information_schema.tables
    WHERE table_schema = 'public'
      AND table_name   = v_row.collection_name
      AND table_type   = 'BASE TABLE'
  ) INTO v_table_exists;

  IF v_table_exists THEN
    SELECT
      COALESCE(BOOL_OR(
        array_length(v_sibling_oids, 1) IS NOT NULL
        AND t.tgfoid = ANY(v_sibling_oids)
        AND (t.tgtype & 1) = 1
        AND (t.tgtype & 2) = 0
        AND (t.tgtype & 4) = 4
      ), false)
    INTO v_has_sibling_air
    FROM pg_trigger t
    JOIN pg_class     c ON c.oid = t.tgrelid
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public'
      AND c.relname = v_row.collection_name
      AND NOT t.tgisinternal
      AND t.tgenabled <> 'D';

    v_has_out_of_scope_sibling :=
      v_has_sibling_air
      AND NOT (v_row.collection_name = ANY(v_sibling_collections));

    IF v_has_out_of_scope_sibling THEN
      PERFORM public.fn_b3f1_log_collection_onboarding_gap(
        v_row.code,
        '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.',
               v_row.code, v_row.collection_name)
      );
    END IF;
  END IF;

  ----------------------------------------------------------------
  -- Check 1: MISSING_COVERAGE_POLICY
  ----------------------------------------------------------------
  IF v_row.coverage_status IS NULL THEN
    PERFORM public.fn_b3f1_log_collection_onboarding_gap(
      v_row.code, 'MISSING_COVERAGE_POLICY', 'critical',
      format('collection_registry row %s (collection_name=%s) has NULL coverage_status.',
             v_row.code, v_row.collection_name)
    );
    -- cannot evaluate downstream policy checks without coverage_status
    RETURN NEW;
  END IF;

  ----------------------------------------------------------------
  -- Check 3: UNCLASSIFIED_COVERAGE_POLICY
  ----------------------------------------------------------------
  IF v_row.coverage_status = 'UNCLASSIFIED_NEW' THEN
    PERFORM public.fn_b3f1_log_collection_onboarding_gap(
      v_row.code, 'UNCLASSIFIED_COVERAGE_POLICY', 'warning',
      format('collection_registry row %s (collection_name=%s) has coverage_status=UNCLASSIFIED_NEW.',
             v_row.code, v_row.collection_name)
    );
  END IF;

  ----------------------------------------------------------------
  -- Check 2: MISSING_SCOPE_POLICY
  ----------------------------------------------------------------
  IF v_row.coverage_scope_status IS NULL THEN
    PERFORM public.fn_b3f1_log_collection_onboarding_gap(
      v_row.code, 'MISSING_SCOPE_POLICY', 'warning',
      format('collection_registry row %s (collection_name=%s) has coverage_status=%s but NULL coverage_scope_status.',
             v_row.code, v_row.collection_name, v_row.coverage_status)
    );
  END IF;

  ----------------------------------------------------------------
  -- Check 4: DEFERRED_WITHOUT_OWNER
  ----------------------------------------------------------------
  IF v_row.coverage_status = 'BIRTH_DEFERRED_NEEDS_REVIEW'
     AND (v_row.coverage_review_owner IS NULL OR length(trim(v_row.coverage_review_owner)) = 0) THEN
    PERFORM public.fn_b3f1_log_collection_onboarding_gap(
      v_row.code, 'DEFERRED_WITHOUT_OWNER', 'warning',
      format('collection_registry row %s (collection_name=%s) is BIRTH_DEFERRED_NEEDS_REVIEW but coverage_review_owner is empty.',
             v_row.code, v_row.collection_name)
    );
  END IF;

  ----------------------------------------------------------------
  -- Check 5: SCOPE_MISMATCH
  ----------------------------------------------------------------
  IF v_row.coverage_status = 'BIRTH_REQUIRED'
     AND v_row.coverage_scope_status IS DISTINCT FROM 'IN_SCOPE' THEN
    PERFORM public.fn_b3f1_log_collection_onboarding_gap(
      v_row.code, 'SCOPE_MISMATCH', 'warning',
      format('collection_registry row %s (collection_name=%s) is BIRTH_REQUIRED but coverage_scope_status=%s.',
             v_row.code, v_row.collection_name, COALESCE(v_row.coverage_scope_status, 'NULL'))
    );
  END IF;

  ----------------------------------------------------------------
  -- Check 6: EXEMPT_WITHOUT_REASON
  ----------------------------------------------------------------
  IF v_row.coverage_status LIKE 'BIRTH_EXEMPT_%'
     AND (v_row.coverage_exemption_reason IS NULL OR length(trim(v_row.coverage_exemption_reason)) = 0) THEN
    PERFORM public.fn_b3f1_log_collection_onboarding_gap(
      v_row.code, 'EXEMPT_WITHOUT_REASON', 'warning',
      format('collection_registry row %s (collection_name=%s) has coverage_status=%s but coverage_exemption_reason is empty.',
             v_row.code, v_row.collection_name, v_row.coverage_status)
    );
  END IF;

  ----------------------------------------------------------------
  -- Checks 7, 8, 9: scoped to BIRTH_REQUIRED ∩ IN_SCOPE only
  ----------------------------------------------------------------
  IF v_row.coverage_status = 'BIRTH_REQUIRED'
     AND v_row.coverage_scope_status = 'IN_SCOPE' THEN

    -- 7. MISSING_PHYSICAL_TABLE
    IF NOT v_table_exists THEN
      PERFORM public.fn_b3f1_log_collection_onboarding_gap(
        v_row.code, '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.',
               v_row.code, v_row.collection_name)
      );
    END IF;

    -- 8. MISSING_SPECIES_MAPPING
    SELECT EXISTS (
      SELECT 1
      FROM public.species_collection_map m
      WHERE m.collection_name = v_row.collection_name
    ) INTO v_species_exists;

    IF NOT v_species_exists THEN
      PERFORM public.fn_b3f1_log_collection_onboarding_gap(
        v_row.code, '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.',
               v_row.code, v_row.collection_name)
      );
    END IF;

    -- 9. MISSING_ACCEPTED_BIRTH_TRIGGER
    IF v_table_exists THEN
      SELECT
        COALESCE(BOOL_OR(
          t.tgfoid = v_contract_oid
          AND (t.tgtype & 1) = 1
          AND (t.tgtype & 2) = 0
          AND (t.tgtype & 4) = 4
        ), false)
      INTO v_has_contract_air
      FROM pg_trigger t
      JOIN pg_class     c ON c.oid = t.tgrelid
      JOIN pg_namespace n ON n.oid = c.relnamespace
      WHERE n.nspname = 'public'
        AND c.relname = v_row.collection_name
        AND NOT t.tgisinternal
        AND t.tgenabled <> 'D';

      v_has_accepted_birth_trigger :=
        v_has_contract_air
        OR (v_has_sibling_air AND v_row.collection_name = ANY(v_sibling_collections));

      IF NOT v_has_accepted_birth_trigger THEN
        PERFORM public.fn_b3f1_log_collection_onboarding_gap(
          v_row.code, 'MISSING_ACCEPTED_BIRTH_TRIGGER', 'critical',
          format('collection_registry row %s (collection_name=%s) is BIRTH_REQUIRED ∩ IN_SCOPE but has no accepted AFTER INSERT ROW birth trigger (contract or in-policy sibling).',
                 v_row.code, v_row.collection_name)
        );
      END IF;
    ELSE
      -- No physical table → no trigger possible → emit MISSING_ACCEPTED_BIRTH_TRIGGER
      PERFORM public.fn_b3f1_log_collection_onboarding_gap(
        v_row.code, 'MISSING_ACCEPTED_BIRTH_TRIGGER', 'critical',
        format('collection_registry row %s (collection_name=%s) is BIRTH_REQUIRED ∩ IN_SCOPE but no physical table exists; no birth trigger possible.',
               v_row.code, v_row.collection_name)
      );
    END IF;

  END IF;

  RETURN NEW;
EXCEPTION WHEN OTHERS THEN
  -- Soft gate must NEVER raise. Single exception boundary.
  RETURN NEW;
END;
$body$;

---

Notes for GPT reviewer (rev3)

1. CREATE FUNCTION (no OR REPLACE) on both helper and gate.
2. Helper is task-specific (fn_b3f1_log_collection_onboarding_gap).
3. Severity is gap-specific via p_severity parameter.
4. rev3 fix #1: SIBLING_OUT_OF_SCOPE evaluated GLOBALLY for any non-birth_registry row with a physical table. It runs before the BIRTH_REQUIRED ∩ IN_SCOPE block. A DEFERRED, EXEMPT, or out-of-scope row with a rogue sibling AIR trigger is still flagged.
5. rev3 fix #2: All COMMENT ON FUNCTION and COMMENT ON TRIGGER statements are removed from this executable artifact.
6. rev3 fix #3: Strict zero-arg trigger function resolution for both contract and sibling. Uses array_agg(p.oid) (not LIMIT 1); explicitly raises on ambiguity (>1) and on contract-missing (=0); for a sibling-policy entry, missing function with live usage raises, missing function with no live usage is silently skipped.
7. rev3 fix #4: Helper has no EXCEPTION block. The outer gate function EXCEPTION WHEN OTHERS THEN RETURN NEW is the single non-blocking boundary.
8. Trigger detection uses tgfoid and tgtype bits (tgtype & 1)=1 (ROW), (tgtype & 2)=0 (AFTER), (tgtype & 4)=4 (INSERT). Disabled triggers (tgenabled='D') excluded. NOT t.tgisinternal. No MAX(proname).
9. Sibling scope is read live from dot_config['policy.birth_trigger.accepted_sibling_scope']. Runtime supports multiple entries. No sibling collection list is embedded in code.
10. Re-entrancy guarded via pg_trigger_depth() > 1.
11. coalesce_key set to leverage idx_system_issues_coalesce. source = 'dot-soft-gate-collection-onboarding' (compatible with fn_guard_system_issues_insert).
12. Ordering of checks within the function: recursive risk (birth_registry only) → global SIBLING_OUT_OF_SCOPE → policy checks 1–6 → BIRTH_REQUIRED∩IN_SCOPE checks 7–9.