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
```bash
npm run dev
```
This command will:
1. ✅ Run all pending migrations automatically
2. 🚀 Start the development server with hot reload

### Running Migrations Only
```bash
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
```typescript
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`:

```typescript
// 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
```bash
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:

```typescript
// 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
```typescript
await queryInterface.addColumn('table_name', 'new_column', {
  type: DataTypes.STRING(100),
  allowNull: false,
  defaultValue: 'default_value',
  comment: 'Column description'
});
```

### Adding Foreign Key
```typescript
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
```typescript
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
```typescript
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
```typescript
await queryInterface.changeColumn('table_name', 'column_name', {
  type: DataTypes.STRING(200), // Changed from 100
  allowNull: true // Changed from false
});
```

### Dropping Column
```typescript
await queryInterface.removeColumn('table_name', 'old_column');
```

### Raw SQL Queries
```typescript
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
```bash
# 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`