#1574 Leaderboard phase 2: take score_grants off the board read path — read today from score_daily

Phase 2 of #1572. No schema change. This is the phase that removes the term scaling with event rate.

Problem

resolvedRows builds the board substrate from two branches:

• history: swamp.score_daily FINAL WHERE day < today()
• today: swamp.score_grants WHERE granted_at >= toStartOfDay(...) GROUP BY distinct_id, grant_id

The second branch is a live scan of today's entire grant tail, on the request path, per viewer, uncached. It grows with the daily event rate, which is the fastest-growing quantity in the system.

Measured impact of the tail alone: on a day with 367K grants, one board render goes from 143 MiB / 40 ms to 264 MiB / 119 ms. The tail branch by itself reads 446K rows / 76 MiB. At 10B scale the modelled tail is ~14.5M grants/day, and the full render is 3,596 ms / 10.85 GiB (see #1572).

The observation that makes this cheap

score_daily already contains today's rows. The rollup projector's touched window is:

WHERE toDate(granted_at) >= today() - 2
   OR created_at >= now64(3) - INTERVAL 30 MINUTE

so today's grains are recomputed in full every tick. The read excludes them (day < today()) purely for staleness — the projector runs on a ~60s tick, and the current design wants a just-landed grant (a badge, a tier crossing) to appear immediately on every pod.

That is a freshness requirement, and phase 1 (#1572) supplies a better mechanism for it than a live scan.

Proposal

Move the history/today split so the board reads score_daily for today as well, and rely on the phase-1 epoch for freshness. Wire the tier crossing to poke the projector, then bump the epoch — in that order:

1. crossing claimed
2. projector run (runScoreRollupProjectorOnce, already exported and lease-gated)
3. epoch bump
4. next board read misses the cache and reads a fresh rollup

This is the eager-tick-plus-announcer-poke shape. A tier-up produces a fresh board because the same event drove both.

Design point to settle before starting

runScoreRollupProjectorOnce runs in telemetry; the tier-crossing watcher runs in the app. The poke therefore needs either an internal endpoint reached via serviceClient, or the watcher moves. Worth deciding up front — it is the main scoping question in this phase.

Expected effect

Deletes the today-tail from the read path entirely. After this, board read cost is a function of the owner population and history depth only — not of how many events/day the platform ingests. That decoupling is the actual goal of the epic.

Constraints and traps

• This changes what the board counts, so parity is the gate, not a nice-to-have. Prove byte-identical board output old-query vs new-query across a prod-shaped mirror, including ghosts, multi-device operatives, and the label-only case.
• The badge fold in ownedGrants depends on subject_id surviving only for grant_type = 'badge'; the projector preserves that. Confirm it still holds when today's rows come from the rollup rather than the live tail.
• Genesis badge history is category='badges' but grant_type='genesis' with empty subject_id, and must keep summing like a normal grant. Existing trap, must not regress.
• Worst-case staleness becomes "one projector tick" for anything that is not a significant event. State explicitly which events bust and which do not — that list is the user-visible contract.

Verification

• Parity harness: identical results for both query shapes over the same dataset.
• End-to-end: drive a real tier-up and assert the board reflects it, proving the poke-then-bump ordering works.
• Assert the board read no longer references swamp.score_grants at all (a static check, so a future edit cannot silently reintroduce it).

Risk / rollback

Medium — correctness change. Rollback is reverting the split boundary; no data migration is involved, so it is a code-only revert.