P3D — B3-ELD-EXEC Rollback SQL Artifact

DATA-LOSS WARNING. Running this rollback DROPS three columns from public.birth_registry. If enrichment writes have occurred since the DDL was applied, dropping these columns permanently deletes that enrichment data. There is no in-database backup. Separate approval is REQUIRED before running this rollback after enrichment has begun.

Prepared by: Opus (Claude Code) — COMPILE ONLY Prepared at: 2026-05-14 Pairs with: knowledge/dev/laws/dieu44-trien-khai/artifacts/p3d-birth-b3-eld-exec-ddl.sql.md Secrets redacted: true

---

1. Purpose

Reverse the B3-ELD-EXEC DDL by dropping canonical_address, owner, and jsonb_profile from public.birth_registry. Intended only for emergency rollback prior to enrichment, or for clean reset during pre-production trials.

---

2. Pre-rollback verification (REQUIRED)

Run this query and inspect every count before proceeding:

-- 2.1 Detect any enrichment writes that would be destroyed by DROP COLUMN
SELECT
  count(*) AS rows_with_any_enrichment,
  count(*) FILTER (WHERE jsonb_profile <> '{}'::jsonb)      AS jsonb_profile_populated,
  count(*) FILTER (WHERE canonical_address IS NOT NULL)     AS canonical_address_populated,
  count(*) FILTER (WHERE owner IS NOT NULL)                 AS owner_populated
FROM public.birth_registry
WHERE jsonb_profile <> '{}'::jsonb
   OR canonical_address IS NOT NULL
   OR owner IS NOT NULL;
-- If ANY count > 0: STOP. Obtain separate written approval before proceeding.
-- If ALL counts = 0: rollback is safe; no enrichment data exists.

-- 2.2 Confirm columns still present (sanity check before DROP)
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema='public' AND table_name='birth_registry'
  AND column_name IN ('canonical_address','owner','jsonb_profile')
ORDER BY column_name;
-- expected: 3 rows. If 0 rows, rollback already applied.

---

3. Rollback SQL

-- B3-ELD-EXEC ROLLBACK — drops Entity Living DB placeholder columns from public.birth_registry.
-- WARNING: If enrichment writes have occurred since DDL was applied, this DESTROYS that data.
-- Run the pre-rollback verification (§2) and obtain separate approval first.

BEGIN;

ALTER TABLE public.birth_registry
  DROP COLUMN IF EXISTS jsonb_profile,
  DROP COLUMN IF EXISTS owner,
  DROP COLUMN IF EXISTS canonical_address;

COMMIT;

Notes:

• DROP COLUMN IF EXISTS is idempotent. Re-running after partial application is safe.
• Drop order is intentionally the reverse of ADD order (FILO), although PostgreSQL imposes no ordering requirement for unrelated columns.
• Lock: ACCESS EXCLUSIVE on public.birth_registry for the duration of the ALTER. Drop is metadata-only and sub-second on this row count.

---

4. Post-rollback verification

-- 4.1 Confirm all three columns removed
SELECT column_name
FROM information_schema.columns
WHERE table_schema='public' AND table_name='birth_registry'
  AND column_name IN ('canonical_address','owner','jsonb_profile');
-- expected: 0 rows

-- 4.2 Confirm row count unchanged
SELECT count(*) AS row_count FROM public.birth_registry;
-- expected: same as pre-rollback snapshot (modulo any concurrent births)

-- 4.3 Confirm triggers still attached
SELECT tgname FROM pg_trigger
WHERE tgrelid = 'public.birth_registry'::regclass
  AND NOT tgisinternal
ORDER BY tgname;
-- expected: trg_birth_auto_certify, trg_birth_change_flag_matrix, trg_count_birth_registry

-- 4.4 Confirm dependent views still valid
SELECT count(*) FROM public.v_entity_full_classification LIMIT 1;
SELECT count(*) FROM public.v_species_matrix;
-- expected: both succeed

---

5. Separate-approval requirement

Rollback requires its own explicit approval chain, distinct from the DDL approval. Specifically:

1. Operator runs §2 pre-rollback verification.
2. Operator reports counts to reviewer (GPT or designated human).
3. Reviewer evaluates whether data loss is acceptable.
4. Only on written "rollback approved" does the operator run §3.

A rollback approval issued before the DDL was applied does NOT carry over — the enrichment state at rollback time is what matters.

---

6. Banner

DATA-LOSS WARNING — DO NOT EXECUTE WITHOUT FRESH APPROVAL. Approval issued for the original DDL does not authorize this rollback. Always run §2 verification first.

---

B3-ELD-EXEC Rollback SQL Artifact | COMPILE ONLY | Opus | 2026-05-14