Incomex AI Portal
KB-4044 rev 3

P3D Pack 1 Phase 2 — DDL Extend IU Schema — Prompt rev3

18 min read Revision 3
p3dpack1phase2ddlrev3approvedstrict

P3D Pack 1 Phase 2 — DDL Extend IU Schema — Implementation Prompt rev3

Date: 2026-05-11 Status: rev3 — GPT patched; APPROVED_FOR_AGENT_DISPATCH_AFTER_USER_CONFIRMATION Design ref: knowledge/dev/laws/dieu44-trien-khai/design/p3d-pack1-design-addendum-post-inventory-2026-05-11.md GPT review rev1: knowledge/dev/laws/dieu44-trien-khai/reviews/gpt-review-p3d-pack1-design-addendum-and-phase2-ddl-prompt-rev1-2026-05-11.md Scope: ADD nullable columns to native IU tables only. NO defaults. NO FK. NO index. NO trigger. NO data migration. NO TAC modification. NO Directus mutation. NO vector columns.

0. Objective

Safely extend native IU schema so it can later absorb TAC law-unit metadata under the EVOLVE path.

This phase is schema extension only. It must not migrate data, modify TAC, create views, create FKs, create indexes, create triggers, update Directus, or touch vector/Qdrant.

1. Hard boundaries

  • NO data migration.
  • NO TAC table modification.
  • NO view creation.
  • NO publication_member modification.
  • NO vocab table rename or FK addition.
  • NO Qdrant/vector mutation.
  • NO Nuxt code.
  • NO DOT-119 execution.
  • NO Directus mutation/restart/schema-refresh.
  • NO DEFAULT values on new columns.
  • NO FK constraints on new columns.
  • NO new indexes.
  • NO new triggers.
  • NO vector columns: do not add vector_sync_status, vector_synced_at, vector_chunk_count.
  • NO updates to existing rows.
  • NO information_unit / unit_version direct data writes.

Allowed:

  • ALTER TABLE public.information_unit ADD COLUMN IF NOT EXISTS ... for approved nullable columns.
  • ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS ... for approved nullable columns.
  • COMMENT ON COLUMN ... for documentation.
  • Read-only checks.
  • Rollback by dropping only the columns added in this pack if required.

2. Approved columns only

public.information_unit — 3 columns

doc_code TEXT NULL
section_type TEXT NULL
section_code TEXT NULL

public.unit_version — 7 columns

title TEXT NULL
description TEXT NULL
review_state TEXT NULL
provenance TEXT NULL
editor TEXT NULL
enacted_at TIMESTAMPTZ NULL
updated_at TIMESTAMPTZ NULL

Do not add any other column in this phase.

3. Preflight — strict STOP gates

Run this before any DDL. If any STOP gate fails, do not execute DDL. Upload a BLOCKED report.

#!/usr/bin/env bash
set -euo pipefail
TS=$(date +%Y%m%d-%H%M%S)
LOG="/tmp/p3d-pack1-phase2-${TS}.log"
exec > >(tee -a "$LOG") 2>&1

CONTAINER="${PG_CONTAINER:-postgres}"
DB="${PG_DB:-directus}"
DBUSER="${PG_USER:-directus}"
PSQL=(docker exec -i "$CONTAINER" psql -U "$DBUSER" -d "$DB" -v ON_ERROR_STOP=1 -P pager=off)

echo "=== P3D PACK1 PHASE2 PREFLIGHT $TS ==="
echo "CONTAINER=$CONTAINER DB=$DB DBUSER=$DBUSER"

# GATE 1 — tables exist in public schema
G1=$("${PSQL[@]}" -t -A -c "
SELECT count(*)
FROM pg_class c
JOIN pg_namespace n ON n.oid=c.relnamespace
WHERE n.nspname='public'
  AND c.relkind='r'
  AND c.relname IN ('information_unit','unit_version');")
echo "G1_PUBLIC_TABLE_COUNT=$G1"
if [ "$G1" != "2" ]; then echo "STOP_G1: expected public information_unit + unit_version"; exit 1; fi

# GATE 2 — record current columns and rows
IU_COLS_BEFORE=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM information_schema.columns WHERE table_schema='public' AND table_name='information_unit';")
UV_COLS_BEFORE=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM information_schema.columns WHERE table_schema='public' AND table_name='unit_version';")
IU_ROWS_BEFORE=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM public.information_unit;")
UV_ROWS_BEFORE=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM public.unit_version;")
echo "IU_COLS_BEFORE=$IU_COLS_BEFORE UV_COLS_BEFORE=$UV_COLS_BEFORE IU_ROWS_BEFORE=$IU_ROWS_BEFORE UV_ROWS_BEFORE=$UV_ROWS_BEFORE"

# GATE 3 — gateway enforced
G3=$("${PSQL[@]}" -t -A -c "SELECT value FROM public.dot_config WHERE key='iu_create.gateway.mode' LIMIT 1;")
echo "G3_GATEWAY=$G3"
if [ "$G3" != "enforced" ]; then echo "STOP_G3: gateway not enforced"; exit 1; fi

# GATE 4 — gateway guards enabled. Parentheses matter.
G4=$("${PSQL[@]}" -t -A -c "
SELECT count(*)
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 IN ('information_unit','unit_version')
  AND t.tgenabled='O'
  AND t.tgname IN ('trg_aa_iu_gateway_write_guard','trg_aa_uv_gateway_write_guard');")
echo "G4_GATEWAY_TRIGGER_COUNT=$G4"
if [ "$G4" != "2" ]; then echo "STOP_G4: expected 2 enabled gateway triggers"; exit 1; fi

# GATE 5 — if target columns already exist, types must match approved definitions.
echo "--- GATE 5 existing target columns/type check ---"
"${PSQL[@]}" -c "
WITH expected(table_name,column_name,data_type) AS (
  VALUES
    ('information_unit','doc_code','text'),
    ('information_unit','section_type','text'),
    ('information_unit','section_code','text'),
    ('unit_version','title','text'),
    ('unit_version','description','text'),
    ('unit_version','review_state','text'),
    ('unit_version','provenance','text'),
    ('unit_version','editor','text'),
    ('unit_version','enacted_at','timestamp with time zone'),
    ('unit_version','updated_at','timestamp with time zone')
), actual AS (
  SELECT table_name, column_name, data_type, is_nullable, column_default
  FROM information_schema.columns
  WHERE table_schema='public'
    AND table_name IN ('information_unit','unit_version')
)
SELECT e.table_name, e.column_name, e.data_type AS expected_type,
       a.data_type AS actual_type, a.is_nullable, a.column_default,
       CASE
         WHEN a.column_name IS NULL THEN 'missing_ok'
         WHEN a.data_type=e.data_type AND a.is_nullable='YES' AND a.column_default IS NULL THEN 'exists_ok'
         ELSE 'STOP_BAD_EXISTING_COLUMN'
       END AS verdict
FROM expected e
LEFT JOIN actual a USING (table_name,column_name)
ORDER BY e.table_name, e.column_name;"

G5_BAD=$("${PSQL[@]}" -t -A -c "
WITH expected(table_name,column_name,data_type) AS (
  VALUES
    ('information_unit','doc_code','text'),
    ('information_unit','section_type','text'),
    ('information_unit','section_code','text'),
    ('unit_version','title','text'),
    ('unit_version','description','text'),
    ('unit_version','review_state','text'),
    ('unit_version','provenance','text'),
    ('unit_version','editor','text'),
    ('unit_version','enacted_at','timestamp with time zone'),
    ('unit_version','updated_at','timestamp with time zone')
), actual AS (
  SELECT table_name, column_name, data_type, is_nullable, column_default
  FROM information_schema.columns
  WHERE table_schema='public'
    AND table_name IN ('information_unit','unit_version')
)
SELECT count(*)
FROM expected e
JOIN actual a USING (table_name,column_name)
WHERE NOT (a.data_type=e.data_type AND a.is_nullable='YES' AND a.column_default IS NULL); ")
echo "G5_BAD_EXISTING_COLUMNS=$G5_BAD"
if [ "$G5_BAD" != "0" ]; then echo "STOP_G5: existing target column has wrong type/nullability/default"; exit 1; fi

# GATE 6 — approved vector columns must not already be added by mistake.
G6=$("${PSQL[@]}" -t -A -c "
SELECT count(*)
FROM information_schema.columns
WHERE table_schema='public'
  AND table_name='unit_version'
  AND column_name IN ('vector_sync_status','vector_synced_at','vector_chunk_count');")
echo "G6_VECTOR_COLUMNS_PRESENT=$G6"
if [ "$G6" != "0" ]; then echo "STOP_G6: vector columns present; outside Phase2 scope"; exit 1; fi

# GATE 7 — no obvious active long transaction on target tables.
# Informational: do not kill anything.
echo "--- GATE 7 lock/activity snapshot ---"
"${PSQL[@]}" -c "
SELECT a.pid, a.state, a.wait_event_type, a.wait_event, a.query_start, left(a.query,120) AS query
FROM pg_stat_activity a
WHERE a.datname=current_database()
  AND a.state <> 'idle'
ORDER BY a.query_start NULLS LAST
LIMIT 10;"
echo "GATE_7_RECORDED"

# INVESTIGATION — hash algorithm; record only, do not gate DDL on conclusion.
echo "--- INVESTIGATION: hash algorithm samples ---"
"${PSQL[@]}" -c "
SELECT 'TAC' AS source, content_hash, md5(body) AS md5_body,
       length(content_hash) AS hash_len,
       content_hash = md5(body) AS md5_match,
       content_hash = encode(sha256(body::bytea), 'hex') AS sha256_match
FROM public.tac_unit_version
LIMIT 5;"
"${PSQL[@]}" -c "
SELECT 'IU' AS source, content_hash, md5(body) AS md5_body,
       length(content_hash) AS hash_len,
       content_hash = md5(body) AS md5_match,
       content_hash = encode(sha256(body::bytea), 'hex') AS sha256_match
FROM public.unit_version
LIMIT 5;"

echo "=== PREFLIGHT PASS — DDL MAY PROCEED ==="

4. DDL execution — transaction + lock timeout

Run all DDL in one transaction. If lock cannot be acquired quickly, fail safely.

BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '60s';

-- information_unit: 3 nullable columns, no defaults, no constraints
ALTER TABLE public.information_unit ADD COLUMN IF NOT EXISTS doc_code TEXT;
ALTER TABLE public.information_unit ADD COLUMN IF NOT EXISTS section_type TEXT;
ALTER TABLE public.information_unit ADD COLUMN IF NOT EXISTS section_code TEXT;

COMMENT ON COLUMN public.information_unit.doc_code IS 'Structural document lineage binding. NULL for non-law units. No FK in Phase 2.';
COMMENT ON COLUMN public.information_unit.section_type IS 'Section classification. No FK in Phase 2; vocab reconciliation deferred.';
COMMENT ON COLUMN public.information_unit.section_code IS 'Human-readable section alias. Not identity.';

-- unit_version: 7 nullable columns, no defaults, no constraints
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS title TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS review_state TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS provenance TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS editor TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS enacted_at TIMESTAMPTZ;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ;

COMMENT ON COLUMN public.unit_version.title IS 'Unit title per version. NULL until populated.';
COMMENT ON COLUMN public.unit_version.description IS 'Unit description per version. NULL until populated.';
COMMENT ON COLUMN public.unit_version.review_state IS 'Review lifecycle. No FK/default in Phase 2. NULL means unset.';
COMMENT ON COLUMN public.unit_version.provenance IS 'Content provenance. No FK/default in Phase 2. NULL means unset.';
COMMENT ON COLUMN public.unit_version.updated_at IS 'Last content update timestamp. NULL means not set by current workflow.';

COMMIT;

Execution template:

"${PSQL[@]}" <<'SQL'
BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '60s';
ALTER TABLE public.information_unit ADD COLUMN IF NOT EXISTS doc_code TEXT;
ALTER TABLE public.information_unit ADD COLUMN IF NOT EXISTS section_type TEXT;
ALTER TABLE public.information_unit ADD COLUMN IF NOT EXISTS section_code TEXT;
COMMENT ON COLUMN public.information_unit.doc_code IS 'Structural document lineage binding. NULL for non-law units. No FK in Phase 2.';
COMMENT ON COLUMN public.information_unit.section_type IS 'Section classification. No FK in Phase 2; vocab reconciliation deferred.';
COMMENT ON COLUMN public.information_unit.section_code IS 'Human-readable section alias. Not identity.';
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS title TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS description TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS review_state TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS provenance TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS editor TEXT;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS enacted_at TIMESTAMPTZ;
ALTER TABLE public.unit_version ADD COLUMN IF NOT EXISTS updated_at TIMESTAMPTZ;
COMMENT ON COLUMN public.unit_version.title IS 'Unit title per version. NULL until populated.';
COMMENT ON COLUMN public.unit_version.description IS 'Unit description per version. NULL until populated.';
COMMENT ON COLUMN public.unit_version.review_state IS 'Review lifecycle. No FK/default in Phase 2. NULL means unset.';
COMMENT ON COLUMN public.unit_version.provenance IS 'Content provenance. No FK/default in Phase 2. NULL means unset.';
COMMENT ON COLUMN public.unit_version.updated_at IS 'Last content update timestamp. NULL means not set by current workflow.';
COMMIT;
SQL

5. Post-DDL verification

echo "=== POST-DDL VERIFY ==="

IU_COLS_AFTER=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM information_schema.columns WHERE table_schema='public' AND table_name='information_unit';")
UV_COLS_AFTER=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM information_schema.columns WHERE table_schema='public' AND table_name='unit_version';")
IU_ROWS_AFTER=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM public.information_unit;")
UV_ROWS_AFTER=$("${PSQL[@]}" -t -A -c "SELECT count(*) FROM public.unit_version;")

echo "IU_COLS_AFTER=$IU_COLS_AFTER EXPECTED=$((IU_COLS_BEFORE+3))"
echo "UV_COLS_AFTER=$UV_COLS_AFTER EXPECTED=$((UV_COLS_BEFORE+7))"
echo "IU_ROWS_AFTER=$IU_ROWS_AFTER EXPECTED=$IU_ROWS_BEFORE"
echo "UV_ROWS_AFTER=$UV_ROWS_AFTER EXPECTED=$UV_ROWS_BEFORE"

if [ "$IU_COLS_AFTER" -ne $((IU_COLS_BEFORE+3)) ]; then echo "VERIFY_FAIL: IU cols"; exit 1; fi
if [ "$UV_COLS_AFTER" -ne $((UV_COLS_BEFORE+7)) ]; then echo "VERIFY_FAIL: UV cols"; exit 1; fi
if [ "$IU_ROWS_AFTER" != "$IU_ROWS_BEFORE" ]; then echo "VERIFY_FAIL: IU row count changed"; exit 1; fi
if [ "$UV_ROWS_AFTER" != "$UV_ROWS_BEFORE" ]; then echo "VERIFY_FAIL: UV row count changed"; exit 1; fi

# Verify all new columns remain NULL in existing rows
"${PSQL[@]}" -c "
SELECT count(*) FILTER (WHERE doc_code IS NOT NULL OR section_type IS NOT NULL OR section_code IS NOT NULL) AS iu_non_null_new_cols
FROM public.information_unit;"
"${PSQL[@]}" -c "
SELECT count(*) FILTER (WHERE title IS NOT NULL OR description IS NOT NULL OR review_state IS NOT NULL OR provenance IS NOT NULL OR editor IS NOT NULL OR enacted_at IS NOT NULL OR updated_at IS NOT NULL) AS uv_non_null_new_cols
FROM public.unit_version;"

# Verify no defaults/constraints on new columns
"${PSQL[@]}" -c "
SELECT table_name, column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_schema='public'
  AND ((table_name='information_unit' AND column_name IN ('doc_code','section_type','section_code'))
    OR (table_name='unit_version' AND column_name IN ('title','description','review_state','provenance','editor','enacted_at','updated_at')))
ORDER BY table_name, column_name;"

# Gateway and guard still enforced/enabled
"${PSQL[@]}" -c "SELECT key, value FROM public.dot_config WHERE key='iu_create.gateway.mode';"
"${PSQL[@]}" -c "
SELECT tgname, tgrelid::regclass AS table_name, tgenabled
FROM pg_trigger
WHERE tgname IN ('trg_aa_iu_gateway_write_guard','trg_aa_uv_gateway_write_guard')
ORDER BY tgname;"

# Function reachability: dry-run only; do not create row.
"${PSQL[@]}" -c "
SELECT public.fn_iu_create_plan('test/phase2-verify', 'law_unit', 'Phase 2 verify', 'Test body', NULL, NULL, NULL, NULL, NULL) AS plan_result;"

If post-DDL verification fails, rollback immediately.

6. Rollback

Only use if DDL or verification fails. Rollback drops only columns from this pack. Since Phase 2 does not write data into them and creates no FK/index/trigger/default, rollback is safe.

BEGIN;
SET LOCAL lock_timeout = '5s';
SET LOCAL statement_timeout = '60s';
ALTER TABLE public.information_unit DROP COLUMN IF EXISTS doc_code;
ALTER TABLE public.information_unit DROP COLUMN IF EXISTS section_type;
ALTER TABLE public.information_unit DROP COLUMN IF EXISTS section_code;
ALTER TABLE public.unit_version DROP COLUMN IF EXISTS title;
ALTER TABLE public.unit_version DROP COLUMN IF EXISTS description;
ALTER TABLE public.unit_version DROP COLUMN IF EXISTS review_state;
ALTER TABLE public.unit_version DROP COLUMN IF EXISTS provenance;
ALTER TABLE public.unit_version DROP COLUMN IF EXISTS editor;
ALTER TABLE public.unit_version DROP COLUMN IF EXISTS enacted_at;
ALTER TABLE public.unit_version DROP COLUMN IF EXISTS updated_at;
COMMIT;

7. Report

Upload report to:

knowledge/dev/laws/dieu44-trien-khai/reports/p3d-pack1-phase2-ddl-extend-implementation-report.md

Report must include:

phase_status=PASS|FAIL|BLOCKED|ROLLED_BACK
mode=DDL_SCHEMA_EXTENSION_ONLY
gate_results=G1..G7
hash_investigation_findings=<summary>
iu_cols_before=<n>
iu_cols_after=<n>
uv_cols_before=<n>
uv_cols_after=<n>
iu_rows_before=<n>
iu_rows_after=<n>
uv_rows_before=<n>
uv_rows_after=<n>
columns_added=<list>
all_new_columns_nullable=true|false
new_column_defaults_present=false
fk_added=false
index_added=false
trigger_added=false
data_migration_performed=false
tac_tables_modified=false
directus_mutation_performed=false
vector_columns_added=false
qdrant_mutation_performed=false
gateway_after=enforced|other
gateway_triggers_enabled=true|false
fn_iu_create_plan_reachable=true|false
rollback_performed=true|false
log_path=<path>

Also include:

  • exact DDL executed;
  • post-DDL verification evidence;
  • any warnings;
  • next recommended phase.

8. Final response expected from Agent

pack1_phase2_status=PASS|FAIL|BLOCKED|ROLLED_BACK
report_path=knowledge/dev/laws/dieu44-trien-khai/reports/p3d-pack1-phase2-ddl-extend-implementation-report.md
columns_added=<list>
iu_cols_before=<n> iu_cols_after=<n>
uv_cols_before=<n> uv_cols_after=<n>
row_counts_unchanged=true|false
all_new_columns_null=true|false
no_defaults=true|false
no_fk_index_trigger=true|false
no_data_migration=true
no_tac_modification=true
rollback_performed=true|false

Return only the final response plus critical blocker notes.

Back to Knowledge Hub knowledge/dev/laws/dieu44-trien-khai/prompts/p3d-pack1-phase2-ddl-extend-implementation-prompt.md