1import * as Effect from "effect/Effect";
2import * as SqlClient from "effect/sql/SqlClient";
3
4import ApplicationEventSequenceIndexes from "./OrchestrationV2/ApplicationEventSequenceIndexes.ts";
5import ApplicationEventSource from "./OrchestrationV2/ApplicationEventSource.ts";
6import OrchestrationV2EffectCancellation from "./OrchestrationV2/EffectCancellation.ts";
7import OrchestrationV2Foundation from "./OrchestrationV2/Foundation.ts";
8import LegacyV1ImportState from "./OrchestrationV2/LegacyV1ImportState.ts";
9import OrchestrationV2ProviderSessionBindings from "./OrchestrationV2/ProviderSessionBindings.ts";
10import OrchestrationV2RecoveryIndexes from "./OrchestrationV2/RecoveryIndexes.ts";
11import ScheduledTasks from "./OrchestrationV2/ScheduledTasks.ts";
12import OrchestrationV2ShellIndexes from "./OrchestrationV2/ShellIndexes.ts";
13import OrchestrationV2Subagents from "./OrchestrationV2/Subagents.ts";
14import OrchestrationV2ThreadLaunchWorkflows from "./OrchestrationV2/ThreadLaunchWorkflows.ts";
15
16// Base schema for the orchestration V2 event log and projections. The setup
17// steps below were developed separately while V2 was private, but released
18// databases run and record them as one migration from schema 49.
19const OrchestrationV2Base = Effect.gen(function* () {
20 const sql = yield* SqlClient.SqlClient;
21
22 yield* sql`
23 CREATE TABLE orchestration_v2_events (
24 sequence INTEGER PRIMARY KEY AUTOINCREMENT,
25 event_id TEXT NOT NULL UNIQUE,
26 command_id TEXT,
27 thread_id TEXT NOT NULL,
28 run_id TEXT,
29 node_id TEXT,
30 provider TEXT,
31 raw_event_id TEXT,
32 event_type TEXT NOT NULL,
33 occurred_at TEXT NOT NULL,
34 payload_json TEXT NOT NULL
35 )
36 `;
37 yield* sql`CREATE INDEX orchestration_v2_events_command_idx ON orchestration_v2_events(command_id, sequence)`;
38 yield* sql`CREATE INDEX orchestration_v2_events_thread_sequence_idx ON orchestration_v2_events(thread_id, sequence)`;
39 yield* sql`CREATE INDEX orchestration_v2_events_thread_type_sequence_idx ON orchestration_v2_events(thread_id, event_type, sequence)`;
40 yield* sql`CREATE INDEX orchestration_v2_events_run_sequence_idx ON orchestration_v2_events(run_id, sequence)`;
41 yield* sql`CREATE INDEX orchestration_v2_events_node_sequence_idx ON orchestration_v2_events(node_id, sequence)`;
42 yield* sql`CREATE INDEX orchestration_v2_events_raw_event_idx ON orchestration_v2_events(raw_event_id)`;
43
44 yield* sql`
45 CREATE TABLE orchestration_v2_command_receipts (
46 command_id TEXT PRIMARY KEY,
47 thread_id TEXT NOT NULL,
48 command_type TEXT NOT NULL,
49 accepted_at TEXT NOT NULL,
50 result_sequence INTEGER NOT NULL,
51 status TEXT NOT NULL,
52 error TEXT
53 )
54 `;
55 yield* sql`CREATE INDEX orchestration_v2_command_receipts_thread_sequence_idx ON orchestration_v2_command_receipts(thread_id, result_sequence)`;
56
57 yield* sql`
58 CREATE TABLE orchestration_v2_projection_threads (
59 thread_id TEXT PRIMARY KEY,
60 project_id TEXT NOT NULL,
61 title TEXT NOT NULL,
62 default_provider TEXT NOT NULL,
63 runtime_mode TEXT NOT NULL,
64 interaction_mode TEXT NOT NULL,
65 active_provider_thread_id TEXT,
66 created_at TEXT NOT NULL,
67 updated_at TEXT NOT NULL,
68 archived_at TEXT,
69 deleted_at TEXT,
70 payload_json TEXT NOT NULL
71 )
72 `;
73 yield* sql`CREATE INDEX orchestration_v2_projection_threads_project_updated_idx ON orchestration_v2_projection_threads(project_id, updated_at)`;
74
75 yield* sql`
76 CREATE TABLE orchestration_v2_projection_runs (
77 run_id TEXT PRIMARY KEY,
78 thread_id TEXT NOT NULL,
79 ordinal INTEGER NOT NULL,
80 provider TEXT NOT NULL,
81 provider_thread_id TEXT,
82 status TEXT NOT NULL,
83 requested_at TEXT NOT NULL,
84 completed_at TEXT,
85 payload_json TEXT NOT NULL
86 )
87 `;
88 yield* sql`CREATE UNIQUE INDEX orchestration_v2_projection_runs_thread_ordinal_idx ON orchestration_v2_projection_runs(thread_id, ordinal)`;
89 yield* sql`CREATE INDEX orchestration_v2_projection_runs_provider_thread_idx ON orchestration_v2_projection_runs(provider_thread_id)`;
90 yield* sql`CREATE INDEX orchestration_v2_projection_runs_thread_status_idx ON orchestration_v2_projection_runs(thread_id, status)`;
91
92 yield* sql`
93 CREATE TABLE orchestration_v2_projection_run_attempts (
94 attempt_id TEXT PRIMARY KEY,
95 thread_id TEXT NOT NULL,
96 run_id TEXT NOT NULL,
97 attempt_ordinal INTEGER NOT NULL,
98 root_node_id TEXT NOT NULL,
99 provider TEXT NOT NULL,
100 provider_thread_id TEXT NOT NULL,
101 provider_turn_id TEXT,
102 status TEXT NOT NULL,
103 payload_json TEXT NOT NULL
104 )
105 `;
106 yield* sql`CREATE UNIQUE INDEX orchestration_v2_projection_run_attempts_run_ordinal_idx ON orchestration_v2_projection_run_attempts(run_id, attempt_ordinal)`;
107 yield* sql`CREATE INDEX orchestration_v2_projection_run_attempts_thread_idx ON orchestration_v2_projection_run_attempts(thread_id, run_id)`;
108
109 yield* sql`
110 CREATE TABLE orchestration_v2_projection_nodes (
111 node_id TEXT PRIMARY KEY,
112 thread_id TEXT NOT NULL,
113 run_id TEXT,
114 parent_node_id TEXT,
115 root_node_id TEXT NOT NULL,
116 kind TEXT NOT NULL,
117 status TEXT NOT NULL,
118 provider_thread_id TEXT,
119 provider_turn_id TEXT,
120 runtime_request_id TEXT,
121 checkpoint_scope_id TEXT,
122 started_at TEXT,
123 completed_at TEXT,
124 payload_json TEXT NOT NULL
125 )
126 `;
127 yield* sql`CREATE INDEX orchestration_v2_projection_nodes_thread_run_idx ON orchestration_v2_projection_nodes(thread_id, run_id)`;
128 yield* sql`CREATE INDEX orchestration_v2_projection_nodes_parent_idx ON orchestration_v2_projection_nodes(parent_node_id)`;
129 yield* sql`CREATE INDEX orchestration_v2_projection_nodes_provider_turn_idx ON orchestration_v2_projection_nodes(provider_turn_id)`;
130
131 yield* sql`
132 CREATE TABLE orchestration_v2_projection_provider_sessions (
133 provider_session_id TEXT PRIMARY KEY,
134 thread_id TEXT,
135 provider TEXT NOT NULL,
136 status TEXT NOT NULL,
137 model TEXT,
138 updated_at TEXT NOT NULL,
139 payload_json TEXT NOT NULL
140 )
141 `;
142 yield* sql`CREATE INDEX orchestration_v2_projection_provider_sessions_thread_idx ON orchestration_v2_projection_provider_sessions(thread_id)`;
143 yield* sql`CREATE INDEX orchestration_v2_projection_provider_sessions_provider_status_idx ON orchestration_v2_projection_provider_sessions(provider, status)`;
144
145 yield* sql`
146 CREATE TABLE orchestration_v2_projection_provider_threads (
147 provider_thread_id TEXT PRIMARY KEY,
148 thread_id TEXT,
149 owner_node_id TEXT,
150 provider TEXT NOT NULL,
151 provider_session_id TEXT,
152 status TEXT NOT NULL,
153 first_run_ordinal INTEGER,
154 last_run_ordinal INTEGER,
155 updated_at TEXT NOT NULL,
156 payload_json TEXT NOT NULL
157 )
158 `;
159 yield* sql`CREATE INDEX orchestration_v2_projection_provider_threads_thread_idx ON orchestration_v2_projection_provider_threads(thread_id)`;
160 yield* sql`CREATE INDEX orchestration_v2_projection_provider_threads_session_idx ON orchestration_v2_projection_provider_threads(provider_session_id)`;
161 yield* sql`CREATE INDEX orchestration_v2_projection_provider_threads_owner_idx ON orchestration_v2_projection_provider_threads(owner_node_id)`;
162
163 yield* sql`
164 CREATE TABLE orchestration_v2_projection_provider_turns (
165 provider_turn_id TEXT PRIMARY KEY,
166 thread_id TEXT NOT NULL,
167 provider_thread_id TEXT NOT NULL,
168 node_id TEXT NOT NULL,
169 run_attempt_id TEXT,
170 ordinal INTEGER NOT NULL,
171 status TEXT NOT NULL,
172 started_at TEXT,
173 completed_at TEXT,
174 payload_json TEXT NOT NULL
175 )
176 `;
177 yield* sql`CREATE INDEX orchestration_v2_projection_provider_turns_thread_idx ON orchestration_v2_projection_provider_turns(thread_id)`;
178 yield* sql`CREATE UNIQUE INDEX orchestration_v2_projection_provider_turns_thread_ordinal_idx ON orchestration_v2_projection_provider_turns(provider_thread_id, ordinal)`;
179
180 yield* sql`
181 CREATE TABLE orchestration_v2_projection_runtime_requests (
182 runtime_request_id TEXT PRIMARY KEY,
183 thread_id TEXT NOT NULL,
184 node_id TEXT NOT NULL,
185 provider_turn_id TEXT,
186 kind TEXT NOT NULL,
187 status TEXT NOT NULL,
188 created_at TEXT NOT NULL,
189 resolved_at TEXT,
190 payload_json TEXT NOT NULL
191 )
192 `;
193 yield* sql`CREATE INDEX orchestration_v2_projection_runtime_requests_thread_status_idx ON orchestration_v2_projection_runtime_requests(thread_id, status)`;
194 yield* sql`CREATE INDEX orchestration_v2_projection_runtime_requests_provider_turn_idx ON orchestration_v2_projection_runtime_requests(provider_turn_id)`;
195
196 yield* sql`
197 CREATE TABLE orchestration_v2_projection_messages (
198 message_id TEXT PRIMARY KEY,
199 thread_id TEXT NOT NULL,
200 run_id TEXT,
201 node_id TEXT,
202 role TEXT NOT NULL,
203 streaming INTEGER NOT NULL,
204 created_at TEXT NOT NULL,
205 updated_at TEXT NOT NULL,
206 payload_json TEXT NOT NULL
207 )
208 `;
209 yield* sql`CREATE INDEX orchestration_v2_projection_messages_thread_created_idx ON orchestration_v2_projection_messages(thread_id, created_at, message_id)`;
210 yield* sql`CREATE INDEX orchestration_v2_projection_messages_run_idx ON orchestration_v2_projection_messages(run_id)`;
211 yield* sql`CREATE INDEX orchestration_v2_projection_messages_node_idx ON orchestration_v2_projection_messages(node_id)`;
212
213 yield* sql`
214 CREATE TABLE orchestration_v2_projection_plans (
215 plan_id TEXT PRIMARY KEY,
216 thread_id TEXT NOT NULL,
217 run_id TEXT,
218 node_id TEXT NOT NULL,
219 kind TEXT NOT NULL,
220 status TEXT NOT NULL,
221 payload_json TEXT NOT NULL
222 )
223 `;
224 yield* sql`CREATE INDEX orchestration_v2_projection_plans_thread_idx ON orchestration_v2_projection_plans(thread_id)`;
225 yield* sql`CREATE INDEX orchestration_v2_projection_plans_run_idx ON orchestration_v2_projection_plans(run_id)`;
226
227 yield* sql`
228 CREATE TABLE orchestration_v2_projection_turn_items (
229 turn_item_id TEXT PRIMARY KEY,
230 thread_id TEXT NOT NULL,
231 run_id TEXT,
232 node_id TEXT,
233 provider_thread_id TEXT,
234 provider_turn_id TEXT,
235 parent_item_id TEXT,
236 ordinal INTEGER NOT NULL,
237 type TEXT NOT NULL,
238 status TEXT NOT NULL,
239 updated_at TEXT NOT NULL,
240 payload_json TEXT NOT NULL
241 )
242 `;
243 yield* sql`CREATE INDEX orchestration_v2_projection_turn_items_thread_ordinal_idx ON orchestration_v2_projection_turn_items(thread_id, ordinal, turn_item_id)`;
244 yield* sql`CREATE INDEX orchestration_v2_projection_turn_items_run_ordinal_idx ON orchestration_v2_projection_turn_items(run_id, ordinal)`;
245 yield* sql`CREATE INDEX orchestration_v2_projection_turn_items_node_ordinal_idx ON orchestration_v2_projection_turn_items(node_id, ordinal)`;
246 yield* sql`CREATE INDEX orchestration_v2_projection_turn_items_provider_turn_idx ON orchestration_v2_projection_turn_items(provider_turn_id)`;
247
248 yield* sql`
249 CREATE TABLE orchestration_v2_projection_checkpoint_scopes (
250 scope_id TEXT PRIMARY KEY,
251 thread_id TEXT NOT NULL,
252 run_id TEXT,
253 node_id TEXT NOT NULL,
254 parent_scope_id TEXT,
255 provider_thread_id TEXT,
256 kind TEXT NOT NULL,
257 ordinal_within_parent INTEGER NOT NULL,
258 advances_app_run_count INTEGER NOT NULL,
259 created_at TEXT NOT NULL,
260 payload_json TEXT NOT NULL
261 )
262 `;
263 yield* sql`CREATE INDEX orchestration_v2_projection_checkpoint_scopes_thread_idx ON orchestration_v2_projection_checkpoint_scopes(thread_id)`;
264 yield* sql`CREATE INDEX orchestration_v2_projection_checkpoint_scopes_parent_idx ON orchestration_v2_projection_checkpoint_scopes(parent_scope_id)`;
265
266 yield* sql`
267 CREATE TABLE orchestration_v2_projection_checkpoints (
268 checkpoint_id TEXT PRIMARY KEY,
269 thread_id TEXT NOT NULL,
270 scope_id TEXT NOT NULL,
271 run_id TEXT,
272 node_id TEXT NOT NULL,
273 parent_checkpoint_id TEXT,
274 ordinal_within_scope INTEGER NOT NULL,
275 app_run_ordinal INTEGER,
276 status TEXT NOT NULL,
277 captured_at TEXT NOT NULL,
278 payload_json TEXT NOT NULL
279 )
280 `;
281 yield* sql`CREATE UNIQUE INDEX orchestration_v2_projection_checkpoints_scope_ordinal_idx ON orchestration_v2_projection_checkpoints(scope_id, ordinal_within_scope)`;
282 yield* sql`CREATE INDEX orchestration_v2_projection_checkpoints_thread_idx ON orchestration_v2_projection_checkpoints(thread_id)`;
283 yield* sql`CREATE INDEX orchestration_v2_projection_checkpoints_parent_idx ON orchestration_v2_projection_checkpoints(parent_checkpoint_id)`;
284
285 yield* sql`
286 CREATE TABLE orchestration_v2_projection_context_handoffs (
287 context_handoff_id TEXT PRIMARY KEY,
288 thread_id TEXT NOT NULL,
289 target_run_id TEXT NOT NULL,
290 to_provider_thread_id TEXT NOT NULL,
291 strategy TEXT NOT NULL,
292 status TEXT NOT NULL,
293 updated_at TEXT NOT NULL,
294 payload_json TEXT NOT NULL
295 )
296 `;
297 yield* sql`CREATE INDEX orchestration_v2_projection_context_handoffs_thread_idx ON orchestration_v2_projection_context_handoffs(thread_id)`;
298 yield* sql`CREATE INDEX orchestration_v2_projection_context_handoffs_target_run_idx ON orchestration_v2_projection_context_handoffs(target_run_id)`;
299
300 yield* sql`
301 CREATE TABLE orchestration_v2_projection_context_transfers (
302 context_transfer_id TEXT PRIMARY KEY,
303 source_thread_id TEXT NOT NULL,
304 target_thread_id TEXT NOT NULL,
305 target_run_id TEXT,
306 type TEXT NOT NULL,
307 status TEXT NOT NULL,
308 source_provider TEXT,
309 target_provider TEXT,
310 updated_at TEXT NOT NULL,
311 payload_json TEXT NOT NULL
312 )
313 `;
314 yield* sql`CREATE INDEX orchestration_v2_projection_context_transfers_source_thread_idx ON orchestration_v2_projection_context_transfers(source_thread_id)`;
315 yield* sql`CREATE INDEX orchestration_v2_projection_context_transfers_target_thread_idx ON orchestration_v2_projection_context_transfers(target_thread_id, status)`;
316 yield* sql`CREATE INDEX orchestration_v2_projection_context_transfers_target_run_idx ON orchestration_v2_projection_context_transfers(target_run_id)`;
317});
318
319export default Effect.gen(function* () {
320 yield* OrchestrationV2Base;
321 yield* OrchestrationV2Subagents;
322 yield* OrchestrationV2Foundation;
323 yield* OrchestrationV2ProviderSessionBindings;
324 yield* OrchestrationV2ThreadLaunchWorkflows;
325 yield* ApplicationEventSource;
326 yield* OrchestrationV2EffectCancellation;
327 yield* ScheduledTasks;
328 yield* LegacyV1ImportState;
329 yield* ApplicationEventSequenceIndexes;
330 yield* OrchestrationV2RecoveryIndexes;
331 yield* OrchestrationV2ShellIndexes;
332});