Thread list fails with "too many SQL variables" once a server has more than 32,766 threads, so no client can load or sync its sidebar#25621

opened23h agobyNNoorChasib

What happened

On a server with a large number of subagent threads, every client stopped syncing its thread list. Desktop (Nightly, against a remote server), the website, and iOS 2.1.0 each show a different, stale sidebar:

New threads never appear in the sidebar. They still run and use provider usage, but after switching away there is no way to find them again.
Settling, snoozing or closing a thread on one client never shows up on the others.
iOS sits on "Syncing threads…" indefinitely.

The server is healthy otherwise. It answers over Tailscale Serve, and provider sessions keep running.

Diagnosis

ProjectionStore.readShellSnapshotRows (apps/server/src/orchestration-v2/ProjectionStore.ts) loads every matching thread, then passes all of their ids as bound parameters to several follow-up queries: selectShellItemCounts, selectShellProviderThreadRows, selectShellPendingTurnItemRows, each using thread_id IN ${sql.in(threadIds)}. The SQLite bundled with Node allows at most 32,766 bound parameters per statement. Once a server has more than 32,766 non-deleted threads matching the query, the statement fails to prepare:

1	effect/sql/SqlError: Failed to prepare statement
2	[cause]: Error: too many SQL variables
3	at ProjectionStore.readShellSnapshotRows
4	at OrchestratorV2.readShellSnapshot
5	at ShellStream.loadShellSnapshotParts
6	at ws.orchestrationV2.loadShellSnapshot

The error surfaces as orchestration_snapshot_failed → OrchestratorProjectionError → ProjectionStoreReadError (threadId: 'thread:shell'). It fails the same way over WebSocket (ws.rpc.orchestration.subscribeShell) and HTTP (http.orchestration.loadShellSnapshot), so every client is affected.

The sidebar uses location: "active", so archived threads are excluded there. Several server-internal callers use getShellSnapshot() with no location filter, so archived threads still count toward the limit for them: storageCleanup.ts, serverRuntimeStartup.ts, CheckpointRestoreSafety.ts, ThreadLaunchService.ts (worktree branch rename), and AgentAwarenessRelay. Those break as well.

The thread count gets this high mainly through workflow/subagent fan-out. On this server, 33,786 of 34,940 live threads are subagent threads, and a single root thread has 18,805 subagent descendants. Deleting a parent thread does not delete its subagent thread rows.

Possible fix directions: split id lists into batches below the limit (e.g. 10k) and merge the results, or replace the IN (...) lists with joins or subqueries driven by the same filter used to pick the threads (or a temp table / json_each(?) with a single bound parameter).

Repro steps

Run a server whose statev2.sqlite contains more than 32,766 non-deleted, non-archived threads. Workflow-heavy use creates them quickly; this server added 1,300–3,500 per day.
Open any client (desktop, web, or mobile) connected to that server.
The sidebar never refreshes. The server logs environment api operation failed { reason: 'orchestration_snapshot_failed' } every 15–20 s, caused by too many SQL variables.

Minimal standalone reproduction against a copy of the database, using node:sqlite (Node v26.12.1):

1	import { DatabaseSync } from "node:sqlite";
2	const db = new DatabaseSync("statev2.sqlite", { readOnly: true });
3	const ids = db.prepare("select thread_id from orchestration_v2_projection_threads where deleted_at is null").all().map((r) => r.thread_id);
4	for (const n of [32766, 32767]) {
5	try {
6	db.prepare(`select count(*) from orchestration_v2_projection_threads where thread_id in (${Array(n).fill("?").join(",")})`).get(...ids.slice(0, n));
7	console.log(n, "OK");
8	} catch (e) { console.log(n, "FAIL", e.message); }
9	}
10	// 32766 OK
11	// 32767 FAIL too many SQL variables

Environment

Version the bug happened on: server 0.1.3-nightly.20261026.3726 (commit 3f9448747bb8, currently the tip of main), running as the background service (boot-service) on a Linux x64 VPS
Clients: macOS desktop app 0.1.3-nightly.20261026.3726 connecting to the remote server; web app in Vivaldi; iOS app 2.1.0. All connect to the same server over Tailscale Serve.
Triage CLI version: 0.1.3-nightly.20261026.3726
Node (host): v26.12.1
Database: 34,940 non-deleted threads (1,161 top-level, 33,786 subagent, 3 forks), 1.2M turn items, 9.6k runs

Evidence

The 32,767th thread was created at 2026-10-24T21:39Z. The first orchestration_snapshot_failed error appears shortly after, and the service log has more than 5,100 occurrences since then.
Trace spans: ProjectionStore.readShellSnapshotRows → sqlite (selectShellItemCounts statement) exit Failure: SqlError: Failed to prepare statement. Parents include ws.orchestrationV2.loadShellSnapshot, ws.rpc.orchestration.subscribeShell, http.orchestration.loadShellSnapshot, and AgentAwarenessRelay.start.

Related issues

#24997 — iOS thread list misses days of threads (possibly the same symptom on large databases)
#24895 — History-wide reads saturate the SQLite reader on large databases

Filed via t3 triage by Claude Code (Claude Opus 5.5, claude-opus-5-5).

BBilalIrfanContact22h ago

I’d like to take this on. I’ll first reproduce the SQL variable limit with a focused test using an isolated database, then work on a fix that preserves thread filtering and related data. Please let me know if anyone is already working on it.