Incomex AI Portal
KB-4BB1

P9 G6 — Migration Dry-Run Execution Prompt v0.2 OFFICIAL (full content)

52 min read Revision 1
dieu38p9g6execution-prompttier3s184officialv0.2not-to-runfull-content

P9 G6 — Migration Dry-Run Execution Prompt

Loại: Execution prompt cho agent (Claude CLI / Claude Code style) Phase: P9 Entry Gate, Tier 3, Gate G6 Trạng thái: OFFICIAL v0.2 — GPT R3 (6 patches applied) + GPT R4 PASS nội dung. Finalization complete (KB persist + errata + manifest). Chờ User authorize execution gate. Phiên: S184 | Ngày: 2026-04-27 Agent soạn: Opus 4.7 (Desktop) Bám chặt: P9-G6-dry-run-package.md v0.2 (GPT R2 PASS, 6 patches applied)

CẢNH BÁO: File này là prompt cho agent thực thi G6 trên VPS. NOT TO RUN cho đến khi User explicit authorize execution gate. Mọi DDL/DML đều bị gate bởi User authorization.

§-1 PRECEDENCE NOTE (BẮT BUỘC ĐỌC ĐẦU TIÊN)

Cho execution G6, prompt v0.2 SUPERSEDES package v0.2 nếu có xung đột wording.

Errata documented tại: knowledge/dev/laws/dieu38-trien-khai/P9-G6-precedence-errata.md

Errata chính: PF-01 wording — package v0.2 còn nói "schema exists → DROP", prompt v0.2 sửa đúng "schema exists → STOP + report residue + escalate cleanup gate riêng". Agent BẮT BUỘC theo prompt v0.2 §6.1, KHÔNG package v0.2 §4.1 PF-01 wording cũ.

§0 BỐI CẢNH

P9 Entry Gate đã qua 9/12 cửa. G6 = Migration Dry-Run trên schema isolated p9_g6_dryrun — chứng minh 14 tables (P8 §1.4) compile được, 6 functions/triggers compile + behavior-critical OK, rollback DROP SCHEMA CASCADE để lại 0 residue. Sau G6 PASS → G8 (Directus roles) → G11 (User approval) → P9 production migration.

Phân vai:

  • User (Huyên): Duyệt mục tiêu/gate. KHÔNG thao tác kỹ thuật.
  • Opus (Desktop): Điều hành + soạn prompt này. Generate seed manifest expected JSON trước execution.
  • GPT: Giám sát + phản biện. Đã PASS package v0.2 (R2). Đã PASS prompt v0.1 with patches (R3). Đã PASS prompt v0.2 nội dung (R4) + finalization yêu cầu.
  • Agent (Claude CLI / Claude Code): Thực thi prompt này theo plan-then-execute pattern.

Operating principle: Prompt mục tiêu mở (PASS/FAIL criteria), KHÔNG step-by-step micromanagement. Agent tự lập kế hoạch chi tiết → execute → verify → report.

§1 BẮT BUỘC ĐỌC TRƯỚC (CHECKPOINT KHÔNG ĐƯỢC BỎ QUA)

Agent BẮT BUỘC đọc đầy đủ 5 tài liệu sau qua agent-data MCP trước khi viết bất kỳ DDL/SQL nào:

# Path Section quan trọng
1 knowledge/dev/laws/dieu38-trien-khai/P9-G6-dry-run-package.md (v0.2) §5 (DDL 14 tables), §6 (6 functions), §7 (61 seed rows), §8 (verification), §9 (rollback), §10 (hard exclusions), §15 (PASS/FAIL)
2 knowledge/dev/laws/dieu38-trien-khai/P9-G6-source-extraction-note.md (v0.1) §11 (bài học S183), §12 (pre-flight rationale)
3 knowledge/dev/laws/dieu38-trien-khai/P9-G6-precedence-errata.md Toàn bộ — precedence prompt > package + PF-01 errata
4 knowledge/dev/laws/dieu38-trien-khai/tham-khao/handoff-s183-e-r3-e7-e6.md §3 (G6 scope), §5 (sai lầm S183)
5 knowledge/dev/reports/gpt-review-g6-execution-prompt-r4-2026-04-27.md Toàn bộ — finalization PASS

Sau khi đọc, agent PHẢI quote 3 dòng để chứng minh đã đọc:

  1. Quote §5.5 D4 package: "NOTE (v0.2 GPT R1#1): Partial index on enacted publications optimization DEFERRED..."
  2. Quote §6.7 package: "KHÔNG cần pgcrypto extension. Tất cả function dùng built-in..."
  3. Quote §10 HE-08 package: "KHÔNG INSERT dot_action_log..."
  4. Quote errata §1: "Cho execution G6, prompt v0.2 SUPERSEDES package v0.2 nếu có xung đột wording."

Nếu KHÔNG quote được → STOP, đọc lại.

§2 5-GATE + Assembly Gate (S176B) — Bắt buộc trả lời TRƯỚC khi viết SQL

Agent PHẢI trả lời 5 câu sau bằng văn bản trước khi chạy bất kỳ statement nào:

Gate Câu hỏi Yêu cầu trả lời
G1 Đọc luật đủ điều kiện Đã đọc package v0.2 + Đ33 PG Law + Đ24 Label Law + Đ35 DOT Law + bài học S183 + errata chưa? Liệt kê file paths + section đã đọc
G2 COUNT trước WHERE Bao nhiêu objects sẽ tạo trong p9_g6_dryrun? Bao nhiêu seed rows? Bao nhiêu pre-flight checks? Số cụ thể: 14 tables, 6 functions, 6 triggers, ~21 indexes, 61 seed rows, 11 PF (PF-Pre + PF-01→10) + FK vocab verify
G3 Đối chiếu ≥3 luật chéo Hard exclusions §10 package có conflict với Đ33/Đ24/Đ35 không? Cross-check: Đ33 (E1 exception OK), Đ24 (no entity_labels mutation), Đ35 (no dot_action_log)
G4 Từ chuẩn trước đặt tên Mọi DDL có dùng schema-qualify p9_g6_dryrun.tac_* chưa? Có unqualified statement nào không? grep test trên DDL bundle (§7.3)
G5 Không chắc → DỪNG Có điểm nào trong package v0.2 hoặc prompt không hiểu hoặc mơ hồ không? Đặc biệt PF-01 — đã hiểu errata chưa? Liệt kê — nếu có → STOP, escalate Opus, KHÔNG tự diễn giải

Assembly Gate (Đ-Assembly):

  • Câu 0: PostgreSQL đã giải quyết? → CÓ, toàn bộ G6 = PG DDL/DML thuần.
  • Câu 1-5: Directus/Nuxt UI/agent_data không liên quan G6 (chỉ verify Directus API GET cho PF-04/05).

§3 Phạm vi (mục tiêu mở) — KHÔNG step-by-step

Mục tiêu G6 PASS = thỏa mãn TẤT CẢ 15 PASS criteria trong package v0.2 §15.1:

  1. 11 pre-flight checks PASS (PF-Pre + PF-01 → PF-10)
  2. Schema p9_g6_dryrun created
  3. 14 tables compile match P8 §1.4
  4. FK/UNIQUE/CHECK constraints bind
  5. ≥21 indexes created
  6. 6 functions compile + SECURITY DEFINER + search_path locked
  7. 6 triggers attached
  8. Seed 61 rows + SHA-256 manifest match (hoặc PENDING_COMPUTE first run, ghi log)
  9. Constraint smoke V3 (8 sub-tests) behavior expected (SQLSTATE match)
  10. Production isolation V4-01→V4-07 PASS
  11. Rollback DROP SCHEMA CASCADE → 0 residue
  12. Action log markdown report uploaded KB (gồm DDL bundle hash)
  13. Hard exclusions HE-01→19 không vi phạm
  14. GPT/User authorize execution gate trước run
  15. User authorize G6 PASS → G8 (final gate)

Agent tự lập kế hoạch (plan-then-execute), tự verify, tự rollback. KHÔNG yêu cầu Opus/User chỉ đạo từng bước.

§4 Hard exclusions (CẤM tuyệt đối — vi phạm = abort + rollback ngay)

Theo package v0.2 §10 + GPT R3/R4 patches:

# Hard exclusion Verify post-execution
HE-01 KHÔNG CREATE/ALTER/DROP public.tac_* (mọi loại object: table/view/sequence/type/function/trigger) V4-01 (PF-02 expanded)
HE-02 KHÔNG CREATE/MODIFY Directus roles Manual audit Directus API
HE-03 KHÔNG CREATE/MODIFY Directus collections Manual audit Directus API
HE-04 KHÔNG activate cron jobs crontab -l verify
HE-05 KHÔNG INSERT/UPDATE/DELETE taxonomy_labels Directus API GET count
HE-06 KHÔNG INSERT/UPDATE/DELETE entity_labels Directus API GET count
HE-07 KHÔNG INSERT system_issues (kể cả qua wrapper) V4-05 (schema-adaptive)
HE-08 KHÔNG INSERT dot_action_log V4-06 schema-adaptive (introspect columns trước query)
HE-09 KHÔNG cleanup _dot_origin Audit metadata field
HE-10 KHÔNG behavior test functions/triggers ngoài V3 smoke Limit V3 đúng 8 sub-tests
HE-11 KHÔNG DROP/ALTER public.fn_tac_log_checker_issue PF-06 trước, V4-02 sau
HE-12 KHÔNG modify dot_tools / taxonomy_facets V4-03, V4-04
HE-13 KHÔNG modify GSM secrets Out of session scope
HE-14 KHÔNG hardcode -U directus grep DDL bundle (§7.3)
HE-15 KHÔNG unqualified DDL grep test §7.3 trên DDL bundle thực tế
HE-16 KHÔNG bypass pre-flight Sequential check, fail-stop
HE-17 KHÔNG CREATE EXTENSION pgcrypto (hoặc bất kỳ extension nào) grep DDL bundle
HE-18 KHÔNG auto-DROP schema p9_g6_dryrun trong pre-flight PF-01 STOP nếu schema tồn tại
HE-19 KHÔNG tự install missing tools/env vars PF-Pre STOP nếu thiếu

Vi phạm bất kỳ HE nào → IMMEDIATE rollback + escalate Opus.

§5 Connection setup (§0-AU compliance — discover read-only)

KHÔNG hardcode -U directus hoặc bất kỳ user nào. Discover .env path read-only:

# Discover .env path (read-only, không create)
ENV_FILE=""
for candidate in /opt/incomex/.env /etc/incomex/env /opt/incomex-saigon/.env $HOME/incomex/.env; do
    if [ -f "$candidate" ] && [ -r "$candidate" ]; then
        ENV_FILE="$candidate"
        break
    fi
done

if [ -z "$ENV_FILE" ]; then
    echo "FAIL: .env file không tìm thấy ở các path candidate."
    echo "Candidate paths checked: /opt/incomex/.env, /etc/incomex/env, /opt/incomex-saigon/.env, \$HOME/incomex/.env"
    echo "Cần Opus confirm exact .env path trước khi proceed."
    exit 1
fi

echo "ENV_FILE discovered: $ENV_FILE"

# Source env (KHÔNG modify file)
set -a
source "$ENV_FILE"
set +a

# Verify required env vars
: "${DB_HOST:?DB_HOST chưa set trong $ENV_FILE}"
: "${DB_PORT:?DB_PORT chưa set}"
: "${DB_USER:?DB_USER chưa set}"
: "${DB_NAME:?DB_NAME chưa set}"
: "${DB_PASSWORD:?DB_PASSWORD chưa set}"

# Connection string (KHÔNG hardcode user)
export PGCONN="host=${DB_HOST} port=${DB_PORT} user=${DB_USER} dbname=${DB_NAME} password=${DB_PASSWORD} sslmode=prefer"

# Verify connection (read-only)
psql "$PGCONN" -c "SELECT current_user, current_database(), version();" \
    -v ON_ERROR_STOP=1

Hoặc nếu kết nối qua Docker exec (S178 evidence: VPS dùng Docker):

docker exec -e PGPASSWORD="$DB_PASSWORD" postgres \
  psql -h localhost -U "$DB_USER" -d "$DB_NAME" \
  -v ON_ERROR_STOP=1 \
  -c "SELECT current_user, current_database();"

Verify identity sau connect:

  • current_user thuộc danh sách actor có CREATE SCHEMA permission (verify pg_roles có rolcreatedb hoặc explicit GRANT, hoặc rolsuper).
  • current_database() = directus (per S178 vaccine).
  • Nếu user KHÔNG có CREATE SCHEMA permission → STOP, escalate (KHÔNG tự GRANT).

§6 Pre-flight phase

Agent chạy lần lượt từng check. BẤT KỲ check nào FAIL → STOP, không proceed sang DDL.

§6.0 PF-Pre — Tools + Env verify (NEW v0.2 GPT R3#2/#4)

Mục đích: Confirm tools + env sẵn TRƯỚC khi chạy pre-flight chính. Tránh execution dừng giữa chừng vì thiếu công cụ.

# PF-Pre.1: Required CLI tools available
REQUIRED_TOOLS=(jq rclone sha256sum psql curl docker)
MISSING_TOOLS=()
for tool in "${REQUIRED_TOOLS[@]}"; do
    if ! command -v "$tool" >/dev/null 2>&1; then
        MISSING_TOOLS+=("$tool")
    fi
done
if [ ${#MISSING_TOOLS[@]} -gt 0 ]; then
    echo "FAIL: Missing tools: ${MISSING_TOOLS[*]}"
    echo "STOP — KHÔNG tự install. Escalate Opus."
    exit 1
fi
echo "PF-Pre.1 PASS: all required tools available"

# PF-Pre.2: .env discovered + sourced (đã làm ở §5)
echo "PF-Pre.2 PASS: ENV_FILE=$ENV_FILE"

# PF-Pre.3: Directus API URL + admin token retrieval
DIRECTUS_URL="${DIRECTUS_URL:-}"
DIRECTUS_ADMIN_TOKEN="${DIRECTUS_ADMIN_TOKEN:-${DIRECTUS_TOKEN:-}}"

if [ -z "$DIRECTUS_URL" ]; then
    DIRECTUS_URL=$(gcloud secrets versions access latest --secret=directus-url 2>/dev/null || echo "")
fi
if [ -z "$DIRECTUS_ADMIN_TOKEN" ]; then
    DIRECTUS_ADMIN_TOKEN=$(gcloud secrets versions access latest --secret=directus-admin-token 2>/dev/null || echo "")
fi

if [ -z "$DIRECTUS_URL" ] || [ -z "$DIRECTUS_ADMIN_TOKEN" ]; then
    echo "FAIL: DIRECTUS_URL hoặc DIRECTUS_ADMIN_TOKEN không tìm thấy trong env/.env/GSM"
    echo "STOP — Escalate Opus, không hỏi User thao tác kỹ thuật."
    exit 1
fi

HTTP_CODE=$(curl -s -o /dev/null -w "%{http_code}" \
    -H "Authorization: Bearer $DIRECTUS_ADMIN_TOKEN" \
    "$DIRECTUS_URL/server/info")
if [ "$HTTP_CODE" != "200" ]; then
    echo "FAIL: Directus API unreachable hoặc token invalid (HTTP $HTTP_CODE)"
    exit 1
fi
echo "PF-Pre.3 PASS: Directus API + token OK"

# PF-Pre.4: Seed manifest expected JSON exists
SEED_MANIFEST="${SEED_MANIFEST_PATH:-./seed-manifest-g6-expected.json}"
# Nếu chạy trên VPS, agent có thể download từ KB:
#   knowledge/dev/laws/dieu38-trien-khai/seed-manifest-g6-expected.json
# qua agent-data MCP, save về local path.
if [ ! -f "$SEED_MANIFEST" ]; then
    echo "FAIL: Seed manifest expected không tồn tại: $SEED_MANIFEST"
    echo "Opus PHẢI generate manifest trước execution gate (OI-01 RESOLVED v0.2)."
    exit 1
fi
jq empty "$SEED_MANIFEST" 2>/dev/null || {
    echo "FAIL: Seed manifest không phải JSON hợp lệ"
    exit 1
}
TOTAL_EXPECTED=$(jq -r '.total_seed_rows' "$SEED_MANIFEST")
TOTAL_FILES=$(jq -r '.total_files' "$SEED_MANIFEST")
if [ "$TOTAL_EXPECTED" != "61" ] || [ "$TOTAL_FILES" != "8" ]; then
    echo "FAIL: Manifest semantic invalid (total_seed_rows=$TOTAL_EXPECTED, total_files=$TOTAL_FILES)"
    exit 1
fi
echo "PF-Pre.4 PASS: Seed manifest exists and is valid (8 files, 61 rows)"

# Note: nếu manifest hash_status='PENDING_COMPUTE', agent compute hash trên first run,
# ghi action log + warn 'manifest hashes pending Opus update'. KHÔNG block execution.

# PF-Pre.5: Workspace directory cho artifacts
WORKSPACE="/tmp/g6_run_$(date +%Y%m%d_%H%M%S)"
mkdir -p "$WORKSPACE"/{preflight,ddl,verify,rollback,seed}
echo "PF-Pre.5 PASS: workspace=$WORKSPACE"

# Capture run start timestamp (cho V4-05/V4-06)
export RUN_START_TS=$(date -u +"%Y-%m-%d %H:%M:%S+00")
echo "RUN_START_TS=$RUN_START_TS"

Nếu PF-Pre bất kỳ phần nào FAIL → STOP toàn bộ execution. KHÔNG proceed sang PF-01.

§6.1 PF-01 — Schema p9_g6_dryrun KHÔNG được tồn tại (v0.2 GPT R3#1, errata applied)

ERRATA APPLIED: Wording cũ trong package v0.2 §4.1 PF-01 ("DROP rồi re-check") DEPRECATED. Theo errata + HE-18, PF-01 phải STOP + report residue + escalate cleanup gate riêng.

-- PF-01: STOP if schema exists, KHÔNG auto-DROP
SELECT count(*) AS schema_exists 
  FROM information_schema.schemata 
 WHERE schema_name = 'p9_g6_dryrun';
-- Expected: 0

IF schema_exists > 0:

  1. STOP — KHÔNG auto-DROP.
  2. Report residue đầy đủ:
-- Residue listing
SELECT 'table' AS object_type, table_name AS object_name 
  FROM information_schema.tables WHERE table_schema='p9_g6_dryrun'
UNION ALL
SELECT 'function', proname FROM pg_proc p 
  JOIN pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname='p9_g6_dryrun'
UNION ALL
SELECT 'trigger', 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='p9_g6_dryrun' AND NOT tg.tgisinternal
ORDER BY object_type, object_name;
  1. Upload partial action log với section "PRE-FLIGHT FAIL — schema residue from previous run".
  2. Escalate Opus mở cleanup gate riêng trước khi retry G6.
  3. DROP CASCADE chỉ được phép trong rollback của current run (§9), KHÔNG bao giờ trong pre-flight.

§6.2 PF-02 — Production public.* sạch tac_* objects (mở rộng v0.2 GPT R1#4)

-- PF-02 expanded: tables/views/sequences/types/triggers/functions
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')
    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'
    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 >0 → STOP, log objects, escalate Opus (KHÔNG tự xóa production)

§6.3 PF-03 — pg_catalog.sha256 + PG version (v0.2 GPT R1#2 + R2)

-- PF-03a: PG version >= 11 (server_version_num >= 110000)
SELECT 
    current_setting('server_version_num')::int AS version_num,
    (current_setting('server_version_num')::int >= 110000) AS version_ok;
-- Expected: version_num >= 110000, version_ok=true

-- PF-03b: pg_catalog.sha256(bytea) function exists
SELECT count(*) AS sha256_exists
  FROM pg_proc p 
  JOIN pg_namespace n ON n.oid = p.pronamespace 
 WHERE n.nspname = 'pg_catalog' 
   AND p.proname = 'sha256'
   AND pg_get_function_arguments(p.oid) = 'bytea';
-- Expected: 1
-- If 0 → STOP (PG built-in missing — không expected trên PG 16, escalate)

§6.4 PF-04 — FAC-07/08/09 sẵn (Đ24, dùng env từ PF-Pre)

RESPONSE=$(curl -s "${DIRECTUS_URL}/items/taxonomy_facets?filter[code][_in]=FAC-07,FAC-08,FAC-09&filter[status][_eq]=active" \
    -H "Authorization: Bearer ${DIRECTUS_ADMIN_TOKEN}")

FAC_COUNT=$(echo "$RESPONSE" | jq '.data | length')
FAC_IDS=$(echo "$RESPONSE" | jq -c '.data | map(.id) | sort')

if [ "$FAC_COUNT" != "3" ]; then
    echo "FAIL PF-04: expected 3 facets, got $FAC_COUNT"
    exit 1
fi

if [ "$FAC_IDS" != "[8,9,10]" ]; then
    echo "FAIL PF-04: expected ids [8,9,10], got $FAC_IDS"
    exit 1
fi

echo "PF-04 PASS: FAC-07/08/09 active, ids=[8,9,10]"

§6.5 PF-05 — 19 DOT-TAC-* sẵn (Đ35)

RESPONSE=$(curl -s "${DIRECTUS_URL}/items/dot_tools?filter[code][_starts_with]=DOT-TAC-&filter[status][_eq]=active&limit=25" \
    -H "Authorization: Bearer ${DIRECTUS_ADMIN_TOKEN}")

DOT_COUNT=$(echo "$RESPONSE" | jq '.data | length')
DOT_IDS_MIN=$(echo "$RESPONSE" | jq '[.data[].id] | min')
DOT_IDS_MAX=$(echo "$RESPONSE" | jq '[.data[].id] | max')

if [ "$DOT_COUNT" != "19" ]; then
    echo "FAIL PF-05: expected 19 DOTs, got $DOT_COUNT"
    exit 1
fi

if [ "$DOT_IDS_MIN" != "971" ] || [ "$DOT_IDS_MAX" != "989" ]; then
    echo "FAIL PF-05: expected ids in [971,989], got [$DOT_IDS_MIN,$DOT_IDS_MAX]"
    exit 1
fi

echo "PF-05 PASS: 19 DOT-TAC-* active, ids range [971,989]"

§6.6 PF-06 — fn_tac_log_checker_issue sẵn (E-R3)

SELECT 
    p.proname,
    p.prosecdef,
    pg_get_function_arguments(p.oid) AS args
  FROM pg_proc p 
  JOIN pg_namespace n ON n.oid = p.pronamespace 
 WHERE n.nspname = 'public' 
   AND p.proname = 'fn_tac_log_checker_issue';
-- Expected: 1 row, prosecdef=true
-- args contain TEXT,TEXT,TEXT,TEXT,TEXT,JSONB (6 params)

§6.7 PF-07 — Backup tươi (< 6h)

LATEST_BACKUP=$(ls -t /opt/incomex/backup/postgres-*.sql.gz 2>/dev/null | head -1)
if [ -z "$LATEST_BACKUP" ]; then
    echo "WARN: No local backup found, checking GDrive..."
    LATEST_BACKUP_GDRIVE=$(rclone lsl gdrive-backup:incomex-backup/ 2>/dev/null | tail -1)
    if [ -z "$LATEST_BACKUP_GDRIVE" ]; then
        echo "FAIL PF-07: No backup found locally or on GDrive"
        exit 1
    fi
    echo "GDrive backup: $LATEST_BACKUP_GDRIVE"
else
    BACKUP_AGE_HOURS=$(( ($(date +%s) - $(stat -c %Y "$LATEST_BACKUP")) / 3600 ))
    echo "Latest backup: $LATEST_BACKUP, age: ${BACKUP_AGE_HOURS}h"
    if [ "$BACKUP_AGE_HOURS" -ge 6 ]; then
        echo "FAIL PF-07: Backup older than 6h"
        exit 1
    fi
fi

echo "PF-07 PASS: backup tươi"

§6.8 PF-08 — TRIGGER-GUARD informational mode

SELECT 
    evtname, 
    evtevent, 
    evtenabled,
    pg_catalog.pg_get_userbyid(evtowner) AS owner
  FROM pg_event_trigger
 WHERE evtenabled IN ('A', 'O', 'R')
 ORDER BY evtname;
-- Document output. If any event trigger BLOCKS DDL → escalate, không proceed

§6.9 PF-09 — Connection identity + permission

SELECT 
    current_user,
    current_database(),
    has_database_privilege(current_user, current_database(), 'CREATE') AS can_create_schema,
    rolcreatedb,
    rolsuper
  FROM pg_roles
 WHERE rolname = current_user;
-- Expected: 
--   current_database = 'directus'
--   can_create_schema = true (or rolsuper = true, or rolcreatedb = true)
-- If can_create_schema=false → STOP, escalate (KHÔNG tự GRANT)

§6.10 PF-10 — Search path baseline

SHOW search_path;
-- Document. Sau khi schema tạo (§7.2 step 1), agent SET search_path TO p9_g6_dryrun, pg_catalog

§6.11 STOP gate sau pre-flight

Nếu BẤT KỲ PF-Pre / PF-01→PF-10 FAIL:

  1. STOP — không CREATE SCHEMA, không DDL.
  2. Document fail reason + raw output.
  3. Upload partial action log lên KB: reports/p9-g6-execution-log-2026-04-27.md với section "PRE-FLIGHT FAIL".
  4. Escalate Opus.
  5. KHÔNG tự sửa hoặc retry mà không có Opus authorize.

§7 Execution phase — CHỈ KHI ĐÃ pre-flight PASS

§7.1 Plan-then-execute pattern (v0.2 GPT R3#6)

Agent:

  1. Đọc package v0.2 §5 (DDL 14 tables), §6 (6 functions/triggers), §7 (61 seed rows) qua agent-data MCP.
  2. Download 8 seed SQL files từ KB knowledge/dev/laws/dieu38-trien-khai/seed-g6/seed-tac-*.sql về $WORKSPACE/seed/.
  3. Generate DDL bundle file $WORKSPACE/ddl/g6_execution_bundle.sql chứa:
    • SET search_path TO p9_g6_dryrun, pg_catalog;
    • DDL phase: 14 tables (groups A→E theo package §5.2-§5.6)
    • Functions phase: 6 functions + 6 triggers (theo package §6.1-§6.6)
    • Seed phase: 8 \i directives include từ $WORKSPACE/seed/seed-tac-*.sql
  4. Compute SHA-256 hash của bundle + 8 seed files:
sha256sum "$WORKSPACE/ddl/g6_execution_bundle.sql" > "$WORKSPACE/ddl/bundle.sha256"
DDL_BUNDLE_HASH=$(awk '{print $1}' "$WORKSPACE/ddl/bundle.sha256")

# Compute hash của 8 seed files
sha256sum "$WORKSPACE"/seed/seed-tac-*.sql > "$WORKSPACE/seed/seeds.sha256"

# Compare với manifest (nếu manifest hashes != PENDING_COMPUTE)
MANIFEST_STATUS=$(jq -r '.hash_status' "$SEED_MANIFEST")
if [ "$MANIFEST_STATUS" = "PENDING_COMPUTE" ]; then
    echo "WARN: Manifest hashes PENDING_COMPUTE. Computed hashes recorded in action log for Opus update."
    # Agent ghi computed hashes vào action log §0
else
    # Verify hash match per file
    for seed_file in "$WORKSPACE"/seed/seed-tac-*.sql; do
        FILENAME=$(basename "$seed_file")
        ACTUAL_HASH=$(sha256sum "$seed_file" | awk '{print $1}')
        EXPECTED_HASH=$(jq -r ".files[] | select(.path | endswith(\"$FILENAME\")) | .sha256" "$SEED_MANIFEST")
        if [ "$ACTUAL_HASH" != "$EXPECTED_HASH" ]; then
            echo "FAIL F-04: Seed hash mismatch for $FILENAME"
            echo "  Expected: $EXPECTED_HASH"
            echo "  Actual:   $ACTUAL_HASH"
            exit 1
        fi
    done
fi

echo "DDL bundle SHA-256: $DDL_BUNDLE_HASH"
echo "Bundle file: $WORKSPACE/ddl/g6_execution_bundle.sql"
  1. Run grep tests (§7.3) trên $WORKSPACE/ddl/g6_execution_bundle.sql + 8 seed files.
  2. Execute bundle với psql ... -v ON_ERROR_STOP=1 -f $WORKSPACE/ddl/g6_execution_bundle.sql trong single transaction nếu có thể (BEGIN/COMMIT). Nếu fail → ROLLBACK.
  3. Sau script PASS → tiến hành verification §8.
  4. Action log §10 BẮT BUỘC ghi DDL_BUNDLE_HASH + bundle_path + 8 seed hashes + RUN_START_TS.

§7.2 DDL execution order (BẮT BUỘC)

Theo package v0.2 §5:

1. CREATE SCHEMA p9_g6_dryrun;
2. SET search_path TO p9_g6_dryrun, pg_catalog;
3. Group A — 5 lifecycle vocab tables (no FK dependencies)
4. Group B — 2 type vocab tables (no FK dependencies)
5. Group C — 1 config table (no FK dependencies)
6. Group D — 4 core tables (FK to Group A+B)
   - tac_logical_unit (FK section_type_vocab + lu_lifecycle_vocab)
   - tac_unit_version (FK logical_unit + uv_lifecycle_vocab + review_state_vocab)
   - tac_publication (FK publication_type_vocab + pub_lifecycle_vocab)
   - tac_publication_member (FK publication + logical_unit + unit_version)
7. Group E — 2 change-set hook tables (FK to Group A+D)
   - tac_change_set (FK publication + cs_lifecycle_vocab)
   - tac_change_set_member (FK change_set + logical_unit + unit_version)
8. Functions — 6 functions (depend on tables existing)
9. Triggers — 6 triggers (depend on functions + tables)
10. Seed — \i 8 seed files (61 rows)

§7.3 Schema-qualify enforcement (grep tests on actual bundle)

Chạy trên $WORKSPACE/ddl/g6_execution_bundle.sql và 8 seed files (the actual canonical files):

BUNDLE="$WORKSPACE/ddl/g6_execution_bundle.sql"
SEED_FILES=("$WORKSPACE"/seed/seed-tac-*.sql)

# Test 1: Không có unqualified DDL trên tac_* objects
if grep -nE '^\s*(CREATE|ALTER|DROP)\s+(TABLE|FUNCTION|TRIGGER|INDEX|VIEW|SEQUENCE|TYPE)\s+(IF\s+(NOT\s+)?EXISTS\s+)?(tac_|fn_tac_|trg_tac_)' "$BUNDLE" "${SEED_FILES[@]}" | \
   grep -v 'p9_g6_dryrun\.'; then
    echo "FAIL: unqualified DDL detected on tac_* objects"
    exit 1
fi
echo "Test 1 PASS: all tac_* DDL schema-qualified"

# Test 2: Không có CREATE EXTENSION
if grep -niE '^\s*CREATE\s+EXTENSION' "$BUNDLE" "${SEED_FILES[@]}"; then
    echo "FAIL: CREATE EXTENSION detected (HE-17 violation)"
    exit 1
fi
echo "Test 2 PASS: no CREATE EXTENSION"

# Test 3: Không hardcode -U directus
if grep -niE '\-U\s+directus' "$BUNDLE" "${SEED_FILES[@]}"; then
    echo "FAIL: hardcoded -U directus detected (HE-14 violation)"
    exit 1
fi
echo "Test 3 PASS: no hardcoded user"

# Test 4: Seed files schema-qualify INSERT INTO p9_g6_dryrun.*
for seed_file in "${SEED_FILES[@]}"; do
    if grep -nE '^\s*INSERT\s+INTO\s+(?!p9_g6_dryrun\.)' "$seed_file"; then
        echo "FAIL: unqualified INSERT detected in $seed_file"
        exit 1
    fi
done
echo "Test 4 PASS: all seed INSERTs schema-qualified to p9_g6_dryrun.*"

# Test 5: Không có DROP SCHEMA p9_g6_dryrun trong bundle (DROP chỉ ở rollback phase riêng)
if grep -niE 'DROP\s+SCHEMA\s+p9_g6_dryrun' "$BUNDLE"; then
    echo "FAIL: DROP SCHEMA detected in execution bundle (rollback chỉ ở §9)"
    exit 1
fi
echo "Test 5 PASS: no DROP SCHEMA in execution bundle"

§7.4 Seed SHA-256 verification

Đã handle trong §7.1 step 4. Manifest verify protocol:

  • If hash_status='PENDING_COMPUTE': agent compute, ghi action log, không block.
  • If hashes are computed: agent compare actual vs expected, mismatch → STOP F-04.

§7.5 Execution STOP conditions

# Condition Action
F-01 Pre-flight ANY fail (gồm PF-Pre) Đã handle ở §6.11
F-02 DDL compile error Rollback transaction, document error, STOP
F-03 Function PL/pgSQL parse error Rollback, document, STOP
F-04 Seed SHA-256 mismatch (nếu manifest có hash) STOP, regen seed, re-hash
F-05 Constraint smoke V3 expected SQLSTATE không xảy ra FAIL, document expected vs actual SQLSTATE, rollback schema, STOP
F-06 V4 production isolation fail CRITICAL — immediate rollback + escalate
F-07 Rollback không clean CRITICAL — manual cleanup + escalate
F-08 Hard exclusion violation CRITICAL — immediate rollback + escalate
F-09 dot_action_log có rows từ G6 run CRITICAL — vi phạm scope, immediate audit
F-10 Schema p9_g6_dryrun đã tồn tại trước execution (PF-01 fail) STOP — escalate cleanup gate
F-11 DDL bundle hash drift giữa generation và execution STOP — re-generate bundle, re-hash, re-verify

Abort conditions (per package v0.2 §15.3): A-01 production touched · A-02 TRIGGER-GUARD blocks · A-03 backup không tươi · A-04 connection permission insufficient · A-05 agent runaway · A-06 PG version < 11.

§8 Verification phase — embed full SQL

§8.1 Stage 1 — Compile verify (V1-01 → V1-10)

-- V1-01: Schema exists
SELECT 1 FROM information_schema.schemata WHERE schema_name='p9_g6_dryrun';
-- Expected: 1 row

-- V1-02: 14 tables
SELECT table_name 
  FROM information_schema.tables 
 WHERE table_schema='p9_g6_dryrun' 
   AND table_type='BASE TABLE'
 ORDER BY table_name;
-- Expected: 14 rows

-- V1-03: FK constraints
SELECT count(*) AS fk_count
  FROM information_schema.table_constraints 
 WHERE table_schema='p9_g6_dryrun' 
   AND constraint_type='FOREIGN KEY';
-- Expected: >= 12

-- V1-04: UNIQUE constraints
SELECT count(*) AS unique_count
  FROM information_schema.table_constraints 
 WHERE table_schema='p9_g6_dryrun' 
   AND constraint_type='UNIQUE';
-- Expected: >= 2

-- V1-05: CHECK constraints
SELECT count(*) AS check_count
  FROM information_schema.table_constraints 
 WHERE table_schema='p9_g6_dryrun' 
   AND constraint_type='CHECK';
-- Expected: >= 24

-- V1-06: Indexes
SELECT count(*) AS index_count
  FROM pg_indexes 
 WHERE schemaname='p9_g6_dryrun';
-- Expected: >= 21

-- V1-07: 6 functions exist
SELECT proname 
  FROM pg_proc 
 WHERE pronamespace='p9_g6_dryrun'::regnamespace 
 ORDER BY proname;
-- Expected: 6 rows

-- V1-08: 6 triggers attached
SELECT trigger_schema, trigger_name, event_manipulation, event_object_table 
  FROM information_schema.triggers 
 WHERE trigger_schema='p9_g6_dryrun' 
 ORDER BY trigger_name, event_manipulation;
-- Expected: 6 distinct trigger_name

-- V1-09: All functions SECURITY DEFINER
SELECT proname, prosecdef 
  FROM pg_proc 
 WHERE pronamespace='p9_g6_dryrun'::regnamespace;
-- Expected: 6 rows, ALL prosecdef=true

-- V1-10: All functions search_path locked
SELECT proname, proconfig 
  FROM pg_proc 
 WHERE pronamespace='p9_g6_dryrun'::regnamespace;
-- Expected: 6 rows, ALL proconfig contain 'search_path=p9_g6_dryrun, pg_catalog'

§8.2 Stage 2 — Seed verify (V2-01 → V2-10)

-- V2-01 → V2-08: Count per vocab table
SELECT 'tac_lu_lifecycle_vocab' AS tbl, count(*) FROM p9_g6_dryrun.tac_lu_lifecycle_vocab UNION ALL
SELECT 'tac_uv_lifecycle_vocab', count(*) FROM p9_g6_dryrun.tac_uv_lifecycle_vocab UNION ALL
SELECT 'tac_review_state_vocab', count(*) FROM p9_g6_dryrun.tac_review_state_vocab UNION ALL
SELECT 'tac_pub_lifecycle_vocab', count(*) FROM p9_g6_dryrun.tac_pub_lifecycle_vocab UNION ALL
SELECT 'tac_cs_lifecycle_vocab', count(*) FROM p9_g6_dryrun.tac_cs_lifecycle_vocab UNION ALL
SELECT 'tac_section_type_vocab', count(*) FROM p9_g6_dryrun.tac_section_type_vocab UNION ALL
SELECT 'tac_publication_type_vocab', count(*) FROM p9_g6_dryrun.tac_publication_type_vocab UNION ALL
SELECT 'tac_birth_gate_config', count(*) FROM p9_g6_dryrun.tac_birth_gate_config;
-- Expected: 3, 4, 5, 4, 7, 17, 10, 11

-- V2-09: Total
SELECT (
    (SELECT count(*) FROM p9_g6_dryrun.tac_lu_lifecycle_vocab) +
    (SELECT count(*) FROM p9_g6_dryrun.tac_uv_lifecycle_vocab) +
    (SELECT count(*) FROM p9_g6_dryrun.tac_review_state_vocab) +
    (SELECT count(*) FROM p9_g6_dryrun.tac_pub_lifecycle_vocab) +
    (SELECT count(*) FROM p9_g6_dryrun.tac_cs_lifecycle_vocab) +
    (SELECT count(*) FROM p9_g6_dryrun.tac_section_type_vocab) +
    (SELECT count(*) FROM p9_g6_dryrun.tac_publication_type_vocab) +
    (SELECT count(*) FROM p9_g6_dryrun.tac_birth_gate_config)
) AS total_seed_rows;
-- Expected: 61

-- V2-10: SHA-256 manifest match (đã verify ở §7.1 step 4)

§8.3 Stage 3 — Constraint behavior smoke (V3-01 → V3-08, v0.2 GPT R3#3 — DO block harness)

Pattern: Mỗi sub-test dùng PL/pgSQL DO block với EXCEPTION clause để catch expected error code. Ghi expected vs actual SQLSTATE vào output. KHÔNG raw INSERT expected FAIL — sẽ kill psql ON_ERROR_STOP.

-- V3-01 (UNIQUE): Duplicate canonical_address → expected SQLSTATE 23505
DO $$
DECLARE
    v_first_id UUID;
    v_actual_sqlstate TEXT;
BEGIN
    INSERT INTO p9_g6_dryrun.tac_logical_unit 
        (canonical_address, doc_code, section_type, owner) 
    VALUES ('D38-G6TEST-ROOT', 'D38-G6TEST', 'heading', 'g6-test')
    RETURNING id INTO v_first_id;
    
    RAISE NOTICE 'V3-01 setup: first insert id=%', v_first_id;
    
    BEGIN
        INSERT INTO p9_g6_dryrun.tac_logical_unit 
            (canonical_address, doc_code, section_type, owner) 
        VALUES ('D38-G6TEST-ROOT', 'D38-G6TEST', 'heading', 'g6-test');
        
        RAISE EXCEPTION 'V3-01 FAIL: expected unique_violation but insert succeeded';
    EXCEPTION
        WHEN unique_violation THEN
            v_actual_sqlstate := SQLSTATE;
            RAISE NOTICE 'V3-01 PASS: expected=23505, actual=%, message=%', v_actual_sqlstate, SQLERRM;
    END;
    
    DELETE FROM p9_g6_dryrun.tac_logical_unit WHERE canonical_address='D38-G6TEST-ROOT';
END $$;

-- V3-02 (FK): section_type không tồn tại → expected SQLSTATE 23503
DO $$
DECLARE
    v_actual_sqlstate TEXT;
BEGIN
    BEGIN
        INSERT INTO p9_g6_dryrun.tac_logical_unit 
            (canonical_address, doc_code, section_type, owner) 
        VALUES ('D38-G6TEST-S1', 'D38-G6TEST', 'nonexistent_type', 'g6-test');
        
        RAISE EXCEPTION 'V3-02 FAIL: expected foreign_key_violation';
    EXCEPTION
        WHEN foreign_key_violation THEN
            v_actual_sqlstate := SQLSTATE;
            RAISE NOTICE 'V3-02 PASS: expected=23503, actual=%, message=%', v_actual_sqlstate, SQLERRM;
    END;
END $$;

-- V3-03 (CHECK): risk_tier ngoài enum → expected SQLSTATE 23514
DO $$
DECLARE
    v_actual_sqlstate TEXT;
BEGIN
    BEGIN
        INSERT INTO p9_g6_dryrun.tac_publication 
            (doc_code, version, publication_type, name, owner, risk_tier) 
        VALUES ('D38-G6TEST', 'v0', 'design_note', 'G6 Test', 'g6-test', 'invalid_tier');
        
        RAISE EXCEPTION 'V3-03 FAIL: expected check_violation';
    EXCEPTION
        WHEN check_violation THEN
            v_actual_sqlstate := SQLSTATE;
            RAISE NOTICE 'V3-03 PASS: expected=23514, actual=%, message=%', v_actual_sqlstate, SQLERRM;
    END;
END $$;

-- V3-04 (NOT NULL): owner thiếu → expected SQLSTATE 23502
DO $$
DECLARE
    v_actual_sqlstate TEXT;
BEGIN
    BEGIN
        INSERT INTO p9_g6_dryrun.tac_logical_unit 
            (canonical_address, doc_code, section_type, owner) 
        VALUES ('D38-G6TEST-S2', 'D38-G6TEST', 'heading', NULL);
        
        RAISE EXCEPTION 'V3-04 FAIL: expected not_null_violation';
    EXCEPTION
        WHEN not_null_violation THEN
            v_actual_sqlstate := SQLSTATE;
            RAISE NOTICE 'V3-04 PASS: expected=23502, actual=%, message=%', v_actual_sqlstate, SQLERRM;
    END;
END $$;

-- V3-05 (Trigger BG-LU-01): canonical_address regex fail → expected SQLSTATE 23514
DO $$
DECLARE
    v_actual_sqlstate TEXT;
BEGIN
    BEGIN
        INSERT INTO p9_g6_dryrun.tac_logical_unit 
            (canonical_address, doc_code, section_type, owner) 
        VALUES ('WRONG-FORMAT', 'D38-G6TEST', 'heading', 'g6-test');
        
        RAISE EXCEPTION 'V3-05 FAIL: expected check_violation from trigger BG-LU-01';
    EXCEPTION
        WHEN check_violation THEN
            v_actual_sqlstate := SQLSTATE;
            RAISE NOTICE 'V3-05 PASS: expected=23514 (trigger), actual=%, message=%', v_actual_sqlstate, SQLERRM;
    END;
END $$;

-- V3-06 (Trigger BG-LU-03): parent doc_code khác child → expected SQLSTATE 23514
DO $$
DECLARE
    v_parent_id UUID;
    v_actual_sqlstate TEXT;
BEGIN
    INSERT INTO p9_g6_dryrun.tac_logical_unit 
        (canonical_address, doc_code, section_type, owner) 
    VALUES ('D38-PARENT-ROOT', 'D38-PARENT', 'heading', 'g6-test')
    RETURNING id INTO v_parent_id;
    
    BEGIN
        INSERT INTO p9_g6_dryrun.tac_logical_unit 
            (canonical_address, doc_code, parent_id, section_type, owner) 
        VALUES ('D38-CHILD-ROOT', 'D38-CHILD', v_parent_id, 'heading', 'g6-test');
        
        RAISE EXCEPTION 'V3-06 FAIL: expected check_violation from trigger BG-LU-03';
    EXCEPTION
        WHEN check_violation THEN
            v_actual_sqlstate := SQLSTATE;
            RAISE NOTICE 'V3-06 PASS: expected=23514 (trigger BG-LU-03), actual=%, message=%', v_actual_sqlstate, SQLERRM;
    END;
    
    DELETE FROM p9_g6_dryrun.tac_logical_unit WHERE canonical_address='D38-PARENT-ROOT';
END $$;

-- V3-07 (Trigger PM-CONSISTENCY): pm.lu_id ≠ uv.lu_id → expected SQLSTATE 23514
DO $$
DECLARE
    v_lu1_id UUID;
    v_lu2_id UUID;
    v_uv_id UUID;
    v_pub_id UUID;
    v_actual_sqlstate TEXT;
BEGIN
    INSERT INTO p9_g6_dryrun.tac_logical_unit 
        (canonical_address, doc_code, section_type, owner) 
    VALUES ('D38-G6TEST-LU1', 'D38-G6TEST', 'paragraph', 'g6-test')
    RETURNING id INTO v_lu1_id;
    
    INSERT INTO p9_g6_dryrun.tac_logical_unit 
        (canonical_address, doc_code, section_type, owner) 
    VALUES ('D38-G6TEST-LU2', 'D38-G6TEST', 'paragraph', 'g6-test')
    RETURNING id INTO v_lu2_id;
    
    INSERT INTO p9_g6_dryrun.tac_unit_version 
        (logical_unit_id, title, body, description) 
    VALUES (v_lu1_id, 'Test UV', 'body content', 'test desc')
    RETURNING id INTO v_uv_id;
    
    INSERT INTO p9_g6_dryrun.tac_publication 
        (doc_code, version, publication_type, name, owner) 
    VALUES ('D38-G6TEST', 'v0', 'design_note', 'G6 Test', 'g6-test')
    RETURNING id INTO v_pub_id;
    
    BEGIN
        INSERT INTO p9_g6_dryrun.tac_publication_member 
            (publication_id, logical_unit_id, unit_version_id) 
        VALUES (v_pub_id, v_lu2_id, v_uv_id);
        
        RAISE EXCEPTION 'V3-07 FAIL: expected check_violation from trigger PM-CONSISTENCY';
    EXCEPTION
        WHEN check_violation THEN
            v_actual_sqlstate := SQLSTATE;
            RAISE NOTICE 'V3-07 PASS: expected=23514 (PM-CONSISTENCY), actual=%, message=%', v_actual_sqlstate, SQLERRM;
    END;
    
    DELETE FROM p9_g6_dryrun.tac_unit_version WHERE id=v_uv_id;
    DELETE FROM p9_g6_dryrun.tac_logical_unit WHERE id IN (v_lu1_id, v_lu2_id);
    DELETE FROM p9_g6_dryrun.tac_publication WHERE id=v_pub_id;
END $$;

-- V3-08 (Cleanup verify): 0 test rows remaining
SELECT 
    (SELECT count(*) FROM p9_g6_dryrun.tac_logical_unit WHERE doc_code LIKE 'D38-G6TEST%' OR doc_code LIKE 'D38-PARENT' OR doc_code LIKE 'D38-CHILD') AS lu_test_remaining,
    (SELECT count(*) FROM p9_g6_dryrun.tac_unit_version WHERE title='Test UV') AS uv_test_remaining,
    (SELECT count(*) FROM p9_g6_dryrun.tac_publication WHERE doc_code LIKE 'D38-G6TEST%') AS pub_test_remaining,
    (SELECT count(*) FROM p9_g6_dryrun.tac_publication_member) AS pm_test_remaining;
-- Expected: 0, 0, 0, 0

Action log §10 PHẢI ghi expected vs actual SQLSTATE cho mỗi V3-01→V3-07.

§8.4 Stage 4 — Production isolation verify (V4-01 → V4-07)

-- V4-01: Re-run PF-02 expanded
-- Same query as PF-02 in §6.2
-- Expected: total_violations=0

-- V4-02: public.fn_tac_* chỉ có whitelisted
SELECT count(*), array_agg(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 '\';
-- Expected: count=1, array=['fn_tac_log_checker_issue']

-- V4-03: taxonomy_facets không thay đổi (Directus API)
-- V4-04: dot_tools không thay đổi (Directus API)

§8.4.1 V4-05/V4-06 schema-adaptive (v0.2 GPT R3#5)

-- V4-05: system_issues schema-adaptive query
-- Step 1: Introspect columns
WITH cols AS (
    SELECT column_name 
      FROM information_schema.columns 
     WHERE table_schema='public' AND table_name='system_issues'
)
SELECT 
    array_agg(column_name ORDER BY column_name) AS available_columns,
    bool_or(column_name='created_at') AS has_created_at,
    bool_or(column_name='details') AS has_details,
    bool_or(column_name='coalesce_key') AS has_coalesce_key,
    bool_or(column_name='evidence_snapshot') AS has_evidence_snapshot,
    (SELECT count(*) FROM cols) AS total_columns
  FROM cols;

-- Step 2: Agent build adaptive query based on available columns
-- Pseudo:
--   IF has_created_at AND has_details:
--     SELECT count(*) FROM public.system_issues 
--       WHERE created_at > '${RUN_START_TS}' 
--         AND details::text LIKE '%p9_g6_dryrun%';
--   ELIF has_created_at AND has_evidence_snapshot:
--     SELECT count(*) FROM public.system_issues 
--       WHERE created_at > '${RUN_START_TS}' 
--         AND evidence_snapshot::text LIKE '%p9_g6_dryrun%';
--   ELSE:
--     report "V4-05 N/A: schema không match expected columns. Available: <list>"

-- V4-06: dot_action_log schema-adaptive query (mở rộng v0.2 GPT R2 + R3#5)
WITH 
table_check AS (
    SELECT count(*) AS table_exists 
      FROM information_schema.tables 
     WHERE table_schema='public' AND table_name='dot_action_log'
),
cols AS (
    SELECT column_name 
      FROM information_schema.columns 
     WHERE table_schema='public' AND table_name='dot_action_log'
)
SELECT 
    (SELECT table_exists FROM table_check) AS table_exists,
    array_agg(column_name ORDER BY column_name) AS available_columns,
    bool_or(column_name='created_at') AS has_created_at,
    bool_or(column_name='action_metadata') AS has_action_metadata,
    bool_or(column_name='tool_code') AS has_tool_code,
    bool_or(column_name='payload') AS has_payload,
    bool_or(column_name='message') AS has_message
  FROM cols;

-- Step 2: Agent build adaptive query
-- Pseudo:
--   IF NOT table_exists:
--     report "V4-06 N/A: table dot_action_log không tồn tại"
--     PASS by default (HE-08 vacuously holds)
--   ELSE:
--     conditions = []
--     IF has_action_metadata: conditions.append("action_metadata::text LIKE '%p9_g6_dryrun%'")
--     IF has_tool_code: conditions.append("tool_code LIKE 'DOT-TAC-%'")
--     IF has_payload: conditions.append("coalesce(payload::text, '') LIKE '%p9_g6_dryrun%'")
--     IF has_message: conditions.append("coalesce(message, '') LIKE '%p9_g6_dryrun%'")
--     query = f"SELECT count(*) FROM public.dot_action_log WHERE created_at > '{RUN_START_TS}' AND ({' OR '.join(conditions)})"
--     execute → expect count=0

-- V4-07: Backup integrity (sau G6, trước rollback)
-- Re-run rclone check GDrive backup

§9 Rollback phase

§9.1 Rollback steps

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

-- Step 2: DROP SCHEMA CASCADE (rollback của current run — HỢP LỆ per HE-18)
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

SELECT count(*) 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 = 'p9_g6_dryrun';
-- Expected: 0

§9.2 Rollback PASS criteria (R-01 → R-08)

Per package v0.2 §9.2. Re-run V4-01→V4-04 sau rollback để confirm production unchanged.

§9.3 Rollback timing

  • Rollback chạy NGAY SAU khi V1-V4 verification PASS hoặc bất kỳ FAIL/Abort condition trigger.
  • Schema p9_g6_dryrun KHÔNG được tồn tại quá 24h.
  • Sau rollback → archive log → upload KB report.

§10 Action log / Báo cáo template (v0.2 GPT R3#3 — SQLSTATE + #6 — DDL hash)

§10.1 Output path

knowledge/dev/laws/dieu38-trien-khai/reports/p9-g6-execution-log-2026-04-27.md

§10.2 Template structure

# P9 G6 — Execution Log

> Phiên: S{xxx} | Ngày: 2026-04-{date}
> Agent: Claude CLI / Claude Code
> Bám: P9-G6-execution-prompt.md v0.2
> Status: {PASS / FAIL / ABORT}

## 0. Run metadata

| Field | Value |
|---|---|
| RUN_START_TS | ${RUN_START_TS} |
| ENV_FILE discovered | ${ENV_FILE} |
| Workspace | ${WORKSPACE} |
| DDL bundle path | ${WORKSPACE}/ddl/g6_execution_bundle.sql |
| **DDL bundle SHA-256** | ${DDL_BUNDLE_HASH} |
| Seed manifest path | ${SEED_MANIFEST} |
| Seed manifest SHA-256 verify | PASS / PENDING_COMPUTE first run |
| Computed seed hashes (if first run) | (8 hashes ghi cho Opus update manifest) |
| Connection actor | (current_user from PF-09) |
| PG version | (server_version_num from PF-03) |

## 1. PF-Pre results

[table with results]

## 2. Pre-flight results (PF-01 → PF-10)

[table with results]

## 3. Execution phase

### 3.1 Schema bootstrap
- DDL bundle file: ${WORKSPACE}/ddl/g6_execution_bundle.sql
- DDL bundle SHA-256: ${DDL_BUNDLE_HASH}
- Grep tests on bundle + 8 seed files: PASS/FAIL (5 tests)

### 3.2 DDL phase
- 14 tables created: PASS/FAIL
- ~21 indexes: actual count

### 3.3 Functions/triggers
- 6 functions: PASS/FAIL
- 6 triggers: PASS/FAIL

### 3.4 Seed phase
- 61 rows inserted: PASS/FAIL
- SHA-256 manifest match: PASS/PENDING_COMPUTE_first_run

## 4. Verification results

### 4.1 Stage 1 — Compile (V1-01 → V1-10)
[table]

### 4.2 Stage 2 — Seed (V2-01 → V2-10)
[table]

### 4.3 Stage 3 — Constraint smoke (V3-01 → V3-08) — SQLSTATE harness
| V | Constraint type | Expected SQLSTATE | Actual SQLSTATE | Match? | NOTICE message |
|---|---|---|---|---|---|
| V3-01 | UNIQUE | 23505 | ... | YES/NO | ... |
| V3-02 | FK | 23503 | ... | YES/NO | ... |
| V3-03 | CHECK | 23514 | ... | YES/NO | ... |
| V3-04 | NOT NULL | 23502 | ... | YES/NO | ... |
| V3-05 | Trigger BG-LU-01 | 23514 | ... | YES/NO | ... |
| V3-06 | Trigger BG-LU-03 | 23514 | ... | YES/NO | ... |
| V3-07 | Trigger PM-CONSISTENCY | 23514 | ... | YES/NO | ... |
| V3-08 | Cleanup verify | 0 | ... | PASS/FAIL | ... |

### 4.4 Stage 4 — Production isolation (V4-01 → V4-07)
[table với V4-05/06 introspection results]

## 5. Rollback results

[table]

## 6. Hard exclusion compliance

[HE-01 → HE-19]

## 7. Errors / Issues / Blockers

[Liệt kê chi tiết]

## 8. Conclusion

- G6 PASS: yes/no
- 15/15 PASS criteria: yes/no
- Recommendation: G8 / re-run / patch package

## 9. Evidence files (local agent workspace)

[File listing]

§10.3 Upload KB

Agent upload action log lên KB qua agent-data MCP sau khi tất cả phase complete.

§11 Tự kiểm tra (BẢNG TỰ KIỂM TRA TRƯỚC KHI BÁO HOÀN THÀNH)

Agent PHẢI trả lời TẤT CẢ 22 mục dưới với evidence cụ thể trước khi báo PASS:

# Mục tiêu ĐÃ ĐẠT? Bằng chứng
1 Đọc đủ 5 docs §1 (gồm errata) ? Quote 4 dòng
2 5-GATE + Assembly Gate trả lời đầy đủ ? Section §2 trả lời
3 PF-Pre 5 checks PASS (tools + .env + Directus + manifest + workspace) ? PF-Pre output
4 All 10 PF-01→PF-10 PASS ? PF-01→PF-10 output
5 PF-01: schema p9_g6_dryrun KHÔNG tồn tại trước (KHÔNG auto-DROP per errata) ? PF-01 output
6 DDL bundle generated + SHA-256 hash recorded ? bundle path + hash
7 Grep tests trên DDL bundle + 8 seed files PASS (5 tests) ? grep output
8 Schema p9_g6_dryrun created ? V1-01
9 14 tables compile ? V1-02
10 FK ≥12, UNIQUE ≥2, CHECK ≥24 ? V1-03/04/05
11 Indexes ≥21 ? V1-06
12 6 functions + 6 triggers, all SECURITY DEFINER + search_path locked ? V1-07/08/09/10
13 Seed 61 rows, SHA-256 match manifest (hoặc PENDING_COMPUTE first run) ? V2
14 V3-01→V3-07 SQLSTATE expected = actual (DO block harness) ? V3 NOTICE messages
15 V3-08 cleanup verify 0 residue ? V3-08
16 Production isolation V4-01→V4-04 PASS ? V4
17 V4-05 system_issues schema-adaptive (PASS hoặc N/A có evidence) ? V4-05 introspection
18 V4-06 dot_action_log schema-adaptive (PASS hoặc N/A có evidence) ? V4-06 introspection
19 Rollback DROP CASCADE → 0 residue ? §9.1
20 Hard exclusions HE-01→HE-19 không vi phạm ? §10 audit
21 Action log markdown uploaded KB đúng path (gồm DDL bundle hash + 8 seed hashes) ? KB GET verify
22 Không hardcode credentials, không CREATE EXTENSION, mọi DDL schema-qualified ? grep tests §7.3

Nếu BẤT KỲ mục nào CHƯA ĐẠT → KHÔNG báo PASS. Agent escalate Opus với evidence.

§12 Output paths

File Path Khi nào upload
Action log knowledge/dev/laws/dieu38-trien-khai/reports/p9-g6-execution-log-2026-04-27.md Sau khi tất cả phase complete (PASS hoặc FAIL/ABORT)
Pre-flight raw outputs local agent workspace ${WORKSPACE}/preflight/ Trong execution
DDL bundle + hash local ${WORKSPACE}/ddl/g6_execution_bundle.sql + .sha256 Trong execution
Grep tests output local ${WORKSPACE}/ddl/grep-tests-output.txt Trong execution
Verification outputs local ${WORKSPACE}/verify/ Trong execution
Rollback outputs local ${WORKSPACE}/rollback/ Trong execution
Seed manifest expected knowledge/dev/laws/dieu38-trien-khai/seed-manifest-g6-expected.json (KB, OI-01 RESOLVED v0.2)
Seed canonical files knowledge/dev/laws/dieu38-trien-khai/seed-g6/seed-tac-*.sql (KB, 8 files)

§13 STOP — sau khi báo cáo

Sau khi upload action log, agent DỪNG, KHÔNG tự:

  • Mở G8 hoặc bất kỳ gate tiếp theo
  • Tạo Directus roles
  • Activate cron
  • Chạy P9 production migration
  • Bất kỳ mutation nào ngoài G6 scope

Báo Opus + GPT review action log. Opus sẽ chuyển User để authorize G6 PASS → G8.

§14 Note — bài học S183 baked

# Sai lầm S183 G6 mitigation
1 E-R3 v0.1 hardcode -U directus §5 Connection setup (.env discover + $DB_USER) + grep test §7.3
2 E-R3 v0.1 thiếu schema-qualify §3 G4 + §7.3 grep test trên actual bundle + mọi DDL trong §7.2 schema-qualified
3 E7 v0.1 domain FK "tac.*" invalid §6 PF-04/05 verify FAC + DOT exists trước, không đoán
4 E4 v0.1 scope lệch (facets + labels gộp) §3 mục tiêu mở 15 PASS criteria + §4 hard exclusions explicit (HE-01→19)
5 Opus hỏi User "gate mở chưa?" quá nhiều §13 sau báo cáo agent DỪNG, không chủ động hỏi gate tiếp theo

§15 Patch log

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

  • Initial draft sau GPT R2 PASS package v0.2
  • 17 hard exclusions, 18 mục tự kiểm tra

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

# Patch Áp dụng
1 PF-01 không auto-DROP — STOP + report residue §6.1, HE-18, F-10
2 PF-Pre tools + env verify NEW §6.0, HE-19
3 V3 PL/pgSQL DO harness + SQLSTATE §8.3 rewrite
4 V4-05/V4-06 schema-adaptive §8.4.1 new
5 DDL bundle hash + grep on actual bundle §7.1, §7.3
6 22 mục tự kiểm tra §11 expanded

v0.2 finalization (S184, 2026-04-27) — GPT R4 patches (5 finalization items)

# Item Resolution
1 Persist full prompt KB This file (full content, not summary)
2 PF-01 precedence/errata P9-G6-precedence-errata.md + §-1 PRECEDENCE NOTE đầu file
3 Generate seed manifest seed-manifest-g6-expected.json + 8 files seed-g6/seed-tac-*.sql
4 Update index/status index.md rev mới
5 Report một lần Opus message sau finalization

Patch dự kiến v0.3

  • Pending User authorize execution gate; sau execution có thể có v0.3 với learnings từ first run (đặc biệt seed hashes computed)

§16 Open Items — RESOLVED v0.2 + finalization

OI Item Resolution
OI-01 Generate seed-manifest-g6-expected.json ✅ KB path knowledge/dev/laws/dieu38-trien-khai/seed-manifest-g6-expected.json + 8 seed files trong seed-g6/. Hashes PENDING_COMPUTE first run.
OI-02 Verify VPS có jq, rclone, sha256sum, psql, curl, docker ✅ PF-Pre.1 explicit verify
OI-03 Confirm exact .env path ✅ §5 + PF-Pre.2 discover read-only
OI-04 Confirm Directus admin token retrieval ✅ PF-Pre.3 env → GSM fallback

Tất cả OI RESOLVED. Pre-execution gate chỉ chờ User authorize.

P9 G6 Migration Dry-Run Execution Prompt v0.2 OFFICIAL | S184 | 2026-04-27 | Opus 4.7 Bám chặt: P9-G6-dry-run-package.md v0.2 (GPT R2 PASS) + GPT R3 6 patches + GPT R4 finalization NOT TO RUN cho đến khi User explicit authorize execution gate