Incomex AI Portal
KB-FEF8 rev 2

P3D Birth B3-F1c-H Wrapper + Row Rollback SQL (Compile-Only)

12 min read Revision 2
p3dbirth-systemb3f1c-hrollbacksql-artifactcompile-only2026-05-14

P3D Birth — B3-F1c-H Wrapper + Row Rollback SQL (Compile-Only)

Date: 2026-05-14 Mode: COMPILE-ONLY. NOT executed. No DDL/DML run. Author: Claude (Opus 4.7, 1M context) GPT approval: knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b2-rev3-b3f1c-h-dual-trigger-patch-approved-2026-05-14.md Companion wrapper install (rev 2): knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-b3f1c-h-wrapper-function.sql.md Companion row install: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-b3f1c-h-system-health-check-row.sql.md

0. Note on the COMMENT signature contract (2026-05-14 patch)

Rollback gates on obj_description(<wrapper_oid>, 'pg_proc') LIKE 'B3-F1c-H wrapper%'. With wrapper artifact rev 2, the install transaction is now atomic and includes:

  1. CREATE FUNCTION
  2. COMMENT ON FUNCTION (mandatory)
  3. Post-create verification gate that RAISEs if the COMMENT signature is missing or wrong.

This means a successful install always leaves the wrapper with a comment starting B3-F1c-H wrapper. A failed install leaves nothing at all (atomic rollback). The rollback artifact's comment-signature gate is therefore reliable: if it fails on a wrapper that exists, the wrapper was installed by something other than this artifact, and the rollback correctly refuses to proceed.

The prefix B3-F1c-H wrapper is a contract — see …/p3d-birth-b3f1c-h-wrapper-function.sql.md §5.

1. Purpose

Cleanly reverse the wrapper-and-row install if a problem is discovered after execution. This rollback is not to be run pre-emptively; it is the emergency-undo path only.

2. Authorization & ordering rules

Rollback execution requires:

  1. Separate fresh authorization. Authorization to install does NOT include authorization to rollback. The Council must re-approve.
  2. Order: row DELETE first, then wrapper DROP. Reverse of install. This avoids leaving an orphan health-check row that points at a missing function.
  3. Safeguards must pass. If any gate fails, rollback aborts; manual investigation required.
  4. No CASCADE. No partial deletes. No conditional skips.
  5. Single transaction. All-or-nothing.

3. Safeguards (normative)

Safeguard What it checks Why
Row presence Row DOT-BIRTH-ONBOARD-FULLSCAN-HC exists Nothing to delete otherwise; abort early.
Row signature _dot_origin = 'dieu35_fix25' AND executor_ref = 'fn_birth_onboarding_full_scan_hc' AND executor_type = 'function' Confirms the row was installed by this artifact and has not been repurposed by later governance. If signature drifted, abort — separate approval needed.
DELETE rowcount Exactly 1 row deleted Refuses partial state.
Wrapper presence Function public.fn_birth_onboarding_full_scan_hc(jsonb) exists Nothing to drop otherwise.
Wrapper comment signature Comment starts with 'B3-F1c-H wrapper' Confirms the function was installed by this artifact (and not by some other process that reused the name).
No other health-check row references the wrapper executor_ref='fn_birth_onboarding_full_scan_hc' row count after DELETE = 0 Prevents dropping a function that is still referenced by another row.

4. Hard-boundary attestation

Boundary Held
No DDL executed YES
No DML executed YES
Rollback SQL run NO (compile-only)
Secrets disclosed NONE

5. The SQL — single transaction, gated rollback

-- =============================================================================
-- B3-F1c-H rollback — compile artifact (NOT executed).
-- Reverses the wrapper + row install in the reverse of install order:
--   1) DELETE row
--   2) DROP wrapper function
-- Separate fresh authorization required before running this block.
-- =============================================================================
BEGIN;

-- -----------------------------------------------------------------------------
-- Phase 1: row DELETE with signature gate + rowcount gate.
-- -----------------------------------------------------------------------------
DO $rollback_row$
DECLARE
  v_row record;
  v_expected_origin   text := 'dieu35_fix25';
  v_expected_ref      text := 'fn_birth_onboarding_full_scan_hc';
  v_expected_type     text := 'function';
  v_deleted_count     integer;
BEGIN
  -- Gate: row must exist.
  SELECT * INTO v_row
  FROM public.system_health_checks
  WHERE code = 'DOT-BIRTH-ONBOARD-FULLSCAN-HC';

  IF NOT FOUND THEN
    RAISE EXCEPTION
      'B3-F1c-H rollback aborted: row DOT-BIRTH-ONBOARD-FULLSCAN-HC not present. '
      'Nothing to delete. If the row was already removed, drop the wrapper manually with separate approval.';
  END IF;

  -- Gate: row signature must match this install.
  IF v_row._dot_origin   <> v_expected_origin
     OR v_row.executor_ref  <> v_expected_ref
     OR v_row.executor_type <> v_expected_type
  THEN
    RAISE EXCEPTION
      'B3-F1c-H rollback aborted: row signature drift detected. '
      'Expected (_dot_origin=%, executor_ref=%, executor_type=%); got (=%, =%, =%). '
      'Row may have been modified by later governance. Separate approval required.',
      v_expected_origin, v_expected_ref, v_expected_type,
      v_row._dot_origin, v_row.executor_ref, v_row.executor_type;
  END IF;

  -- DELETE.
  DELETE FROM public.system_health_checks
  WHERE code = 'DOT-BIRTH-ONBOARD-FULLSCAN-HC'
    AND _dot_origin   = v_expected_origin
    AND executor_ref  = v_expected_ref
    AND executor_type = v_expected_type;

  GET DIAGNOSTICS v_deleted_count = ROW_COUNT;

  IF v_deleted_count <> 1 THEN
    RAISE EXCEPTION
      'B3-F1c-H rollback inconsistency: expected DELETE rowcount=1, got %.', v_deleted_count;
  END IF;
END$rollback_row$;

-- -----------------------------------------------------------------------------
-- Phase 2: wrapper DROP with comment-signature gate + referrer gate.
-- -----------------------------------------------------------------------------
DO $rollback_func$
DECLARE
  v_func_exists  boolean;
  v_comment      text;
  v_referrers    integer;
BEGIN
  -- Gate: function must exist.
  SELECT EXISTS (
    SELECT 1
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = 'public'
      AND p.proname = 'fn_birth_onboarding_full_scan_hc'
  ) INTO v_func_exists;

  IF NOT v_func_exists THEN
    RAISE EXCEPTION
      'B3-F1c-H rollback aborted: row deleted but wrapper function already absent. State inconsistent.';
  END IF;

  -- Gate: comment must match install signature.
  SELECT obj_description(p.oid, 'pg_proc') INTO v_comment
  FROM pg_proc p
  JOIN pg_namespace n ON p.pronamespace = n.oid
  WHERE n.nspname = 'public'
    AND p.proname = 'fn_birth_onboarding_full_scan_hc'
  LIMIT 1;

  IF v_comment IS NULL OR v_comment NOT LIKE 'B3-F1c-H wrapper%' THEN
    RAISE EXCEPTION
      'B3-F1c-H rollback aborted: wrapper comment does not match B3-F1c-H install signature. '
      'Comment=[%]. Function may have been re-purposed. Separate approval required.',
      COALESCE(v_comment, '(NULL)');
  END IF;

  -- Gate: no other system_health_checks row references the wrapper.
  SELECT count(*) INTO v_referrers
  FROM public.system_health_checks
  WHERE executor_ref = 'fn_birth_onboarding_full_scan_hc';

  IF v_referrers > 0 THEN
    RAISE EXCEPTION
      'B3-F1c-H rollback aborted: % other system_health_checks row(s) still reference '
      'executor_ref=fn_birth_onboarding_full_scan_hc. Drop them first with separate approval.',
      v_referrers;
  END IF;
END$rollback_func$;

DROP FUNCTION public.fn_birth_onboarding_full_scan_hc(jsonb);

-- -----------------------------------------------------------------------------
-- Phase 3: post-rollback integrity verification.
-- -----------------------------------------------------------------------------
DO $rollback_verify$
DECLARE
  v_row_count    integer;
  v_func_count   integer;
BEGIN
  SELECT count(*) INTO v_row_count
  FROM public.system_health_checks
  WHERE code = 'DOT-BIRTH-ONBOARD-FULLSCAN-HC';

  SELECT count(*) INTO v_func_count
  FROM pg_proc p
  JOIN pg_namespace n ON p.pronamespace = n.oid
  WHERE n.nspname = 'public'
    AND p.proname = 'fn_birth_onboarding_full_scan_hc';

  IF v_row_count <> 0 THEN
    RAISE EXCEPTION
      'B3-F1c-H rollback verification failed: row still present (count=%).', v_row_count;
  END IF;
  IF v_func_count <> 0 THEN
    RAISE EXCEPTION
      'B3-F1c-H rollback verification failed: function still present (count=%).', v_func_count;
  END IF;
END$rollback_verify$;

COMMIT;

6. Expected echo (when executed)

BEGIN
DO        -- phase 1 (row gate + DELETE + rowcount gate)
DO        -- phase 2 (function gate + comment gate + referrer gate)
DROP FUNCTION
DO        -- phase 3 (verification)
COMMIT

7. What this rollback does NOT do

Not done Why
Drop scanner fn_birth_onboarding_full_scan Not installed by this workstream. Owned by b3f1c lineage. Do not touch.
Roll back birth_registry ELD columns (canonical_address, owner, jsonb_profile) Out of scope — owned by B3-ELD-EXEC, separate rollback artifact at …/p3d-birth-b3-eld-exec-ddl.sql.md (DROP COLUMN sequence), separately authorized.
Remove system_issues rows the scanner may have written via earlier on-demand Trigger B2 invocations Scanner outputs are append-only audit trail; deleting them would corrupt history. If cleanup is needed, separate approval + dedicated DELETE artifact.
Touch dot_tools Not authored by this workstream.
Touch cron / dot-dot-health Not authored by this workstream.
CREATE OR REPLACE anything Not applicable to rollback.

8. Failure handling

Failure Action
Gate 1 (row missing) Abort. Manual investigation. If row was already removed but function remains, file separate approval to drop function alone.
Gate 2 (row signature drift) Abort. Row was modified post-install; consult Council.
Gate 3 (DELETE rowcount ≠ 1) Abort. Concurrent change; investigate.
Gate 4 (function missing after row delete) Abort. State already inconsistent — Council triage.
Gate 5 (comment signature drift) Abort. Function was re-purposed; consult.
Gate 6 (other referrers) Abort. Drop the referrers first (separate approval).
Gate 7 (post-verification) Abort + ROLLBACK. The transaction is rolled back automatically by the unhandled exception.

Because all phases are inside BEGIN ... COMMIT, any RAISE EXCEPTION aborts the transaction and PostgreSQL rolls back implicitly. No partial state can land.

9. Compile-time fingerprint

Field Value
Order of operations row DELETE → function DROP → post-verify
Transaction single, all-or-nothing
Gates 7 (signature drift detection at row + function level; rowcount; referrer; post-verify)
CASCADE used NO
Touches scanner NO
Touches birth_registry schema NO

P3D Birth — B3-F1c-H Wrapper + Row Rollback SQL Artifact | 2026-05-14 | COMPILE-ONLY

Back to Knowledge Hub knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-b3f1c-h-wrapper-row-rollback.sql.md