#1586 Profile day drill-down returns 0 entries at the 5s ClickHouse ceiling for every operative

Summary

GET /api/v1/users/NAME/score-history?detail=full burns roughly 5 seconds of ClickHouse and returns zero entries, for every operative tested — not just the device-heavy skew identities the existing notes describe. The slim variant of the same window returns full data in under a second. The profile day drill-down (the per-day event mix / combat log detail) is therefore dead UI that silently renders as "no data" rather than as an error.

Found while investigating the slow profile card (see PR #1055, which fixed a different 5s read on the same page).

Evidence (production, 2026-08-11)

Same operative, same window, same instant. The only difference is detail=full, which is the arm that pays the raw-events event-mix scan.

Downstream: /u/webframp/activity measures 1.9s to 3.7s across runs (variance consistent with a cache-dependent full scan), versus 1.0s for keeb.

Cause

This is the residual documented in infrastructure/clickhouse/CLAUDE.md under "Quest-read latency: the distinct_id scan trap (Lab #1317)". dailyHistory.eventMix is the last request-time raw-events read on the profile path. swamp.events is ORDER BY (event, created_at) with only a distinct_id bloom index, so a per-identity filter has no primary-key access path and the read scans the partition, breaching the 5s max_execution_time.

The failure is invisible because loadProfileScore catches a ledger read failure and degrades to an empty standing. A timeout therefore renders as "no activity" rather than as an outage — the same "a 5s failure that looks like no data" shape the doc already calls out.

The docs understate the blast radius

infrastructure/clickhouse/CLAUDE.md frames the residual as specific to an operative whose devices touch every granule, naming webframp at 1187 devices, and says pruning fixed the general case (measured 335ms to 14ms). Production disagrees: keeb (182 active days, roughly 570k events, not device-heavy) fails identically at 4.80s with 0 entries. So this is not a skew-identity tail — the detail=full read appears to be failing broadly. That claim in the doc should be corrected as part of this fix.

Suggested fix

The doc already prescribes it: pruning cannot fix this shape, only a rollup can. Build a (distinct_id, day, event, command, subcommand)-keyed materialized view — the quest_signal_counts treatment plus a day dimension — and switch the read to it.

Ordering matters and is non-negotiable: the CREATE TABLE, CREATE MV and one-shot idempotent backfill must reach production as a clickhouse-migration (hash bumped, applied) BEFORE the code that reads the new object deploys. Otherwise the read 404s UNKNOWN_TABLE on every call, which is the 2026-07-06 ordered-change trap that froze the activity projector for 16 hours.

Two smaller things worth doing in the same change:

1. Make the drill-down distinguish "still loading / degraded" from "no activity in this window", so a ceiling breach reports an outage instead of quietly claiming the operative did nothing. Same trap as the leaderboard fill, where pending had to be checked before the empty-rows branch.
2. Consider whether detail=full should be served at all while it cannot answer within the ceiling — right now every call costs roughly 5 seconds of a 2-vCPU node and returns nothing, which is pure load with no product value.