P3D — B3-ELD-EXEC DDL SQL Artifact

DO NOT EXECUTE WITHOUT AUTHORIZATION. This file is a compile-only artifact. GPT review and explicit user approval are required before any DDL is executed. Prepared by: Opus (Claude Code) — COMPILE ONLY Prepared at: 2026-05-14 Design ref: knowledge/dev/laws/dieu44-trien-khai/design/p3d-birth-entity-living-db-placeholder-design.md GPT review ref: knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-b3-eld-design-and-b3f1c-g-patch-conditional-approval-2026-05-14.md Secrets redacted: true

1. Purpose

Add three nullable/defaulted Entity Living DB (ELD) placeholder columns to public.birth_registry per Option A in the B3-ELD design, conditionally approved by GPT on 2026-05-14.

Target columns:

Column	Type	Nullability	Default
`canonical_address`	`text`	NULL	none
`owner`	`text`	NULL	none
`jsonb_profile`	`jsonb`	NOT NULL	`'{}'::jsonb`

No trigger patch. No function patch. No backfill DML. Existing 280,846 rows receive '{}'::jsonb for jsonb_profile via fast-path constant default; canonical_address and owner remain NULL until B6 enrichment.

2. Preflight discovery summary (live PG, READ-ONLY, 2026-05-14)

All queries executed via docker exec -i postgres psql -U directus -d directus -At on VPS (38.242.240.89). No mutation. Secrets redacted.

Check	Result	Pass?
Table `public.birth_registry` exists	`to_regclass = 'birth_registry'`	YES
Target columns absent	Empty result set for `canonical_address`, `owner`, `jsonb_profile` in `information_schema.columns`	YES
PostgreSQL version	`16.13 (Debian 16.13-1.pgdg13+1)` / `server_version_num = 160013`	YES (≥110000)
Row count	`280846`	INFO
Triggers on table	`trg_birth_auto_certify` (fn_birth_auto_certify), `trg_birth_change_flag_matrix` (fn_birth_change_flag_matrix), `trg_count_birth_registry` (update_record_count)	INFO — column-shape-agnostic; no patch needed
Dependent views	`public.v_entity_full_classification`, `public.v_species_matrix`	INFO — both reference named columns only (no `SELECT *`); ADD COLUMN is transparent
History/shadow tables matching `%birth_registry%`	Only `public.birth_registry` itself	YES — no shadow tables
FKs referencing `public.birth_registry`	None	YES
RLS policies on `public.birth_registry`	None	YES

Stop conditions evaluation: ALL PASSED.

3. Fast-path / metadata-only assessment

PostgreSQL 11+ supports fast-path ADD COLUMN ... DEFAULT <non-volatile-literal> without rewriting the table (the default is stored as a per-row "missing value" in pg_attribute.atthasmissing / attmissingval).
Verified PG version: 16.13 (server_version_num = 160013 ≥ 110000). Fast-path semantics apply.
'{}'::jsonb is a literal constant default — non-volatile — eligible for fast-path. No table rewrite expected for jsonb_profile.
canonical_address and owner are nullable without a default — these are always metadata-only adds (the new attribute is simply marked NULL for existing rows without rewrite).
Expected lock: ACCESS EXCLUSIVE on public.birth_registry for the duration of the ALTER TABLE. Catalog-only updates → milliseconds-scale on 280,846 rows.
Expected duration: sub-second on this row count, assuming no concurrent long writers.

Reference: PostgreSQL 11 release notes — "ALTER TABLE ... ADD COLUMN with a non-null default can now be made in metadata only."

4. DDL SQL (canonical)

-- B3-ELD-EXEC DDL — applies to public.birth_registry
-- Adds Entity Living DB placeholder columns: canonical_address, owner, jsonb_profile.
-- Wrap in an explicit transaction so failure rolls all three back.
-- DO NOT EXECUTE without GPT review + user approval.

BEGIN;

ALTER TABLE public.birth_registry
  ADD COLUMN canonical_address text NULL,
  ADD COLUMN owner             text NULL,
  ADD COLUMN jsonb_profile     jsonb NOT NULL DEFAULT '{}'::jsonb;

COMMIT;

4.1 Notes on lock acquisition

ALTER TABLE ... ADD COLUMN automatically acquires ACCESS EXCLUSIVE on the target relation in PostgreSQL — an explicit LOCK TABLE public.birth_registry IN ACCESS EXCLUSIVE MODE; is not required and is intentionally omitted. Documenting this for operator clarity, not as a code change.

4.2 Idempotency

This artifact intentionally does not include IF NOT EXISTS guards. The pre-execution checklist (§6) requires the operator to confirm columns are absent immediately before execution. If a re-run after partial application is needed, treat it as a separate compile pass.

5. Recommended pre-execution checks (operator runs immediately before DDL)

-- 5.1 Re-verify table exists
SELECT to_regclass('public.birth_registry') AS reg;
-- expected: birth_registry

-- 5.2 Re-verify target columns still absent
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

-- 5.3 Confirm no concurrent long-running write transactions on birth_registry
SELECT pid, state, query_start, left(query, 120) AS q
FROM pg_stat_activity
WHERE state <> 'idle'
  AND query ILIKE '%birth_registry%'
  AND pid <> pg_backend_pid();
-- expected: 0 rows (or only short queries)

-- 5.4 Optional: snapshot row count for comparison post-DDL
SELECT count(*) AS row_count FROM public.birth_registry;

6. Expected behavior on execution

Single transaction; all three columns added atomically.
Lock: ACCESS EXCLUSIVE on public.birth_registry for the duration of the ALTER (catalog catalog update + per-attribute setup). Sub-second expected.
No row rewrite. jsonb_profile default '{}'::jsonb stored as attmissingval.
Triggers trg_birth_auto_certify, trg_birth_change_flag_matrix, trg_count_birth_registry continue to fire on subsequent INSERTs/UPDATEs unchanged — they do not reference the new columns.
Dependent views v_entity_full_classification and v_species_matrix continue to function without recreation — both reference named columns of birth_registry (id, entity_code, species_code, composition_level, certified); ADD COLUMN does not affect them.
fn_birth_registry_auto continues to INSERT its 8 named columns (per design §2.4) — the 3 new columns default appropriately on every future row (NULL / NULL / '{}').

7. Post-execution verification SQL (operator runs after DDL)

-- 7.1 Confirm all three columns present with correct types/defaults
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:
--   canonical_address | text  | YES | NULL
--   jsonb_profile     | jsonb | NO  | '{}'::jsonb
--   owner             | text  | YES | NULL

-- 7.2 Confirm jsonb_profile default applied to all existing rows
SELECT count(*) AS all_rows,
       count(*) FILTER (WHERE jsonb_profile = '{}'::jsonb) AS empty_profile_rows,
       count(*) FILTER (WHERE jsonb_profile IS NULL)        AS null_profile_rows
FROM public.birth_registry;
-- expected: all_rows == empty_profile_rows; null_profile_rows == 0

-- 7.3 Confirm row count unchanged
SELECT count(*) AS row_count FROM public.birth_registry;
-- expected: 280846 (or higher if births happened during DDL window)

-- 7.4 Confirm triggers still attached and not invalidated
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

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

DO NOT EXECUTE WITHOUT AUTHORIZATION. Required approvals: (1) GPT review of this artifact, (2) explicit user "execute" signal. Execution path is a separate, tightly-scoped session — not this compile session.

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