Skip to content

Commit f91a7d3

Browse files
perf(server): shell reads of a worktree or family no longer parse every thread payload
Shell thread reads filtered archivedAt, worktreePath and lineage.rootThreadId with json_extract on payload_json, so even a 2-thread worktree read scanned and parsed every thread. Shell item counts joined runs by primary key just to read status, loading each run's full row. Archive filters now use the existing archived_at column. A migration adds expression indexes for the worktree and family filters (guarded by json_valid like the other payload indexes, so malformed rows stay writable) and a (run_id, status) index the item counts read through. The terminal-failure lookup reads the already-joined presented run instead of re-joining runs, which keeps its error lookup on the failed-error index. Fixes #36139 Co-Authored-By: Claude Opus 5.5 <[email protected]>
1 parent 3ae93a0 commit f91a7d3

4 files changed

Lines changed: 45 additions & 10 deletions

File tree

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

Lines changed: 15 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -7666,12 +7666,11 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
76667666
(
76677667
SELECT item.payload_json
76687668
FROM orchestration_v2_projection_turn_items item
7669-
INNER JOIN orchestration_v2_projection_runs r ON r.run_id = item.run_id
7670-
WHERE r.run_id = presented.run_id
7671-
AND r.status = 'failed'
7669+
WHERE presented.status = 'failed'
76727670
AND item.thread_id = t.thread_id
7671+
AND item.run_id = presented.run_id
76737672
AND item.type = 'error' AND item.status = 'failed'
7674-
AND item.node_id IS json_extract(r.payload_json, '$.rootNodeId')
7673+
AND item.node_id IS json_extract(presented.payload_json, '$.rootNodeId')
76757674
ORDER BY item.updated_at DESC, item.ordinal DESC, item.turn_item_id DESC
76767675
LIMIT 1
76777676
) AS terminal_failure_payload_json,
@@ -7789,9 +7788,9 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
77897788
projectId === undefined ? sql`` : sql` AND t.project_id = ${projectId}`
77907789
}${
77917790
location === "active"
7792-
? sql` AND json_extract(t.payload_json, '$.archivedAt') IS NULL`
7791+
? sql` AND t.archived_at IS NULL`
77937792
: location === "archive"
7794-
? sql` AND json_extract(t.payload_json, '$.archivedAt') IS NOT NULL`
7793+
? sql` AND t.archived_at IS NOT NULL`
77957794
: sql``
77967795
}${
77977796
unsettledOnly
@@ -7800,15 +7799,17 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
78007799
}${
78017800
familyOf === undefined
78027801
? sql``
7803-
: sql` AND json_extract(t.payload_json, '$.lineage.rootThreadId') = (
7802+
: sql` AND CASE WHEN json_valid(t.payload_json)
7803+
THEN json_extract(t.payload_json, '$.lineage.rootThreadId') END = (
78047804
SELECT json_extract(member.payload_json, '$.lineage.rootThreadId')
78057805
FROM orchestration_v2_projection_threads member
78067806
WHERE member.thread_id = ${familyOf}
78077807
)`
78087808
}${
78097809
worktreePath === undefined
78107810
? sql``
7811-
: sql` AND json_extract(t.payload_json, '$.worktreePath') = ${worktreePath}`
7811+
: sql` AND CASE WHEN json_valid(t.payload_json)
7812+
THEN json_extract(t.payload_json, '$.worktreePath') END = ${worktreePath}`
78127813
}${threadIds === undefined ? sql`` : sql` AND t.thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))`}
78137814
ORDER BY t.updated_at ASC, t.thread_id ASC
78147815
`;
@@ -7836,6 +7837,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
78367837
WHEN EXISTS (
78377838
SELECT 1
78387839
FROM orchestration_v2_projection_runs AS run
7840+
INDEXED BY orchestration_v2_projection_runs_run_status_idx
78397841
WHERE run.run_id = per_run.run_key
78407842
AND (
78417843
run.status IN ('queued', 'cancelled')
@@ -7882,7 +7884,10 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
78827884
SUM(CASE WHEN counts.run_key = '' THEN counts.item_count ELSE 0 END)
78837885
AS runless_item_count
78847886
FROM orchestration_v2_projection_turn_item_counts counts
7885-
LEFT JOIN orchestration_v2_projection_runs r ON r.run_id = counts.run_key
7887+
-- The primary key would load each run's whole row just for its status.
7888+
LEFT JOIN orchestration_v2_projection_runs r
7889+
INDEXED BY orchestration_v2_projection_runs_run_status_idx
7890+
ON r.run_id = counts.run_key
78867891
WHERE counts.thread_id IN (SELECT value FROM json_each(${encodeIdList(threadIds)}))
78877892
GROUP BY counts.thread_id
78887893
`;
@@ -8742,7 +8747,7 @@ export const layer: Layer.Layer<ProjectionStoreV2, never, SqlClient.SqlClient> =
87428747
) AS has_remote_tasks
87438748
FROM orchestration_v2_projection_threads t
87448749
WHERE t.deleted_at IS NULL
8745-
AND json_extract(t.payload_json, '$.archivedAt') IS NULL
8750+
AND t.archived_at IS NULL
87468751
ORDER BY t.updated_at ASC, t.thread_id ASC
87478752
`;
87488753
return rows.map((row): ActiveShellIndexEntry => ({

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -96,6 +96,7 @@ import Migration0085 from "./Migrations/085_TurnItemCounts.ts";
9696
import Migration0086 from "./Migrations/086_QueuedTurnInputIndex.ts";
9797
import Migration0087 from "./Migrations/087_ProjectionProjectsCustomTitle.ts";
9898
import Migration0088 from "./Migrations/088_TurnItemTypeStatusIndex.ts";
99+
import Migration0089 from "./Migrations/089_ShellThreadReadIndexes.ts";
99100

100101
const migrationEntries = [
101102
[1, "OrchestrationEvents", Migration0001],
@@ -187,6 +188,7 @@ const migrationEntries = [
187188
[86, "QueuedTurnInputIndex", Migration0086],
188189
[87, "ProjectionProjectsCustomTitle", Migration0087],
189190
[88, "TurnItemTypeStatusIndex", Migration0088],
191+
[89, "ShellThreadReadIndexes", Migration0089],
190192
] as const;
191193

192194
export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
Lines changed: 26 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,26 @@
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+
// Keep these expressions identical to ProjectionStore's shell worktree and family filters.
8+
// json_valid keeps a malformed payload writable, as the other payload indexes do.
9+
yield* sql`
10+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_threads_worktree_path_idx
11+
ON orchestration_v2_projection_threads(
12+
CASE WHEN json_valid(payload_json) THEN json_extract(payload_json, '$.worktreePath') END
13+
)
14+
`;
15+
yield* sql`
16+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_threads_root_thread_idx
17+
ON orchestration_v2_projection_threads(
18+
CASE WHEN json_valid(payload_json) THEN json_extract(payload_json, '$.lineage.rootThreadId') END
19+
)
20+
`;
21+
// Shell item counts read only a run's status; the primary key would load the whole row.
22+
yield* sql`
23+
CREATE INDEX IF NOT EXISTS orchestration_v2_projection_runs_run_status_idx
24+
ON orchestration_v2_projection_runs(run_id, status)
25+
`;
26+
});

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

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -68,6 +68,7 @@ describe("V2 preview upgrade", () => {
6868
[86, "QueuedTurnInputIndex"],
6969
[87, "ProjectionProjectsCustomTitle"],
7070
[88, "TurnItemTypeStatusIndex"],
71+
[89, "ShellThreadReadIndexes"],
7172
]);
7273
assert.deepStrictEqual(yield* runMigrations(), []);
7374
assert.deepStrictEqual(yield* sql`SELECT * FROM orchestration_v2_legacy_imports`, imports);
@@ -179,6 +180,7 @@ describe("V2 preview upgrade", () => {
179180
[86, "QueuedTurnInputIndex"],
180181
[87, "ProjectionProjectsCustomTitle"],
181182
[88, "TurnItemTypeStatusIndex"],
183+
[89, "ShellThreadReadIndexes"],
182184
]);
183185
}).pipe(Effect.provide(NodeSqliteClient.layer({ filename: ":memory:" }))),
184186
);

0 commit comments

Comments
 (0)