---

title: 05 — Dynamic Drilldown Wiring (Branch D) date: 2026-05-31 status: concrete + rehearsal-backed; COMMIT DEFERRED (own gate)

05 — Dynamic Drilldown Wiring (Branch D)

Problem: pivot_definitions.parent_code is NULL on all 37 pivots → the drill tree is flat (37 roots / 0 parents). This branch designs the parent_code graph, proves it flat→hierarchical with a recursive walk, proves no dangling parent and that a leaf exposes its DB substrate, and defers the COMMIT.

Schema facts (live)

• pivot_definitions.parent_code exists, all NULL → wiring is an UPDATE, not an ALTER.
• meta_catalog has no parent_code and no pivot_code column (28 cols inspected). The leaf→pivot link is the existing join meta_catalog.registry_collection = pivot_definitions.source_object (already used by the six objects). A new meta_catalog.pivot_code column is an optional explicit overlay, not required — the registry_collection=source_object join is the data-driven link today.

Proposed parent_code graph (13 edges, rooted on existing pivots)

catalog spine:   PIV-101, PIV-102, PIV-106  → PIV-001   (catalog cross-tabs under catalog total)
L2 drill:        PIV-201..PIV-206           → PIV-101   (by-layer → per-layer-by-species)
per-registry:    PIV-104 → PIV-007 (dot)    PIV-105 → PIV-009 (collections)   PIV-103 → PIV-016 (species)
matrix:          MTX-L2-ATOM                → MTX-L1-OVERVIEW

When PIV-500 is later committed it becomes the super-root and all *-Total pivots become its children (Macro 3 picture). For the committable-now graph (UPDATE existing only), roots stay on existing pivots — no invented nodes.

PROOF — BEGIN..ROLLBACK rehearsal (GREEN)

D0-D5 use a TEMP overlay (no trigger). D6 does ONE real single-statement UPDATE.

D0  PRE                : 37 pivots, parent_code non-null = 0   (flat)
D1  dangling           : child_missing 0, parent_missing 0     (every edge endpoint exists)
D2  shape              : 37 nodes → 24 roots / 13 children / 6 parents   (flat → hierarchical)
D3  recursive walk     : reachable 37 / 37, max_depth 3, cycle-free
D4  count>1 next layer : PIV-001 → {PIV-101, PIV-102, PIV-106};  PIV-101 → {PIV-201..PIV-206}
D5  leaf → substrate   : PIV-007 → source dot_tools → CAT-006 → db_substrate "File:dot/bin/" (309/163)
D5  POST               : parent_code non-null = 0 (temp objects gone)
D6  REAL UPDATE        : UPDATE 13 → non_null_in_tx 13, dangling 0 → ROLLBACK → non_null_post 0
D6  POST               : idle_in_tx 0

• Flat→hierarchical proven (24 roots/6 parents/13 children).
• No dangling parent (D1 + D6 dangling 0).
• count>1 exposes next layer (D4 — a node with children drills down).
• leaf exposes substrate (D5 — fn_registries_pivot_node_substrate-style resolution; for model-B the substrate is a file path File:dot/bin/, not a PG table — the leaf panel must render a file/relationship reference, not assume a table).
• Committable path proven (D6 real single-statement UPDATE fires one refresh, rolls back clean — this is exactly the gated commit action).

Recursive drill contract (for UI / Nuxt render shell)

1. A node's drill = filter the pivot to the chosen group value → child pivot rows.
2. count > 1 ⇒ backend emits the next inner layer (variable depth).
3. Leaf (a single registry / CAT code) ⇒ fn_registries_pivot_node_substrate(code) → {registry_collection, source_location, record_count, actual_count, pivot_backed, pivot_count}. Model-A → PG table; model-B → file path; relationship leaves → iu_relation.
4. No hierarchy in the frontend — is_root/has_children come from v_registries_pivot_tree, driven by parent_code in PG (Đ28).

Commit procedure (ONLY after its gate — exact, ONE statement)

BEGIN;
SET LOCAL lock_timeout='3s'; SET LOCAL statement_timeout='90s'; SET LOCAL idle_in_transaction_session_timeout='120s';
UPDATE pivot_definitions p SET parent_code = e.parent
FROM (VALUES
 ('PIV-101','PIV-001'),('PIV-102','PIV-001'),('PIV-106','PIV-001'),
 ('PIV-201','PIV-101'),('PIV-202','PIV-101'),('PIV-203','PIV-101'),('PIV-204','PIV-101'),('PIV-205','PIV-101'),('PIV-206','PIV-101'),
 ('PIV-104','PIV-007'),('PIV-105','PIV-009'),('PIV-103','PIV-016'),
 ('MTX-L2-ATOM','MTX-L1-OVERVIEW')) AS e(child,parent)
WHERE p.code = e.child;          -- ONE statement → ONE refresh
-- verify: non-null 13, dangling 0; then COMMIT;
COMMIT;

Rollback: UPDATE pivot_definitions SET parent_code = NULL WHERE code IN (…13 children…);

Status: drilldown CONCRETE + rehearsal-backed. COMMIT DEFERRED.