Files

T

Wes McKinney 5e6c6fe18e perf(usage): serve usage reports from an exact daily rollup cache (#1454 )

SQLite aggregate usage reads (`usage daily`, top sessions, billed and matching
session counts) are now served from a disposable cache of daily rollups
instead of ranking and pricing every token-bearing message on each request.
Warm 30-day reads on a production-scale archive complete in under a second,
down from several seconds. Results are byte-identical to the live path.

**How it works**

- A sibling database (`usage-cache-v1-<id>.db`) holds timezone-neutral
  normalized facts as a build substrate plus per-timezone daily rollup rows at
  `(session, local day, model)` grain.
- Dedup groups are classified per group at build time. Groups whose resolution
  cannot vary with the query window or live filters are finalized into daily
  rows with exact winner, attribution, and web-search semantics. Irreducible
  groups (cross-session, cross-day, cross-model, Copilot authoritative costs,
  Cursor events) stay in a narrow exception tier resolved at read time, so
  exception volume scales with genuine duplicates, not with messages.
- Every read captures archive fingerprints, pricing, and Cursor high-water
  state, then verifies all required installs in one pinned cache transaction.
  When a fill, Cursor batch, or deletion changes a session's dedup identities,
  the same cache transaction invalidates every other session sharing a changed
  identity, so a finalized daily row never survives gaining a sibling.
- Stale or racing reads retry up to three times, then fail clearly. The cache
  never falls back to stale or live aggregate results.
- A writable daemon backfills newest sessions first and warms the local plus
  recently requested timezones. Requests slower than two seconds log
  privacy-safe per-phase timings.

**Archive changes**

- The first writable open builds three usage discovery/covering indexes on
  `messages`; upgrading a large archive blocks startup while they build, and
  the wait is logged. Read-only binaries require these indexes.
- Full resync drops those indexes in the temporary database during the bulk
  load and rebuilds each once before the swap, and newly inserted sessions
  skip a redundant sync-marker touch, so resync throughput does not regress.
- Finalizing streamed usage now participates in transcript identity (revision
  bump, mirror refresh, secret-scan invalidation on real changes).

**Limits and tradeoffs**

- The cache is derived data: safe to delete when nothing is running, rebuilt
  automatically. The first query after install, upgrade, or deletion pays a
  cold build; background backfill covers it afterward.
- Cursor usage events stay entirely on the exception tier (their keys and
  per-row headless filters are not window-independent).
- PostgreSQL keeps its live implementation under complete-result parity
  coverage; the PG-native optimization is tracked in #1451.

**Where to look**

- `internal/db/usage_rollup_classify.go`: group classification and
  cross-session identity checks
- `internal/db/usage_cache_fill.go`: fill, notification, and sibling
  invalidation
- `internal/sync/engine.go`: resync index drop/rebuild
- `docs/internal/usage-aggregate-cache.md` and `docs/agents/storage.md`:
  durable contracts


Co-authored-by: Wes McKinney <wesm@users.noreply.github.com>

2026-08-20 09:24:31 -05:00

4.2 KiB

Raw Permalink Blame History

Usage Aggregate Cache Implementation Plan

Outcome

Replace request-time normalized-fact aggregation with exact daily rollups while keeping the facts cache as a disposable build substrate. Daily usage, top sessions, billed counts, and relaxed matching counts use rollups. Session detail and PostgreSQL keep the live implementation. Aggregate cache failure is an error, never a live-path fallback.

Constraints

Preserve arbitrary date ranges, timezones, filters, window-scoped dedup, per-row money rounding, authoritative allocation, and Cursor behavior.
A live transcript change or resync must invalidate its source-session facts and every timezone rollup built from them before the next result.
Bake exactly agent and started_at; join other session metadata live.
Resolve pricing in Go and fingerprint the canonical effective pricing data.
Recheck full source fingerprints before install. Do not order by sync_marker, which can decrease.
Keep the SQLite/PostgreSQL complete-result parity test.
Warm 30-day CLI release gate: at most two seconds on the protected clone.

Execution record

1. Disposable schema and identities

Add timezone records and exact local-day UTC intervals.
Add per-session rollup installs keyed by source fingerprint, fact revision, baked metadata, pricing digest, and install revision.
Add daily, activity, and narrow exception rows with covering window indexes.
Use canonical IANA identity or a stable rule fingerprint for anonymous local zones.
Keep fail-closed file recognition and database-ID/schema generations.

2. Go builder

Load only narrow normalized facts for stale source sessions.
Convert ordinary token facts directly to (session, day, model, rate) daily rows using existing Go pricing and money functions.
Store every dedup-capable fact in the exception tier. This subsumes the more complicated connected-component classifier while preserving exact window-scoped semantics.
Build model-aware user activity rows and a synthetic Cursor exception install.
Replace each source session atomically after the archive fingerprint recheck.

3. Aggregate reads

Ensure candidate facts, then requested-timezone rollups.
Verify required installs in one pinned cache transaction.
Read indexed daily rows and only in-window exception rows.
Resolve snapshot/general dedup and price exception survivors in Go.
Apply live filters and authoritative session-cost allocation.
Route all aggregate consumers to this path; retain the bounded live path for session detail and PostgreSQL.

4. Background lifecycle

Backfill newest sessions first in 256-session batches after daemon readiness.
Rewarm process-local plus eight most recently requested named timezones.
Share detached foreground/background fills and retry moving fingerprints at most three times.
Sweep deletion hygiene, optimize between batches, incrementally vacuum a large freelist, and analyze after complete backfill.

5. Remove superseded machinery

Delete the request-time normalized-facts SQL engine and its temp-table population.
Delete SQLite pricing UDFs and SQL-specific rounding tests.
Keep fact extraction/invalidation tests because facts remain the build substrate.
Repoint public consumer and PostgreSQL parity tests at rollup-served SQLite results.

6. Verification

Run focused lifecycle, mutation, randomized parity, and aggregate consumer tests.
Run go fmt ./..., go vet ./..., the full fts5 test suite, and lint.
Run BenchmarkGetDailyUsage and the benchmark gate.
On the protected clone, require byte-identical 7-day, 30-day, and all-history output and measure cold/warm 1-, 7-, 30-day, and all-history behavior.
Run the explicitly requested roborev-fix pass and address actionable findings before delivery.

Measured result

The final warm CLI path reuses rollups across processes. The canonical pricing digest fixes nondeterministic whole-cache invalidation, and stable process-local identity prevents one daemon backfill from splitting across timezone keys. Observed protected-clone warm CLI times are approximately 0.19 seconds (1 day), 0.35 seconds (7 days), and 0.85 seconds (30 days). Cold construction and all-history remain separately reported background-scale work.

4.2 KiB Raw Permalink Blame History