Make the board's cost flat, so / keeps answering in milliseconds however many matches the app has stored.
standings in src/db.ts is a GROUP BY identity over the whole match table with no index that can serve it, and it is asked for on the hot paths:
gamePage on every GET / (the first paint),
statsHtml in the POST /api/match response — so a live match pays it once a round,
readData in src/tty.ts, at 20 rows, on every terminal frame.
Measured here, seeding through saveMatch itself (5,000 identities, 24 rounds a row), best of 20 calls:
rows
standings(db, 10)
recentMatches(db, 10)
50,000
41.6 ms
0.07 ms
200,000
171.1 ms
0.13 ms
recentMatches is flat because match_by_time serves it. standings is a full scan plus a sort, on one thread, so at 200k rows every other request waits behind it too. Production is small today — this is a measured cliff, not a present outage, and the point of the issue is that nothing in the repository would notice it arriving.
Done looks like
[ ] A test in src/db.test.ts seeds a stated number of rows (enough to be honest — 20k is plenty) through saveMatch and asserts standings returns inside a stated budget, with the budget and the row count named in the test's own words. It fails against today's db.ts.
[ ] The board's cost no longer grows with the table: either an aggregate kept per identity and maintained inside saveMatch, or a memoised result invalidated on write — the builder's call, argued in the pull.
[ ] The answer is unchanged: one row per identity, the name from that identity's most recent match, the same ORDER BY kills DESC, damage DESC, the same WHERE rounds > 0, the same shape for /api/leaderboard, the page and the terminal. An existing db.test.ts case proves it.
[ ] A database written before this change works on first open, migrated in openDatabase beside the identity, walked_out and map migrations, in the same style.
[ ] POST /api/match still answers with the poster's own numbers including the result it just stored — no stale board in the response that a player reads as their match not counting.
[ ] SQL stays in src/db.ts; no caller learns anything new.
Risks
saveMatch raises a row's columns with MAX(...) on conflict, so an aggregate cannot be maintained by adding the incoming row — it has to move by the delta between stored and stored-after, or be recomputed for that one identity. A test with two posts for the same match id (the periodic post then the final one, and then the periodic one arriving late) is the one that catches this.
A cache shared between the page and the terminal is two processes over one file: the terminal reads the database directly, so an in-process cache in the server must not be the only place the truth lives.
deleteMatch (the terminal's x) has to move the aggregate too.
Files I expect to change
src/db.ts, src/db.test.ts. Possibly src/server.test.ts if a budget assertion belongs at the boundary too. Nothing in src/client/.
Suggested: scout — this is the server's own floor, and the measurement is the work.
Make the board's cost flat, so
/keeps answering in milliseconds however many matches the app has stored.standingsinsrc/db.tsis aGROUP BY identityover the wholematchtable with no index that can serve it, and it is asked for on the hot paths:gamePageon everyGET /(the first paint),statsHtmlin thePOST /api/matchresponse — so a live match pays it once a round,readDatainsrc/tty.ts, at 20 rows, on every terminal frame.Measured here, seeding through
saveMatchitself (5,000 identities, 24 rounds a row), best of 20 calls:standings(db, 10)recentMatches(db, 10)recentMatchesis flat becausematch_by_timeserves it.standingsis a full scan plus a sort, on one thread, so at 200k rows every other request waits behind it too. Production is small today — this is a measured cliff, not a present outage, and the point of the issue is that nothing in the repository would notice it arriving.Done looks like
src/db.test.tsseeds a stated number of rows (enough to be honest — 20k is plenty) throughsaveMatchand assertsstandingsreturns inside a stated budget, with the budget and the row count named in the test's own words. It fails against today'sdb.ts.saveMatch, or a memoised result invalidated on write — the builder's call, argued in the pull.ORDER BY kills DESC, damage DESC, the sameWHERE rounds > 0, the same shape for/api/leaderboard, the page and the terminal. An existingdb.test.tscase proves it.openDatabasebeside theidentity,walked_outandmapmigrations, in the same style.POST /api/matchstill answers with the poster's own numbers including the result it just stored — no stale board in the response that a player reads as their match not counting.src/db.ts; no caller learns anything new.Risks
saveMatchraises a row's columns withMAX(...)on conflict, so an aggregate cannot be maintained by adding the incoming row — it has to move by the delta between stored and stored-after, or be recomputed for that one identity. A test with two posts for the same match id (the periodic post then the final one, and then the periodic one arriving late) is the one that catches this.deleteMatch(the terminal'sx) has to move the aggregate too.Files I expect to change
src/db.ts,src/db.test.ts. Possiblysrc/server.test.tsif a budget assertion belongs at the boundary too. Nothing insrc/client/.Suggested: scout — this is the server's own floor, and the measurement is the work.