# Capability Spec: api-migrations **Package**: `@suaveplan/api-migrations` **Status**: ACTIVE ## ADDED Requirements ### Requirement: MigrationAdapter is the per-dialect surface The package SHALL export `MigrationAdapter` interface with `{ dialect: MigrationDialect; ensureTable(name): Promise; selectApplied(table): Promise; insertApplied(table, record): Promise; deleteApplied(table, id): Promise; execute(content, signal?): Promise }`. Each dialect (`surrealql`/`postgres`/`sqlite`) ships its own `ensureTable` DDL — no hard-coded SurrealQL in the runner. `MigrationRunner` consumes the adapter, not a generic `DatabaseLike`. #### Scenario: surrealql adapter emits SurrealQL DDL on ensureTable - **WHEN** `createSurrealqlAdapter(client).ensureTable("_migrations")` is called - **THEN** the adapter executes `DEFINE TABLE IF NOT EXISTS _migrations SCHEMAFULL; DEFINE FIELD ...` #### Scenario: postgres adapter emits standard SQL DDL on ensureTable - **WHEN** `createPostgresAdapter(pgClient).ensureTable("_migrations")` is called - **THEN** the adapter executes `CREATE TABLE IF NOT EXISTS _migrations (id text primary key, name text unique not null, applied_at timestamptz not null default now(), checksum text not null, duration_ms int not null)` **Verified by:** `src/adapters/surrealql/surrealql.test.ts::"ensureTable emits SurrealQL DDL"` AND `src/adapters/postgres/postgres.test.ts::"ensureTable emits standard SQL DDL"` --- ### Requirement: Runner applies pending migrations in numerical order `MigrationRunner.scanDirectory(dir)` SHALL return files matching `fileExtension`, parsing leading digits from filenames (`001_initial.sql` → order `1`) and sorting ascending. `run(files)` SHALL filter out already-applied (by name match against `selectApplied`), apply each pending migration via `adapter.execute(content)`, record via `adapter.insertApplied(table, {id, name, appliedAt, checksum, duration})`, and halt on first failure with `MigrationError(MIGRATION_APPLY_FAILED)`. #### Scenario: pending applied in order - **WHEN** `_migrations` contains record `001_a` and directory contains `002_b.sql`, `003_c.sql` - **THEN** `runFromDirectory(dir)` applies `002_b` then `003_c` #### Scenario: failure halts the apply loop - **WHEN** `003_c` throws and there are 4+ pending migrations - **THEN** the runner returns `[result_for_002_b, result_for_003_c_with_success: false]` and does NOT apply `004_d` **Verified by:** `src/runner/runner.test.ts::"applies pending in numerical order"` AND `src/runner/runner.test.ts::"halts on first failure"` --- ### Requirement: Checksums are actually validated When `validateChecksums: true` (default), BEFORE applying any pending migration, the runner SHALL re-read every already-applied migration's file content, compute SHA-256, and compare against the stored checksum. Mismatch SHALL raise `MigrationError(MIGRATION_CHECKSUM_DRIFT)` with `context.{name, expected, actual}` — apply halts. #### Scenario: checksum drift halts apply - **WHEN** `001_initial.sql` was applied with checksum `abc`, the file is modified post-apply, and `runFromDirectory(dir)` is called - **THEN** the call rejects with `MigrationError(MIGRATION_CHECKSUM_DRIFT)` BEFORE attempting any pending migration #### Scenario: validateChecksums: false skips drift check - **WHEN** the same scenario runs with `validateChecksums: false` - **THEN** apply proceeds without drift detection **Verified by:** `src/runner/runner.test.ts::"checksum drift raises MIGRATION_CHECKSUM_DRIFT"` AND `src/runner/runner.test.ts::"validateChecksums false skips drift check"` --- ### Requirement: AbortSignal cancels between migrations `runFromDirectory(dir, { signal })` SHALL check `signal.aborted` before each pending migration. If aborted, the in-progress migration completes (per the adapter's `execute` semantics) and the runner returns the partial results array. No further migrations are applied. The runner does NOT inject the signal into the adapter — that's per-adapter discretion. #### Scenario: pre-aborted signal returns empty - **WHEN** `runFromDirectory(dir, { signal: AbortSignal.abort() })` is awaited - **THEN** the result array is empty (no migration applied) #### Scenario: abort mid-loop returns partial result - **WHEN** abort fires after 002 succeeds but before 003 starts - **THEN** the result array contains only the 002 entry **Verified by:** `src/runner/runner.test.ts::"pre-aborted signal applies nothing"` AND `src/runner/runner.test.ts::"abort mid-loop returns partial result"` --- ### Requirement: Dry-run logs but does not apply `dryRun: true` SHALL cause `runMigration(file)` to log `[DRY RUN] Would apply: ${name}` and return `{name, success: true, duration: }` without invoking `adapter.execute` or `adapter.insertApplied`. #### Scenario: dry-run does not call adapter - **WHEN** `runFromDirectory(dir, { dryRun: true })` is invoked and 3 pending migrations exist - **THEN** `adapter.execute` is not called, `adapter.insertApplied` is not called, and the result array has 3 entries with `success: true` **Verified by:** `src/runner/runner.test.ts::"dryRun does not call adapter.execute or insertApplied"` --- ### Requirement: OpenTelemetry counter + histogram per apply The package SHALL register counter `migrations.applied_total` (attributes `dialect`, `outcome`) and histogram `migrations.apply_duration_ms` (attribute `name`). Both SHALL emit per migration regardless of success/failure. #### Scenario: counter increments on success - **WHEN** a successful apply runs against a real in-memory `MeterProvider` - **THEN** `migrations.applied_total{dialect, outcome: "success"}` has 1 observation #### Scenario: counter increments on failure - **WHEN** an apply fails - **THEN** `migrations.applied_total{dialect, outcome: "error"}` has 1 observation **Verified by:** `src/otel/otel.test.ts::"applied_total emits success"` AND `src/otel/otel.test.ts::"applied_total emits error"` AND `src/otel/otel.test.ts::"apply_duration_ms records per migration"` --- ### Requirement: Rollback removes the last tracking record only `rollback()` SHALL fetch the most recently applied migration record (sort by `appliedAt` desc, take 1), call `adapter.deleteApplied(table, lastRecord.id)`, and return `{name, success: true, duration: 0}`. It does NOT attempt to revert the schema — that's application policy. If no records exist, returns `null`. #### Scenario: rollback removes last record - **WHEN** records `001`, `002`, `003` are applied and `rollback()` is awaited - **THEN** `adapter.deleteApplied` is called with id of `003` only #### Scenario: no records → returns null - **WHEN** the migrations table is empty and `rollback()` is awaited - **THEN** the result is `null` **Verified by:** `src/runner/runner.test.ts::"rollback removes last record"` AND `src/runner/runner.test.ts::"rollback empty returns null"` --- ### Requirement: Every MIGRATION_* code is thrown from a production path `MIGRATION_CODES` SHALL define exactly 6 codes (`MIGRATION_TABLE_BOOTSTRAP_FAILED`, `MIGRATION_APPLY_FAILED`, `MIGRATION_CHECKSUM_DRIFT`, `MIGRATION_DIRECTORY_NOT_FOUND`, `MIGRATION_INVALID_FILE_NAME`, `MIGRATION_ROLLBACK_NOT_SUPPORTED`). Every code SHALL be raised by at least one production call site. **Verified by:** `src/api-migrations.spec-conformance.test.ts::"every MIGRATION_* code is thrown from a production path"` --- ### Requirement: Server-tier package shape with per-dialect subpaths The package SHALL run in Bun + Node. `package.json` SHALL declare subpath exports for the runner (`./runner/runner`), every shipped adapter (`./adapters/surrealql/surrealql`, `./adapters/postgres/postgres`, `./adapters/sqlite/sqlite`), the OTel layer, and the error codes. Every implementation `.ts` SHALL have a sibling `.md` ≥200 words. No `export *` in feature barrels. 100/100/100/100 coverage. #### Scenario: each dialect adapter is independently importable - **WHEN** a consumer imports `@suaveplan/api-migrations/adapters/postgres/postgres` - **THEN** the import succeeds without dragging in the SurrealQL adapter **Verified by:** `tests/dep-graph.test.ts::"each adapter has its own subpath"` AND `tests/dep-graph.test.ts::"subpath exports cover every feature module"` AND `tests/dep-graph.test.ts::"every impl .ts has a sibling .md"` --- ### Requirement: SurrealQL adapter records the ledger row under SurrealDB v3 `createSurrealqlAdapter.insertApplied(table, record)` SHALL produce a record-id reference in the form `:\`\`` where `` is `record.id` with every backtick doubled. The adapter SHALL NOT use the `${table}:$name` form — SurrealDB v3 parses `$param` in the record-id position as a literal ident (not a parameter substitution), so the CREATE is acknowledged but silently records no row, leaving the ledger empty and causing the runner to re-execute completed migrations. After a successful `insertApplied` invocation, a subsequent `selectApplied(table)` on the same connection SHALL return a row whose `name`,`checksum`, and`duration` match the inserted record. `createSurrealqlAdapter.deleteApplied(table, id)` SHALL produce the matching `:\`\`` reference so the round-trip from `selectApplied` → `deleteApplied` correctly targets the row that `insertApplied` wrote, regardless of whether `id` is a bare SurrealQL ident. `createSurrealqlAdapter.selectApplied(table)` SHALL normalise the SurrealDB-returned `id` field to the bare migration name — strip the `${table}:` prefix and any wrapping backticks, and for object-form `RecordId { tb, id }` return the inner `id` string when present. This guarantees `rollback()`'s call to `deleteApplied(table, last.id)` produces the same record-ref shape as the original `insertApplied`. `createSurrealqlAdapter.insertApplied` SHALL bind `record.appliedAt` as the raw `Date` value, NOT as an ISO 8601 string. SurrealDB v3's `TYPE datetime` column refuses to coerce a `string` into `datetime` and rejects the CREATE with `InternalError: Couldn't coerce value for field 'appliedAt'`. The SDK CBOR-encodes the Date binding as a SurrealDB `datetime` value. #### Scenario: insertApplied uses backticked record-id literal (v3-safe) - **WHEN** `createSurrealqlAdapter(client).insertApplied("_migrations", { id: "001", name: "001", appliedAt, checksum: "h", duration: 5 })` is called - **THEN** the issued SurrealQL is `CREATE _migrations:\`001\` SET name = $name, appliedAt = $appliedAt, checksum = $checksum, duration = $duration` (NOT `CREATE _migrations:$name SET ...`) #### Scenario: backticks in id are escaped - **WHEN** `record.id` is `weird\`id` - **THEN** the issued SurrealQL contains `CREATE _migrations:\`weird\`\`id\` SET ...` #### Scenario: ledger row is queryable after the migration body succeeds (live v3) - **WHEN** `runFromDirectory(dir)` applies a single migration against a real SurrealDB v3.x testcontainer - **THEN** `SELECT name, checksum, duration FROM _migrations` on the same connection returns exactly one row with the migration name and a 64-char-hex `checksum` #### Scenario: second run sees the ledger and skips already-applied migrations (live v3) - **WHEN** `runFromDirectory(dir)` runs a second time after the first has applied one migration - **THEN** the result array is empty AND the ledger still contains exactly one row #### Scenario: rollback removes the ledger row written by insertApplied (live v3) - **WHEN** `runner.rollback()` is awaited after one successful apply - **THEN** the rollback result is `{ name, success: true }` AND the ledger table is empty #### Scenario: selectApplied normalises returned ids to the bare migration name - **WHEN** SurrealDB returns `id: "_migrations:\`001_initial\`"` for a row - **THEN** `selectApplied("_migrations")` returns `{ id: "001_initial", ... }` **Verified by:** `src/adapters/surrealql/surrealql.test.ts::"insertApplied uses backticked record-id literal (v3-safe)"` AND `src/adapters/surrealql/surrealql.test.ts::"insertApplied escapes backticks inside the migration id"` AND `src/adapters/surrealql/surrealql.test.ts::"insertApplied binds appliedAt as a Date (not a string)"` AND `src/adapters/surrealql/surrealql.test.ts::"deleteApplied uses backticked record-id literal (v3-safe)"` AND `src/adapters/surrealql/surrealql.test.ts::"selectApplied strips backtick-quoted id form"` AND `src/adapters/surrealql/surrealql.test.ts::"selectApplied strips table prefix from string ids"` AND `tests/integration/surrealql-ledger.integration.test.ts::"ledger row is queryable after a migration body succeeds (regression: silent CREATE drop)"` AND `tests/integration/surrealql-ledger.integration.test.ts::"second run sees the ledger and skips already-applied migrations"` AND `tests/integration/surrealql-ledger.integration.test.ts::"rollback() removes the ledger row written by insertApplied (round-trip)"`