1import * as Migrator from "effect/sql/Migrator";
2import * as Effect from "effect/Effect";
3import * as SqlClient from "effect/sql/SqlClient";
4import {
5 DATABASE_MIGRATION_PROGRESS_MESSAGE,
6 DATABASE_MIGRATIONS_FINISHED_MESSAGE,
7 DATABASE_MIGRATIONS_STARTED_MESSAGE,
8} from "@t3tools/shared/databaseMigrationLog";
9import { reconcileV2PreviewMigration } from "./reconcileV2PreviewMigration.ts";
10
11import Migration0001 from "./Migrations/001_OrchestrationEvents.ts";
12import Migration0002 from "./Migrations/002_OrchestrationCommandReceipts.ts";
13import Migration0003 from "./Migrations/003_CheckpointDiffBlobs.ts";
14import Migration0004 from "./Migrations/004_ProviderSessionRuntime.ts";
15import Migration0005 from "./Migrations/005_Projections.ts";
16import Migration0006 from "./Migrations/006_ProjectionThreadSessionRuntimeModeColumns.ts";
17import Migration0007 from "./Migrations/007_ProjectionThreadMessageAttachments.ts";
18import Migration0008 from "./Migrations/008_ProjectionThreadActivitySequence.ts";
19import Migration0009 from "./Migrations/009_ProviderSessionRuntimeMode.ts";
20import Migration0010 from "./Migrations/010_ProjectionThreadsRuntimeMode.ts";
21import Migration0011 from "./Migrations/011_OrchestrationThreadCreatedRuntimeMode.ts";
22import Migration0012 from "./Migrations/012_ProjectionThreadsInteractionMode.ts";
23import Migration0013 from "./Migrations/013_ProjectionThreadProposedPlans.ts";
24import Migration0014 from "./Migrations/014_ProjectionThreadProposedPlanImplementation.ts";
25import Migration0015 from "./Migrations/015_ProjectionTurnsSourceProposedPlan.ts";
26import Migration0016 from "./Migrations/016_CanonicalizeModelSelections.ts";
27import Migration0017 from "./Migrations/017_ProjectionThreadsArchivedAt.ts";
28import Migration0018 from "./Migrations/018_ProjectionThreadsArchivedAtIndex.ts";
29import Migration0019 from "./Migrations/019_ProjectionSnapshotLookupIndexes.ts";
30import Migration0020 from "./Migrations/020_AuthAccessManagement.ts";
31import Migration0021 from "./Migrations/021_AuthSessionClientMetadata.ts";
32import Migration0022 from "./Migrations/022_AuthSessionLastConnectedAt.ts";
33import Migration0023 from "./Migrations/023_ProjectionThreadShellSummary.ts";
34import Migration0024 from "./Migrations/024_BackfillProjectionThreadShellSummary.ts";
35import Migration0025 from "./Migrations/025_CleanupInvalidProjectionPendingApprovals.ts";
36import Migration0026 from "./Migrations/026_CanonicalizeModelSelectionOptions.ts";
37import Migration0027 from "./Migrations/027_ProviderSessionRuntimeInstanceId.ts";
38import Migration0028 from "./Migrations/028_ProjectionThreadSessionInstanceId.ts";
39import Migration0029 from "./Migrations/029_ProjectionThreadDetailOrderingIndexes.ts";
40import Migration0030 from "./Migrations/030_ProjectionThreadShellArchiveIndexes.ts";
41import Migration0031 from "./Migrations/031_AuthAuthorizationScopes.ts";
42import Migration0032 from "./Migrations/032_AuthPairingProofKeyThumbprint.ts";
43import Migration0033 from "./Migrations/033_ProjectionThreadsSettled.ts";
44import Migration0034 from "./Migrations/034_ProjectionThreadsSnoozed.ts";
45import Migration0035 from "./Migrations/035_ProjectionThreadTitleRegeneration.ts";
46import Migration0036 from "./Migrations/036_ProjectionThreadsPinned.ts";
47import Migration0037 from "./Migrations/037_ProjectionTurnsKeysetIndex.ts";
48import Migration0038 from "./Migrations/038_ProjectionThreadsPinOrderKey.ts";
49import Migration0039 from "./Migrations/039_ProjectionProjectsDefaultThreadEnvMode.ts";
50import Migration0040 from "./Migrations/040_ProjectionProjectFaviconPath.ts";
51import Migration0041 from "./Migrations/041_AuthSessionClientConnection.ts";
52import Migration0042 from "./Migrations/042_ProjectionThreadLinkedPullRequest.ts";
53import Migration0043 from "./Migrations/043_ProjectionThreadsUnsettledAt.ts";
54import Migration0044 from "./Migrations/044_ClearAutomaticProjectModelDefaults.ts";
55import Migration0045 from "./Migrations/045_ProjectionProjectsAutoPull.ts";
56import Migration0046 from "./Migrations/046_RepairAutomaticSettlementTimestamps.ts";
57import Migration0047 from "./Migrations/047_ProjectionProjectIcon.ts";
58import Migration0048 from "./Migrations/048_ProjectionThreadBranchPullRequest.ts";
59import Migration0049 from "./Migrations/049_ProjectionThreadsActiveOrderKey.ts";
60import Migration0050 from "./Migrations/050_ProjectionThreadPullRequests.ts";
61import Migration0051 from "./Migrations/051_ProjectionThreadMessageContext.ts";
62import Migration0052 from "./Migrations/052_ProjectionThreadTitleState.ts";
63import Migration0053 from "./Migrations/053_PullRequestFilesViewed.ts";
64import Migration0054 from "./Migrations/054_ProjectionThreadsAutoSettleDisabledAt.ts";
65import Migration0055 from "./Migrations/055_OrchestrationV2.ts";
66import Migration0056 from "./Migrations/056_RemoveRedundantProjectionIndexes.ts";
67import Migration0057 from "./Migrations/057_ScheduledTaskWebhooks.ts";
68import Migration0058 from "./Migrations/058_WebhookRelayDeliveries.ts";
69import Migration0059 from "./Migrations/059_McpAppModelContext.ts";
70import Migration0060 from "./Migrations/060_ThreadSnapshotWindowIndexes.ts";
71import Migration0061 from "./Migrations/061_PullRequestReviewGuides.ts";
72import Migration0062 from "./Migrations/062_ThreadFailureLookupIndex.ts";
73import Migration0063 from "./Migrations/063_DropUnusedV1Projections.ts";
74import Migration0064 from "./Migrations/064_CanonicalizeThreadPullRequestLinks.ts";
75import Migration0065 from "./Migrations/065_CodexUsageNativeTurnIndex.ts";
76import Migration0066 from "./Migrations/066_RepairClientSettlementTimestamps.ts";
77import Migration0067 from "./Migrations/067_WebhookDedupe.ts";
78import Migration0068 from "./Migrations/068_ShellStopRequestIndex.ts";
79import Migration0069 from "./Migrations/069_ThreadMessageSearch.ts";
80import Migration0070 from "./Migrations/070_PeerLinks.ts";
81import Migration0071 from "./Migrations/071_PeerLinkFence.ts";
82import Migration0072 from "./Migrations/072_WaitingLinkRequestIndex.ts";
83import Migration0073 from "./Migrations/073_ActiveTurnItemIndexes.ts";
84import Migration0074 from "./Migrations/074_ExcludeAttachmentPlaceholdersFromSearch.ts";
85import Migration0075 from "./Migrations/075_SubagentDeliveryState.ts";
86import Migration0076 from "./Migrations/076_ProjectionProjectsPinArchive.ts";
87import Migration0077 from "./Migrations/077_RepairBooleanSettledOverride.ts";
88import Migration0078 from "./Migrations/078_DropOrchestrationEventCorrelationIndex.ts";
89import Migration0079 from "./Migrations/079_DelegatedRecoveryIndexes.ts";
90import Migration0080 from "./Migrations/080_ScheduledTaskThreadMode.ts";
91import Migration0081 from "./Migrations/081_ProjectionMessageSearchText.ts";
92import Migration0082 from "./Migrations/082_TurnItemConversationIndex.ts";
93import Migration0083 from "./Migrations/083_CommandReadIndexes.ts";
94import Migration0084 from "./Migrations/084_LastExecutedRunIndex.ts";
95import Migration0085 from "./Migrations/085_TurnItemCounts.ts";
96import Migration0086 from "./Migrations/086_QueuedTurnInputIndex.ts";
97import Migration0087 from "./Migrations/087_ProjectionProjectsCustomTitle.ts";
98import Migration0088 from "./Migrations/088_TurnItemTypeStatusIndex.ts";
99
100const migrationEntries = [
101 [1, "OrchestrationEvents", Migration0001],
102 [2, "OrchestrationCommandReceipts", Migration0002],
103 [3, "CheckpointDiffBlobs", Migration0003],
104 [4, "ProviderSessionRuntime", Migration0004],
105 [5, "Projections", Migration0005],
106 [6, "ProjectionThreadSessionRuntimeModeColumns", Migration0006],
107 [7, "ProjectionThreadMessageAttachments", Migration0007],
108 [8, "ProjectionThreadActivitySequence", Migration0008],
109 [9, "ProviderSessionRuntimeMode", Migration0009],
110 [10, "ProjectionThreadsRuntimeMode", Migration0010],
111 [11, "OrchestrationThreadCreatedRuntimeMode", Migration0011],
112 [12, "ProjectionThreadsInteractionMode", Migration0012],
113 [13, "ProjectionThreadProposedPlans", Migration0013],
114 [14, "ProjectionThreadProposedPlanImplementation", Migration0014],
115 [15, "ProjectionTurnsSourceProposedPlan", Migration0015],
116 [16, "CanonicalizeModelSelections", Migration0016],
117 [17, "ProjectionThreadsArchivedAt", Migration0017],
118 [18, "ProjectionThreadsArchivedAtIndex", Migration0018],
119 [19, "ProjectionSnapshotLookupIndexes", Migration0019],
120 [20, "AuthAccessManagement", Migration0020],
121 [21, "AuthSessionClientMetadata", Migration0021],
122 [22, "AuthSessionLastConnectedAt", Migration0022],
123 [23, "ProjectionThreadShellSummary", Migration0023],
124 [24, "BackfillProjectionThreadShellSummary", Migration0024],
125 [25, "CleanupInvalidProjectionPendingApprovals", Migration0025],
126 [26, "CanonicalizeModelSelectionOptions", Migration0026],
127 [27, "ProviderSessionRuntimeInstanceId", Migration0027],
128 [28, "ProjectionThreadSessionInstanceId", Migration0028],
129 [29, "ProjectionThreadDetailOrderingIndexes", Migration0029],
130 [30, "ProjectionThreadShellArchiveIndexes", Migration0030],
131 [31, "AuthAuthorizationScopes", Migration0031],
132 [32, "AuthPairingProofKeyThumbprint", Migration0032],
133 [33, "ProjectionThreadsSettled", Migration0033],
134 [34, "ProjectionThreadsSnoozed", Migration0034],
135 [35, "ProjectionThreadTitleRegeneration", Migration0035],
136 [36, "ProjectionThreadsPinned", Migration0036],
137 [37, "ProjectionTurnsKeysetIndex", Migration0037],
138 [38, "ProjectionThreadsPinOrderKey", Migration0038],
139 [39, "ProjectionProjectsDefaultThreadEnvMode", Migration0039],
140 [40, "ProjectionProjectFaviconPath", Migration0040],
141 [41, "AuthSessionClientConnection", Migration0041],
142 [42, "ProjectionThreadLinkedPullRequest", Migration0042],
143 [43, "ProjectionThreadsUnsettledAt", Migration0043],
144 [44, "ClearAutomaticProjectModelDefaults", Migration0044],
145 [45, "ProjectionProjectsAutoPull", Migration0045],
146 [46, "RepairAutomaticSettlementTimestamps", Migration0046],
147 [47, "ProjectionProjectIcon", Migration0047],
148 [48, "ProjectionThreadBranchPullRequest", Migration0048],
149 [49, "ProjectionThreadsActiveOrderKey", Migration0049],
150 [50, "ProjectionThreadPullRequests", Migration0050],
151 [51, "ProjectionThreadMessageContext", Migration0051],
152 [52, "ProjectionThreadTitleState", Migration0052],
153 [53, "PullRequestFilesViewed", Migration0053],
154 [54, "ProjectionThreadsAutoSettleDisabledAt", Migration0054],
155 // Preserve this migration's schema. Future V2 schema changes need new migrations.
156 [55, "OrchestrationV2", Migration0055],
157 [56, "RemoveRedundantProjectionIndexes", Migration0056],
158 [57, "ScheduledTaskWebhooks", Migration0057],
159 [58, "WebhookRelayDeliveries", Migration0058],
160 [59, "McpAppModelContext", Migration0059],
161 [60, "ThreadSnapshotWindowIndexes", Migration0060],
162 [61, "PullRequestReviewGuides", Migration0061],
163 [62, "ThreadFailureLookupIndex", Migration0062],
164 [63, "DropUnusedV1Projections", Migration0063],
165 [64, "CanonicalizeThreadPullRequestLinks", Migration0064],
166 [65, "CodexUsageNativeTurnIndex", Migration0065],
167 [66, "RepairClientSettlementTimestamps", Migration0066],
168 [67, "WebhookDedupe", Migration0067],
169 [68, "ShellStopRequestIndex", Migration0068],
170 [69, "ThreadMessageSearch", Migration0069],
171 [70, "PeerLinks", Migration0070],
172 [71, "PeerLinkFence", Migration0071],
173 [72, "WaitingLinkRequestIndex", Migration0072],
174 [73, "ActiveTurnItemIndexes", Migration0073],
175 [74, "ExcludeAttachmentPlaceholdersFromSearch", Migration0074],
176 [75, "SubagentDeliveryState", Migration0075],
177 [76, "ProjectionProjectsPinArchive", Migration0076],
178 [77, "RepairBooleanSettledOverride", Migration0077],
179 [78, "DropOrchestrationEventCorrelationIndex", Migration0078],
180 [79, "DelegatedRecoveryIndexes", Migration0079],
181 [80, "ScheduledTaskThreadMode", Migration0080],
182 [81, "ProjectionMessageSearchText", Migration0081],
183 [82, "TurnItemConversationIndex", Migration0082],
184 [83, "CommandReadIndexes", Migration0083],
185 [84, "LastExecutedRunIndex", Migration0084],
186 [85, "TurnItemCounts", Migration0085],
187 [86, "QueuedTurnInputIndex", Migration0086],
188 [87, "ProjectionProjectsCustomTitle", Migration0087],
189 [88, "TurnItemTypeStatusIndex", Migration0088],
190] as const;
191
192export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
193
194const makeMigrationLoader = (throughId?: number) =>
195 Migrator.fromRecord(
196 Object.fromEntries(
197 migrationEntries
198 .filter(([id]) => throughId === undefined || id <= throughId)
199 .map(([id, name, migration]) => [`${id}_${name}`, migration]),
200 ),
201 );
202
203const FIRST_V2_MIGRATION_ID = 55;
204
205export function skippedV2Migrations(
206 recorded: ReadonlyArray<{ readonly id: number; readonly name: string }>,
207 throughId?: number,
208): string[] {
209 const latestRecordedId = Math.max(0, ...recorded.map((row) => row.id));
210 const recordedNames = new Map(recorded.map((row) => [row.id, row.name]));
211 return migrationManifest.flatMap(([id, name]) => {
212 if (
213 id < FIRST_V2_MIGRATION_ID ||
214 id > latestRecordedId ||
215 (throughId !== undefined && id > throughId)
216 )
217 return [];
218 const recordedName = recordedNames.get(id);
219 return recordedName === name ? [] : [`${id}_${name} (recorded: ${recordedName ?? "nothing"})`];
220 });
221}
222
223// Migrators skip ids up to the highest recorded; reject collisions that would skip V2 schema.
224const assertV2MigrationsCanRun = Effect.fn("assertV2MigrationsCanRun")(function* (
225 throughId?: number,
226) {
227 const sql = yield* SqlClient.SqlClient;
228 const tables = yield* sql`
229 SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'effect_sql_migrations'
230 `;
231 if (tables.length === 0) return;
232 const recorded = yield* sql<{
233 readonly migration_id: number;
234 readonly name: string;
235 }>`SELECT migration_id, name FROM effect_sql_migrations`;
236 const skipped = skippedV2Migrations(
237 recorded.map((row) => ({ id: row.migration_id, name: row.name })),
238 throughId,
239 );
240 const latestRecordedId = Math.max(0, ...recorded.map((row) => row.migration_id));
241 if (skipped.length === 0) return;
242 return yield* new Migrator.MigrationError({
243 kind: "BadState",
244 message: `This database's migration history would skip required schema migrations: ${skipped.join(", ")}. The migrator skips every id up to the highest recorded id (${latestRecordedId}), regardless of name. It was probably written by a different build. Start the build that wrote it, or report this migration history so an upgrade path can be added.`,
245 });
246});
247
248const latestMigrationId = Math.max(...migrationEntries.map(([id]) => id));
249
250// Mirrors the Migrator, which runs only ids above the highest recorded, plus the V2-preview reconciliation.
251export const hasPendingMigrations = Effect.fn("hasPendingMigrations")(function* () {
252 const sql = yield* SqlClient.SqlClient;
253 const tables = yield* sql`
254 SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'effect_sql_migrations'
255 `;
256 if (tables.length === 0) return true;
257 const rows = yield* sql<{ readonly latestId: number | null; readonly previewRows: number }>`
258 SELECT
259 MAX(migration_id) AS latestId,
260 SUM(name = 'OrchestrationV2' AND migration_id IN (53, 54)) AS previewRows
261 FROM effect_sql_migrations
262 `;
263 const row = rows[0];
264 return (row?.latestId ?? 0) < latestMigrationId || (row?.previewRows ?? 0) > 0;
265});
266
267const run = Migrator.make({});
268
269export interface RunMigrationsOptions {
270 readonly toMigrationInclusive?: number | undefined;
271}
272
273export const runMigrations = Effect.fn("runMigrations")(function* ({
274 toMigrationInclusive,
275}: RunMigrationsOptions = {}) {
276 // The desktop waits past its readiness budget until the finished line follows this one.
277 if (yield* hasPendingMigrations()) yield* Effect.log(DATABASE_MIGRATIONS_STARTED_MESSAGE);
278 const includesV2 =
279 toMigrationInclusive === undefined || toMigrationInclusive >= FIRST_V2_MIGRATION_ID;
280 const previewMigrations = includesV2 ? yield* reconcileV2PreviewMigration() : [];
281 if (includesV2) {
282 yield* assertV2MigrationsCanRun(toMigrationInclusive);
283 }
284 const sql = yield* SqlClient.SqlClient;
285 const ledger = yield* sql`
286 SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'effect_sql_migrations'
287 `;
288 const [latest] =
289 ledger.length === 0
290 ? []
291 : yield* sql<{
292 readonly id: number | null;
293 }>`SELECT MAX(migration_id) AS id FROM effect_sql_migrations`;
294 const latestRecordedId = latest?.id ?? 0;
295 const pending = migrationEntries.filter(
296 ([id]) =>
297 id > latestRecordedId && (toMigrationInclusive === undefined || id <= toMigrationInclusive),
298 );
299 // One transaction per migration: an index build on a large database can take minutes, and a
300 // restart then resumes after the last finished migration instead of rolling all of them back.
301 const executedMigrations: Array<readonly [id: number, name: string]> = [...previewMigrations];
302 for (const [index, [id, name]] of pending.entries()) {
303 if (latestRecordedId > 0) {
304 yield* Effect.log(DATABASE_MIGRATION_PROGRESS_MESSAGE).pipe(
305 Effect.annotateLogs({
306 migration: `${id}_${name}`,
307 progress: `${index + 1} of ${pending.length}`,
308 }),
309 );
310 }
311 executedMigrations.push(...(yield* run({ loader: makeMigrationLoader(id) })));
312 }
313 const migrations = executedMigrations.map(([id, name]) => `${id}_${name}`);
314 yield* migrations.length === 0
315 ? Effect.logDebug("Database schema is current")
316 : Effect.log(DATABASE_MIGRATIONS_FINISHED_MESSAGE).pipe(Effect.annotateLogs({ migrations }));
317
318 const recorded = yield* sql<{
319 readonly migration_id: number;
320 readonly name: string;
321 }>`SELECT migration_id, name FROM effect_sql_migrations`;
322 const manifestNames = new Map<number, string>(migrationEntries.map(([id, name]) => [id, name]));
323 const divergent = recorded.flatMap((row) => {
324 const expected = manifestNames.get(row.migration_id);
325 if (expected === undefined) {
326 return [`${row.migration_id}:${row.name} (unknown to this build)`];
327 }
328 return expected === row.name
329 ? []
330 : [`${row.migration_id}:${row.name} (this build: ${expected})`];
331 });
332 if (divergent.length > 0) {
333 yield* Effect.logWarning(
334 "Database migration history diverges from this build; recorded migration ids are skipped, not reconciled by name.",
335 ).pipe(Effect.annotateLogs({ divergent }));
336 }
337 return executedMigrations;
338});