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.
2831import * as NodeRuntime from "@effect/platform-node/NodeRuntime" ;
2932import * as NodeServices from "@effect/platform-node/NodeServices" ;
3033import * as NodeOS from "node:os" ;
34+ import { pathToFileURL } from "node:url" ;
3135import { resolveWorktreeT3Home } from "@t3tools/shared/devHome" ;
3236import * as Console from "effect/Console" ;
3337import * 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+
245325const 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