Bound local analytics memory with a derived SQLite store

[thrr87]

Problem

Codex Limits still retains local analytics history as full Swift arrays.
The latest bounded restore lowers the first refresh, but each later refresh
adds more facts to the resident set.

Measured on the current local dataset:

• 406,727 Derived Records and about 400 MB of app-owned JSONL
• installed pre-fix build: 1.62–2.36 GB peak RSS on launch
• current codex/reduce-refresh-cost build: 122–135 MB on the first refresh
• 20,000-record restore: +38.6 MB RSS
• 100,000-record restore: +266.5 MB RSS

The current seam publishes [LocalActivityFact]. UsageMonitor retains that
history and the reader builds receipts, diagnostics, timelines, and indexes
from it. Bounded reading delays the growth but cannot bound the final resident
set.

A native SQLite probe over all 406,727 records used:

• 28.4 MB peak RSS during a 4.31 s indexed import
• 178.5 MB on disk
• 22.5 MB peak RSS for a fresh query process

These figures validate SQLite as a local derived store. They do not set final
product timings.

Direction

Create one deep LocalActivityStore module backed by system SQLite3.

The module must own incremental ingest, source cursors, normalization,
deduplication, local projections, and bounded queries. It must return summaries,
series, and pages. It must never return the full fact history.

Keep:

• SwiftUI and the current Swift app
• Codex source files read-only
• account usage history and optional folder sync in the current JSON format
• Coverage, Confidence, Task Tree, Project, and deletion rules

Do not add Rust, a resident helper, an ORM, a connection pool, or a new package.

Delivery

1. Remove the full refresh tied to opening the menu. Show the last account
   snapshot at once; keep launch, ten-minute, wake, and manual refresh paths.
2. Add a performance contract and parity fixtures for the current aggregators.
3. Add a local, versioned SQLite store and import legacy Derived Records in
   bounded transactions.
4. Move local token, timeline, filter, receipt, and task-detail reads behind
   bounded store queries.
5. Remove [LocalActivityFact] from the monitor/reader seam and retire the old
   JSONL cache only after a verified migration.

Required behavior

• The default Usage remaining graph performs no local-store or Codex-source read.
• Import reads Codex sources without changing them.
• One transaction commits Derived Records and its source cursor together.
• Cancellation never publishes a partial batch.
• Queries see one committed revision and return bounded results.
• Receipt and Task Tree detail use stable keyset pages.
• Missing source data lowers Coverage and names the reason.
• Delete analytics history removes the database, its helper files, legacy
  local cache, account history, and sync-folder history.
• Automatic import cannot restore data older than the deletion cutoff.
• Rebuild remains a separate user action.
• A failed migration keeps the old cache and reports the failure.

Performance gate

Validate on a copy of the current real dataset:

• default menu and Usage remaining: at most 120 MB RSS
• an ordinary loaded local view: at most 150 MB RSS
• import peak: at most 250 MB RSS
• no upward RSS trend across ten no-change refreshes
• menu open: at most 50 ms on the main thread
• bounded local read: at most 150 ms cold

If the spike misses these gates, stop and profile before completing the
migration. Consider Rust only if parsing remains the measured bottleneck after
full-history residency is gone.

[thrr87]

Stage 1 is in #37.

It removes local history reads from the default Usage remaining path and
releases local facts after the user leaves local analytics. On the real
profile, a clean QA launch and manual account refresh stay near 102–103 MB
RSS.

Local Facts still reaches about 160–175 MB, so this issue stays open. The next
stacked PR will replace the full local fact buffer with bounded SQLite reads.

[thrr87]

Implemented the agreed lightweight-core cut in draft PR #39.

Delivered:

• no local rollout scan in the default Usage remaining view
• native SQLite store with bounded queries
• Token Activity filters and ranges
• streamed import of large records
• deduplication for forked and resumed tasks

Real-data result (about 20 GB of rollouts): cold import about 6m39s at about 106–122 MB RSS; warm launch about 2s at about 111 MB RSS. SQLite used 13 MB plus a 5 MB WAL. Exact CI command passed 549 tests with 1 skipped and 0 failures.

Deferred from this cut: Usage per token, concurrency, receipts, and insights.

Release gate: cross-Mac Token Activity sync is not covered by the existing Usage history sync. #38 tracks the safe sync format and blocks final acceptance here. We will not sync live SQLite/WAL/SHM files.

[thrr87]

Superseded for the current delivery by #41. SQLite may still serve the opt-in local analytics work in #40, but the lightweight core will not scan or import local token history.