Storage cleanup still rereads every thread shell, archived ones included, after each provider session stops (follow-up to #17479) #25995

What happened

The desktop app kept stalling: the sidebar and thread switches took many seconds, and writes queued behind long reads. The local database is 14 GB with 2,504 threads, most of them finished subagent threads from delegate_task. Archiving 2,312 finished threads made the PR discovery read fast, but a 13 to 24 s shell read kept repeating about once a minute.

Diagnosis

The repeating read is StorageCleanup.sweep → cleanWorktrees → readThreads. It calls projections.getShellSnapshot() with no location filter, so it builds the full thread shell for every thread that is not deleted, archived threads included, with all of its correlated per-thread subqueries (apps/server/src/storageCleanup.ts, readThreads, unchanged on main as of fe585bc). Archiving threads therefore does not reduce its cost; only deleting them would.

What makes it run so often: with storageCleanup.worktreeOnDelete: true (the default is false), the event stream requests a sweep on every provider-session.updated whose status is stopped or error, for any thread (storageCleanup.ts, the Stream.runForEach(events, ...) block near line 1255). With 10 to 20 agents running, sessions stop once or twice a minute, so the sweep runs back to back. On this machine no thread had ever been deleted, so every one of those sweeps had nothing to clean.

The index from #23000 makes each sweep's read cheap here (13 s to 1.3 s), but the sweep still runs once per stopped session and still grows with every archived thread. Two changes would fix that, independently:

1. Request a sweep on provider-session.updated only when that session's thread is deleted. That is the only case where worktreeOnDelete can act (if (deleted && !settings.worktreeOnDelete) return keep("no rules apply")).
2. Have readThreads select only the columns the cleanup uses (thread id, project, worktree path, branch, archived, deleted, settled) instead of the full shell snapshot.

Steps to reproduce

1. Set storageCleanup.worktreeOnDelete to true in settings.
2. Have a database with a few thousand threads, most of them archived.
3. Run several agent threads so provider sessions stop regularly.
4. In server.trace.ndjson, list StorageCleanup.sweep spans: one starts after each provider session stops, and each one's ProjectionStore.readShellSnapshotRows child reads every thread, archived ones included.

Version

T3 Code (Beta) desktop 0.1.2, local server serverVersion 0.1.2. The same code is on main at fe585bc.

Environment

macOS 26.6.2 (25G83), Apple Silicon, 24 GB RAM. Desktop app with its bundled server. statev2.sqlite 14 GB: 2,504 threads (150 top-level, 2,354 subagent), 2,312 of them archived after cleanup. 240 to 390 subagent threads created per day.

Evidence

ProjectionStore.readShellSnapshotRows spans from server.trace.ndjson, local time, grouped by caller:

before archiving (2,480 unarchived threads)
  20:29-20:40  ThreadPullRequestServiceV2.synchronize, about every 66 s: 6.3 to 28.8 s, median about 14 s

after archiving 2,312 finished threads (176 unarchived)
  20:56-21:00  ThreadPullRequestServiceV2.synchronize: 0.19 to 2.7 s
  20:57-21:02  StorageCleanup.sweep, 6 runs in 5 minutes: 18.8 to 24.5 s each,
               readShellSnapshotRows 12.9 to 13.1 s each

after also applying #23000's run_interrupt_request partial index (app restarted)
  21:07-21:17  all readShellSnapshotRows: 76 ms to 2.05 s, median about 0.32 s (20 spans)
  21:07:43     StorageCleanup.sweep at startup: readThreads 1.3 s over all 2,504 threads

Provider session events in the same window, from orchestration_events (UTC):

02:57  provider-session.updated stopped  1
02:58  provider-session.updated stopped  2
02:59  provider-session.updated stopped  1
03:01  provider-session.updated stopped  1
03:02  provider-session.updated stopped  1

The six sweeps above start in the same minutes as these six events.

Related issues

• [Bug]: Worktree cleanup runs sweeps back to back and stalls the server on large databases #17479 (closed 2026-10-14) reported back-to-back sweeps with worktreeOnDelete on. Its fix coalesces queued sweeps and only reacts to stopped/error, which matches what I see in 0.1.2: no back-to-back queue. What remains is in this issue: every stopped session on any live thread still requests a full sweep, and readThreads still includes archived threads, so the cost grows with history that archiving was supposed to retire.
• [Bug]: Thread-shell and recovery queries take 2–22 s on a 26.5 GB V2 database; six proposed indexes (follow-up to #22659) #23000 covers the slow thread-shell query itself and proposes indexes, including the run_interrupt_request partial index applied here. This issue is about how often storage cleanup runs that query, and that it includes archived threads, which no index removes.
• PR discovery rebuilds thread shells and counts unused history #18618 covers PR discovery rebuilding thread shells. Archiving fixed that path here; storage cleanup is the remaining one.
• Any read transaction left open on statev2.sqlite freezes every server write once the WAL passes 32 MB #23482: long reads like these pin the WAL and stall writes.
• [Feature]: Batch archive: archiving thousands of finished subagent threads takes one heavy command per thread and pushes an update per thread to every client #24662 and [Bug]: Thread opens take seconds with ~5,000 unarchived threads; client work scales with every settled thread in the shell #22372: batch archive and shell size with many settled threads.

Fix applied or workaround

• Archived 2,312 finished threads through the MCP tool t3_thread_organize.
• Applied [Bug]: Thread-shell and recovery queries take 2–22 s on a 26.5 GB V2 database; six proposed indexes (follow-up to #22659) #23000's partial index ON orchestration_v2_projection_turn_items(thread_id, run_id) WHERE type = 'run_interrupt_request' with the app closed, after a backup.

Filed by

Claude Code (Claude Opus 5.5), following .github/triage/PLAYBOOK.md.