Skip to content

Commit 98a70c3

Browse files
fix(server): migrate-dev-db copies only visible threads
migrate-dev-db took a full VACUUM INTO snapshot of ~/.t3 before pruning it. On a long-lived install that is a 68 GB temporary copy and several minutes, nearly all of it events from deleted, archived, and settled threads that the prune throws away. It now attaches the source read-only and copies only the rows of visible thread families, in one read transaction. Same result, about 10 seconds, no full-size temp file. Co-Authored-By: Claude Opus 5.5 (1M context) <[email protected]>
1 parent 7f04023 commit 98a70c3

2 files changed

Lines changed: 133 additions & 28 deletions

File tree

‎apps/server/scripts/migrate-dev-db.test.ts‎

Lines changed: 40 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -142,6 +142,46 @@ it.layer(NodeServices.layer)("migrate-dev-db", (it) => {
142142
}),
143143
);
144144

145+
it.effect("never copies a settled thread's rows, and new events append after the source's", () =>
146+
Effect.gen(function* () {
147+
const fs = yield* FileSystem.FileSystem;
148+
const sourceDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-slice-" });
149+
const destDir = yield* fs.makeTempDirectoryScoped({ prefix: "migrate-dev-db-slice-dest-" });
150+
const source = yield* createFixtureSource(sourceDir);
151+
const [sourceSequence] = yield* withDatabase(
152+
source,
153+
Effect.gen(function* () {
154+
const sql = yield* SqlClient.SqlClient;
155+
return yield* sql<{ seq: number }>`
156+
SELECT seq FROM sqlite_sequence WHERE name = 'orchestration_events'`;
157+
}),
158+
);
159+
160+
// Keep every family, so only the copy can leave the settled one out.
161+
const result = yield* runMigrateDevDb(
162+
{ baseDir: destDir, source, projects: 5, threadsPerProject: 100 },
163+
{ sharedHome: sourceDir },
164+
);
165+
166+
const copied = yield* withDatabase(
167+
result.databasePath,
168+
Effect.gen(function* () {
169+
const sql = yield* SqlClient.SqlClient;
170+
const [settledRows] = yield* sql<{ count: number }>`
171+
SELECT
172+
(SELECT COUNT(*) FROM orchestration_v2_projection_runs WHERE thread_id = 'settled-thread')
173+
+ (SELECT COUNT(*) FROM orchestration_events WHERE stream_id = 'settled-thread')
174+
AS count`;
175+
const [sequence] = yield* sql<{ seq: number }>`
176+
SELECT seq FROM sqlite_sequence WHERE name = 'orchestration_events'`;
177+
return { settledRows: settledRows?.count, sequence: sequence?.seq };
178+
}),
179+
);
180+
assert.equal(copied.settledRows, 0);
181+
assert.equal(copied.sequence, sourceSequence?.seq);
182+
}),
183+
);
184+
145185
it.effect("fails loudly on a migration slot collision", () =>
146186
Effect.gen(function* () {
147187
const fs = yield* FileSystem.FileSystem;

‎apps/server/scripts/migrate-dev-db.ts‎

Lines changed: 93 additions & 28 deletions
Original file line numberDiff line numberDiff line change
@@ -6,9 +6,12 @@
66
*
77
* `vp run migrate-dev-db` from a worktree:
88
* 1. Nukes `<worktree>/.t3/userdata/statev2.sqlite`.
9-
* 2. Snapshots the real db (`~/.t3/userdata/statev2.sqlite`, read-only
10-
* VACUUM INTO) and prunes it to the most recently updated projects and,
11-
* per project, the most recent threads that have fully stopped, with
9+
* 2. Copies a slice of the real db (`~/.t3/userdata/statev2.sqlite`,
10+
* attached read-only): the schema, and every row except those of
11+
* deleted, archived, and settled threads, which are nearly all of a
12+
* long-lived database. It then prunes that to the most recently updated
13+
* projects and, per project, the most recent threads that have fully
14+
* stopped, with
1215
* their forks and subagents. Working, settled, and archived threads, and
1316
* threads with pending recovery, are skipped, and scheduled tasks and
1417
* queued effects are dropped, so the dev server never adopts live work.
@@ -28,6 +31,7 @@
2831
import * as NodeRuntime from "@effect/platform-node/NodeRuntime";
2932
import * as NodeServices from "@effect/platform-node/NodeServices";
3033
import * as NodeOS from "node:os";
34+
import { pathToFileURL } from "node:url";
3135
import { resolveWorktreeT3Home } from "@t3tools/shared/devHome";
3236
import * as Console from "effect/Console";
3337
import * as Effect from "effect/Effect";
@@ -242,6 +246,82 @@ const RECOVERY_KINDS: ReadonlyArray<ProjectionStore.ProjectionRecoveryKind> = [
242246
"delegated-completions",
243247
];
244248

249+
/** Tables the prune empties. The copy skips their rows. */
250+
const CLEARED_TABLES: ReadonlyArray<string> = [
251+
// Pending work the dev server would otherwise pick up and run.
252+
"scheduled_tasks",
253+
"orchestration_v2_effect_outbox",
254+
"orchestration_v2_thread_launch_workflows",
255+
"orchestration_command_receipts",
256+
"provider_session_runtime",
257+
"auth_sessions",
258+
"auth_pairing_links",
259+
];
260+
261+
/** Shared between threads; the prune keeps the ones a kept thread is bound to. */
262+
const PROVIDER_SESSIONS_TABLE = "orchestration_v2_projection_provider_sessions";
263+
264+
/** Whether the thread aliased `root` is shown: not deleted, archived, or settled. */
265+
const rootIsVisible = (sql: SqlClient.SqlClient) => sql`
266+
root.deleted_at IS NULL
267+
AND json_extract(root.payload_json, '$.deletedAt') IS NULL
268+
AND json_extract(root.payload_json, '$.archivedAt') IS NULL
269+
AND json_extract(root.payload_json, '$.settledAt') IS NULL
270+
AND json_extract(root.payload_json, '$.settledOverride') IS NOT 'settled'`;
271+
272+
/**
273+
* Copies the source into the empty snapshot, without the rows of thread families whose root is
274+
* hidden. The prune drops those anyway, and they hold nearly all of a long-lived database's
275+
* events, so copying them is what used to make this take a full-size copy of the real db.
276+
* One transaction reads the source, so the copy is consistent while the real server writes.
277+
*/
278+
const copySourceSlice = Effect.fn("copyDevDbSourceSlice")(function* (sourcePath: string) {
279+
const sql = yield* SqlClient.SqlClient;
280+
yield* sql`ATTACH DATABASE ${`${pathToFileURL(sourcePath).href}?mode=ro`} AS src`;
281+
const schema = yield* sql<{ type: string; name: string; sql: string }>`
282+
SELECT type, name, sql FROM src.sqlite_master
283+
WHERE sql IS NOT NULL AND name NOT LIKE 'sqlite_%'`;
284+
const threadColumns = yield* sql<{ name: string; notnull: number }>`
285+
SELECT m.name, c."notnull" FROM src.sqlite_master m, pragma_table_info(m.name, 'src') c
286+
WHERE m.type = 'table' AND c.name = 'thread_id'`;
287+
const threadIdNullable = new Map(threadColumns.map((row) => [row.name, row.notnull === 0]));
288+
const tables = schema.filter((entry) => entry.type === "table");
289+
290+
yield* sql.withTransaction(
291+
Effect.gen(function* () {
292+
for (const table of tables) yield* sql.unsafe(table.sql).unprepared;
293+
yield* sql`CREATE TEMP TABLE copied_threads (thread_id TEXT PRIMARY KEY)`;
294+
yield* sql`INSERT OR IGNORE INTO copied_threads
295+
SELECT family.thread_id FROM src.orchestration_v2_projection_threads family
296+
JOIN src.orchestration_v2_projection_threads root ON root.thread_id =
297+
COALESCE(json_extract(family.payload_json, '$.lineage.rootThreadId'), family.thread_id)
298+
WHERE ${rootIsVisible(sql)}`;
299+
for (const { name } of tables) {
300+
if (CLEARED_TABLES.includes(name)) continue;
301+
const nullable = threadIdNullable.get(name);
302+
const filter =
303+
name === "orchestration_events"
304+
? // Two ranges rather than `<>`, so SQLite seeks the (aggregate_kind, stream_id)
305+
// index instead of scanning every event.
306+
`WHERE (aggregate_kind = 'thread' AND stream_id IN (SELECT thread_id FROM copied_threads))
307+
OR aggregate_kind < 'thread' OR aggregate_kind > 'thread'`
308+
: nullable === undefined || name === PROVIDER_SESSIONS_TABLE
309+
? ""
310+
: `WHERE thread_id IN (SELECT thread_id FROM copied_threads)${nullable ? " OR thread_id IS NULL" : ""}`;
311+
yield* sql.unsafe(`INSERT INTO main."${name}" SELECT * FROM src."${name}" ${filter}`)
312+
.unprepared;
313+
}
314+
// The source's AUTOINCREMENT high-water marks, so new events append after them.
315+
yield* sql`DELETE FROM main.sqlite_sequence`;
316+
yield* sql`INSERT INTO main.sqlite_sequence SELECT name, seq FROM src.sqlite_sequence`;
317+
for (const entry of schema) {
318+
if (entry.type !== "table") yield* sql.unsafe(entry.sql).unprepared;
319+
}
320+
}),
321+
);
322+
yield* sql`DETACH DATABASE src`;
323+
});
324+
245325
const pruneSnapshot = Effect.fn("pruneDevDbSnapshot")(function* (input: RunMigrateDevDbInput) {
246326
const sql = yield* SqlClient.SqlClient;
247327

@@ -278,11 +358,7 @@ const pruneSnapshot = Effect.fn("pruneDevDbSnapshot")(function* (input: RunMigra
278358
SELECT f.root_id, root.project_id, MAX(f.updated_at) AS updated_at
279359
FROM thread_families f
280360
JOIN orchestration_v2_projection_threads root ON root.thread_id = f.root_id
281-
WHERE root.deleted_at IS NULL
282-
AND json_extract(root.payload_json, '$.deletedAt') IS NULL
283-
AND json_extract(root.payload_json, '$.archivedAt') IS NULL
284-
AND json_extract(root.payload_json, '$.settledAt') IS NULL
285-
AND json_extract(root.payload_json, '$.settledOverride') IS NOT 'settled'
361+
WHERE ${rootIsVisible(sql)}
286362
GROUP BY f.root_id, root.project_id
287363
HAVING SUM(f.thread_id IN (SELECT thread_id FROM live_threads)) = 0`;
288364

@@ -323,13 +399,13 @@ const pruneSnapshot = Effect.fn("pruneDevDbSnapshot")(function* (input: RunMigra
323399
const threadTables = yield* sql<{ name: string }>`
324400
SELECT m.name FROM sqlite_master m, pragma_table_info(m.name) c
325401
WHERE m.type = 'table' AND c.name = 'thread_id'
326-
AND m.name <> 'orchestration_v2_projection_provider_sessions'`;
402+
AND m.name <> ${PROVIDER_SESSIONS_TABLE}`;
327403

328404
yield* sql.withTransaction(
329405
Effect.gen(function* () {
330406
yield* sql`DELETE FROM projection_projects
331407
WHERE project_id NOT IN (SELECT project_id FROM kept_projects)`;
332-
yield* sql`DELETE FROM orchestration_v2_projection_provider_sessions
408+
yield* sql`DELETE FROM ${sql(PROVIDER_SESSIONS_TABLE)}
333409
WHERE COALESCE(thread_id, '') NOT IN (SELECT thread_id FROM kept_threads)
334410
AND provider_session_id NOT IN (
335411
SELECT provider_session_id FROM orchestration_v2_projection_provider_session_bindings
@@ -348,14 +424,9 @@ const pruneSnapshot = Effect.fn("pruneDevDbSnapshot")(function* (input: RunMigra
348424
AND stream_id NOT IN (SELECT thread_id FROM kept_threads))
349425
OR (aggregate_kind = 'project'
350426
AND stream_id NOT IN (SELECT project_id FROM kept_projects))`;
351-
// Pending work the dev server would otherwise pick up and run.
352-
yield* sql`DELETE FROM scheduled_tasks`;
353-
yield* sql`DELETE FROM orchestration_v2_effect_outbox`;
354-
yield* sql`DELETE FROM orchestration_v2_thread_launch_workflows`;
355-
yield* sql`DELETE FROM orchestration_command_receipts`;
356-
yield* sql`DELETE FROM provider_session_runtime`;
357-
yield* sql`DELETE FROM auth_sessions`;
358-
yield* sql`DELETE FROM auth_pairing_links`;
427+
for (const table of CLEARED_TABLES) {
428+
yield* sql`DELETE FROM ${sql(table)}`;
429+
}
359430
}),
360431
);
361432

@@ -457,22 +528,16 @@ export const runMigrateDevDb = Effect.fn("runMigrateDevDb")(function* (
457528
);
458529

459530
yield* removeDatabaseFiles(snapshotPath);
460-
// The snapshot is a full-size copy of the source; make sure it is removed
461-
// even when a phase fails partway through.
531+
// Make sure the snapshot is removed even when a phase fails partway through.
462532
const { executedMigrations, pruned } = yield* Effect.gen(function* () {
463-
yield* Console.log(`Snapshotting ${sourcePath} (read-only)...`);
464-
yield* Effect.gen(function* () {
465-
const sql = yield* SqlClient.SqlClient;
466-
yield* sql`VACUUM INTO ${snapshotPath}`;
467-
}).pipe(
468-
Effect.provide(NodeSqliteClient.layer({ filename: sourcePath, readonly: true })),
533+
yield* Console.log(`Copying visible threads from ${sourcePath} (read-only)...`);
534+
yield* copySourceSlice(sourcePath).pipe(
535+
Effect.provide(NodeSqliteClient.layer({ filename: snapshotPath })),
469536
wrapPhase("snapshot", sourcePath),
470537
);
471538

472539
// Migrate before pruning: a source older than this checkout would
473540
// otherwise crash the prune queries on columns that don't exist yet.
474-
// Running against the full snapshot also exercises new migrations on the
475-
// same data volume the real database would face.
476541
yield* Console.log("Running migrations on the snapshot...");
477542
const executed = yield* Effect.gen(function* () {
478543
const sql = yield* SqlClient.SqlClient;

0 commit comments

Comments
 (0)