Đ43 Phase 1.6 APPLY — Report (v1.2 rev 3 — hoàn thiện hạ tầng)

Date: 2026-04-17 UTC+7 Phiên: S178 Fix 12 Phase 1.6 Agent: Claude CLI qua SSH contabo Spec: Đ43 v1.2 FINAL rev 3 (KB rev 20) — amend /→__ separator

CHECKPOINT

Đã đọc: Đ43 v1.2 rev 3 §5.2+§5.3+§5.4+§5.7+§5.8+§5.9+§11 Bước 12 P11; HP v4.6.2 NT2/NT4/NT11; Đ33 §13; memory #29 rà NT 3 tầng.

1. BASELINE TRƯỚC

3 CHECK constraint dùng / separator (Phase 1.5 legacy)
context_pack_sections v1.1 schema (section_name TEXT + CHECK enum 8 values; row_count=0)
context_pack_requests thiếu 3 retry cols (row_count=0)
context_pack_manifest CHECK section_count = 8 (v1.1 legacy)
FDW overhead per kb_documents INSERT: 4.9 ms/insert (vs 0.14 ms without trigger — 35x slower)

2. 3 FILE MIGRATION APPLIED

File	DB	User	Scope
`05-dieu43-v1-2-rev3-alter-check.sql`	directus	directus	ALTER 3 CHECK `/` → `__` (§5.7 + §5.8)
`06-dieu43-v1-2-schema-migrate.sql`	directus	directus	Migrate sections (FK section_code §5.4) + requests (3 retry cols §5.2) + manifest (CHECK section_count > 0 §5.3) + recreate view (CASCADE drop)
`07-dieu43-v1-2-fdw-optimize.sql`	directus + incomex_metadata	workflow_admin	FDW optimization (local cache table + bidirectional FDW sync trigger)

SHA256:

05: cbc9fa2da5465da91ff3043f8de05f04e42e8ae3fd18608d49a65b7d18ea5f18
06 (patched +manifest): re-synced after adding ALTER TABLE context_pack_manifest for section_count CHECK
07: 47512c803b8e5447a757e977dced36adb68bdab77412b48ac870673ece32398d

3. 6 TIÊU CHÍ — EVIDENCE

T1 — 3 CHECK constraint PG có pattern `__`

chk_query_kb_path_whitelist    | CHECK (... LIKE 'knowledge__current-state__queries__%'::text)
chk_template_kb_path_whitelist | CHECK (... LIKE 'knowledge__current-state__templates__%'::text)
chk_sql_executor_path          | CHECK (... LIKE 'knowledge__current-state__queries__%'::text)

Negative test: INSERT với / path bị REJECT → ERROR: new row violates check constraint "chk_query_kb_path_whitelist". PASS

T2 — `context_pack_sections` FK `section_code` hoạt động

-- Valid INSERT section_code='project_map'
INSERT 0 1 (section_code='project_map' returned)

-- Invalid INSERT section_code='not_a_valid'
ERROR: insert or update on table "context_pack_sections" violates foreign key constraint "context_pack_sections_section_code_fkey"
DETAIL: Key (section_code)=(not_a_valid) is not present in table "context_pack_section_definitions".

PASS

T3 — `context_pack_requests` 3 retry cols default

INSERT ... RETURNING id, retry_count, next_retry_at, last_error
→ id=3 | retry_count=0 | next_retry_at=NULL | last_error=NULL

PASS

T4 — FDW lookup: UPDATE dot_config → trigger fire immediate (không DROP/CREATE)

STEP 1 cache: ["knowledge__dev__laws__%", "knowledge__dev__ssot__%", "knowledge__dev__architecture__%"]
STEP 2 UPDATE dot_config add "_p16_probe_%"
STEP 3 cache auto-synced via trg_sync_cp_patterns (same TX):
        ["knowledge__dev__laws__%", "knowledge__dev__ssot__%", "knowledge__dev__architecture__%", "_p16_probe_%"]
        updated_at=2026-04-17 09:04:11.769043
STEP 4 INSERT kb_doc key='_p16_probe_t4_immediate'
        → Asynchronous notification "context_pack_event" payload 
          {"kind":"law_enact","key":"_p16_probe_t4_immediate","pattern":"_p16_probe_%"}

Cache updated IN SAME TRANSACTION với dot_config UPDATE (zero latency propagation). fn_context_pack_on_law_enact đọc LOCAL _cp_patterns_cache, không FDW. PASS

T5 — Benchmark 100 INSERT (giảm ≥ 50% latency)

Scenario	Total ms / 100	ms/insert	Speedup vs baseline
Baseline (FDW trigger, v1.5)	491 ms	4.9 ms	1.0x (reference)
Without trigger disabled	13 ms	0.14 ms	35x (floor)
Optimized (local cache)	20 ms	0.20 ms	★ 24x

Trigger overhead: 4.78 ms → 0.06 ms per INSERT (98.7% giảm). Vượt xa target ≥ 50%. PASS

T6 — Idempotent (rerun 3 files)

Rerun 05, 06, 07: all exit 0, post-checks green.
State unchanged:
  def_count=8
  sections_fk=1 (FK context_pack_sections_section_code_fkey intact)
  cache_rows=1 (single-row CHECK enforce)
  sync_trigger=1 (single trigger instance)

PASS

4. V3 PHƯƠNG ÁN CLI CHỌN — LÝ DO

Kiến trúc: Local cache table + bidirectional FDW sync trigger

Hot path (kb_documents INSERT — chạy 1000+ lần/giờ):

Trigger trg_context_pack_law_enact → function fn_context_pack_on_law_enact
SELECT patterns FROM _cp_patterns_cache WHERE id=1 (LOCAL table, ~0.06ms)
KHÔNG còn FDW call trong hot path

Cold path (dot_config UPDATE — rare):

Trigger trg_sync_cp_patterns trên directus.dot_config AFTER UPDATE
Function fn_sync_cp_patterns_to_meta: UPDATE _cp_patterns_cache_remote (foreign table) SET patterns = NEW.value
FDW WRITE happens in cùng transaction → immediate propagation, không TTL, không stale cache
Overhead ~5ms/UPDATE nhưng chỉ khi UPDATE dot_config (rare)

Tại sao chọn phương án này (so với alternatives):

Phương án	Pros	Cons	Chọn?
Local cache table + FDW sync trigger (CHỌN)	Immediate propagation (same TX), fast hot path (~0.2ms), preserve P11 runtime config	Cần postgres_fdw bidirectional, extra table + trigger	✅
Session GUC cache với TTL	Simplest, pure in-session	TTL > 0 → stale during window (breaks T4 immediate test); per-session (each connection cold-starts)	❌
Materialized view + REFRESH	Standard PG pattern	REFRESH cần trigger per UPDATE; lock contention; cross-DB refresh còn phức tạp	❌
Pin patterns into function body (regen on UPDATE)	Zero FDW/lookup overhead runtime	DDL-from-DML không sạch; CREATE OR REPLACE FUNCTION mỗi UPDATE	❌
Giữ FDW no optimization	Simplest	35x perf penalty — không đạt goal	❌

Tuân thủ NT/P11:

NT1 SSOT: source of truth vẫn là directus.dot_config. Cache là derived copy, sync immediate → no drift.
NT2+NT4: Thay đổi pattern = UPDATE dot_config → sync tự động → hiệu lực ngay. Không sửa code, không DROP/CREATE trigger.
P11: body trigger fn_context_pack_on_law_enact không bind pattern vào WHEN; đọc patterns runtime từ local cache (cache = proxy của dot_config, propagation zero-latency).

5. VẤN ĐỀ PHÁT SINH + FIX

Issue 1 (File 06): context_pack_manifest CHECK section_count=8 (v1.1) chưa migrate

Phát hiện: Test T2 fail với ERROR "violates constraint context_pack_manifest_section_count_check". Prompt §3 T2 chỉ yêu cầu FK section_code trên sections, nhưng CHECK section_count = 8 trên manifest là v1.1 legacy, v1.2 §5.3 đổi thành > 0. File 06 ban đầu không cover. Fix: Patch File 06 thêm ALTER TABLE context_pack_manifest DROP CONSTRAINT ... ADD CONSTRAINT ... CHECK (section_count > 0). Apply lại idempotent.

Issue 2 (File 06): `DROP TABLE context_pack_sections CASCADE` phá `v_context_pack_latest` view

Phát hiện: View đã tham chiếu bảng cũ; DROP CASCADE cũng drop view. Fix: Thêm CREATE OR REPLACE VIEW v_context_pack_latest AS ... sau migrate. Cũng là cơ hội upgrade view sang section_code (v1.2 §5.5).

Issue 3 (File 07): FDW bidirectional cần CREATE EXTENSION postgres_fdw ở cả 2 DB

Phát hiện: Extension installed ở incomex_metadata (Phase 1.5) nhưng không ở directus. Trigger sync ở directus cần extension ở directus. Fix: CREATE EXTENSION IF NOT EXISTS postgres_fdw ở cả 2 DB. Idempotent.

6. TUÂN THỦ LUẬT

Đ41 VPS-SSOT: 3 SQL files soạn trên VPS (upload local temp rồi scp — acceptable vì là artifacts migration, không phải source code DOT)
§6.X CẤM HARDCODE: Pattern trong dot_config.context_pack_watched_key_patterns là config JSONB, KHÔNG hardcode trong trigger body. Trigger fn_context_pack_on_law_enact đọc runtime từ local cache (mirror of config). Sync trigger body filter by NEW.key = 'context_pack_watched_key_patterns' — specific key check chứ không hardcode pattern.
P10 DEFAULT PRIVILEGES: _cp_patterns_cache table mới tự có GRANT SELECT cho context_pack_readonly nhờ DEFAULT PRIVILEGES từ Phase 1.5. Không GRANT riêng lẻ.
P11 runtime config: Pattern UPDATE có hiệu lực NGAY qua sync trigger same-TX (verified T4).
13 NT rà 3 tầng (memory #29):
- Tầng 1 code: N/A (Phase 1.6 là SQL DDL, không bash/python/code)
- Tầng 2 DDL: CHECK constraints dùng __ khớp reality; FK REFERENCES (không CHECK enum); CREATE OR REPLACE FUNCTION + DO block IF NOT EXISTS trigger; ALTER DEFAULT PRIVILEGES hoạt động tự động với table mới
- Tầng 3 data seed: 0 rows seeded mới (Phase 1.5 đã seed 7+8+9 rows). Cache table có 1 row tự động từ ON CONFLICT UPSERT initial.

7. DELIVERABLES UPLOAD KB

knowledge/dev/laws/dieu43-migrations/05-dieu43-v1-2-rev3-alter-check.sql ✅
knowledge/dev/laws/dieu43-migrations/06-dieu43-v1-2-schema-migrate.sql ✅
knowledge/dev/laws/dieu43-migrations/07-dieu43-v1-2-fdw-optimize.sql ✅
knowledge/dev/laws/dieu43-migrations/report-phase1-6-apply.md (this file)

8. KẾT LUẬN

Phase 1.6 PASS — 6/6 tiêu chí.

Hạ tầng v1.2 rev 3 hoàn chỉnh:

3 CHECK constraint khớp __ separator (Đ43 rev 3 §5.7 + §5.8)
Schema sections + requests + manifest migrate v1.1 → v1.2 (§5.2 + §5.3 + §5.4)
View v_context_pack_latest recreated với section_code FK (§5.5)
FDW optimization 24x speedup (4.9ms → 0.20ms per kb_documents INSERT); P11 runtime config preserved
Idempotent 100%

Sẵn sàng P1 Phase 4a (code skeleton build.sh ~500 dòng).

Report by Claude CLI — S178 Fix 12 Phase 1.6 — 2026-04-17

Đ43 Phase 1.6 APPLY — Report (v1.2 rev 3 — hoàn thiện hạ tầng)

CHECKPOINT

1. BASELINE TRƯỚC

2. 3 FILE MIGRATION APPLIED

3. 6 TIÊU CHÍ — EVIDENCE

T1 — 3 CHECK constraint PG có pattern __

T2 — context_pack_sections FK section_code hoạt động

T3 — context_pack_requests 3 retry cols default

T4 — FDW lookup: UPDATE dot_config → trigger fire immediate (không DROP/CREATE)

T5 — Benchmark 100 INSERT (giảm ≥ 50% latency)

T6 — Idempotent (rerun 3 files)

4. V3 PHƯƠNG ÁN CLI CHỌN — LÝ DO

Kiến trúc: Local cache table + bidirectional FDW sync trigger

5. VẤN ĐỀ PHÁT SINH + FIX

Issue 1 (File 06): context_pack_manifest CHECK section_count=8 (v1.1) chưa migrate

Issue 2 (File 06): DROP TABLE context_pack_sections CASCADE phá v_context_pack_latest view

Issue 3 (File 07): FDW bidirectional cần CREATE EXTENSION postgres_fdw ở cả 2 DB

6. TUÂN THỦ LUẬT

7. DELIVERABLES UPLOAD KB

8. KẾT LUẬN

T1 — 3 CHECK constraint PG có pattern `__`

T2 — `context_pack_sections` FK `section_code` hoạt động

T3 — `context_pack_requests` 3 retry cols default

Issue 2 (File 06): `DROP TABLE context_pack_sections CASCADE` phá `v_context_pack_latest` view