1/**
2 * Migration runner with an inline loader.
3 *
4 * Uses Migrator.make with fromRecord to define migrations inline.
5 * All migrations are statically imported - no dynamic file system loading.
6 *
7 * `runMigrations` is called by the SQLite persistence layer at startup, so the
8 * schema is always up to date before the application starts.
9 */
10
11import * as Migrator from "effect/sql/Migrator";
12import * as Effect from "effect/Effect";
13import * as SqlClient from "effect/sql/SqlClient";
14import { reconcileV2PreviewMigration } from "./reconcileV2PreviewMigration.ts";
15
16// Import all migrations statically
17import Migration0001 from "./Migrations/001_OrchestrationEvents.ts";
18import Migration0002 from "./Migrations/002_OrchestrationCommandReceipts.ts";
19import Migration0003 from "./Migrations/003_CheckpointDiffBlobs.ts";
20import Migration0004 from "./Migrations/004_ProviderSessionRuntime.ts";
21import Migration0005 from "./Migrations/005_Projections.ts";
22import Migration0006 from "./Migrations/006_ProjectionThreadSessionRuntimeModeColumns.ts";
23import Migration0007 from "./Migrations/007_ProjectionThreadMessageAttachments.ts";
24import Migration0008 from "./Migrations/008_ProjectionThreadActivitySequence.ts";
25import Migration0009 from "./Migrations/009_ProviderSessionRuntimeMode.ts";
26import Migration0010 from "./Migrations/010_ProjectionThreadsRuntimeMode.ts";
27import Migration0011 from "./Migrations/011_OrchestrationThreadCreatedRuntimeMode.ts";
28import Migration0012 from "./Migrations/012_ProjectionThreadsInteractionMode.ts";
29import Migration0013 from "./Migrations/013_ProjectionThreadProposedPlans.ts";
30import Migration0014 from "./Migrations/014_ProjectionThreadProposedPlanImplementation.ts";
31import Migration0015 from "./Migrations/015_ProjectionTurnsSourceProposedPlan.ts";
32import Migration0016 from "./Migrations/016_CanonicalizeModelSelections.ts";
33import Migration0017 from "./Migrations/017_ProjectionThreadsArchivedAt.ts";
34import Migration0018 from "./Migrations/018_ProjectionThreadsArchivedAtIndex.ts";
35import Migration0019 from "./Migrations/019_ProjectionSnapshotLookupIndexes.ts";
36import Migration0020 from "./Migrations/020_AuthAccessManagement.ts";
37import Migration0021 from "./Migrations/021_AuthSessionClientMetadata.ts";
38import Migration0022 from "./Migrations/022_AuthSessionLastConnectedAt.ts";
39import Migration0023 from "./Migrations/023_ProjectionThreadShellSummary.ts";
40import Migration0024 from "./Migrations/024_BackfillProjectionThreadShellSummary.ts";
41import Migration0025 from "./Migrations/025_CleanupInvalidProjectionPendingApprovals.ts";
42import Migration0026 from "./Migrations/026_CanonicalizeModelSelectionOptions.ts";
43import Migration0027 from "./Migrations/027_ProviderSessionRuntimeInstanceId.ts";
44import Migration0028 from "./Migrations/028_ProjectionThreadSessionInstanceId.ts";
45import Migration0029 from "./Migrations/029_ProjectionThreadDetailOrderingIndexes.ts";
46import Migration0030 from "./Migrations/030_ProjectionThreadShellArchiveIndexes.ts";
47import Migration0031 from "./Migrations/031_AuthAuthorizationScopes.ts";
48import Migration0032 from "./Migrations/032_AuthPairingProofKeyThumbprint.ts";
49import Migration0033 from "./Migrations/033_ProjectionThreadsSettled.ts";
50import Migration0034 from "./Migrations/034_ProjectionThreadsSnoozed.ts";
51import Migration0035 from "./Migrations/035_ProjectionThreadTitleRegeneration.ts";
52import Migration0036 from "./Migrations/036_ProjectionThreadsPinned.ts";
53import Migration0037 from "./Migrations/037_ProjectionTurnsKeysetIndex.ts";
54import Migration0038 from "./Migrations/038_ProjectionThreadsPinOrderKey.ts";
55import Migration0039 from "./Migrations/039_ProjectionProjectsDefaultThreadEnvMode.ts";
56import Migration0040 from "./Migrations/040_ProjectionProjectFaviconPath.ts";
57import Migration0041 from "./Migrations/041_AuthSessionClientConnection.ts";
58import Migration0042 from "./Migrations/042_ProjectionThreadLinkedPullRequest.ts";
59import Migration0043 from "./Migrations/043_ProjectionThreadsUnsettledAt.ts";
60import Migration0044 from "./Migrations/044_ClearAutomaticProjectModelDefaults.ts";
61import Migration0045 from "./Migrations/045_ProjectionProjectsAutoPull.ts";
62import Migration0046 from "./Migrations/046_RepairAutomaticSettlementTimestamps.ts";
63import Migration0047 from "./Migrations/047_ProjectionProjectIcon.ts";
64import Migration0048 from "./Migrations/048_ProjectionThreadBranchPullRequest.ts";
65import Migration0049 from "./Migrations/049_ProjectionThreadsActiveOrderKey.ts";
66import Migration0050 from "./Migrations/050_ProjectionThreadPullRequests.ts";
67import Migration0051 from "./Migrations/051_ProjectionThreadMessageContext.ts";
68import Migration0052 from "./Migrations/052_ProjectionThreadTitleState.ts";
69import Migration0053 from "./Migrations/053_PullRequestFilesViewed.ts";
70import Migration0054 from "./Migrations/054_ProjectionThreadsAutoSettleDisabledAt.ts";
71import Migration0055 from "./Migrations/055_OrchestrationV2.ts";
72import Migration0056 from "./Migrations/056_RemoveRedundantProjectionIndexes.ts";
73import Migration0057 from "./Migrations/057_ScheduledTaskWebhooks.ts";
74import Migration0058 from "./Migrations/058_WebhookRelayDeliveries.ts";
75import Migration0059 from "./Migrations/059_McpAppModelContext.ts";
76import Migration0060 from "./Migrations/060_ThreadSnapshotWindowIndexes.ts";
77import Migration0061 from "./Migrations/061_PullRequestReviewGuides.ts";
78import Migration0062 from "./Migrations/062_ThreadFailureLookupIndex.ts";
79import Migration0063 from "./Migrations/063_TranscriptArchives.ts";
80import Migration0064 from "./Migrations/064_AuthSessionDelegation.ts";
81import Migration0065 from "./Migrations/065_HubInbox.ts";
82import Migration0066 from "./Migrations/066_HostedAuthDevices.ts";
83import Migration0067 from "./Migrations/067_ArchiveCatalogAndSync.ts";
84
85/**
86 * Migration loader with all migrations defined inline.
87 *
88 * Key format: "{id}_{name}" where:
89 * - id: numeric migration ID (determines execution order)
90 * - name: descriptive name for the migration
91 *
92 * Uses Migrator.fromRecord which parses the key format and
93 * returns migrations sorted by ID.
94 */
95export const migrationEntries = [
96 [1, "OrchestrationEvents", Migration0001],
97 [2, "OrchestrationCommandReceipts", Migration0002],
98 [3, "CheckpointDiffBlobs", Migration0003],
99 [4, "ProviderSessionRuntime", Migration0004],
100 [5, "Projections", Migration0005],
101 [6, "ProjectionThreadSessionRuntimeModeColumns", Migration0006],
102 [7, "ProjectionThreadMessageAttachments", Migration0007],
103 [8, "ProjectionThreadActivitySequence", Migration0008],
104 [9, "ProviderSessionRuntimeMode", Migration0009],
105 [10, "ProjectionThreadsRuntimeMode", Migration0010],
106 [11, "OrchestrationThreadCreatedRuntimeMode", Migration0011],
107 [12, "ProjectionThreadsInteractionMode", Migration0012],
108 [13, "ProjectionThreadProposedPlans", Migration0013],
109 [14, "ProjectionThreadProposedPlanImplementation", Migration0014],
110 [15, "ProjectionTurnsSourceProposedPlan", Migration0015],
111 [16, "CanonicalizeModelSelections", Migration0016],
112 [17, "ProjectionThreadsArchivedAt", Migration0017],
113 [18, "ProjectionThreadsArchivedAtIndex", Migration0018],
114 [19, "ProjectionSnapshotLookupIndexes", Migration0019],
115 [20, "AuthAccessManagement", Migration0020],
116 [21, "AuthSessionClientMetadata", Migration0021],
117 [22, "AuthSessionLastConnectedAt", Migration0022],
118 [23, "ProjectionThreadShellSummary", Migration0023],
119 [24, "BackfillProjectionThreadShellSummary", Migration0024],
120 [25, "CleanupInvalidProjectionPendingApprovals", Migration0025],
121 [26, "CanonicalizeModelSelectionOptions", Migration0026],
122 [27, "ProviderSessionRuntimeInstanceId", Migration0027],
123 [28, "ProjectionThreadSessionInstanceId", Migration0028],
124 [29, "ProjectionThreadDetailOrderingIndexes", Migration0029],
125 [30, "ProjectionThreadShellArchiveIndexes", Migration0030],
126 [31, "AuthAuthorizationScopes", Migration0031],
127 [32, "AuthPairingProofKeyThumbprint", Migration0032],
128 [33, "ProjectionThreadsSettled", Migration0033],
129 [34, "ProjectionThreadsSnoozed", Migration0034],
130 [35, "ProjectionThreadTitleRegeneration", Migration0035],
131 [36, "ProjectionThreadsPinned", Migration0036],
132 [37, "ProjectionTurnsKeysetIndex", Migration0037],
133 [38, "ProjectionThreadsPinOrderKey", Migration0038],
134 [39, "ProjectionProjectsDefaultThreadEnvMode", Migration0039],
135 [40, "ProjectionProjectFaviconPath", Migration0040],
136 [41, "AuthSessionClientConnection", Migration0041],
137 [42, "ProjectionThreadLinkedPullRequest", Migration0042],
138 [43, "ProjectionThreadsUnsettledAt", Migration0043],
139 [44, "ClearAutomaticProjectModelDefaults", Migration0044],
140 [45, "ProjectionProjectsAutoPull", Migration0045],
141 [46, "RepairAutomaticSettlementTimestamps", Migration0046],
142 [47, "ProjectionProjectIcon", Migration0047],
143 [48, "ProjectionThreadBranchPullRequest", Migration0048],
144 [49, "ProjectionThreadsActiveOrderKey", Migration0049],
145 [50, "ProjectionThreadPullRequests", Migration0050],
146 [51, "ProjectionThreadMessageContext", Migration0051],
147 [52, "ProjectionThreadTitleState", Migration0052],
148 [53, "PullRequestFilesViewed", Migration0053],
149 [54, "ProjectionThreadsAutoSettleDisabledAt", Migration0054],
150 // Released as 53 and 54 in V2 previews; reconcileV2PreviewMigration preserves their ledger.
151 // Preserve this migration's schema. Future V2 schema changes need new migrations.
152 [55, "OrchestrationV2", Migration0055],
153 [56, "RemoveRedundantProjectionIndexes", Migration0056],
154 [57, "ScheduledTaskWebhooks", Migration0057],
155 [58, "WebhookRelayDeliveries", Migration0058],
156 [59, "McpAppModelContext", Migration0059],
157 [60, "ThreadSnapshotWindowIndexes", Migration0060],
158 [61, "PullRequestReviewGuides", Migration0061],
159 [62, "ThreadFailureLookupIndex", Migration0062],
160 [63, "TranscriptArchives", Migration0063],
161 [64, "AuthSessionDelegation", Migration0064],
162 [65, "HubInbox", Migration0065],
163 [66, "HostedAuthDevices", Migration0066],
164 [67, "ArchiveCatalogAndSync", Migration0067],
165] as const;
166
167export const migrationManifest = migrationEntries.map(([id, name]) => [id, name] as const);
168
169const makeMigrationLoader = (throughId?: number) =>
170 Migrator.fromRecord(
171 Object.fromEntries(
172 migrationEntries
173 .filter(([id]) => throughId === undefined || id <= throughId)
174 .map(([id, name, migration]) => [`${id}_${name}`, migration]),
175 ),
176 );
177
178// V2's schema migrations start at 55 after the legacy schema migrations.
179const FIRST_V2_MIGRATION_ID = 55;
180
181/**
182 * Fail before migrating when the migrator would skip one of V2's own
183 * migrations. It skips every id up to the highest recorded one, whatever name
184 * was recorded, so a database that took a V2 id for another migration would
185 * start without V2's schema and fail on first use. Earlier ids keep the
186 * warning below: databases with a site-local migration there still work.
187 */
188const assertV2MigrationsCanRun = Effect.fn("assertV2MigrationsCanRun")(function* (
189 throughId?: number,
190) {
191 const sql = yield* SqlClient.SqlClient;
192 const tables = yield* sql`
193 SELECT name FROM sqlite_master WHERE type = 'table' AND name = 'effect_sql_migrations'
194 `;
195 if (tables.length === 0) return;
196 const recorded = yield* sql<{
197 readonly migration_id: number;
198 readonly name: string;
199 }>`SELECT migration_id, name FROM effect_sql_migrations`;
200 const latestRecordedId = Math.max(0, ...recorded.map((row) => row.migration_id));
201 const recordedNames = new Map(recorded.map((row) => [row.migration_id, row.name]));
202 const skipped = migrationEntries.flatMap(([id, name]) => {
203 if (
204 id < FIRST_V2_MIGRATION_ID ||
205 id > latestRecordedId ||
206 (throughId !== undefined && id > throughId)
207 )
208 return [];
209 const recordedName = recordedNames.get(id);
210 if (recordedName === name) return [];
211 return [`${id}_${name} (recorded: ${recordedName ?? "nothing"})`];
212 });
213 if (skipped.length === 0) return;
214 return yield* new Migrator.MigrationError({
215 kind: "BadState",
216 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.`,
217 });
218});
219
220/**
221 * Migrator run function - no schema dumping needed
222 * Uses the base Migrator.make without platform dependencies
223 */
224const run = Migrator.make({});
225
226export interface RunMigrationsOptions {
227 readonly toMigrationInclusive?: number | undefined;
228}
229
230/**
231 * Run all pending migrations.
232 *
233 * Creates the migrations tracking table (effect_sql_migrations) if it doesn't exist,
234 * then runs any migrations with ID greater than the latest recorded migration.
235 *
236 * Returns array of [id, name] tuples for migrations that were run.
237 *
238 * @returns Effect containing array of executed migrations
239 */
240export const runMigrations = Effect.fn("runMigrations")(function* ({
241 toMigrationInclusive,
242}: RunMigrationsOptions = {}) {
243 const includesV2 =
244 toMigrationInclusive === undefined || toMigrationInclusive >= FIRST_V2_MIGRATION_ID;
245 const previewMigrations = includesV2 ? yield* reconcileV2PreviewMigration() : [];
246 if (includesV2) {
247 yield* assertV2MigrationsCanRun(toMigrationInclusive);
248 }
249 const executedMigrations = [
250 ...previewMigrations,
251 ...(yield* run({ loader: makeMigrationLoader(toMigrationInclusive) })),
252 ];
253 const migrations = executedMigrations.map(([id, name]) => `${id}_${name}`);
254 yield* migrations.length === 0
255 ? Effect.logDebug("Database schema is current")
256 : Effect.log("Migrations ran successfully").pipe(Effect.annotateLogs({ migrations }));
257
258 // The migrator keys on migration_id: a database that recorded a different
259 // migration under a shared id (local or fork builds) keeps that id and
260 // silently skips this build's migration at it. Surface the divergence so the
261 // skipped schema change is diagnosable.
262 const sql = yield* SqlClient.SqlClient;
263 const recorded = yield* sql<{
264 readonly migration_id: number;
265 readonly name: string;
266 }>`SELECT migration_id, name FROM effect_sql_migrations`;
267 const manifestNames = new Map<number, string>(migrationEntries.map(([id, name]) => [id, name]));
268 const divergent = recorded.flatMap((row) => {
269 const expected = manifestNames.get(row.migration_id);
270 if (expected === undefined) {
271 return [`${row.migration_id}:${row.name} (unknown to this build)`];
272 }
273 return expected === row.name
274 ? []
275 : [`${row.migration_id}:${row.name} (this build: ${expected})`];
276 });
277 if (divergent.length > 0) {
278 yield* Effect.logWarning(
279 "Database migration history diverges from this build; recorded migration ids are skipped, not reconciled by name.",
280 ).pipe(Effect.annotateLogs({ divergent }));
281 }
282 return executedMigrations;
283});