Migrations have rollback capability (down migrations or revert scripts)

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.

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.

// 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)."

• ID: database-design-operations.migration-versioning.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:

  // 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)