perf(storage): Usage activity pages still scan the remaining time range

[liuxiaocs7]

What happened

The new revision-consistent Usage screen returns only 50 activity rows per page, but its Storage query still performs work proportional to the remaining time range. The cursor and LIMIT 51 sit outside a wide three-source projection, so pagination alone does not bound the scan/projection and sorting work needed to select a page.

At commit 0117d76c5688475e7467ee6db83057bf65edbabd, a second-page request over 50,000 tool records takes 53.530 ms median / 60.211 ms p95 on Node and 59.392 / 76.300 ms on Electron. These are synthetic local Storage-facade measurements, not end-to-end Desktop latency.

The same read path does redundant count work:

• Exact filtered counts reuse the full activity projection, including fields irrelevant to the filter.
• Unfiltered screens count activity again after complete statistics have already counted the same readable records.
• A search with zero matches runs the activity predicates again after the exact count has proved that the page is empty.

Expected: use the existing indexes to seek to the cursor and select a bounded set of matching candidates per source before merging the page. Preserve exact counts, ordering and revision consistency while avoiding the redundant count/page work above. Sparse substring filters and unreadable rows may still require inspecting more index entries; complete statistics and exact filtered counts remain range-sized work.

How to reproduce

1. Build Core and Storage at baseline commit 0117d76c5688475e7467ee6db83057bf65edbabd in a separate checkout.

2. Build the implementation checkout from perf(storage): bound Usage page queries and avoid redundant counts #5514 and run its reproducible benchmark:

   MAKA_PERF_OUTPUT=/tmp/usage-pages-node node scripts/perf/usage-pages.mjs /path/to/built-baseline-checkout

3. Inspect before/second-page-ms for 1,000, 10,000 and 50,000 tool records: the baseline medians grow from 1.541 → 10.871 → 53.530 ms even though each request returns 50 rows.

4. Inspect the matching-page and no-match profiles. At 50,000 tool records, the baseline makes 99,900 lowercase callback evaluations for a matching second page and 300,000 for a no-match screen. These are function-evaluation counts, not physical row-visit counts.

The harness invokes the actual readUsageScreen facade at both revisions against the same persisted fixture, alternates calls in one process, and records three warmups plus 15 samples. It also covers equal timestamps across canonical, legacy and tool sources, middle/tail pages, SQL and query plans. Full reproduction and results in PR #5514.

Environment

• Baseline: 0117d76c5688475e7467ee6db83057bf65edbabd
• Surface: Storage readUsageScreen, used by Runtime Host and Desktop Usage
• macOS, Darwin 24.6.0, arm64, Apple M4 Pro
• Node 24.14.0 / SQLite 3.51.2
• Electron 43.4.1 / SQLite 3.53.1

Logs, screenshots, or additional context

Acceptance for this issue:

• Activity continuation uses indexed cursor seeks and bounded matching candidates before the global merge; dense matching histories do not require a full remaining-history scan/sort just to return a page.
• Unfiltered activity totals reuse complete statistics; filtered exact counts project only the fields required by the filter.
• A zero exact count skips the activity query within the same read snapshot.
• Preserve complete traversal, equal-timestamp ordering, stable identities, literal/Unicode search, exact counts, accounting rules and revision fencing.
• Include behavioral/work regression coverage that fails on the baseline and reproducible before/after measurements on Node and Electron.

Implementation: #5514. Parent performance tracker: #5038.

This follows the already-closed #4531 and #5019 with evidence from the new SQL screen path. Search-triggered statistics caching is separate follow-up work. Frontend debounce and navigation are covered by #5410 and are outside this issue's acceptance.

AI assistance: Codex inspected the source, ran the benchmarks and drafted this issue under contributor direction.