05 — Live PG Substrate Inventory (VERIFIED v2)

v2 ERRATA: v1 of this doc contained column/count specifics asserted before the queries returned. This v2 is rewritten from verified live results (DB directus, role context_pack_readonly, 2026-05-30 ~13:27–13:29 UTC). Corrected: meta_catalog 169 (not 156); species_collection_map 164 (not 38); composition_levels table does NOT exist; pivot schema = source_object/group_spec/is_active; no fn_pivot_count.

Exact live counts (count(*))

Object	Count		Object	Count
meta_catalog	169		dot_tools	309
collection_registry	168		dot_iu_command_catalog	54
table_registry	21		information_unit	219
pivot_definitions	37		iu_relation	60
pivot_results	126		governance_registry	9
entity_species	42 (all depth=1)		species_collection_map	164

Real schemas (key tables — verbatim columns)

• meta_catalog: code, name, name_en, entity_type, source_model, source_location, registry_collection, sync_script, sync_frequency, record_count, ui_page, status, description, layer, actual_count, orphan_count, last_scan_date, baseline_count, atom_group, composition_level, identity_class, _dot_origin, active_count, code_column, name_column, display_order, species_count. → stores numbers in 6 columns.
• pivot_definitions: code, name, source_object, filter_spec(jsonb), group_spec(jsonb), metric_spec(jsonb), registry_group, composition_level, species, parent_code, display_order, is_active, cache_tier, superseded_by, normalized_signature, matrix_spec, template_code, template_version.
• pivot_results: pivot_code, pivot_name, source_object, group_values(jsonb), metric_values(jsonb), refreshed_at, needs_refresh. (metric_values = {count:N} or {total:N})
• entity_species: code, species_code, display_name, composition_level, management_mode, prefix, description, status, parent_id, depth, kg_metadata. (NO species_kind, NO name)
• collection_registry: …code, name, classification, owner, collection_name, field_count, species_code, governance_role, storage_role, source_kind, coverage_*… → no record_count column (counts live in meta_catalog).
• table_registry: table_id, name, collection, fields(jsonb), page_url, enable_*, status, module… → no row_count column.
• composition_levels: DOES NOT EXIST as a table. The 6 layers exist only as string values in composition_level columns (atom/molecule/compound/material/product/building, + dirty values 'meta','1').

Functions (verified via pg_proc)

Function	Signature	Role	Verdict
pivot_count	(p_code text)→record	canonical live count	REUSE (canonical)
pivot_query	(p_code text)→record	query a pivot	REUSE
pivot_matrix	(p_source_table, p_row_axis, p_col_axis, p_agg, p_filter jsonb)→jsonb	2-D matrix	REUSE
refresh_pivot_results	()→record	recompute pivot_results	REUSE; ran 13:27 today
refresh_meta_catalog_from_pivot	()→record	sync meta_catalog FROM pivot (correct direction)	REUSE (preferred)
refresh_registry_count	(p_cat_code, p_count)→void; + trigger variants	per-CAT count writer	RECONCILE
refresh_registry_counts	()→void	bulk count writer	RECONCILE
fn_auto_sync_v_registry_counts	trigger	backs trg_auto_sync_registry_counts	RECONCILE
birth family	fn_birth_registry_auto, fn_birth_gate, fn_pre_birth_check(p_collection,p_code,p_name,p_dot_origin), fn_birth_onboarding_full_scan, …	birth automation	REUSE
trg_pivot_def_refresh	trigger	refresh on pivot_def change	REUSE

No fn_pivot_count exists (v1 error). Only pivot_count.

Triggers (verified)

• trg_auto_sync_registry_counts — ENABLED ('O'), on table meta_catalog (fn fn_auto_sync_v_registry_counts). (v1 wrongly said birth_registry.)

Views

• v_registry_counts (≈169 rows), v_registry_summary (1 row), v_kg_edges_all (view). All feed the registries page; overlap pivot → RECONCILE.

Pivot coverage (verified — 37 defs, 35 active / 2 inactive)

Grouped by registry_group → source_object:

• cấu_trúc: PIV-001 meta_catalog, PIV-002 table_registry, PIV-009 collection_registry
• công_cụ: PIV-003 modules, PIV-007 dot_tools, PIV-008 ui_pages, PIV-011 agents
• cross-table: PIV-101/102/106 meta_catalog, PIV-103 entity_species, PIV-104 dot_tools (by category), PIV-105 collection_registry
• default: PIV-015 trigger_registry, PIV-016 entity_species, PIV-017 species_collection_map, PIV-018 entity_audit_queue, PIV-019 birth_registry, PIV-020 _uncategorized (inactive), PIV-021 task_comments
• dữ_liệu: PIV-010 tasks · giám_sát: PIV-014 entity_dependencies, PIV-207 approval_requests
• l2-drill: PIV-201..206 meta_catalog by composition_level (atom→building)
• matrix_l1/l2: MTX-L1-OVERVIEW, MTX-L2-ATOM (birth_registry) · quy_trình: PIV-004 workflows, PIV-005 workflow_steps, PIV-006 workflow_change_requests, PIV-012 checkpoint_types, PIV-013 checkpoint_sets · test: MTX-TEST (inactive) → ~20 distinct source_objects covered. Adding a list = INSERT one pivot_definitions row + refresh. Verdict: REUSE (canonical engine).

Counting-integrity findings (verified)

• DOT dual rows: CAT-006 (record_count 309, actual_count 163, orphan 0) vs CAT-DOT (record_count 307, actual_count null, orphan 140, entity_type dot_total, composition_level 'meta'). Live dot_tools=309; fresh PIV-007=309. → CAT-006.record matches live; CAT-006.actual (163) and CAT-DOT (307) are both wrong/stale. Pivot path is right; stored paths drift.
• record_count ≠ actual_count in 7/169 rows (incl CAT-006 309≠163, CAT-007 37≠52). meta_catalog is internally self-inconsistent across its own count columns.
• meta_catalog stores numbers in 164/169 rows (record_count) + 161 actual_count — against Đ26 MT5 ("list of lists, no numbers").
• pivot freshness uneven: pivot_results newest 2026-05-30 13:27, oldest 2026-05-11, 3 rows needs_refresh=true. Refresh runs but not uniformly; staleness is per-row, not global.