Database Migrations in Paychainly: Safe Schema Changes Without Downtime

The Golden Rule: Additive Migrations Only

Never drop, rename, or change column types in a single migration. Breaking changes require two deployments: (1) add the new column, deploy; (2) remove the old column after the code is stable.

Migration File Naming Convention

YYYYMMDDHHMMSS-description.js
// Example:
20260521000001-add-customer-email-to-payment-links.js

Migration Template

'use strict';

module.exports = {
  async up(queryInterface, Sequelize) {
    await queryInterface.addColumn('payment_links', 'customerEmail', {
      type: Sequelize.STRING(255),
      allowNull: true,
      after: 'customerId', // optional: position hint for MySQL
    });
  },

  async down(queryInterface) {
    await queryInterface.removeColumn('payment_links', 'customerEmail');
  },
};

Running Migrations

cd api.paychainly.com/
npx sequelize-cli db:migrate              # apply pending
npx sequelize-cli db:migrate:status       # see applied/pending
npx sequelize-cli db:migrate:undo         # revert last one
npx sequelize-cli db:migrate:undo:all     # revert all (dangerous!)

Pre-Deploy Checklist

• All new columns are nullable or have defaults — running code may not set them.
• New unique indexes use CONCURRENTLY to avoid table locks on large tables.
• Test down() migration in staging before production.
• Never use synchronize: true in production — schema drift without migration history.

Emergency Rollback

# Revert to before the bad migration
npx sequelize-cli db:migrate:undo
# Then redeploy previous container image
docker run ... paychainly/api:previous-tag