P3D — B3-F1c-b Rollback SQL Rev2 — Function-Only (compile-only)

Mode: COMPILE-ONLY. NOT executed. Rev: 2 Date: 2026-05-13 Scope: B3-F1c-b function rollback ONLY. Drops fn_birth_onboarding_full_scan() and nothing else. Revision trigger: GPT review Issue 4 — rollback must not include Directus Flow/Operation DML until scheduler binding is designed/approved separately.

What this rollback covers

ONE object only:

1. public.fn_birth_onboarding_full_scan() — the full-scan function.

What this rollback explicitly does NOT touch

• public.fn_b3f1_log_collection_onboarding_gap(text,text,text,text) — B3-F1b helper.
• public.fn_collection_onboarding_soft_gate() — B3-F1b gate.
• public.trg_collection_onboarding_soft_gate — B3-F1b trigger.
• public.dot_config['policy.birth_trigger.accepted_sibling_scope'] — policy data.
• public.system_issues rows — historical gap log.
• public.system_health_checks rows — definition registry.
• B3-A trigger functions and their attachments.
• public.collection_registry data.
• public.species_collection_map data.
• public.birth_registry data.
• public.directus_flows — NO Directus Flow DML (scheduler not yet designed/approved).
• public.directus_operations — NO Directus Operation DML (scheduler not yet designed/approved).

Rollback SQL (DO NOT EXECUTE in this run)

-- B3-F1c-b rollback. COMPILE-ONLY artifact. Execute only on explicit reviewer instruction.
-- Scope: function-only. No Directus Flow/Operation DML (Issue 4 fix).
BEGIN;

-- 1. Drop the full-scan function. IF EXISTS so rollback is idempotent.
DROP FUNCTION IF EXISTS public.fn_birth_onboarding_full_scan();

-- 2. Negative assertion guard: refuse to commit if any B3-F1b object was collaterally affected.
DO $$
DECLARE
  v_helper_present  boolean;
  v_gate_present    boolean;
  v_policy_present  boolean;
BEGIN
  SELECT EXISTS (
    SELECT 1 FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE n.nspname = 'public'
      AND p.proname = 'fn_b3f1_log_collection_onboarding_gap'
      AND p.pronargs = 4
  ) INTO v_helper_present;

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

  SELECT EXISTS (
    SELECT 1 FROM public.dot_config
    WHERE key = 'policy.birth_trigger.accepted_sibling_scope'
  ) INTO v_policy_present;

  IF NOT v_helper_present THEN
    RAISE EXCEPTION 'B3-F1c-b rollback aborted: B3-F1b helper missing (would have been a collateral drop).';
  END IF;
  IF NOT v_gate_present THEN
    RAISE EXCEPTION 'B3-F1c-b rollback aborted: B3-F1b gate missing (would have been a collateral drop).';
  END IF;
  IF NOT v_policy_present THEN
    RAISE EXCEPTION 'B3-F1c-b rollback aborted: sibling policy missing (would have been a collateral drop).';
  END IF;
END$$;

COMMIT;

Verification SELECT after rollback (read-only)

-- Expected: 0 rows (function removed)
SELECT proname FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public' AND p.proname = 'fn_birth_onboarding_full_scan';

-- Expected: 1 row (helper preserved)
SELECT proname FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname='public' AND p.proname='fn_b3f1_log_collection_onboarding_gap' AND p.pronargs=4;

-- Expected: 1 row (gate preserved)
SELECT proname FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname='public' AND p.proname='fn_collection_onboarding_soft_gate' AND p.pronargs=0;

-- Expected: 1 row (policy preserved)
SELECT key FROM public.dot_config WHERE key='policy.birth_trigger.accepted_sibling_scope';

Future scheduler rollback (NOT in this artifact — deferred to B3-F1c-c/B3-F1d)

When Directus Flow scheduler binding is designed and approved, a separate rollback artifact will be created covering:

• Removal of specifically-created directus_flows row(s).
• Removal of specifically-created directus_operations row(s).
• Potential Nuxt endpoint cleanup (if applicable).

That rollback will be scoped and reviewed independently.

B3-F1c-b Rollback Rev2 | Function-Only | COMPILE-ONLY | 2026-05-13