---

title: 09 — SQL Appendix & Evidence date: 2026-05-31

09 — SQL Appendix & Evidence

Local SQL artifacts (/Users/nmhuyen/rp_gateway/sql/)

Commit-ready six-object pack (canonical) + rehearsal harnesses. SHA256:

001_create_v_registry_leaf_set.sql   c3ea153029d18186cb9bfc2ecb418ef90ce3a347f6e4b1350ea5ca2692664d45
002_create_v_count_integrity.sql     ee18b46aaa13d99221406772d95f214416f045114371a32362fd03757b4df945
003_create_v_count_drift.sql         4fac986af5de67039b9899f876e1e620b554d2bc8ceed1d87aacfaccbfcdca16
004_create_v_living_lists.sql        fc511a3e4bfb2291e89f5396b92872ed416215607441eff1356dffc00bfb41c6
005_create_v_registries_pivot_tree.sql 1a03d832cc30b36de30129ab7bd52eb9b94e698f4c3e611928706abf9e0d9b74
006_create_fn_node_substrate.sql     b834d036b96d96bf85a9529bdf42194885ff895296d54157317322a72276cab6
900_verify.sql                       0505ccf4514c494e3fa72be9ff6f367da9974a724917185f7542da1f08aa4f92
999_rollback.sql                     dddb2b40afb803ca6c154f8bdc125e98b0a308f904d8bfa61ef6fe203b66808b
concatenated 001..006                f6d44bc9e87dfeec0d132136bd2a912ae13fc7f5f25c497b21f262b9dd0c945f
rehearse_campaign.sql                9cddc2b59b3a7e68e2d1dc49f9e26f88b9a90df401f90f2a02a1659fe19fbe16
branch_c_pivot_bundle.sql            38440e2de9d266fdab71dfeae15720f93a64da8a83d904ee59008bd82cb838f7
branch_d_drilldown.sql               7609f38d63b40e62eb28d85b7186c6cfccfa11278e3f990a83b1567e2b1c2079

Local SQL dir is cleaned between sessions; recreate from this package + memory.

Apply channel (confirmed)

ssh contabo → docker exec -i postgres psql -v ON_ERROR_STOP=1 -U workflow_admin -d directus < <file>. All rehearsals used SET LOCAL lock_timeout/statement_timeout/ idle_in_transaction_session_timeout and BEGIN..ROLLBACK. Read channel = query_pg (AST-validated, READ ONLY, 5 s, LIMIT 500). query_pg AST-blocks DDL → DDL only via ssh.

Before / after (zero net mutation)

object	entry	exit
meta_catalog	169	169
pivot_definitions	37	37
pivot_results	126	126
pivot_definitions.parent_code non-null	0	0
six objects present	0	0
fn present	0	0
idle_in_transaction	0	0

Rehearsal logs (verbatim highlights)

Six-object (rehearse_campaign.sql §1) — GREEN

S1 PRE six_pre=0
CREATE VIEW ×5, CREATE FUNCTION ×1
V1 leaf=160
V2 invariant=160 / rec 2007256 / act 2001909 / net_gap 5347 / drift 3 / unver 5 / orph 0
V3 closure: leaf_gap 5347 == drift_gap 5347
V4 coverage: 160 / 21 backed / 139 missing
V5 verdict: FAILED (152 ok / 3 failed / 5 unver)
V7 tree: 37 / 37 roots / 0 parents
V8 fn006: 309 / 163 / t / 309
ROLLBACK → six_post=0, fn_post=0

Additive (§2) — GREEN

registry_pin: INSERT 2, active 2
display_policy: 160 leaves, 28 exceed ceiling
wired_tree: 37 nodes → 24 roots / 6 parents / 0 dangling
ROLLBACK → pin_threshold_post=0

Pivot temp-clone (§3) + real insert (§4) — GREEN

§3 pd_clone: 6 rows accepted (PIV-500/301/302/303/311/321)
§4 PRE pd_pre=37 → INSERT 0 1 → pd_in_tx=38 → ROLLBACK → pd_post=37, idle_in_tx=0

Branch C bundle (branch_c_pivot_bundle.sql) — GREEN

C-A sim: PIV-500 ALL/2007256 · PIV-301 ALL/161 · PIV-302 1 · PIV-303 3 · PIV-311 23
C-B real pivot_query (INSERT 0 5, pd_in_tx=42):
   PIV-500 {bucket:ALL}{total:2007261}  PIV-301 {bucket:ALL}{total:161}
   PIV-302 {count:1}  PIV-303 {count:3}  PIV-311 23 groups Σ726850
ROLLBACK → pd_post=37, idle_in_tx=0

Branch D drilldown (branch_d_drilldown.sql) — GREEN

D0 37/parent_null=0  D1 dangling 0/0  D2 24 roots/13 children/6 parents
D3 walk 37/37 max_depth 3 cycle-free  D4 PIV-001→{101,102,106}; PIV-101→{201..206}
D5 leaf PIV-007→dot_tools→CAT-006→File:dot/bin/ (309/163)
D6 UPDATE 13, dangling 0 → ROLLBACK → non_null_post 0, idle_in_tx 0

Approval scan evidence (Branch A)

approval_requests: 25 matched; max id 234; only human review id 211 'president'
  (2026-04-19, S178-Fix18 admin_fallback_log — OUT OF SCOPE); 16 registry rows = machine birth_orphan
apr_approvals: 0 rows in scope/window
os_proposal_approvals: 0 rows total (empty — no sovereign sign-off)
workflow_change_requests: 0 rows matching
KB: no outcome/approval doc
→ NO_APPROVAL_FOUND

Routes (HTTP)

/knowledge/registries 200 (4.3 s / 603 KB legacy; one transient 000 then 200×3) · /knowledge/pivot 200 · /ui-preview/registries-pivot/v1/ 200.