---
name: database-migration
description: Safely migrate database schema changes across Prisma and Supabase dual-database system. Use when modifying tables, adding columns, changing relationships, or syncing schema between databases. Ensures data integrity and zero-downtime migrations.
---

# Database Migration Skill

## Overview

Safely execute database schema changes across Mawidi's dual-database architecture (Prisma + Supabase). This skill ensures data integrity, handles rollbacks, and maintains sync between both databases.

**Databases:**

- **Prisma** - Primary ORM, schema source of truth
- **Supabase** - Secondary database for real-time features

**Key Principle:** Schema changes must be applied to BOTH databases consistently.

## When to Use

Use this skill when:

- Adding new tables or columns
- Modifying existing schema
- Changing relationships between tables
- Running data migrations
- Syncing schema between Prisma and Supabase
- Rolling back failed migrations

## Pre-Migration Checklist

Before ANY migration:

- [ ] Back up production data
- [ ] Test migration on staging/development first
- [ ] Review migration SQL generated by Prisma
- [ ] Prepare rollback plan
- [ ] Schedule during low-traffic period
- [ ] Notify team of planned migration

## Migration Workflow

### Step 1: Update Prisma Schema

```prisma
// prisma/schema.prisma

model Booking {
  id          String   @id @default(uuid())
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  // NEW FIELD
  cancelledAt DateTime?
  cancelReason String?

  // Existing fields...
  userId      String
  serviceId   String
  date        DateTime
  status      BookingStatus @default(PENDING)

  // Relations
  user        User    @relation(fields: [userId], references: [id])
  service     Service @relation(fields: [serviceId], references: [id])
}

enum BookingStatus {
  PENDING
  CONFIRMED
  CANCELLED   // NEW VALUE
  COMPLETED
}
```

### Step 2: Generate Migration

```bash
# Generate migration file without applying
npx prisma migrate dev --name add_booking_cancellation --create-only

# Review the generated SQL
cat prisma/migrations/[timestamp]_add_booking_cancellation/migration.sql
```

### Step 3: Review Generated SQL

```sql
-- prisma/migrations/[timestamp]_add_booking_cancellation/migration.sql

-- AlterTable
ALTER TABLE "Booking" ADD COLUMN "cancelledAt" TIMESTAMP(3);
ALTER TABLE "Booking" ADD COLUMN "cancelReason" TEXT;

-- AlterEnum
ALTER TYPE "BookingStatus" ADD VALUE 'CANCELLED';
```

### Step 4: Apply to Development

```bash
# Apply migration to development database
npx prisma migrate dev
```

### Step 5: Sync to Supabase

```bash
# Run sync script to apply changes to Supabase
node scripts/sync-all-prisma-to-supabase.mjs

# Verify schema integrity
npm run test -- supabase-schema-integrity
```

### Step 6: Deploy to Production

```bash
# Apply migration to production
npx prisma migrate deploy

# Sync production Supabase
NODE_ENV=production node scripts/sync-all-prisma-to-supabase.mjs
```

## Common Migration Patterns

### Adding a New Column

```prisma
// Safe: Add nullable column first
model User {
  // ... existing fields
  phoneVerified Boolean?  // Nullable first
}
```

```bash
# Step 1: Add nullable column
npx prisma migrate dev --name add_phone_verified

# Step 2: Backfill data
npx prisma db execute --file scripts/backfill-phone-verified.sql

# Step 3: Make non-nullable (separate migration)
# Update schema: phoneVerified Boolean @default(false)
npx prisma migrate dev --name make_phone_verified_required
```

### Renaming a Column

```sql
-- Manual migration required (Prisma can't rename)
-- prisma/migrations/[timestamp]_rename_column/migration.sql

ALTER TABLE "User" RENAME COLUMN "oldName" TO "newName";
```

```prisma
// Update schema to match
model User {
  newName String  // was: oldName
}
```

### Adding a New Table

```prisma
model AuditLog {
  id        String   @id @default(uuid())
  createdAt DateTime @default(now())

  userId    String
  action    String
  resource  String
  details   Json?

  user      User @relation(fields: [userId], references: [id])

  @@index([userId])
  @@index([createdAt])
}
```

### Adding Relationships

```prisma
// One-to-Many
model User {
  id       String    @id @default(uuid())
  bookings Booking[]  // Add relation
}

model Booking {
  id     String @id @default(uuid())
  userId String
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)
}

// Many-to-Many
model User {
  id    String @id @default(uuid())
  roles Role[] @relation("UserRoles")
}

model Role {
  id    String @id @default(uuid())
  users User[] @relation("UserRoles")
}
```

### Adding Indexes

```prisma
model Booking {
  id        String   @id @default(uuid())
  userId    String
  date      DateTime
  status    BookingStatus

  @@index([userId])           // Single column
  @@index([date, status])     // Composite
  @@index([userId, date])     // For common queries
}
```

## Data Migration Scripts

### Backfill Pattern

```typescript
// scripts/backfill-example.ts

import { prisma } from "@/lib/db";

async function backfill() {
  const batchSize = 100;
  let processed = 0;

  while (true) {
    const records = await prisma.booking.findMany({
      where: { newField: null },
      take: batchSize,
    });

    if (records.length === 0) break;

    for (const record of records) {
      await prisma.booking.update({
        where: { id: record.id },
        data: { newField: computeValue(record) },
      });
    }

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

  console.log("Backfill complete");
}

backfill().catch(console.error);
```

### Safe Data Transform

```typescript
// Use transactions for data integrity
import { prisma } from "@/lib/db";

async function migrateData() {
  await prisma.$transaction(async (tx) => {
    // Step 1: Create new records
    const oldData = await tx.oldTable.findMany();

    for (const item of oldData) {
      await tx.newTable.create({
        data: transformItem(item),
      });
    }

    // Step 2: Update references
    await tx.relatedTable.updateMany({
      where: { oldTableId: { not: null } },
      data: {
        /* update references */
      },
    });

    // Transaction commits only if all operations succeed
  });
}
```

## Dual Database Sync

### Using saveToBothDatabases

```typescript
import { saveToBothDatabases } from "@/lib/dual-database";

// Automatically syncs to both Prisma and Supabase
await saveToBothDatabases("users", {
  id: userId,
  email: "user@example.com",
  name: "John Doe",
});
```

### Manual Supabase Sync

```typescript
import { supabase } from "@/lib/supabase";
import { prisma } from "@/lib/db";

// After Prisma operation, sync to Supabase
const user = await prisma.user.create({
  data: { email, name },
});

await supabase.from("users").insert({
  id: user.id,
  email: user.email,
  name: user.name,
  created_at: user.createdAt.toISOString(),
});
```

### Schema Validation Script

```bash
# Validate schema consistency between databases
npm run check:schema

# Full sync if needed
node scripts/sync-all-prisma-to-supabase.mjs

# Test integrity
npm run test -- supabase-schema-integrity
```

## Rollback Strategies

### Prisma Rollback

```bash
# Rollback last migration
npx prisma migrate reset --skip-seed

# Or manually revert specific migration
npx prisma db execute --file rollback.sql
```

### Manual Rollback Script

```sql
-- rollback.sql
-- Reverse the migration changes

-- Remove added column
ALTER TABLE "Booking" DROP COLUMN "cancelledAt";
ALTER TABLE "Booking" DROP COLUMN "cancelReason";

-- Note: Removing enum values requires recreation
-- Be careful with enum rollbacks
```

### Blue-Green Deployment Pattern

```
1. Deploy new code that handles BOTH old and new schema
2. Apply migration
3. Verify application works
4. Remove old schema handling code
5. Deploy final version
```

## Pre-Deploy Validation (MANDATORY)

```bash
# From CLAUDE.md - run before EVERY deployment

# 1. Check schema consistency
npm run check:schema

# 2. Sync databases
node scripts/sync-all-prisma-to-supabase.mjs

# 3. Verify integrity
npm run test -- supabase-schema-integrity
```

## Common Pitfalls

### ❌ Destructive Changes Without Backup

```bash
# BAD: Dropping column directly
ALTER TABLE "User" DROP COLUMN "important_data";

# GOOD: Backup first
CREATE TABLE "User_backup" AS SELECT * FROM "User";
ALTER TABLE "User" DROP COLUMN "important_data";
```

### ❌ Non-Nullable Column on Existing Data

```prisma
// BAD: Will fail if table has existing rows
newField String  // Required, no default

// GOOD: Add with default or nullable
newField String @default("unknown")
// OR
newField String?  // Nullable, backfill later
```

### ❌ Forgetting Supabase Sync

```bash
# BAD: Only running Prisma migration
npx prisma migrate deploy

# GOOD: Sync both databases
npx prisma migrate deploy
node scripts/sync-all-prisma-to-supabase.mjs
npm run test -- supabase-schema-integrity
```

### ❌ Large Migrations During Peak Hours

```
# BAD: Running during business hours
# Migrations can lock tables

# GOOD: Schedule for low-traffic periods
# Notify team, prepare rollback
```

## Migration Checklist Template

```markdown
## Migration: [Name]

### Pre-Migration

- [ ] Schema changes reviewed
- [ ] Generated SQL reviewed
- [ ] Tested on development
- [ ] Rollback plan documented
- [ ] Data backup created
- [ ] Team notified

### Execution

- [ ] Applied to staging
- [ ] Staging tests passed
- [ ] Applied to production Prisma
- [ ] Synced to production Supabase
- [ ] Schema integrity verified

### Post-Migration

- [ ] Application tested
- [ ] Monitoring checked
- [ ] Rollback plan on standby
- [ ] Documentation updated
```

## Quick Reference

### Common Commands

```bash
# Generate migration
npx prisma migrate dev --name description

# Apply to production
npx prisma migrate deploy

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

# Generate client
npx prisma generate

# Open Prisma Studio
npx prisma studio

# Sync to Supabase
node scripts/sync-all-prisma-to-supabase.mjs
```

### File Locations

| File                   | Purpose              |
| ---------------------- | -------------------- |
| `prisma/schema.prisma` | Schema definition    |
| `prisma/migrations/`   | Migration history    |
| `lib/db.ts`            | Prisma client        |
| `lib/supabase.ts`      | Supabase client      |
| `lib/dual-database.ts` | Dual-write utilities |

## See Also

- [dual-database-save.ts](../dual-database-save.ts) - Dual database operations
- [api-development](../api-development/SKILL.md) - API patterns using Prisma
- `lib/database-sync/` - Database sync architecture
