#1575 Leaderboard phase 3: owner-grain board_totals so a cache miss is survivable at 10B

Phase 3 of #1572. First DDL of the epic. Needed before 10B, not urgent at 225M.

Problem

Even with phases 1-2 (#1573, #1574) landed, a cache miss still pays a full recompute of the entire owner population. At current scale that is a 143 MiB annoyance. At 10B it is fatal.

Measured against a 10B-scale synthetic built from the real SHOW CREATE TABLE output:

The failure is in the GROUP BY owner, before any concurrency. Prod is 3 replicas, single shard, with no Distributed table — there is no sharding escape hatch to fall back on.

The structural point: the board query reduces 368K rows to 10,722 owners to display 25. The answer is tiny; the work to produce it is proportional to all history.

Component breakdown at current scale: the aggregation to owners costs ~32 MiB; the three row_number() OVER window functions plus countIf() OVER () take it to 143 MiB. Roughly 75% of the memory is spent ranking a set that could have been pre-ranked.

Proposal

An owner-grain table maintained by the existing lease-gated projector:

board_totals (owner, t_all, t_week, t_today, active_days, is_ghost, version)
ENGINE = ReplacingMergeTree(version)
ORDER BY owner

Board reads become a scan bounded by owner count, permanently decoupled from event count.

Measured effect

The synthetic is deliberately pessimistic at 3.23M owners. Real prod has ~10K board owners, so the uncached read there would be ~1 ms. The fill (the write side, paid once per bust by one leaseholder, not per viewer) measured 2.28 s.

Relationship to #1495

#1495 proposes active_days_by_id to make the streak board cheaper, on the measurement that days_active is a 99.96% superset of the other two day sources. That remains true and useful — it is a natural input to this table rather than a competitor. Two options worth deciding between:

• board_totals carries the streak column, computed by the projector from #1495's table. One read serves all four boards.
• board_totals covers the three score boards; the streak board reads #1495's table directly.

The first is fewer reads; the second keeps the streak's gaps-and-islands logic in one place. Worth settling when this is picked up, ideally with #1495's owner in the room.

Constraints and traps

• Identity stays late-bound. This table is keyed by resolved owner, which is a departure from the distinct_id-keyed rule that governs the ledger — and that is only safe because it is a derived projection, rebuilt by the projector, never a system of record. The ledger keeps distinct_id. If a device rebinds, the next projector run corrects the projection. This must be stated in the DDL comment or someone will later mistake it for a frozen identity mapping.
• Prod DDL must land via the giga-swamp clickhouse-migrations Job with both Job and ConfigMap hash-bumped, on all 3 nodes, and verified per-IP — a load-balanced Service hides per-node drift, and that gap silently dropped every issue_shipped grant after the last cutover.
• Two-step ordering rule: the table must exist and be backfilled before the reading code deploys.
• The exclusion anti-join (hidden_usernames) and the founder set still apply at read time and must not be baked into the projection — hiding is app policy that changes without a grant changing.

Verification

• Parity: board_totals-backed board output byte-identical to the live query across ghosts, multi-device operatives, hidden/enterprise operatives, and founders.
• A ranking-drift check after a projector stall, proving self-healing.
• Per-replica verification of the DDL, by IP, not through the Service.

Risk / rollback

Medium. Rollback is a read-path flag back to the live query; the table can be left in place and dropped later.