---

title: 04 — Missing-Pivot Bundle (Branch C) date: 2026-05-31 status: concrete + REAL-engine proven; COMMIT DEFERRED (own gate)

04 — Missing-Pivot Bundle (Branch C)

Pivot coverage is 21/160 leaves (139 PIVOT_MISSING + 5 unmeasured). This branch designs the system/integrity pivots that erase the blind-count zones, proves them against the real pivot_query() engine under BEGIN..ROLLBACK, and defers the COMMIT.

Coverage decomposition (live)

source_model	backed	not-backed	note
A	18	132	each a distinct registry_collection; bulk of PIVOT_MISSING
B	3	1	model-B file/page registries
pg_table	0	5	CAT-1006..1010, registry_collection NULL → also the 5 unmeasured (NULL counts)
taxonomy	0	1
total	21	139

The 5 unmeasured (pg_table, NULL counts): CAT-1006 apr_action_types, CAT-1007 apr_request_types, CAT-1008 apr_approvals, CAT-1009 admin_fallback_log, CAT-1010 law_version_verification_log — physical tables with no scan + no pivot.

ENGINE CONSTRAINT (read from pivot_query source — load-bearing)

allowed_ops   = [=, !=, >, <, >=, <=, in, not_in, is_null, is_not_null, like]   (NO not_like; NO column-vs-column — filter value is always a %L literal)
allowed_funcs = [count, sum, avg, min, max]

Two hard consequences:

1. The leaf predicate entity_type NOT LIKE '%_total' is not expressible → any pivot over the leaf set must be VIEW-BACKED (source v_registry_leaf_set, which already encodes the NOT LIKE). source_object is injected via format('%I', …) so a view name works as a pivot source.
2. The no-group branch hard-codes SELECT count(*) and ignores metric_spec. A "sum, no groups" pivot returns a row count, not the sum. A true grand-total SUM therefore needs the grouped branch over a constant-bucket column (GROUP BY bucket). (This corrects the prior temp-clone design, which only validated row INSERT.)

The bundle (corrected, engine-faithful)

Helper view (rehearsed): v_count_rollup_src — a constant-bucket UNION that forces the grouped branch so sum() is honored:

CREATE VIEW v_count_rollup_src AS
  SELECT 'leaf' AS scope, 'ALL' AS bucket, record_count, actual_count, 0 AS orphan_count FROM v_registry_leaf_set
  UNION ALL
  SELECT 'meta', 'ALL', 0, 0, COALESCE(orphan_count,0) FROM meta_catalog WHERE composition_level='meta';

pivot	semantics	source	filter / group / metric	branch	viable
PIV-500	grand total Σrecord (leaf)	v_count_rollup_src	scope='leaf' / bucket / sum(record_count)	grouped-sum	needs helper view
PIV-301	total orphan (meta rows)	v_count_rollup_src	scope='meta' / bucket / sum(orphan_count)	grouped-sum	needs helper view
PIV-302	phantom candidate count	v_count_drift	drift_classification='model_b_phantom_candidate' / — / count	count	needs v_count_drift
PIV-303	drift total count	v_count_drift	— / — / count	count	needs v_count_drift
PIV-311	labels by facet	entity_labels	— / label_code / count	grouped-count	native now
PIV-321	pins by scope	registry_pin	— / scope / count	grouped-count	needs registry_pin (Macro 2)

PIV-302 counts candidates only — phantom is a LAW_DEFINITION_GAP (council/RG4); the pivot never asserts "phantom", it counts model_b_phantom_candidate.

PROOF — real engine under BEGIN..ROLLBACK (GREEN, ROLLBACK clean)

Part A (engine-equivalent SQL): PIV-500_sim ALL/2,007,256 · PIV-301_sim ALL/161 · PIV-302_sim 1 · PIV-303_sim 3 · PIV-311_sim 23 groups.

Part B (bundle inserted ACTIVE in ONE statement → real pivot_query()):

INSERT 0 5     pd_in_tx=42                         (37+5; one statement = one refresh)
engine_PIV-500 → {"bucket":"ALL"} {"total": 2007261}   ← SUM (not count): view-backed grouped-sum works
engine_PIV-301 → {"bucket":"ALL"} {"total": 161}
engine_PIV-302 → {}              {"count": 1}
engine_PIV-303 → {}              {"count": 3}
engine_PIV-311 → 23 groups, Σtotal 726,850
ROLLBACK   →   pd_post=37,  idle_in_tx=0

PIV-500 returned 2,007,261, not 2,007,256. The INSERT fired trg_pivot_def_refresh → refresh_meta_catalog_from_pivot(), which re-pulled birth_registry's live count; +5 births landed during the ~2 s rehearsal. This is in-transaction proof that the grand total is a live moving target driven by CAT-023 (model-A), and that the engine emits a genuine SUM.

The 139 PIVOT_MISSING leaves (bulk)

The 132 model-A + 1 model-B + 1 taxonomy leaves each need either (a) a per-leaf total pivot source_object = <registry_collection>, batched in one INSERT statement (is_active=false until activated), or (b) acceptance that their count comes from the scan (actual_count) and they remain PIVOT_MISSING but honestly surfaced by v_living_lists. Recommendation: bulk-INSERT the per-leaf totals in ONE statement after RG2; the 5 pg_table rows need a scan first (counts are NULL) — Macro 2.

Engine fact restated (forbidden compliance)

pivot_definitions carries 3 enabled refresh paths; any INSERT/UPDATE fires a statement-level full refresh. Therefore: never row-by-row insert — always one batch statement, is_active=false off-peak. (Verified: INSERT 0 5 fired exactly one refresh.)

Status: bundle CONCRETE + engine-proven. COMMIT DEFERRED (needs its own gate).