# Migrations have rollback capability (down migrations or revert scripts)

- **Pattern:** `ab-000910` (`database-design-operations.migration-versioning.rollback-capability`)
- **Severity:** medium
- **Lifecycle:** active
- **Last modified:** 2026-04-18
- **Canonical URL:** https://auditbuffet.com/patterns/ab-000910
- **License:** CC-BY-4.0 — attribute to AuditBuffet Pattern Catalog (https://auditbuffet.com/patterns/ab-000910)

## Why it matters

Migrations without down functions (or a documented corrective-migration approach for Prisma) mean that a bad migration applied to production has no automated recovery path. The team must reconstruct the revert SQL under pressure during an incident, while the production database is in a broken state and users are affected. ISO 25010 recoverability requires a defined process for restoring normal operation; a migration system where every rollback is improvised from scratch does not meet this bar.

## Severity rationale

Medium because rollback gaps surface only during incidents, but when they do, the absence of a down migration turns a recoverable schema mistake into an extended outage.

## Remediation

For Knex, Sequelize, and TypeORM, define a `down` function in every migration that exactly reverses the `up` function. For Prisma (which intentionally omits down migrations), document the corrective migration approach in a project runbook.

```js
// Knex — both up and down defined
exports.up = async (knex) => {
  await knex.schema.createTable('posts', (t) => {
    t.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'))
    t.string('title').notNullable()
    t.timestamp('created_at').defaultTo(knex.fn.now())
  })
}
exports.down = async (knex) => {
  await knex.schema.dropTable('posts')
}
```

For Prisma, add a `docs/rollback-policy.md` that states: "To reverse a migration, create a new corrective migration (`npx prisma migrate dev --name revert_<issue>`) with SQL that undoes the previous change. For dev-only rollback: `npx prisma migrate reset` (destroys data — never run in production)."

## Detection

- **ID:** `rollback-capability`
- **Severity:** `medium`
- **What to look for:** Count every migration file and check whether the migration framework supports and has down migrations defined. In Knex: look for `exports.down = async (knex) => { ... }` in migration files — every migration should have a down function that reverses the up function. In Sequelize: similarly look for `queryInterface.down`. In TypeORM: look for the `down()` method. In raw SQL migrations using paired files: look for `*.down.sql` or `*_rollback.sql` files alongside each `*.up.sql`. In Prisma: Prisma Migrate does not support down migrations by design — check for documentation on the intended approach (create a corrective migration) or a `scripts/rollback.sh` that uses `prisma migrate reset`. In Supabase: check if SQL migrations have paired rollback comments or instructions.
- **Pass criteria:** Migration framework supports rollback and down migrations are defined for at least 80% of migrations, OR the project uses Prisma Migrate (which intentionally omits down migrations) and documents the corrective migration approach. Rollback procedure is actionable.
- **Fail criteria:** Migration framework supports down migrations but none are defined (only `exports.up` with empty or missing `exports.down`). No rollback procedure documented anywhere.
- **Skip (N/A) when:** Project uses Prisma Migrate, which does not support down migrations by design. In this case, verify that the project has documentation acknowledging this limitation and describing the corrective migration approach.
- **Detail on fail:** Specify the gap. Example: `"16 Knex migration files all have empty exports.down functions — no rollback capability defined."` or `"No down migrations defined. No rollback documentation. A bad migration would require manual database surgery to reverse."`.
- **Remediation:** Add down migrations:

  ```js
  // Knex migration — both up and down defined
  exports.up = async function(knex) {
    await knex.schema.createTable('posts', (table) => {
      table.uuid('id').primary().defaultTo(knex.raw('gen_random_uuid()'))
      table.string('title').notNullable()
      table.text('content').notNullable()
      table.timestamp('created_at').defaultTo(knex.fn.now())
    })
  }

  // Down migration reverses the up migration exactly
  exports.down = async function(knex) {
    await knex.schema.dropTable('posts')
  }
  ```

  For Prisma (no down migrations by design), document the approach:
  ```
  # Rollback Policy (Prisma)
  Prisma Migrate does not support down migrations.
  To reverse a migration:
  1. Create a new corrective migration: `npx prisma migrate dev --name revert_<issue>`
  2. The new migration SQL should undo the previous change
  3. Deploy the corrective migration through the normal pipeline
  For development rollback: `npx prisma migrate reset` (destroys data — dev only)
  ```

---

## External references

- iso-25010 reliability.recoverability

Taxons: operational-readiness

HTML version: https://auditbuffet.com/patterns/ab-000910