Skip to content

Commit 0d51375

Browse files
fix(server): thread lists load beyond SQLite variable limit (pingdotgg#25696)
Co-authored-by: Claude Opus 5.5 <[email protected]>
1 parent 1b3e330 commit 0d51375

2 files changed

Lines changed: 71 additions & 7 deletions

File tree

‎apps/server/src/orchestration-v2/ProjectionStore.test.ts‎

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1374,6 +1374,70 @@ it.effect("lists only active subagent IDs without loading shell data", () =>
13741374
}).pipe(Effect.provide(layerTest)),
13751375
);
13761376

1377+
// regression: #25621 SQLite rejects statements binding more than 32,766 variables
1378+
it.effect("lists every thread in the shell snapshot past SQLite's bound variable limit", () =>
1379+
Effect.gen(function* () {
1380+
const store = yield* ProjectionStore.ProjectionStoreV2;
1381+
const sql = yield* SqlClient.SqlClient;
1382+
const now = yield* DateTime.now;
1383+
const templateId = ThreadId.make("thread:many:00000");
1384+
yield* store.apply({
1385+
id: EventId.make(`event:${templateId}:created`),
1386+
type: "thread.created",
1387+
threadId: templateId,
1388+
occurredAt: now,
1389+
payload: {
1390+
createdBy: "user",
1391+
creationSource: "web",
1392+
id: templateId,
1393+
projectId: ProjectId.make("project:many"),
1394+
title: "Many",
1395+
providerInstanceId,
1396+
modelSelection,
1397+
runtimeMode: "full-access",
1398+
interactionMode: "default",
1399+
branch: null,
1400+
worktreePath: null,
1401+
activeProviderThreadId: null,
1402+
lineage: { parentThreadId: null, relationshipToParent: null, rootThreadId: templateId },
1403+
forkedFrom: null,
1404+
createdAt: now,
1405+
updatedAt: now,
1406+
archivedAt: null,
1407+
settledOverride: null,
1408+
settledAt: null,
1409+
lastVisitedAt: null,
1410+
deletedAt: null,
1411+
},
1412+
});
1413+
const threadCount = 32_767;
1414+
yield* sql`
1415+
CREATE TEMP TABLE many_threads AS
1416+
WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < ${threadCount - 1})
1417+
SELECT t.*, n.i AS clone_index FROM orchestration_v2_projection_threads t, n
1418+
WHERE t.thread_id = ${templateId}
1419+
`;
1420+
yield* sql`
1421+
UPDATE many_threads SET
1422+
thread_id = printf('thread:many:%05d', clone_index),
1423+
payload_json = json_set(
1424+
payload_json,
1425+
'$.id', printf('thread:many:%05d', clone_index),
1426+
'$.lineage.rootThreadId', printf('thread:many:%05d', clone_index)
1427+
)
1428+
`;
1429+
yield* sql`ALTER TABLE many_threads DROP COLUMN clone_index`;
1430+
yield* sql`INSERT INTO orchestration_v2_projection_threads SELECT * FROM many_threads`;
1431+
1432+
const [unscoped, active] = yield* Effect.all([
1433+
store.getShellSnapshot(),
1434+
store.getShellSnapshot({ location: "active" }),
1435+
]);
1436+
assert.strictEqual(unscoped.threads.length, threadCount);
1437+
assert.strictEqual(active.threads.length, threadCount);
1438+
}).pipe(Effect.provide(layerTest)),
1439+
);
1440+
13771441
it.layer(layerTest)("ProjectionStoreV2", (it) => {
13781442
// regression: #21969 keep corrupt plan JSON in the typed read error
13791443
it.effect("returns a typed read error for malformed stored plan JSON", () =>

‎apps/server/src/orchestration-v2/ProjectionStore.ts‎

Lines changed: 7 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -5940,7 +5940,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
59405940
worktreePath === undefined
59415941
? sql``
59425942
: sql` AND json_extract(t.payload_json, '$.worktreePath') = ${worktreePath}`
5943-
}${threadIds === undefined ? sql`` : sql` AND t.thread_id IN ${sql.in(threadIds)}`}
5943+
}${threadIds === undefined ? sql`` : sql` AND t.thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))`}
59445944
ORDER BY t.updated_at ASC, t.thread_id ASC
59455945
`;
59465946

@@ -5953,14 +5953,14 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
59535953
: sql<ShellRunRow>`
59545954
SELECT thread_id, run_id, ordinal
59555955
FROM orchestration_v2_projection_runs
5956-
WHERE thread_id IN ${sql.in(threadIds)}
5956+
WHERE thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
59575957
`;
59585958

59595959
const selectShellRunItemCounts = (threadIds: ReadonlyArray<ThreadId>) =>
59605960
sql<ShellRunItemCountRow>`
59615961
SELECT thread_id, run_id, COUNT(*) AS item_count
59625962
FROM orchestration_v2_projection_turn_items
5963-
WHERE run_id IS NOT NULL AND thread_id IN ${sql.in(threadIds)}
5963+
WHERE run_id IS NOT NULL AND thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
59645964
GROUP BY thread_id, run_id
59655965
`;
59665966

@@ -5975,7 +5975,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
59755975
FROM (
59765976
SELECT thread_id, run_id, COUNT(*) AS item_count
59775977
FROM orchestration_v2_projection_turn_items
5978-
WHERE thread_id IN ${sql.in(threadIds)}
5978+
WHERE thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
59795979
GROUP BY thread_id, run_id
59805980
) counts
59815981
LEFT JOIN orchestration_v2_projection_runs r ON r.run_id = counts.run_id
@@ -5992,7 +5992,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
59925992
: sql<PayloadRow & { readonly thread_id: string }>`
59935993
SELECT thread_id, payload_json
59945994
FROM orchestration_v2_projection_provider_threads
5995-
WHERE thread_id IN ${sql.in(threadIds)}
5995+
WHERE thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
59965996
`;
59975997

59985998
const selectShellPendingTurnItemRows = (
@@ -6008,7 +6008,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
60086008
WHERE i.type IN ('command_execution', 'dynamic_tool', 'subagent')
60096009
AND i.status NOT IN ('completed', 'interrupted', 'failed', 'cancelled')
60106010
AND (i.run_id IS NULL OR r.status <> 'rolled_back')
6011-
${threadIds === undefined ? sql`` : sql`AND i.thread_id IN ${sql.in(threadIds)}`}
6011+
${threadIds === undefined ? sql`` : sql`AND i.thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))`}
60126012
${
60136013
activeRunIds.length === 0
60146014
? sql``
@@ -6017,7 +6017,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
60176017
UNION ALL
60186018
SELECT i.thread_id, i.payload_json, NULL AS owning_provider_instance_id
60196019
FROM orchestration_v2_projection_turn_items i
6020-
WHERE i.run_id IN ${sql.in(activeRunIds)}
6020+
WHERE i.run_id IN (SELECT value FROM json_each(${encodeIdList(activeRunIds)}))
60216021
AND i.type IN ('file_change', 'file_search', 'web_search', 'compaction')
60226022
AND i.status IN ('running', 'waiting')
60236023
`

0 commit comments

Comments
 (0)