#789 Leaderboard window baseline: 90-day cutoff zeroes returning-dormant users (latent, 0 impact today)

Summary

The windowed leaderboard ("today" / "this week") computes each user's window gain as current_score − baseline, where baseline is the user's latest daily snapshot on or before the window start (sinceDate). To bound the scan, findTopByWindow prepends $match: { date: { $gte: sinceDate − 90d } } before the baseline $group. That cutoff can exclude the very snapshot that is the baseline.

A user whose last snapshot before the window is older than 90 days falls to the { d: "", s: 0 } sentinel → baseline 0 → their entire accumulated score is counted as window gain, rocketing them up the windowed board. This is a returning-after-long-dormancy user, not a new user (new users correctly start from a 0 baseline).

Location

• lib/infrastructure/mongo-leaderboard-query.ts → findTopByWindow, the $match { date: { $gte: cutoffStr } } stage (cutoff = sinceDate − 90d) that precedes the baseline $group.
• Affected windows: the 1-day and 7-day boards (routes/api/v1/leaderboard/{page,locate}.ts).

Impact today: ZERO (verified against production)

The bug requires a user who is (a) active in the trailing window, (b) has no snapshot in the 90-day pre-window band, and (c) has real older history before the cutoff. Production data (as of 2026-06-23, 7-day window, cutoff 2026-03-18):

• 83 users active in the last 7 days (the weekly-board population).
• 21 of them have no baseline snapshot in the 90-day band.
• Of those 21, 0 have any snapshot before the cutoff — all are genuinely new users, for whom a 0 baseline is correct, not the bug.

Root cause of the zero impact: production scoring history only reaches back to 2026-02-26 (~4 months), so there is barely any pre-cutoff history for anyone to have been dormant from. No user satisfies all three conditions.

When it becomes live

Once (1) scoring history is deeper than window + 90 days for real users, and (2) actual usage exhibits 90+ day dormancy followed by a return. Neither holds yet.

Why this is filed for later (needs design)

The 90-day cutoff is a scan-bounding heuristic, not a deliberate baseline policy. The "correct" baseline window is entangled with score lifetime / decay semantics — how long a score persists, how/whether it decays, and what a "window gain" should mean for a returning user. That is a design conversation, not a one-line patch.

A candidate direction (to evaluate, not commit): compute the baseline as the latest snapshot ≤ sinceDate with no lower bound, leaning on the { username, date } index, or precompute per-user baselines — decided as part of the score-lifetime design.

Provenance

Found during an audit of the performance changes merged in the prior 24h (the leaderboard full-board materialization removal). Two sibling findings in that same change — the fixed banned-buffer paging window and the 10,000-entry "locate my rank" cap — are both latent at current scale (0 banned users; board in the low hundreds) and are tracked as deferred alongside this one.