A spending budget makes every request read the whole period's usage table: 62 ms per request at 20k rows, 0.6-2.5 s at 200k #226

What

With any budget under Usage:Budgets, every request reads every usage row of the budget period:

1. PromptDispatcher calls BudgetGate.Blocking for every candidate model of every request.
2. Blocking calls StatusOf, which calls IUsageLedger.Total(start, end, …).
3. On SQLite, Total runs SELECT … FROM usage WHERE timestamp_ticks >= $since AND … over the whole budget period.
4. It builds a UsageRecord for every row, with three decimal.Parse calls each, and sums them in memory.

Nothing is aggregated in SQL and nothing is cached. So the cost per request grows linearly with the number of requests already made this period, and the router slows down as the month goes on.

Evidence

• src/SemanticRouter.AspNetCore/Middleware/PromptDispatcher.cs:98, inside the attempt loop: if (usage.Budgets.Count > 0 && _budgets?.Blocking(model, usage, plan.Requirements.Profile) is { } spent).
• src/SemanticRouter.AspNetCore/Runtime/BudgetGate.cs: Blocking at :72-84, and StatusOf → ledger.Total(start, end, …) at :112-115.
• src/SemanticRouter.Storage.Sqlite/SqliteRouterState.cs: Total = Read(...).Aggregate(...) at :904-905, and Read materialises every row at :918-940.

The measurement.

• Setup: Hashing embeddings and one budget that is never reached: { Name: monthly, Connector: stub, Period: Month, MaxCostUsd: 1000000 }.
• Method: the usage table was preloaded with rows from the last 2.3 days, then the router was restarted and sent unique short prompts.

The router's RSS during the 200k-row budget run was 878 MB, against 285 MB without the budget.

Impact

Claude Code sends a request per tool turn, so one busy developer reaches 20k requests a month and a team gateway reaches 200k.

• A budget, the feature meant to keep spend in check, then adds tens to hundreds of milliseconds to every request, growing through the month.
• At 200k rows the gateway is effectively down (1.5 requests/s at c=1), and it recovers only when the period rolls over.
• The dashboard's usage and status endpoints call the same code.

Suggested fix

• Aggregate in SQL: SELECT COUNT(*), SUM(input), … with the filters. Store costs as INTEGER micro-dollars or REAL so they can be summed.
• Better still, keep running totals per budget in memory:
  • seed them once at start and once per period;
  • add each recorded UsageRecord to them;
  • re-read from the database at most every few seconds, so instances that share the file converge.
• If SQL filters stay, add indexes on (connector, timestamp_ticks) and (model, timestamp_ticks).

Done when

• With one budget and 200k usage rows in the period, request latency at c=1 is within 1 ms of the no-budget case.
• A test or benchmark asserts that the budget check does not scale with the number of usage rows.
• Total and Summarize no longer materialise every row.