Incomex AI Portal
KB-15CB

Existing Birth Code Column Patterns — Read-only Investigation

9 min read Revision 1
dieu44birth-registryinvestigationread-onlyinformation_unit

Existing Birth Code Column Patterns — Read-only Investigation

Date: 2026-05-05 | Mode: read-only | Target: map precedent code-column choices to inform IU (information_unit) khai sinh.

Read-list confirmed

  • s145-m2-dot119-birth-triggers-report — DOT-119 v1.0.0, 4 birth triggers seeded
  • s157a-birth-registry-report.md — birth_registry PG triggers + indexes
  • s160-unified-system-report.md — fn_birth_registry_auto v2 (synthetic code) + 133 birth triggers
  • birth-process-v1.md — QT-001 backfill / QT-002 birth-first

Q1 — Birth triggers inventory (30 rows)

28 triggers fire fn_birth_registry_auto(<arg>); 3 triggers fire fn_birth_registry_auto_id() (no arg). 2 triggers on birth_registry itself (auto_certify, change_flag_matrix) are not entity-birth triggers.

Function Trigger count
fn_birth_registry_auto('code') 21
fn_birth_registry_auto('<non-code>') 5
fn_birth_registry_auto_id() 3
birth_registry self-triggers 2

Q2 — Code column groupings

Use code column (21): agents, approval_requests, checkpoint_sets, checkpoint_types, collection_registry, dot_tools, entity_dependencies, entity_species, governance_registry, meta_catalog, modules, normative_registry, pivot_definitions, system_issues, tasks, taxonomy, taxonomy_facets, ui_pages, workflow_change_requests, workflow_steps, +1 (count via Q1).

Use non-code column (5):

Table Code column arg Note
table_registry table_id text identifier (not numeric id)
workflows process_code domain-specific code field
law_catalog law_code domain-specific code field
measurement_registry measurement_id text id
pivot_results pivot_code domain-specific code field

Use synthetic-only via fn_birth_registry_auto_id() (3): law_jurisdiction, governance_relations, law_dot_enforcement → produces <table>:<id>.

Q3 — Function source

fn_birth_registry_auto(text) (current installed v2)

  • Reads code column from TG_ARGV[0] via to_jsonb(NEW)->>v_code_field (no dynamic EXECUTE, tolerates absent column).
  • Synthetic fallback present: if value is NULL or empty, v_entity_code := TG_TABLE_NAME || '::' || (NEW.id)::text (double colon).
  • No UUID/text cast logic beyond (NEW.id)::text in fallback path.
  • Idempotent: WHERE entity_code=… AND collection_name=… then ON CONFLICT (entity_code) DO NOTHING.
  • Reads _dot_origin from row, falls back to PG:trg_birth_<table>.

Note: the DOT-119 script source (Q7) still ships the v1 body without synthetic fallback (skips on null code). The installed function is v2 from S160 — script is out of date with installed function. For IU this matters: deploying via DOT-119 with --code-column=missing would re-install v1 and delete the synthetic fallback. ⚠

fn_birth_registry_auto_id() (no-arg variant)

  • Hardcoded v_entity_code := TG_TABLE_NAME || ':' || NEW.id::text (single colon).
  • No synthetic fallback (id is required NOT NULL anyway).
  • Used by 3 governance tables that have no code at all and id is the natural identifier.

Q4 — birth_registry schema

19 columns. Relevant: entity_code varchar NOT NULL, collection_name varchar NOT NULL, species_code varchar NULL, composition_level, dot_origin, born_at timestamptz, governance_role, certified bool NOT NULL. Constraints: PK(id), UNIQUE(entity_code) — global, not (entity_code, collection_name).

⚠ The unique constraint is on entity_code alone, even though the function checks idempotency on (entity_code, collection_name). Synthetic codes that include <table>::<id> avoid collisions across tables; raw UUIDs or short codes risk collision.

Q5 — entity_code format distribution (75,005 rows)

Format Count
TABLE::id (synthetic, double colon) 69,644
PREFIX-NNN (e.g. APR-0162, CAT-000) 4,474
OTHER (e.g. ar-SA, MSR-AUTO-CAT_1000, kb_audit_log) 564
TABLE:id (synthetic, single colon — fn_birth_registry_auto_id) 323
Pure UUID 0
Path/slash (%.% or %/%) 0

Per-collection samples include UUID embedded as id within the synthetic shell:

  • block_hero::40e873b9-b95b-41b8-…
  • navigation_items::00000000-0000-…
  • pages::0ea2e35a-…
  • knowledge_documents::2129
  • entity_labels::65538

So UUID-PK collections have always been registered through the synthetic shell, never as bare UUID.

Q6 — Code-column mapping (cross-referenced)

Table Trigger arg Function Column exists Sample entity_code
agents code auto yes AGT-001
approval_requests code auto yes APR-0162
table_registry table_id auto yes (varchar) table_registry::1*
workflows process_code auto yes workflows::1*
law_catalog law_code auto yes (e.g. LAW-22)
measurement_registry measurement_id auto yes MSR-AUTO-CAT_1000
pivot_results pivot_code auto yes PIV-101
law_jurisdiction — auto_id n/a law_jurisdiction:10
governance_relations — auto_id n/a governance_relations:10
law_dot_enforcement — auto_id n/a law_dot_enforcement:10

* table_registry::1 / workflows::1 indicate those rows landed via the synthetic fallback (column was empty when row was inserted), not via the named code column. The non-code arg is wired but the data also confirms v2 fallback is the real safety net.

Answer to Q6 lead question: Yes — multiple collections have been khai-sinh without using a code column: 5 via specialized text-id args, 3 via the no-arg _id variant.

Q7 — DOT-119 script highlights

  • Path: /opt/incomex/dot/bin/dot-birth-trigger-setup (275 lines).
  • Hard-codes the master function body (v1 — no synthetic fallback). Re-running it would clobber the v2 installed function.
  • CODE_FIELDS associative array only maps table_registry → table_id, workflows → process_code. Other non-code mappings (law_catalog, measurement_registry, pivot_results) were applied outside this script (later patches / S160 work), not from the DOT.
  • No --code-column= CLI flag — code column is hardcoded per collection inside the script.
  • No path / _id-variant code path: the script cannot deploy fn_birth_registry_auto_id().

Q8 — information_unit columns

15 columns. No code column. Key candidates:

Column Type Nullable Default
id uuid NO gen_random_uuid()
canonical_address text NO —
unit_kind text NO —
owner_ref text NO —
lifecycle_status text NO 'draft'

canonical_address is text and NOT NULL — closest to a "name" field — but its lexical shape (likely path-style) has no precedent in birth_registry.

Final answers

  1. IU không có code — đã có precedent chưa? Có. 8/29 entity-birth collections không dùng cột code: 5 dùng non-code arg (table_id, process_code, law_code, measurement_id, pivot_code), 3 dùng fn_birth_registry_auto_id() no-arg.
  2. Precedent dùng cột gì? Specialized text-id columns (table_id, measurement_id) hoặc domain-code columns (process_code, law_code, pivot_code); hoặc no-arg fallback to <table>:<id>.
  3. Precedent dùng UUID/id? Có — gián tiếp. 0 rows là UUID trần. ~70k rows ở dạng synthetic shell <table>::<uuid> (double colon, fn_birth_registry_auto v2 fallback) hoặc <table>:<id> (single colon, _id variant). UUID-PK collections (block_hero, navigation_items, pages, posts, …) đều đi qua synthetic shell.
  4. Precedent dùng path/canonical text? Không. 0 rows match %.% hoặc %/%. Đẩy canonical_address vào sẽ là precedent đầu tiên cho định dạng path.
  5. IU nên dùng gì?
    • Khuyến nghị mạnh nhất: synthetic shell information_unit::<uuid> — khớp 100% precedent của các UUID-PK collections. Implementation = không pass arg hoặc pass tên cột không tồn tại để v2 fallback kích hoạt; idempotency và unique-entity_code an toàn vì <table>::<uuid> không đụng độ.
    • Tránh id arg trần → tạo entity_code = raw UUID, không có precedent, có rủi ro va chạm nếu UUID xuất hiện ở các nguồn khác.
    • canonical_address chỉ chọn nếu IU domain bắt buộc entity_code phải đọc được & ổn định trong toàn hệ thống — sẽ tạo precedent path-style mới, cần đồng thuận governance.
    • Cần adapt tool: DOT-119 script phải được sửa trước khi dùng cho IU vì (a) ghi đè master function về v1 không có fallback, (b) không hỗ trợ --code-column CLI, (c) không hỗ trợ deploy fn_birth_registry_auto_id(). Cách an toàn nhất: deploy trigger IU bằng SQL trực tiếp (CREATE TRIGGER … EXECUTE FUNCTION fn_birth_registry_auto('non_existent_col')) thay vì chạy DOT-119, để giữ nguyên function v2 đã cài.

Hard stop confirmation

Read-only only. Không dry-run DOT-119. Không tạo trigger. Không tạo IU rows. Không sửa gì.