fix(core): make usage statistics fast and responsive - #47527

Why

/stats can sit on “Gathering your stats…” for tens of seconds on a large history. The request reads large message JSON bodies to extract a few usage fields, and its synchronous SQL/aggregation loop can also stall unrelated server work.

A live request took 20.78 s. On a fixed private snapshot containing roughly 847,000 assistant steps, the original implementation took 54.93 s median. Those are separate measurements, not an end-to-end before/after pair.

What changes

• Add a covering index for the existing timestamp/session/type/model/token/cost query, avoiding message-body and tool-output reads.
• Process messages in daily batches with an explicit yield between batches so the server can handle other work.
• Reuse the formatted local date within timezone-aware day bounds instead of formatting it for every assistant message. This cache lasts only for the request and respects DST.
• Add a regression test that captures the actual production SQL and verifies its covering query plan, plus boundary, mutation, and timezone tests.

The final nine runs retained the baseline response digest, with a maximum observed event-loop timer delay of 28.5 ms. Calendar batches are not a hard row-count or latency bound. The historical snapshot benchmark used base 97303c39dd; the matched video below uses the current PR base.

Demo

Real OpenCode TUI, recorded with OpenCode Drive 2.1.0 and Bun 1.4.2. Left: 7a4ad68af6, 2.42 s until visible; right: e94f3b7e24, 0.21 s. Both use independent copies of the same synthetic fixture: 80,000 assistant steps with 64 KiB bodies, across 100 sessions. No private conversation data, simulated provider response, or injected latency. The index migration completes before recording begins.

Playback is real-time and aligned on opening /stats. The completed after frame is held to match the before clip's duration. Native 1600×680 side-by-side export at 60 fps, inspected at the loading and completed checkpoints. The recorded revision has the same production fix; benchmark and recording tooling have since been removed from the PR.

Index cost

The large snapshot's index adds 91.9 MiB and took 69.6 s to build once. SQLite maintains it as messages change. In an in-memory benchmark using the production content projector, incremental update cost was about 0.077 ms for 64 KiB and 1.15 ms for 1 MiB bodies. These are projection measurements, not a full disk-durability or streaming benchmark.

Scope

This PR contains the core stats optimization, its index migration and generated schema, and regression coverage. It optimizes the TUI's year-to-date, all-projects, tools: "none" request. Benchmark harnesses, synthetic fixture generation, recording scripts, and the experiment log are retained locally.

Verification

bunx prettier --check $(git diff --name-only origin/v2...HEAD)
bunx oxlint $(git diff --name-only origin/v2...HEAD -- '*.ts' '*.tsx')
cd packages/core
bun run test test/session-stats.test.ts test/session-projector.test.ts
bun typecheck
bun run migration --check

20 focused tests pass, including the actual covering-query plan, large bodies, indexed updates/deletes, project/fork/subagent filtering, compaction usage, exclusive date bounds, DST changes, and a quarter-hour timezone. Formatting, file-scoped lint, core typechecking, and migration validation pass. Focused tests and core checks were repeated with the required Bun 1.4.2 toolchain. The enabled pre-push hook also passed all 33 workspace typecheck tasks.