Skip to content

Commit a947a7c

Browse files
perf(server): cover unfinished item recovery lookups
1 parent dcc8536 commit a947a7c

2 files changed

Lines changed: 137 additions & 1 deletion

File tree

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

Lines changed: 132 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1110,7 +1110,7 @@ const assertProjectScopedShells = Effect.fn("assertProjectScopedShells")(functio
11101110
});
11111111

11121112
it.layer(layerTest)("ProjectionStoreV2", (it) => {
1113-
it.effect("uses the last-executed index in the thread-shell list query", () =>
1113+
it.effect("uses the lookup indexes in the thread-shell list query", () =>
11141114
Effect.gen(function* () {
11151115
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;
11161116
const sql = yield* SqlClient.SqlClient;
@@ -1139,6 +1139,59 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
11391139
subquerySteps.some((row) => row.detail.includes("TEMP B-TREE")),
11401140
JSON.stringify(subquerySteps),
11411141
);
1142+
assert.lengthOf(
1143+
plan.filter((row) =>
1144+
row.detail.includes(
1145+
"SEARCH stop_request USING COVERING INDEX orchestration_v2_projection_turn_items_interrupt_idx (thread_id=? AND run_id=?)",
1146+
),
1147+
),
1148+
2,
1149+
JSON.stringify(plan),
1150+
);
1151+
}),
1152+
);
1153+
1154+
it.effect("uses a covering unfinished-status lookup in the recovery run query", () =>
1155+
Effect.gen(function* () {
1156+
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;
1157+
const sql = yield* SqlClient.SqlClient;
1158+
const threadId = yield* addRolledBackRecoveryCandidate("recovery-status-index");
1159+
// Reproduce the competing historical-type and ordered unfinished access paths.
1160+
yield* sql`CREATE INDEX orchestration_v2_projection_turn_items_thread_type_idx
1161+
ON orchestration_v2_projection_turn_items(thread_id, type)`;
1162+
yield* sql`CREATE INDEX orchestration_v2_projection_turn_items_active_idx
1163+
ON orchestration_v2_projection_turn_items(thread_id, ordinal, turn_item_id)
1164+
WHERE status IN ('pending','running','waiting')`;
1165+
yield* Effect.gen(function* () {
1166+
const { statements, tracer } = traceSqlStatements();
1167+
yield* projectionStore
1168+
.getRuntimeRecoveryProjection(threadId)
1169+
.pipe(Effect.withTracer(tracer));
1170+
const statement = statements.find((query) =>
1171+
query.includes("SELECT payload_json FROM orchestration_v2_projection_runs AS run"),
1172+
);
1173+
assert.isDefined(statement);
1174+
const plan = yield* sql.unsafe<{ readonly detail: string }>(
1175+
`EXPLAIN QUERY PLAN ${statement}`,
1176+
);
1177+
assert.isTrue(
1178+
plan.some((row) =>
1179+
row.detail.includes(
1180+
"SEARCH item USING COVERING INDEX orchestration_v2_projection_turn_items_unfinished_status_idx (thread_id=? AND status=? AND type=?",
1181+
),
1182+
),
1183+
JSON.stringify(plan),
1184+
);
1185+
}).pipe(
1186+
Effect.ensuring(
1187+
Effect.orDie(
1188+
Effect.gen(function* () {
1189+
yield* sql`DROP INDEX orchestration_v2_projection_turn_items_thread_type_idx`;
1190+
yield* sql`DROP INDEX orchestration_v2_projection_turn_items_active_idx`;
1191+
}),
1192+
),
1193+
),
1194+
);
11421195
}),
11431196
);
11441197

@@ -5738,6 +5791,31 @@ const assertThreadShellIndexes = Effect.fn("test.assertThreadShellIndexes")(func
57385791
interruptKeys.map((column) => column.desc),
57395792
[0, 0],
57405793
);
5794+
const unfinishedDefinitions = yield* sql<{ readonly sql: string }>`
5795+
SELECT sql FROM sqlite_master
5796+
WHERE type = 'index' AND name = 'orchestration_v2_projection_turn_items_unfinished_status_idx'
5797+
`;
5798+
assert.lengthOf(unfinishedDefinitions, 1);
5799+
assert.include(
5800+
normalizeIndexSql(unfinishedDefinitions[0]!.sql),
5801+
normalizeIndexSql(
5802+
"ON orchestration_v2_projection_turn_items(thread_id,status,type,run_id,node_id,provider_thread_id) WHERE status IN ('pending','running','waiting')",
5803+
),
5804+
);
5805+
const unfinishedColumns = yield* sql<{
5806+
readonly name: string | null;
5807+
readonly desc: number;
5808+
readonly key: number;
5809+
}>`PRAGMA index_xinfo('orchestration_v2_projection_turn_items_unfinished_status_idx')`;
5810+
const unfinishedKeys = unfinishedColumns.filter((column) => column.key === 1);
5811+
assert.deepEqual(
5812+
unfinishedKeys.map((column) => column.name),
5813+
["thread_id", "status", "type", "run_id", "node_id", "provider_thread_id"],
5814+
);
5815+
assert.deepEqual(
5816+
unfinishedKeys.map((column) => column.desc),
5817+
[0, 0, 0, 0, 0, 0],
5818+
);
57415819
});
57425820

57435821
const seedProjectionLookupRows = Effect.fn("test.seedProjectionLookupRows")(function* () {
@@ -5770,6 +5848,42 @@ const seedProjectionLookupRows = Effect.fn("test.seedProjectionLookupRows")(func
57705848
(turn_item_id, thread_id, run_id, ordinal, type, status, updated_at, payload_json)
57715849
VALUES (${id}, ${threadId}, ${runId}, 1, ${type}, 'completed', '2026-01-01', '{}')`;
57725850
}
5851+
for (const [id, threadId, status, type, runId, nodeId, providerThreadId] of [
5852+
[
5853+
"unfinished-pending",
5854+
"lookup-thread",
5855+
"pending",
5856+
"command_execution",
5857+
"run-1",
5858+
"node-1",
5859+
"provider-1",
5860+
],
5861+
["unfinished-running", "lookup-thread", "running", "dynamic_tool", "run-2", null, "provider-2"],
5862+
["unfinished-waiting", "lookup-thread", "waiting", "subagent", null, "node-3", null],
5863+
[
5864+
"finished-command",
5865+
"lookup-thread",
5866+
"completed",
5867+
"command_execution",
5868+
"run-1",
5869+
"node-1",
5870+
"provider-1",
5871+
],
5872+
[
5873+
"other-thread-pending",
5874+
"other-thread",
5875+
"pending",
5876+
"command_execution",
5877+
"other-run",
5878+
"other-node",
5879+
"other-provider",
5880+
],
5881+
["pending-message", "lookup-thread", "pending", "assistant_message", "run-4", null, null],
5882+
] as const) {
5883+
yield* sql`INSERT INTO orchestration_v2_projection_turn_items
5884+
(turn_item_id, thread_id, run_id, node_id, provider_thread_id, ordinal, type, status, updated_at, payload_json)
5885+
VALUES (${id}, ${threadId}, ${runId}, ${nodeId}, ${providerThreadId}, 2, ${type}, ${status}, '2026-01-01', '{}')`;
5886+
}
57735887
});
57745888

57755889
describe("ProjectionStoreV2 migration 68 thread-shell indexes", () => {
@@ -5801,8 +5915,25 @@ describe("ProjectionStoreV2 migration 68 thread-shell indexes", () => {
58015915
interruptsBefore.map((row) => row.turn_item_id),
58025916
["interrupt-1", "interrupt-2"],
58035917
);
5918+
const unfinishedLookup =
5919+
"WHERE thread_id = 'lookup-thread' AND status IN ('pending','running','waiting') AND type IN ('command_execution','dynamic_tool','subagent') ORDER BY turn_item_id";
5920+
const unfinishedBefore = yield* sql.unsafe<{ readonly turn_item_id: string }>(
5921+
`SELECT * FROM orchestration_v2_projection_turn_items NOT INDEXED ${unfinishedLookup}`,
5922+
);
5923+
assert.deepEqual(
5924+
unfinishedBefore.map((row) => row.turn_item_id),
5925+
["unfinished-pending", "unfinished-running", "unfinished-waiting"],
5926+
);
58045927
yield* runMigrations({ toMigrationInclusive: 68 });
58055928
yield* assertThreadShellIndexes();
5929+
const unfinishedIndexed = yield* sql.unsafe(
5930+
`SELECT * FROM orchestration_v2_projection_turn_items INDEXED BY orchestration_v2_projection_turn_items_unfinished_status_idx ${unfinishedLookup}`,
5931+
);
5932+
const unfinishedUnindexed = yield* sql.unsafe(
5933+
`SELECT * FROM orchestration_v2_projection_turn_items NOT INDEXED ${unfinishedLookup}`,
5934+
);
5935+
assert.deepEqual(unfinishedIndexed, unfinishedBefore);
5936+
assert.deepEqual(unfinishedIndexed, unfinishedUnindexed);
58065937
const interruptsIndexed = yield* sql.unsafe(
58075938
`SELECT * FROM orchestration_v2_projection_turn_items INDEXED BY orchestration_v2_projection_turn_items_interrupt_idx ${interruptLookup}`,
58085939
);

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

Lines changed: 5 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -17,4 +17,9 @@ export default Effect.gen(function* () {
1717
ON orchestration_v2_projection_turn_items(thread_id, run_id)
1818
WHERE type='run_interrupt_request'
1919
`;
20+
yield* sql`
21+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_turn_items_unfinished_status_idx
22+
ON orchestration_v2_projection_turn_items(thread_id, status, type, run_id, node_id, provider_thread_id)
23+
WHERE status IN ('pending','running','waiting')
24+
`;
2025
});

0 commit comments

Comments
 (0)