---

title: 05 — Missing Pivot Bundle (Branch C) date: 2026-05-31 gate: RG3 / macro M3. Rehearsed (temp-clone + real INSERT); commit DEFERRED.

05 — Missing Pivot Bundle (Branch C)

A. Pivot-engine capability (verified from pivot_query source)

• allowed_ops = = != > < >= <= in not_in is_null is_not_null like — no not_like, no column-vs-column comparison.
• allowed_funcs = count sum avg min max — sum IS supported.
• group/metric DSL: {"filters":[{op,field,value}]}, {"groups":[{field,alias}]}, {"metrics":[{func|op,field,alias}]}. → Consequence: a pivot whose definition needs not_like (drop %_total) or record<>actual (column-vs-column) cannot be expressed over a base table; it must source a committed view.

B. The bundle (each row: source · dimension · metric · owner · reason · expressibility · is_active)

code	source_object	dimension	metric	owner	reason	DSL?	gate
PIV-500	v_registry_leaf_set (view)	none	sum(record_count)	COUNCIL/SIV	one explained grand total (collapses the 5 disagreeing totals)	view-backed (needs not_like)	RG3
PIV-301	meta_catalog	none	sum(orphan_count) filter composition_level='meta'	SIV	orphan grand total (the 161)	native	RG3
PIV-302	v_count_drift (view)	none	count(*)	COUNCIL	phantom total — placeholder until RG4 phantom law	view-backed	RG3+RG4
PIV-303	v_count_drift (view)	drift_side	count(*)	SIV	drift total by side	view-backed (record<>actual)	RG3
PIV-311	entity_labels	label_code	count(*)	NRM-SYS	makes 722,803 labels pivot-counted (today uncounted)	native	RG3
PIV-321	registry_pin	scope	count(*)	DOT/GOV	pinned-count pivot-backed (after pin table born)	native	RG3+RG5
Coverage targets: PIV-500/301/303 + per-leaf pivot_code (doc 06) address the 139 PIVOT_MISSING;
the 5 unmeasured (CAT-1006..1010) get counts via dedicated pivots or scan onboarding.

C. Rehearsal evidence (this session, BEGIN..ROLLBACK)

• Temp-clone (CREATE TEMP TABLE pd_clone (LIKE pivot_definitions INCLUDING ALL)): all 6 rows accepted (schema/constraints valid) — fires no triggers. Proves pivot_definitions can accept the bundle.
• Real INSERT (PIV-311, is_active=false): inserted into live pivot_definitions inside a tx → fired trg_after_pivot_definitions_change → full refresh_meta_catalog_from_pivot() + refresh_pivot_results() (recomputes 35 active pivots) + fn_birth_registry_auto (birth row) → completed < 45 s, count 37→38 → ROLLBACK → 37, idle_tx 0. Proves the real trigger path works.

D. Trigger cost finding (operational)

Any INSERT/UPDATE on pivot_definitions fires a FOR EACH STATEMENT full refresh of all active pivots (incl. birth count 985k + matrices). For the bundle commit (M3): insert all rows with is_active=false in one statement (one refresh), then activate after a single controlled refresh off-peak — or temporarily disable user triggers if owner. Do not insert row-by-row (N refreshes).

E. Gated commit SQL (DO NOT run until RG3) — abbreviated

-- view-backed totals require the six-object layer (M2) committed first
INSERT INTO pivot_definitions (code,name,source_object,filter_spec,group_spec,metric_spec,registry_group,composition_level,is_active,display_order) VALUES
 ('PIV-500','Grand total (leaf-set)','v_registry_leaf_set','{"filters":[]}','{"groups":[]}','{"metrics":[{"func":"sum","alias":"total","field":"record_count"}]}','grand-total','meta',false,500),
 ('PIV-301','Orphan total','meta_catalog','{"filters":[{"op":"=","field":"composition_level","value":"meta"}]}','{"groups":[]}','{"metrics":[{"func":"sum","alias":"total","field":"orphan_count"}]}','integrity','meta',false,301),
 ('PIV-303','Drift total by side','v_count_drift','{"filters":[]}','{"groups":[{"field":"drift_side","alias":"side"}]}','{"metrics":[{"op":"count"}]}','integrity','meta',false,303),
 ('PIV-311','Label by facet','entity_labels','{"filters":[]}','{"groups":[{"field":"label_code","alias":"label"}]}','{"metrics":[{"func":"count","alias":"total","field":"*"}]}','label','material',false,311);
-- then a single controlled refresh, verify pivot_results, then UPDATE is_active=true.

PIV-302 (phantom) waits on RG4; PIV-321 (pin) waits on RG5 (pin table). No COMMIT without RG3.

F. Schema gaps recorded

1. pivot_query lacks not_like and column-vs-column ops → grand/drift/phantom totals are view-backed by design (acceptable; or extend the engine — a larger change, not recommended now).
2. meta_catalog.pivot_code mapping column still needed for per-leaf backing (doc 06).