-- ============================================================================= -- File 08: Đ43 v1.2 rev 6 — Multi-DB dispatch column target_db -- Scope: directus DB — ALTER context_pack_section_definitions -- Step 1: ADD COLUMN target_db TEXT (nullable, no CHECK yet) -- Step 2: UPDATE 8 seed rows with correct target_db values (§5.7 rev 6 seed) -- Step 3: ADD CONSTRAINT chk_target_db_values — enum whitelist -- Step 4: ADD CONSTRAINT chk_target_db_consistency — NULL rule vs data_source -- Spec: Đ43 v1.2 rev 6 §5.7 (KB rev 35) — NT8 Assembly First multi-DB dispatch -- Run as: workflow_admin (owner of table / has ALTER privilege) -- Idempotent: YES (IF NOT EXISTS + DO blocks + conditional UPDATE) -- ============================================================================= -- DOT-MIGRATION-APPLY-OK -- Author: Claude Code CLI, S178 Fix 12 post-Pre-D' Task 2 -- Date: 2026-04-17 -- =============================================================================

\c directus

---

-- Step 1 — ADD COLUMN target_db (nullable, no CHECK yet so UPDATE can populate)

---

ALTER TABLE context_pack_section_definitions ADD COLUMN IF NOT EXISTS target_db TEXT;

COMMENT ON COLUMN context_pack_section_definitions.target_db IS 'Đ43 v1.2 rev 6 §5.7 — Target PG DB cho executor dispatch. NOT NULL khi data_source IN (pg_query, kb_query); NULL khi static/filesystem_scan/custom. Enum: directus, incomex_metadata, workflow.';

---

-- Step 2 — UPDATE 8 seed rows (idempotent: set only nếu khác / NULL)

---

UPDATE context_pack_section_definitions SET target_db = 'directus' WHERE code = 'project_map' AND (target_db IS DISTINCT FROM 'directus'); UPDATE context_pack_section_definitions SET target_db = 'incomex_metadata' WHERE code = 'laws_index' AND (target_db IS DISTINCT FROM 'incomex_metadata'); UPDATE context_pack_section_definitions SET target_db = 'directus' WHERE code = 'dot_registry' AND (target_db IS DISTINCT FROM 'directus'); UPDATE context_pack_section_definitions SET target_db = 'directus' WHERE code = 'entities_overview' AND (target_db IS DISTINCT FROM 'directus'); UPDATE context_pack_section_definitions SET target_db = 'directus' WHERE code = 'db_map' AND (target_db IS DISTINCT FROM 'directus'); UPDATE context_pack_section_definitions SET target_db = NULL WHERE code = 'red_zones' AND target_db IS NOT NULL; UPDATE context_pack_section_definitions SET target_db = 'directus' WHERE code = 'architecture_mmd' AND (target_db IS DISTINCT FROM 'directus'); UPDATE context_pack_section_definitions SET target_db = 'directus' WHERE code = 'project_map_json' AND (target_db IS DISTINCT FROM 'directus');

---

-- Step 3 — ADD CONSTRAINT chk_target_db_values (enum whitelist)

---

DO $cons_values$ BEGIN IF NOT EXISTS ( SELECT 1 FROM pg_constraint WHERE conname = 'chk_target_db_values' AND conrelid = 'public.context_pack_section_definitions'::regclass ) THEN ALTER TABLE context_pack_section_definitions ADD CONSTRAINT chk_target_db_values CHECK ( target_db IS NULL OR target_db IN ('directus','incomex_metadata','workflow') ); END IF; END $cons_values$;

---

-- Step 4 — ADD CONSTRAINT chk_target_db_consistency (§5.7 rev 6 rule)

---

DO $cons_consistency$ BEGIN IF NOT EXISTS ( SELECT 1 FROM pg_constraint WHERE conname = 'chk_target_db_consistency' AND conrelid = 'public.context_pack_section_definitions'::regclass ) THEN ALTER TABLE context_pack_section_definitions ADD CONSTRAINT chk_target_db_consistency CHECK ( (data_source IN ('pg_query','kb_query') AND target_db IS NOT NULL) OR (data_source IN ('static','filesystem_scan','custom') AND target_db IS NULL) ); END IF; END $cons_consistency$;

---

-- Post-check — must show 8 rows with expected target_db mapping

---

SELECT 'target_db_column_exists=' || CASE WHEN EXISTS ( SELECT 1 FROM information_schema.columns WHERE table_schema='public' AND table_name='context_pack_section_definitions' AND column_name='target_db' ) THEN 'YES' ELSE 'NO' END;

SELECT 'chk_target_db_values_exists=' || CASE WHEN EXISTS ( SELECT 1 FROM pg_constraint WHERE conname='chk_target_db_values' AND conrelid='public.context_pack_section_definitions'::regclass ) THEN 'YES' ELSE 'NO' END;

SELECT 'chk_target_db_consistency_exists=' || CASE WHEN EXISTS ( SELECT 1 FROM pg_constraint WHERE conname='chk_target_db_consistency' AND conrelid='public.context_pack_section_definitions'::regclass ) THEN 'YES' ELSE 'NO' END;

SELECT code, data_source, target_db FROM context_pack_section_definitions ORDER BY order_index;