---

title: 06 — Drilldown Parent/Pivot Mapping (Branch D) date: 2026-05-31 gate: macro M4 (P-DRILL). Overlay-rehearsed; commit DEFERRED.

06 — Drilldown Parent/Pivot Mapping (Branch D)

Problem (re-verified)

pivot_definitions.parent_code NULL on all 37 → v_registries_pivot_tree flat (37 roots/0 children). Drill contract needs: count>1 → backend-declared child layer; singular leaf → DB substrate. No frontend hierarchy.

Two data structures (no-hardcode source)

1. parent_code (existing column; UPDATE, not ALTER) — the pivot→pivot graph.
2. NEW meta_catalog.pivot_code — maps each leaf registry row to its counting pivot (since registry_collection covers only 21/160; source_location is unreliable: File:dot/bin/).

Proposed graph (overlay-rehearsed GREEN this session)

13 edges → applying them turns 37 flat roots into 24 roots / 6 parents / 0 dangling:

PIV-500 (root, gated)
 ├ PIV-001 ─ PIV-101 ─ {PIV-201..206}   (meta_catalog: total → by-Lớp → by-loài per level)
 │         ├ PIV-102   ├ PIV-106
 ├ PIV-007 ─ PIV-104   ├ PIV-009 ─ PIV-105   ├ PIV-016 ─ PIV-103
 └ MTX-L1-OVERVIEW ─ MTX-L2-ATOM

Edges derived from existing columns (registry_group tier, shared source_object, composition_level, display_order) via a generator fn_propose_pivot_parents(); council reviews the generated list; wiring is an UPDATE of data, never a frontend if level===N.

Drill semantics (rendered from data only)

node state	signal	behaviour
count>1, child pivot	has_children=true	expand → child pivot rows
count>1, no child pivot	has_children=false, list_count>1	rows from v_living_lists; PIVOT_MISSING badge if no pivot
singular leaf	leaf row	fn_registries_pivot_node_substrate(code) → substrate panel

Gated migration (DO NOT run until M4; UPDATE fires the heavy refresh trigger — off-peak, one statement)

ALTER TABLE meta_catalog ADD COLUMN IF NOT EXISTS pivot_code text;
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')) v(child,parent)
WHERE pivot_definitions.code=v.child;
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;

Trigger note (doc 05 D): the parent_code UPDATE fires trg_after_pivot_definitions_change → full refresh; do it as one statement, off-peak. Frontend reads v_registries_pivot_tree + pivot_code.

Missing-mapping registry (honesty)

Until wired: 139/160 PIVOT_MISSING, 37/37 roots. A v_pivot_mapping_gap view (proposed) = the M3/M4 work-list.