Skip to main content

Database Migrations Runbook

How to manage Prisma migrations for the CRM database.

Overview​

The CRM uses Prisma ORM with PostgreSQL. Migrations are stored in libs/prisma/crm-client/prisma/migrations/.

Local Development​

Create a New Migration​

cd libs/prisma/crm-client
npx prisma migrate dev --name <migration_name>

Example:

npx prisma migrate dev --name add_campaign_ab_testing

Apply Migrations​

npx prisma migrate dev

Generate Client​

npx prisma generate

Reset Database (Destructive)​

npx prisma migrate reset

Production Deployment​

Pre-Deployment Checklist​

  1. Migration tested locally
  2. Migration reviewed in PR
  3. Backup verified
  4. Rollback plan documented

Deploy Migration​

Migrations run as a Kubernetes Job before the main deployment:

apiVersion: batch/v1
kind: Job
metadata:
  name: crm-migrate
  namespace: crm
spec:
  template:
    spec:
      containers:
        - name: migrate
          image: harbor.digiwedge.dev/crm/backend:latest
          command: ["npx", "prisma", "migrate", "deploy"]
          env:
            - name: CRM_DATABASE_URL
              valueFrom:
                secretKeyRef:
                  name: crm-secrets
                  key: DATABASE_URL
      restartPolicy: Never
  backoffLimit: 3

Monitor Migration​

# Watch job status
kubectl get job/crm-migrate -n crm -w

# Check logs
kubectl logs job/crm-migrate -n crm

# Verify migrations applied
kubectl exec -it deploy/crm-backend -n crm -- \
  npx prisma migrate status

Rollback Procedures​

Option 1: Revert Migration (Preferred)​

Create a new migration that reverses the changes:

# Create rollback migration
npx prisma migrate dev --name revert_add_campaign_ab_testing

Option 2: Database Restore​

If migration is catastrophic, restore from backup:

# List available backups
aws s3 ls s3://digiwedge-backups/crm/

# Restore from backup
pg_restore -h HOST -U USER -d crm backup.dump

Option 3: Manual SQL​

For simple column additions:

-- Revert added column
ALTER TABLE "Campaign" DROP COLUMN IF EXISTS "abTestConfig";

Common Issues​

Migration Conflict​

When multiple developers create migrations:

# Check for conflicts
npx prisma migrate status

# Reset migration history (dev only)
npx prisma migrate reset

Pending Migration in Production​

# Check status
npx prisma migrate status

# Deploy pending
npx prisma migrate deploy

Schema Drift​

When production differs from migration history:

# Compare schemas
npx prisma migrate diff \
  --from-schema-datamodel prisma/schema.prisma \
  --to-schema-datasource prisma/schema.prisma

Best Practices​

  1. Small migrations - One change per migration
  2. Reversible changes - Always plan rollback
  3. No data loss - Use ALTER TABLE not DROP TABLE
  4. Test locally - Full cycle before PR
  5. Backup first - Especially for large tables

Seed Data​

Local Seeding​

npx prisma db seed

Seed Script Location​

libs/prisma/crm-client/prisma/seed.ts

System Segments​

Consider seeding common segments:

  • "All Customers"
  • "Active Members"
  • "High Engagement"
  • "At Risk (Churn)"