Skip to content

Commit 9e0d6ec

Browse files
braulioboclaude
andcommitted
fix(server): thread shell reads no longer scan every finished run
The shell query looked for live runs by walking a thread's whole ordinal index and reading each run row, and sorted every run to find the one that ended last. Use the status index for the live-run lookups and add an index matching the latest-executed-run ordering. Co-Authored-By: Claude Sonnet 5.5 <[email protected]>
1 parent c7537d0 commit 9e0d6ec

5 files changed

Lines changed: 129 additions & 0 deletions

File tree

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

Lines changed: 107 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6522,6 +6522,113 @@ it.layer(layerTest)("ProjectionStoreV2", (it) => {
65226522
}),
65236523
);
65246524

6525+
it.effect(
6526+
"reads the shell of a thread with a long finished run history without scanning it",
6527+
() =>
6528+
Effect.gen(function* () {
6529+
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;
6530+
const sql = yield* SqlClient.SqlClient;
6531+
const suffix = "shell-long-history";
6532+
const threadId = ThreadId.make(`thread:${suffix}`);
6533+
const now = yield* DateTime.now;
6534+
yield* projectionStore.apply({
6535+
id: EventId.make(`event:${suffix}:thread-created`),
6536+
type: "thread.created",
6537+
threadId,
6538+
occurredAt: now,
6539+
payload: {
6540+
createdBy: "user",
6541+
creationSource: "web",
6542+
id: threadId,
6543+
projectId: ProjectId.make(`project:${suffix}`),
6544+
title: "Long finished history",
6545+
providerInstanceId,
6546+
modelSelection,
6547+
runtimeMode: "full-access",
6548+
interactionMode: "default",
6549+
branch: null,
6550+
worktreePath: null,
6551+
activeProviderThreadId: null,
6552+
lineage: { parentThreadId: null, relationshipToParent: null, rootThreadId: threadId },
6553+
forkedFrom: null,
6554+
createdAt: now,
6555+
updatedAt: now,
6556+
archivedAt: null,
6557+
settledOverride: null,
6558+
settledAt: null,
6559+
lastVisitedAt: null,
6560+
deletedAt: null,
6561+
},
6562+
});
6563+
for (let ordinal = 1; ordinal <= 3; ordinal++) {
6564+
const runId = RunId.make(`run:${suffix}:${ordinal}`);
6565+
const rootNodeId = NodeId.make(`node:${suffix}:${ordinal}`);
6566+
yield* projectionStore.apply({
6567+
id: EventId.make(`event:${suffix}:run-${ordinal}`),
6568+
type: "run.created",
6569+
threadId,
6570+
runId,
6571+
nodeId: rootNodeId,
6572+
driver,
6573+
providerInstanceId,
6574+
occurredAt: now,
6575+
payload: {
6576+
id: runId,
6577+
threadId,
6578+
ordinal,
6579+
providerInstanceId,
6580+
modelSelection,
6581+
providerThreadId: null,
6582+
userMessageId: MessageId.make(`message:${suffix}:${ordinal}`),
6583+
rootNodeId,
6584+
activeAttemptId: null,
6585+
status: "completed" as const,
6586+
requestedAt: now,
6587+
startedAt: now,
6588+
completedAt: now,
6589+
checkpointId: null,
6590+
contextHandoffId: null,
6591+
},
6592+
});
6593+
}
6594+
6595+
const { statements, tracer } = traceSqlStatements();
6596+
const shell = yield* projectionStore
6597+
.getThreadShell(threadId)
6598+
.pipe(Effect.withTracer(tracer));
6599+
assert.isNotNull(shell);
6600+
const statement = statements.find((query) => query.includes("AS activity_run_status"));
6601+
assert.isDefined(statement);
6602+
const plan = yield* sql.unsafe<{
6603+
readonly id: number;
6604+
readonly parent: number;
6605+
readonly detail: string;
6606+
}>(`EXPLAIN QUERY PLAN ${statement}`);
6607+
// With no live run, a lookup that walks the ordinal index reads every run the thread has had.
6608+
const liveRunLookups = plan.filter((row) =>
6609+
row.detail.startsWith("SEARCH r USING INDEX orchestration_v2_projection_runs_"),
6610+
);
6611+
assert.isAbove(liveRunLookups.length, 0);
6612+
for (const lookup of liveRunLookups) {
6613+
assert.include(
6614+
lookup.detail,
6615+
"orchestration_v2_projection_runs_thread_status_idx (thread_id=? AND status=?)",
6616+
);
6617+
}
6618+
// The newest run needs no sort, but the run that ended last must come from an ordered index.
6619+
const candidateLookups = plan.filter((row) =>
6620+
row.detail.startsWith("SEARCH candidate USING INDEX orchestration_v2_projection_runs_"),
6621+
);
6622+
assert.isAbove(candidateLookups.length, 0);
6623+
const sortedCandidates = plan.filter(
6624+
(row) =>
6625+
row.detail === "USE TEMP B-TREE FOR ORDER BY" &&
6626+
candidateLookups.some((lookup) => lookup.parent === row.parent),
6627+
);
6628+
assert.deepEqual(sortedCandidates, []);
6629+
}),
6630+
);
6631+
65256632
it.effect("selects the latest waiting secret only from active runs", () =>
65266633
Effect.gen(function* () {
65276634
const projectionStore = yield* ProjectionStore.ProjectionStoreV2;

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

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6171,6 +6171,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
61716171
(
61726172
SELECT r.status
61736173
FROM orchestration_v2_projection_runs r
6174+
INDEXED BY orchestration_v2_projection_runs_thread_status_idx
61746175
WHERE r.thread_id = t.thread_id
61756176
AND r.status IN ('preparing', 'starting', 'running', 'waiting')
61766177
AND NOT ${isSilentShellRun("r")}
@@ -6185,6 +6186,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
61856186
r.requested_at
61866187
)
61876188
FROM orchestration_v2_projection_runs r
6189+
INDEXED BY orchestration_v2_projection_runs_thread_status_idx
61886190
WHERE r.thread_id = t.thread_id
61896191
AND r.status IN ('preparing', 'starting', 'running', 'waiting')
61906192
AND NOT ${isSilentShellRun("r")}
@@ -6246,6 +6248,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
62466248
SELECT secret.payload_json
62476249
-- Keep runs outermost so completed history is never scanned for a pending secret.
62486250
FROM orchestration_v2_projection_runs r
6251+
INDEXED BY orchestration_v2_projection_runs_thread_status_idx
62496252
CROSS JOIN orchestration_v2_projection_turn_items secret
62506253
INDEXED BY orchestration_v2_projection_turn_items_thread_run_idx
62516254
WHERE r.thread_id = t.thread_id
@@ -6294,6 +6297,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
62946297
LEFT JOIN orchestration_v2_projection_runs active ON active.run_id = (
62956298
SELECT r.run_id
62966299
FROM orchestration_v2_projection_runs r
6300+
INDEXED BY orchestration_v2_projection_runs_thread_status_idx
62976301
WHERE r.thread_id = t.thread_id
62986302
AND r.status IN ('preparing', 'starting', 'running')
62996303
AND NOT ${isSilentShellRun("r")}

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -83,6 +83,7 @@ import Migration0077 from "./Migrations/077_RepairBooleanSettledOverride.ts";
8383
import Migration0078 from "./Migrations/078_DropOrchestrationEventCorrelationIndex.ts";
8484
import Migration0079 from "./Migrations/079_DelegatedRecoveryIndexes.ts";
8585
import Migration0080 from "./Migrations/080_ScheduledTaskThreadMode.ts";
86+
import Migration0081 from "./Migrations/081_ProjectionRunsExecutedIndex.ts";
8687

8788
const migrationEntries = [
8889
[1, "OrchestrationEvents", Migration0001],
@@ -166,6 +167,7 @@ const migrationEntries = [
166167
[78, "DropOrchestrationEventCorrelationIndex", Migration0078],
167168
[79, "DelegatedRecoveryIndexes", Migration0079],
168169
[80, "ScheduledTaskThreadMode", Migration0080],
170+
[81, "ProjectionRunsExecutedIndex", Migration0081],
169171
] as const;
170172

171173
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 14 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,14 @@
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+
// Matches the shell's latest-executed-run ordering, so it reads the newest rows instead of sorting every run.
8+
yield* sql`
9+
CREATE INDEX orchestration_v2_projection_runs_executed_idx
10+
ON orchestration_v2_projection_runs(
11+
thread_id, (completed_at IS NULL), completed_at, ordinal, run_id
12+
)
13+
`;
14+
});

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -60,6 +60,7 @@ describe("V2 preview upgrade", () => {
6060
[78, "DropOrchestrationEventCorrelationIndex"],
6161
[79, "DelegatedRecoveryIndexes"],
6262
[80, "ScheduledTaskThreadMode"],
63+
[81, "ProjectionRunsExecutedIndex"],
6364
]);
6465
assert.deepStrictEqual(yield* runMigrations(), []);
6566
assert.deepStrictEqual(yield* sql`SELECT * FROM orchestration_v2_legacy_imports`, imports);
@@ -163,6 +164,7 @@ describe("V2 preview upgrade", () => {
163164
[78, "DropOrchestrationEventCorrelationIndex"],
164165
[79, "DelegatedRecoveryIndexes"],
165166
[80, "ScheduledTaskThreadMode"],
167+
[81, "ProjectionRunsExecutedIndex"],
166168
]);
167169
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
168170
);

0 commit comments

Comments
 (0)