-- ============================================================================= -- File 03: Đ43 v1.2 extensions — section_definitions + health_checks + 7 dot_config keys -- Target DB: directus -- Run as: directus (owns DB) -- Idempotent: YES (IF NOT EXISTS + ON CONFLICT DO NOTHING) -- Spec: Đ43 v1.2 FINAL rev 2 §5.7 + §5.8 + §5.9 -- ============================================================================= -- DOT-MIGRATION-APPLY-OK -- Author: Claude CLI, S178 Fix 12 Phase 1.5 -- Date: 2026-04-17 -- Order: Run AFTER File 02 (requires dot_config table) -- =============================================================================

BEGIN;

---

-- §5.7 — context_pack_section_definitions + P8 path whitelist CHECK

---

CREATE TABLE IF NOT EXISTS context_pack_section_definitions ( code TEXT PRIMARY KEY, name TEXT NOT NULL, order_index INT NOT NULL UNIQUE, output_filename TEXT NOT NULL UNIQUE, format TEXT NOT NULL CHECK (format IN ('markdown','json','mermaid')), min_size_bytes INT NOT NULL CHECK (min_size_bytes >= 0), target_size_bytes INT NOT NULL CHECK (target_size_bytes >= min_size_bytes), max_size_bytes INT CHECK (max_size_bytes IS NULL OR max_size_bytes >= target_size_bytes), template_kb_path TEXT, query_kb_path TEXT, data_source TEXT NOT NULL CHECK (data_source IN ('pg_query','filesystem_scan','static','kb_query','custom')), render_config JSONB NOT NULL DEFAULT '{}'::jsonb, is_active BOOLEAN NOT NULL DEFAULT true, _dot_origin TEXT NOT NULL DEFAULT 'dieu43_v1_2_seed', CONSTRAINT chk_query_kb_path_whitelist CHECK ( query_kb_path IS NULL OR query_kb_path LIKE 'knowledge/current-state/queries/%' ), CONSTRAINT chk_template_kb_path_whitelist CHECK ( template_kb_path IS NULL OR template_kb_path LIKE 'knowledge/current-state/templates/%' ) );

COMMENT ON TABLE context_pack_section_definitions IS 'Đ43 v1.2 §5.7 — section definitions with path whitelist P8 security. Thêm section = INSERT row (NT2+NT4). render_config key whitelist §5.7 P9 enforced by verify script.';

INSERT INTO context_pack_section_definitions (code, name, order_index, output_filename, format, min_size_bytes, target_size_bytes, max_size_bytes, data_source, render_config) VALUES ('project_map', 'Project Map', 1, 'PROJECT_MAP.md', 'markdown', 3000, 15000, 20000, 'pg_query', '{"placeholder_style":"mustache"}'::jsonb), ('laws_index', 'Laws Index', 2, 'LAWS_INDEX.md', 'markdown', 1000, 8000, 15000, 'kb_query', '{"source_patterns_key":"context_pack_watched_key_patterns"}'::jsonb), ('dot_registry', 'DOT Registry', 3, 'DOT_REGISTRY.md', 'markdown', 2000, 20000, 30000, 'pg_query', '{}'::jsonb), ('entities_overview', 'Entities Overview', 4, 'ENTITIES_OVERVIEW.md', 'markdown', 500, 5000, 10000, 'pg_query', '{"group_by":"species"}'::jsonb), ('db_map', 'Database Map', 5, 'DB_MAP.md', 'markdown', 1000, 10000, 15000, 'pg_query', '{"whitelist_key":"context_pack_scan_db_whitelist"}'::jsonb), ('red_zones', 'Red Zones', 6, 'RED_ZONES.md', 'markdown', 500, 3000, 8000, 'static', '{}'::jsonb), ('architecture_mmd', 'Architecture Diagram', 7, 'ARCHITECTURE.mmd', 'mermaid', 500, 8000, 15000, 'pg_query', '{"diagram_type":"flowchart"}'::jsonb), ('project_map_json', 'Project Map JSON', 8, 'project-map.json', 'json', 200, 2000, 5000, 'pg_query', '{}'::jsonb) ON CONFLICT (code) DO NOTHING;

---

-- §5.8 — context_pack_health_checks + P8 SQL path whitelist CHECK

---

CREATE TABLE IF NOT EXISTS context_pack_health_checks ( code TEXT PRIMARY KEY, name TEXT NOT NULL, executor_type TEXT NOT NULL CHECK (executor_type IN ('builtin','sql','function')), executor_ref TEXT NOT NULL, threshold_config JSONB NOT NULL DEFAULT '{}'::jsonb, severity_on_fail TEXT NOT NULL CHECK (severity_on_fail IN ('warn','critical')), is_active BOOLEAN NOT NULL DEFAULT true, order_index INT NOT NULL UNIQUE, _dot_origin TEXT NOT NULL DEFAULT 'dieu43_v1_2_seed', CONSTRAINT chk_sql_executor_path CHECK ( executor_type != 'sql' OR executor_ref LIKE 'knowledge/current-state/queries/%' ) );

COMMENT ON TABLE context_pack_health_checks IS 'Đ43 v1.2 §5.8 — generic health check executor (builtin/sql/function). 5 guards enforced by verify script when executor_type=sql.';

INSERT INTO context_pack_health_checks (code, name, executor_type, executor_ref, threshold_config, severity_on_fail, order_index) VALUES ('H1','Manifest LIVE age', 'builtin','check_manifest_age', '{"critical_hours":6,"warn_hours":3}'::jsonb, 'critical', 1), ('H2','Section file exists', 'builtin','check_section_exists', '{}'::jsonb, 'critical', 2), ('H3','Section file_checksum match', 'builtin','check_checksum_match', '{}'::jsonb, 'critical', 3), ('H4','KB mirror diff', 'builtin','check_kb_mirror_diff', '{"warn_pct":1,"critical_pct":5}'::jsonb, 'warn', 4), ('H5','PROJECT_MAP size', 'builtin','check_section_size', '{"section":"project_map","warn_kb":15,"critical_kb":20}'::jsonb, 'warn', 5), ('H6','Required headers', 'builtin','check_section_headers', '{}'::jsonb, 'critical', 6), ('H7','Mermaid parse OK', 'builtin','check_mermaid_parse', '{"section":"architecture_mmd"}'::jsonb, 'critical', 7), ('H8','JSON schema valid', 'builtin','check_json_valid', '{"section":"project_map_json"}'::jsonb, 'critical', 8), ('H9','Publish state consistency', 'builtin','check_publish_state', '{"staging_timeout_min":15}'::jsonb, 'critical', 9) ON CONFLICT (code) DO NOTHING;

---

-- §5.9 — 7 dot_config keys Đ43

---

-- Q6 CLI verify: kb_documents.key PG storage dùng '' separator (0 matches '/' vs 59 matches ''). -- Seed pattern dùng '' để LIKE match reality, không phải '/' wording của §5.9. -- Flag TD-S178-18: amend §5.9 wording hoặc fix sync pipeline (cả 2 hợp lệ — Desktop decide). INSERT INTO dot_config(key, value, description) VALUES ('context_pack_scan_paths', '["/opt/incomex/dot/bin","/opt/incomex/web/app","/opt/incomex/deploy","/opt/incomex/scripts"]', 'JSON array: folders scan Đ43 §6 Bước 4. 2/4 folder có thể missing trên VPS — script WARN+skip.'), ('context_pack_scan_db_whitelist', '[]', 'JSON array DB names. Rỗng → pg_database catalog (NT11). Đ43 §6 Bước 3.'), ('context_pack_watched_key_patterns', '["knowledge__dev__laws%","knowledge__dev__ssot__%","knowledge__dev__architecture__%"]', 'JSON array LIKE patterns kb_documents.key. "__" separator do Agent Data sync normalize. Q6 tested 59 matches underscore vs 0 matches slash.'), ('context_pack_retry_policy', '{"max_retries":3,"backoff_seconds":[60,300,1800]}', 'JSON retry cho build failed §5.2 + §6 Bước 8.'), ('context_pack_mode', 'warn', 'warn|block — grace enforcement §10.'), ('context_pack_output_root', '/opt/incomex/context-pack', 'Thư mục gốc output §5.9 + §6 Bước 5. UPDATE để chuyển vị trí.'), ('context_pack_grace_period_days', '7', 'Grace period Đ43 §10.') ON CONFLICT (key) DO NOTHING;

COMMIT;

-- Post-check SELECT 'section_definitions_count=' || COUNT() FROM context_pack_section_definitions; SELECT 'health_checks_count=' || COUNT() FROM context_pack_health_checks; SELECT 'dot_config_dieu43_keys=' || COUNT(*) FROM dot_config WHERE key LIKE 'context_pack_%'; SELECT 'path_whitelist_enforced=' || CASE WHEN EXISTS(SELECT 1 FROM pg_constraint WHERE conname='chk_query_kb_path_whitelist') AND EXISTS(SELECT 1 FROM pg_constraint WHERE conname='chk_template_kb_path_whitelist') AND EXISTS(SELECT 1 FROM pg_constraint WHERE conname='chk_sql_executor_path') THEN 'YES' ELSE 'NO' END;