Make a cache refresh one transaction, retiring the refresh_pending flag #374

A cache refresh is several transactions: one per file's entry rows, then the session rows, the project aggregates and the cross-session sidecar. #365 makes an interrupted refresh recoverable with a journal flag, projects.refresh_pending (migration 015): every entry write sets it, update_project_aggregates clears it, and while it is set the next run takes the full refresh.

The flag works within one process, but it has a known limit, raised in review on #365: if two processes refresh the same project at once, one can clear the flag after the other has written newer entry rows but before it finished. The project is then recorded as current while its derived rows miss those entries. It needs two overlapping runs on one cache (a second --all-projects pass, or serve --watch beside a CLI run); it cannot happen within one run. The flag has also already needed special rules (a date-filtered run restores it rather than clearing it; Codex projects clear it on any run), and its semantics are likely to keep growing.

Proposal: one transaction per refresh

If a project's refresh commits as one transaction, an interruption at any point (a lock-out, Ctrl+C, a kill) leaves the cache exactly as it was, and every freshness check derives the truth from cached_files as it did before #365. Two overlapping refreshes then commit two self-consistent snapshots: the later one wins, and if it parsed older files its cached_files rows say so and the next run repairs it. That retires:

• migration 015's flag and the one-time heal on upgrade,
• the date-filter and Codex special cases,
• the ordering constraint between the sidecar merge and the aggregate update,
• the concurrent-clear race above.

The obstacle is that a transaction spanning the parse would hold the write lock during CPU work, which is what #365 removed. The proposed way around it is spool, then merge: each worker writes its parsed rows into a private SQLite file as it parses (no shared lock), and the refresh's single transaction ATTACHes that file and copies with INSERT … SELECT. A prototype on a 122 MB session file measured:

So the merge holds the lock for less time than today's per-file insert, with no Python per row inside it. The spool costs disk (72 MB for that file) until merged. The alternative, parsing the whole project into memory before one transaction, costs memory of the order of the project's share of the cache.

Costs

• A refactor of the save path in load_transcript and of both refresh paths (ensure_fresh_cache_detailed, _incremental_cache_refresh).
• The extend_cached_entries row-count check moves into the merge.
• After a kill, the modified files are re-parsed instead of reusing rows already saved (which the flag allows).
• Render records (update_html_cache, update_page_cache) stay as they are: each is already self-validating through the staleness check.

A complementary, smaller change, one conversion pass per cache file at a time, removes the realistic instance of the race (two passes on one archive) independently of this redesign.

🤖 Generated with Claude Code