import { type Kysely, sql } from "kysely"; import { type Migration, type MigrationProvider, Migrator } from "kysely/migration"; import { MIGRATION_LOCK_BUSY_MESSAGE } from "../pg-migration-lock.js"; import type { Database } from "../types.js"; // Import migrations statically for bundling import * as m001 from "./001_initial.js"; import * as m002 from "./002_media_status.js"; import * as m003 from "./003_schema_registry.js"; import * as m004 from "./004_plugins.js"; import * as m005 from "./005_menus.js"; import * as m006 from "./006_taxonomy_defs.js"; import * as m007 from "./007_widgets.js"; import * as m008 from "./008_auth.js"; import * as m009 from "./009_user_disabled.js"; import * as m011 from "./011_sections.js"; import * as m012 from "./012_search.js"; import * as m013 from "./013_scheduled_publishing.js"; import * as m014 from "./014_draft_revisions.js"; import * as m015 from "./015_indexes.js"; import * as m016 from "./016_api_tokens.js"; import * as m017 from "./017_authorization_codes.js"; import * as m018 from "./018_seo.js"; import * as m019 from "./019_i18n.js"; import * as m020 from "./020_collection_url_pattern.js"; import * as m021 from "./021_remove_section_categories.js"; import * as m022 from "./022_marketplace_plugin_state.js"; import * as m023 from "./023_plugin_metadata.js"; import * as m024 from "./024_media_placeholders.js"; import * as m025 from "./025_oauth_clients.js"; import * as m026 from "./026_cron_tasks.js"; import * as m027 from "./027_comments.js"; import * as m028 from "./028_drop_author_url.js"; import * as m029 from "./029_redirects.js"; import * as m030 from "./030_widen_scheduled_index.js"; import * as m031 from "./031_bylines.js"; import * as m032 from "./032_rate_limits.js"; import * as m033 from "./033_optimize_content_indexes.js"; import * as m034 from "./034_published_at_index.js"; import * as m035 from "./035_bounded_404_log.js"; import * as m036 from "./036_i18n_menus_and_taxonomies.js"; import * as m037 from "./037_credential_algorithm.js"; import * as m038 from "./038_registry_plugin_state.js"; import * as m039 from "./039_fix_fts5_triggers.js"; import * as m040 from "./040_byline_i18n.js"; import * as m041 from "./041_content_locale_list_index.js"; import * as m042 from "./042_byline_fields.js"; import * as m043 from "./043_content_references.js"; import * as m044 from "./044_comment_reactions.js"; import * as m045 from "./045_taxonomy_parent_group.js"; import * as m046 from "./046_media_usage_index.js"; import * as m047 from "./047_restore_taxonomy_parent_index.js"; import * as m048 from "./048_restore_content_taxonomies_term_index.js"; import * as m049 from "./049_taxonomies_name_locale_index.js"; import * as m050 from "./050_media_usage_index_status.js"; import * as m051 from "./051_content_taxonomies_denorm.js"; import * as m052 from "./052_media_usage_read_index.js"; import * as m053 from "./053_plugin_mcp_tools.js"; import * as m054 from "./054_media_upload_attempts.js"; import * as m055 from "./055_content_translation_group_locale_index.js"; import * as m056 from "./056_taxonomy_term_sort_order.js"; import * as m057 from "./057_collection_hidden.js"; import * as m058 from "./058_collection_sort_order.js"; import * as m059 from "./059_revision_prune_queue.js"; import * as m060 from "./060_collection_admin_config.js"; import * as m061 from "./061_media_usage_cleanup.js"; import * as m062 from "./062_media_usage_cleanup_fence.js"; import * as m063 from "./063_media_usage_incremental_work.js"; import * as m064 from "./064_fts_plain_text.js"; import * as m065 from "./065_media_usage_collection_deletion.js"; import * as m066 from "./066_media_usage_reconciliation.js"; import * as m067 from "./067_indexed_content_fields.js"; import * as m068 from "./068_content_taxonomy_entry_groups.js"; import * as m069 from "./069_collection_title_date_fields.js"; import * as m070 from "./070_collection_routable.js"; import * as m071 from "./071_restore_content_bylines_table.js"; import * as m072 from "./072_authz.js"; import * as m073 from "./073_billing_usage.js"; import * as m074 from "./074_collection_storage.js"; import * as m075 from "./075_api_token_cors.js"; const MIGRATIONS: Readonly> = Object.freeze({ "001_initial": m001, "002_media_status": m002, "003_schema_registry": m003, "004_plugins": m004, "005_menus": m005, "006_taxonomy_defs": m006, "007_widgets": m007, "008_auth": m008, "009_user_disabled": m009, "011_sections": m011, "012_search": m012, "013_scheduled_publishing": m013, "014_draft_revisions": m014, "015_indexes": m015, "016_api_tokens": m016, "017_authorization_codes": m017, "018_seo": m018, "019_i18n": m019, "020_collection_url_pattern": m020, "021_remove_section_categories": m021, "022_marketplace_plugin_state": m022, "023_plugin_metadata": m023, "024_media_placeholders": m024, "025_oauth_clients": m025, "026_cron_tasks": m026, "027_comments": m027, "028_drop_author_url": m028, "029_redirects": m029, "030_widen_scheduled_index": m030, "031_bylines": m031, "032_rate_limits": m032, "033_optimize_content_indexes": m033, "034_published_at_index": m034, "035_bounded_404_log": m035, "036_i18n_menus_and_taxonomies": m036, "037_credential_algorithm": m037, "038_registry_plugin_state": m038, "039_fix_fts5_triggers": m039, "040_byline_i18n": m040, "041_content_locale_list_index": m041, "042_byline_fields": m042, "043_content_references": m043, "044_comment_reactions": m044, "045_taxonomy_parent_group": m045, "046_media_usage_index": m046, "047_restore_taxonomy_parent_index": m047, "048_restore_content_taxonomies_term_index": m048, "049_taxonomies_name_locale_index": m049, "050_media_usage_index_status": m050, "051_content_taxonomies_denorm": m051, "052_media_usage_read_index": m052, "053_plugin_mcp_tools": m053, "054_media_upload_attempts": m054, "055_content_translation_group_locale_index": m055, "056_taxonomy_term_sort_order": m056, "057_collection_hidden": m057, "058_collection_sort_order": m058, "059_revision_prune_queue": m059, "060_collection_admin_config": m060, "061_media_usage_cleanup": m061, "062_media_usage_cleanup_fence": m062, "063_media_usage_incremental_work": m063, "064_fts_plain_text": m064, "065_media_usage_collection_deletion": m065, "066_media_usage_reconciliation": m066, "067_indexed_content_fields": m067, "068_content_taxonomy_entry_groups": m068, "069_collection_title_date_fields": m069, "070_collection_routable": m070, "071_restore_content_bylines_table": m071, "072_authz": m072, "073_billing_usage": m073, "074_collection_storage": m074, "075_api_token_cors": m075, }); /** Ordered names from the statically registered migration set. */ export const MIGRATION_NAMES: readonly string[] = Object.freeze(Object.keys(MIGRATIONS)); /** Total number of registered migrations. Exported for use in tests. */ export const MIGRATION_COUNT = MIGRATION_NAMES.length; /** * Migration provider that uses statically imported migrations. * This approach works well with bundlers and avoids filesystem access. */ class StaticMigrationProvider implements MigrationProvider { async getMigrations(): Promise> { return MIGRATIONS; } } export interface MigrationStatus { applied: string[]; pending: string[]; } export interface ExactMigrationStatus { knownApplied: string[]; pending: string[]; unknownApplied: string[]; } /** * Thrown when another instance held the migration lock for the whole wait * window. This is NOT a migration failure — the holder may simply be slow * (e.g. many pending migrations over a remote Postgres connection) — so * callers with failure-backoff logic (`getDatabase` in emdash-runtime.ts) * exempt it: the next request waits again instead of backing off, and init * recovers as soon as the holder finishes. */ export class ConcurrentMigrationTimeoutError extends Error { constructor(waitMs: number) { super( `Timed out waiting for another instance's migrations: the migration lock was still held after ${waitMs}ms. ` + "It may still be applying migrations, or its migration may be failing repeatedly.", ); this.name = "ConcurrentMigrationTimeoutError"; } } /** Custom migration table name */ const MIGRATION_TABLE = "_emdash_migrations"; const MIGRATION_LOCK_TABLE = "_emdash_migrations_lock"; export interface MigrationOptions { migrationTableSchema?: string; /** * Override how long to wait for a concurrent migrator to finish before * giving up. Defaults to MIGRATION_RACE_WAIT_MS; tests shorten it so the * give-up path doesn't take 10 seconds per case. */ raceWaitMs?: number; } function createMigrator(db: Kysely, options?: MigrationOptions): Migrator { return new Migrator({ db, provider: new StaticMigrationProvider(), migrationTableName: MIGRATION_TABLE, migrationLockTableName: MIGRATION_LOCK_TABLE, migrationTableSchema: options?.migrationTableSchema, }); } /** * Get migration status */ export async function getMigrationStatus( db: Kysely, options?: MigrationOptions, ): Promise { const migrator = createMigrator(db, options); const migrations = await migrator.getMigrations(); const applied: string[] = []; const pending: string[] = []; for (const migration of migrations) { if (migration.executedAt) { applied.push(migration.name); } else { pending.push(migration.name); } } return { applied, pending }; } /** Pattern for escaping special regex characters. Matches the shared helper in `database/repositories/content.ts`. */ const REGEX_ESCAPE_PATTERN = /[.*+?^${}()|[\]\\]/g; /** Escape special regex characters so a string can be embedded literally in `new RegExp()`. */ function escapeRegExp(value: string): string { return value.replace(REGEX_ESCAPE_PATTERN, "\\$&"); } /** * Pattern used to detect the concurrent-migration race. The Kysely * `SqliteAdapter.acquireMigrationLock` is a no-op (inherited by `kysely-d1` * and our `EmDashD1Dialect`), so two isolates running migrations against the * same database can both attempt `INSERT INTO _emdash_migrations` for the * same migration name. The losing insert fails with a UNIQUE constraint * error, which is benign: the other isolate is applying the same schema. * * We match on the table name (not the full error text) because different * SQLite drivers phrase the message differently * (`UNIQUE constraint failed: _emdash_migrations.name` for better-sqlite3, * `D1_ERROR: UNIQUE constraint failed: _emdash_migrations.name: SQLITE_CONSTRAINT` * for D1, etc.). The pattern is built from `MIGRATION_TABLE` so a rename * cannot silently disable race detection. */ const MIGRATION_RACE_PATTERN = new RegExp( `UNIQUE constraint failed: ${escapeRegExp(MIGRATION_TABLE)}\\.name`, "i", ); /** * How long to wait for a concurrent migrator to finish before giving up. * Exported because the db init lock's reclaim deadline must comfortably * exceed it (see DB_INIT_DEADLINE_MS in emdash-runtime.ts) — a healthy * init can legitimately block this long inside waitForConcurrentMigrator. */ export const MIGRATION_RACE_WAIT_MS = 10_000; /** Polling interval while waiting for a concurrent migrator. */ const MIGRATION_RACE_POLL_MS = 100; /** * Pattern used to detect "table does not exist" errors across the dialects * EmDash supports. The phrasing differs by driver: * * - better-sqlite3: `no such table: _emdash_migrations` * - D1: `D1_ERROR: no such table: _emdash_migrations: SQLITE_ERROR` * - PostgreSQL: `relation "_emdash_migrations" does not exist` * (also occasionally `table "_emdash_migrations" does not exist`) * * We deliberately match on the migration table name (rather than using the * generic `isMissingTableError` helper) so an unexpected missing-table error * naming a different table — implausible today since * `getAppliedMigrationCount` only references `MIGRATION_TABLE`, but cheap * insurance against future edits — is not silently swallowed. The pattern is * built from `MIGRATION_TABLE` so a rename cannot drift. */ const MIGRATION_TABLE_MISSING_PATTERN = new RegExp( `(?:no such table:\\s*(?:[a-z][a-z0-9_]*\\.)?${escapeRegExp(MIGRATION_TABLE)}\\b` + `|(?:relation|table)\\s+"?(?:[a-z][a-z0-9_]*\\.)?${escapeRegExp(MIGRATION_TABLE)}"?\\s+does(?:n't| not) exist\\b)`, "i", ); /** * Read the count of applied migrations. * * Returns `null` only when the migration table does not exist yet (which is * the normal state on a fresh database before the first migration runs). * Any other error is rethrown so callers — particularly * `waitForConcurrentMigrator` — don't silently mask connection failures, * permission errors, or other unexpected driver problems behind a 10s wait * and a bogus "we're done" verdict. */ async function getAppliedMigrationCount(db: Kysely): Promise { try { const result = await sql<{ count: number }>` SELECT COUNT(*) as count FROM ${sql.ref(MIGRATION_TABLE)} `.execute(db); return Number(result.rows[0]?.count ?? 0); } catch (error) { if (MIGRATION_TABLE_MISSING_PATTERN.test(deepErrorMessage(error))) { return null; } throw error; } } /** * Wait for a concurrent migrator to finish applying all migrations. * * Resolves to `true` once the migration table contains at least * `MIGRATION_COUNT` rows (i.e. every migration this build knows about has * been recorded), `false` if the deadline elapses first. We use `>=` rather * than `===` so that an old isolate observing a database that has already * been migrated by a newer build still treats the wait as settled instead * of timing out. */ async function waitForConcurrentMigrator( db: Kysely, waitMs: number = MIGRATION_RACE_WAIT_MS, ): Promise { const deadline = Date.now() + waitMs; while (Date.now() < deadline) { const count = await getAppliedMigrationCount(db); if (count !== null && count >= MIGRATION_COUNT) { return true; } await new Promise((resolve) => setTimeout(resolve, MIGRATION_RACE_POLL_MS)); } const finalCount = await getAppliedMigrationCount(db); return finalCount !== null && finalCount >= MIGRATION_COUNT; } /** Extract the deepest error message available from a thrown value. */ function deepErrorMessage(error: unknown): string { if (error instanceof Error) { const own = error.message ?? ""; if (error.cause) { const causeMsg = deepErrorMessage(error.cause); return own ? `${own}: ${causeMsg}` : causeMsg; } return own; } if (typeof error === "string") return error; try { return JSON.stringify(error); } catch { return String(error); } } /** * Read exact migration status without invoking Kysely's migration * introspection. Registered names retain execution order; unknown database * records are sorted so reports remain stable across dialects. */ export async function getExactMigrationStatus( db: Kysely, options?: MigrationOptions, ): Promise { const table = options?.migrationTableSchema ? sql`${sql.ref(options.migrationTableSchema)}.${sql.ref(MIGRATION_TABLE)}` : sql.ref(MIGRATION_TABLE); let rows: readonly { name: string }[]; try { const result = await sql<{ name: string }>`SELECT name FROM ${table}`.execute(db); rows = result.rows; } catch (error) { if (MIGRATION_TABLE_MISSING_PATTERN.test(deepErrorMessage(error))) { return { knownApplied: [], pending: [...MIGRATION_NAMES], unknownApplied: [], }; } throw error; } const appliedNames = new Set(rows.map((row) => row.name)); const knownApplied = MIGRATION_NAMES.filter((name) => appliedNames.has(name)); const pending = MIGRATION_NAMES.filter((name) => !appliedNames.has(name)); const knownNames = new Set(MIGRATION_NAMES); const unknownApplied = [...appliedNames].filter((name) => !knownNames.has(name)).toSorted(); return { knownApplied, pending, unknownApplied }; } /** * Run all pending migrations. * * Includes a fast-path: if the migration table already exists and contains * at least MIGRATION_COUNT rows, all migrations this build knows about have * been applied and we can skip the Kysely Migrator entirely. This avoids * the expensive `pragma_table_info` introspection that Kysely runs for * every table in the database (twice!) just to check if the migration * tables exist. On D1 with ~57 tables, that's ~116 queries saved per init. * * Concurrent-migration safety: the Kysely Migrator's `acquireMigrationLock` * is a no-op for SQLite (and therefore D1), so two callers running this * concurrently against the same database will both try to apply pending * migrations. SQLite serializes the writes, but the loser still surfaces a * `UNIQUE constraint failed: _emdash_migrations.name` error. We treat that * specific error as benign: another caller is already applying the same * schema. We wait for the concurrent migrator to finish, then return * success. This matches the user-observable expectation that running * migrations twice in a row is a no-op. */ export async function runMigrations( db: Kysely, options?: MigrationOptions, ): Promise<{ applied: string[] }> { // Fast path: check if all migrations are already applied. // A single cheap query vs the Migrator's full schema introspection. // We use `>=` rather than `===` so a database with extra rows from a // newer build (e.g. mid-deploy old isolate, or downgrade) still skips // the migrator instead of falling through to the race-recovery path // unnecessarily. if (!options?.migrationTableSchema) { const initialCount = await getAppliedMigrationCount(db); if (initialCount !== null && initialCount >= MIGRATION_COUNT) { return { applied: [] }; } } const migrator = createMigrator(db, options); const { error, results } = await migrator.migrateToLatest(); const applied = results?.filter((r) => r.status === "Success").map((r) => r.migrationName) ?? []; if (error) { // Walk error.cause to get the underlying driver message — Kysely // often wraps with an empty top-level message. const msg = deepErrorMessage(error); const failedMigration = results?.find((r) => r.status === "Error"); // Concurrent-migration race: another caller is applying (or just // applied) the same migration. SQLite/D1 surface it as the // bookkeeping UNIQUE violation (their migration lock is a no-op); // Postgres surfaces it as the fail-fast advisory try-lock reporting // busy (see pg-migration-lock.ts). Either way: wait for the // concurrent migrator to finish, then verify the schema is fully // migrated and treat as success. const lockBusy = msg.includes(MIGRATION_LOCK_BUSY_MESSAGE); if (MIGRATION_RACE_PATTERN.test(msg) || lockBusy) { const settled = await waitForConcurrentMigrator(db, options?.raceWaitMs); if (settled) { return { applied }; } if (lockBusy) { // The lock holder didn't finish within the wait window — // either it's slow or its migration is failing. Surface a // distinct error type instead of the raw sentinel message so // callers can tell "still in progress" from "failed". throw new ConcurrentMigrationTimeoutError(options?.raceWaitMs ?? MIGRATION_RACE_WAIT_MS); } } const failedSuffix = failedMigration ? ` (migration: ${failedMigration.migrationName})` : ""; throw new Error(`Migration failed: ${msg || "unknown error"}${failedSuffix}`); } return { applied }; } /** * Rollback the last migration */ export async function rollbackMigration( db: Kysely, options?: MigrationOptions, ): Promise<{ rolledBack: string | null }> { const migrator = createMigrator(db, options); const { error, results } = await migrator.migrateDown(); const rolledBack = results?.[0]?.status === "Success" ? results[0].migrationName : null; if (error) { const msg = error instanceof Error ? error.message : JSON.stringify(error); throw new Error(`Rollback failed: ${msg}`); } return { rolledBack }; }