Skip to content

Commit 6e2bdf0

Browse files
braulioboclaude
andcommitted
perf(server): the chat list no longer counts every item of a long thread on each update
A thread's chat-list entry shows its item count, and every update to an active thread recounted all of its items. On a thread with about 59k items that is a 0.6-0.9 s query per update, and active threads trigger it many times a minute. ProjectionStore now keeps a per-run item count row (and whether the run counts) as items and runs are written, so the chat list sums one small row per run instead of reading every item. Migration 083 builds the rows from the existing items. Co-Authored-By: Claude Sonnet 5.5 <[email protected]>
1 parent cd8fc91 commit 6e2bdf0

7 files changed

Lines changed: 172 additions & 23 deletions

File tree

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

Lines changed: 32 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -5457,6 +5457,7 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
54575457
},
54585458
});
54595459
}
5460+
let runWrites = 0;
54605461
const applyRun = (
54615462
threadId: ThreadId,
54625463
suffix: string,
@@ -5465,9 +5466,10 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
54655466
) => {
54665467
const runId = RunId.make(`run:shell-item-counts:${suffix}`);
54675468
const rootNodeId = NodeId.make(`node:shell-item-counts:${suffix}`);
5469+
runWrites += 1;
54685470
return projectionStore
54695471
.apply({
5470-
id: EventId.make(`event:shell-item-counts:${suffix}:run`),
5472+
id: EventId.make(`event:shell-item-counts:${suffix}:run:${runWrites}`),
54715473
type: "run.created",
54725474
threadId,
54735475
runId,
@@ -5494,11 +5496,14 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
54945496
})
54955497
.pipe(Effect.as(runId));
54965498
};
5497-
let ordinal = 0;
5498-
const applyItem = (threadId: ThreadId, runId: RunId | null) => {
5499-
ordinal += 1;
5499+
let items = 0;
5500+
let writes = 0;
5501+
// Passing `rewrite` writes that earlier item again, possibly under another run.
5502+
const applyItem = (threadId: ThreadId, runId: RunId | null, rewrite?: number) => {
5503+
const ordinal = rewrite ?? (items += 1);
5504+
writes += 1;
55005505
return projectionStore.apply({
5501-
id: EventId.make(`event:shell-item-counts:item:${ordinal}`),
5506+
id: EventId.make(`event:shell-item-counts:write:${writes}`),
55025507
type: "turn-item.updated",
55035508
threadId,
55045509
...(runId === null ? {} : { runId }),
@@ -5551,6 +5556,28 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
55515556
);
55525557
assert.equal(shell?.itemCount, itemCount);
55535558
}
5559+
5560+
// Writing an item again keeps its count; moving it to another run moves the count.
5561+
const itemCountOf = (threadId: ThreadId) =>
5562+
projectionStore.getThreadShell(threadId).pipe(Effect.map((shell) => shell?.itemCount));
5563+
yield* applyItem(busyThreadId, completedRunId, 3);
5564+
assert.equal(yield* itemCountOf(busyThreadId), 4);
5565+
yield* applyItem(busyThreadId, rolledBackRunId, 3);
5566+
assert.equal(yield* itemCountOf(busyThreadId), 3);
5567+
yield* applyItem(busyThreadId, null, 3);
5568+
assert.equal(yield* itemCountOf(busyThreadId), 4);
5569+
5570+
// A run's rollback uncounts its items, and writing the run again counts them back.
5571+
yield* applyRun(busyThreadId, "busy-completed", 1, true);
5572+
assert.equal(yield* itemCountOf(busyThreadId), 3);
5573+
yield* applyRun(busyThreadId, "busy-completed", 1, false);
5574+
assert.equal(yield* itemCountOf(busyThreadId), 4);
5575+
// Items written before their run exist count once it does.
5576+
const lateRunId = RunId.make("run:shell-item-counts:late");
5577+
yield* applyItem(otherThreadId, lateRunId);
5578+
assert.equal(yield* itemCountOf(otherThreadId), 1);
5579+
yield* applyRun(otherThreadId, "late", 2, false);
5580+
assert.equal(yield* itemCountOf(otherThreadId), 2);
55545581
}),
55555582
);
55565583

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

Lines changed: 51 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -2028,6 +2028,24 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
20282028
`(${DEFERRED_WORK_ITEM_TYPES.map((type) => `'${type}'`).join(", ")})`,
20292029
);
20302030

2031+
// The shell counts a thread's items from these rows, so it never reads the thread's items.
2032+
// Items without a run count under run_id ''. `counted` mirrors whether the run exists and was
2033+
// not rolled back; run events keep it current.
2034+
const bumpRunItemCount = (threadId: ThreadId, runId: RunId | null, delta: 1 | -1) => sql`
2035+
INSERT INTO orchestration_v2_projection_run_item_counts (thread_id, run_id, item_count, counted)
2036+
VALUES (
2037+
${threadId}, ${runId ?? ""}, ${delta},
2038+
${
2039+
runId === null
2040+
? sql`1`
2041+
: sql`COALESCE(
2042+
(SELECT status <> 'rolled_back' FROM orchestration_v2_projection_runs
2043+
WHERE run_id = ${runId}), 0)`
2044+
}
2045+
)
2046+
ON CONFLICT(thread_id, run_id) DO UPDATE SET item_count = item_count + excluded.item_count
2047+
`;
2048+
20312049
// A todo list is never closed when its run ends, so a long thread holds an active one per
20322050
// old run. Counting those as live work drags each run's nodes and subagents into every window.
20332051
const todoListOfSettledRun = (row: Statement.Fragment) => sql`(
@@ -2230,6 +2248,12 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
22302248
keepRecordedRunField(sql`excluded.payload_json`, "$.delegatedCompletion"),
22312249
"$.restartCancelledBackgroundWork",
22322250
)}
2251+
`;
2252+
yield* sql`
2253+
UPDATE orchestration_v2_projection_run_item_counts
2254+
SET counted = ${event.payload.status === "rolled_back" ? 0 : 1}
2255+
WHERE thread_id = ${event.payload.threadId} AND run_id = ${event.payload.id}
2256+
AND counted <> ${event.payload.status === "rolled_back" ? 0 : 1}
22332257
`;
22342258
break;
22352259
}
@@ -2724,6 +2748,13 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
27242748
case "turn-item.updated": {
27252749
const payloadJson = yield* encodeTurnItemPayload(event.payload);
27262750
const updatedAt = yield* encodePayloadTimestamp(event.payload.updatedAt);
2751+
const [previous] = yield* sql<{
2752+
readonly thread_id: ThreadId;
2753+
readonly run_id: RunId | null;
2754+
}>`
2755+
SELECT thread_id, run_id FROM orchestration_v2_projection_turn_items
2756+
WHERE turn_item_id = ${event.payload.id}
2757+
`;
27272758
yield* sql`
27282759
INSERT INTO orchestration_v2_projection_turn_items (
27292760
turn_item_id,
@@ -2767,6 +2798,15 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
27672798
updated_at = excluded.updated_at,
27682799
payload_json = excluded.payload_json
27692800
`;
2801+
if (
2802+
previous?.thread_id !== event.payload.threadId ||
2803+
previous.run_id !== event.payload.runId
2804+
) {
2805+
if (previous !== undefined) {
2806+
yield* bumpRunItemCount(previous.thread_id, previous.run_id, -1);
2807+
}
2808+
yield* bumpRunItemCount(event.payload.threadId, event.payload.runId, 1);
2809+
}
27702810
break;
27712811
}
27722812
case "checkpoint-scope.created": {
@@ -6719,28 +6759,21 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
67196759

67206760
const selectShellRunItemCounts = (threadIds: ReadonlyArray<ThreadId>) =>
67216761
sql<ShellRunItemCountRow>`
6722-
SELECT thread_id, run_id, COUNT(*) AS item_count
6723-
FROM orchestration_v2_projection_turn_items
6724-
WHERE run_id IS NOT NULL AND thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
6725-
GROUP BY thread_id, run_id
6762+
SELECT thread_id, run_id, item_count
6763+
FROM orchestration_v2_projection_run_item_counts
6764+
WHERE run_id <> '' AND item_count > 0
6765+
AND thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
67266766
`;
67276767

6728-
// Count all selected threads in one index walk; fork sources still need per-run counts.
6768+
// Fork sources still need per-run counts.
67296769
const selectShellItemCounts = (threadIds: ReadonlyArray<ThreadId>) =>
67306770
sql<ShellItemCountRow>`
6731-
SELECT counts.thread_id,
6732-
SUM(CASE WHEN counts.run_id IS NULL OR r.status <> 'rolled_back'
6733-
THEN counts.item_count ELSE 0 END) AS item_count,
6734-
SUM(CASE WHEN counts.run_id IS NULL THEN counts.item_count ELSE 0 END)
6735-
AS runless_item_count
6736-
FROM (
6737-
SELECT thread_id, run_id, COUNT(*) AS item_count
6738-
FROM orchestration_v2_projection_turn_items
6739-
WHERE thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
6740-
GROUP BY thread_id, run_id
6741-
) counts
6742-
LEFT JOIN orchestration_v2_projection_runs r ON r.run_id = counts.run_id
6743-
GROUP BY counts.thread_id
6771+
SELECT thread_id,
6772+
SUM(CASE WHEN counted = 1 THEN item_count ELSE 0 END) AS item_count,
6773+
SUM(CASE WHEN run_id = '' THEN item_count ELSE 0 END) AS runless_item_count
6774+
FROM orchestration_v2_projection_run_item_counts
6775+
WHERE thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
6776+
GROUP BY thread_id
67446777
`;
67456778

67466779
const selectShellProviderThreadRows = (threadIds?: ReadonlyArray<ThreadId>) =>

‎apps/server/src/orchestration-v2/testkit/ProjectionReplay.ts‎

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -108,6 +108,7 @@ export const layer: Layer.Layer<
108108
yield* sql`DELETE FROM orchestration_v2_projection_checkpoints`;
109109
yield* sql`DELETE FROM orchestration_v2_projection_checkpoint_scopes`;
110110
yield* sql`DELETE FROM orchestration_v2_projection_turn_items`;
111+
yield* sql`DELETE FROM orchestration_v2_projection_run_item_counts`;
111112
yield* sql`DELETE FROM orchestration_v2_projection_plans`;
112113
yield* sql`DELETE FROM orchestration_v2_projection_messages`;
113114
yield* sql`DELETE FROM orchestration_v2_projection_runtime_requests`;

‎apps/server/src/persistence/Migrations.ts‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -85,6 +85,7 @@ import Migration0079 from "./Migrations/079_DelegatedRecoveryIndexes.ts";
8585
import Migration0080 from "./Migrations/080_ScheduledTaskThreadMode.ts";
8686
import Migration0081 from "./Migrations/081_ProjectionMessageSearchText.ts";
8787
import Migration0082 from "./Migrations/082_TurnItemConversationIndex.ts";
88+
import Migration0083 from "./Migrations/083_RunItemCounts.ts";
8889

8990
const migrationEntries = [
9091
[1, "OrchestrationEvents", Migration0001],
@@ -170,6 +171,7 @@ const migrationEntries = [
170171
[80, "ScheduledTaskThreadMode", Migration0080],
171172
[81, "ProjectionMessageSearchText", Migration0081],
172173
[82, "TurnItemConversationIndex", Migration0082],
174+
[83, "RunItemCounts", Migration0083],
173175
] as const;
174176

175177
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 53 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,53 @@
1+
import { assert, it } from "@effect/vitest";
2+
import * as NodeSqliteClient from "@t3tools/shared/nodeSqliteClient";
3+
import * as Effect from "effect/Effect";
4+
import * as SqlClient from "effect/sql/SqlClient";
5+
6+
import { runMigrations } from "../Migrations.ts";
7+
8+
const at = "2026-10-01T00:00:00.000Z";
9+
10+
it.layer(NodeSqliteClient.layer({ filename: ":memory:" }))("083_RunItemCounts", (it) => {
11+
it.effect(
12+
"counts the existing items per run, marking rolled-back and missing runs uncounted",
13+
() =>
14+
Effect.gen(function* () {
15+
const sql = yield* SqlClient.SqlClient;
16+
yield* runMigrations({ toMigrationInclusive: 82 });
17+
const insertItem = (id: string, threadId: string, runId: string | null) => sql`
18+
INSERT INTO orchestration_v2_projection_turn_items (
19+
turn_item_id, thread_id, run_id, ordinal, type, status, updated_at, payload_json
20+
) VALUES (${id}, ${threadId}, ${runId}, 1, 'user_message', 'completed', ${at}, '{}')
21+
`;
22+
const insertRun = (runId: string, ordinal: number, status: string) => sql`
23+
INSERT INTO orchestration_v2_projection_runs (
24+
run_id, thread_id, ordinal, provider, status, requested_at, payload_json
25+
) VALUES (${runId}, 'thread:a', ${ordinal}, 'codex', ${status}, ${at}, '{}')
26+
`;
27+
yield* insertRun("run:1", 1, "completed");
28+
yield* insertRun("run:2", 2, "rolled_back");
29+
yield* insertItem("a1", "thread:a", "run:1");
30+
yield* insertItem("a2", "thread:a", "run:1");
31+
yield* insertItem("a3", "thread:a", "run:2");
32+
yield* insertItem("a4", "thread:a", null);
33+
yield* insertItem("b1", "thread:b", "run:3");
34+
35+
assert.deepEqual(yield* runMigrations({ toMigrationInclusive: 83 }), [
36+
[83, "RunItemCounts"],
37+
]);
38+
assert.deepEqual(
39+
yield* sql`
40+
SELECT thread_id, run_id, item_count, counted
41+
FROM orchestration_v2_projection_run_item_counts
42+
ORDER BY thread_id, run_id
43+
`,
44+
[
45+
{ thread_id: "thread:a", run_id: "", item_count: 1, counted: 1 },
46+
{ thread_id: "thread:a", run_id: "run:1", item_count: 2, counted: 1 },
47+
{ thread_id: "thread:a", run_id: "run:2", item_count: 1, counted: 0 },
48+
{ thread_id: "thread:b", run_id: "run:3", item_count: 1, counted: 0 },
49+
],
50+
);
51+
}),
52+
);
53+
});
Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,31 @@
1+
import * as Effect from "effect/Effect";
2+
import * as SqlClient from "effect/sql/SqlClient";
3+
4+
export default Effect.gen(function* () {
5+
const sql = yield* SqlClient.SqlClient;
6+
7+
// A thread's shell shows how many items it holds. ProjectionStore keeps these counts as items
8+
// and runs are written, so the shell no longer counts every item of a long thread on each update.
9+
// Items without a run count under run_id ''. `counted` is 1 for a run that exists and was not
10+
// rolled back, and for items without a run.
11+
yield* sql`
12+
CREATE TABLE orchestration_v2_projection_run_item_counts (
13+
thread_id TEXT NOT NULL,
14+
run_id TEXT NOT NULL,
15+
item_count INTEGER NOT NULL,
16+
counted INTEGER NOT NULL,
17+
PRIMARY KEY (thread_id, run_id)
18+
) WITHOUT ROWID
19+
`;
20+
yield* sql`
21+
INSERT INTO orchestration_v2_projection_run_item_counts (thread_id, run_id, item_count, counted)
22+
SELECT counts.thread_id, counts.run_id, counts.item_count,
23+
CASE WHEN counts.run_id = '' OR run.status <> 'rolled_back' THEN 1 ELSE 0 END
24+
FROM (
25+
SELECT thread_id, COALESCE(run_id, '') AS run_id, COUNT(*) AS item_count
26+
FROM orchestration_v2_projection_turn_items
27+
GROUP BY thread_id, COALESCE(run_id, '')
28+
) AS counts
29+
LEFT JOIN orchestration_v2_projection_runs AS run ON run.run_id = counts.run_id
30+
`;
31+
});

‎apps/server/src/persistence/reconcileV2PreviewMigration.test.ts‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -62,6 +62,7 @@ describe("V2 preview upgrade", () => {
6262
[80, "ScheduledTaskThreadMode"],
6363
[81, "ProjectionMessageSearchText"],
6464
[82, "TurnItemConversationIndex"],
65+
[83, "RunItemCounts"],
6566
]);
6667
assert.deepStrictEqual(yield* runMigrations(), []);
6768
assert.deepStrictEqual(yield* sql`SELECT * FROM orchestration_v2_legacy_imports`, imports);
@@ -167,6 +168,7 @@ describe("V2 preview upgrade", () => {
167168
[80, "ScheduledTaskThreadMode"],
168169
[81, "ProjectionMessageSearchText"],
169170
[82, "TurnItemConversationIndex"],
171+
[83, "RunItemCounts"],
170172
]);
171173
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
172174
);

0 commit comments

Comments
 (0)