perf(ai-hist): Reflex sync repeatedly re-scans and backfills large local histories

[willwashburn]

Summary

When ai-hist-native powers Agent Relay Reflex, a background syncAndPush() can consume a CPU core for several minutes on a machine with a substantial existing agent-history corpus. The work completes, then is eligible to run again on the next periodic tick.

Observed behavior

• ai-hist-native: 0.4.1 (macOS arm64; Node 22.22.2)
• Local corpus: 1,525 Codex rollout files, 786 Claude transcript files, and 180 Cursor project files
• Local ai-hist database: about 930 MB / 52,624 history rows
• Persisted .sync-state.json: about 917 KB
• A single Reflex sync took roughly 8 minutes of wall time and accumulated about 4 CPU-minutes in the Node host process. The broker and harness sidecars were idle.
• CPU returned to idle when syncAndPush() completed. Disabling Reflex prevented the next periodic sync.

No paths, transcript content, session identifiers, or account information were collected for this report.

Suspected hot path

sync_and_push() calls sync_basic(), which calls sync_codex() and sync_claude_session_metadata() each run. build_codex_session_maps() still traverses all rollout files to obtain file stamps. More importantly, backfill_codex_metadata() iterates every mapped Codex session and performs an UPDATE, a SELECT MIN/MAX, and an upsert_session() even when no session metadata has changed. Similar per-file/session work occurs for Claude metadata.

On a large, already-imported corpus, the importer is logically incremental for new JSONL bytes but the metadata work is effectively a repeated full maintenance pass.

Expected behavior

A periodic Reflex sync should have near-idle cost when there are no new or changed history files. It should not repeatedly scan/backfill every previously indexed session.

Suggested direction

1. Record a metadata/backfill-complete stamp per session and only run backfill_codex_metadata() for new or changed rollout metadata, or for rows actually missing project/branch values.
2. Avoid per-session UPDATE/MIN/MAX/upsert queries for unchanged sessions; batch the remaining work where possible.
3. Preserve file-stamp-based discovery but avoid expensive full directory traversal where an incremental index or directory-level mtime is safe.
4. Add a benchmark/regression fixture with thousands of unchanged Codex and Claude session files, asserting a second sync is bounded and materially cheaper than the first.
5. Consider exposing duration/scan-count diagnostics from the N-API API so embedding hosts can surface or adapt to unexpectedly slow syncs.

Reproduction outline

1. Build a local history corpus with thousands of previously imported Codex/Claude session files.
2. Enable Reflex and start an Agent Relay node.
3. Let the delayed syncAndPush() run.
4. Observe sustained CPU until completion despite no new history being created.
5. Leave the node running: the next eligible periodic tick repeats the maintenance work.

ai-hist-native 0.4.3 appears to address quiet logging; it does not appear to contain this performance optimization.