Database Migrations

Overview

Tresa Contafy uses Sequelize CLI to manage database migrations. Migrations allow you to version control your database schema and apply changes incrementally.

Prerequisites

PostgreSQL database created and accessible
DATABASE_URL configured in .env
Dependencies installed with pnpm install

Migration Commands

Run Migrations

Apply all pending migrations to your database:

pnpm db:migrate

This executes:

sequelize-cli db:migrate

Always run migrations before starting the application for the first time or after pulling schema changes

Check Migration Status

View which migrations have been applied:

pnpm db:migrate:status

Output example:

up 20260103041617-create-users.cjs
up 20260103041623-create-profiles.cjs
up 20260103041628-create-subscriptions.cjs
down 20260304000003-add-sat-download-fiel-to-profiles.cjs

Undo Last Migration

Rollback the most recent migration:

pnpm db:migrate:undo

Use with caution in production! This can cause data loss if the migration modified data.

Undo All Migrations

Rollback all migrations (resets database):

pnpm db:migrate:undo:all

This will drop all tables! Only use in development or for complete database resets.

Creating New Migrations

Generate Migration File

Create a new migration with a descriptive name:

pnpm db:migrate:generate add-column-to-users

This runs:

node scripts/generate-migration.cjs add-column-to-users

Generated file (in src/database/migrations/):

20260307123456-add-column-to-users.cjs

Migration File Structure

Each migration has up (apply) and down (rollback) methods:

module.exports = {
  async up(queryInterface, Sequelize) {
    // Apply migration changes
    await queryInterface.addColumn('users', 'new_column', {
      type: Sequelize.STRING,
      allowNull: true,
    });
  },

  async down(queryInterface, Sequelize) {
    // Rollback migration changes
    await queryInterface.removeColumn('users', 'new_column');
  },
};

Migration Configuration

Migrations are configured through .sequelizerc in the project root:

module.exports = {
  config: path.resolve('src', 'database', 'config.cjs'),
  'models-path': path.resolve('src', 'database', 'models'),
  'seeders-path': path.resolve('src', 'database', 'seeders'),
  'migrations-path': path.resolve('src', 'database', 'migrations'),
};

Database Configuration

Database connection settings are in src/database/config.cjs:

module.exports = {
  development: {
    url: process.env.DATABASE_URL,
    dialect: 'postgres',
    dialectOptions: {
      ssl: {
        require: true,
        rejectUnauthorized: false,
      },
    },
  },
  production: {
    url: process.env.DATABASE_URL,
    dialect: 'postgres',
    dialectOptions: {
      ssl: {
        require: true,
        rejectUnauthorized: false,
      },
    },
  },
};

Deployment Workflow

Pull Latest Code

Get the latest migrations from your repository:

git pull origin main

Install Dependencies

Ensure all packages are up to date:

pnpm install

Check Migration Status

Verify which migrations need to be applied:

pnpm db:migrate:status

Run Migrations

Apply pending migrations:

pnpm db:migrate

In production, migrations should be run as part of your CI/CD pipeline before deploying new code

Verify Success

Confirm all migrations completed:

pnpm db:migrate:status

All migrations should show up status.

Existing Migrations

Tresa Contafy includes the following migrations:

Core Tables (2026-01-03)

Initial database schema:

20260103041617-create-users.cjs - User accounts
20260103041623-create-profiles.cjs - RFC profiles
20260103041628-create-subscriptions.cjs - Subscription management
20260103041631-create-payment-events.cjs - Payment event log
20260103041636-create-invoices.cjs - Invoice records
20260103041640-create-expenses.cjs - Expense records (later renamed)

Authentication (2026-01-03 - 2026-01-17)

20260103044233-add-email-verification-token.cjs - Email verification
20260117000000-add-password-reset-token.cjs - Password reset functionality

Payment Complements (2026-01-16)

20260116090000-create-payment-complements.cjs - Payment complement documents
20260116090010-create-payment-complement-items.cjs - Related payment items

Features (2026-01 - 2026-02)

20260107174900-add-user-profile-fields.cjs - Extended user profile
20260114120000-create-discount-codes.cjs - Discount code system
20260115020000-add-profile-cascade-delete.cjs - Cascade delete constraints
20260118202111-add-trial-used-to-users.cjs - Trial tracking
20260120000000-add-tour-fields-to-users.cjs - Onboarding tour
20260122000000-add-profile-freeze-fields.cjs - Period freezing
20260218000000-add-firebase-uid-to-users.cjs - Google authentication
20260216000000-add-rfc-unique-to-profiles.cjs - Unique RFC constraint
20260223000000-add-trial-days-to-discount-codes.cjs - Trial period in discounts

SAT Integration (2026-01-21 - 2026-02-06)

20260121000000-create-sat-product-services.cjs - SAT product/service catalog
20260121000001-create-sat-search-logs.cjs - SAT search logging
20260206140000-profile-regimenes-fiscales-array.cjs - Fiscal regime array
20260206150000-create-sat-regimenes-fiscales.cjs - Fiscal regime catalog

Recent Updates (2026-02 - 2026-03)

20260205000000-rename-expenses-to-accrued-expenses.cjs - Table rename
20260205000001-add-invoice-tax-columns.cjs - Tax breakdown columns
20260205000002-create-periods.cjs - Period management
20260205000003-create-manual-incomes.cjs - Manual income entries
20260205000004-add-payment-date-accrued-expenses.cjs - Payment tracking
20260206000000-create-payrolls.cjs - Payroll management
20260206120000-create-plugins.cjs - Plugin system
20260206130000-create-subscription-plugins.cjs - Plugin subscriptions
20260304000001-add-branding-to-users.cjs - Custom branding
20260304000002-create-public-report-tokens.cjs - Public report sharing
20260304000003-add-sat-download-fiel-to-profiles.cjs - SAT FIEL credentials

Database Seeding

Seed data for development and testing:

Run Seeders

pnpm db:seed

Undo Seeders

pnpm db:seed:undo

Available Seeders

20260121000000-seed-sat-product-services.cjs - SAT product/service catalog
20260206120000-seed-plugins.cjs - Plugin definitions
20260218120000-seed-test-user-profile.cjs - Test user and profile

Seeders are intended for development only. Do not run in production unless explicitly needed.

Best Practices

Always Test Migrations Locally

Run migrations on a local or staging database before production:

# Local testing
pnpm db:migrate:status
pnpm db:migrate

Write Reversible Migrations

Always implement the down method to allow rollbacks:

// Good: Reversible
async up(queryInterface, Sequelize) {
  await queryInterface.addColumn('users', 'phone');
},
async down(queryInterface, Sequelize) {
  await queryInterface.removeColumn('users', 'phone');
}

Backup Before Production Migrations

Always backup your production database before running migrations:

# PostgreSQL backup
pg_dump $DATABASE_URL > backup-$(date +%Y%m%d).sql

Use Transactions

Wrap migrations in transactions for atomicity:

async up(queryInterface, Sequelize) {
  const transaction = await queryInterface.sequelize.transaction();
  try {
    await queryInterface.addColumn('users', 'phone', 
      { type: Sequelize.STRING }, { transaction });
    await transaction.commit();
  } catch (error) {
    await transaction.rollback();
    throw error;
  }
}

Troubleshooting

Migration fails with 'relation already exists'

Problem: Migration tries to create a table/column that already existsSolution:

# Check what's actually in the database
psql $DATABASE_URL -c "\dt"

# Mark migration as complete without running it
# (Advanced - use with caution)
pnpm db:migrate:status

DATABASE_URL not defined error

Problem: Sequelize can’t find database connectionSolution:

Verify .env file exists with DATABASE_URL
Check you’re in the correct directory
Ensure dotenv is loaded in config file

SSL connection errors

Problem: Database requires SSL but connection failsSolution: Update src/database/config.cjs:

dialectOptions: {
  ssl: {
    require: true,
    rejectUnauthorized: false
  }
}

Setup

Production

Overview

Prerequisites

Migration Commands

Run Migrations

Check Migration Status

Undo Last Migration

Undo All Migrations

Creating New Migrations

Generate Migration File

Migration File Structure

Migration Configuration

Database Configuration

Deployment Workflow

Existing Migrations

Database Seeding

Run Seeders

Undo Seeders

Available Seeders

Best Practices

Troubleshooting

Next Steps

Production Deployment

Database Setup

Build docs developers (and LLMs) love

Setup

Production

​Overview

​Prerequisites

​Migration Commands

​Run Migrations

​Check Migration Status

​Undo Last Migration

​Undo All Migrations

​Creating New Migrations

​Generate Migration File

​Migration File Structure

​Migration Configuration

​Database Configuration

​Deployment Workflow

​Existing Migrations

​Database Seeding

​Run Seeders

​Undo Seeders

​Available Seeders

​Best Practices

​Troubleshooting

​Next Steps

Production Deployment

Database Setup

Build docs developers (and LLMs) love

Overview

Prerequisites

Migration Commands

Run Migrations

Check Migration Status

Undo Last Migration

Undo All Migrations

Creating New Migrations

Generate Migration File

Migration File Structure

Migration Configuration

Database Configuration

Deployment Workflow

Existing Migrations

Database Seeding

Run Seeders

Undo Seeders

Available Seeders

Best Practices

Troubleshooting

Next Steps