Database Migration Workflow | Bootspring Docs

The Database Migration workflow provides a structured, safe approach to schema changes and data migrations, ensuring data integrity, minimal downtime, and the ability to rollback if issues arise.

Overview#

Property	Value
Phases	4
Tier	Free
Typical Duration	5-6 days
Best For	Schema changes, data transformations, database refactoring

Outcomes#

A successful database migration results in:

Migration completed safely with rollback readiness
Migration rehearsed in staging environment
Rollback procedure tested and documented
Data integrity checks passing

Phases#

Phase 1: Schema Design (1 day)#

Agents: database-expert

Design the new schema changes with careful consideration for backward compatibility and data integrity.

Tasks:

Document current schema state
Design target schema changes
Identify breaking vs non-breaking changes
Plan for backward compatibility
Define data transformation requirements
Create migration scripts

Migration Types:

┌────────────────────────────────────────────────────────────┐
│                    Migration Types                          │
├─────────────────┬──────────────────────────────────────────┤
│ Non-Breaking    │ Add column (nullable), add table,        │
│                 │ add index, expand column type            │
├─────────────────┼──────────────────────────────────────────┤
│ Breaking        │ Remove column, rename column,            │
│                 │ change column type, drop table           │
├─────────────────┼──────────────────────────────────────────┤
│ Data Migration  │ Transform existing data, backfill        │
│                 │ values, merge/split tables               │
└─────────────────┴──────────────────────────────────────────┘

Schema Change Example:

// Before
model User {
  id        String   @id @default(cuid())
  name      String
  email     String   @unique
  createdAt DateTime @default(now())
}

// After - Adding new fields (non-breaking)
model User {
  id          String    @id @default(cuid())
  name        String
  email       String    @unique
  avatarUrl   String?   // New nullable field
  lastLoginAt DateTime? // New nullable field
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt // New field with default
}

Phase 2: Migration Plan (1 day)#

Agents: database-expert, devops-expert

Create a detailed migration plan including timing, rollback procedures, and communication strategy.

Tasks:

Define migration window and timing
Create step-by-step execution plan
Document rollback procedures
Plan data validation checks
Coordinate with stakeholders
Set up monitoring for migration

Migration Plan Template:

## Migration: [Name]
**Date:** YYYY-MM-DD
**Window:** HH:MM - HH:MM (timezone)
**Risk Level:** Low/Medium/High

### Pre-Migration Checklist
- [ ] Backup completed and verified
- [ ] Staging migration successful
- [ ] Rollback tested
- [ ] Team notified
- [ ] Monitoring dashboards open

### Execution Steps
1. Enable maintenance mode (if needed)
2. Take final backup
3. Run migration: `npx prisma migrate deploy`
4. Verify data integrity
5. Run application smoke tests
6. Disable maintenance mode

### Rollback Procedure
1. Enable maintenance mode
2. Restore from backup
3. Revert application code
4. Verify rollback success
5. Disable maintenance mode

### Success Criteria
- [ ] All migrations applied
- [ ] Data integrity checks pass
- [ ] Application functional
- [ ] No error spikes in monitoring

Phase 3: Implementation (2-3 days)#

Agents: database-expert, backend-expert

Execute the migration with proper staging validation and production deployment.

Tasks:

Test migration in development
Run migration in staging environment
Validate staging data integrity
Execute production migration
Run data integrity checks
Update application code as needed

Prisma Migration Commands:

# Create migration (development)
npx prisma migrate dev --name add_user_avatar

# Check migration status
npx prisma migrate status

# Deploy to production
npx prisma migrate deploy

# Reset database (development only!)
npx prisma migrate reset

Zero-Downtime Pattern:

Phase 1: Deploy code that handles both schemas
         (reads new column, falls back to old)
            │
            ▼
Phase 2: Run migration to add new column
            │
            ▼
Phase 3: Backfill data for new column
            │
            ▼
Phase 4: Deploy code that requires new column
            │
            ▼
Phase 5: Run migration to remove old column

Backfill Script Example:

// scripts/backfill-avatar-urls.ts
import { prisma } from '@/lib/prisma';

async function backfillAvatarUrls() {
  const batchSize = 1000;
  let processed = 0;
  let hasMore = true;

  while (hasMore) {
    const users = await prisma.user.findMany({
      where: { avatarUrl: null },
      take: batchSize,
      select: { id: true, email: true }
    });

    if (users.length === 0) {
      hasMore = false;
      continue;
    }

    await prisma.$transaction(
      users.map(user =>
        prisma.user.update({
          where: { id: user.id },
          data: {
            avatarUrl: generateGravatarUrl(user.email)
          }
        })
      )
    );

    processed += users.length;
    console.log(`Processed ${processed} users`);
  }

  console.log('Backfill complete');
}

backfillAvatarUrls();

Phase 4: Verification (1 day)#

Agents: testing-expert

Verify the migration was successful and all data integrity requirements are met.

Tasks:

Run data integrity checks
Verify row counts match expectations
Test application functionality
Validate API responses
Check for data inconsistencies
Document migration results

Data Integrity Checks:

-- Verify row counts
SELECT 'users' as table_name, COUNT(*) as count FROM users
UNION ALL
SELECT 'orders', COUNT(*) FROM orders;

-- Check for orphaned records
SELECT o.id FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

-- Verify not null constraints
SELECT COUNT(*) as null_count
FROM users
WHERE avatar_url IS NULL AND created_at < '2024-01-01';

-- Check data consistency
SELECT id, email FROM users
WHERE email NOT LIKE '%@%.%';

Starting the Workflow#

# Start the workflow
bootspring workflow start database-migration

# Check current status
bootspring workflow status

# Advance to next phase
bootspring workflow next

# Mark a checkpoint complete
bootspring workflow checkpoint "Migration rehearsed"

Completion Signals#

Track progress with these checkpoints:

Migration rehearsed - Successfully tested in staging
Rollback tested - Rollback procedure verified
Data integrity checks passing - All validation queries pass

Best Practices#

Always Have a Backup#

# PostgreSQL backup
pg_dump -Fc database_name > backup_$(date +%Y%m%d_%H%M%S).dump

# Restore from backup
pg_restore -d database_name backup.dump

Use Transactions#

// Wrap related changes in a transaction
await prisma.$transaction(async (tx) => {
  // Create new record
  const newRecord = await tx.newTable.create({
    data: transformedData
  });

  // Update reference
  await tx.oldTable.update({
    where: { id: oldRecord.id },
    data: { newTableId: newRecord.id }
  });
});

Batch Large Updates#

// Process large datasets in batches
const BATCH_SIZE = 1000;
let skip = 0;
let hasMore = true;

while (hasMore) {
  const records = await prisma.record.findMany({
    take: BATCH_SIZE,
    skip,
  });

  if (records.length < BATCH_SIZE) {
    hasMore = false;
  }

  await processBatch(records);
  skip += BATCH_SIZE;
}

Monitor During Migration#

// Log migration progress
console.log(`[${new Date().toISOString()}] Starting migration`);
console.log(`Records to process: ${totalCount}`);

// Track timing
const startTime = Date.now();
// ... migration code ...
const duration = Date.now() - startTime;
console.log(`Migration completed in ${duration}ms`);

Tips for Success#

Never skip staging - Always test migrations in a production-like environment
Plan for failure - Have a tested rollback procedure ready
Communicate early - Notify stakeholders about planned downtime
Batch large operations - Avoid long-running transactions
Monitor closely - Watch for errors and performance issues during migration