---

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

15 — SQL Appendix / Evidence

E1. Gate 0

current_database/current_user → directus / context_pack_readonly; idle_in_tx 0; six views 0 / fn 0 / pin+threshold 0 (no commit occurred). Routes: registries 200 · pivot 200 · registries-pivot 200 · ui-preview/registries-pivot/v1/ 404 (pre-deploy). Apply channel workflow_admin confirmed.

E2. Baseline counts (unchanged)

meta_catalog 169 · pivot_definitions 37 · pivot_results 126 · collection_registry 168 · table_registry 21 · entity_species 42 · species_collection_map 164 · taxonomy_facets 10 · taxonomy 58 · label_rules 38 · entity_labels 722,803 · dot_tools 309 · dot_iu_command_catalog 54 · information_unit 219 · iu_relation 60 · v_registry_counts 169.

E3. Approval spine (Branch A)

approval_requests 211 · approved 2 · reviewed 201 · latest_created 2026-04-22. 16 registries-matching rows ALL 2026-03-28 auto birth_orphan/accuracy_drift, reviewed_by orchestrator-s142b/auto-apply-function/ system_auto_expire (machine). e.g. APR-0165 "CAT-023 count drift record=21049 actual=21056" expired. → NO human/council approval. COMMIT FORBIDDEN.

E4. Trigger inspection (pivot_definitions)

3 enabled triggers: trg_after_pivot_definitions_change AFTER I/U/D STATEMENT → trg_pivot_def_refresh() = PERFORM refresh_meta_catalog_from_pivot(); PERFORM refresh_pivot_results(); (refresh_pivot_results loops ALL active pivots calling pivot_query, upsert pivot_results) · trg_birth_pivot_definitions AFTER INSERT ROW → fn_birth_registry_auto('code') · trg_matrix_config_changed WHEN matrix_spec NOT NULL. → any insert fires a full refresh.

E5. Pivot engine capability (pivot_query source)

allowed_ops = != > < >= <= in not_in is_null is_not_null like (no not_like, no col-vs-col); allowed_funcs count sum avg min max. metric shape {func|op,field,alias}. → PIV-500/drift/phantom view-backed; PIV-31x/orphan native.

E6. Live drift rows

CAT-006 dot_tools B 309/163 +146 (stable) · CAT-007 ui_pages B 37/52 −15 (stable) · CAT-023 birth A 985,488/985,471 +17 (last_scan 02:13; fluctuated +1→+16→+17 across session = live-write race).

E7. REHEARSAL — rehearse_campaign.sql (4 sections, one ssh pipe, -v ON_ERROR_STOP=1, SET LOCAL timeouts) — raw

S1 SIX-OBJECT: PRE 0 → V1 leaf 160 · V2 160|2,002,057|2,001,909|net_gap 148|drift 3|unver 5|orph 0 ·
   V3 closure 148==148 · V4 21/139 · V5 FAILED(152/3/5) · V6 balanced152/unmeasured5/A-surplus1/B-phantom1/B-unreg1 ·
   V7 37/37/0 · V8 CAT-006 309/163/t/309 → POST 0/0 (clean)
S2 ADDITIVE: pin 2/2 · threshold 160 leaves/28 exceed · wired_tree 37→24 roots/6 parents/0 dangling → POST 0
S3 TEMP-CLONE: pivot_clone_accept 6 (PIV-500/301/302/303/311/321 all schema-valid)
S4 REAL INSERT (PIV-311 is_active=false): PRE 37 → in_tx 38 (full refresh fired, <45s) → POST 37 · idle_in_tx 0

Verdict: GREEN ×4, zero net mutation.

E8. Real source scan (/opt/incomex/docker/nuxt-repo/web, read-only ssh grep)

health.get.ts:123 totalGap=reduce(+Math.abs(gap)); :117 sort abs; index.vue:312 code:'CAT-017', :595 branch, :271 orphan_count:hd.totalGap, :161-162 reduce; [entityType]/index.vue:80-81 reduce; raw-counts.get.ts:58 reduce; health/index.vue noi_chua/noi_sinh cols. localStorage pins: none. API route inventory captured (doc 10).

E9. Static preview deploy

tar czf - registries-pivot | ssh contabo "tar xzf - -C /opt/incomex/docker/nginx/static/ui-preview" → files owner 501; GET /ui-preview/registries-pivot/v1/ 200 (11,337 B), mock-data.json 200; title + DRAFT banner verified. Rollback = rm -rf …/ui-preview/registries-pivot.

E10. Mutation proof

4 workflow_admin transactions, all ROLLBACK. PRE==POST object existence (0→0; pivot_definitions 37→37); idle_in_tx 0 throughout. No COMMIT, no base-table DDL, no row persisted, no Directus/Qdrant change. Only outward change = static files under /ui-preview/ (no DB).