---

title: 05 — PG Substrate Mapping (VERIFIED) date: 2026-05-30

05 — Live PG Substrate Mapping (READ-ONLY, VERIFIED)

All facts live-queried (db directus, schema public, role context_pack_readonly, READ ONLY).

Live counts

entity_species=42 · species_collection_map=164 · collection_registry=168 · table_registry=21 · birth_registry=964,607 · governance_registry=9 · meta_catalog=169 · pivot_definitions=37 · pivot_results=126 (needs_refresh=3; oldest 2026-05-11 12:50, newest 2026-05-30 13:57) · information_unit=219 · iu_relation=60 · dot_tools=309 · dot_iu_command_catalog=54.

Pivot engine (Điều 26)

Functions: pivot_count(p_code DEFAULT NULL)→(code,name,source_object,count_value) · pivot_query(p_code)→(pivot_code,pivot_name,source_object,group_values,metric_values) · pivot_matrix(p_source_table,p_row_axis,p_col_axis,p_agg,p_filter)→jsonb · refresh_meta_catalog_from_pivot()→(cat_code,old_count,new_count,changed) · refresh_pivot_results()→(piv_code,rows_upserted). No fn_pivot_count.

pivot_definitions (37; codes PIV-*/MTX-*, NOT CAT-*). Cols: code,name,source_object,group_spec,metric_spec,registry_group,composition_level,species,parent_code,is_active,superseded_by,cache_tier,matrix_spec,template_code/version. parent_code NULL on all 37. pivot_count() returns 35 = the 35 is_active=true defs (skips inactive PIV-020 _uncategorized + MTX-TEST) → effectively active-only.

VERIFIED pivot_count() values: PIV-001 meta_catalog=169 · PIV-002 table_registry=21 · PIV-003 modules=5 · PIV-004 workflows=2 · PIV-005 workflow_steps=70 · PIV-006 wcr=3 · PIV-007 dot_tools=309 · PIV-008 ui_pages=37 · PIV-009 collection_registry=168 · PIV-010 tasks=10 · PIV-011 agents=6 · PIV-012 checkpoint_types=31 · PIV-013 checkpoint_sets=2 · PIV-014 entity_dependencies=142 · PIV-015 trigger_registry=107 · PIV-016 entity_species=42 · PIV-017 species_collection_map=164 · PIV-018 entity_audit_queue=1 · PIV-019 birth_registry=964,607 · PIV-021 task_comments=75 · PIV-101/102/106 meta_catalog grouped=164 · PIV-103 entity_species grouped=42 · PIV-104 dot_tools by group=16 · PIV-105 collection_registry by class=168 · PIV-201/202/203 meta_catalog L2 atom/mol/comp=66/47/33 · PIV-204/205/206=0 · PIV-207 approval_requests=211 · MTX-L1-OVERVIEW/MTX-L2-ATOM birth_registry=964,607.

Finding A (revised): duplicate-count risk = multiple ACTIVE pivots over same source_object (meta_catalog: PIV-001 total 169 vs PIV-101/102/106 grouped 164; dot_tools: PIV-007 total 309 vs PIV-104 16 groups). Contract picks ONE canonical total per node; grouping pivots are child layers, never summed.

Pivot views (VERIFIED)

v_pivot_by_level = meta_catalog GROUP BY composition_level (count+sum(record_count)). v_pivot_species_by_level = entity_species GROUP BY composition_level. v_pivot_dot_by_category = dot_tools status=published GROUP BY category (23). v_registry_counts_compat = pivot_definitions JOIN LATERAL pivot_count(code) WHERE is_active — pivot-backed, reusable.

Substrate correction (vs memory & earlier draft): entity_composition_levels and entity_living_index DO NOT EXIST (UndefinedTable). Layers = meta_catalog.composition_level string VALUES only — no composition-levels dimension table (GAP stands). entity_species has spurious extra composition_level='1' (1 row) beside atom(21)/molecule(11)/compound(8)/meta(1); all depth=1, parent_id NULL → flat tree.

Reuse verdict

REUSE: pivot engine + v_registry_counts_compat + 3 grouping views + refresh_meta_catalog_from_pivot() + entity_species/collection_registry/table_registry/species_collection_map/birth_registry + dot/iu/kg. RECONCILE: meta_catalog (counts defer to pivot). REUSE(domain-map): governance_registry. NEW(design only): v_living_lists, v_registries_pivot_tree, test_counting_contract(), v_count_drift, fn_node_substrate. NEW(GAP, deferred): composition-levels dimension table.