Đ43 Block 1 Phase 1 — Schema Migration SQL

KB-6D2C

Đ43 Block 1 Phase 1 — Schema Migration SQL

10 min read Revision 1

dieu43migrationphase1schemacontext-packsql

-- ============================================================================= -- Đ43 BLOCK 1 BOOTSTRAP — PHASE 1: SCHEMA MIGRATION -- Target DB: directus -- Idempotent: YES (IF NOT EXISTS + ON CONFLICT DO NOTHING) -- Spec: Đ43 v1.1 FINAL §5.1-5.6 (BAN HÀNH 2026-04-16) -- Principles: HP v4.6.1 NT1, NT4, NT10, NT13 PG Native -- ============================================================================= -- DOT-MIGRATION-APPLY-OK -- Author: Claude Desktop (Opus 4.7), S178 Fix 9 -- Date: 2026-04-17 -- ============================================================================= -- DEPENDENCY: Bảng dot_operations phải tồn tại trước khi chạy file này. -- Nếu chưa có → chạy 00-prereq-dot-operations.sql trước (sẽ soạn nếu PRECHECK -- xác nhận thiếu). -- =============================================================================

BEGIN;

-- §5.1 — Lookup: context_trigger_sources (6 rows enum values)

CREATE TABLE IF NOT EXISTS context_trigger_sources ( code TEXT PRIMARY KEY, name TEXT NOT NULL, description TEXT );

INSERT INTO context_trigger_sources(code, name, description) VALUES ('cron', 'Lịch định kỳ', '3 giờ 1 lần'), ('on_demand', 'Theo yêu cầu', 'Bash CLI / API'), ('on_deploy', 'Sau deploy', 'CHỈ sau Đ41 Bước 6.5 is_known_good=true'), ('on_law_enact', 'Luật thay đổi', 'PG NOTIFY khi kb_documents key LIKE knowledge/dev/laws/%'), ('on_dot_register', 'DOT registry thay đổi', 'PG NOTIFY khi INSERT dot_tools'), ('system_init', 'Bootstrap lần đầu', 'Khi enact Đ43') ON CONFLICT (code) DO NOTHING;

-- §5.2 — Queue: context_pack_requests (chống event storm qua dedupe_bucket) -- ★ R2 Patch A: status enum cứng 5 values. Lý do skip vào detail.reason JSONB.

CREATE TABLE IF NOT EXISTS context_pack_requests ( id BIGSERIAL PRIMARY KEY, requested_at TIMESTAMPTZ NOT NULL DEFAULT now(), trigger_source TEXT NOT NULL REFERENCES context_trigger_sources(code), dedupe_bucket TIMESTAMPTZ NOT NULL, status TEXT NOT NULL CHECK (status IN ('pending','running','done','skipped','failed')) DEFAULT 'pending', detail JSONB NOT NULL DEFAULT '{}'::jsonb, coalesced_events_count INT DEFAULT 0, started_at TIMESTAMPTZ, finished_at TIMESTAMPTZ, manifest_id BIGINT );

-- Chỉ 1 request OPEN (pending/running) cho cùng trigger_source + bucket. -- Event trùng rơi vào cùng bucket → INSERT fail → coalesce qua UPDATE count. CREATE UNIQUE INDEX IF NOT EXISTS uq_context_pack_requests_open ON context_pack_requests(trigger_source, dedupe_bucket) WHERE status IN ('pending','running');

-- §5.3 — Manifest: context_pack_manifest (1 row = 1 build) -- ★ R2 Patch B: 2 checksum (logical strip volatile header + file full) -- ★ R2 Patch C: publish_step cho repair state FS-live/DB-staging

CREATE TABLE IF NOT EXISTS context_pack_manifest ( id BIGSERIAL PRIMARY KEY, generated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), published_at TIMESTAMPTZ, git_commit TEXT NOT NULL, trigger_source TEXT NOT NULL REFERENCES context_trigger_sources(code), law_count INT NOT NULL, dot_count INT NOT NULL, entity_count BIGINT NOT NULL, species_count INT NOT NULL, db_count INT NOT NULL, total_size_bytes BIGINT NOT NULL, section_count INT NOT NULL CHECK (section_count = 8), logical_checksum_sha256 TEXT NOT NULL, file_checksum_sha256 TEXT NOT NULL, publish_status TEXT NOT NULL CHECK (publish_status IN ('staging','live','failed')) DEFAULT 'staging', kb_mirror_status TEXT NOT NULL CHECK (kb_mirror_status IN ('pending','live','failed')) DEFAULT 'pending', publish_step TEXT, health_status TEXT NOT NULL CHECK (health_status IN ('healthy','warn','stale','fail')), generation_duration_ms INT, _dot_origin TEXT NOT NULL DEFAULT 'unknown' );

CREATE INDEX IF NOT EXISTS idx_cpm_generated_at ON context_pack_manifest(generated_at DESC);

CREATE INDEX IF NOT EXISTS idx_cpm_logical_checksum ON context_pack_manifest(logical_checksum_sha256);

CREATE INDEX IF NOT EXISTS idx_cpm_publish_status ON context_pack_manifest(publish_status);

-- §5.4 — Sections: context_pack_sections (8 rows per manifest)

CREATE TABLE IF NOT EXISTS context_pack_sections ( id BIGSERIAL PRIMARY KEY, manifest_id BIGINT NOT NULL REFERENCES context_pack_manifest(id) ON DELETE CASCADE, section_name TEXT NOT NULL CHECK (section_name IN ( 'project_map', 'laws_index', 'dot_registry', 'entities_overview', 'db_map', 'red_zones', 'architecture_mmd', 'project_map_json' )), file_path TEXT NOT NULL, kb_document_path TEXT, size_bytes INT NOT NULL CHECK (size_bytes >= 0), line_count INT NOT NULL CHECK (line_count >= 0), logical_checksum_sha256 TEXT NOT NULL, file_checksum_sha256 TEXT NOT NULL,

UNIQUE(manifest_id, section_name) );

-- §5.5 — View: v_context_pack_latest (JOIN manifest + sections, filter live)

CREATE OR REPLACE VIEW v_context_pack_latest AS SELECT m.*, s.section_name, s.file_path, s.kb_document_path, s.size_bytes, s.logical_checksum_sha256 AS section_logical_checksum, s.file_checksum_sha256 AS section_file_checksum FROM context_pack_manifest m JOIN context_pack_sections s ON s.manifest_id = m.id WHERE m.id = ( SELECT id FROM context_pack_manifest WHERE publish_status = 'live' ORDER BY published_at DESC NULLS LAST, generated_at DESC LIMIT 1 );

-- §5.6 — Seed: 2 new operations vào dot_operations (Đ43 specific) -- DEPENDENCY: table dot_operations phải tồn tại (tạo từ Đ35 v5.1 bootstrap)

INSERT INTO dot_operations (code, name, description) VALUES ('CONTEXT_PACK_BUILD', 'Build context pack', 'Sinh toàn bộ context pack files (8 sections)'), ('CONTEXT_PACK_VERIFY', 'Verify context pack', 'Kiểm tra stale / drift / checksum mismatch / repair state') ON CONFLICT (code) DO NOTHING;

COMMIT;

-- ============================================================================= -- POST-MIGRATION VERIFY BLOCK (chạy tách sau COMMIT) -- Không nằm trong TX chính để nếu verify fail vẫn giữ được migration đã apply. -- =============================================================================

SELECT 'trigger_sources_count' AS check_name, COUNT()::text AS result FROM context_trigger_sources UNION ALL SELECT 'requests_table_exists', CASE WHEN EXISTS(SELECT 1 FROM pg_tables WHERE tablename='context_pack_requests') THEN 'YES' ELSE 'NO' END UNION ALL SELECT 'manifest_table_exists', CASE WHEN EXISTS(SELECT 1 FROM pg_tables WHERE tablename='context_pack_manifest') THEN 'YES' ELSE 'NO' END UNION ALL SELECT 'sections_table_exists', CASE WHEN EXISTS(SELECT 1 FROM pg_tables WHERE tablename='context_pack_sections') THEN 'YES' ELSE 'NO' END UNION ALL SELECT 'view_latest_exists', CASE WHEN EXISTS(SELECT 1 FROM pg_views WHERE viewname ='v_context_pack_latest') THEN 'YES' ELSE 'NO' END UNION ALL SELECT 'context_pack_operations', COUNT()::text FROM dot_operations WHERE code LIKE 'CONTEXT_PACK_%' UNION ALL SELECT 'uq_requests_open_index', CASE WHEN EXISTS(SELECT 1 FROM pg_indexes WHERE indexname='uq_context_pack_requests_open') THEN 'YES' ELSE 'NO' END UNION ALL SELECT 'idx_manifest_generated', CASE WHEN EXISTS(SELECT 1 FROM pg_indexes WHERE indexname='idx_cpm_generated_at') THEN 'YES' ELSE 'NO' END;

-- ============================================================================= -- END — idempotent, chạy lại không lỗi -- =============================================================================