-- ============================================================================= -- File 06: Đ43 v1.2 rev 3 — Schema migrate v1.1 → v1.2 -- §5.4 context_pack_sections: section_name CHECK enum → section_code FK -- §5.2 context_pack_requests: +retry_count +next_retry_at +last_error -- Target DB: directus -- Run as: directus (owns tables) -- Idempotent: YES (DO block detect + conditional migrate) -- Safety: both tables row_count=0 confirmed Phase 1.6 precheck → DROP+RECREATE safe cho sections -- ============================================================================= -- DOT-MIGRATION-APPLY-OK -- Author: Claude CLI, S178 Fix 12 Phase 1.6 -- Date: 2026-04-17 -- =============================================================================

BEGIN;

---

-- §5.4 — context_pack_sections: migrate section_name → section_code FK -- Idempotent: DROP only if v1.1 schema (has section_name col, 0 rows)

---

DO $migrate_sections$ DECLARE v_has_section_name BOOLEAN; v_has_section_code BOOLEAN; v_row_count BIGINT; BEGIN SELECT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='context_pack_sections' AND column_name='section_name' AND table_schema='public') INTO v_has_section_name; SELECT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='context_pack_sections' AND column_name='section_code' AND table_schema='public') INTO v_has_section_code;

IF v_has_section_code AND NOT v_has_section_name THEN RAISE NOTICE 'context_pack_sections already v1.2 schema — skip'; RETURN; END IF;

SELECT COUNT(*) INTO v_row_count FROM context_pack_sections; IF v_row_count > 0 THEN RAISE EXCEPTION 'context_pack_sections has % rows — refuse DROP+RECREATE. Manual migrate needed.', v_row_count; END IF;

DROP TABLE context_pack_sections CASCADE;

CREATE TABLE context_pack_sections ( id BIGSERIAL PRIMARY KEY, manifest_id BIGINT NOT NULL REFERENCES context_pack_manifest(id) ON DELETE CASCADE, section_code TEXT NOT NULL REFERENCES context_pack_section_definitions(code), 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_code) );

COMMENT ON TABLE context_pack_sections IS 'Đ43 v1.2 §5.4 — FK section_code → context_pack_section_definitions. Migrate from v1.1 CHECK enum.'; END $migrate_sections$;

---

-- §5.2 — context_pack_requests: +retry_count +next_retry_at +last_error -- Idempotent: ADD COLUMN IF NOT EXISTS

---

ALTER TABLE context_pack_requests ADD COLUMN IF NOT EXISTS retry_count INT NOT NULL DEFAULT 0; ALTER TABLE context_pack_requests ADD COLUMN IF NOT EXISTS next_retry_at TIMESTAMPTZ; ALTER TABLE context_pack_requests ADD COLUMN IF NOT EXISTS last_error TEXT;

---

-- §5.3 — context_pack_manifest: section_count CHECK = 8 → > 0 (v1.2)

---

ALTER TABLE context_pack_manifest DROP CONSTRAINT IF EXISTS context_pack_manifest_section_count_check; ALTER TABLE context_pack_manifest ADD CONSTRAINT context_pack_manifest_section_count_check CHECK (section_count > 0);

COMMIT;

---

-- Recreate view v_context_pack_latest — CASCADE drop removed it with old sections

---

-- (v1.2 §5.5 + section_code per §5.4) CREATE OR REPLACE VIEW v_context_pack_latest AS SELECT m.*, s.section_code, 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 );

-- Post-check SELECT 'sections_has_section_code=' || CASE WHEN EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='context_pack_sections' AND column_name='section_code' AND table_schema='public') THEN 'YES' ELSE 'NO' END; SELECT 'sections_has_section_name_gone=' || CASE WHEN NOT EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='context_pack_sections' AND column_name='section_name' AND table_schema='public') THEN 'YES' ELSE 'NO' END; SELECT 'sections_fk_section_code=' || CASE WHEN EXISTS(SELECT 1 FROM pg_constraint con JOIN pg_class c ON c.oid = con.conrelid WHERE c.relname='context_pack_sections' AND con.contype='f' AND pg_get_constraintdef(con.oid) LIKE '%section_code%REFERENCES%context_pack_section_definitions%') THEN 'YES' ELSE 'NO' END; SELECT 'requests_retry_count=' || CASE WHEN EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='context_pack_requests' AND column_name='retry_count' AND table_schema='public') THEN 'YES' ELSE 'NO' END; SELECT 'requests_next_retry_at=' || CASE WHEN EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='context_pack_requests' AND column_name='next_retry_at' AND table_schema='public') THEN 'YES' ELSE 'NO' END; SELECT 'requests_last_error=' || CASE WHEN EXISTS(SELECT 1 FROM information_schema.columns WHERE table_name='context_pack_requests' AND column_name='last_error' AND table_schema='public') THEN 'YES' ELSE 'NO' END; SELECT 'view_latest_exists=' || CASE WHEN EXISTS(SELECT 1 FROM pg_views WHERE viewname='v_context_pack_latest') THEN 'YES' ELSE 'NO' END;