Add @suaveplan/api-migrations — DB-adapter-agnostic migration runner (4 of 5)

Package: @suaveplan/api-migrations (NEW, server-tier) Status: PROPOSED Type: new-package (split from upstream @suaveplan/api)

Spec delta: ./specs/api-migrations/spec.md
Companions: add-api-plugin, add-api-tenant, add-api-openapi, enterprise-api
Position in chain: independent of plugin/tenant/openapi (only depends on @suaveplan/types DatabaseLike)

Why

Upstream @suaveplan/api's database/migration-runner.ts (331 LOC) hides three problems: (1) it's pretends to be database-adapter-agnostic via the DatabaseAdapter interface but bootstraps its tracking table with SurrealQL DDL (DEFINE TABLE _migrations SCHEMAFULL ... DEFAULT time::now()), locking it to SurrealDB; (2) it declares validateChecksums: true as default but never actually compares stored checksums against current file content — silent drift; (3) it's coupled to the plugin registry's applyMigrations() soft-fail path, so production deploys can have migrations silently skipped.

Splitting it into a dedicated package makes the dialect-bootstrap pluggable, actually validates checksums, and decouples migration from plugin init so the app orchestrator runs migrations at an explicit boot phase with explicit error policy.

What changes

Net-new package at 0.1.0. ADDED:

MigrationRunner class — reads migration files from a directory, applies pending ones in order, records each with SHA-256 checksum. Constructor takes a MigrationAdapter (NOT the broader DatabaseAdapter) so the surface is narrow and testable.
MigrationAdapter interface — { dialect: MigrationDialect; ensureTable(name: string): Promise<void>; selectApplied(table: string): Promise<MigrationRecord[]>; insertApplied(table, record): Promise<void>; execute(content: string): Promise<void>; deleteApplied(table, id): Promise<void> }. Each adapter ships its own ensureTable DDL.
MigrationDialect literal union: "surrealql" | "postgres" | "sqlite" | "mysql". Shipped adapters: createSurrealqlAdapter(surrealAdapter), createPostgresAdapter(pgClient), createSqliteAdapter(sqliteDb). MySQL is declared in the union but ships as a separate package (@suaveplan/api-migrations-mysql) — included as a typed slot to prevent the union from being closed.
MigrationFile ({ name, path, order, content?, checksum?: string }), MigrationRecord ({ id, name, appliedAt, checksum, duration }), MigrationResult ({ name, success, duration, error? }).
MigrationRunnerOptions — { tableName?: string (default "_migrations"); fileExtension?: string (default ".surql" / ".sql" per dialect); validateChecksums?: boolean (default true, ACTUALLY VALIDATES); dryRun?: boolean; logger?: LoggerLike; clock?: Clock }.
Checksum enforcement: when validateChecksums: true, before applying any pending migration, the runner computes SHA-256 of every already-applied migration's file and compares against the stored checksum. Mismatch raises MigrationError(MIGRATION_CHECKSUM_DRIFT) with context.{name, expected, actual} — apply halts. Genesis behaviour change from upstream's no-op.
runFromDirectory(dir), run(files), runMigration(file), rollback(), getAppliedMigrations(), getPendingMigrations(dir), scanDirectory(dir).
MigrationError extends SuaveplanError<MigrationErrorCode> with codes MIGRATION_TABLE_BOOTSTRAP_FAILED, MIGRATION_APPLY_FAILED, MIGRATION_CHECKSUM_DRIFT, MIGRATION_DIRECTORY_NOT_FOUND, MIGRATION_INVALID_FILE_NAME, MIGRATION_ROLLBACK_NOT_SUPPORTED.
AbortSignal propagation — runFromDirectory(dir, { signal }) honors cancellation between migrations.
OTel — counter migrations.applied_total{dialect, outcome}, histogram migrations.apply_duration_ms{name}.
Subpath exports per feature module + per dialect adapter.

Out of scope

Schema diffing / migration generation. Genesis ships only the apply-runner. Tools that generate migration files from schema diffs (drizzle-kit etc.) are external.
Down migrations. SurrealDB has no automatic rollback support; Postgres/SQLite do but require careful authoring. Shipped rollback() only deletes the tracking record for the last applied migration; reverting schema is application policy.
Distributed lock for multi-instance deploys. A real distributed advisory lock (Postgres pg_advisory_lock, Redis setnx) belongs in an adapter-side wrapper. The shipped runner is single-instance-safe (transactional record insert) but not cluster-coordinated.
MySQL adapter. Declared in the dialect union but actual adapter ships separately as @suaveplan/api-migrations-mysql.

Source mapping

Capability	Upstream source	Notes
Migration runner + scanDirectory + checksum compute	`../suaveplan/packages/server/api/src/database/migration-runner.ts` L1-L331	port shape + actually validate checksums + narrow adapter interface from `DatabaseAdapter` to `MigrationAdapter`
SurrealQL DDL bootstrap	`../suaveplan/packages/server/api/src/database/migration-runner.ts` L277-L284	extract into `createSurrealqlAdapter` so it's per-dialect, not hard-coded
`MigrationFile` / `MigrationRecord` / `MigrationResult` types	`../suaveplan/packages/server/api/src/database/migration-runner.ts` L9-L38	verbatim port

Impact

Affected dependents

None today — net-new. Will be consumed by @suaveplan/api orchestrator (calls runner.runFromDirectory(...) between plugin register and init phases) and any plugin that ships .surql/.sql files alongside its source.

5.5 KiB Raw Permalink Blame History