DOT-119 Repair — P1a Schema Tool Inspection Report

Date: 2026-05-05 | Phase: P1a (read-only) Controlling: 18a rev4 FINAL + 18b rev3 PASS + P0 mapping report Mode: READ-ONLY. Zero mutation. Only cat + --help invocations.

§1. DOT-074 — `dot-schema-registry-collections-ensure`


Path	`/opt/incomex/dot/bin/dot-schema-registry-collections-ensure`
Size / Mtime	16430 bytes, Apr 2 16:35
Version	1.0.0
Stated purpose	Creates 4 registry collections (`dot_tools`, `ui_pages`, `collection_registry`, `agents`) + fixes `modules` permissions

Capabilities

Hardcoded collection schemas + per-collection specific_fields lists. collection_registry's hardcoded list is [collection_name, group, icon, field_count, has_note, purpose]. 3 new fields are NOT in this list.
Goes through Directus REST API (POST /collections, POST /fields/{coll}, POST /permissions, PATCH /items/meta_catalog) — not raw PG DDL.
Idempotent at the per-call level: collection_exists() and field_exists() skip if found.
Side effects beyond schema (this is the concern):
- STEP 3: writes permissions for PUBLIC_POLICY and AI_AGENT_POLICY on 5 collections (read / CRUD).
- STEP 4: PATCHes meta_catalog rows CAT-006, CAT-007, CAT-008, CAT-010, CAT-002 to status='active' and registry_collection=<X>. Will overwrite if any of those rows have been retired/changed since the original run.
No CREATE OR REPLACE FUNCTION; no DDL on triggers/functions; no fn_birth_registry_auto clobber risk.
No --add-field= / --column= CLI — cannot add arbitrary fields without source edit.

To use for the 3 new fields

Would require (a) editing the script to append birth_code_strategy, birth_code_column, birth_identity_source to COLLECTIONS['collection_registry']['specific_fields'] and (b) re-running. Existing fields skip, new fields added. But STEP 3 (permissions) and STEP 4 (meta_catalog PATCH) re-fire unless guarded — pollution risk.

Clobber risk

Schema/data overwrite: MEDIUM (meta_catalog PATCH forces status, registry_collection).
Functions/triggers: NONE.

§2. DOT-063 — `dot-schema-apply`


Path	`/opt/incomex/dot/bin/dot-schema-apply`
Size / Mtime	9440 bytes, Apr 2 16:35
Version	1.0.0
Stated purpose	Apply field-add schema changes via Directus API from a JSON spec file

Capabilities

Generic, spec-driven. Usage: dot-schema-apply <spec.json> [--cloud|--local|--dry-run]. Spec format:

{ "collection": "<name>", "fields": [ { "field": "...", "type": "...", "schema": {...}, "meta": {...} }, ... ] }

Validates collection exists (404 if not), fetches existing fields, then for each field in spec: skips if exists else POST /fields/{collection}.
--dry-run supported.
Pure single-concern: only adds fields. Does not create/drop collections, does not modify permissions, does not touch meta_catalog or any data row, does not edit functions/triggers.
Goes through Directus REST → Directus auto-registers directus_fields.

Clobber risk

NONE. No assumptions baked in beyond what's in the spec file.

To use for the 3 new fields

Write a spec JSON listing the 3 fields, then run dot-schema-apply <spec>.json --cloud. Idempotent re-runs safe.

§3. DOT-067 — `dot-schema-diff`


Path	`/opt/incomex/dot/bin/dot-schema-diff`
Size / Mtime	5249 bytes, Apr 2 16:35
Version	1.0.0
Stated purpose	Compare schema snapshots — `POST /schema/diff` (live API mode) or local file-vs-file mode

Capabilities

Read-only. No POST /collections, no POST /fields, no PATCH, no DELETE. Only POST /schema/diff (Directus drift-detection endpoint, returns the diff payload, does not apply it) or local Python json compare.

Clobber risk

NONE. Cannot mutate. Useful as P1 pre/post verification (snapshot before, snapshot after, diff).

§4. DOT-068 — `dot-schema-ensure`


Path	`/opt/incomex/dot/bin/dot-schema-ensure`
Size / Mtime	6955 bytes, Apr 2 16:35
Stated purpose	Ensure the `agent_views` collection exists with a fixed set of fields

Capabilities

Hardcoded scope. COLLECTION="agent_views" and REQUIRED_FIELDS=(source_id permalink title content summary tags is_global). Hardcoded JSON payloads per field.
Not generic; no CLI to override target collection or fields. Out of scope for collection_registry work.

Clobber risk

NONE for our scope (won't touch collection_registry).

§5. Answers to 5 Questions

Q1: DOT-074 có add columns vào collection_registry được không?
A1: PARTIAL — chỉ qua source-edit + re-run. Hardcoded specific_fields list không có
    birth_code_strategy / birth_code_column / birth_identity_source. Có thể append vào
    list và rerun (existing skip, mới thì add), NHƯNG STEP 3 (permissions) + STEP 4
    (PATCH meta_catalog status='active' cho 5 CAT codes) sẽ re-fire — gây side effect
    ngoài phạm vi "add 3 fields". Không sạch.

Q2: DOT-063/067/068 support arbitrary ADD COLUMN an toàn không?
A2:
  - DOT-063 dot-schema-apply: YES. Generic spec-driven, idempotent skip existing,
    chỉ POST /fields. Không meta_catalog, không permissions, không function/trigger.
    Đây là kênh sạch nhất cho add field.
  - DOT-067 dot-schema-diff: NO (read-only diff). Hữu ích làm preflight/postflight
    verification, không add column.
  - DOT-068 dot-schema-ensure: NO. Hardcoded cho agent_views, không generic.

Q3: Dùng/extend DOT-074 có thấp rủi ro hơn raw DDL không?
A3:
  - DOT-074 vs raw DDL: ngang/cao hơn raw DDL về phạm vi (DOT-074 mutate cả
    meta_catalog + permissions, raw DDL chỉ ALTER TABLE). DOT-074 lợi 1 điểm: đi
    qua Directus REST nên directus_fields tự auto-register (raw PG DDL bypass —
    xem project memory s178_fix20_m3a Directus fields gap).
  - Nhưng DOT-063 dot-schema-apply ALSO đi qua Directus REST + scope hẹp hơn
    DOT-074 → DOT-063 thấp rủi ro nhất.
  - Tóm lại: DOT-063 < raw DDL < DOT-074 về thứ tự rủi ro tăng dần (cho mục đích
    add 3 fields). Câu hỏi gốc: DOT-074 KHÔNG thấp rủi ro hơn DOT-063.

Q4: Tool nào có embedded assumption cũ có thể clobber schema/data/function?
A4:
  - DOT-074:
    * PATCHes 5 meta_catalog rows (CAT-002, CAT-006, CAT-007, CAT-008, CAT-010) →
      forces status='active' + registry_collection field. Nếu rows này đã được
      retire/edit từ Apr 2, rerun sẽ OVERWRITE. Mức rủi ro: medium.
    * POSTs permissions cho PUBLIC_POLICY + AI_AGENT_POLICY trên 5 collections.
      400 'already exists' = vô hại; nhưng nếu policy IDs đã rotate → tạo permission
      orphan trên policy không tồn tại. Mức rủi ro: low.
  - DOT-063: NONE. Pure spec-driven.
  - DOT-067: NONE. Read-only.
  - DOT-068: NONE for our scope (chỉ touch agent_views).
  - KHÔNG tool nào trong 4 này chứa CREATE OR REPLACE FUNCTION → fn_birth_registry_auto
    v2 (hash 1f729b3571a74963089bb3ef388217f3) AN TOÀN bất kể chọn tool nào.

Q5: P1 nên dùng kênh nào?
A5: KHUYẾN NGHỊ — DOT-063 dot-schema-apply + spec JSON mới.

Reasoning:
  1. Single-concern: chỉ POST /fields. Không đụng meta_catalog, permissions, function.
  2. Idempotent skip existing → re-run an toàn.
  3. Đi qua Directus REST → directus_fields tự register, đóng gap đã memo
     trong s178_fix20_m3a (raw PG DDL bypass Directus fields).
  4. Spec JSON lưu paper-trail rõ ràng cho 3 fields exact (interface, options, etc).
  5. fn_birth_registry_auto baseline hash giữ nguyên — không có CREATE OR REPLACE
     FUNCTION nào trong DOT-063.
  6. --dry-run available → có thể preview trước khi áp dụng thật.
  7. P1 prompt sẽ gồm: (a) viết spec JSON, (b) chạy --dry-run, (c) chạy live,
     (d) verify qua DOT-067 dot-schema-diff (postflight), (e) verify fn hash unchanged.

  Lựa chọn thay thế bị loại:
  - DOT-074: kèm side effect lớn (meta_catalog + permissions); cần source-edit
    rồi rerun không sạch.
  - Raw DDL: bypass Directus → phải tự PG-workaround đăng ký directus_fields
    (xem precedent S178 Fix 20 M3A); nhiều việc hơn, dễ sai.
  - DOT-068: không relevant (hardcoded agent_views).

§6. Recommendation

Channel for P1 DDL: DOT-063 dot-schema-apply with a spec JSON file targeting collection_registry.

Suggested P1 prompt outline (do NOT execute now — for next-phase drafting):

Compose spec JSON dot/specs/collection_registry_birth_identity_fields.json:

{  "collection": "collection_registry",  "fields": [    {"field": "birth_code_strategy", "type": "string",     "schema": {"is_nullable": true},     "meta": {"interface": "select-dropdown",              "options": {"choices": [                {"text": "column",                  "value": "column"},                {"text": "synthetic_id",            "value": "synthetic_id"},                {"text": "legacy_id_single_colon",  "value": "legacy_id_single_colon"},                {"text": "subordinate",             "value": "subordinate"},                {"text": "disabled",                "value": "disabled"},                {"text": "unclassified",            "value": "unclassified"}              ]}}},    {"field": "birth_code_column",  "type": "string",     "schema": {"is_nullable": true},     "meta": {"interface": "input"}},    {"field": "birth_identity_source", "type": "string",     "schema": {"is_nullable": true},     "meta": {"interface": "select-dropdown",              "options": {"choices": [                {"text": "inferred_from_existing_trigger", "value": "inferred_from_existing_trigger"},                {"text": "manual",          "value": "manual"},                {"text": "system_default",  "value": "system_default"},                {"text": "unclassified",    "value": "unclassified"}              ]}}}  ]}

(P1 prompt should validate enum sets against 18a rev4 §3 strategy list.)

Snapshot baseline before: dot-schema-snapshot (or equivalent) → save pre.json.
Capture fn_birth_registry_auto hash (must remain 1f729b3571a74963089bb3ef388217f3).
Run dot-schema-apply <spec> --cloud --dry-run → review.
Run dot-schema-apply <spec> --cloud → live add.
Postflight: re-query information_schema.columns for the 3 fields; re-hash function (must match baseline); dot-schema-diff between pre/post.
Commit spec JSON to agent-data-repo per feedback_git_commit_after_vps_edit.

§7. Clobber / Safety Assessment Summary

Tool	Schema clobber	Data row clobber	Function/trigger clobber
DOT-074	low (idempotent skip)	medium (meta_catalog PATCH)	none
DOT-063	none (skip existing)	none	none
DOT-067	none (read-only)	none	none
DOT-068	none (out of scope)	none	none

fn_birth_registry_auto v2 — hash 1f729b3571a74963089bb3ef388217f3 — is safe regardless of tool choice among these 4. The only known clobber threat to the function is DOT-119 itself (dot-birth-trigger-setup) per P0 §2 — separate concern, deferred to P2.

P1a schema tool inspection report | 2026-05-05 | HARD STOP. Awaiting GPT/User review before P1 DDL prompt drafted.

DOT-119 Repair — P1a Schema Tool Inspection Report

§1. DOT-074 — dot-schema-registry-collections-ensure

Capabilities

To use for the 3 new fields

Clobber risk

§2. DOT-063 — dot-schema-apply

Capabilities

Clobber risk

To use for the 3 new fields

§3. DOT-067 — dot-schema-diff

Capabilities

Clobber risk

§4. DOT-068 — dot-schema-ensure

Capabilities

Clobber risk

§5. Answers to 5 Questions

§6. Recommendation

§7. Clobber / Safety Assessment Summary

§1. DOT-074 — `dot-schema-registry-collections-ensure`

§2. DOT-063 — `dot-schema-apply`

§3. DOT-067 — `dot-schema-diff`

§4. DOT-068 — `dot-schema-ensure`