Database Migration Procedures

Last Updated: 2025-12-13 Version: 2.0.0 Maintainer: Engineering Team

Table of Contents

• Overview
• Quick Reference
• Migration Architecture
• Creating New Migrations
• Testing Migrations
• Production Deployment
• Rollback Procedures
• Emergency Procedures
• Best Practices
• Common Patterns
• Troubleshooting
• CI/CD Integration

Overview

Petunia uses Prisma as the database ORM with PostgreSQL on Supabase. All migrations are tracked in the prisma/migrations/ directory and follow a strict deployment workflow to ensure data integrity and zero-downtime deployments.

Key Principles

1. Idempotency First: All migrations must be safe to run multiple times
2. Zero Downtime: Migrations should not lock tables or block operations
3. Forward Only: Never edit applied migrations, always create new ones
4. RLS Aware: Always consider Row Level Security policies when changing schema
5. Test Thoroughly: Every migration must pass local testing and CI gates

Technology Stack

• Database: PostgreSQL 15+ (Supabase)
• ORM: Prisma 5.x
• Connection Pooling: Supabase Connection Pooler
• Direct Connection: Required for migrations (non-pooled)

Quick Reference

# Local Development
npx prisma migrate dev --name descriptive_name    # Create and apply migration
npx prisma migrate status                         # Check migration status
npm run db:health                                 # Verify database health
pnpm lint:supabase                               # Check Supabase RLS/lint rules

# Testing
pnpm test                                        # Run all tests (includes migration tests)
pnpm test:rls                                    # Test RLS policies
npm run db:verify-authid                         # Verify critical columns

# Production Deployment (Automatic)
# Migrations run automatically in Vercel builds via:
bash scripts/deploy-migrations.sh

# Manual Production Deployment
export DIRECT_DATABASE_URL="postgresql://..."
bash scripts/deploy-migrations.sh

# Troubleshooting
npx prisma migrate resolve --applied <name>      # Mark migration as applied
npx prisma migrate resolve --rolled-back <name>  # Mark migration as rolled back
npx prisma db pull                               # Pull schema from database
npx prisma studio                                # Open database GUI

Migration Architecture

File Structure

prisma/
├── schema.prisma                    # Source of truth for schema
├── seed.ts                          # Seed data for development
├── migrations/                      # All migration files
│   ├── migration_lock.toml         # Lock file (PostgreSQL)
│   ├── 20250101000000_init/        # Initial migration
│   │   └── migration.sql
│   ├── 20251213_add_feature/       # Feature migration
│   │   └── migration.sql
│   └── ...
scripts/
├── deploy-migrations.sh             # Production migration deployment
├── check-database-health.ts         # Database health checks
├── verify-authid-column.mjs        # Critical column verification
└── migrate-encrypted-data.ts        # Data migration utilities

Naming Convention

Migrations follow this naming pattern:

YYYYMMDD[HHMMSS]_descriptive_name

Examples:

• 20251213_add_lead_idempotency - Date-based (recommended)
• 20251213123045_add_user_mfa - Timestamp-based (for same-day migrations)
• 20251212_fix_portal_client_linkage - Bugfix migration
• 20251205_apply_core_rls_policies - RLS policy migration

Naming Best Practices:

• Use lowercase with underscores
• Start with verb: add_, create_, update_, fix_, remove_
• Be specific: add_mfa_columns not just update_user
• Include context: fix_portal_client_linkage not just fix_linkage

Environment Variables

# Required for Migrations
DIRECT_DATABASE_URL="postgresql://postgres:password@db.project.supabase.co:5432/postgres"

# Runtime Connection (Pooled)
DATABASE_URL="postgresql://postgres.project:password@aws-0-us-east-1.pooler.supabase.com:6543/postgres"

# Supabase Connection
SUPABASE_DB_URL="postgresql://postgres:password@db.project.supabase.co:5432/postgres"

Important: Migrations MUST use DIRECT_DATABASE_URL (non-pooled) to avoid connection pooling issues with schema changes.

Creating New Migrations

Step 1: Modify Prisma Schema

Edit prisma/schema.prisma to add your changes:

model User {
  id        String   @id
  email     String   @unique
  name      String?
  // Add new field
  mfaEnabled Boolean @default(false)
  mfaSecret  String?

  @@index([email])
}

Step 2: Create Migration

# Create migration with descriptive name
npx prisma migrate dev --name add_mfa_columns

# This will:
# 1. Generate SQL in prisma/migrations/YYYYMMDD_add_mfa_columns/migration.sql
# 2. Apply migration to local database
# 3. Regenerate Prisma Client

Step 3: Review Generated SQL

Always review the generated SQL before committing:

cat prisma/migrations/20251213_add_mfa_columns/migration.sql

Check for:

• Correct column types
• Appropriate constraints
• Index creation
• Default values
• NOT NULL constraints (avoid if possible for existing tables)

Step 4: Make It Idempotent

Edit the migration SQL to be idempotent (safe to run multiple times):

-- ❌ NOT Idempotent (fails on second run)
ALTER TABLE "User" ADD COLUMN "mfaEnabled" BOOLEAN DEFAULT false;
ALTER TABLE "User" ADD COLUMN "mfaSecret" TEXT;

-- ✅ Idempotent (safe to run multiple times)
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "mfaEnabled" BOOLEAN DEFAULT false;
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "mfaSecret" TEXT;

-- For indexes
CREATE INDEX IF NOT EXISTS "User_email_idx" ON "User"("email");

Step 5: Add Migration Comments

Document your migration with comments:

-- Migration: Add MFA support to User table
-- Date: 2025-12-13
-- Purpose: Enable two-factor authentication for user accounts
-- Ticket: JIRA-1234

-- Add MFA enabled flag
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "mfaEnabled" BOOLEAN DEFAULT false;

-- Add MFA secret storage (encrypted in application)
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "mfaSecret" TEXT;

-- Add index for MFA-enabled users lookup
CREATE INDEX IF NOT EXISTS "User_mfaEnabled_idx" ON "User"("mfaEnabled") WHERE "mfaEnabled" = true;

Step 6: Test Locally

# Apply migration
npx prisma migrate dev

# Check status
npx prisma migrate status

# Verify health
npm run db:health

# Test application
npm run dev

# Run tests
pnpm test

Testing Migrations

Local Testing Checklist

Before committing a migration, verify:

• Migration SQL reviewed and understood
• Migration is idempotent (uses IF NOT EXISTS, IF EXISTS)
• No destructive operations without backups
• npx prisma migrate status shows all applied
• npm run db:health passes
• Application starts without errors
• Unit tests pass: pnpm test
• RLS policies intact: pnpm test:rls
• Supabase lint passes: pnpm lint:supabase

Testing Data Migrations

For migrations that modify existing data:

// Example: scripts/migrate-encrypted-data.ts pattern
#!/usr/bin/env npx tsx

import { prisma } from '../lib/database';

const isDryRun = !process.argv.includes('--execute');

async function main() {
  console.log(`Mode: ${isDryRun ? 'DRY RUN' : 'EXECUTE'}`);

  // 1. Find records that need migration
  const records = await prisma.user.findMany({
    where: { mfaSecret: { not: null } }
  });

  console.log(`Found ${records.length} records to migrate`);

  for (const record of records) {
    try {
      // 2. Perform transformation
      const newValue = transformData(record.mfaSecret);

      // 3. Verify transformation
      if (!verifyTransformation(newValue)) {
        console.error(`Failed to transform record ${record.id}`);
        continue;
      }

      // 4. Apply if not dry run
      if (!isDryRun) {
        await prisma.user.update({
          where: { id: record.id },
          data: { mfaSecret: newValue }
        });
      }

      console.log(`✓ Migrated record ${record.id}`);
    } catch (error) {
      console.error(`✗ Error migrating ${record.id}:`, error);
    }
  }
}

main().catch(console.error);

Usage:

# Test first (dry run)
npx tsx scripts/migrate-data.ts

# Execute if dry run looks good
npx tsx scripts/migrate-data.ts --execute

Testing with Fresh Database

Test migration idempotency:

# 1. Reset database and apply all migrations
npx prisma migrate reset --force

# 2. Run health checks
npm run db:health

# 3. Run application tests
pnpm test

# 4. Seed data
npx prisma db seed

# 5. Verify application works
npm run dev

Production Deployment

Automatic Deployment (Vercel)

Migrations deploy automatically during Vercel builds:

# Build sequence:
1. Install dependencies (pnpm install)
2. Deploy migrations (scripts/deploy-migrations.sh)
3. Generate Prisma Client (prisma generate)
4. Build Next.js (next build)

Vercel Environment Variables:

DIRECT_DATABASE_URL="postgresql://postgres:pwd@db.project.supabase.co:5432/postgres"
DATABASE_URL="postgresql://postgres.project:pwd@pooler.supabase.com:6543/postgres"

Manual Production Deployment

When needed (e.g., hotfixes, emergency migrations):

# 1. Set environment variable
export DIRECT_DATABASE_URL="postgresql://..."

# 2. Check current status
npx prisma migrate status

# 3. Deploy migrations
bash scripts/deploy-migrations.sh

# 4. Verify deployment
npm run db:health

# 5. Monitor application
# Check Vercel logs, error tracking, database metrics

Zero-Downtime Deployment Strategy

For breaking schema changes, use a multi-phase approach:

Phase 1: Additive Changes (Deploy 1)

-- Add new column (nullable)
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "newEmail" TEXT;

-- Add index
CREATE INDEX CONCURRENTLY IF NOT EXISTS "User_newEmail_idx" ON "User"("newEmail");

Deploy application that:

• Writes to both email and newEmail
• Reads from email (old column)

Phase 2: Data Migration (Background)

# Run data migration script
npx tsx scripts/migrate-email-column.ts --execute

Phase 3: Switch Reads (Deploy 2)

Deploy application that:

• Writes to both email and newEmail
• Reads from newEmail (new column)

Phase 4: Remove Old Column (Deploy 3)

-- Remove old column (safe now)
ALTER TABLE "User" DROP COLUMN IF EXISTS "email";

Monitoring Post-Deployment

After deployment, monitor:

1. Vercel Logs: Check for migration success messages
2. Database Health: npm run db:health
3. Error Tracking: Check error rates in monitoring
4. Application Metrics: API response times, error rates
5. Database Metrics: Connection pool, query performance

Rollback Procedures

Rollback Strategy

Golden Rule: Never rollback migrations by reverting. Always roll forward with a new migration.

Scenario 1: Migration Failed Partway

If migration fails during deployment:

# 1. Check migration status
npx prisma migrate status

# 2. See which migrations failed
# Look for "Failed" or "Pending" migrations

# 3. Mark failed migration as rolled back
npx prisma migrate resolve --rolled-back 20251213_add_feature

# 4. Fix the migration SQL or schema
# Edit prisma/schema.prisma or the migration file

# 5. Create new migration
npx prisma migrate dev --name fix_previous_migration

# 6. Deploy fix
bash scripts/deploy-migrations.sh

Scenario 2: Migration Applied but Causes Issues

Option A: Create Reverting Migration (Recommended)

# 1. Create new migration that undoes changes
npx prisma migrate dev --name revert_add_feature

# 2. Edit migration SQL to revert changes
# Example: Remove columns added in previous migration

-- Revert migration: 20251213_add_feature
-- Removes columns added in that migration

ALTER TABLE "User" DROP COLUMN IF EXISTS "mfaEnabled";
ALTER TABLE "User" DROP COLUMN IF EXISTS "mfaSecret";
DROP INDEX IF EXISTS "User_mfaEnabled_idx";

# 3. Apply revert migration
npx prisma migrate deploy

# 4. Update Prisma schema to match
# Remove fields from schema.prisma

# 5. Regenerate client
npx prisma generate

Option B: Manual Database Rollback (Emergency Only)

# 1. Connect to database
psql $DIRECT_DATABASE_URL

# 2. Manually revert changes
ALTER TABLE "User" DROP COLUMN "mfaEnabled";
ALTER TABLE "User" DROP COLUMN "mfaSecret";

# 3. Update migration tracking
DELETE FROM "_prisma_migrations"
WHERE migration_name = '20251213_add_feature';

# 4. Pull schema to sync
npx prisma db pull

# 5. Verify schema matches
diff prisma/schema.prisma prisma/schema.prisma.backup

# 6. Regenerate client
npx prisma generate

Scenario 3: Data Migration Needs Rollback

For data migrations (e.g., encrypted data format change):

# 1. Stop writes to affected tables (if possible)
# Use feature flags or deploy application without write logic

# 2. Run reverse data migration
npx tsx scripts/revert-data-migration.ts --execute

# 3. Verify data integrity
npx tsx scripts/verify-data-migration.ts

# 4. Resume writes

Emergency Procedures

Emergency Migration (Production Down)

When production is down and requires immediate schema fix:

Step 1: Create Emergency Migration

# Use emergency naming convention
npx prisma migrate dev --name emergency_fix_critical_issue

Step 2: Make It Ultra-Safe

-- Emergency Migration: Fix critical production issue
-- Date: 2025-12-13 14:30 UTC
-- Incident: INC-12345
-- Risk: LOW - Adding nullable column only

-- Add missing column causing 500 errors
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "authId" TEXT;

-- Add index for performance (CONCURRENTLY to avoid locks)
CREATE INDEX CONCURRENTLY IF NOT EXISTS "User_authId_key"
ON "User"("authId") WHERE "authId" IS NOT NULL;

Step 3: Deploy Immediately

# Deploy to production
export DIRECT_DATABASE_URL="postgresql://..."
bash scripts/deploy-migrations.sh

# Verify
npm run db:health

Step 4: Verify Application Recovery

# Check Vercel deployment logs
vercel logs --follow

# Check error tracking for reduction in errors

# Test critical user flows

Step 5: Post-Mortem

Document in migration file:

-- POST-MORTEM:
-- Root Cause: authId column missing due to skipped migration in build
-- Impact: 100% of login attempts failing with 500 error
-- Duration: 15 minutes
-- Resolution: Emergency migration to add column
-- Prevention: Enhanced CI checks for schema drift

Emergency Rollback (Dangerous)

WARNING: Only use in absolute emergencies when application is completely down.

# 1. Backup current database state
pg_dump $DIRECT_DATABASE_URL > backup_$(date +%Y%m%d_%H%M%S).sql

# 2. Connect to database
psql $DIRECT_DATABASE_URL

# 3. Check recent migrations
SELECT * FROM "_prisma_migrations"
ORDER BY finished_at DESC
LIMIT 5;

# 4. Manually revert the last migration
-- Run the inverse of the migration SQL

# 5. Update migration tracking
DELETE FROM "_prisma_migrations"
WHERE migration_name = 'MIGRATION_TO_ROLLBACK'
AND finished_at = (
  SELECT MAX(finished_at)
  FROM "_prisma_migrations"
);

# 6. Verify application recovery

# 7. Create proper forward migration to fix

Best Practices

1. Schema Design

-- ✅ GOOD: Nullable columns for existing tables
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "newField" TEXT;

-- ❌ BAD: NOT NULL on existing tables (requires default or backfill)
ALTER TABLE "User" ADD COLUMN "newField" TEXT NOT NULL;

-- ✅ GOOD: Add NOT NULL in separate migration after backfill
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "newField" TEXT;
-- Later, after data backfill:
ALTER TABLE "User" ALTER COLUMN "newField" SET NOT NULL;

2. Index Creation

-- ✅ GOOD: Use CONCURRENTLY to avoid table locks
CREATE INDEX CONCURRENTLY IF NOT EXISTS "User_email_idx"
ON "User"("email");

-- ❌ BAD: Regular index creation locks table
CREATE INDEX "User_email_idx" ON "User"("email");

-- ✅ GOOD: Partial index for better performance
CREATE INDEX CONCURRENTLY IF NOT EXISTS "User_active_email_idx"
ON "User"("email")
WHERE "deletedAt" IS NULL;

3. Data Type Changes

-- ✅ GOOD: Compatible type change (varchar to text)
ALTER TABLE "User" ALTER COLUMN "bio" TYPE TEXT;

-- ⚠️ RISKY: Narrowing type (requires validation)
-- First, add check constraint
ALTER TABLE "User" ADD CONSTRAINT "email_length_check"
CHECK (length("email") <= 255);

-- Then change type
ALTER TABLE "User" ALTER COLUMN "email" TYPE VARCHAR(255);

-- ❌ BAD: Breaking type change without migration path
ALTER TABLE "User" ALTER COLUMN "age" TYPE INTEGER
USING "age"::INTEGER; -- Fails if data not convertible

4. Foreign Key Constraints

-- ✅ GOOD: Add FK with index for performance
ALTER TABLE "Post" ADD COLUMN IF NOT EXISTS "userId" TEXT;

CREATE INDEX CONCURRENTLY IF NOT EXISTS "Post_userId_idx"
ON "Post"("userId");

ALTER TABLE "Post" ADD CONSTRAINT "Post_userId_fkey"
FOREIGN KEY ("userId") REFERENCES "User"("id")
ON DELETE CASCADE;

-- ❌ BAD: FK without index (poor query performance)
ALTER TABLE "Post" ADD CONSTRAINT "Post_userId_fkey"
FOREIGN KEY ("userId") REFERENCES "User"("id");

5. Enum Changes

-- ✅ GOOD: Add enum value (safe)
ALTER TYPE "UserRole" ADD VALUE IF NOT EXISTS 'SUPER_ADMIN';

-- ⚠️ RISKY: Remove enum value (requires data migration)
-- First, migrate existing data
UPDATE "User" SET "role" = 'ADMIN' WHERE "role" = 'OLD_ROLE';

-- Then remove from Prisma schema and create new migration
-- Postgres doesn't support removing enum values directly

-- Alternative: Create new enum, migrate, drop old
CREATE TYPE "UserRole_new" AS ENUM ('USER', 'ADMIN', 'SUPER_ADMIN');
ALTER TABLE "User" ALTER COLUMN "role" TYPE "UserRole_new"
USING "role"::text::"UserRole_new";
DROP TYPE "UserRole";
ALTER TYPE "UserRole_new" RENAME TO "UserRole";

6. RLS Policy Changes

-- ✅ GOOD: Drop before creating (idempotent)
DROP POLICY IF EXISTS "Users can view their own data" ON "User";

CREATE POLICY "Users can view their own data" ON "User"
  FOR SELECT USING (
    (SELECT auth.uid())::text = id
  );

-- ✅ GOOD: Grant permissions for policy evaluation
GRANT SELECT ON "UserCompany" TO authenticated;

7. Backfill Patterns

-- ✅ GOOD: Safe backfill with conflict handling
INSERT INTO "Company" (id, name, "createdAt", "updatedAt", "createdByUserId")
SELECT
  'company_' || u.id,
  COALESCE(u.name, 'Default Company'),
  NOW(),
  NOW(),
  u.id
FROM "User" u
LEFT JOIN "UserCompany" uc ON u.id = uc."userId"
WHERE uc.id IS NULL
ON CONFLICT (id) DO NOTHING;

-- ❌ BAD: Backfill without conflict handling (fails on retry)
INSERT INTO "Company" (id, name, "createdAt", "updatedAt", "createdByUserId")
SELECT ...
FROM "User" u
WHERE ...;

Common Patterns

Pattern 1: Idempotent Column Addition

-- Migration: Add feature columns to User
-- Date: 2025-12-13

DO $do$
BEGIN
    -- Add column if it doesn't exist
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_schema = 'public'
        AND table_name = 'User'
        AND column_name = 'featureFlag'
    ) THEN
        ALTER TABLE "User" ADD COLUMN "featureFlag" BOOLEAN DEFAULT false;
    END IF;
END $do$;

-- Alternative shorter syntax
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "featureFlag" BOOLEAN DEFAULT false;

Pattern 2: Idempotent Index Creation

-- Create index if it doesn't exist
CREATE INDEX CONCURRENTLY IF NOT EXISTS "User_email_idx"
ON "User"("email");

-- Create unique index with partial condition
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS "User_authId_key"
ON "User"("authId")
WHERE "authId" IS NOT NULL;

Pattern 3: Safe Unique Constraint Addition

-- Step 1: Add column as nullable
ALTER TABLE "User" ADD COLUMN IF NOT EXISTS "username" TEXT;

-- Step 2: Create partial unique index (allows NULLs)
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS "User_username_key"
ON "User"("username")
WHERE "username" IS NOT NULL;

-- Step 3: (Later migration) Make NOT NULL after backfill
-- ALTER TABLE "User" ALTER COLUMN "username" SET NOT NULL;

Pattern 4: Row Level Security Migration

-- Migration: Apply RLS policies to Contact table
-- Date: 2025-12-13

-- Enable RLS
ALTER TABLE "Contact" ENABLE ROW LEVEL SECURITY;

-- Drop old policies if they exist
DROP POLICY IF EXISTS "Users can view contacts for their companies" ON "Contact";

-- Create new policy
CREATE POLICY "Users can view contacts for their companies" ON "Contact"
  FOR SELECT USING (
    EXISTS (
      SELECT 1 FROM "UserCompany" uc
      WHERE uc."companyId" = "Contact"."companyId"
      AND uc."userId" = (SELECT auth.uid())::text
    )
  );

-- Grant necessary permissions
GRANT SELECT ON "Contact" TO authenticated;
GRANT SELECT ON "UserCompany" TO authenticated;

Pattern 5: Supabase Auth Helper Functions

-- Migration: Add Supabase auth helpers for vanilla Postgres
-- Ensures CI can run migrations without full Supabase runtime

DO $do$
BEGIN
  -- Create auth schema if not exists
  IF NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'auth') THEN
    CREATE SCHEMA auth;
  END IF;

  -- Create auth.uid() if not exists
  IF NOT EXISTS (
    SELECT 1 FROM pg_proc p
    JOIN pg_namespace n ON n.oid = p.pronamespace
    WHERE p.proname = 'uid' AND n.nspname = 'auth'
  ) THEN
    EXECUTE $fn$
      CREATE FUNCTION auth.uid() RETURNS uuid
      LANGUAGE sql STABLE SECURITY DEFINER
      SET search_path = ''
      AS $$
        SELECT COALESCE(
          current_setting('request.jwt.claims', true)::jsonb ->> 'sub',
          ''
        )::uuid;
      $$;
    $fn$;
  END IF;
END
$do$;

Pattern 6: Data Migration with Verification

-- Migration: Backfill orphaned companies
-- Date: 2025-12-13
-- Safe to run multiple times

-- Create companies for users without them
INSERT INTO "Company" (id, name, "createdAt", "updatedAt", "createdByUserId")
SELECT
  'company_backfill_' || u.id,
  COALESCE(u.name, SPLIT_PART(u.email, '@', 1)) || '''s Business',
  NOW(),
  NOW(),
  u.id
FROM "User" u
LEFT JOIN "UserCompany" uc ON u.id = uc."userId"
WHERE uc.id IS NULL
ON CONFLICT (id) DO NOTHING;

-- Verification query (run separately to check)
-- SELECT COUNT(*) FROM "User" u
-- LEFT JOIN "UserCompany" uc ON u.id = uc."userId"
-- WHERE uc.id IS NULL;
-- Should return 0 after migration

Troubleshooting

Issue 1: "Column does not exist" Error

Symptom:

Invalid 'prisma.user.create()' invocation:
The column 'authId' does not exist in the current database

Root Cause: Schema drift - Prisma Client doesn't match database

Solution:

# 1. Check migration status
npx prisma migrate status

# 2. Deploy pending migrations
bash scripts/deploy-migrations.sh

# 3. Regenerate Prisma Client
npx prisma generate

# 4. Verify health
npm run db:health

# 5. Restart application
npm run dev

Issue 2: Migration Already Applied

Symptom:

Migration '20251213_add_feature' has already been applied

Solution:

# Option A: Mark as applied (if safe)
npx prisma migrate resolve --applied 20251213_add_feature

# Option B: Mark as rolled back and retry
npx prisma migrate resolve --rolled-back 20251213_add_feature
npx prisma migrate deploy

Issue 3: Migration Timeout in Vercel Build

Symptom: Build fails with timeout during migration

Root Cause: Long-running migration (index creation, data backfill)

Solution:

# 1. Apply migration manually before deployment
export DIRECT_DATABASE_URL="postgresql://..."
bash scripts/deploy-migrations.sh

# 2. Optimize migration:
# - Use CREATE INDEX CONCURRENTLY
# - Break into smaller migrations
# - Move data backfills to background scripts

# 3. For large tables, create indexes in parts:
CREATE INDEX CONCURRENTLY "large_table_col_idx"
ON "LargeTable"("column");

Issue 4: Schema Drift (Local vs Production)

Symptom: Local works but production fails

Solution:

# 1. Pull production schema
DATABASE_URL="$DIRECT_DATABASE_URL" npx prisma db pull

# 2. Compare schemas
diff prisma/schema.prisma prisma/schema.prisma.backup

# 3. Identify missing migrations
npx prisma migrate status

# 4. Apply missing migrations
bash scripts/deploy-migrations.sh

# 5. Verify sync
npm run db:health

Issue 5: RLS Policy Blocking Migration

Symptom: Migration fails with permission denied

Root Cause: RLS policies blocking migration user

Solution:

# 1. Check if RLS is enabled
psql $DIRECT_DATABASE_URL -c "
SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public' AND rowsecurity = true;"

# 2. Temporarily disable RLS (CAUTION)
psql $DIRECT_DATABASE_URL -c "
ALTER TABLE \"TableName\" DISABLE ROW LEVEL SECURITY;"

# 3. Run migration
bash scripts/deploy-migrations.sh

# 4. Re-enable RLS
psql $DIRECT_DATABASE_URL -c "
ALTER TABLE \"TableName\" ENABLE ROW LEVEL SECURITY;"

# Better: Use service_role connection for migrations
# Ensure DIRECT_DATABASE_URL has sufficient privileges

Issue 6: Failed to Connect (Connection Pooling)

Symptom:

Error: P1001: Can't reach database server

Root Cause: Using pooled connection for migrations

Solution:

# Ensure DIRECT_DATABASE_URL is set (non-pooled)
export DIRECT_DATABASE_URL="postgresql://postgres:password@db.project.supabase.co:5432/postgres"

# NOT this (pooled):
# postgresql://postgres.project:password@pooler.supabase.com:6543/postgres

# Verify connection
psql $DIRECT_DATABASE_URL -c "SELECT version();"

# Deploy migrations
bash scripts/deploy-migrations.sh

CI/CD Integration

GitHub Actions Example

name: Database Migration Tests

on:
  pull_request:
    paths:
      - 'prisma/**'
      - 'scripts/deploy-migrations.sh'

jobs:
  migration-tests:
    runs-on: ubuntu-latest

    services:
      postgres:
        image: postgres:15
        env:
          POSTGRES_PASSWORD: postgres
          POSTGRES_DB: test
        options: >-
          --health-cmd pg_isready
          --health-interval 10s
          --health-timeout 5s
          --health-retries 5
        ports:
          - 5432:5432

    steps:
      - uses: actions/checkout@v3

      - name: Setup Node.js
        uses: actions/setup-node@v3
        with:
          node-version: '22'

      - name: Install dependencies
        run: pnpm install

      - name: Run migrations
        env:
          DIRECT_DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test
        run: |
          bash scripts/deploy-migrations.sh

      - name: Check migration status
        env:
          DIRECT_DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test
        run: |
          npx prisma migrate status

      - name: Verify database health
        env:
          DIRECT_DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test
        run: |
          npm run db:health

      - name: Test migration idempotency
        env:
          DIRECT_DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test
        run: |
          # Run migrations again - should succeed
          bash scripts/deploy-migrations.sh

      - name: Run tests
        env:
          DATABASE_URL: postgresql://postgres:postgres@localhost:5432/test
        run: |
          pnpm test

Vercel Build Configuration

vercel.json:

{
  "buildCommand": "bash scripts/vercel-build.sh",
  "installCommand": "pnpm install",
  "env": {
    "ENABLE_EXPERIMENTAL_COREPACK": "1"
  }
}

scripts/vercel-build.sh:

#!/bin/bash
set -e

echo "🏗️  Vercel Build Process"

# 1. Deploy migrations
if [ ! -z "$DIRECT_DATABASE_URL" ]; then
  echo "📦 Deploying migrations..."
  bash scripts/deploy-migrations.sh
else
  echo "⚠️  DIRECT_DATABASE_URL not set - skipping migrations"
fi

# 2. Generate Prisma Client
echo "🔄 Generating Prisma Client..."
pnpm exec prisma generate

# 3. Build Next.js
echo "📦 Building Next.js..."
pnpm exec next build

CI Quality Gates

All PRs must pass:

1. Supabase Lint: pnpm lint:supabase
2. Migration Deploy: bash scripts/deploy-migrations.sh
3. Database Health: npm run db:health
4. RLS Tests: pnpm test:rls
5. Idempotency: Migrations run twice successfully
6. Unit Tests: pnpm test

Related Documentation

• Database Schema - Schema overview and models
• RLS Policies - Row Level Security documentation
• Environment Variables - Configuration guide
• Supabase Integration - Supabase setup

Appendix

Migration Scripts Reference

Script	Purpose
scripts/deploy-migrations.sh	Deploy migrations to production
scripts/check-database-health.ts	Comprehensive health checks
scripts/verify-authid-column.mjs	Verify critical authId column
scripts/migrate-encrypted-data.ts	Data encryption migration
scripts/test-rls-policies.ts	Test RLS policy correctness
scripts/check-rls-health.ts	Verify RLS policy health

Common Commands Reference

# Migration Commands
npx prisma migrate dev --name NAME          # Create and apply migration
npx prisma migrate deploy                   # Deploy migrations
npx prisma migrate status                   # Check status
npx prisma migrate resolve --applied NAME   # Mark as applied
npx prisma migrate reset                    # Reset database (dev only)

# Schema Commands
npx prisma db pull                          # Pull schema from database
npx prisma db push                          # Push schema to database (dev only)
npx prisma generate                         # Generate Prisma Client
npx prisma studio                           # Open database GUI

# Validation Commands
npm run db:health                           # Database health check
npm run db:verify-authid                    # Verify authId column
pnpm lint:supabase                         # Supabase lint check
pnpm test:rls                              # Test RLS policies

# Production Commands
bash scripts/deploy-migrations.sh           # Deploy to production
psql $DIRECT_DATABASE_URL                  # Connect to database

Exit Codes

Code	Meaning
0	Success
1	Missing environment variables
2	Migration failed
3	Health check failed

---

Version History:

• 2.0.0 (2025-12-13): Comprehensive rewrite with patterns, troubleshooting, CI/CD
• 1.0.0 (2025-11-14): Initial version with basic workflow

Maintainers: Engineering Team Last Review: 2025-12-13