Re_Backend/MIGRATION_WORKFLOW.md

Migration Workflow Guide

Overview

This project uses a TypeScript-based migration system for database schema changes. All migrations are automatically executed when you start the development server.

🚀 Quick Start

Running Development Server with Migrations

npm run dev

This command will:

1. ✅ Run all pending migrations automatically
2. 🚀 Start the development server with hot reload

Running Migrations Only

npm run migrate

Use this when you only want to apply migrations without starting the server.

📝 Creating New Migrations

Step 1: Create Migration File

Create a new TypeScript file in src/migrations/ with the naming pattern:

YYYYMMDD-descriptive-name.ts

Example: 20251105-add-new-field.ts

Step 2: Migration Template

import { QueryInterface, DataTypes } from 'sequelize';

/**
 * Migration: Brief description
 * Purpose: Detailed explanation
 * Date: YYYY-MM-DD
 */

export async function up(queryInterface: QueryInterface): Promise<void> {
  // Add your forward migration logic here
  await queryInterface.addColumn('table_name', 'column_name', {
    type: DataTypes.STRING,
    allowNull: true,
  });
  
  console.log('✅ Migration description completed');
}

export async function down(queryInterface: QueryInterface): Promise<void> {
  // Add your rollback logic here
  await queryInterface.removeColumn('table_name', 'column_name');
  
  console.log('✅ Migration rolled back');
}

Step 3: Register Migration

Add your new migration to src/scripts/migrate.ts:

// 1. Import at the top
import * as m15 from '../migrations/20251105-add-new-field';

// 2. Execute in the run() function
await (m15 as any).up(sequelize.getQueryInterface());

Step 4: Test

npm run migrate

📋 Current Migrations

The following migrations are configured and will run in order:

1. 2025103001-create-workflow-requests - Core workflow requests table
2. 2025103002-create-approval-levels - Approval hierarchy structure
3. 2025103003-create-participants - Workflow participants
4. 2025103004-create-documents - Document attachments
5. 20251031_01_create_subscriptions - User subscriptions
6. 20251031_02_create_activities - Activity tracking
7. 20251031_03_create_work_notes - Work notes/comments
8. 20251031_04_create_work_note_attachments - Note attachments
9. 20251104-add-tat-alert-fields - TAT alert fields
10. 20251104-create-tat-alerts - TAT alerts table
11. 20251104-create-kpi-views - KPI database views
12. 20251104-create-holidays - Holiday calendar
13. 20251104-create-admin-config - Admin configurations
14. 20251105-add-skip-fields-to-approval-levels - Skip approver functionality

🔄 Migration Safety Features

Idempotent Migrations

All migrations should be idempotent (safe to run multiple times). Use checks like:

// Check if column exists before adding
const tableDescription = await queryInterface.describeTable('table_name');
if (!tableDescription.column_name) {
  await queryInterface.addColumn(/* ... */);
}

// Check if table exists before creating
const tables = await queryInterface.showAllTables();
if (!tables.includes('table_name')) {
  await queryInterface.createTable(/* ... */);
}

Error Handling

Migrations automatically:

• ✅ Stop on first error
• ❌ Exit with error code 1 on failure
• 📝 Log detailed error messages
• 🔄 Prevent server startup if migrations fail

🛠️ Common Migration Operations

Adding a Column

await queryInterface.addColumn('table_name', 'new_column', {
  type: DataTypes.STRING(100),
  allowNull: false,
  defaultValue: 'default_value',
  comment: 'Column description'
});

Adding Foreign Key

await queryInterface.addColumn('table_name', 'foreign_key_id', {
  type: DataTypes.UUID,
  allowNull: true,
  references: {
    model: 'referenced_table',
    key: 'id'
  },
  onUpdate: 'CASCADE',
  onDelete: 'SET NULL'
});

Creating Index

await queryInterface.addIndex('table_name', ['column_name'], {
  name: 'idx_table_column',
  unique: false
});

// Partial index with WHERE clause
await queryInterface.addIndex('table_name', ['status'], {
  name: 'idx_table_active',
  where: {
    is_active: true
  }
});

Creating Table

await queryInterface.createTable('new_table', {
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    primaryKey: true
  },
  name: {
    type: DataTypes.STRING(100),
    allowNull: false
  },
  created_at: {
    type: DataTypes.DATE,
    allowNull: false,
    defaultValue: DataTypes.NOW
  },
  updated_at: {
    type: DataTypes.DATE,
    allowNull: false,
    defaultValue: DataTypes.NOW
  }
});

Modifying Column

await queryInterface.changeColumn('table_name', 'column_name', {
  type: DataTypes.STRING(200), // Changed from 100
  allowNull: true // Changed from false
});

Dropping Column

await queryInterface.removeColumn('table_name', 'old_column');

Raw SQL Queries

await queryInterface.sequelize.query(`
  CREATE OR REPLACE VIEW view_name AS
  SELECT * FROM table_name WHERE condition
`);

📊 Database Structure Reference

Always refer to backend_structure.txt for the authoritative database structure including:

• All tables and their columns
• Data types and constraints
• Relationships and foreign keys
• Enum values
• Indexes

🚨 Troubleshooting

Migration Fails with "Column Already Exists"

• The migration might have partially run
• Add idempotent checks or manually rollback the failed migration

Server Won't Start After Migration

• Check the migration error in console
• Fix the migration file
• Run npm run migrate to retry

Need to Rollback a Migration

# Manual rollback (requires implementing down() function)
ts-node src/scripts/rollback.ts

🎯 Best Practices

1. Always test migrations on development database first
2. Write rollback logic in down() function
3. Use descriptive names for migrations
4. Add comments explaining the purpose
5. Keep migrations small - one logical change per file
6. Never modify existing migration files after they run in production
7. Use transactions for complex multi-step migrations
8. Backup production before running new migrations

📝 Migration Checklist

Before running migrations in production:

• Tested on local development database
• Verified rollback functionality works
• Checked for data loss scenarios
• Reviewed index impact on performance
• Confirmed migration is idempotent
• Updated backend_structure.txt documentation
• Added migration to version control
• Registered in migrate.ts

🔗 Related Files

• Migration Scripts: src/migrations/
• Migration Runner: src/scripts/migrate.ts
• Database Config: src/config/database.ts
• Database Structure: backend_structure.txt
• Package Scripts: package.json

💡 Example: Recent Migration

The latest migration (20251105-add-skip-fields-to-approval-levels) demonstrates best practices:

• ✅ Descriptive naming
• ✅ Clear documentation
• ✅ Multiple related columns added together
• ✅ Foreign key relationships
• ✅ Indexed for query performance
• ✅ Includes rollback logic
• ✅ Helpful console messages

---

Last Updated: November 5, 2025
Migration Count: 14 migrations
Auto-Run: Enabled for npm run dev