---

title: 12 — SQL Appendix / Evidence date: 2026-05-31 note: every statement below was run read-only (query_pg) or inside BEGIN..ROLLBACK (workflow_admin). Reproducible.

12 — SQL Appendix / Evidence

E1. Leaf-set rule + invariant (read-only)

SELECT count(*) AS leaf_rows,
  count(*) FILTER (WHERE actual_count IS NULL) AS leaf_actual_null,
  sum(record_count) AS sum_record, sum(actual_count) AS sum_actual, sum(orphan_count) AS sum_orphan,
  sum(record_count)-sum(actual_count) AS net_gap,
  count(*) FILTER (WHERE record_count IS DISTINCT FROM actual_count) AS drift_rows,
  count(*) FILTER (WHERE record_count > actual_count) AS phantom_side,
  count(*) FILTER (WHERE actual_count > record_count) AS orphan_side,
  sum(GREATEST(record_count-actual_count,0)) AS phantom_mag,
  sum(GREATEST(actual_count-record_count,0)) AS orphan_mag
FROM meta_catalog
WHERE composition_level <> 'meta' AND entity_type NOT LIKE '%_total' AND entity_type <> 'all';
-- => 160 | 5 | 1954686 | 1917903 | 0 | 36783 | 3 | 2 | 1 | 36798 | 15

E2. The 3 leaf drift rows (read-only)

CAT-023 birth_registry (A)  record 980378  actual 943726  gap +36652  phantom_side
CAT-006 dot_tools/File (B)  record 309     actual 163     gap +146    phantom_side
CAT-007 ui_pages/File  (B)  record 37      actual 52      gap -15     orphan_side

E3. Pivot reproduction + hidden drift (read-only)

pivot_count('PIV-001')=169 ; ('PIV-019')=980378 ; ('PIV-007')=309
CAT-068 entity_labels: stored=actual=690341  vs live count(*)=718744  (hidden +28403)
CAT-017 system_issue:  stored=actual=171939  vs live count(*)=179074  (hidden +7135)

E4. Branch D full rehearsal (workflow_admin, BEGIN..ROLLBACK) — abridged

BEGIN;
SET LOCAL lock_timeout='3s'; SET LOCAL statement_timeout='20s'; SET LOCAL idle_in_transaction_session_timeout='40s';

CREATE VIEW v_registry_leaf_set AS
  SELECT code, entity_type, composition_level, layer, record_count, actual_count, orphan_count,
         baseline_count, source_model, source_location, name_column, code_column
  FROM meta_catalog
  WHERE composition_level <> 'meta' AND entity_type NOT LIKE '%_total' AND entity_type <> 'all';

-- CANONICAL (fan-out-free) v_count_integrity:
CREATE VIEW v_count_integrity AS
  SELECT m.code, m.entity_type, m.composition_level, m.source_model, m.source_location,
         m.record_count AS counted, m.actual_count, COALESCE(m.orphan_count,0) AS orphan_count,
         GREATEST(COALESCE(m.record_count,0)-COALESCE(m.actual_count,0),0) AS phantom_count_derived,
         GREATEST(COALESCE(m.actual_count,0)-COALESCE(m.record_count,0),0) AS unregistered_surplus,
         (m.record_count IS DISTINCT FROM m.actual_count) AS drift_flag,
         EXISTS (SELECT 1 FROM pivot_definitions pd
                 WHERE pd.source_object = split_part(m.source_location,':',2) AND pd.is_active) AS pivot_backed,
         CASE WHEN m.actual_count IS NULL THEN 'unverified'
              WHEN m.record_count = m.actual_count THEN 'ok' ELSE 'failed' END AS count_integrity_status
  FROM meta_catalog m
  WHERE m.composition_level <> 'meta' AND m.entity_type NOT LIKE '%_total' AND m.entity_type <> 'all';

CREATE VIEW v_count_drift AS
  SELECT code, entity_type, composition_level, source_model, source_location,
         record_count, actual_count, (record_count-actual_count) AS gap,
         CASE WHEN record_count>actual_count THEN 'phantom_side'
              WHEN actual_count>record_count THEN 'orphan_side' ELSE 'none' END AS drift_side
  FROM v_registry_leaf_set WHERE record_count IS DISTINCT FROM actual_count;

CREATE VIEW v_registries_pivot_tree AS
  SELECT p.code AS node_code, p.parent_code, p.source_object, p.composition_level, p.species,
         p.registry_group, p.is_active, (p.parent_code IS NULL) AS is_root,
         EXISTS (SELECT 1 FROM pivot_definitions c WHERE c.parent_code=p.code) AS has_children
  FROM pivot_definitions p;

CREATE FUNCTION fn_registries_pivot_node_substrate(p_code text)
  RETURNS TABLE(node_code text, source_object text, source_location text, source_model text, record_count int, has_pivot boolean)
  LANGUAGE sql STABLE AS $FN$
    SELECT m.code, split_part(m.source_location,':',2), m.source_location, m.source_model, m.record_count,
           EXISTS(SELECT 1 FROM pivot_definitions pd WHERE pd.source_object = split_part(m.source_location,':',2))
    FROM meta_catalog m WHERE m.code = p_code; $FN$;

CREATE TABLE registry_pin (
  id serial PRIMARY KEY, object_ref text NOT NULL, object_kind text, surface_ref text NOT NULL,
  pinned_by text NOT NULL, scope text NOT NULL DEFAULT 'user', scope_ref text, reason text,
  priority int DEFAULT 0, created_at timestamptz DEFAULT now(), active boolean DEFAULT true,
  CONSTRAINT registry_pin_scope_ck CHECK (scope IN ('global','user','role','team')) );

-- verification (results captured) ...
ROLLBACK;
-- post: views leftover=0 ; tables leftover=0 ; fn leftover=0 ; idle in transaction=0

Branch D captured results

naive-JOIN v_count_integrity : rows=172  (FAN-OUT BUG) ; invariant sum_counted=3917005 sum_phantom=110102
corrected   v_count_integrity: rows=160  pivot_backed=13 failed=3 unverified=5
            invariant: sum_counted=1954686 sum_actual=1917903 sum_orphan=0 sum_phantom=36798 sum_unreg=15
v_count_drift: 3 rows (CAT-023 +36652 phantom_side ; CAT-006 +146 phantom_side ; CAT-007 -15 orphan_side)
v_registries_pivot_tree: rows=37 with_children=0 roots=37     (parent_code NULL ⇒ flat)
v_living_lists: pivot_backed=13 pivot_missing=147 (naive mapping; underestimate for File-model rows)
v_registry_label_grouping_required: exceed_50=27 of 160
fn_substrate('CAT-023') => birth_registry / Directus:birth_registry / A / 980378 / has_pivot=t
fn_substrate('CAT-006') => dot/bin/ / File:dot/bin/ / B / 309 / has_pivot=f   (mapping gap)
registry_pin: 1 row, scope=global
meta_rollup_record_excluded=517 (=307+168+42) ; system_orphan_total=161

E5. Live anti-pattern evidence (Branch I)

GET /api/registry/health (200) :
  birth_registry  noi_chua=985434 noi_sinh=0   gap=985434 status=ORPHAN
  dot_tools       noi_chua=309    noi_sinh=592 gap=-283   status=PHANTOM   (4th DOT count)
  system_issues   noi_chua=180087 noi_sinh=180217 gap=-130 status=PHANTOM
  totals: {khop:11, orphan:15, phantom:10, totalGap:986215}
GET /api/registry/counts (200) : {"total":1688702}     (5th-order disagreement)
GET /api/registries-pivot/health : 404
build bundle .output/server contains: totalGap, orphan_count||0, orphan_count>0 ?

E6. Pivot wiring map (37 defs; ALL parent_code NULL)

registry_group families: cấu_trúc(PIV-001/002/009) · công_cụ(PIV-003/007/008/011) · cross-table(PIV-101–106) · l2-drill(PIV-201–206 = meta_catalog by composition_level) · giám_sát(PIV-014/207) · quy_trình(PIV-004/005/006/012/013) · dữ_liệu(PIV-010) · default(PIV-015–021) · matrix_l1/l2(MTX-*) · test(MTX-TEST inactive, PIV-020 inactive). pivot_query('PIV-104') group key = category ⇒ PIV-104 = dot_tools⋅category = child layer of PIV-007.

E7. Mutation guard

Entry == exit for all structural tables (doc 13 / final response). Read-only role for E1–E3/E5–E7; E4 wrapped BEGIN..ROLLBACK, post-checks all 0; no COMMIT issued in the session.