Skip to content

Commit f8b758d

Browse files
committed
perf(server): shell reads stop counting every turn item
Shell snapshots and getThreadShell counted each thread's items by walking the (thread_id, run_id) index, one entry per item. Migration 076 keeps one count per (thread, run) with triggers on turn items, and the shell count queries read it.
1 parent 2b15ba0 commit f8b758d

6 files changed

Lines changed: 135 additions & 17 deletions

File tree

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

Lines changed: 8 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -6079,27 +6079,21 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
60796079

60806080
const selectShellRunItemCounts = (threadIds: ReadonlyArray<ThreadId>) =>
60816081
sql<ShellRunItemCountRow>`
6082-
SELECT thread_id, run_id, COUNT(*) AS item_count
6083-
FROM orchestration_v2_projection_turn_items
6084-
WHERE run_id IS NOT NULL AND thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
6085-
GROUP BY thread_id, run_id
6082+
SELECT thread_id, run_key AS run_id, item_count
6083+
FROM orchestration_v2_projection_turn_item_counts
6084+
WHERE run_key <> '' AND thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
60866085
`;
60876086

6088-
// Count all selected threads in one index walk; fork sources still need per-run counts.
60896087
const selectShellItemCounts = (threadIds: ReadonlyArray<ThreadId>) =>
60906088
sql<ShellItemCountRow>`
60916089
SELECT counts.thread_id,
6092-
SUM(CASE WHEN counts.run_id IS NULL OR r.status <> 'rolled_back'
6090+
SUM(CASE WHEN counts.run_key = '' OR r.status <> 'rolled_back'
60936091
THEN counts.item_count ELSE 0 END) AS item_count,
6094-
SUM(CASE WHEN counts.run_id IS NULL THEN counts.item_count ELSE 0 END)
6092+
SUM(CASE WHEN counts.run_key = '' THEN counts.item_count ELSE 0 END)
60956093
AS runless_item_count
6096-
FROM (
6097-
SELECT thread_id, run_id, COUNT(*) AS item_count
6098-
FROM orchestration_v2_projection_turn_items
6099-
WHERE thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
6100-
GROUP BY thread_id, run_id
6101-
) counts
6102-
LEFT JOIN orchestration_v2_projection_runs r ON r.run_id = counts.run_id
6094+
FROM orchestration_v2_projection_turn_item_counts counts
6095+
LEFT JOIN orchestration_v2_projection_runs r ON r.run_id = counts.run_key
6096+
WHERE counts.thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
61036097
GROUP BY counts.thread_id
61046098
`;
61056099

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -78,6 +78,7 @@ import Migration0072 from "./Migrations/072_WaitingLinkRequestIndex.ts";
7878
import Migration0073 from "./Migrations/073_ActiveTurnItemIndexes.ts";
7979
import Migration0074 from "./Migrations/074_ExcludeAttachmentPlaceholdersFromSearch.ts";
8080
import Migration0075 from "./Migrations/075_SubagentDeliveryState.ts";
81+
import Migration0076 from "./Migrations/076_TurnItemCounts.ts";
8182

8283
const migrationEntries = [
8384
[1, "OrchestrationEvents", Migration0001],
@@ -156,6 +157,7 @@ const migrationEntries = [
156157
[73, "ActiveTurnItemIndexes", Migration0073],
157158
[74, "ExcludeAttachmentPlaceholdersFromSearch", Migration0074],
158159
[75, "SubagentDeliveryState", Migration0075],
160+
[76, "TurnItemCounts", Migration0076],
159161
] as const;
160162

161163
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
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 layer = it.layer(NodeSqliteClient.layer({ filename: ":memory:" }));
9+
10+
layer("076_TurnItemCounts", (it) => {
11+
it.effect("backfills item counts, then follows upserts, moves, and deletes", () =>
12+
Effect.gen(function* () {
13+
const sql = yield* SqlClient.SqlClient;
14+
yield* runMigrations({ toMigrationInclusive: 75 });
15+
const upsert = (id: string, threadId: string, runId: string | null) => sql`
16+
INSERT INTO orchestration_v2_projection_turn_items (
17+
turn_item_id, thread_id, run_id, ordinal, type, status, updated_at, payload_json
18+
)
19+
VALUES (
20+
${id}, ${threadId}, ${runId}, 1, 'assistant_message', 'completed',
21+
'2026-10-29T00:00:00.000Z', '{}'
22+
)
23+
ON CONFLICT(turn_item_id) DO UPDATE SET
24+
thread_id = excluded.thread_id,
25+
run_id = excluded.run_id,
26+
status = excluded.status
27+
`;
28+
yield* upsert("runless-1", "thread-1", null);
29+
yield* upsert("runless-2", "thread-1", null);
30+
yield* upsert("run-1", "thread-1", "run-1");
31+
32+
yield* runMigrations();
33+
34+
const counts = () =>
35+
sql<{ readonly thread_id: string; readonly run_key: string; readonly item_count: number }>`
36+
SELECT thread_id, run_key, item_count
37+
FROM orchestration_v2_projection_turn_item_counts
38+
ORDER BY thread_id, run_key
39+
`;
40+
assert.deepEqual(yield* counts(), [
41+
{ thread_id: "thread-1", run_key: "", item_count: 2 },
42+
{ thread_id: "thread-1", run_key: "run-1", item_count: 1 },
43+
]);
44+
45+
yield* upsert("run-1", "thread-1", "run-1");
46+
yield* upsert("run-2", "thread-2", "run-2");
47+
yield* upsert("runless-2", "thread-1", "run-1");
48+
assert.deepEqual(yield* counts(), [
49+
{ thread_id: "thread-1", run_key: "", item_count: 1 },
50+
{ thread_id: "thread-1", run_key: "run-1", item_count: 2 },
51+
{ thread_id: "thread-2", run_key: "run-2", item_count: 1 },
52+
]);
53+
54+
yield* sql`DELETE FROM orchestration_v2_projection_turn_items WHERE thread_id = 'thread-1'`;
55+
assert.deepEqual(yield* counts(), [
56+
{ thread_id: "thread-2", run_key: "run-2", item_count: 1 },
57+
]);
58+
}),
59+
);
60+
});
Lines changed: 60 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,60 @@
1+
import * as Effect from "effect/Effect";
2+
import * as SqlClient from "effect/sql/SqlClient";
3+
4+
/**
5+
* Shell reads count items per (thread, run) here instead of walking every turn item. Triggers
6+
* keep it exact for every writer, including projection rebuilds and dev-database pruning.
7+
*/
8+
export default Effect.gen(function* () {
9+
const sql = yield* SqlClient.SqlClient;
10+
11+
// run_key is '' for items without a run; NULLs never conflict in a primary key.
12+
yield* sql`
13+
CREATE TABLE orchestration_v2_projection_turn_item_counts (
14+
thread_id TEXT NOT NULL,
15+
run_key TEXT NOT NULL,
16+
item_count INTEGER NOT NULL,
17+
PRIMARY KEY (thread_id, run_key)
18+
) WITHOUT ROWID
19+
`;
20+
yield* sql`
21+
INSERT INTO orchestration_v2_projection_turn_item_counts (thread_id, run_key, item_count)
22+
SELECT thread_id, COALESCE(run_id, ''), COUNT(*)
23+
FROM orchestration_v2_projection_turn_items
24+
GROUP BY thread_id, run_id
25+
`;
26+
// An upsert that hits an existing item fires only the update trigger.
27+
yield* sql`
28+
CREATE TRIGGER orchestration_v2_turn_item_counts_insert
29+
AFTER INSERT ON orchestration_v2_projection_turn_items
30+
BEGIN
31+
INSERT INTO orchestration_v2_projection_turn_item_counts (thread_id, run_key, item_count)
32+
VALUES (new.thread_id, COALESCE(new.run_id, ''), 1)
33+
ON CONFLICT (thread_id, run_key) DO UPDATE SET item_count = item_count + 1;
34+
END
35+
`;
36+
yield* sql`
37+
CREATE TRIGGER orchestration_v2_turn_item_counts_update
38+
AFTER UPDATE OF thread_id, run_id ON orchestration_v2_projection_turn_items
39+
WHEN old.thread_id IS NOT new.thread_id OR old.run_id IS NOT new.run_id
40+
BEGIN
41+
UPDATE orchestration_v2_projection_turn_item_counts SET item_count = item_count - 1
42+
WHERE thread_id = old.thread_id AND run_key = COALESCE(old.run_id, '');
43+
DELETE FROM orchestration_v2_projection_turn_item_counts
44+
WHERE thread_id = old.thread_id AND run_key = COALESCE(old.run_id, '') AND item_count <= 0;
45+
INSERT INTO orchestration_v2_projection_turn_item_counts (thread_id, run_key, item_count)
46+
VALUES (new.thread_id, COALESCE(new.run_id, ''), 1)
47+
ON CONFLICT (thread_id, run_key) DO UPDATE SET item_count = item_count + 1;
48+
END
49+
`;
50+
yield* sql`
51+
CREATE TRIGGER orchestration_v2_turn_item_counts_delete
52+
AFTER DELETE ON orchestration_v2_projection_turn_items
53+
BEGIN
54+
UPDATE orchestration_v2_projection_turn_item_counts SET item_count = item_count - 1
55+
WHERE thread_id = old.thread_id AND run_key = COALESCE(old.run_id, '');
56+
DELETE FROM orchestration_v2_projection_turn_item_counts
57+
WHERE thread_id = old.thread_id AND run_key = COALESCE(old.run_id, '') AND item_count <= 0;
58+
END
59+
`;
60+
});

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -55,6 +55,7 @@ describe("V2 preview upgrade", () => {
5555
[73, "ActiveTurnItemIndexes"],
5656
[74, "ExcludeAttachmentPlaceholdersFromSearch"],
5757
[75, "SubagentDeliveryState"],
58+
[76, "TurnItemCounts"],
5859
]);
5960
assert.deepStrictEqual(yield* runMigrations(), []);
6061
assert.deepStrictEqual(yield* sql`SELECT * FROM orchestration_v2_legacy_imports`, imports);
@@ -153,6 +154,7 @@ describe("V2 preview upgrade", () => {
153154
[73, "ActiveTurnItemIndexes"],
154155
[74, "ExcludeAttachmentPlaceholdersFromSearch"],
155156
[75, "SubagentDeliveryState"],
157+
[76, "TurnItemCounts"],
156158
]);
157159
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
158160
);

‎docs/internals/performance-regressions.md‎

Lines changed: 3 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -101,9 +101,9 @@ sessions. The orchestrator still validates the current thread before applying se
101101

102102
Read receipts use `ProjectionStore.getThread` to load only canonical thread metadata. A visit
103103
advances `lastVisitedAt` monotonically without changing `updatedAt` or decoding history. Shell
104-
queries use a covering thread/run index for item counts and a partial index for nonterminal
105-
background items, including idle items. Latest user-message reads use an index ordered by update
106-
time.
104+
queries read item counts from a per-thread, per-run table that turn-item triggers keep exact, and
105+
use a partial index for nonterminal background items, including idle items. Latest user-message
106+
reads use an index ordered by update time.
107107

108108
Command palette results keep live VCS and linked pull-request queries leased to the visible
109109
viewport, including a small overscan region. Cached badges remain available while leases are

0 commit comments

Comments
 (0)