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