Open History to see the last commit
1import * as NodeServices from "@effect/platform-node/NodeServices";
2import { assert, it } from "@effect/vitest";
3import * as Effect from "effect/Effect";
4import * as FileSystem from "effect/FileSystem";
5import * as Path from "effect/Path";
6import * as SqlClient from "effect/sql/SqlClient";
7
8import { runMigrations } from "../src/persistence/Migrations.ts";
9import * as NodeSqliteClient from "@t3tools/shared/nodeSqliteClient";
10import { runMigrateDevDb } from "./migrate-dev-db.ts";
11
12const withDatabase = <A, E>(
13 databasePath: string,
14 effect: Effect.Effect<A, E, SqlClient.SqlClient>,
15) => effect.pipe(Effect.provide(NodeSqliteClient.layer({ filename: databasePath })));
16
17const insertPeerLink = (sql: SqlClient.SqlClient) => sql`INSERT INTO peer_environment_links
18 (environment_id, secret_name, label, urls_json, access, linked_at, expires_at)
19 VALUES ('environment-box', 'peer-link-box', 'Box', '["https://box.example"]', 'auto',
20 '2026-08-01', '2027-08-01')`;
21
22const createFixtureSource = Effect.fn("createMigrateDevDbFixtureSource")(function* (
23 baseDir: string,
24 toMigrationInclusive?: number,
25) {
26 const fs = yield* FileSystem.FileSystem;
27 const path = yield* Path.Path;
28 const stateDir = path.join(baseDir, "userdata");
29 const databasePath = path.join(stateDir, "statev2.sqlite");
30 yield* fs.makeDirectory(stateDir, { recursive: true });
31 yield* withDatabase(
32 databasePath,
33 Effect.gen(function* () {
34 const sql = yield* SqlClient.SqlClient;
35 yield* runMigrations({ toMigrationInclusive });
36
37 yield* sql`INSERT INTO projection_projects
38 (project_id, title, workspace_root, scripts_json, created_at, updated_at, deleted_at)
39 VALUES
40 ('project-kept', 'Kept', '/tmp/kept', '[]', '2026-08-01', '2026-08-01', NULL),
41 ('project-deleted', 'Deleted', '/tmp/deleted', '[]', '2026-08-01', '2026-08-02', '2026-08-02')`;
42
43 const forkPayload =
44 '{"lineage":{"parentThreadId":"stopped-thread","relationshipToParent":"fork","rootThreadId":"stopped-thread"}}';
45 const subagentPayload =
46 '{"lineage":{"parentThreadId":"subagent-parent","relationshipToParent":"subagent","rootThreadId":"subagent-parent"},"forkedFrom":{"type":"node","nodeId":"node-1"}}';
47 const watchedPayload = JSON.stringify({
48 pullRequests: [
49 {
50 host: "github.com",
51 repository: "owner/repo",
52 number: 7,
53 url: "https://github.com/owner/repo/pull/7",
54 source: "agent",
55 linkedAt: "2026-08-01T00:00:00.000Z",
56 snapshot: null,
57 stack: null,
58 watch: {
59 startedAt: "2026-08-01T00:00:00.000Z",
60 headSha: null,
61 failedChecks: [],
62 passed: false,
63 passedChecks: [],
64 remarksThrough: "2026-08-01T00:00:00.000Z",
65 remarkIds: [],
66 conflicting: false,
67 wakes: 0,
68 },
69 },
70 ],
71 });
72 const threads = [
73 ["stopped-thread", "project-kept", "completed", watchedPayload, "2026-08-01"],
74 ["fork-thread", "project-kept", "completed", forkPayload, "2026-08-02"],
75 ["running-thread", "project-kept", "running", "{}", "2026-08-05"],
76 ["settled-thread", "project-kept", "completed", '{"settledAt":"2026-08-01"}', "2026-08-05"],
77 [
78 "archived-thread",
79 "project-kept",
80 "completed",
81 '{"archivedAt":"2026-08-01"}',
82 "2026-08-05",
83 ],
84 [
85 "limit-thread",
86 "project-kept",
87 "completed",
88 '{"limitRecovery":{"autoResume":true}}',
89 "2026-08-05",
90 ],
91 ["subagent-parent", "project-kept", "completed", "{}", "2026-08-05"],
92 ["subagent-child", "project-kept", "completed", subagentPayload, "2026-08-05"],
93 ["deleted-project-thread", "project-deleted", "completed", "{}", "2026-08-05"],
94 ] as const;
95 for (const [threadId, projectId, runStatus, payload, updatedAt] of threads) {
96 yield* sql`INSERT INTO orchestration_v2_projection_threads
97 (thread_id, project_id, title, default_provider, runtime_mode, interaction_mode, created_at, updated_at, payload_json)
98 VALUES (${threadId}, ${projectId}, ${threadId}, 'codex', 'full-access', 'default', '2026-08-01', ${updatedAt}, ${payload})`;
99 yield* sql`INSERT INTO orchestration_v2_projection_runs
100 (run_id, thread_id, ordinal, provider, status, requested_at, payload_json)
101 VALUES (${`run-${threadId}`}, ${threadId}, 1, 'codex', ${runStatus}, '2026-08-01', '{}')`;
102 yield* sql`INSERT INTO orchestration_events
103 (event_id, aggregate_kind, stream_id, stream_version, event_type, occurred_at, actor_kind, payload_json, metadata_json)
104 VALUES (${`event-${threadId}`}, 'thread', ${threadId}, 0, 'thread.created', '2026-08-01', 'user', '{}', '{}')`;
105 }
106 yield* sql`INSERT INTO orchestration_v2_projection_provider_sessions
107 (provider_session_id, thread_id, provider, status, updated_at, payload_json)
108 VALUES ('session-shared', 'running-thread', 'codex', 'stopped', '2026-08-01', '{}')`;
109 yield* sql`INSERT INTO orchestration_v2_projection_provider_session_bindings
110 (provider_session_id, thread_id)
111 VALUES ('session-shared', 'running-thread'), ('session-shared', 'stopped-thread')`;
112 yield* sql`INSERT INTO orchestration_v2_projection_context_transfers
113 (context_transfer_id, source_thread_id, target_thread_id, type, status, updated_at, payload_json)
114 VALUES ('transfer-1', 'archived-thread', 'stopped-thread', 'provider_handoff', 'completed', '2026-08-01', '{}')`;
115 yield* sql`INSERT INTO scheduled_tasks
116 (task_id, title, prompt, enabled, schedule_json, project_id, workspace_strategy_json,
117 model_selection_json, runtime_mode, interaction_mode, created_by, creation_source,
118 created_at, updated_at, last_run_status, run_count)
119 VALUES ('task-1', 'Nightly', 'Run it', 1, '{}', 'project-kept', '{}', '{}',
120 'full-access', 'default', 'user', 'user', '2026-08-01', '2026-08-01', 'never', 0)`;
121 yield* sql`INSERT INTO scheduled_task_webhook_replay_keys
122 (task_id, replay_key, delivery_id, expires_at)
123 VALUES ('task-1', 'replay-1', 'delivery-1', 1900000000000)`;
124 yield* sql`INSERT INTO scheduled_task_webhook_pending
125 (delivery_id, task_id, coalesce_key, due_at, task_json, request_json, delivery_ids_json)
126 VALUES ('delivery-1', 'task-1', 'key-1', 1900000000000, '{}', '{}', '[]')`;
127 yield* sql`INSERT INTO auth_sessions (session_id, subject, scopes, method, issued_at, expires_at)
128 VALUES ('session-1', 'user', '[]', 'pairing', '2026-08-01', '2027-08-01')`;
129 if (toMigrationInclusive === undefined) yield* insertPeerLink(sql);
130 }),
131 );
132 return databasePath;
133});
134
135it.layer(NodeServices.layer)("migrate-dev-db", (it) => {
136 it.effect("keeps stopped and settled thread families and clears pending work", () =>
137 Effect.gen(function* () {
138 const fs = yield* FileSystem.FileSystem;
139 const path = yield* Path.Path;
140 const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-src-" });
141 const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-dest-" });
142 const source = yield* createFixtureSource(sourceDir);
143
144 const result = yield* runMigrateDevDb(
145 { baseDir: destDir, source, projects: 5, threadsPerProject: 2 },
146 { sharedHome: sourceDir },
147 );
148
149 assert.equal(result.databasePath, path.join(destDir, "userdata", "statev2.sqlite"));
150 const kept = yield* withDatabase(
151 result.databasePath,
152 Effect.gen(function* () {
153 const sql = yield* SqlClient.SqlClient;
154 const threads = yield* sql<{ thread_id: string }>`
155 SELECT thread_id FROM orchestration_v2_projection_threads ORDER BY thread_id`;
156 const events = yield* sql<{ stream_id: string }>`
157 SELECT stream_id FROM orchestration_events ORDER BY stream_id`;
158 const sessions = yield* sql<{ provider_session_id: string }>`
159 SELECT provider_session_id FROM orchestration_v2_projection_provider_sessions`;
160 const [pullRequest] = yield* sql<{ url: string | null; watch: string | null }>`
161 SELECT
162 json_extract(payload_json, '$.pullRequests[0].url') AS url,
163 json_extract(payload_json, '$.pullRequests[0].watch') AS watch
164 FROM orchestration_v2_projection_threads WHERE thread_id = 'stopped-thread'`;
165 const [leftovers] = yield* sql<{
166 auth: number;
167 tasks: number;
168 webhooks: number;
169 transfers: number;
170 links: number;
171 }>`
172 SELECT
173 (SELECT COUNT(*) FROM auth_sessions) AS auth,
174 (SELECT COUNT(*) FROM scheduled_tasks) AS tasks,
175 (SELECT COUNT(*) FROM scheduled_task_webhook_replay_keys) +
176 (SELECT COUNT(*) FROM scheduled_task_webhook_pending) AS webhooks,
177 (SELECT COUNT(*) FROM orchestration_v2_projection_context_transfers) AS transfers,
178 (SELECT COUNT(*) FROM peer_environment_links) AS links`;
179 return { threads, events, sessions, pullRequest, leftovers };
180 }),
181 );
182 assert.deepStrictEqual(
183 kept.threads.map((row) => row.thread_id),
184 ["fork-thread", "settled-thread", "stopped-thread"],
185 );
186 assert.deepStrictEqual(
187 kept.events.map((row) => row.stream_id),
188 ["fork-thread", "settled-thread", "stopped-thread"],
189 );
190 assert.deepStrictEqual(
191 kept.sessions.map((row) => row.provider_session_id),
192 ["session-shared"],
193 );
194 assert.deepStrictEqual(
195 kept.pullRequest,
196 { url: "https://github.com/owner/repo/pull/7", watch: null },
197 "copied pull request links keep their history but stop being watched",
198 );
199 assert.deepStrictEqual(kept.leftovers, {
200 auth: 0,
201 tasks: 0,
202 webhooks: 0,
203 transfers: 0,
204 links: 0,
205 });
206 }),
207 );
208
209 it.effect("never copies an archived thread, and new events append after the source's", () =>
210 Effect.gen(function* () {
211 const fs = yield* FileSystem.FileSystem;
212 const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-slice-" });
213 const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-slice-dest-" });
214 const source = yield* createFixtureSource(sourceDir);
215 const [sourceSequence] = yield* withDatabase(
216 source,
217 Effect.gen(function* () {
218 const sql = yield* SqlClient.SqlClient;
219 return yield* sql<{ seq: number }>`
220 SELECT seq FROM sqlite_sequence WHERE name = 'orchestration_events'`;
221 }),
222 );
223
224 const result = yield* runMigrateDevDb(
225 { baseDir: destDir, source, projects: 5, threadsPerProject: 100 },
226 { sharedHome: sourceDir },
227 );
228
229 const copied = yield* withDatabase(
230 result.databasePath,
231 Effect.gen(function* () {
232 const sql = yield* SqlClient.SqlClient;
233 const [archivedRows] = yield* sql<{ count: number }>`
234 SELECT
235 (SELECT COUNT(*) FROM orchestration_v2_projection_runs WHERE thread_id = 'archived-thread')
236 + (SELECT COUNT(*) FROM orchestration_events WHERE stream_id = 'archived-thread')
237 AS count`;
238 const [sequence] = yield* sql<{ seq: number }>`
239 SELECT seq FROM sqlite_sequence WHERE name = 'orchestration_events'`;
240 return { archivedRows: archivedRows?.count, sequence: sequence?.seq };
241 }),
242 );
243 assert.equal(copied.archivedRows, 0);
244 assert.equal(copied.sequence, sourceSequence?.seq);
245 }),
246 );
247
248 it.effect("skips a thread family larger than the per-project limit", () =>
249 Effect.gen(function* () {
250 const fs = yield* FileSystem.FileSystem;
251 const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-family-" });
252 const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-family-dest-" });
253 const source = yield* createFixtureSource(sourceDir);
254
255 // stopped-thread and its fork are 2 threads; settled-thread is 1.
256 const result = yield* runMigrateDevDb(
257 { baseDir: destDir, source, projects: 5, threadsPerProject: 1 },
258 { sharedHome: sourceDir },
259 );
260 const threads = yield* withDatabase(
261 result.databasePath,
262 Effect.gen(function* () {
263 const sql = yield* SqlClient.SqlClient;
264 return yield* sql<{ thread_id: string }>`
265 SELECT thread_id FROM orchestration_v2_projection_threads`;
266 }),
267 );
268 assert.deepStrictEqual(
269 threads.map((row) => row.thread_id),
270 ["settled-thread"],
271 );
272 }),
273 );
274
275 it.effect("upgrades a source from before the V2 thread tables", () =>
276 Effect.gen(function* () {
277 const fs = yield* FileSystem.FileSystem;
278 const path = yield* Path.Path;
279 const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-v1-" });
280 const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-v1-dest-" });
281 const stateDir = path.join(sourceDir, "userdata");
282 const source = path.join(stateDir, "statev2.sqlite");
283 yield* fs.makeDirectory(stateDir, { recursive: true });
284 yield* withDatabase(source, runMigrations({ toMigrationInclusive: 54 }));
285
286 const result = yield* runMigrateDevDb(
287 { baseDir: destDir, source, projects: 5, threadsPerProject: 10 },
288 { sharedHome: sourceDir },
289 );
290 assert.include(result.executedMigrations, "55_OrchestrationV2");
291 }),
292 );
293
294 it.effect("seeds from a source that recorded another migration below V2", () =>
295 Effect.gen(function* () {
296 const fs = yield* FileSystem.FileSystem;
297 const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-local-" });
298 const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-local-dest-" });
299 const source = yield* createFixtureSource(sourceDir);
300 yield* withDatabase(
301 source,
302 Effect.gen(function* () {
303 const sql = yield* SqlClient.SqlClient;
304 yield* sql`UPDATE effect_sql_migrations SET name = 'SiteLocalMigration'
305 WHERE migration_id = 51`;
306 }),
307 );
308
309 const result = yield* runMigrateDevDb(
310 { baseDir: destDir, source, projects: 5, threadsPerProject: 2 },
311 { sharedHome: sourceDir },
312 );
313 const threads = yield* withDatabase(
314 result.databasePath,
315 Effect.gen(function* () {
316 const sql = yield* SqlClient.SqlClient;
317 return yield* sql<{ thread_id: string }>`
318 SELECT thread_id FROM orchestration_v2_projection_threads ORDER BY thread_id`;
319 }),
320 );
321 assert.deepStrictEqual(
322 threads.map((row) => row.thread_id),
323 ["fork-thread", "settled-thread", "stopped-thread"],
324 );
325 }),
326 );
327
328 it.effect("refuses a source whose V2 migration history clashes with this checkout", () =>
329 Effect.gen(function* () {
330 const fs = yield* FileSystem.FileSystem;
331 const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-clash-" });
332 const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-clash-dest-" });
333 const source = yield* createFixtureSource(sourceDir);
334 yield* withDatabase(
335 source,
336 Effect.gen(function* () {
337 const sql = yield* SqlClient.SqlClient;
338 yield* sql`UPDATE effect_sql_migrations SET name = 'OtherBuildMigration'
339 WHERE migration_id = 60`;
340 }),
341 );
342
343 const destination = yield* createFixtureSource(destDir);
344 const sourceBefore = yield* fs.readFile(source);
345 const destinationBefore = yield* fs.readFile(destination);
346
347 const error = yield* runMigrateDevDb(
348 { baseDir: destDir, source, projects: 5, threadsPerProject: 2 },
349 { sharedHome: sourceDir },
350 ).pipe(Effect.flip);
351 assert.equal(error._tag, "MigrateDevDbPhaseError");
352 if (error._tag === "MigrateDevDbPhaseError") {
353 assert.equal(error.phase, "migrate");
354 assert.include(String(error.cause), "60_ThreadSnapshotWindowIndexes");
355 }
356 assert.deepStrictEqual(yield* fs.readFile(source), sourceBefore);
357 assert.deepStrictEqual(yield* fs.readFile(destination), destinationBefore);
358 }),
359 );
360
361 it.effect("imports a dev seed whose ids after 67 came from the peer links branch", () =>
362 Effect.gen(function* () {
363 const fs = yield* FileSystem.FileSystem;
364 const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-peer-" });
365 const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-peer-dest-" });
366 const source = yield* createFixtureSource(sourceDir, 67);
367 // Mirrors a real ~/.t3 seed written by a build whose 68-70 differ from main's.
368 yield* withDatabase(
369 source,
370 Effect.gen(function* () {
371 const sql = yield* SqlClient.SqlClient;
372 yield* sql`CREATE TABLE peer_environment_links (
373 environment_id TEXT PRIMARY KEY,
374 secret_name TEXT NOT NULL,
375 label TEXT NOT NULL,
376 urls_json TEXT NOT NULL,
377 access TEXT NOT NULL,
378 linked_at TEXT NOT NULL,
379 expires_at TEXT NOT NULL,
380 last_reached_at TEXT,
381 last_error TEXT,
382 fenced INTEGER NOT NULL DEFAULT 0)`;
383 yield* sql`CREATE INDEX orchestration_v2_projection_turn_items_waiting_link_idx
384 ON orchestration_v2_projection_turn_items(thread_id, updated_at DESC, turn_item_id DESC)
385 WHERE type = 'link_request' AND status = 'waiting'`;
386 yield* insertPeerLink(sql);
387 yield* sql`INSERT INTO effect_sql_migrations (migration_id, name) VALUES
388 (68, 'PeerLinks'), (69, 'PeerLinkFence'), (70, 'WaitingLinkRequestIndex')`;
389 }),
390 );
391
392 const result = yield* runMigrateDevDb(
393 { baseDir: destDir, source, projects: 5, threadsPerProject: 2 },
394 { sharedHome: sourceDir },
395 );
396
397 const imported = yield* withDatabase(
398 result.databasePath,
399 Effect.gen(function* () {
400 const sql = yield* SqlClient.SqlClient;
401 const threads = yield* sql<{ thread_id: string }>`
402 SELECT thread_id FROM orchestration_v2_projection_threads ORDER BY thread_id`;
403 const schema = yield* sql<{ name: string }>`
404 SELECT name FROM sqlite_master WHERE name IN (
405 'orchestration_v2_projection_turn_items_stop_request_idx',
406 'orchestration_v2_message_search')
407 ORDER BY name`;
408 return { threads, schema };
409 }),
410 );
411 assert.deepStrictEqual(
412 imported.threads.map((row) => row.thread_id),
413 ["fork-thread", "settled-thread", "stopped-thread"],
414 );
415 assert.deepStrictEqual(
416 imported.schema.map((row) => row.name),
417 [
418 "orchestration_v2_message_search",
419 "orchestration_v2_projection_turn_items_stop_request_idx",
420 ],
421 );
422 }),
423 );
424
425 it.effect("refuses while a dev server holds the destination", () =>
426 Effect.gen(function* () {
427 const fs = yield* FileSystem.FileSystem;
428 const path = yield* Path.Path;
429 const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-busy-" });
430 const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-busy-dest-" });
431 const source = yield* createFixtureSource(sourceDir);
432 const stateDir = path.join(destDir, "userdata");
433 yield* fs.makeDirectory(stateDir, { recursive: true });
434 yield* fs.writeFileString(
435 path.join(stateDir, "server-runtime.json"),
436 `{"version":1,"pid":${process.pid}}`,
437 );
438
439 const error = yield* runMigrateDevDb(
440 { baseDir: destDir, source, projects: 5, threadsPerProject: 10 },
441 { sharedHome: sourceDir },
442 ).pipe(Effect.flip);
443 assert.equal(error._tag, "MigrateDevDbServerRunningError");
444 if (error._tag === "MigrateDevDbServerRunningError") {
445 assert.equal(error.pid, process.pid);
446 }
447 }),
448 );
449
450 it.effect("refuses a source that resolves to a destination path", () =>
451 Effect.gen(function* () {
452 const fs = yield* FileSystem.FileSystem;
453 const path = yield* Path.Path;
454 const sharedDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-overlap-" });
455 const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-overlap-dest-" });
456 const leftoverSnapshot = path.join(destDir, "userdata", "statev2.sqlite.migrate-dev-db-tmp");
457 yield* fs.makeDirectory(path.dirname(leftoverSnapshot), { recursive: true });
458 yield* fs.writeFileString(leftoverSnapshot, "not a real db");
459
460 const error = yield* runMigrateDevDb(
461 { baseDir: destDir, source: leftoverSnapshot, projects: 5, threadsPerProject: 10 },
462 { sharedHome: sharedDir },
463 ).pipe(Effect.flip);
464 assert.equal(error._tag, "MigrateDevDbSourceIsDestinationError");
465 assert.equal(yield* fs.exists(leftoverSnapshot), true);
466 }),
467 );
468
469 it.effect("refuses to rebuild the shared home", () =>
470 Effect.gen(function* () {
471 const fs = yield* FileSystem.FileSystem;
472 const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-shared-" });
473 const source = yield* createFixtureSource(sourceDir);
474
475 const error = yield* runMigrateDevDb(
476 { baseDir: sourceDir, source, projects: 5, threadsPerProject: 10 },
477 { sharedHome: sourceDir },
478 ).pipe(Effect.flip);
479 assert.equal(error._tag, "MigrateDevDbSharedHomeError");
480 }),
481 );
482});