title: 14 — SQL Appendix / Evidence date: 2026-05-31 channel_read: query_pg (read-only role context_pack_readonly) · ssh read channel_apply: ssh contabo → docker exec -i postgres psql -U workflow_admin -d directus (BEGIN..ROLLBACK only)

14 — SQL Appendix / Evidence

All numbers in this package trace to the queries below, run live 2026-05-31.

E1. Gate 0 — infra & role

list_docker → container postgres (postgres:16) Up 6 weeks (healthy); incomex-directus, incomex-nuxt (nuxt-ssr-local:d28gmr-…), incomex-nginx, incomex-qdrant all up.
SELECT current_database(), current_user, version() → directus, context_pack_readonly, PostgreSQL 16.13.
Idle-in-transaction scan → only the read session itself; 0 stale tx.
Apply channel test: ssh contabo "docker exec postgres psql -U workflow_admin -d directus -tAc 'select current_user'" → workflow_admin ✅.

E2. Entry counts (one UNION query)

collection_registry 168 · dot_iu_command_catalog 54 · dot_tools 309 · entity_labels 722,803 · entity_species 42 · information_unit 219 · iu_relation 60 · label_rules 38 · meta_catalog 169 · pivot_definitions 37 · pivot_results 126 · species_collection_map 164 · table_registry 21 · taxonomy 58 · taxonomy_facets 10 · v_registry_counts 169 (relkind r = TABLE). New objects (v_count_integrity, v_count_drift, v_registry_leaf_set, v_registries_pivot_tree, v_living_lists, fn_*substrate, registry_pin) → none exist (clean baseline; prior rehearsals left 0 residue).

E3. Composition-level distribution (proves the 9 rollup rows)

atom 77 (et_all=1 → CAT-ALL)   building 1 (_total)   compound 34 (_total)
material 2 (_total)   meta 3 (_total, orphans=161)   molecule 51 (_total)   product 1 (_total)

→ leaf = 169 − (3 meta + 5 other _total + 1 all) = 160.

E4. Leaf invariant (live)

WITH leaf AS (SELECT * FROM meta_catalog
  WHERE composition_level <> 'meta' AND entity_type NOT LIKE '%_total' AND entity_type <> 'all')
SELECT count(*), SUM(record_count), SUM(actual_count),
       SUM(record_count)-SUM(actual_count) AS net_gap,
       count(*) FILTER (WHERE record_count IS DISTINCT FROM actual_count) AS drift,
       count(*) FILTER (WHERE actual_count IS NULL OR record_count IS NULL) AS unmeasured,
       SUM(orphan_count) AS leaf_orphans FROM leaf;
-- → 160 | 2,002,041 | 2,001,909 | +132 | 3 | 5 | 0

E5. The 3 drift rows + 5 unmeasured

CAT-006 DOT Tools      model B  File:dot/bin/        record 309    actual 163    gap +146  (scan 05-31 00:00)
CAT-007 Pages/Routes   model B  File:web/pages/      record 37     actual 52     gap  -15
CAT-023 So khai sinh   model A  Directus:birth_registry record 985,472 actual 985,471 gap +1 (scan 05-31 01:33)
unmeasured (record & actual NULL, source_model pg_table):
  CAT-1006 APR action types · CAT-1007 APR request types · CAT-1008 APR approvals ledger
  CAT-1009 Admin fallback log · CAT-1010 Law version verification log
meta rollups (actual_count NULL, carry 161 orphans): CAT-COL(20) · CAT-DOT(140) · CAT-SPE(1)
CAT-ALL: entity_type='all', composition_level='atom', record 1,719,958 / actual 2,003,754 (excluded from leaf)
scan recency: max(last_scan_date)=2026-05-31 01:35:02 · scanned_today=135 · null_actual=8 · null_record=5

E6. pivot_definitions (37; parent_code NULL ×37)

L1 totals PIV-001..021 (per source_object); cross-table PIV-101..106; L2-drill PIV-201..206 (source_object meta_catalog, by composition_level); matrices MTX-L1-OVERVIEW / MTX-L2-ATOM / MTX-TEST. parent_code NULL on all 37; 35 active (PIV-020 _uncategorized, MTX-TEST inactive); 22 distinct source_objects. pivot_results grand-total rows: group_values={}, metric_values={"count":N} (PIV-001→169, PIV-007→309, PIV-019→985,472). Functions present: pivot_count(text), pivot_query(text), pivot_matrix(...), refresh_meta_catalog_from_pivot(), refresh_pivot_results(), trigger trg_pivot_def_refresh.

E7. Pivot coverage of the leaf set

... count(*) FILTER (WHERE EXISTS (SELECT 1 FROM pivot_definitions p
       WHERE p.is_active AND p.source_object = leaf.registry_collection)) ...
-- leaf 160 | backed_by_registry_collection 21 | null_regcoll 5 | distinct_regcoll 155

Prior rehearsal keyed on split_part(source_location,':',2) → 13/160 (under-reports; File:dot/bin/→dot/bin/). This pack keys on registry_collection → 21/160 (correct table name). Improvement adopted.

E8. Label / threshold / pin machinery (live)

taxonomy_facets(10): has cardinality, max_labels_per_entity (labels-per-entity, NOT a display ceiling).
label_rules(38): facet_id, rule_type, condition jsonb, result_label, priority, **skip_wide_warning**, status.
taxonomy(58): hierarchical (parent_id, parent_facet, depth, scope[]).
species_collection_map(164): species_code, collection_name, is_primary, discriminator_field/value/operator/config.
kg_thresholds (TABLE): dimension, lower_is_better, threshold_green/yellow/red, is_active — green/yellow/red pattern (reusable for integrity status thresholds).
No pin/ghim/watch table exists → registry_pin is NEW.

E9. Event / issue registries (live)

event_type_registry 40 rows, domains iu(16)/mother(9)/piece(6)/staging(5)/system(4); columns event_domain, event_type, event_stream, delivery_lane, default_severity, description, active (PK composite). No count_integrity / phantom / orphan / label / pin / drift / pivot event types → all NEW.
system_issues (rich SoT): code,title,entity_type,entity_code,issue_type,severity,source,status, first/last_seen_at,occurrence_count,coalesce_key,reopen_count,evidence_snapshot,…. No registries-pivot source exists (only dot-layer-integrity-audit) → NEW source needed; idempotent via coalesce_key.

E10. Legacy endpoints (live, for Branch J)

GET /api/registry/health (200, cached 2026-05-31T01:39:55Z): per-collection gap = noi_chua − noi_sinh, status ORPHAN/PHANTOM/KHOP; totalGap: 986253 (khop 11/orphan 15/phantom 10). Drivers: birth_registry noi_sinh=0 → gap 985,472 ORPHAN (contradicts the real +1); dot_tools noi_sinh=592 (a 4th DOT count vs 309 table / 163 files / 309 pivot); many -1 sentinels mislabeled ORPHAN. Đ28 violation.
GET /api/registry/counts (200): {"total":1721334} — a 5th disagreeing grand total.
Routes: /knowledge/registries 200 · /knowledge/pivot 200 · /knowledge/registries-pivot 200 (1.73 MB).

E11. REHEARSAL 1 — view/function layer (BEGIN..ROLLBACK) — raw output

Command: cat 001..006 900 | ssh contabo "docker exec -i postgres psql -U workflow_admin -d directus -v ON_ERROR_STOP=1 …" wrapped in BEGIN; SET LOCAL lock_timeout=3s/statement_timeout=30s/idle_in_transaction_session_timeout=60s; … ROLLBACK;.

==PRE==  v_* views: (0 rows)   pre_fn: 0
==VERIFY-IN-TX==
 V1 leaf_rows = 160
 V2 invariant: 160 | 2002041 | 2001909 | net_gap 132 | drift 3 | unverified 5 | leaf_orphans 0
 V3 drift_closure: leaf_net_gap 132 == sum_drift_gap 132
 V4 coverage: 160 | pivot_backed 21 | pivot_missing 139
 V5 verdict: FAILED | ok 152 | failed 3 | unverified 5
 V6 classification: balanced 152 · unmeasured 5 · model_a_surplus_recheck 1 · model_b_unregistered_candidate 1 · model_b_phantom_candidate 1
 V7 tree: nodes 37 | roots 37 | parents 0
 V8 fn CAT-006: 309 | 163 | pivot_backed t | pivot_count 309
 V8 fn CAT-023: 985472 | 985471 | pivot_backed t | pivot_count 985472
 V9 living_lists: scan_actual 155 (backed 21) · unmeasured 5 (backed 0)
==ROLLBACK==
==POST==  v_* views: (0 rows)   post_fn: 0   idle_in_tx: 0

Verdict: GREEN. Compiled, invariant reproduced, drift closure proven, rolled back clean, zero net mutation.

E12. REHEARSAL 2 — additive proposals (BEGIN..ROLLBACK) — raw output

==PRE2==  registry_pin/display_policy: (0 rows)   idle_pre 0
 pin_rows: 2 (active 2)
 pin_check: CHECK ((scope = ANY (ARRAY['global','user','role','team'])))
 threshold_demo: leaves_evaluated 160 | exceed_resolved_ceiling 28   (no literal 50 in query; default from __default__ row)
 wired_tree: nodes 37 | roots_after_wiring 24 | distinct_parents 6 | dangling_edges 0
 piv500_candidates: leaf_sum_record 2002041 | leaf_sum_actual 2001909 | cat_all_record 1719958 | cat_all_actual 2003754
==POST2==  registry_pin/display_policy: (0 rows)   idle_post 0

Verdict: GREEN. New table + threshold table + drill-wiring overlay all valid; zero net mutation.

E13. Mutation proof

Two transactions opened on workflow_admin, both ended with ROLLBACK. PRE==POST on object existence (0 → 0). idle in transaction = 0 before and after each. No COMMIT issued. No base table altered. No row inserted/updated/deleted persistently. No Directus/Qdrant/Nuxt change.