05 — PIV-104 DOT Drill Gap Fix (Workstream D)

The problem (live, before)

• Parent PIV-007 = DOT Tools — Total = 309 (all dot_tools).
• Child PIV-104 = DOT Tools theo Nhóm grouped by category, but carried filter_spec = status='published'.
• Result: PIV-104's group rows summed to Σ16 (pivot 8 / null 4 / Vòng đời 2 / Giám sát 2) — only the 16 published DOTs — under a parent of 309. A child silently summing to 16 under 309 = a misleading drill.

Root cause

The status='published' filter. dot_tools.status distributes as active 291 / published 16 / null 2. The drill named "by group/category" should cover all tools; the published filter was a stray restriction, not a labelled sub-axis.

Chosen fix — smallest clean solution (§7 option 1)

Drop the filter so PIV-104 groups all 309 by category:

UPDATE pivot_definitions SET filter_spec='{"filters":[]}'::jsonb, updated_at=now() WHERE code='PIV-104';

This is metadata-only (UPDATE, no birth), same class as the parent_code edits. Pre-image saved: {"filters":[{"op":"=","field":"status","value":"published"}]}.

Rejected alternatives: (2) keep published-only + rename — would orphan the 293 non-published DOTs from any drill; (3/4) add separate all/active/published child pivots — more births + canon expansion for no current need. Option 1 is minimal and makes the existing child honest.

Proof (live, after)

PIV-104 pivot_results recomputed by the refresh trigger:

• 25 category groups summing to 309 (was 4 groups / 16).
• Drill reconciliation view: PIV-007 → RECONCILES (parent 309 = children_total 309).

Two distinct "drift" signals — do not conflate

After the fix, the node contract still shows drift_status='drift' on PIV-007/PIV-104. This is a different, legitimate drift and must stay surfaced:

• It comes from v_count_integrity for dot_tools: counted 309 vs actual_count 163 → record_surplus 146, drift_classification='model_b_phantom_candidate', count_integrity_status='failed'.
• That is the registry-vs-actual record surplus (146 phantom-candidate DOTs) — the known dot_tools hygiene gap — not the parent/child drill-sum mismatch.
• The drill-sum mismatch (the thing this workstream fixed) is now resolved (309=309); the record-surplus drift is honestly retained.

Note on the node contract field

PIV-104's count_value is structurally NULL (count_status PIVOT_MISSING) because it is a grouped drilldown, not a scalar total — the contract's count_value reads only the group_values='{}' scalar row, which grouped pivots don't have. The drill total lives in the group rows (now 309). The new v_rp_drill_reconciliation view is what exposes the parent↔child reconciliation the base contract never computed.

Completion

DOT drill is honest: PIV-104 sums to 309 and reconciles to PIV-007. No fake totals, no hardcoded categories, no published-only collapse. ✅