Database Migrations - ThriveSend B2B2G

Database Migrations - ThriveSend B2B2G

Last Updated: January 26, 2026 Status: Production Developer Guide: Database Layer

---

Overview

This guide covers database migrations for ThriveSend B2B2G using Prisma Migrate. Learn how to create, apply, and manage schema changes while maintaining POPIA compliance and data integrity.

Migration Strategy:

• Schema-first approach with Prisma
• Version-controlled SQL migrations
• Zero-downtime production deployments
• POPIA-compliant data transformations

---

Migration Workflow

Development Workflow

graph LR
    A[Edit Schema] --> B[Create Migration]
    B --> C[Review SQL]
    C --> D[Apply Migration]
    D --> E[Test Changes]
    E --> F[Commit to Git]
    F --> G[Deploy to Production]

Key Commands

Command	Purpose	Environment
npx prisma migrate dev	Create and apply migration	Development
npx prisma migrate deploy	Apply pending migrations	Production
npx prisma migrate status	Check migration status	All
npx prisma migrate resolve	Mark migration as applied	Production (recovery)
npx prisma db push	Sync schema without migration	Development (prototyping)

---

Creating Migrations

Step 1: Update Prisma Schema

Edit prisma/schema.prisma to add or modify models:

// Example: Adding a new field to Client model
model Client {
  id             String @id @default(cuid())
  organizationId String
  name           String

  // NEW FIELD: Contact email (required for POPIA compliance)
  contactEmail   String // Added field

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

Step 2: Create Migration

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

# What happens:
# 1. Prisma analyzes schema changes
# 2. Generates SQL migration file
# 3. Applies migration to development database
# 4. Regenerates Prisma Client

Migration Output:

Environment variables loaded from .env.local
Prisma schema loaded from prisma/schema.prisma
Datasource "db": PostgreSQL database "thrivesend_dev", schema "public" at "localhost:5432"

Applying migration `20251002193302_make_contact_email_required`

The following migration(s) have been created and applied from new schema changes:

migrations/
  └─ 20251002193302_make_contact_email_required/
    └─ migration.sql

Your database is now in sync with your schema.

✔ Generated Prisma Client

Step 3: Review Generated SQL

Check the generated migration file:

-- migrations/20251002193302_make_contact_email_required/migration.sql

/*
  Warnings:
  - Made the column `contactEmail` on table `clients` required.
    This step will fail if there are existing NULL values in that column.
*/

-- AlterTable
ALTER TABLE "public"."clients" ALTER COLUMN "contactEmail" SET NOT NULL;

Step 4: Test the Migration

// Test in development
import { prisma } from '@/lib/db'

// This should now work (contactEmail is required)
const client = await prisma.client.create({
  data: {
    organizationId: 'org_123',
    name: 'Test Client',
    contactEmail: 'contact@example.com', // Now required
  }
})

// This should fail (missing contactEmail)
const invalidClient = await prisma.client.create({
  data: {
    organizationId: 'org_123',
    name: 'Invalid Client',
    // Missing contactEmail - will throw error
  }
})
// Error: Field 'contactEmail' is required

---

Real-World Migration Examples

Example 1: Adding Invitation System (POPIA Compliant)

This is a real migration from the ThriveSend B2B2G codebase:

-- migrations/20251002193302_make_contact_email_required/migration.sql

-- Create enums for invitation system
CREATE TYPE "public"."InvitationRole" AS ENUM (
  'OWNER', 'ADMIN', 'MANAGER', 'CONTENT_CREATOR',
  'REVIEWER', 'APPROVER', 'PUBLISHER', 'ANALYST',
  'VIEWER', 'GOVERNMENT_LIAISON', 'COMPLIANCE_OFFICER'
);

CREATE TYPE "public"."InvitationStatus" AS ENUM (
  'PENDING', 'SENT', 'OPENED', 'ACCEPTED',
  'EXPIRED', 'REVOKED', 'BOUNCED', 'FAILED'
);

CREATE TYPE "public"."EmailStatus" AS ENUM (
  'PENDING', 'SENT', 'DELIVERED', 'OPENED',
  'CLICKED', 'BOUNCED', 'COMPLAINED', 'FAILED', 'PROCESSED'
);

-- Create invitations table
CREATE TABLE "public"."invitations" (
    "id" TEXT NOT NULL,
    "token" TEXT NOT NULL,
    "email" TEXT NOT NULL,
    "organizationId" TEXT NOT NULL,
    "role" "public"."InvitationRole" NOT NULL,
    "securityClearance" "public"."SecurityClearanceLevel" NOT NULL,
    "clientAccess" TEXT[],
    "invitedBy" TEXT NOT NULL,
    "status" "public"."InvitationStatus" NOT NULL DEFAULT 'PENDING',
    "emailSentAt" TIMESTAMP(3),
    "emailOpenedAt" TIMESTAMP(3),
    "acceptedAt" TIMESTAMP(3),
    "expiredAt" TIMESTAMP(3) NOT NULL,
    "popiaConsentGiven" BOOLEAN NOT NULL DEFAULT false,
    "consentMetadata" JSONB,
    "auditMetadata" JSONB NOT NULL DEFAULT '{}',
    "customMessage" TEXT,
    "ipAddress" TEXT,
    "userAgent" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "invitations_pkey" PRIMARY KEY ("id")
);

-- Create POPIA consent records table
CREATE TABLE "public"."popia_consent_records" (
    "id" TEXT NOT NULL,
    "invitationId" TEXT,
    "userId" TEXT,
    "email" TEXT NOT NULL,
    "organizationId" TEXT NOT NULL,
    "consentType" TEXT NOT NULL DEFAULT 'INVITATION_ACCEPTANCE',
    "dataProcessingConsent" BOOLEAN NOT NULL,
    "marketingConsent" BOOLEAN NOT NULL DEFAULT false,
    "analyticsConsent" BOOLEAN NOT NULL DEFAULT false,
    "consentVersion" TEXT NOT NULL DEFAULT '1.0',
    "consentLanguage" TEXT NOT NULL DEFAULT 'en',
    "ipAddress" TEXT NOT NULL,
    "userAgent" TEXT NOT NULL,
    "consentTimestamp" TIMESTAMP(3) NOT NULL,
    "withdrawnAt" TIMESTAMP(3),
    "withdrawalReason" TEXT,
    "auditMetadata" JSONB NOT NULL DEFAULT '{}',
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "popia_consent_records_pkey" PRIMARY KEY ("id")
);

-- Create indexes for performance
CREATE UNIQUE INDEX "invitations_token_key"
  ON "public"."invitations"("token");
CREATE INDEX "invitations_email_idx"
  ON "public"."invitations"("email");
CREATE INDEX "invitations_organizationId_idx"
  ON "public"."invitations"("organizationId");
CREATE INDEX "popia_consent_records_email_idx"
  ON "public"."popia_consent_records"("email");

-- Add foreign key constraints
ALTER TABLE "public"."invitations"
  ADD CONSTRAINT "invitations_organizationId_fkey"
  FOREIGN KEY ("organizationId")
  REFERENCES "public"."organizations"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "public"."popia_consent_records"
  ADD CONSTRAINT "popia_consent_records_organizationId_fkey"
  FOREIGN KEY ("organizationId")
  REFERENCES "public"."organizations"("id")
  ON DELETE CASCADE ON UPDATE CASCADE;

What This Migration Does:

• Creates invitation system with POPIA compliance
• Adds consent tracking for data protection
• Includes audit metadata for all actions
• Enforces SA data residency (dataResidency field)
• Creates performance indexes

Example 2: Adding Political Organization Support

-- migrations/20251002203425_add_political_organization_support/migration.sql

-- Create enum for political levels
CREATE TYPE "public"."PoliticalLevel" AS ENUM (
  'NATIONAL', 'PROVINCIAL', 'LOCAL', 'INDEPENDENT'
);

-- Add political organization fields to clients table
ALTER TABLE "public"."clients"
  ADD COLUMN "politicalLevel" "public"."PoliticalLevel",
  ADD COLUMN "iecRegistrationNumber" TEXT,
  ADD COLUMN "electoralConstituency" TEXT,
  ADD COLUMN "partyAffiliation" TEXT;

-- Create index for IEC lookups
CREATE INDEX "clients_iecRegistrationNumber_idx"
  ON "public"."clients"("iecRegistrationNumber");

---

Applying Migrations

Development Environment

# Apply all pending migrations
npx prisma migrate dev

# Apply migrations and skip prompts
npx prisma migrate dev --skip-generate

# Create migration without applying (dry run)
npx prisma migrate dev --create-only --name add_new_field

Production Environment

# IMPORTANT: Never use `migrate dev` in production!

# Apply pending migrations (production-safe)
npx prisma migrate deploy

# Check migration status first
npx prisma migrate status

Production Migration Process:

# On production server
cd /var/www/thrivesend

# 1. Check current status
npx prisma migrate status

# 2. Backup database
pg_dump thrivesend_b2b2g > backup_$(date +%Y%m%d_%H%M%S).sql

# 3. Apply migrations
npx prisma migrate deploy

# 4. Verify success
npx prisma migrate status

# 5. Restart application
pm2 restart thrivesend

---

Migration Best Practices

✅ DO

1. Use descriptive migration names:

   # ✅ GOOD
   npx prisma migrate dev --name add_contact_email_to_clients
   npx prisma migrate dev --name add_security_clearance_levels
   
   # ❌ BAD
   npx prisma migrate dev --name update
   npx prisma migrate dev --name fix

2. Review SQL before applying:

   # Create migration without applying
   npx prisma migrate dev --create-only --name my_migration
   
   # Review the SQL
   cat prisma/migrations/*/migration.sql
   
   # Apply manually if needed
   npx prisma migrate dev

3. Test migrations on development database first:

   # Test on dev database
   DATABASE_URL="postgresql://localhost:5432/thrivesend_dev" \
     npx prisma migrate dev
   
   # Verify it works
   # Then apply to production

4. Include data transformations when needed:

   -- Good example: Update existing records before adding constraint
   UPDATE clients SET contactEmail = 'info@example.com'
   WHERE contactEmail IS NULL;
   
   ALTER TABLE clients ALTER COLUMN contactEmail SET NOT NULL;

5. Add indexes for POPIA queries:

   -- Add indexes for audit log queries
   CREATE INDEX "audit_logs_timestamp_idx"
     ON "public"."audit_logs"("timestamp");
   CREATE INDEX "audit_logs_userId_idx"
     ON "public"."audit_logs"("userId");

❌ DON’T

1. Edit applied migrations:

   # ❌ NEVER edit migrations that have been applied
   # Once a migration is in production, it's immutable
   
   # ✅ Instead, create a new migration
   npx prisma migrate dev --name fix_previous_migration

2. Use prisma db push in production:

   # ❌ DANGEROUS - No migration history
   npx prisma db push
   
   # ✅ Always use migrate deploy
   npx prisma migrate deploy

3. Delete migrations:

   # ❌ NEVER delete migration files
   rm -rf prisma/migrations/
   
   # This breaks production deployments

4. Skip backing up before production migrations:

   # ❌ RISKY
   npx prisma migrate deploy
   
   # ✅ SAFE
   pg_dump thrivesend_b2b2g > backup.sql
   npx prisma migrate deploy

---

Checking Migration Status

View Migration Status

# Check which migrations are applied
npx prisma migrate status

Example Output:

Status: All migrations have been applied

Database schema is up to date!

Applied migrations:
  20250924095149_init
  20251002193302_make_contact_email_required
  20251002203425_add_political_organization_support

When Migrations Are Pending

Status: 2 migrations have not been applied

Pending migrations:
  20260115120000_add_campaign_analytics
  20260118140000_add_security_clearance_expiry

To apply pending migrations, run:
  npx prisma migrate deploy

---

Handling Migration Issues

Issue: Migration Failed Midway

Scenario: Migration partially applied, database in inconsistent state.

# 1. Check status
npx prisma migrate status
# Shows: Migration failed at step X

# 2. Restore from backup
psql thrivesend_dev < backup_20260126.sql

# 3. Fix the migration SQL
# Edit: prisma/migrations/TIMESTAMP_name/migration.sql

# 4. Try again
npx prisma migrate dev

Issue: Schema Drift Detected

Scenario: Database schema doesn’t match Prisma schema.

Error: The database schema is not in sync with your migration history.

To fix:
  1. Run `npx prisma migrate dev` (development)
  2. Run `npx prisma migrate deploy` (production)

Solution:

# Development: Sync and create migration
npx prisma migrate dev --name sync_schema

# Production: Review and apply carefully
npx prisma migrate status
npx prisma migrate deploy

Issue: Baseline Existing Database

Scenario: Adding Prisma to existing database with data.

# 1. Create initial migration from schema
npx prisma migrate dev --name init --create-only

# 2. Mark migration as applied (don't run SQL)
npx prisma migrate resolve --applied init

# 3. Continue with normal workflow
npx prisma migrate dev --name your_next_migration

---

POPIA Compliance Considerations

Data Transformations

When migrating data, ensure POPIA compliance:

-- ❌ BAD: Exposes personal information in migration
UPDATE clients SET email = 'test@example.com';

-- ✅ GOOD: Anonymize while preserving structure
UPDATE clients SET
  email = CONCAT('user', id, '@example.com')
WHERE env = 'development';

-- Log the transformation
INSERT INTO audit_logs (action, resourceType, details, timestamp)
VALUES (
  'DATA_MIGRATION',
  'Client',
  '{"migration": "anonymize_dev_data", "count": ' || ROW_COUNT || '}',
  NOW()
);

Consent Migrations

When adding consent fields, handle existing users:

-- Add consent fields
ALTER TABLE organization_users
  ADD COLUMN "popiaConsentGiven" BOOLEAN DEFAULT false,
  ADD COLUMN "consentDate" TIMESTAMP(3);

-- Create consent records for existing users
INSERT INTO popia_consent_records (
  userId, email, organizationId,
  dataProcessingConsent, consentTimestamp,
  ipAddress, userAgent
)
SELECT
  id, email, organizationId,
  false, -- Require re-consent
  NOW(),
  '0.0.0.0', -- Unknown IP for existing users
  'migration'
FROM organization_users
WHERE popiaConsentGiven IS NULL;

Audit Log Migrations

Always log schema changes:

-- After migration, log the change
INSERT INTO audit_logs (
  action, resourceType, details, timestamp
)
VALUES (
  'SCHEMA_MIGRATION',
  'Database',
  '{"migration": "add_political_organization_support", "models": ["Client"]}',
  NOW()
);

---

Zero-Downtime Migrations

For production deployments with minimal downtime:

Strategy 1: Additive Changes

-- Phase 1: Add new column (nullable)
ALTER TABLE clients ADD COLUMN newField TEXT;

-- Deploy code that writes to both old and new fields

-- Phase 2: Backfill data
UPDATE clients SET newField = oldField WHERE newField IS NULL;

-- Phase 3: Make column required
ALTER TABLE clients ALTER COLUMN newField SET NOT NULL;

-- Phase 4: Remove old column
ALTER TABLE clients DROP COLUMN oldField;

Strategy 2: Expand-Contract Pattern

-- Expand: Add new structure
CREATE TABLE clients_new (
  -- new schema
);

-- Migrate data
INSERT INTO clients_new SELECT * FROM clients;

-- Contract: Replace old table
DROP TABLE clients;
ALTER TABLE clients_new RENAME TO clients;

---

Migration Checklist

Before deploying migrations to production:

• Migration tested on development database
• Migration reviewed by team member
• Database backup created
• Migration SQL reviewed for POPIA compliance
• Rollback plan documented
• Production maintenance window scheduled (if needed)
• Monitoring alerts configured
• Post-migration verification queries prepared

Example Checklist:

## Migration: add_campaign_analytics
- [x] Created migration: `npx prisma migrate dev --name add_campaign_analytics`
- [x] Tested on local database
- [x] Reviewed SQL file
- [x] POPIA compliance verified (no PII in migration)
- [x] Backup plan: `pg_dump thrivesend_b2b2g > backup_20260126.sql`
- [x] Rollback SQL prepared: `DROP TABLE campaign_analytics;`
- [x] Deployment window: 2026-01-27 02:00 AM SAST (low traffic)
- [x] Verification query: `SELECT COUNT(*) FROM campaign_analytics;`

---

Next Steps

• ← Prisma Setup - Database configuration
• Schema Management → - B2B2G schema design
• Querying Data → - Query patterns and best practices

---

Related Documentation:

• Service Architecture - Using Prisma in services

---

Last Updated: January 26, 2026