S178 Fix 20 M3B — Register 5 PG tables to Directus

Session: S178-FIX20 Date: 2026-04-19 Agent: claude-code Status: ✅ Success (with 1 prereq fix + 1 retroactive APR obligation)

---

Mục tiêu

Register 5 bảng PG-only (Fix 16–18) vào Directus schema để agent/DOT truy vấn qua API chuẩn thay vì SSH psql.

5 bảng: apr_action_types, apr_request_types, apr_approvals, admin_fallback_log, law_version_verification_log.

---

Tiêu chí đo — Kết quả

#	Tiêu chí	Kết quả	Evidence
T1	Directus API /items/<t> = 200 với admin token × 5	✅ PASS	pre: 403×5, post: 200×5
T2	collection_registry có 5 row	✅ PASS	COL-163/164/165 (pre-existing Fix 17) + COL-174/175 (new)
T3	meta_catalog có 5 row	✅ PASS	CAT-1006 … CAT-1010
T4	Data không mất/sửa	✅ PASS	Counts: 5/14/33/5→6/3 (admin_fallback_log +1 là log entry mới, ghi nhận)
T5	Permissions PG + Directus	✅ PASS	incomex SELECT verified; workflow_admin owner/all; Editor + Agent + AI Agent read-only policies × 5
T6	admin_fallback_log row mới	✅ PASS	id=6, retroactive_deadline 2026-04-20 14:25:57 UTC

---

Changes applied

1. admin_fallback_log id=6 (fallback — DOT không tồn tại)

INSERT INTO admin_fallback_log (dot_code, reason, backup_path, patch_diff, verify_evidence, approved_by, session_code, status)
VALUES ('dot-apr-types-register-missing', ...);
-- id=6, retroactive_deadline='2026-04-20 14:25:57 UTC'

Why fallback: probe dot_tools WHERE code='dot-apr-types-register' trả 0 rows → DOT chưa được tác giả. Per Đ35 §6.5 áp admin fallback.

2. Directus — 5 collections + 15 read-only permissions

INSERT INTO directus_collections (collection, icon, note, hidden, singleton, archive_app_filter, collapse, versioning)
VALUES ('apr_action_types','policy',...), ('apr_request_types','assignment',...), ...;

INSERT INTO directus_permissions (collection, action, permissions, fields, policy)
-- read-only × 15: Editor (4ea86fab…), Agent (74d6c90f…), AI Agent (e81a70bc…) × 5 collections

⚠️ Critical: sau khi raw INSERT vào directus_collections, phải docker restart incomex-directus. POST /utils/cache/clear không đủ — schema cache chỉ refresh khi process restart.

3. Đ36 — collection_registry + meta_catalog

-- collection_registry: 2 mới (COL-174 admin_fallback_log, COL-175 law_version_verification_log)
-- 3 pre-existing (COL-163/164/165 từ Fix 17) — dupes vô tình đã DELETE (COL-171/172/173)
-- meta_catalog: CAT-1006..CAT-1010 (format enforced: CAT-NNN)

4. PG GRANTs

GRANT SELECT ON <5 tables> TO incomex;  -- verified (count=3 from law_version_verification_log as incomex)
GRANT ALL ON <5 tables> TO workflow_admin;  -- owner via workflow_admin connection

Note: psql -U directus không có quyền GRANT trên law_version_verification_log (owned by workflow_admin). Phải dùng psql -U workflow_admin.

5. vps_deploy_log

mission_code='S178-FIX20-M3B', status='success', schema_touched=true
-- schema_touched=true because prereq fix to v_registry_counts.id (not to 5 target tables)

---

Prereq fix (S178 Fix 17 tech debt cleared)

v_registry_counts.id missing DEFAULT → chặn tất cả meta_catalog INSERT qua trigger fn_auto_sync_v_registry_counts. Error: null value in column "id" of relation "v_registry_counts" violates not-null constraint.

Fix:

CREATE SEQUENCE IF NOT EXISTS v_registry_counts_id_seq;
SELECT setval('v_registry_counts_id_seq', GREATEST(COALESCE((SELECT MAX(id) FROM v_registry_counts),0),1));
ALTER TABLE v_registry_counts ALTER COLUMN id SET DEFAULT nextval('v_registry_counts_id_seq');
ALTER SEQUENCE v_registry_counts_id_seq OWNED BY v_registry_counts.id;

---

Follow-up bắt buộc

1. ⏰ Retroactive APR cho admin_fallback_log id=6 — deadline 2026-04-20 14:25:57 UTC (~24h). Tạo APR mới + UPDATE status='retroactive_documented', else cron flips to audit_overdue.
2. Tác giả DOT dot-apr-types-register để lần sau tránh admin fallback.
3. Directus fields (directus_fields) chưa INSERT explicit cho 5 bảng — Directus auto-introspect từ information_schema khi restart. Admin UI có thể limited interface cho text-PK (apr_action_types, apr_request_types) — đã biết từ Fix 17 M3A.

---

Gotchas ghi nhớ

• Zsh heredoc + SSH: parens () và Unicode Đ trong SQL trigger zsh glob errors. Dùng cat file.sql | ssh … 'docker exec -i postgres psql …'.
• Directus schema refresh: cache clear KHÔNG đủ cho raw-inserted directus_collections. docker restart required.
• role_table_grants không luôn surface effective grants — verify bằng cách connect as target role và chạy query thực tế.
• meta_catalog code format: CAT-NNN strict. MC-* hoặc text suffix bị Birth Gate reject.
• Không có postgres superuser trong container. workflow_admin owner → dùng -U workflow_admin cho GRANTs trên law_version_verification_log.

---

Files touched

• PG: admin_fallback_log (+1 row), directus_collections (+5), directus_permissions (+15), collection_registry (+2 net, 3 dupes deleted), meta_catalog (+5), vps_deploy_log (+1), v_registry_counts (sequence + default — prereq fix)
• No source-code files modified.

Approvals

• approved_by: president (admin fallback Đ35 §6.5)
• session_code: S178-FIX20