---

title: 05 — Dynamic Drilldown Wiring Plan date: 2026-05-31 gate: P-DRILL (after RG1+RG2/P1). Design + overlay-rehearsed; commit DEFERRED.

05 — Dynamic Drilldown Wiring Plan

Problem (verified live)

• pivot_definitions.parent_code is NULL on all 37 pivots → v_registries_pivot_tree is flat (37 roots, 0 children).
• The drilldown contract requires: a node with count > 1 exposes a backend-declared child layer; a singular leaf resolves to the DB/relationship substrate.
• Hierarchy must live in PG data, never in Nuxt (Đ28).

Two data structures carry the drill

1. pivot_definitions.parent_code (exists, text) — the pivot→pivot graph (which pivot is a breakdown of which). This is the aggregate drill (Total → by-Lớp → by-loài → matrix).
2. NEW meta_catalog.pivot_code (proposed column) — maps each registry/leaf row to the pivot that counts it. Needed because source_location is an unreliable key (File:dot/bin/) and registry_collection only covers 21/160. This is the leaf drill (registry row → its pivot → its DB substrate). EXTEND beyond parent_code.

Proposed parent-child pivot graph (rehearsed valid: 13 edges, 0 dangling)

PIV-500  Grand total (NEW, RG3/P5)          [root]
  ├─ PIV-001  meta_catalog Total
  │    ├─ PIV-101  Danh mục theo Lớp (by composition_level)
  │    │    ├─ PIV-201  L2 Atom: theo loài
  │    │    ├─ PIV-202  L2 Molecule: theo loài
  │    │    ├─ PIV-203  L2 Compound: theo loài
  │    │    ├─ PIV-204  L2 Material: theo loài
  │    │    ├─ PIV-205  L2 Product: theo loài
  │    │    └─ PIV-206  L2 Building: theo loài
  │    ├─ PIV-102  Danh mục theo Loại quản lý
  │    └─ PIV-106  Danh mục: Lớp × Loại quản lý
  ├─ PIV-007  DOT Tools Total ── PIV-104  DOT Tools theo Nhóm
  ├─ PIV-009  Collections Total ── PIV-105  Collections theo Phân loại
  ├─ PIV-016  Loài Total ── PIV-103  Loài theo Lớp
  └─ MTX-L1-OVERVIEW ── MTX-L2-ATOM

Overlay rehearsal (doc 14 E12): applying these 13 edges collapses 37 flat roots → 24 roots / 6 parents / 0 dangling edges. PIV-500 (gated) becomes the single super-root linking the L1 totals.

Mapping source (no-hardcode proof)

The graph is derived from existing data columns, not invented per node:

• registry_group tier: cross-table (PIV-101..106) sits under its L1 total; l2-drill (PIV-201..206) sits under the by-level breakdown; matrix_l2 under matrix_l1.
• source_object: a breakdown's parent shares its source_object total (e.g. PIV-104 dot_tools → PIV-007 dot_tools; PIV-105 collection_registry → PIV-009).
• composition_level / display_order: deterministic ordering within a tier.

A generator function fn_propose_pivot_parents() can emit these edges from the columns above; the council reviews the generated edge list and the wiring is an UPDATE of data, never a frontend if level===2 branch. No CAT/PIV code list is hardcoded in app.

Drill contract semantics (rendered from data only)

node state	data signal	UI behaviour
count > 1, child pivot exists	has_children=true (parent_code points here)	expandable → child layer = the child pivot's rows
count > 1, no child pivot	has_children=false AND list_count>1	render rows from v_living_lists; if no pivot → PIVOT_MISSING badge
singular leaf	leaf registry row	fn_registries_pivot_node_substrate(code) → DB substrate panel

Missing-mapping registry (honesty, not silent failure)

Until meta_catalog.pivot_code is populated, 139/160 leaf rows are PIVOT_MISSING and 37/37 pivots are roots. These are surfaced (V4 coverage, V7 tree, v_living_lists.pivot_backed), not hidden. A v_pivot_mapping_gap view (proposed) lists every leaf row with no pivot_code and every pivot with no parent — the work-list for P5/P-DRILL.

Gated migration/commit SQL (DO NOT run until P-DRILL approved)

-- (a) add the leaf mapping column (additive, nullable)
ALTER TABLE meta_catalog ADD COLUMN IF NOT EXISTS pivot_code text;     -- FK-soft to pivot_definitions.code
-- (b) wire the aggregate graph (data UPDATE; fires trg_pivot_def_refresh → run off-peak)
UPDATE pivot_definitions SET parent_code = v.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 v(child,parent)
WHERE pivot_definitions.code = v.child;
-- (c) backfill leaf→pivot mapping where a unique active total pivot matches registry_collection
UPDATE meta_catalog m SET pivot_code = (
  SELECT pd.code FROM pivot_definitions pd
  WHERE pd.is_active AND pd.source_object = m.registry_collection
  ORDER BY pd.display_order NULLS LAST LIMIT 1)
WHERE m.pivot_code IS NULL;

Rehearse (b)/(c) in BEGIN..ROLLBACK first; (b) fires the refresh trigger. Frontend never encodes any of this — it reads v_registries_pivot_tree + pivot_code.