#923 Leaderboard: window boards are unindexed full-collection aggregations ×4 per locate miss; locate fetches 4×10k rows to rank one user; search() has O(sum-of-ranks) lookup fan-out

From the 2026-07-02 full-stack performance audit. Observed: /api/v1/leaderboard/locate hit 16.7s in prod. Root cause: every board aggregation runs with maxTimeMS: 15_000 (mongo-leaderboard-query.ts:11) and the window aggregation saturates that budget. Related (semantics, not perf): #789.

1. findTopByWindow: unindexed full-collection scan + $group of metric_snapshots_daily, ×4 per locate cache miss

lib/infrastructure/mongo-leaderboard-query.ts:245-375:

• $match {date: {$gte: cutoffStr}} → $group per username → $sort on computed windowScore. No index supports the $match — only {username, date} / {username, month} exist (mongo-metric-snapshot-repository.ts:129-130); a bare date range can't use them → COLLSCAN of the whole snapshot history.
• No $project before $group — daily docs carry full contributions arrays + eventCounts maps; Mongo reads every full doc for 5 scalar fields.
• The scan+group runs twice per call — dataPipeline (:309-331) and countPipeline (:333-353) each re-execute the core pipeline; the count variant additionally $lookups every grouped user.
• locate calls it for two windows (week + today) → 4 full scans+groups per cache miss. The public /leaderboard page (routes/leaderboard.tsx:24-25) pays the same 4 per view.
• In-memory sort of one doc per user; no allowDiskUse → hard failure at the 100MB limit on the growth curve.

Fix: (a) createIndex({date: 1}) (or {date:1, username:1}); (b) $project the 5 fields right after $match; (c) single execution for data+count ($facet or reuse); (d) durable: materialize window standings — only two windows exist (1d/7d), trivially precomputable every few minutes.

2. locate fetches 4 × 10,000-entry boards to find one username

routes/api/v1/leaderboard/locate.ts:18,77-96,99-127: rank computed by materializing whole boards + findIndex — the O(n)-transfer version of countDocuments({totalScore: {$gt: s}}) against the existing {totalScore:-1, username:1} index (streak rank likewise via {signInStreak:-1, username:1}). Compounding: findTop(10_000) triggers enrichWithSnapshots (mongo-leaderboard-query.ts:184,399-417) — a 10k-username $in returning 10k docs for fields locate never renders. ~30-40k full docs per cache miss to produce ≤28 rows.

Fix: target via findByUsername; rank via countDocuments; neighbors via indexed sort+skip+limit (7 rows); substring fallback via anchored regex on the indexed username field. Drops to ~4 counts + 4 seven-row fetches.

3. Board cache stampede — result cached, not promise

locate.ts:28-41: every request arriving during the ~15s cold fetch launches its own 4-board storm; the client fires per keystroke pause (300ms debounce, components/SearchInput.tsx:21 → islands/LeaderboardLadders.tsx:412). Per-replica module cache, 30s TTL.

Fix: cache the in-flight Promise; stale-while-revalidate. Mostly moot after fix 2.

4. search(): $lookup-before-limit over the entire collection + per-row rank N+1

mongo-leaderboard-query.ts:36-144: with an empty query it $lookups every user_scores doc against user, in-memory sorts the whole set — twice (data + count). The globalRank loop (:97-139) runs one aggregation per result row, each $lookup-ing every higher-scored doc: perPage × O(N) lookups.

Fix: mirror findTop()'s correct order ($match → indexed $sort → $limit → $lookup on the small set); count via countExcludingBanned; rank via one indexed countDocuments per row (no banned $lookup) or a single window-function pass.

5. Banned-user filtering via per-document $lookup in every pipeline

:59-74, 158-173, 212-227, 312-351: each candidate doc joined to user to read one boolean; count pipelines run it over the entire filtered collection. Banned users are rare and already fetched as a set in countExcludingBanned (:383-396).

Fix: load banned userIds once (~60s cache), replace every $lookup+$match with userId: {$nin: bannedIds} in the initial $match.

6. findTop / findTopByStreak ship full documents (10,050 each)

:153-186: only _user is projected out — full contributions arrays + ledger total maps transferred for 10k docs when only username/totalScore/signInStreak are consumed. Fix: $project 4 fields; with fix 5 the plan becomes near-covering.

Expected impact

locate 16.7s → tens of ms; public /leaderboard window boards rescued from the same collscans; search from O(sum-of-ranks) lookups to a handful of indexed counts.

Verified clean: streaks are precomputed at write time with a supporting index; resolveTiersByUsernames is properly batched; page/leaderboard.tsx fetch only what they render (but pay finding 1 on window boards).