1import * as Effect from "effect/Effect";
2import * as SqlClient from "effect/sql/SqlClient";
3
4export default Effect.gen(function* () {
5 const sql = yield* SqlClient.SqlClient;
6
7 // A bounded thread snapshot finds its turn boundary from the newest user
8 // messages. Without this index it reads every turn item in the thread to
9 // find a few dozen of them. ProjectionStore's turn_anchors CTE relies on it.
10 yield* sql`
11 CREATE INDEX IF NOT EXISTS orchestration_v2_projection_turn_items_user_message_idx
12 ON orchestration_v2_projection_turn_items(thread_id, ordinal, turn_item_id)
13 WHERE type = 'user_message'
14 `;
15 // The same snapshot keeps every unfinished node. A long thread has thousands
16 // of finished ones, and reading them all to find a few live ones was more
17 // than half of the node query's time.
18 yield* sql`
19 CREATE INDEX IF NOT EXISTS orchestration_v2_projection_nodes_live_idx
20 ON orchestration_v2_projection_nodes(thread_id)
21 WHERE status IN ('pending', 'starting', 'running', 'waiting')
22 `;
23});