KB-2117

P9 Production DDL/Collection Gate Design v0.2 — GPT PASS Final

10 min read Revision 1

p9production-ddlgate-designdirectus-collectionsseeds185gpt-passv0.2

P9 Production DDL / Collection Gate Design v0.2

Status: DRAFT v0.2 — patched per probe results + GPT review (PASS final) Phiên: S185 | Ngày: 2026-04-28 P9 Gate: 10/12 PASS | Gates này: A (DDL) + B (Collections) + C (Seed) Revision: v0.1 (S185) → v0.2 (probe-corrected, GPT PASS) Chờ: Per-gate execution prompts → GPT review → User approve → Agent execute

⚠️ DOC-ONLY — KHÔNG AUTHORIZE MUTATION

Không production DDL. Không Directus collection creation. Không seed INSERT. Không role/policy/permission. Không token. Không G8B/G11/P9.

1. Evidence Chain

Evidence	Status	Path
G6 Run #4 PASS	✅	`reports/p9-g6-execution-log-run4-2026-04-28.md`
G6 DDL bundle SHA verified on VPS	✅	`/tmp/g6_run4.../ddl/` — EXISTS + SHA MATCH
G8A v0.3 PASS	✅	`P9-G8A-directus-roles-readiness-design.md` rev 4
Probe report 6/6 checks	✅	Artifact S185
Seed manifest (8 files, SHA-256)	✅	`seed-manifest-g6-expected.json` rev 2

2. Reusable Artifacts from G6

2.1 DDL (14 tables + indexes)

Metric	Value
Tables	14 (4 core + 2 member + 7 vocab + 1 config)
FK constraints	17
UNIQUE constraints	5
CHECK constraints	115
Indexes	41
VPS source	`/tmp/g6_run4_20260428_041121/ddl/g6_execution_bundle.sql`
SHA-256	`c23987f29911e92d43d9ab6cb5e4fe77d64547785d98588859dfcc6f02997cad`
Size	23,528 bytes / 566 lines

⚠️ /tmp là ephemeral. Trước execution Gate A phải persist/canonicalize DDL candidate ra KB hoặc persistent VPS path. Không dùng /tmp làm source of truth dài hạn.

⚠️ Local Mac copy (SHA bea9... / 450 lines) KHÔNG phải bản gốc. Chỉ dùng VPS bundle đã verify SHA.

2.2 Functions (6) — EXACT names from G6 V1-07

#	Function	Type
1	`fn_tac_birth_gate_lu`	SECURITY DEFINER, search_path locked
2	`fn_tac_birth_gate_uv`	SECURITY DEFINER, search_path locked
3	`fn_tac_enacted_immut`	SECURITY DEFINER, search_path locked
4	`fn_tac_pm_consistency`	SECURITY DEFINER, search_path locked
5	`fn_tac_pm_enacted_lock`	SECURITY DEFINER, search_path locked
6	`fn_tac_uv_compute_derived`	SECURITY DEFINER, search_path locked

2.3 Triggers (6)

Bound 1:1 to functions above on respective tables. Exact names from G6 V1-08.

2.4 Seed (61 rows, 8 files)

File	SHA-256 (dry-run)
seed-tac-birth-gate-config.sql	`1f707c4d...`
seed-tac-cs-lifecycle.sql	`f3860099...`
seed-tac-lu-lifecycle.sql	`f7115871...`
seed-tac-pub-lifecycle.sql	`1c928c99...`
seed-tac-publication-type.sql	`4b9f2793...`
seed-tac-review-state.sql	`94129669...`
seed-tac-section-type.sql	`d5a3d167...`
seed-tac-uv-lifecycle.sql	`4f7b9682...`
Total rows	61

⚠️ Seed files hiện dùng p9_g6_dryrun. schema prefix. Production seed phải retarget → public. + recompute SHA. Dry-run manifest và production manifest phải tách riêng.

3. Production Adaptations

3.1 Schema retarget: `p9_g6_dryrun` → `public`

Item	G6 dry-run	Production
Schema	`p9_g6_dryrun`	`public`
`SET search_path`	`p9_g6_dryrun, pg_catalog`	`public, pg_catalog`
Function search_path	`p9_g6_dryrun, pg_catalog`	`public, pg_catalog`
Rollback	`DROP SCHEMA CASCADE`	Table-level DROP IF EXISTS, reverse FK order (P8 §8.3)

3.2 Scaffolding to REMOVE

Scaffold	Reason
`CREATE SCHEMA p9_g6_dryrun`	Production uses `public`
Probe wrappers (v0.3–v0.6)	Dry-run operational only
V3 adaptations (V3-02 retarget, V3-07 address)	Re-verify on production
Errata §2.2 (4 situations)	Dry-run specific

3.3 Safety mechanisms to KEEP

Mechanism	Reason
SECURITY DEFINER on 6 functions	Privilege boundary (Đ33)
`search_path = public, pg_catalog` locked	Prevent search_path injection
Trigger guard schema-qualify	S184 lesson
Backup PF-07 v0.5	S184 lesson — silent fail protection

3.4 Canonical DDL Persist Step (NEW)

Before Gate A execution:

SSH VPS → copy /tmp/g6_run4.../ddl/g6_execution_bundle.sql to persistent path (e.g. /opt/incomex/data/tac/)
Verify SHA-256 matches c23987f2...
Retarget: p9_g6_dryrun → public (sed or manual)
Compute new SHA-256 for production DDL candidate
Upload production DDL candidate to KB
Create production manifest with new SHA

4. Directus Collection Registration Design

4.1 Auto-introspection (Directus docs confirmed)

Sau Gate A (CREATE TABLE), Directus tự phát hiện 14 tac_* tables → API access hoạt động ngay. Nhưng Data Studio (UI) cần POST /collections với metadata.

4.2 Registration method

POST /collections với meta only (KHÔNG include schema):

{
  "collection": "tac_logical_unit",
  "meta": {
    "icon": "article",
    "group": "tac",
    "hidden": false,
    "note": "TAC core: logical unit"
  }
}

4.3 Gate B live pre-check (GPT requirement)

Trước mutation, Gate B phải xác nhận trên hệ thống thật:

Directus 11.5.1 auto-introspect có thấy tac_* tables sau Gate A
POST /collections metadata-only payload đúng format
Không include schema key

Docs chỉ là supporting evidence, live pre-check là source of truth.

4.4 Registration order

Vocab/config tables (8) — no FK dependencies
Core tables (4) — FK to vocab
Member tables (2) — FK to core

5. Execution Gates — TÁCH RIÊNG

Gate A — Production DDL Only

Pre-checks:

#	Check	Expected
P1	No tac_* tables in public	0
P2	fn_tac_* in public = whitelist only	1 (`fn_tac_log_checker_issue` — E-R3)
P3	Backup recent + valid (PF-07 v0.5)	PASS
P4	Directus healthy	OK
P5	Production DDL candidate persisted + SHA verified	✅

Post-checks:

#	Check	Expected
Q1	tac_* tables in public	14
Q2	FK constraints on tac_*	17
Q3	fn_tac_* in public	7 total: 1 existing whitelist (`fn_tac_log_checker_issue`) + 6 new from §2.2
Q4	trg_tac_* triggers	6
Q5	Indexes on tac_*	41
Q6	Directus still healthy	OK

Gate B — Directus Collection Registration Only

Pre-checks:

#	Check	Expected
R1	Gate A post-checks ALL PASS	✅
R2	No tac_* Directus collections yet	0 (or auto-introspected but no meta)
R3	Live verify payload shape read-only from Directus schema/API metadata and current collection metadata	Do not POST in pre-check

Post-checks:

#	Check	Expected
S1	14 tac_* Directus collections registered with meta	14
S2	Each maps to correct PG table	Schema validation

Gate B execution strategy:

Register first low-risk collection metadata entry (e.g. 1 vocab table)
Verify it (API + Data Studio visibility)
If PASS → continue remaining 13
If FAIL → stop, rollback/delete only that metadata entry if created

Gate C — Seed Only

Pre-checks:

#	Check	Expected
T1	All 14 tac_* tables empty	count = 0 each
T2	Production seed files SHA match production manifest	✅

Post-checks:

#	Check	Expected
U1	61 rows across 8 vocab/config tables	Per manifest
U2	Content hash matches	SHA comparison

6. Rollback / Compensation

Gate	Rollback	Risk
A (DDL)	`DROP TABLE IF EXISTS tac_* CASCADE` reverse FK order + `DROP FUNCTION IF EXISTS fn_tac_*` (keep whitelist!)	Low — no data yet
B (Collections)	`DELETE /collections/tac_*` × 14	Low — metadata only
C (Seed)	`TRUNCATE tac_*_vocab, tac_birth_gate_config CASCADE`	Low — seed only
Full	A+B+C reverse	Clean

Gate A rollback: KHÔNG DROP fn_tac_log_checker_issue — đây là whitelist E-R3, không thuộc Gate A.

7. Dependency Chain (complete)

G6 PASS ✅ + Probe PASS ✅
  │
  ▼ Persist/canonicalize DDL + retarget seed
  │
  ▼
[GATE A] Production DDL (14 tables + 6 fn + 6 trg → public)
  │
  ▼
[GATE B] Directus collection registration (meta-only × 14)
  │
  ▼
[GATE C] Seed (61 rows from production manifest)
  │
  ▼
[GATE D] DOT-TAC-ROLE-ENSURE D11 update
  │
  ▼
[GATE E] G8B: Role + Policy + Access + Permissions + Token
  │
  ▼
[GATE F] V3–V4 re-verify on production
  │
  ▼
[GATE G] Cron + Full verify
  │
  ▼
[GATE H] G11: User final approval

8. Open Decisions (ghi nhận, không block v0.2)

#	Decision	Status
1	Collection `meta.hidden` cho vocab tables	PENDING
2	`tac-admin` `app_access` (G8A §3.2)	PENDING
3	V3–V4 re-verify: adapt from G6 or fresh	PENDING
4	Collection `meta.group` naming	PENDING

9. Execution Model

Aspect	Design
Executor	Agent (Claude Code) via SSH `contabo`
Effort	Gate A: medium-high, Gate B: low-medium, Gate C: low
DB	`docker exec postgres psql -U directus -d directus`
Directus API	Bearer admin token (runtime-only, masked)
Authorize	Per gate: Opus soạn prompt → GPT review → User GO

P9 Production DDL/Collection Gate Design v0.2 | S185 | 2026-04-28 | Doc-only DRAFT Patched: function names, pre-check whitelist, canonical DDL persist, seed retarget, gate split, collection live pre-check, Gate B sequential execution, Q3 breakdown Chờ GPT review → per-gate execution prompts

Document này không authorize bất kỳ mutation nào. Mỗi Gate cần prompt riêng + GPT review + User GO.

P9 Production DDL / Collection Gate Design v0.2

⚠️ DOC-ONLY — KHÔNG AUTHORIZE MUTATION

1. Evidence Chain

2. Reusable Artifacts from G6

2.1 DDL (14 tables + indexes)

2.2 Functions (6) — EXACT names from G6 V1-07

2.3 Triggers (6)

2.4 Seed (61 rows, 8 files)

3. Production Adaptations

3.1 Schema retarget: p9_g6_dryrun → public

3.2 Scaffolding to REMOVE

3.3 Safety mechanisms to KEEP

3.4 Canonical DDL Persist Step (NEW)

4. Directus Collection Registration Design

4.1 Auto-introspection (Directus docs confirmed)

4.2 Registration method

4.3 Gate B live pre-check (GPT requirement)

4.4 Registration order

5. Execution Gates — TÁCH RIÊNG

Gate A — Production DDL Only

Gate B — Directus Collection Registration Only

Gate C — Seed Only

6. Rollback / Compensation

7. Dependency Chain (complete)

8. Open Decisions (ghi nhận, không block v0.2)

9. Execution Model

3.1 Schema retarget: `p9_g6_dryrun` → `public`