@@ -1110,7 +1110,7 @@ const assertProjectScopedShells = Effect.fn("assertProjectScopedShells")(functio
11101110} ) ;
11111111
11121112it . 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
57435821const 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
57755889describe ( "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 ) ;
0 commit comments