Incomex AI Portal
KB-7702

P9 G6 — Migration Dry-Run Package v0.2

70 min read Revision 1
dieu38p9g6dry-runpackagetier3s184draftv0.2

P9 G6 — Migration Dry-Run Package

Loại: Migration dry-run execution package — G6 of P9 Entry Gate Tier 3 Phase: P9 Entry Gate, Tier 3, Gate G6 Trạng thái: DRAFT v0.2 — patches v0.1 theo GPT R1 review (6 patches). Chờ GPT R2. Phiên: S184 | Ngày: 2026-04-27 Agent soạn: Opus 4.7 (Desktop) Inputs đã verify: P5 v0.2, P5b v0.2 (DEFER), P6 v0.2, P7 v0.2, P8 v0.4, Tier 3 Readiness, P9-G6-source-extraction-note v0.1, GPT R1 review (reports/gpt-review-g6-dry-run-package-r1-2026-04-27.md) Operating model: User = mục tiêu/yes-no. Opus = điều hành + soạn. GPT = giám sát + phản biện.

CẢNH BÁO LỚN: Package này là plan + DDL design, KHÔNG phải execution prompt cho agent. Sau khi GPT + User PASS package, sẽ soạn execution prompt riêng giao agent thực thi trên VPS.

1. Mục tiêu G6

G6 = Migration Dry-Run trên schema isolated p9_g6_dryrun.

Chứng minh ba điều:

  1. Schema design 14 tables (P8 §1.4) compile được trên PostgreSQL 16 thật, không lỗi cú pháp/FK/CHECK.
  2. Functions/triggers cần thiết compile được (skeleton + behavior-critical, không cần test runtime đầy đủ).
  3. Rollback DROP SCHEMA p9_g6_dryrun CASCADE để lại 0 residue trong production.

Sau khi G6 PASS → mở G8 (Directus roles tac-agent/tac-admin) → G11 (User approval) → P9 production migration thật.

2. Scope / Out-of-scope

2.1 Trong scope G6

  • CREATE SCHEMA p9_g6_dryrun
  • CREATE 14 tables trong p9_g6_dryrun.* (production-faithful theo P8 §1.1 hardening: FK vocab thay CHECK enum)
  • CREATE 6 functions/triggers compile + behavior-critical trong p9_g6_dryrun.*
  • Seed full vocab values vào 7 vocab tables + 1 config table (61 rows total)
  • Verification plan: DDL exists, FK/CHECK constraints bind, function signature OK, trigger ATTACH OK, seed SHA-256 khớp KB
  • Constraint behavior smoke (test 1 violation per major constraint type — exist + violation cả hai)
  • Rollback test: DROP CASCADE + verify 0 residue
  • Action log: KB markdown report + local artifacts only (KHÔNG ghi dot_action_log table — patched v0.2 GPT R1#3)
  • Pre-flight checks (schema chưa tồn tại, public schema sạch tac_ mọi loại object* — patched v0.2 GPT R1#4, FAC-07/08/09 sẵn, 19 DOTs sẵn, fn_tac_log_checker_issue sẵn, backup tươi)

2.2 Out-of-scope G6 (CẤM tuyệt đối)

  • ❌ CREATE/ALTER/DROP public.tac_* (production, mọi loại object)
  • ❌ CREATE/MODIFY Directus roles (tac-agent, tac-admin) — thuộc G8
  • ❌ CREATE/MODIFY Directus collections — thuộc G8
  • ❌ Activate cron jobs cho DOT-TAC-* — thuộc P9 production
  • ❌ INSERT vào taxonomy_labels / entity_labels
  • ❌ INSERT vào system_issues (kể cả qua fn_tac_log_checker_issue)
  • ❌ INSERT vào dot_action_log (production audit table — patched v0.2 GPT R1#3)
  • ❌ Cleanup _dot_origin field
  • ❌ Behavior test functions/triggers ngoài V3 smoke (chỉ compile + smoke)
  • ❌ Test runtime trên 76 sandbox units
  • ❌ Mọi mutation lên public.* ngoài bản thân DROP SCHEMA p9_g6_dryrun
  • ❌ Modify dot_tools / taxonomy_facets
  • ❌ Sửa GSM secrets
  • ❌ CREATE EXTENSION pgcrypto (DB-level mutation — patched v0.2 GPT R1#2). G6 dùng built-in pg_catalog.sha256() thay digest().

3. Target schema design

3.1 Schema name + lý do

p9_g6_dryrun — chọn schema mới, KHÔNG reuse sandbox_tac.

Tiêu chí p9_g6_dryrun (mới) sandbox_tac (reuse)
Sạch, zero residue trước run ✅ ❌ Có 76 units từ P7
Naming rõ phase ✅ p9.g6.dryrun ❌ generic
ROLLBACK đơn giản ✅ DROP SCHEMA CASCADE ❌ Cần truncate selective
Verify 0 residue dễ ✅ schema biến mất ❌ Phải so trước/sau
Risk write nhầm Thấp (schema không tồn tại trước) Trung bình (đã có data)

3.2 Naming convention trong p9_g6_dryrun.*

Tên bảng/function giữ ĐÚNG như production target (tac_*) — vì G6 mục đích chứng minh DDL production-ready. Khác biệt chỉ ở schema prefix:

  • G6: p9_g6_dryrun.tac_logical_unit
  • Production target: public.tac_logical_unit

Mọi DDL statement BẮT BUỘC schema-qualify đầy đủ. Không bao giờ chạy CREATE TABLE tac_* (không có schema prefix) — vì search_path có thể lệch dẫn tới ghi nhầm public.

3.3 Search path

Connection setup cho dry-run:

SET search_path TO p9_g6_dryrun, pg_catalog;
-- Loại bỏ public khỏi search_path để mọi unqualified reference fail-loud.
-- pg_catalog vẫn cần cho built-in functions (sha256, encode, convert_to, gen_random_uuid, etc.)

4. Pre-flight checks (MANDATORY trước khi chạy DDL)

Theo bài học S183 AP-DRY-RUN-FK + handoff §3.3 + GPT R1 #4 (mở rộng public conflict check).

4.1 Pre-flight checks list

# Check SQL/Method Expected If FAIL
PF-01 Schema p9_g6_dryrun chưa tồn tại SELECT count(*) FROM information_schema.schemata WHERE schema_name='p9_g6_dryrun'; 0 DROP SCHEMA p9_g6_dryrun CASCADE → re-check
PF-02 Production public.* không có tac_* object thuộc bất kỳ loại (tables/views/sequences/types/functions/triggers, trừ whitelisted) — patched v0.2 GPT R1#4 Query gộp UNION ALL (xem §4.1.1 dưới) 0 rows, NULL array STOP — production conflict, escalate
PF-03 pg_catalog.sha256 built-in tồn tại (PG 11+) — patched v0.2 GPT R1#2 SELECT count(*) FROM pg_proc p JOIN pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname='pg_catalog' AND p.proname='sha256'; ≥ 1 (PG 16 có built-in) STOP — version PG quá cũ, escalate
PF-04 FAC-07/08/09 sẵn trong production taxonomy_facets Directus API GET /items/taxonomy_facets?filter[code][_in]=FAC-07,FAC-08,FAC-09&filter[status][_eq]=active 3 rows, ids 8/9/10 STOP — Đ24 dependency missing
PF-05 19 DOT-TAC-* sẵn trong dot_tools Directus API GET /items/dot_tools?filter[code][_starts_with]=DOT-TAC-&filter[status][_eq]=active 19 rows, ids 971–989 STOP — Đ35 dependency missing
PF-06 public.fn_tac_log_checker_issue sẵn (whitelisted E-R3 artifact) SELECT proname, prosecdef FROM pg_proc WHERE proname='fn_tac_log_checker_issue' AND pronamespace='public'::regnamespace; 1 row, prosecdef=true STOP — checker adapter missing
PF-07 Backup tươi (< 6h) ls -la /opt/incomex/backup/postgres-*.sql.gz | tail -1 (Bash via SSH) hoặc rclone lsl GDrive:incomex-backup/ mtime < 6h ago STOP — chạy backup tay trước
PF-08 TRIGGER-GUARD informational mode Verify event triggers permit CREATE SCHEMA Permits STOP — guard configuration issue
PF-09 Connection identity hợp lệ qua .env (KHÔNG hardcode user) — patched v0.2 GPT R1#5 psql "$(cat .env_db_url)" -c 'SELECT current_user, current_database();' → verify user có CREATE SCHEMA permission, db=directus user thuộc danh sách hợp lệ (workflow_admin, hoặc admin-tier user); db=directus STOP — wrong connection / insufficient permission
PF-10 Search path verify SHOW search_path; Set theo §3.3 trước run Adjust

4.1.1 PF-02 — Mở rộng public conflict check (full SQL)

-- PF-02: No tac_* objects in public schema (except whitelisted fn_tac_log_checker_issue)
-- Patched v0.2 GPT R1#4
WITH tac_objects_in_public AS (
    SELECT 'table' AS object_type, table_name AS object_name
      FROM information_schema.tables 
     WHERE table_schema = 'public' 
       AND table_name LIKE 'tac\_%' ESCAPE '\'
       AND table_type = 'BASE TABLE'
    UNION ALL
    SELECT 'view', table_name 
      FROM information_schema.views
     WHERE table_schema = 'public' 
       AND table_name LIKE 'tac\_%' ESCAPE '\'
    UNION ALL
    SELECT 'sequence', sequence_name 
      FROM information_schema.sequences
     WHERE sequence_schema = 'public' 
       AND sequence_name LIKE 'tac\_%' ESCAPE '\'
    UNION ALL
    SELECT 'type', t.typname 
      FROM pg_type t 
      JOIN pg_namespace n ON n.oid = t.typnamespace
     WHERE n.nspname = 'public' 
       AND t.typname LIKE 'tac\_%' ESCAPE '\'
       AND t.typtype IN ('c', 'e', 'd')  -- composite, enum, domain
    UNION ALL
    SELECT 'function', p.proname 
      FROM pg_proc p 
      JOIN pg_namespace n ON n.oid = p.pronamespace
     WHERE n.nspname = 'public' 
       AND p.proname LIKE 'fn_tac\_%' ESCAPE '\'
       AND p.proname != 'fn_tac_log_checker_issue'  -- whitelisted (E-R3)
    UNION ALL
    SELECT 'trigger', tg.tgname 
      FROM pg_trigger tg
      JOIN pg_class c ON c.oid = tg.tgrelid
      JOIN pg_namespace n ON n.oid = c.relnamespace
     WHERE n.nspname = 'public' 
       AND tg.tgname LIKE 'trg_tac\_%' ESCAPE '\'
       AND NOT tg.tgisinternal
)
SELECT 
    count(*) AS total_violations,
    array_agg(object_type || ':' || object_name ORDER BY object_type, object_name) AS objects
  FROM tac_objects_in_public;

-- Expected: total_violations = 0, objects = NULL
-- If total_violations > 0 → STOP, log objects, escalate

Whitelist: Chỉ public.fn_tac_log_checker_issue (E-R3 production artifact, S183).

4.2 Pre-flight FK vocabulary verify (AP-DRY-RUN-FK mitigation)

Trước khi tạo CORE tables tham chiếu vocab tables, verify mỗi FK target sau seed:

Core column FK target Verify
tac_logical_unit.lifecycle_status tac_lu_lifecycle_vocab(code) Sau seed §7.2, query vocab table count = 3 (active/draft_only/retired)
tac_logical_unit.section_type tac_section_type_vocab(code) Sau seed §7.2, count = 17
tac_unit_version.lifecycle_status tac_uv_lifecycle_vocab(code) count = 4
tac_unit_version.review_state tac_review_state_vocab(code) count = 5
tac_publication.lifecycle_status tac_pub_lifecycle_vocab(code) count = 4
tac_publication.publication_type tac_publication_type_vocab(code) count = 10
tac_change_set.lifecycle_status tac_cs_lifecycle_vocab(code) count = 7
tac_publication_member.publication_id tac_publication(id) Empty (no rows yet) — chỉ verify FK constraint exists
tac_publication_member.unit_version_id tac_unit_version(id) Empty — verify FK exists
tac_change_set_member.change_set_id tac_change_set(id) Empty — verify FK exists

5. DDL plan — 14 tables (production-faithful)

Quy tắc: Mọi DDL dùng p9_g6_dryrun.{table}. KHÔNG bao giờ unqualified.

5.1 Schema bootstrap

-- Pre-flight: assume PF-01→PF-10 passed
CREATE SCHEMA p9_g6_dryrun;
COMMENT ON SCHEMA p9_g6_dryrun IS
  'P9 Gate G6 dry-run isolated schema. ROLLBACK: DROP SCHEMA p9_g6_dryrun CASCADE. Phiên S184. NOT FOR PRODUCTION.';

SET search_path TO p9_g6_dryrun, pg_catalog;

5.2 Group A — 5 lifecycle vocab tables

-- A1. tac_lu_lifecycle_vocab
CREATE TABLE p9_g6_dryrun.tac_lu_lifecycle_vocab (
    code              TEXT PRIMARY KEY,
    name              TEXT NOT NULL,
    description       TEXT,
    sort_order        INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- A2. tac_uv_lifecycle_vocab
CREATE TABLE p9_g6_dryrun.tac_uv_lifecycle_vocab (
    code              TEXT PRIMARY KEY,
    name              TEXT NOT NULL,
    description       TEXT,
    sort_order        INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- A3. tac_review_state_vocab
CREATE TABLE p9_g6_dryrun.tac_review_state_vocab (
    code              TEXT PRIMARY KEY,
    name              TEXT NOT NULL,
    description       TEXT,
    sort_order        INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- A4. tac_pub_lifecycle_vocab
CREATE TABLE p9_g6_dryrun.tac_pub_lifecycle_vocab (
    code              TEXT PRIMARY KEY,
    name              TEXT NOT NULL,
    description       TEXT,
    sort_order        INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- A5. tac_cs_lifecycle_vocab
CREATE TABLE p9_g6_dryrun.tac_cs_lifecycle_vocab (
    code              TEXT PRIMARY KEY,
    name              TEXT NOT NULL,
    description       TEXT,
    sort_order        INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
    created_at        TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at        TIMESTAMPTZ NOT NULL DEFAULT now()
);

5.3 Group B — 2 type vocab tables

-- B1. tac_section_type_vocab (per P5 §5.5)
CREATE TABLE p9_g6_dryrun.tac_section_type_vocab (
    code                TEXT PRIMARY KEY,
    name                TEXT NOT NULL,
    description         TEXT,
    lifecycle_status    TEXT NOT NULL DEFAULT 'active'
        CHECK (lifecycle_status IN ('active', 'deprecated', 'retired')),
    owner               TEXT,
    soft_limit_words    INTEGER NOT NULL DEFAULT 500 CHECK (soft_limit_words > 0),
    hard_limit_words    INTEGER NOT NULL DEFAULT 1500 CHECK (hard_limit_words > soft_limit_words),
    description_required BOOLEAN NOT NULL DEFAULT TRUE,
    body_required       BOOLEAN NOT NULL DEFAULT TRUE,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- B2. tac_publication_type_vocab (per P5 §5.6)
CREATE TABLE p9_g6_dryrun.tac_publication_type_vocab (
    code                TEXT PRIMARY KEY,
    name                TEXT NOT NULL,
    description         TEXT,
    lifecycle_status    TEXT NOT NULL DEFAULT 'active'
        CHECK (lifecycle_status IN ('active', 'deprecated', 'retired')),
    default_risk_tier   TEXT NOT NULL DEFAULT 'medium'
        CHECK (default_risk_tier IN ('low', 'medium', 'high', 'highest')),
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

5.4 Group C — 1 config table

-- C1. tac_birth_gate_config (per P8 §2.3)
CREATE TABLE p9_g6_dryrun.tac_birth_gate_config (
    checker_id          TEXT PRIMARY KEY,
        -- Ví dụ: 'BG-LU-02', 'BG-UV-04'.
    mode                TEXT NOT NULL DEFAULT 'block'
        CHECK (mode IN ('block', 'warn')),
    enabled             BOOLEAN NOT NULL DEFAULT TRUE,
    rationale           TEXT,
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

5.5 Group D — 4 core tables

-- D1. tac_logical_unit (per P5 §5.1, P8 §1.1 hardening)
CREATE TABLE p9_g6_dryrun.tac_logical_unit (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    canonical_address   TEXT NOT NULL UNIQUE,
        -- INV-ADDRESS-IMMUT. Format: D38-{DOC}-{(ROOT|S{n}-P{n}...)}
        -- Regex enforced by birth gate fn_tac_birth_gate_lu, KHÔNG CHECK constraint
        -- (vì regex P8 §5.2 D38-local có thể evolve).
    
    doc_code            TEXT NOT NULL,
        -- Structural binding, KHÔNG FK (P5 DP-1, OD-P5-04).
    parent_id           UUID REFERENCES p9_g6_dryrun.tac_logical_unit(id),
    sort_order          INTEGER NOT NULL DEFAULT 0 CHECK (sort_order >= 0),
    
    section_type        TEXT NOT NULL
        REFERENCES p9_g6_dryrun.tac_section_type_vocab(code),
    section_code        TEXT,
    owner               TEXT NOT NULL,
    
    identity_profile    JSONB NOT NULL DEFAULT '{}'::jsonb,
    
    tier                TEXT,
        -- DERIVED, agent KHÔNG khai tay.
    lifecycle_status    TEXT NOT NULL DEFAULT 'draft_only'
        REFERENCES p9_g6_dryrun.tac_lu_lifecycle_vocab(code),
    
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_tac_lu_doc_code ON p9_g6_dryrun.tac_logical_unit(doc_code);
CREATE INDEX idx_tac_lu_parent ON p9_g6_dryrun.tac_logical_unit(parent_id) WHERE parent_id IS NOT NULL;
CREATE INDEX idx_tac_lu_section_type ON p9_g6_dryrun.tac_logical_unit(section_type);
CREATE INDEX idx_tac_lu_lifecycle ON p9_g6_dryrun.tac_logical_unit(lifecycle_status);
CREATE INDEX idx_tac_lu_identity_profile_gin ON p9_g6_dryrun.tac_logical_unit USING GIN (identity_profile);

COMMENT ON TABLE p9_g6_dryrun.tac_logical_unit IS
  'Logical unit identity (P5 §5.1 + P8 §1.1 hardening). G6 dry-run only.';

-- D2. tac_unit_version (per P5 §5.2, P8 §1.1 hardening)
CREATE TABLE p9_g6_dryrun.tac_unit_version (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    logical_unit_id     UUID NOT NULL REFERENCES p9_g6_dryrun.tac_logical_unit(id),
    
    version_number      INTEGER NOT NULL DEFAULT 1 CHECK (version_number > 0),
    title               TEXT NOT NULL,
    body                TEXT,
    description         TEXT,
    
    content_hash        TEXT,
        -- DERIVED by fn_tac_uv_compute_derived (BEFORE INSERT/UPDATE).
        -- Algorithm: pg_catalog.sha256(convert_to(...)) — built-in PG 11+.
        -- KHÔNG dùng pgcrypto digest() để tránh extension dependency (v0.2 GPT R1#2).
    
    lifecycle_status    TEXT NOT NULL DEFAULT 'draft'
        REFERENCES p9_g6_dryrun.tac_uv_lifecycle_vocab(code),
    review_state        TEXT NOT NULL DEFAULT 'unreviewed'
        REFERENCES p9_g6_dryrun.tac_review_state_vocab(code),
    
    -- Length flag GIỮ CHECK (P8 §1.1 chú thích "Cố định technical")
    length_flag         TEXT NOT NULL DEFAULT 'normal'
        CHECK (length_flag IN ('normal', 'soft_limit', 'hard_limit')),
    length_exception_reason TEXT,
    
    content_profile     JSONB NOT NULL DEFAULT '{}'::jsonb,
    
    editor              TEXT,
    provenance          TEXT NOT NULL DEFAULT 'PROV-AI',
        -- Verify FAC-PROV vocab via DOT-TAC-LABEL-FACET-VERIFY post-G6.
    
    -- Vector projection hooks (P5 §5.2)
    -- vector_sync_status GIỮ CHECK 4 values (P5 enum hiện hành).
    -- 'skip' từ P5b cần amend P5 trước khi thêm — out of G6 scope.
    vector_sync_status  TEXT NOT NULL DEFAULT 'pending'
        CHECK (vector_sync_status IN ('pending', 'synced', 'stale', 'error')),
    vector_synced_at    TIMESTAMPTZ,
    vector_chunk_count  INTEGER NOT NULL DEFAULT 0 CHECK (vector_chunk_count >= 0),
    
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    enacted_at          TIMESTAMPTZ,
    
    UNIQUE (logical_unit_id, version_number)
);

CREATE INDEX idx_tac_uv_logical_unit ON p9_g6_dryrun.tac_unit_version(logical_unit_id);
CREATE INDEX idx_tac_uv_lifecycle ON p9_g6_dryrun.tac_unit_version(lifecycle_status);
CREATE INDEX idx_tac_uv_review_state ON p9_g6_dryrun.tac_unit_version(review_state);
CREATE INDEX idx_tac_uv_vector_sync ON p9_g6_dryrun.tac_unit_version(vector_sync_status)
    WHERE vector_sync_status != 'synced';
CREATE INDEX idx_tac_uv_content_profile_gin ON p9_g6_dryrun.tac_unit_version USING GIN (content_profile);

COMMENT ON TABLE p9_g6_dryrun.tac_unit_version IS
  'Unit version content (P5 §5.2 + P8 §1.1 hardening). G6 dry-run only.';

-- D3. tac_publication (per P5 §5.3, P8 §1.1 hardening)
CREATE TABLE p9_g6_dryrun.tac_publication (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    doc_code            TEXT NOT NULL,
    version             TEXT NOT NULL,
    
    publication_type    TEXT NOT NULL
        REFERENCES p9_g6_dryrun.tac_publication_type_vocab(code),
    name                TEXT NOT NULL,
    owner               TEXT NOT NULL,
    description         TEXT,
    
    lifecycle_status    TEXT NOT NULL DEFAULT 'proposed'
        REFERENCES p9_g6_dryrun.tac_pub_lifecycle_vocab(code),
    
    enacted_at          TIMESTAMPTZ,
    council_score       NUMERIC,
    approved_by         TEXT,
    
    risk_tier           TEXT NOT NULL DEFAULT 'medium'
        CHECK (risk_tier IN ('low', 'medium', 'high', 'highest')),
    
    publication_profile JSONB NOT NULL DEFAULT '{}'::jsonb,
    
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    
    UNIQUE (doc_code, version)
);

CREATE INDEX idx_tac_pub_doc_code ON p9_g6_dryrun.tac_publication(doc_code);
CREATE INDEX idx_tac_pub_lifecycle ON p9_g6_dryrun.tac_publication(lifecycle_status);
CREATE INDEX idx_tac_pub_publication_type ON p9_g6_dryrun.tac_publication(publication_type);
CREATE INDEX idx_tac_pub_publication_profile_gin ON p9_g6_dryrun.tac_publication USING GIN (publication_profile);

COMMENT ON TABLE p9_g6_dryrun.tac_publication IS
  'Publication record (P5 §5.3 + P8 §1.1 hardening). G6 dry-run only.';

-- D4. tac_publication_member (per P5 §5.4)
CREATE TABLE p9_g6_dryrun.tac_publication_member (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    publication_id      UUID NOT NULL REFERENCES p9_g6_dryrun.tac_publication(id),
    logical_unit_id     UUID NOT NULL REFERENCES p9_g6_dryrun.tac_logical_unit(id),
    unit_version_id     UUID NOT NULL REFERENCES p9_g6_dryrun.tac_unit_version(id),
    
    render_order        INTEGER NOT NULL DEFAULT 0 CHECK (render_order >= 0),
    
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    
    UNIQUE (publication_id, logical_unit_id)
);

CREATE INDEX idx_tac_pm_publication ON p9_g6_dryrun.tac_publication_member(publication_id);
CREATE INDEX idx_tac_pm_unit_version ON p9_g6_dryrun.tac_publication_member(unit_version_id);
CREATE INDEX idx_tac_pm_logical_unit ON p9_g6_dryrun.tac_publication_member(logical_unit_id);
-- NOTE (v0.2 GPT R1#1): Partial index on enacted publications optimization DEFERRED.
-- Rationale: PG không cho subquery trong WHERE của partial index predicate.
-- Enacted lock được enforce bằng trigger fn_tac_pm_enacted_lock (§6.6), không cần partial index.
-- Nếu future cần optimize query enacted-only → thêm denormalized column hoặc materialized view.

COMMENT ON TABLE p9_g6_dryrun.tac_publication_member IS
  'Junction publication ↔ unit_version (P5 §5.4). G6 dry-run only.';

5.6 Group E — 2 change-set hook tables

-- E1. tac_change_set (per P5 §5.7, P8 §1.1 hardening)
CREATE TABLE p9_g6_dryrun.tac_change_set (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    
    publication_id      UUID REFERENCES p9_g6_dryrun.tac_publication(id),
    scope_description   TEXT NOT NULL,
    
    lifecycle_status    TEXT NOT NULL DEFAULT 'draft'
        REFERENCES p9_g6_dryrun.tac_cs_lifecycle_vocab(code),
    
    apr_ref             TEXT,
    owner               TEXT NOT NULL,
    
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    submitted_at        TIMESTAMPTZ,
    enacted_at          TIMESTAMPTZ
);

CREATE INDEX idx_tac_cs_publication ON p9_g6_dryrun.tac_change_set(publication_id) WHERE publication_id IS NOT NULL;
CREATE INDEX idx_tac_cs_lifecycle ON p9_g6_dryrun.tac_change_set(lifecycle_status);
-- Partial index per P8 §1.2: lifecycle_status='enacted' (HỢP LỆ — predicate constant, không subquery)
CREATE INDEX idx_tac_cs_lifecycle_enacted ON p9_g6_dryrun.tac_change_set(id)
    WHERE lifecycle_status = 'enacted';

-- E2. tac_change_set_member (per P5 §5.8)
CREATE TABLE p9_g6_dryrun.tac_change_set_member (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    change_set_id       UUID NOT NULL REFERENCES p9_g6_dryrun.tac_change_set(id),
    logical_unit_id     UUID NOT NULL REFERENCES p9_g6_dryrun.tac_logical_unit(id),
    
    change_type         TEXT NOT NULL
        CHECK (change_type IN ('create', 'new_version', 'retire', 'structural')),
    old_version_id      UUID REFERENCES p9_g6_dryrun.tac_unit_version(id),
    new_version_id      UUID REFERENCES p9_g6_dryrun.tac_unit_version(id),
    
    snapshot_data       JSONB,
    
    created_at          TIMESTAMPTZ NOT NULL DEFAULT now(),
    
    UNIQUE (change_set_id, logical_unit_id)
);

CREATE INDEX idx_tac_csm_change_set ON p9_g6_dryrun.tac_change_set_member(change_set_id);
CREATE INDEX idx_tac_csm_logical_unit ON p9_g6_dryrun.tac_change_set_member(logical_unit_id);

5.7 Tổng số object DDL group

Group Tables Indexes UNIQUE constraints FK constraints CHECK constraints
A (5 lifecycle vocab) 5 0 (PK chỉ) 5 PK 0 5 (sort_order >= 0)
B (2 type vocab) 2 0 (PK chỉ) 2 PK 0 7 (lifecycle, risk_tier, soft/hard_limit, etc.)
C (1 config) 1 0 1 PK 0 1 (mode)
D (4 core) 4 13 + 4 GIN/PK 4 PK + 1 (uv unique) + 1 (pm unique) 8 (FK chain) ~10 (CHECK riêng)
E (2 change-set) 2 4 (gồm 1 partial hợp lệ) 2 PK + 1 (csm unique) 4 (FK chain) 1 (change_type)
Tổng 14 ~21 ~17 ~12 ~24

Patched v0.2 GPT R1#1: Bỏ invalid partial index idx_tac_pm_pub_enacted. Tổng index giảm 1 (từ ~22 xuống ~21). Partial idx_tac_cs_lifecycle_enacted giữ vì predicate lifecycle_status='enacted' là constant, hợp lệ.

6. Functions/triggers — compile + behavior-critical

Patched v0.2 GPT R1#2: Tất cả function dùng built-in pg_catalog.sha256(bytea) (PG 11+) thay vì pgcrypto.digest(). Không cần pgcrypto extension. Schema-qualified pg_catalog.* để search_path không lệch.

6.1 fn_tac_uv_compute_derived

CREATE OR REPLACE FUNCTION p9_g6_dryrun.fn_tac_uv_compute_derived()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = p9_g6_dryrun, pg_catalog
AS $$
DECLARE
    v_word_count    INTEGER;
    v_soft_limit    INTEGER;
    v_hard_limit    INTEGER;
BEGIN
    -- Compute content_hash (sha256 của title+body+description+content_profile)
    -- Built-in pg_catalog.sha256(bytea) — không cần pgcrypto extension (v0.2 GPT R1#2)
    NEW.content_hash := pg_catalog.encode(
        pg_catalog.sha256(
            pg_catalog.convert_to(
                coalesce(NEW.title, '') || '|' ||
                coalesce(NEW.body, '') || '|' ||
                coalesce(NEW.description, '') || '|' ||
                coalesce(NEW.content_profile::text, '{}'),
                'UTF8'
            )
        ),
        'hex'
    );
    
    -- Compute length_flag (theo section_type thresholds)
    SELECT st.soft_limit_words, st.hard_limit_words
      INTO v_soft_limit, v_hard_limit
      FROM p9_g6_dryrun.tac_section_type_vocab st
      JOIN p9_g6_dryrun.tac_logical_unit lu
        ON lu.section_type = st.code
     WHERE lu.id = NEW.logical_unit_id;
    
    v_word_count := pg_catalog.array_length(
        pg_catalog.regexp_split_to_array(coalesce(NEW.body, ''), '\s+'),
        1
    );
    
    NEW.length_flag := CASE
        WHEN v_word_count IS NULL OR v_word_count = 0 THEN 'normal'
        WHEN v_word_count > v_hard_limit THEN 'hard_limit'
        WHEN v_word_count > v_soft_limit THEN 'soft_limit'
        ELSE 'normal'
    END;
    
    -- updated_at auto
    NEW.updated_at := now();
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_tac_uv_compute_derived
    BEFORE INSERT OR UPDATE
    ON p9_g6_dryrun.tac_unit_version
    FOR EACH ROW
    EXECUTE FUNCTION p9_g6_dryrun.fn_tac_uv_compute_derived();

6.2 fn_tac_birth_gate_lu (BG-LU-01→06 skeleton + behavior-critical)

CREATE OR REPLACE FUNCTION p9_g6_dryrun.fn_tac_birth_gate_lu()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = p9_g6_dryrun, pg_catalog
AS $$
DECLARE
    v_section_active BOOLEAN;
    v_parent_doc_code TEXT;
BEGIN
    -- BG-LU-01: canonical_address regex (D38-local contract per P8 §5.2)
    -- Regex: ^D38-[A-Z0-9]+-((ROOT)|(S[0-9]+(-P[0-9]+(-[0-9]+)*)?))$
    IF NEW.canonical_address !~ '^D38-[A-Z0-9]+-((ROOT)|(S[0-9]+(-P[0-9]+(-[0-9]+)*)?))$' THEN
        RAISE EXCEPTION 'BG-LU-01: canonical_address % không khớp regex D38-local', NEW.canonical_address
            USING ERRCODE = 'check_violation';
    END IF;
    
    -- BG-LU-02: doc_code valid — deferred OD-P5-04, hiện chỉ check non-null
    IF NEW.doc_code IS NULL OR pg_catalog.length(pg_catalog.btrim(NEW.doc_code)) = 0 THEN
        RAISE EXCEPTION 'BG-LU-02: doc_code rỗng' USING ERRCODE = 'not_null_violation';
    END IF;
    
    -- BG-LU-03: parent same doc_code
    IF NEW.parent_id IS NOT NULL THEN
        SELECT doc_code INTO v_parent_doc_code
          FROM p9_g6_dryrun.tac_logical_unit
         WHERE id = NEW.parent_id;
        IF v_parent_doc_code IS NULL THEN
            RAISE EXCEPTION 'BG-LU-03: parent_id % không tồn tại', NEW.parent_id
                USING ERRCODE = 'foreign_key_violation';
        END IF;
        IF v_parent_doc_code != NEW.doc_code THEN
            RAISE EXCEPTION 'BG-LU-03: parent doc_code % != child doc_code %',
                v_parent_doc_code, NEW.doc_code
                USING ERRCODE = 'check_violation';
        END IF;
    END IF;
    
    -- BG-LU-04: section_type active
    SELECT (lifecycle_status = 'active') INTO v_section_active
      FROM p9_g6_dryrun.tac_section_type_vocab
     WHERE code = NEW.section_type;
    IF NOT coalesce(v_section_active, FALSE) THEN
        RAISE EXCEPTION 'BG-LU-04: section_type % không active', NEW.section_type
            USING ERRCODE = 'check_violation';
    END IF;
    
    -- BG-LU-05: owner present (covered by NOT NULL constraint, redundant safety)
    IF NEW.owner IS NULL OR pg_catalog.length(pg_catalog.btrim(NEW.owner)) = 0 THEN
        RAISE EXCEPTION 'BG-LU-05: owner rỗng' USING ERRCODE = 'not_null_violation';
    END IF;
    
    -- BG-LU-06: sort_order valid (covered by CHECK >= 0, redundant safety)
    IF NEW.sort_order IS NULL OR NEW.sort_order < 0 THEN
        RAISE EXCEPTION 'BG-LU-06: sort_order invalid' USING ERRCODE = 'check_violation';
    END IF;
    
    -- updated_at auto
    NEW.updated_at := now();
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_tac_birth_gate_lu
    BEFORE INSERT OR UPDATE
    ON p9_g6_dryrun.tac_logical_unit
    FOR EACH ROW
    EXECUTE FUNCTION p9_g6_dryrun.fn_tac_birth_gate_lu();

6.3 fn_tac_birth_gate_uv (BG-UV-01→06 skeleton + behavior-critical)

CREATE OR REPLACE FUNCTION p9_g6_dryrun.fn_tac_birth_gate_uv()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = p9_g6_dryrun, pg_catalog
AS $$
DECLARE
    v_section_type TEXT;
    v_desc_required BOOLEAN;
    v_body_required BOOLEAN;
    v_bg_uv_02_mode TEXT;
    v_bg_uv_03_mode TEXT;
BEGIN
    -- Get section_type config from logical_unit + section_type_vocab
    SELECT lu.section_type, st.description_required, st.body_required
      INTO v_section_type, v_desc_required, v_body_required
      FROM p9_g6_dryrun.tac_logical_unit lu
      JOIN p9_g6_dryrun.tac_section_type_vocab st ON st.code = lu.section_type
     WHERE lu.id = NEW.logical_unit_id;
    
    IF v_section_type IS NULL THEN
        RAISE EXCEPTION 'BG-UV: logical_unit_id % không tồn tại', NEW.logical_unit_id
            USING ERRCODE = 'foreign_key_violation';
    END IF;
    
    -- BG-UV-01: title present (covered by NOT NULL, redundant)
    IF NEW.title IS NULL OR pg_catalog.length(pg_catalog.btrim(NEW.title)) = 0 THEN
        RAISE EXCEPTION 'BG-UV-01: title rỗng' USING ERRCODE = 'not_null_violation';
    END IF;
    
    -- BG-UV-02: description per config (mode tiered từ tac_birth_gate_config)
    SELECT mode INTO v_bg_uv_02_mode
      FROM p9_g6_dryrun.tac_birth_gate_config
     WHERE checker_id = 'BG-UV-02';
    v_bg_uv_02_mode := coalesce(v_bg_uv_02_mode, 'block');
    
    IF v_desc_required AND (NEW.description IS NULL OR pg_catalog.length(pg_catalog.btrim(NEW.description)) = 0) THEN
        IF v_bg_uv_02_mode = 'block' THEN
            RAISE EXCEPTION 'BG-UV-02: description required cho section_type %', v_section_type
                USING ERRCODE = 'not_null_violation';
        ELSE
            RAISE WARNING 'BG-UV-02: description thiếu (warn mode)';
        END IF;
    END IF;
    
    -- BG-UV-03: body per config
    SELECT mode INTO v_bg_uv_03_mode
      FROM p9_g6_dryrun.tac_birth_gate_config
     WHERE checker_id = 'BG-UV-03';
    v_bg_uv_03_mode := coalesce(v_bg_uv_03_mode, 'warn');
    
    IF v_body_required AND (NEW.body IS NULL OR pg_catalog.length(pg_catalog.btrim(NEW.body)) = 0) THEN
        IF v_bg_uv_03_mode = 'block' THEN
            RAISE EXCEPTION 'BG-UV-03: body required cho section_type %', v_section_type
                USING ERRCODE = 'not_null_violation';
        ELSE
            RAISE WARNING 'BG-UV-03: body thiếu (warn mode)';
        END IF;
    END IF;
    
    -- BG-UV-04: required profile per section_type config (skeleton — chi tiết JSON Schema deferred)
    -- TODO post-G6: validate content_profile against JSON schema in section_type config
    
    -- BG-UV-05: length_flag set by fn_tac_uv_compute_derived (separate trigger)
    
    -- BG-UV-06: provenance valid (verify FAC-PROV vocab — deferred behavior test post-G6)
    IF NEW.provenance NOT IN ('PROV-AI', 'PROV-HUMAN', 'PROV-DOT') THEN
        RAISE WARNING 'BG-UV-06: provenance % chưa map FAC-PROV vocab', NEW.provenance;
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_tac_birth_gate_uv
    BEFORE INSERT
    ON p9_g6_dryrun.tac_unit_version
    FOR EACH ROW
    EXECUTE FUNCTION p9_g6_dryrun.fn_tac_birth_gate_uv();

6.4 fn_tac_enacted_immut (INV-ENACTED-IMMUT)

CREATE OR REPLACE FUNCTION p9_g6_dryrun.fn_tac_enacted_immut()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = p9_g6_dryrun, pg_catalog
AS $$
BEGIN
    -- Block UPDATE on body/title/description/content_profile khi lifecycle='enacted'
    IF OLD.lifecycle_status = 'enacted' AND TG_OP = 'UPDATE' THEN
        IF (NEW.body IS DISTINCT FROM OLD.body)
        OR (NEW.title IS DISTINCT FROM OLD.title)
        OR (NEW.description IS DISTINCT FROM OLD.description)
        OR (NEW.content_profile IS DISTINCT FROM OLD.content_profile)
        THEN
            RAISE EXCEPTION 'INV-ENACTED-IMMUT: cannot modify body/title/description/content_profile of enacted unit_version %', OLD.id
                USING ERRCODE = 'check_violation';
        END IF;
    END IF;
    
    -- Block DELETE enacted
    IF TG_OP = 'DELETE' AND OLD.lifecycle_status = 'enacted' THEN
        RAISE EXCEPTION 'INV-ENACTED-IMMUT: cannot delete enacted unit_version %', OLD.id
            USING ERRCODE = 'check_violation';
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER trg_tac_enacted_immut
    BEFORE UPDATE OR DELETE
    ON p9_g6_dryrun.tac_unit_version
    FOR EACH ROW
    EXECUTE FUNCTION p9_g6_dryrun.fn_tac_enacted_immut();

6.5 fn_tac_pm_consistency (INV-PM-CONSISTENCY)

CREATE OR REPLACE FUNCTION p9_g6_dryrun.fn_tac_pm_consistency()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = p9_g6_dryrun, pg_catalog
AS $$
DECLARE
    v_uv_logical_unit_id UUID;
BEGIN
    -- Verify unit_version.logical_unit_id = publication_member.logical_unit_id
    SELECT logical_unit_id INTO v_uv_logical_unit_id
      FROM p9_g6_dryrun.tac_unit_version
     WHERE id = NEW.unit_version_id;
    
    IF v_uv_logical_unit_id IS NULL THEN
        RAISE EXCEPTION 'INV-PM-CONSISTENCY: unit_version_id % không tồn tại', NEW.unit_version_id
            USING ERRCODE = 'foreign_key_violation';
    END IF;
    
    IF v_uv_logical_unit_id != NEW.logical_unit_id THEN
        RAISE EXCEPTION 'INV-PM-CONSISTENCY: pm.logical_unit_id (%) != uv.logical_unit_id (%)',
            NEW.logical_unit_id, v_uv_logical_unit_id
            USING ERRCODE = 'check_violation';
    END IF;
    
    RETURN NEW;
END;
$$;

CREATE TRIGGER trg_tac_pm_consistency
    BEFORE INSERT OR UPDATE
    ON p9_g6_dryrun.tac_publication_member
    FOR EACH ROW
    EXECUTE FUNCTION p9_g6_dryrun.fn_tac_pm_consistency();

6.6 fn_tac_pm_enacted_lock (INV-ENACTED-PUB)

CREATE OR REPLACE FUNCTION p9_g6_dryrun.fn_tac_pm_enacted_lock()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = p9_g6_dryrun, pg_catalog
AS $$
DECLARE
    v_pub_lifecycle TEXT;
    v_pub_id UUID;
BEGIN
    -- Determine publication_id depending on operation
    IF TG_OP IN ('INSERT', 'UPDATE') THEN
        v_pub_id := NEW.publication_id;
    ELSE -- DELETE
        v_pub_id := OLD.publication_id;
    END IF;
    
    SELECT lifecycle_status INTO v_pub_lifecycle
      FROM p9_g6_dryrun.tac_publication
     WHERE id = v_pub_id;
    
    -- Block all changes when publication = 'enacted'
    IF v_pub_lifecycle = 'enacted' THEN
        RAISE EXCEPTION 'INV-ENACTED-PUB: publication_member của publication % đã enacted, locked', v_pub_id
            USING ERRCODE = 'check_violation';
    END IF;
    
    RETURN COALESCE(NEW, OLD);
END;
$$;

CREATE TRIGGER trg_tac_pm_enacted_lock
    BEFORE INSERT OR UPDATE OR DELETE
    ON p9_g6_dryrun.tac_publication_member
    FOR EACH ROW
    EXECUTE FUNCTION p9_g6_dryrun.fn_tac_pm_enacted_lock();

6.7 Dependency note (v0.2 GPT R1#2)

KHÔNG cần pgcrypto extension. Tất cả function dùng built-in:

  • pg_catalog.sha256(bytea) — PG 11+, built-in
  • pg_catalog.convert_to(text, encoding) — built-in
  • pg_catalog.encode(bytea, format) — built-in
  • pg_catalog.regexp_split_to_array(text, pattern) — built-in
  • pg_catalog.array_length(array, dim) — built-in
  • pg_catalog.length(text), pg_catalog.btrim(text) — built-in
  • gen_random_uuid() — PG 13+, built-in trong default schema

Pre-flight PF-03 verify pg_catalog.sha256 tồn tại (PG 16 chắc chắn có). Nếu PG < 11 → STOP, escalate.

7. Seed plan + SHA-256

7.1 Seed strategy

Mỗi vocab table có 1 file seed .sql read-only. SHA-256 hash tính trước khi run, ghi vào seed-manifest.json. Re-run dry-run phải verify hash khớp.

7.2 Seed values (full per OD-G6-02 GPT R1 PASS)

seed-tac-lu-lifecycle.sql (3 rows):

INSERT INTO p9_g6_dryrun.tac_lu_lifecycle_vocab (code, name, description, sort_order) VALUES
    ('active', 'Hoạt động', 'Logical unit có ≥1 version enacted', 10),
    ('draft_only', 'Chỉ bản nháp', 'Chưa từng có version enacted', 20),
    ('retired', 'Đã rút', 'Quyết định retire qua change-set + APR', 30);

seed-tac-uv-lifecycle.sql (4 rows):

INSERT INTO p9_g6_dryrun.tac_uv_lifecycle_vocab (code, name, description, sort_order) VALUES
    ('draft', 'Bản nháp', 'Mới tạo, chưa approve, sửa tại chỗ', 10),
    ('enacted', 'Đã ban hành', 'Approve qua change-set + APR. Bất biến.', 20),
    ('superseded', 'Bị thay', 'Bị version mới enacted thay. Giữ audit.', 30),
    ('retired', 'Đã rút', 'Cascaded từ retire logical_unit', 40);

seed-tac-review-state.sql (5 rows):

INSERT INTO p9_g6_dryrun.tac_review_state_vocab (code, name, description, sort_order) VALUES
    ('unreviewed', 'Chưa rà soát', '', 10),
    ('in_review', 'Đang rà soát', '', 20),
    ('review_passed', 'Đã pass', '', 30),
    ('review_failed', 'Đã fail', '', 40),
    ('needs_re_review', 'Cần rà lại', '', 50);

seed-tac-pub-lifecycle.sql (4 rows):

INSERT INTO p9_g6_dryrun.tac_pub_lifecycle_vocab (code, name, description, sort_order) VALUES
    ('proposed', 'Đề xuất', 'Mutable, MAY reference draft', 10),
    ('enacted', 'Ban hành', 'Membership locked, immutable', 20),
    ('superseded', 'Bị thay', 'Version publication mới enacted', 30),
    ('retired', 'Đã rút', 'Quyết định governance', 40);

seed-tac-cs-lifecycle.sql (7 rows):

INSERT INTO p9_g6_dryrun.tac_cs_lifecycle_vocab (code, name, description, sort_order) VALUES
    ('draft', 'Nháp', '', 10),
    ('submitted', 'Đã gửi', 'Snapshot đóng băng (I9)', 20),
    ('review_passed', 'Reviewer pass', '', 30),
    ('approval_passed', 'APR quorum đạt', '', 40),
    ('enacted', 'Đã apply', '', 50),
    ('rejected', 'Đã từ chối', '', 60),
    ('withdrawn', 'Đã rút', '', 70);

seed-tac-section-type.sql (17 rows — full per C1A §6):

INSERT INTO p9_g6_dryrun.tac_section_type_vocab
    (code, name, description, soft_limit_words, hard_limit_words, description_required, body_required) VALUES
    ('heading',            'Tiêu đề',                  'Navigation heading',                  100,  300,  FALSE, FALSE),
    ('article',            'Điều',                     'Đơn vị Điều của luật',                500,  1500, TRUE,  TRUE),
    ('paragraph',          'Đoạn',                     'Đoạn văn xuôi',                       300,  1000, TRUE,  TRUE),
    ('definition',         'Định nghĩa',               'Định nghĩa thuật ngữ',                200,  500,  TRUE,  TRUE),
    ('principle',          'Nguyên tắc',               'Nguyên tắc cốt lõi',                  300,  800,  TRUE,  TRUE),
    ('rationale',          'Luận giải',                'Giải thích lý do',                    400,  1200, TRUE,  TRUE),
    ('process',            'Quy trình',                'Quy trình các bước',                  500,  1500, TRUE,  TRUE),
    ('technical_spec',     'Đặc tả kỹ thuật',          'Pseudo-DDL, schema',                  1000, 3000, TRUE,  TRUE),
    ('governance_process', 'Quy trình quản trị',       'Governance flow',                     500,  1500, TRUE,  TRUE),
    ('checklist',          'Danh sách kiểm',           'Checklist',                           300,  1000, FALSE, TRUE),
    ('instruction_block',  'Khối hướng dẫn',           'Hướng dẫn dài',                       1000, 3000, TRUE,  TRUE),
    ('reference_mapping',  'Bảng tham chiếu',          'Crosswalk table',                     500,  2000, FALSE, TRUE),
    ('matrix',             'Ma trận',                  'Multi-dim table',                     500,  2000, FALSE, TRUE),
    ('invariant_list',     'Danh mục bất biến',        'Invariants table',                    500,  2000, FALSE, TRUE),
    ('open_decision_list', 'Danh mục câu hỏi mở',      'Open decisions table',                500,  2000, FALSE, TRUE),
    ('appendix',           'Phụ lục',                  'Appendix dài',                        2000, 5000, FALSE, TRUE),
    ('changelog',          'Lịch sử thay đổi',         'Changelog/patch log',                 500,  2000, FALSE, TRUE);

seed-tac-publication-type.sql (10 rows — full per C1A §11.3):

INSERT INTO p9_g6_dryrun.tac_publication_type_vocab
    (code, name, description, default_risk_tier) VALUES
    ('law',          'Luật',              'Văn bản pháp lý',                'highest'),
    ('policy',       'Chính sách',        'Chính sách công ty',             'high'),
    ('sop',          'Quy trình SOP',     'Standard Operating Procedure',   'high'),
    ('constitution', 'Hiến pháp',         'Hiến pháp kiến trúc',            'highest'),
    ('knowledge',    'Tri thức',          'Knowledge base article',         'medium'),
    ('design_note',  'Ghi chú thiết kế',  'Design note',                    'medium'),
    ('report',       'Báo cáo',           'Report',                         'low'),
    ('memo',         'Memo',              'Internal memo',                  'low'),
    ('draft',        'Nháp',              'Working draft',                  'low'),
    ('working',      'Đang làm việc',     'Working document',               'low');

seed-tac-birth-gate-config.sql (per P8 §2.3 tiered, 11 rows):

INSERT INTO p9_g6_dryrun.tac_birth_gate_config (checker_id, mode, enabled, rationale) VALUES
    ('BG-LU-02', 'block', TRUE, 'Hard invariant'),
    ('BG-LU-03', 'block', TRUE, 'Hard invariant'),
    ('BG-LU-04', 'block', TRUE, 'Hard invariant'),
    ('BG-LU-05', 'block', TRUE, 'Hard invariant'),
    ('BG-LU-06', 'block', TRUE, 'Hard invariant'),
    ('BG-UV-01', 'block', TRUE, 'Hard invariant'),
    ('BG-UV-02', 'block', TRUE, 'Required profile'),
    ('BG-UV-03', 'warn',  TRUE, 'Quality, can grace'),
    ('BG-UV-04', 'block', TRUE, 'Required profile'),
    ('BG-UV-05', 'warn',  TRUE, 'Quality (length)'),
    ('BG-UV-06', 'warn',  TRUE, 'Quality (provenance)');

7.3 SHA-256 manifest

{
  "seed_manifest_version": "0.2",
  "generated_at": "2026-04-27T...",
  "files": [
    {"path": "seed-tac-lu-lifecycle.sql", "rows": 3, "sha256": "..."},
    {"path": "seed-tac-uv-lifecycle.sql", "rows": 4, "sha256": "..."},
    {"path": "seed-tac-review-state.sql", "rows": 5, "sha256": "..."},
    {"path": "seed-tac-pub-lifecycle.sql", "rows": 4, "sha256": "..."},
    {"path": "seed-tac-cs-lifecycle.sql", "rows": 7, "sha256": "..."},
    {"path": "seed-tac-section-type.sql", "rows": 17, "sha256": "..."},
    {"path": "seed-tac-publication-type.sql", "rows": 10, "sha256": "..."},
    {"path": "seed-tac-birth-gate-config.sql", "rows": 11, "sha256": "..."}
  ],
  "total_seed_rows": 61,
  "kb_source": "P9-G6-source-extraction-note.md §4 + P5 §5.5/§5.6 + P8 §2.3"
}

Hash tính bằng sha256sum seed-tac-*.sql (Bash). Manifest commit vào git hoặc upload KB. Agent verify hash trước seed step.

8. Verification plan

8.1 Stage 1 — Compile verify (sau DDL run)

# Check SQL Expected
V1-01 Schema tồn tại SELECT 1 FROM information_schema.schemata WHERE schema_name='p9_g6_dryrun'; 1 row
V1-02 14 tables tồn tại SELECT table_name FROM information_schema.tables WHERE table_schema='p9_g6_dryrun' ORDER BY table_name; 14 rows, names khớp danh sách §5
V1-03 All FK constraints exist SELECT count(*) FROM information_schema.table_constraints WHERE table_schema='p9_g6_dryrun' AND constraint_type='FOREIGN KEY'; ≥ 12 (theo §5.7)
V1-04 All UNIQUE constraints exist SELECT count(*) FROM information_schema.table_constraints WHERE table_schema='p9_g6_dryrun' AND constraint_type='UNIQUE'; ≥ 2 (uv unique, pm unique, csm unique)
V1-05 All CHECK constraints exist SELECT count(*) FROM information_schema.table_constraints WHERE table_schema='p9_g6_dryrun' AND constraint_type='CHECK'; ≥ 24
V1-06 All indexes exist SELECT count(*) FROM pg_indexes WHERE schemaname='p9_g6_dryrun'; ≥ 21 (gồm PK indexes auto)
V1-07 6 functions compile SELECT proname FROM pg_proc WHERE pronamespace='p9_g6_dryrun'::regnamespace ORDER BY proname; 6 rows: fn_tac_birth_gate_lu, fn_tac_birth_gate_uv, fn_tac_enacted_immut, fn_tac_pm_consistency, fn_tac_pm_enacted_lock, fn_tac_uv_compute_derived
V1-08 6 triggers attached SELECT trigger_name FROM information_schema.triggers WHERE trigger_schema='p9_g6_dryrun' ORDER BY trigger_name; 6+ trigger names (BEFORE/AFTER có thể tạo nhiều entries)
V1-09 All functions có SECURITY DEFINER SELECT proname, prosecdef FROM pg_proc WHERE pronamespace='p9_g6_dryrun'::regnamespace; 6 rows, all prosecdef=true
V1-10 All functions có search_path locked SELECT proname, proconfig FROM pg_proc WHERE pronamespace='p9_g6_dryrun'::regnamespace; 6 rows, proconfig contains 'search_path=p9_g6_dryrun, pg_catalog'

8.2 Stage 2 — Seed verify (sau seed run)

# Check SQL Expected
V2-01 tac_lu_lifecycle_vocab seed SELECT count(*) FROM p9_g6_dryrun.tac_lu_lifecycle_vocab; 3
V2-02 tac_uv_lifecycle_vocab seed SELECT count(*) FROM p9_g6_dryrun.tac_uv_lifecycle_vocab; 4
V2-03 tac_review_state_vocab seed SELECT count(*) FROM p9_g6_dryrun.tac_review_state_vocab; 5
V2-04 tac_pub_lifecycle_vocab seed SELECT count(*) FROM p9_g6_dryrun.tac_pub_lifecycle_vocab; 4
V2-05 tac_cs_lifecycle_vocab seed SELECT count(*) FROM p9_g6_dryrun.tac_cs_lifecycle_vocab; 7
V2-06 tac_section_type_vocab seed SELECT count(*) FROM p9_g6_dryrun.tac_section_type_vocab; 17
V2-07 tac_publication_type_vocab seed SELECT count(*) FROM p9_g6_dryrun.tac_publication_type_vocab; 10
V2-08 tac_birth_gate_config seed SELECT count(*) FROM p9_g6_dryrun.tac_birth_gate_config; 11
V2-09 Total seed rows sum tất cả 61
V2-10 SHA-256 manifest match sha256sum seed-*.sql so với manifest Match all

8.3 Stage 3 — Constraint behavior smoke (test 1 violation per type)

Test violation types để chứng minh constraint bind đúng:

# Test SQL Expected
V3-01 (UNIQUE) Duplicate canonical_address INSERT 2 logical_unit cùng canonical_address='D38-G6TEST-ROOT' First PASS, second FAIL with 23505 (unique_violation)
V3-02 (FK) logical_unit với section_type không tồn tại INSERT logical_unit với section_type='nonexistent' FAIL with 23503 (foreign_key_violation)
V3-03 (CHECK) risk_tier ngoài enum INSERT publication với risk_tier='invalid' FAIL with 23514 (check_violation)
V3-04 (NOT NULL) logical_unit thiếu owner INSERT logical_unit với owner=NULL FAIL with 23502 (not_null_violation)
V3-05 (Trigger BG-LU-01) canonical_address không khớp regex INSERT logical_unit với canonical_address='WRONG-FORMAT' FAIL with 23514 (raised by trigger)
V3-06 (Trigger BG-LU-03) parent doc_code khác child Setup parent doc='D38-A', INSERT child doc='D38-B' parent_id=parent.id FAIL raised by trigger
V3-07 (Trigger PM-CONSISTENCY) PM với uv.lu_id ≠ pm.lu_id Setup 2 lu + 1 uv on lu1, INSERT pm với pm.lu_id=lu2 + pm.uv_id=uv1 FAIL raised by trigger
V3-08 (Cleanup) DELETE all test rows DELETE FROM p9_g6_dryrun.tac_publication_member; DELETE FROM tac_unit_version; DELETE FROM tac_logical_unit; DELETE FROM tac_publication; All deleted, 0 rows remaining

Lưu ý: V3 chỉ là smoke test. Behavior test đầy đủ post-G6 (trên P9 production hoặc next dry-run round).

8.4 Stage 4 — Production isolation verify

# Check SQL Expected
V4-01 Production public.* tac_* objects vẫn whitelisted only (mở rộng v0.2 GPT R1#4) Re-run PF-02 query (§4.1.1) total_violations=0
V4-02 Production public.fn_tac_* chỉ có whitelisted SELECT count(*) FROM pg_proc WHERE pronamespace='public'::regnamespace AND proname LIKE 'fn_tac\_%' ESCAPE '\'; 1 (chỉ fn_tac_log_checker_issue)
V4-03 taxonomy_facets không thay đổi Directus API GET facets count 10 (FAC-01→09 + FAC-PROV)
V4-04 dot_tools không thay đổi Directus API GET DOT-TAC-* count 19 (ids 971-989)
V4-05 system_issues không thêm rows từ G6 SELECT count(*) FROM public.system_issues WHERE created_at > '${run_start_ts}' AND details::text LIKE '%p9_g6_dryrun%'; 0
V4-06 dot_action_log không thêm rows từ G6 (v0.2 GPT R1#3) SELECT count(*) FROM public.dot_action_log WHERE created_at > '${run_start_ts}' AND (action_metadata::text LIKE '%p9_g6_dryrun%' OR tool_code LIKE 'DOT-TAC-SCHEMA-%'); 0 — G6 KHÔNG ghi audit table
V4-07 Backup integrity sau G6 rclone check GDrive backup tồn tại OK

9. Rollback plan

9.1 Rollback steps

-- Step 1: Verify current state
SELECT count(*) FROM information_schema.tables WHERE table_schema='p9_g6_dryrun';
-- Expected: 14

-- Step 2: DROP SCHEMA CASCADE
DROP SCHEMA IF EXISTS p9_g6_dryrun CASCADE;

-- Step 3: Verify zero residue
SELECT count(*) FROM information_schema.schemata WHERE schema_name='p9_g6_dryrun';
-- Expected: 0

SELECT count(*) FROM information_schema.tables WHERE table_schema='p9_g6_dryrun';
-- Expected: 0

SELECT count(*) FROM pg_proc p
  JOIN pg_namespace n ON n.oid = p.pronamespace
 WHERE n.nspname = 'p9_g6_dryrun';
-- Expected: 0

-- Step 4: Verify production unchanged
-- (re-run V4 verification)

9.2 Rollback PASS criteria

# Criterion Expected
R-01 Schema p9_g6_dryrun không tồn tại 0 rows in pg_namespace
R-02 0 tables trong schema 0
R-03 0 functions trong schema 0
R-04 0 triggers liên quan 0
R-05 Production public.* tac_* objects vẫn whitelisted only (PF-02 expanded) total_violations=0
R-06 Production public.fn_tac_log_checker_issue vẫn nguyên 1
R-07 taxonomy_facets count vẫn = 10 10
R-08 dot_tools DOT-TAC-* vẫn = 19 19

9.3 Rollback timing

Rollback chạy SAU verification Stage 1+2+3+4 và User/GPT confirm G6 PASS. Hoặc rollback sớm nếu BẤT KỲ verification stage FAIL.

Nguyên tắc: Schema p9_g6_dryrun KHÔNG được tồn tại quá 24h trên production server. Sau verification → rollback ngay → archive log.

10. Hard exclusions (CẤM tuyệt đối trong G6 execution)

Theo §2.2 + handoff §3.3 + GPT R1 chỉ đạo:

# Hard exclusion Rationale
HE-01 KHÔNG CREATE/ALTER/DROP public.tac_* (mọi loại object) Production isolation
HE-02 KHÔNG CREATE/MODIFY Directus roles Thuộc G8
HE-03 KHÔNG CREATE/MODIFY Directus collections Thuộc G8 + P9
HE-04 KHÔNG activate cron jobs Thuộc P9 production
HE-05 KHÔNG INSERT/UPDATE/DELETE taxonomy_labels Đ24 mutation
HE-06 KHÔNG INSERT/UPDATE/DELETE entity_labels Đ24 mutation
HE-07 KHÔNG INSERT system_issues (kể cả qua wrapper) G7 đã PASS, G6 không test path này
HE-08 KHÔNG INSERT dot_action_log (v0.2 GPT R1#3) Production audit table; default G6 chỉ KB markdown + local artifacts
HE-09 KHÔNG cleanup _dot_origin Out of scope
HE-10 KHÔNG behavior test functions/triggers ngoài V3 smoke Defer post-G6
HE-11 KHÔNG DROP/ALTER public.fn_tac_log_checker_issue E-R3 production artifact
HE-12 KHÔNG modify dot_tools / taxonomy_facets E5/E7 production artifacts
HE-13 KHÔNG modify GSM secrets Đ33 §14
HE-14 KHÔNG hardcode -U directus trong DDL script §0-AU + bài học S183
HE-15 KHÔNG unqualified DDL Bài học S183 schema-qualify
HE-16 KHÔNG bypass pre-flight checks AP-DRY-RUN-FK
HE-17 KHÔNG CREATE EXTENSION pgcrypto (v0.2 GPT R1#2) DB-level mutation; G6 dùng built-in pg_catalog.sha256() thay digest()

11. Bài học S183 áp dụng (rà 13 NT HP v4.6.2)

11.1 Schema-qualify (S183 #2)

Áp dụng Hành động
Mọi CREATE TABLE p9_g6_dryrun.tac_*
Mọi CREATE FUNCTION p9_g6_dryrun.fn_tac_*
Mọi CREATE TRIGGER ON p9_g6_dryrun.tac_*
Mọi DROP statement p9_g6_dryrun.{object}
Mọi verify SELECT p9_g6_dryrun.{table} hoặc WHERE table_schema='p9_g6_dryrun'
Mọi function call pg_catalog.sha256, pg_catalog.encode, etc.
SET search_path TO p9_g6_dryrun, pg_catalog (loại public)

11.2 §0-AU không hardcode (S183 #1, v0.2 patched)

Áp dụng Hành động
Connection identity psql "host=$DB_HOST port=$DB_PORT user=$DB_USER dbname=$DB_NAME" từ .env
KHÔNG psql -U directus ... hardcoded
Verify identity sau connect SELECT current_user, current_database();
Permission check Verify user có CREATE SCHEMA permission, không bắt user phải là workflow_admin cụ thể (v0.2 GPT R1#5 OD-G6-09)

11.3 AP-DRY-RUN-FK (S183 #3)

Áp dụng Hành động
Pre-flight §4.2 verify FK vocabulary Trước khi tạo CORE tables, verify vocab tables seed thành công và values khớp expected
FK reference verify Sau seed, kiểm count vocab table = expected count (3/4/5/4/7/17/10/11)
Constraint smoke V3-02 Test FK violation explicitly để chứng minh FK bind

11.4 AP-APR-SCOPE (S183 #4)

Áp dụng Hành động
Scope lock G6 = isolated schema dry-run, không mở rộng sang production/Directus
Scope verify trong execution prompt Liệt kê hard exclusions §10 trong prompt
Verify post-execution Stage 4 Production Isolation verify (V4-01→V4-07, mở rộng v0.2)

11.5 13 NT (HP v4.6.2) rà 3 tầng (Đ43 amend)

NT Tầng 1 (code bash/python) Tầng 2 (SQL DDL) Tầng 3 (SQL data)
NT1 PG nền tảng DDL script chạy psql 16 14 tables + 6 functions PG-native 61 vocab seed rows
NT2 Cơ chế máy psql exit code check CHECK + FK + trigger compile Constraint smoke V3
NT4 Config not hardcode .env cho connection FK vocab thay CHECK enum (P8 §1.1) Vocab values seed từ KB
NT8 Scale Index design 21 indexes (GIN + partial hợp lệ + B-tree) —
NT11 Khai tối thiểu — 14 tables faithful P5, không over-design Seed minimal + đủ
NT13 PG First DDL only PG Trigger PG-native, function PL/pgSQL, built-in sha256() không cần extension —
Đ24 — FK to vocab production via CHECK constraint format —
Đ32 — change_set hooks —
Đ33 E1 exception path E1 cho schema isolated, KHÔNG CREATE EXTENSION (v0.2) E1 bootstrap seed
Đ35 — DOT-TAC-SCHEMA-ENSURE invocation pattern —

12. Đối chiếu luật (constitutional check đầy đủ)

Nguyên tắc / Luật Verdict G6 v0.2 Evidence
NT1 PG nền tảng PASS DDL pure PostgreSQL 16, schema isolated trên cùng instance
NT2 Cơ chế máy PASS 24+ CHECK + 12+ FK + 6 trigger = máy enforce
NT4 Config not hardcode PASS FK vocab + birth_gate_config; seed từ KB không hardcode trong code
NT5 Lỗi=cơ hội vàng PASS Pre-flight + verification capture lỗi sớm; bài học S183 + GPT R1 baked vào
NT8 Scale PASS 21 indexes (GIN + partial hợp lệ + B-tree) per P5 design
NT11 Khai tối thiểu PASS 14 tables faithful P5, không tạo registry song song
NT13 PG First PASS Mọi enforcement = PG trigger + constraint + built-in sha256(), không app-level workaround
NT-DB-CONSTRAINT-FIRST (S176B) PASS UNIQUE, FK, CHECK, NOT NULL bind ở DB layer
Đ24 Label Law PASS Facets E5 đã sẵn (FAC-07/08/09); G6 không ghi entity_labels
Đ32 APR PASS change_set hooks; G6 không enact thật
Đ33 PG Law PASS E1 exception cho DDL trong schema isolated; KHÔNG CREATE EXTENSION (v0.2 GPT R1#2); KHÔNG ghi dot_action_log (v0.2 GPT R1#3)
Đ35 DOT Law PASS DOTs E7 đã registered; G6 = SCHEMA-ENSURE (B) + SCHEMA-VERIFY (A) paired; action log = KB markdown only (v0.2 GPT R1#3)
Đ38 Text as Code PASS Implements P5 schema design + P8 hardening
Đ43 v1.2 PASS Phase pattern P0 (snapshot) → P1 (DDL) → P2 (seed) → P3 (verify) → P4 (smoke) → P5 (rollback) → P6 (report)
§0-AU không hardcode PASS .env + $DB_USER; verify permission, không bắt fixed user (v0.2 GPT R1#5)
§0-AY không register=vô hình N/A G6 schema = transient test, ROLLBACK xóa sạch
§0-AF evidence production URL PASS psql output + verify queries làm evidence + KB markdown report
5-GATE (S176B) PASS G1 đọc luật ✅; G2 COUNT trước WHERE ✅; G3 đối chiếu P5+P8+Đ33 ✅; G4 từ chuẩn ✅; G5 không chắc dừng ✅
NGUYÊN TẮC LUẬT TRƯỚC PASS 4 câu hỏi: (1) Có luật ✅ P5/P8; (2) Đủ ✅ với GPT R1 chốt 10 OD; (3) DOT làm theo ✅ DOT-TAC-SCHEMA-*; (4) Không xung đột ✅
Tuyên ngôn thiết kế ①②③ PASS ① Tạm (G6 = transient); ② Cơ hội nhầm: hard exclusions HE-01→17; ③ Lỗi=vàng: verify đầy đủ

13. Risk register

# Risk Likelihood Impact Mitigation
R-01 Agent ghi nhầm vào public.tac_* thay vì p9_g6_dryrun.* Low Cao (1) Mọi DDL schema-qualify; (2) SET search_path loại public; (3) PF-02 expanded verify production tac_* objects = 0 trước/sau (v0.2); (4) HE-15 explicit
R-02 DROP SCHEMA CASCADE xóa nhầm production Very Low Cao DROP SCHEMA chỉ target p9_g6_dryrun, schema name không trùng production
R-03 Function compile fail vì pgcrypto thiếu → RESOLVED v0.2: dùng built-in sha256() N/A N/A Không cần pgcrypto. PF-03 verify pg_catalog.sha256 (PG 11+ chắc chắn có trên PG 16)
R-04 Seed SHA-256 mismatch Low Trung Manifest commit vào git trước run; agent verify hash trước seed
R-05 Trigger ATTACH fail vì function không tồn tại Low Trung Order trong DDL script: tables → functions → triggers
R-06 TRIGGER-GUARD block CREATE SCHEMA Low Cao PF-08 verify guard mode informational; nếu BLOCK → escalate
R-07 Rollback bị block (orphan FK constraint) Very Low Trung DROP SCHEMA CASCADE handles; verify R-01→R-08 sau rollback
R-08 Connection identity sai (current_user permission insufficient) Low Trung PF-09 verify trước run, dựa permission không phải user name (v0.2)
R-09 Disk space đủ cho schema Very Low Thấp 14 empty tables + 61 seed rows ≈ <1MB
R-10 Concurrent agent chạy cùng lúc Very Low Trung Single-instance agent, locking script không cần
R-11 Public schema có tac_ type/sequence/view residue chưa được PF-02 cũ catch* Low Cao PF-02 expanded v0.2 GPT R1#4 check tables/views/sequences/types/triggers/functions — gộp 1 query UNION ALL
R-12 Dot_action_log accidental write Very Low Trung HE-08 v0.2 explicit ban, V4-06 verify post-execution

14. Open decisions — RESOLVED (GPT R1, v0.2)

Code Câu hỏi Decision (GPT R1) Status
OD-G6-01 Function body: skeleton hay full logic? Skeleton + behavior-critical logic (như §6.1–6.6 với RAISE EXCEPTION, FK lookup thật) ✅ RESOLVED
OD-G6-02 Vocab seed: minimal hay full? Full 61 rows ✅ RESOLVED
OD-G6-03 Constraint smoke: chỉ exist hay test violation? Cả hai — V1 verify exist + V3 test violation ✅ RESOLVED
OD-G6-04 Rollback verify: count residue hay schema check? Cả hai — DROP CASCADE + R-01→R-08 ✅ RESOLVED
OD-G6-05 Action log path KB markdown report + local artifacts only (KHÔNG dot_action_log) ✅ RESOLVED v0.2 GPT R1#3
OD-G6-06 Agent assignment Claude CLI / Claude Code style — DDL phức tạp, plan-then-execute ✅ RESOLVED
OD-G6-07 pgcrypto handling Verify existing built-in pg_catalog.sha256 (PG 11+) — KHÔNG cần pgcrypto. KHÔNG auto CREATE EXTENSION. Nếu PG version quá cũ → STOP ✅ RESOLVED v0.2 GPT R1#2 (alternative chấp nhận)
OD-G6-08 Partial index idx_tac_pm_pub_enacted Bỏ hẳn (cả CREATE và DROP). Note: optimization deferred, enacted lock enforce bằng trigger ✅ RESOLVED v0.2 GPT R1#1
OD-G6-09 Connection user Dùng .env, verify actor có CREATE SCHEMA permission. KHÔNG hardcode chỉ workflow_admin ✅ RESOLVED v0.2 GPT R1#5
OD-G6-10 Run timing Off-peak preferred, không bắt buộc nếu pre-flight + backup PASS ✅ RESOLVED

Tất cả 10 OD đã RESOLVED. Không còn OD pending block execution prompt.

15. PASS / FAIL criteria

15.1 G6 PASS

# Criterion Verify by
1 All 10 pre-flight checks PASS (PF-01→PF-10) — bao gồm PF-02 expanded + PF-03 sha256 built-in §4.1
2 Schema p9_g6_dryrun created V1-01
3 14 tables compile + match P8 §1.4 V1-02
4 All FK/UNIQUE/CHECK constraints bind V1-03→V1-05
5 All indexes (≥21) created V1-06
6 6 functions compile, all SECURITY DEFINER + search_path locked V1-07/V1-09/V1-10
7 6 triggers attached V1-08
8 Seed 61 rows total, SHA-256 manifest match V2-01→V2-10
9 Constraint smoke V3 (8 sub-tests) all behavior expected V3-01→V3-08
10 Production isolation verify (V4-01→V4-07, expanded v0.2) all PASS V4
11 Rollback DROP SCHEMA CASCADE → 0 residue (R-01→R-08) §9.2
12 Action log markdown report uploaded KB (KHÔNG dot_action_log writes) OD-G6-05 v0.2
13 Hard exclusions HE-01→17 không vi phạm (verify production audit) V4
14 GPT review PASS Independent
15 User authorize G6 PASS → G8 Final gate

15.2 G6 FAIL conditions

# Condition Severity
F-01 Pre-flight ANY fail STOP, không proceed
F-02 DDL compile error (cú pháp/FK/CHECK invalid) STOP, fix design, re-design
F-03 Function PL/pgSQL parse error STOP, fix function, re-run
F-04 Seed SHA-256 mismatch STOP, regen seed, re-hash
F-05 Constraint smoke V3 expected violation không xảy ra FAIL, indicates constraint không bind đúng
F-06 V4 production isolation fail (production tac_* objects xuất hiện ngoài whitelist) CRITICAL, immediate rollback + audit
F-07 Rollback không clean (residue > 0) CRITICAL, manual cleanup + post-mortem
F-08 Hard exclusion violation detected CRITICAL, immediate rollback + escalate
F-09 dot_action_log có rows từ G6 run (V4-06 fail) (v0.2) CRITICAL — vi phạm scope, immediate audit

15.3 Abort conditions

# Condition Action
A-01 Production data touched Immediate rollback + escalate User
A-02 TRIGGER-GUARD blocks unexpectedly Stop, escalate
A-03 Backup không tươi (PF-07 fail) Stop, run backup, retry
A-04 Connection permission insufficient (PF-09 fail) Stop, fix .env or escalate, retry
A-05 Agent runaway loop / infinite trigger Kill connection, rollback, post-mortem
A-06 PG version < 11 (sha256 built-in absent, PF-03 fail) (v0.2) Stop, escalate — PG upgrade required, không auto-fix

16. Sequence post-G6 PASS

Gate Mục tiêu Dependency
G6 PASS Migration dry-run verified Current package
G8 Directus roles Tạo tac-agent + tac-admin roles + tokens GSM G6 PASS (cần biết schema để gán permissions)
G11 User approval Final go/no-go cho P9 production migration G6 + G8 PASS, all 12/12 gates
P9 production migration Apply 14 tables + 6 functions + seed + collections + roles + DOTs activate vào public.tac_* G11 PASS

Lưu ý: Sau G6 PASS, schema p9_g6_dryrun đã DROP. G8 sẽ chạy trên production public.tac_* SAU P9 migration thật, KHÔNG trên schema isolated nữa. G8 thiết kế gate riêng — không phải phần của G6 package này.

17. Patch log

v0.1 (S184, 2026-04-27)

  • Initial draft sau khi đọc đầy đủ P5/P5b/P6/P7/P8/Tier 3 Readiness
  • Schema-qualify mọi nơi (p9_g6_dryrun.*)
  • 14 tables faithful P8 §1.4
  • 6 functions skeleton compile-only
  • 61 seed rows với SHA-256 manifest
  • Pre-flight 10 + FK vocab verify
  • Verification 4 stages
  • Risk register 10 items, OD 10 items
  • Constitutional check 19 luật/nguyên tắc
  • Bài học S183 baked: schema-qualify, .env, FK pre-check, scope lock

v0.2 (S184, 2026-04-27) — GPT R1 patches (6 fixes)

# Patch Áp dụng Loại
1 Bỏ invalid partial index idx_tac_pm_pub_enacted ... WHERE EXISTS (...) §5.5 D4: bỏ cả CREATE và DROP, thêm note enforce trigger GPT R1#1
2 Pgcrypto/digest replacement §6.1 chuyển sang pg_catalog.sha256(convert_to(...)) built-in. PF-03 verify sha256. HE-17 cấm CREATE EXTENSION. R-03 RESOLVED. A-06 mới. Đề xuất alternative thay vì template + pre-flight discovery — chờ GPT R2 confirm. GPT R1#2
3 Bỏ dot_action_log writes default G6 §2.1 action log = KB markdown only. HE-08 explicit. V4-06 verify post-execution. F-09 critical fail. OD-G6-05 RESOLVED. GPT R1#3
4 PF-02 expanded từ chỉ tables/functions → tables/views/sequences/types/triggers/functions với UNION ALL query gộp §4.1.1 SQL chi tiết. R-11 mitigation. V4-01 expanded. GPT R1#4
5 OD chốt — chuyển 10 OD từ Pending sang Resolved §14 đầy đủ. PASS criterion #14 cập nhật. GPT R1#5
6 Index revision note (acknowledge, không patch) Note: tương lai dùng update_document thay delete+upload. Không blocker. GPT R1#6

Đề xuất alternative cần GPT R2 confirm: Patch 2 chuyển từ pgcrypto/digest pattern sang built-in pg_catalog.sha256() — clean hơn (không cần extension, không cần discovery). Nếu GPT R2 reject → revert sang template {{EXT_SCHEMA}}.digest(...) + pre-flight pgcrypto discovery + STOP if absent.

Patch dự kiến v0.3 (sau GPT R2)

  • Pending GPT R2 review feedback

18. PASS criteria cho package này (review checklist v0.2)

# Criterion Status
1 14 tables full DDL trong p9_g6_dryrun.* ✅ §5
2 6 functions/triggers full skeleton + behavior-critical ✅ §6
3 61 seed rows với KB source ✅ §7
4 Pre-flight 10 checks (PF-02 expanded, PF-03 sha256) ✅ §4
5 Verification 4 stages (V4 expanded với V4-06 dot_action_log) ✅ §8
6 Rollback plan + verify ✅ §9
7 17 hard exclusions (thêm HE-08 dot_action_log, HE-17 pgcrypto) ✅ §10
8 Bài học S183 áp dụng (5 items + 13 NT 3 tầng) ✅ §11
9 Constitutional check 19 nguyên tắc ✅ §12
10 Risk register 12 items (R-11 expanded check, R-12 dot_action_log) ✅ §13
11 10 OD RESOLVED (GPT R1) — không pending blocker ✅ §14
12 PASS/FAIL/Abort criteria (F-09 dot_action_log critical, A-06 PG version) ✅ §15
13 Sequence post-G6 ✅ §16
14 Schema-qualify mọi DDL ✅ §3.2/§5
15 Không hardcode credentials ✅ §11.2
16 Hard exclusions explicit ✅ §10
17 Package = plan/DDL design, không phải execution prompt ✅ §1 cảnh báo
18 6 GPT R1 patches áp dụng ✅ §17 v0.2 patch log
19 Invalid partial index removed ✅ §5.5 D4
20 Pgcrypto avoided via built-in sha256 ✅ §6.1 + §6.7
21 Dot_action_log excluded ✅ §10 HE-08 + V4-06
22 Public conflict check expanded ✅ §4.1.1

P9 G6 Migration Dry-Run Package v0.2 | DRAFT | S184 | 2026-04-27 | Opus 4.7 Inputs: P5 v0.2, P5b v0.2, P6 v0.2, P7 v0.2, P8 v0.4, Tier 3 Readiness, source-extraction-note v0.1, GPT R1 review (6 patches) Đề xuất alternative trong patch 2 chờ GPT R2 confirm. Sau GPT R2 PASS → User authorize → soạn execution prompt riêng giao agent thực thi.