Skip to content

Commit 3e03072

Browse files
t3dotggbowdjoei4cmPysephbrauliobo
authored
perf(server): thread list reads no longer scan a long thread's runs and items (#35167)
Replaces #34747, #31828, #34666 and #27975. Co-authored-by: Bogdan <[email protected]> Co-authored-by: Michael Zhou <[email protected]> Co-authored-by: Pyseph <[email protected]> Co-authored-by: Braulio Oliveira <[email protected]> Co-Authored-By: Claude Fable 5.2 <[email protected]>
1 parent fc6c7c8 commit 3e03072

9 files changed

Lines changed: 227 additions & 28 deletions

File tree

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3956,6 +3956,7 @@ it.layer(layerTest)("orchestration V2 foundation persistence", (it) => {
39563956
});
39573957
const afterUpdate = yield* projectionStore.getThreadProjection(threadId);
39583958
assert.equal(afterUpdate.turnItems[0]?.ordinal, ordinals[0]);
3959+
assert.equal((yield* projectionStore.getThreadShell(threadId))?.itemCount, 151);
39593960
assert.equal((yield* maintenance.verify).valid, true);
39603961

39613962
yield* sql`
@@ -3970,6 +3971,7 @@ it.layer(layerTest)("orchestration V2 foundation persistence", (it) => {
39703971
const rebuilt = yield* maintenance.rebuild;
39713972
assert.isTrue(rebuilt.valid);
39723973
assert.lengthOf((yield* projectionStore.getThreadProjection(threadId)).turnItems, 151);
3974+
assert.equal((yield* projectionStore.getThreadShell(threadId))?.itemCount, 151);
39733975
}),
39743976
);
39753977
});

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

Lines changed: 62 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -5359,7 +5359,7 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
53595359
}),
53605360
);
53615361

5362-
it.effect("looks up shell stop requests through the interrupt request index", () =>
5362+
it.effect("reads shell runs and item counts without walking a thread's history", () =>
53635363
Effect.gen(function* () {
53645364
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;
53655365
const sql = yield* SqlClient.SqlClient;
@@ -5368,20 +5368,44 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
53685368
yield* projectionStore
53695369
.getShellSnapshot({ projectId, location: "active" })
53705370
.pipe(Effect.withTracer(tracer));
5371+
const planOf = (statement: string) =>
5372+
sql
5373+
.unsafe<{ readonly detail: string }>(`EXPLAIN QUERY PLAN ${statement}`)
5374+
.pipe(Effect.map((rows) => rows.map((row) => row.detail)));
53715375

53725376
const threadQuery = statements.find((statement) =>
53735377
statement.includes("AS latest_run_stop_requested"),
53745378
);
53755379
assert.isDefined(threadQuery);
5376-
const stopRequestLookups = (yield* sql.unsafe<{ readonly detail: string }>(
5377-
`EXPLAIN QUERY PLAN ${threadQuery}`,
5378-
))
5379-
.map((row) => row.detail)
5380-
.filter((detail) => detail.startsWith("SEARCH stop_request "));
5380+
const plan = yield* planOf(threadQuery);
5381+
const stopRequestLookups = plan.filter((detail) => detail.startsWith("SEARCH stop_request "));
53815382
assert.lengthOf(stopRequestLookups, 2);
53825383
for (const lookup of stopRequestLookups) {
53835384
assert.include(lookup, "orchestration_v2_projection_turn_items_stop_request_idx");
53845385
}
5386+
// The active, activity, and pending-secret lookups; walking the ordinal index instead
5387+
// reads every run of an idle thread.
5388+
const activeRunLookups = plan.filter(
5389+
(detail) => detail.startsWith("SEARCH r ") && detail.includes("(thread_id=?"),
5390+
);
5391+
assert.lengthOf(activeRunLookups, 4);
5392+
for (const lookup of activeRunLookups) {
5393+
assert.include(lookup, "orchestration_v2_projection_runs_thread_status_idx");
5394+
}
5395+
const lastExecutedIndex = plan.findIndex((detail) =>
5396+
detail.includes("orchestration_v2_projection_runs_last_executed_idx"),
5397+
);
5398+
assert.notStrictEqual(lastExecutedIndex, -1);
5399+
// The lookup that follows the search is the sort it no longer needs.
5400+
assert.notInclude(plan[lastExecutedIndex + 1] ?? "", "USE TEMP B-TREE");
5401+
5402+
const countQueries = statements.filter((statement) => statement.includes("item_count"));
5403+
assert.isNotEmpty(countQueries);
5404+
for (const countQuery of countQueries) {
5405+
for (const detail of yield* planOf(countQuery)) {
5406+
assert.notInclude(detail, "orchestration_v2_projection_turn_items");
5407+
}
5408+
}
53855409
}),
53865410
);
53875411

@@ -5636,6 +5660,7 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
56365660
},
56375661
});
56385662
}
5663+
let runWrites = 0;
56395664
const applyRun = (
56405665
threadId: ThreadId,
56415666
suffix: string,
@@ -5644,9 +5669,10 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
56445669
) => {
56455670
const runId = RunId.make(`run:shell-item-counts:${suffix}`);
56465671
const rootNodeId = NodeId.make(`node:shell-item-counts:${suffix}`);
5672+
runWrites += 1;
56475673
return projectionStore
56485674
.apply({
5649-
id: EventId.make(`event:shell-item-counts:${suffix}:run`),
5675+
id: EventId.make(`event:shell-item-counts:${suffix}:run:${runWrites}`),
56505676
type: "run.created",
56515677
threadId,
56525678
runId,
@@ -5673,11 +5699,14 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
56735699
})
56745700
.pipe(Effect.as(runId));
56755701
};
5676-
let ordinal = 0;
5677-
const applyItem = (threadId: ThreadId, runId: RunId | null) => {
5678-
ordinal += 1;
5702+
let items = 0;
5703+
let writes = 0;
5704+
// Passing `rewrite` writes that earlier item again, possibly under another run.
5705+
const applyItem = (threadId: ThreadId, runId: RunId | null, rewrite?: number) => {
5706+
const ordinal = rewrite ?? (items += 1);
5707+
writes += 1;
56795708
return projectionStore.apply({
5680-
id: EventId.make(`event:shell-item-counts:item:${ordinal}`),
5709+
id: EventId.make(`event:shell-item-counts:write:${writes}`),
56815710
type: "turn-item.updated",
56825711
threadId,
56835712
...(runId === null ? {} : { runId }),
@@ -5730,6 +5759,28 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
57305759
);
57315760
assert.equal(shell?.itemCount, itemCount);
57325761
}
5762+
5763+
// Writing an item again keeps its count; moving it to another run moves the count.
5764+
const itemCountOf = (threadId: ThreadId) =>
5765+
projectionStore.getThreadShell(threadId).pipe(Effect.map((shell) => shell?.itemCount));
5766+
yield* applyItem(busyThreadId, completedRunId, 3);
5767+
assert.equal(yield* itemCountOf(busyThreadId), 4);
5768+
yield* applyItem(busyThreadId, rolledBackRunId, 3);
5769+
assert.equal(yield* itemCountOf(busyThreadId), 3);
5770+
yield* applyItem(busyThreadId, null, 3);
5771+
assert.equal(yield* itemCountOf(busyThreadId), 4);
5772+
5773+
// A run's rollback uncounts its items, and writing the run again counts them back.
5774+
yield* applyRun(busyThreadId, "busy-completed", 1, true);
5775+
assert.equal(yield* itemCountOf(busyThreadId), 3);
5776+
yield* applyRun(busyThreadId, "busy-completed", 1, false);
5777+
assert.equal(yield* itemCountOf(busyThreadId), 4);
5778+
// Items written before their run exist count once it does.
5779+
const lateRunId = RunId.make("run:shell-item-counts:late");
5780+
yield* applyItem(otherThreadId, lateRunId);
5781+
assert.equal(yield* itemCountOf(otherThreadId), 1);
5782+
yield* applyRun(otherThreadId, "late", 2, false);
5783+
assert.equal(yield* itemCountOf(otherThreadId), 2);
57335784
}),
57345785
);
57355786

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

Lines changed: 14 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -6727,7 +6727,10 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
67276727
active.status AS active_run_status,
67286728
(
67296729
SELECT r.status
6730+
-- Read active runs by status. Walking the ordinal index to skip this sort
6731+
-- reads every run of a thread with none active.
67306732
FROM orchestration_v2_projection_runs r
6733+
INDEXED BY orchestration_v2_projection_runs_thread_status_idx
67316734
WHERE r.thread_id = t.thread_id
67326735
AND r.status IN ('preparing', 'starting', 'running', 'waiting')
67336736
AND NOT ${isSilentShellRun("r")}
@@ -6742,6 +6745,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
67426745
r.requested_at
67436746
)
67446747
FROM orchestration_v2_projection_runs r
6748+
INDEXED BY orchestration_v2_projection_runs_thread_status_idx
67456749
WHERE r.thread_id = t.thread_id
67466750
AND r.status IN ('preparing', 'starting', 'running', 'waiting')
67476751
AND NOT ${isSilentShellRun("r")}
@@ -6804,6 +6808,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
68046808
SELECT secret.payload_json
68056809
-- Keep runs outermost so completed history is never scanned for a pending secret.
68066810
FROM orchestration_v2_projection_runs r
6811+
INDEXED BY orchestration_v2_projection_runs_thread_status_idx
68076812
CROSS JOIN orchestration_v2_projection_turn_items secret
68086813
INDEXED BY orchestration_v2_projection_turn_items_thread_run_idx
68096814
WHERE r.thread_id = t.thread_id
@@ -6852,6 +6857,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
68526857
LEFT JOIN orchestration_v2_projection_runs active ON active.run_id = (
68536858
SELECT r.run_id
68546859
FROM orchestration_v2_projection_runs r
6860+
INDEXED BY orchestration_v2_projection_runs_thread_status_idx
68556861
WHERE r.thread_id = t.thread_id
68566862
AND r.status IN ('preparing', 'starting', 'running')
68576863
AND NOT ${isSilentShellRun("r")}
@@ -6923,27 +6929,21 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
69236929

69246930
const selectShellRunItemCounts = (threadIds: ReadonlyArray<ThreadId>) =>
69256931
sql<ShellRunItemCountRow>`
6926-
SELECT thread_id, run_id, COUNT(*) AS item_count
6927-
FROM orchestration_v2_projection_turn_items
6928-
WHERE run_id IS NOT NULL AND thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
6929-
GROUP BY thread_id, run_id
6932+
SELECT thread_id, run_key AS run_id, item_count
6933+
FROM orchestration_v2_projection_turn_item_counts
6934+
WHERE run_key <> '' AND thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
69306935
`;
69316936

6932-
// Count all selected threads in one index walk; fork sources still need per-run counts.
69336937
const selectShellItemCounts = (threadIds: ReadonlyArray<ThreadId>) =>
69346938
sql<ShellItemCountRow>`
69356939
SELECT counts.thread_id,
6936-
SUM(CASE WHEN counts.run_id IS NULL OR r.status <> 'rolled_back'
6940+
SUM(CASE WHEN counts.run_key = '' OR r.status <> 'rolled_back'
69376941
THEN counts.item_count ELSE 0 END) AS item_count,
6938-
SUM(CASE WHEN counts.run_id IS NULL THEN counts.item_count ELSE 0 END)
6942+
SUM(CASE WHEN counts.run_key = '' THEN counts.item_count ELSE 0 END)
69396943
AS runless_item_count
6940-
FROM (
6941-
SELECT thread_id, run_id, COUNT(*) AS item_count
6942-
FROM orchestration_v2_projection_turn_items
6943-
WHERE thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
6944-
GROUP BY thread_id, run_id
6945-
) counts
6946-
LEFT JOIN orchestration_v2_projection_runs r ON r.run_id = counts.run_id
6944+
FROM orchestration_v2_projection_turn_item_counts counts
6945+
LEFT JOIN orchestration_v2_projection_runs r ON r.run_id = counts.run_key
6946+
WHERE counts.thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
69476947
GROUP BY counts.thread_id
69486948
`;
69496949

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -86,6 +86,8 @@ import Migration0080 from "./Migrations/080_ScheduledTaskThreadMode.ts";
8686
import Migration0081 from "./Migrations/081_ProjectionMessageSearchText.ts";
8787
import Migration0082 from "./Migrations/082_TurnItemConversationIndex.ts";
8888
import Migration0083 from "./Migrations/083_CommandReadIndexes.ts";
89+
import Migration0084 from "./Migrations/084_LastExecutedRunIndex.ts";
90+
import Migration0085 from "./Migrations/085_TurnItemCounts.ts";
8991

9092
const migrationEntries = [
9193
[1, "OrchestrationEvents", Migration0001],
@@ -172,6 +174,8 @@ const migrationEntries = [
172174
[81, "ProjectionMessageSearchText", Migration0081],
173175
[82, "TurnItemConversationIndex", Migration0082],
174176
[83, "CommandReadIndexes", Migration0083],
177+
[84, "LastExecutedRunIndex", Migration0084],
178+
[85, "TurnItemCounts", Migration0085],
175179
] as const;
176180

177181
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 18 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,18 @@
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+
// The shell's last executed run lookup sorts every run of the thread. This index carries
8+
// that order and the same predicate the lookup filters on. A database that already has
9+
// the index keeps it, so a hand-applied one does not fail the migration.
10+
yield* sql`
11+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_runs_last_executed_idx
12+
ON orchestration_v2_projection_runs(
13+
thread_id, (completed_at IS NULL) DESC, completed_at DESC, ordinal DESC, run_id DESC
14+
)
15+
WHERE status <> 'queued'
16+
AND NOT (status = 'cancelled' AND json_extract(payload_json, '$.startedAt') IS NULL)
17+
`;
18+
});
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("085_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: 84 });
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: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -63,6 +63,8 @@ describe("V2 preview upgrade", () => {
6363
[81, "ProjectionMessageSearchText"],
6464
[82, "TurnItemConversationIndex"],
6565
[83, "CommandReadIndexes"],
66+
[84, "LastExecutedRunIndex"],
67+
[85, "TurnItemCounts"],
6668
]);
6769
assert.deepStrictEqual(yield* runMigrations(), []);
6870
assert.deepStrictEqual(yield* sql`SELECT * FROM orchestration_v2_legacy_imports`, imports);
@@ -169,6 +171,8 @@ describe("V2 preview upgrade", () => {
169171
[81, "ProjectionMessageSearchText"],
170172
[82, "TurnItemConversationIndex"],
171173
[83, "CommandReadIndexes"],
174+
[84, "LastExecutedRunIndex"],
175+
[85, "TurnItemCounts"],
172176
]);
173177
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
174178
);

0 commit comments

Comments
 (0)