Incomex AI Portal
KB-18D4

dot-iu-cutter v0.2 — Phase α DDL Draft (2026-05-15)

17 min read Revision 1
dieu44-trien-khaidot-iu-cutterv0.2phase-alphaddl-draftdo-not-executereview-only2026-05-15

dot-iu-cutter v0.2 — Phase α DDL Draft

⚠️ DO NOT EXECUTE WITHOUT EXPLICIT GPT / USER PROMPT. This file contains a textual DDL draft authored for GPT review only. It is NOT a runnable migration script. Execution requires:

  1. GPT review of this draft, and
  2. an explicit production / dry-run authorization prompt referencing this file by path. Until both conditions are satisfied, no part of this DDL may be applied to any database — production, dry-run, sandbox, or otherwise.
document_path: knowledge/dev/laws/dieu44-trien-khai/v0.2-ddl-authoring/dot-iu-cutter-v0.2-phase-alpha-ddl-draft-2026-05-15.sql.md
revision: r1
date: 2026-05-15
author: Agent (Claude Code CLI, Opus 4.7 1M)
phase: v0.2 — Phase α DDL DRAFT (authored for review; NOT executed)
ddl_authored: TRUE
ddl_executed: FALSE
mutation_performed: FALSE
dry_run_started: FALSE
production_migration_allowed: FALSE

§1 — Header (binding posture)

-- ============================================================================
-- dot-iu-cutter v0.2 — Phase α DDL DRAFT
-- DO NOT EXECUTE WITHOUT EXPLICIT GPT/USER PROMPT.
-- This file is for GPT review only.
-- ============================================================================
-- Target environment:    PRODUCTION (db=directus, container=postgres on VPS 38.242.240.89)
-- Target PG version:     PostgreSQL 16.13
-- Migration role:        workflow_admin
-- Source review chain:   BR-2/3/7 closures + BR-4 closure + BR-5 closure + Phase α design (6 docs)
-- GPT-decided Q1–Q6 (Phase α design GPT review):
--   Q1 alias placement:   cutter_governance.canonical_address_alias
--   Q2 FK posture:        soft uuid reference only; no cross-schema FK
--   Q3 authority CHECK:   none in Phase α
--   Q4 sandbox alias:     omit
--   Q5 dry-run env:       fresh sibling env (NOT pg-dry-run-hb05-2026-05-15)
--   Q6 jsonb cleanup:     defer
-- Risk class:            STANDARD
-- ============================================================================

§2 — Single-Transaction DDL (textual draft; for review only)

BEGIN;

-- Set strict error behavior for the dry-run / migration session
SET LOCAL statement_timeout = 0;
SET LOCAL lock_timeout = '30s';
SET LOCAL idle_in_transaction_session_timeout = '60s';

-- ---------------------------------------------------------------------------
-- Step 1 — Add canonical_address_format_version on public.tac_logical_unit
-- ---------------------------------------------------------------------------
-- IF NOT EXISTS guards make the step idempotent under retry.
-- DEFAULT applies the value to all 86 existing rows at column-add time.
ALTER TABLE public.tac_logical_unit
    ADD COLUMN IF NOT EXISTS canonical_address_format_version text NOT NULL
        DEFAULT 'canonical-address-v1';

COMMENT ON COLUMN public.tac_logical_unit.canonical_address_format_version
    IS 'BR-5 (2026-05-15): canonical-address-v1 grammar version stamp; default ''canonical-address-v1''';

-- ---------------------------------------------------------------------------
-- Step 2 — Add authority on public.tac_logical_unit (nullable; default ''draft'')
-- ---------------------------------------------------------------------------
-- nullable in Phase α; NOT NULL promotion deferred to Phase β
-- no CHECK constraint per GPT Q3 decision; application-layer enforcement only
ALTER TABLE public.tac_logical_unit
    ADD COLUMN IF NOT EXISTS authority text
        DEFAULT 'draft';

COMMENT ON COLUMN public.tac_logical_unit.authority
    IS 'BR-4 (2026-05-15): source-authority class {enacted, draft, runtime}; default ''draft''; nullable in Phase α; NOT NULL deferred to Phase β';

-- ---------------------------------------------------------------------------
-- Step 3 — Backfill authority on existing rows via BR-4 Candidate B mapping
-- ---------------------------------------------------------------------------
-- This UPDATE fires trg_tac_birth_gate_lu BEFORE UPDATE for each row.
-- Per the fn_tac_birth_gate_lu inspection (Section §9.2 of the inspection doc):
--   - canonical_address regex re-validates (passes; value unchanged)
--   - section_type vocab check passes
--   - parent_id existence + doc_code parity passes
--   - NEW.updated_at := now()   ← side effect: all 86 rows'' updated_at bumps to backfill time (accepted as normal UPDATE semantics)
-- The CASE handles all 3 lifecycle vocab values; rows with unmapped values would
-- get NULL (column is nullable in Phase α; CASE NULL is benign).
UPDATE public.tac_logical_unit
   SET authority = CASE lifecycle_status
                     WHEN 'draft_only' THEN 'draft'
                     WHEN 'active'     THEN 'enacted'
                     WHEN 'retired'    THEN 'enacted'
                   END
 WHERE authority IS NULL;

-- ---------------------------------------------------------------------------
-- Step 4 — Mirror canonical_address_format_version on sandbox_tac.logical_unit
-- ---------------------------------------------------------------------------
-- 76 sandbox rows; DEFAULT backfills automatically at column-add time.
ALTER TABLE sandbox_tac.logical_unit
    ADD COLUMN IF NOT EXISTS canonical_address_format_version text NOT NULL
        DEFAULT 'canonical-address-v1';

COMMENT ON COLUMN sandbox_tac.logical_unit.canonical_address_format_version
    IS 'BR-5 (2026-05-15): mirror of public.tac_logical_unit.canonical_address_format_version; same default';

-- ---------------------------------------------------------------------------
-- Step 5 — Mirror authority on sandbox_tac.logical_unit (no backfill in Phase α)
-- ---------------------------------------------------------------------------
-- 76 sandbox rows remain authority=NULL after column-add by design
-- (BR-7 Option II + design master §6 + GPT Q4 decision).
ALTER TABLE sandbox_tac.logical_unit
    ADD COLUMN IF NOT EXISTS authority text
        DEFAULT 'draft';

COMMENT ON COLUMN sandbox_tac.logical_unit.authority
    IS 'BR-4 (2026-05-15): mirror of public.tac_logical_unit.authority; no backfill in Phase α; sandbox backfill deferred to Phase β';

-- (Step 6 of the design plan — "NO sandbox backfill in Phase α" — is intentional NO-OP)

-- ---------------------------------------------------------------------------
-- Step 7 — Create cutter_governance.canonical_address_alias
-- ---------------------------------------------------------------------------
-- Placement: cutter_governance (GPT Q1 decision).
-- Soft uuid reference only; no cross-schema PG FK (GPT Q2 decision).
-- Application-layer enforcement of alias_kind enum; no PG CHECK (GPT Q3 spirit).
-- Sandbox alias mirror omitted (GPT Q4 decision).
CREATE TABLE IF NOT EXISTS cutter_governance.canonical_address_alias (
    alias_id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    target_unit_id    uuid NOT NULL,           -- soft reference to public.tac_logical_unit.id (no FK in Phase α)
    alias_text        text NOT NULL,           -- application-layer validates canonical-address-v1 grammar
    alias_kind        text NOT NULL,           -- one of {previous_canonical, rename, redirect, external_reference}
    valid_from        timestamptz NOT NULL DEFAULT now(),
    valid_until       timestamptz,             -- NULL = currently valid
    created_by        text NOT NULL,
    rationale         text,
    scenario_ref      text
);

COMMENT ON TABLE  cutter_governance.canonical_address_alias
    IS 'Phase α (2026-05-15): rename/redirect/external_reference history for canonical_address. 0 writers/readers wired in Phase α; Phase β activates write paths.';
COMMENT ON COLUMN cutter_governance.canonical_address_alias.target_unit_id
    IS 'Soft reference to public.tac_logical_unit.id; no PG FK in Phase α (GPT Q2 decision).';
COMMENT ON COLUMN cutter_governance.canonical_address_alias.alias_kind
    IS 'Application-layer enum: {previous_canonical, rename, redirect, external_reference}. No PG CHECK in Phase α (GPT Q3 spirit).';

-- ---------------------------------------------------------------------------
-- Step 8 — Indexes on cutter_governance.canonical_address_alias
-- ---------------------------------------------------------------------------
-- PK index is created automatically by Step 7.
CREATE INDEX IF NOT EXISTS idx_alias_alias_text
    ON cutter_governance.canonical_address_alias (alias_text);

CREATE INDEX IF NOT EXISTS idx_alias_target_unit_id
    ON cutter_governance.canonical_address_alias (target_unit_id);

CREATE INDEX IF NOT EXISTS idx_alias_alias_kind
    ON cutter_governance.canonical_address_alias (alias_kind);

CREATE INDEX IF NOT EXISTS idx_alias_validity_window
    ON cutter_governance.canonical_address_alias (valid_from, valid_until DESC);

-- ---------------------------------------------------------------------------
-- Final sanity SELECT (read-only confirmation inside the same transaction)
-- ---------------------------------------------------------------------------
SELECT
    (SELECT count(*) FROM public.tac_logical_unit WHERE authority IS NULL)                      AS tac_authority_null_count,
    (SELECT count(*) FROM public.tac_logical_unit WHERE authority='draft')                      AS tac_authority_draft_count,
    (SELECT count(*) FROM public.tac_logical_unit WHERE canonical_address_format_version='canonical-address-v1') AS tac_format_v1_count,
    (SELECT count(*) FROM sandbox_tac.logical_unit WHERE canonical_address_format_version='canonical-address-v1') AS sandbox_format_v1_count,
    (SELECT count(*) FROM sandbox_tac.logical_unit WHERE authority IS NULL)                     AS sandbox_authority_null_count,
    (SELECT count(*) FROM information_schema.tables WHERE table_schema='cutter_governance' AND table_name='canonical_address_alias') AS alias_table_present;

-- Expected (on production):
--   tac_authority_null_count        = 0    (all 86 backfilled to 'draft')
--   tac_authority_draft_count       = 86   (Candidate B mapping)
--   tac_format_v1_count             = 86   (DEFAULT applied)
--   sandbox_format_v1_count         = 76   (DEFAULT applied to mirror)
--   sandbox_authority_null_count    = 76   (no backfill in Phase α; intentional)
--   alias_table_present             = 1

COMMIT;

§3 — Posture Statements

3.1 Transaction wrapping

wrapped_in_BEGIN_COMMIT: YES (single transaction)
on_error_stop_behavior: caller MUST dispatch with --single-transaction --set ON_ERROR_STOP=1 (mirroring v0.1 production execution C-08 pattern)
partial_commit_on_failure: IMPOSSIBLE (single transaction; any error aborts the whole batch)

3.2 Idempotency posture

idempotency_components:
  - ADD COLUMN IF NOT EXISTS (PG 9.6+; PG 16 supported)
  - CREATE TABLE IF NOT EXISTS
  - CREATE INDEX IF NOT EXISTS
  - UPDATE … WHERE authority IS NULL  (re-running affects 0 rows after first success)

idempotency_consequence:
  re-running the entire transaction on a system that has already had Phase α applied produces:
    - 0 schema changes (all IF NOT EXISTS guards short-circuit)
    - 0 row updates (WHERE authority IS NULL matches nothing)
    - the trailing SELECT returns the same expected values

caveat:
  the only non-idempotent side effect is the COMMENT ON statements, which overwrite any existing comment on re-run. This is benign (matching the source-of-truth content) but worth noting.

re-applicability_after_a_failed_run:
  if the transaction fails mid-way, ROLLBACK restores the pre-transaction state. The next run starts from a clean slate; no manual cleanup required.

3.3 What is NOT in this DDL

no_CHECK_constraint_on_authority: TRUE (GPT Q3 — deferred to Phase β alongside vocab table)
no_CHECK_constraint_on_canonical_address_format_version: TRUE (deferred to Phase γ)
no_FOREIGN_KEY_from_alias_target_unit_id_to_tac_logical_unit_id: TRUE (GPT Q2 — soft reference only)
no_sandbox_tac_canonical_address_alias_mirror: TRUE (GPT Q4 — omit)
no_NOT_NULL_promotion_on_authority: TRUE (deferred to Phase β write hook)
no_identity_profile_jsonb_cleanup: TRUE (GPT Q6 — cosmetic; defer)
no_change_to_public.tac_logical_unit.canonical_address: TRUE (SSOT preserved)
no_change_to_any_sister_table: TRUE (Option D scope restriction)
no_change_to_v0_1_cutter_governance_tables: TRUE (decision_backlog_entry, dot_pair_signature, cut_change_set, cut_change_set_affected_row, verify_result remain empty and unchanged)
no_change_to_any_function_or_trigger: TRUE
no_GRANT_REVOKE: TRUE (existing privileges preserved)
no_RLS_policy: TRUE
no_publication_or_replication_change: TRUE
no_directus_collection_metadata_update: TRUE (separate operation if needed; out of Phase α)

§4 — Trigger Side-Effect Acknowledgement

trigger_that_fires_during_step_3_backfill: trg_tac_birth_gate_lu (BEFORE UPDATE FOR EACH ROW → fn_tac_birth_gate_lu)
documented_side_effect: NEW.updated_at := now() — all 86 rows have their updated_at bumped to backfill execution time
accepted_as_normal_UPDATE_semantics: TRUE
mitigation_considered_but_NOT_chosen: SET LOCAL session_replication_role='replica' to bypass trigger — REJECTED because it would also skip the canonical_address / doc_code / parent_id / section_type / owner / sort_order validation re-checks. Re-validation is positive (defensive) and worth the timestamp bump.

§5 — Open Items for GPT Review

items_for_gpt_review:
  - is the single-transaction wrap acceptable for the alias table + 86 backfilled rows? (yes is recommended for atomic rollback)
  - is the trailing SELECT acceptable inside the transaction or should it be moved to a post-transaction verification step? (recommendation: KEEP inside; aids in-flight verification; harmless if rolled back)
  - is the IF NOT EXISTS idempotency posture acceptable? (recommendation: yes; matches v0.1 dry-run DDL pattern)
  - is the updated_at bump on backfilled rows acceptable? (recommendation: yes; normal UPDATE semantics)
  - is bumping idle_in_transaction_session_timeout to 60s sufficient? (recommendation: yes; the transaction body executes in well under 1s based on data scale)

§6 — File Authoring Convention

file_extension: .sql.md
reason: this is a Markdown file containing SQL inside fenced code blocks (NOT an executable .sql script).
to_extract_executable_SQL_(future_session_only): copy the §2 contents into a true .sql file under explicit prompt; the .sql file gets its own sha256 captured at GPT-review time and locked through dry-run + production.

§7 — Hard Boundaries

ddl_authored: TRUE
ddl_executed: FALSE
ddl_dispatched_to_any_database: FALSE
mutation_performed: FALSE
dry_run_started: FALSE
production_migration_allowed: FALSE
self_advance_to_execution: PROHIBITED
output_form: phase_alpha_ddl_draft_review_only

§8 — Cross-References

fn_inspection:        knowledge/dev/laws/dieu44-trien-khai/v0.2-ddl-authoring/dot-iu-cutter-v0.2-phase-alpha-fn-tac-birth-gate-lu-inspection-2026-05-15.md
verification_plan:    knowledge/dev/laws/dieu44-trien-khai/v0.2-ddl-authoring/dot-iu-cutter-v0.2-phase-alpha-ddl-verification-plan-2026-05-15.md
rollback_draft:       knowledge/dev/laws/dieu44-trien-khai/v0.2-ddl-authoring/dot-iu-cutter-v0.2-phase-alpha-rollback-draft-2026-05-15.md
authoring_report:     knowledge/dev/laws/dieu44-trien-khai/v0.2-ddl-authoring/dot-iu-cutter-v0.2-phase-alpha-ddl-authoring-report-2026-05-15.md

phase_α_design_master: knowledge/dev/laws/dieu44-trien-khai/v0.2-design/dot-iu-cutter-v0.2-phase-alpha-design-master-2026-05-15.md
schema_design:         knowledge/dev/laws/dieu44-trien-khai/v0.2-design/dot-iu-cutter-v0.2-phase-alpha-canonical-address-schema-design-2026-05-15.md
alias_design:          knowledge/dev/laws/dieu44-trien-khai/v0.2-design/dot-iu-cutter-v0.2-phase-alpha-canonical-address-alias-design-2026-05-15.md
dry_run_plan:          knowledge/dev/laws/dieu44-trien-khai/v0.2-design/dot-iu-cutter-v0.2-phase-alpha-dry-run-plan-2026-05-15.md
risk_review_plan:      knowledge/dev/laws/dieu44-trien-khai/v0.2-design/dot-iu-cutter-v0.2-phase-alpha-risk-review-plan-2026-05-15.md

br_4_closure:          knowledge/dev/laws/dieu44-trien-khai/v0.2-planning/dot-iu-cutter-v0.2-br-4-authority-backfill-rule-closure-2026-05-15.md
br_5_closure:          knowledge/dev/laws/dieu44-trien-khai/v0.2-planning/dot-iu-cutter-v0.2-br-5-canonical-address-v1-ratification-closure-2026-05-15.md

v0_1_production_handoff: knowledge/dev/laws/dieu44-trien-khai/execution/dot-iu-cutter-v0.1-production-handoff-status-2026-05-15.md
v0_1_HB_05_dry_run_DDL_(pattern_reference): /opt/incomex/backups/dieu44_hb05_2026-05-15/schema/cutter_governance_dryrun_v0.1.sql  (sha256 9eb032e2…)

End of Phase α DDL draft.

Back to Knowledge Hub knowledge/dev/laws/dieu44-trien-khai/v0.2-ddl-authoring/dot-iu-cutter-v0.2-phase-alpha-ddl-draft-2026-05-15.sql.md