05 — Pivot / Counting Cross-Check

Mission §4 pivot/counting objects, each verified live this pass.

Engine confirmed (Đ26)

• pivot_definitions (37) — declarative jsonb spec (source_object, filter_spec, group_spec, metric_spec, registry_group, cache_tier, matrix_spec, template_code). Confirmed live; column set re-read.
• pivot_results (126) — UPSERT cache. Confirmed.
• Functions (definitions only, never executed): pivot_count, pivot_query, pivot_matrix, refresh_pivot_results, refresh_meta_catalog_from_pivot, test_counting_contract. First-pass description holds.
• Views: v_registry_counts (12 cols: cat_code, entity_type, record_count, count_b, count_c, orphan_count, cross_check, active_count, …), v_registry_summary. Confirmed.

Verdict: the auto-count engine is REAL and LIVE. Re-confirmed. Do not rebuild. Adding an official list = INSERT a pivot_definitions row (governed), never a hand count.

Defect 1 — PIV-104 filter (CONFIRMED exact)

PIV-104  source_object=dot_tools
  filter_spec = {"filters":[{"op":"=","field":"status","value":"published"}]}
  group_spec  = {"groups":[{"alias":"category","field":"category"}]}

Only 16/309 dot_tools have status='published' (291 are active). So PIV-104's "dot_tools by category" sees 5% of the table → non-representative. Fix: drop the status filter or change to include active. → EXTEND(fix). Verified by reading the actual filter_spec, not memory.

Defect 2 — Count-integrity LỆCH (REAL) + mis-attributed cause (CORRECTION)

Live v_registry_counts cross_check = LỆCH on:

cat_code	entity_type	record_count	count_b	orphan	note
CAT-006	dot_tool	309	163	0	record vs recompute differ
CAT-007	page	37	52	0	ui_pages dual-count
CAT-023	collection	959,372	943,726	0	birth_registry
CAT-ALL	all	1,661,264	1,919,748	0	roll-up
CAT-CMP	compound_total	423	326	0
CAT-MOL	molecule_total	774	766	0
CAT-MAT	material_total	0	55	0	record_count is 0!
CAT-DOT	dot_total	307	0	140	see Defect 3

🔧 CORRECTION (the key second-pass falsification): the first pass attributed the LỆCH to trg_auto_sync_registry_counts being DISABLED (carried from KB s146). Live pg_trigger check: trg_auto_sync_registry_counts on meta_catalog has tgenabled='O' = ENABLED. The trigger is NOT off.

Therefore:

• "Re-enable the sync trigger" (first-pass doc 10-A action) is a no-op and must be dropped from the remediation packet.
• The real LỆCH cause is a mismatch between the stored meta_catalog.record_count (denormalized, written by refresh fns / the trigger on meta_catalog writes) and the view's live recompute (count_b/count_c). Candidates: (a) refresh_meta_catalog_from_pivot only updates pure-total pivots, leaving grouped/cross CATs stale; (b) the recompute formula (count_b) uses a different predicate than record_count; (c) some CATs (CAT-MAT record 0 vs 55) were never refreshed. → RECONCILE-INVESTIGATE the recompute vs stored definitions, NOT re-enable a trigger.

Defect 3 — DOT dual-count is worse than stated

• CAT-006 (entity_type dot_tool) record_count = 309.
• CAT-DOT (entity_type dot_total) record_count = 307, orphan_count = 140.
• Two meta_catalog rows count the same dot_tools table and disagree by 2 (309 vs 307). First pass said "two CATs count the same table" but reported both as 309/163 — the live 307-vs-309 split is a sharper, separate divergence. → RECONCILE to a single canonical CAT + single pivot (PIV-007→one CAT). Detail in doc 06.

Coverage check (which official lists already have a pivot)

Confirmed live pure-total pivots: PIV-001 meta_catalog, PIV-007 dot_tools, PIV-008 ui_pages, PIV-207 approval_requests (all filters:[], count). First pass's claim that "almost every mature list already has a pivot" holds. Lists still lacking a count pivot (proven absent — no PIV row): normative_registry (law), information_unit/iu_relation/universal_edges (IU/KG), directus_users/roles, event_type_registry. → EXTEND (additive PIV rows).

Verdict

Pivot engine: VERIFIED LIVE, REUSE. Two real defects (PIV-104 filter; record_count↔recompute LỆCH). One first-pass remediation falsified (trigger already enabled). One sharper divergence (CAT-DOT 307 ≠ CAT-006 309). test_counting_contract() should be run read-only to characterize the LỆCH precisely (deferred to P1 — it is a volatile fn, not executed this read-only pass).