The leaderboard is kept, not recomputed: standings stay milliseconds at fifty thousand matches #100

The leaderboard is recomputed from every match ever played on every page view, and at fifty thousand matches that is the slowest request this server has; keep the standings instead of deriving them.

Measured on main (f2004f1), 50,000 matches over 900 players in a real database:

standings is GROUP BY user_id, name over the whole match table with eight aggregates (src/db.ts:143); the plan is SCAN match plus a temp b-tree. The only index is match_by_time. A covering index on (user_id, name, …) takes it to 21 ms — better and still not milliseconds, because the work is proportional to matches and not to players. The app promises the common request in milliseconds; / is over the 50 ms bar at a number of rows this app can reach in a week of use.

There is exactly one writer (saveMatch) and one deleter (deleteMatch, from the terminal), so the totals can be kept as they are written.

What done looks like

• [ ] A standing table keyed by (user_id, name) holding the eight numbers standings returns today, maintained inside the same transaction as the write that changes it.
• [ ] saveMatch upserts — the shooter posts a result once a round — so the rollup subtracts the row's previous contribution and adds the new one; a match that changes team, name or user moves between rows. deleteMatch subtracts, and a row that reaches zero matches is gone.
• [ ] Opening a database with matches and no standing table builds it once from match, so today's production data arrives correct without a migration step anyone has to run.
• [ ] A differential test is the proof: a seeded sequence of a few thousand saves, re-saves of existing match ids and deletes, after which the standing table equals the old GROUP BY query row for row and number for number. Keep the old query in the test as the oracle.
• [ ] standings(db, limit) reads the rollup and nothing else; matchCount stays what it is.
• [ ] At 50,000 matches, statsHtml(db) is under 5 ms and a POST /api/match is not measurably slower than it is today. The numbers go in the pull, not in an assertion — assert the plan (EXPLAIN QUERY PLAN shows no scan of match) if you want a test that holds.
• [ ] The terminal's leaderboard and src/tty.ts's delete still agree with the page after a delete.
• [ ] bun test passes.

Files

src/db.ts, src/db.test.ts, src/tty.ts if the delete path needs it, src/pages/game.ts only if #89 left something to move.

Risks

A rollup is a second copy of the truth: every write path has to maintain it or the board quietly lies, which is why the oracle test above is the deliverable and not a nicety. Watch the upsert — the row being re-saved is already in the totals. Keep it all inside one SQLite transaction so a crash between the two writes cannot leave them apart.

Waits for #89, which rewrites the same query and the same page to show your own row and a real rank: land that first and this issue makes its rank query fast rather than fighting it.

Suggested: Torque — the database, the arithmetic and the oracle test.

Measured beside #256, which I have just opened for the half this issue does not cover. This one is about the standings staying fast; #256 is about how much disk the leaderboard is allowed to be — today nothing ever deletes a match, src/db.ts's only DELETE is the terminal's manual one, and the limiter lets a caller post a burst of forty and refill at one every two seconds. The two should not re-derive each other's numbers: whichever lands second reads the first's.