Database Migration Strategies for ChatGPT Apps: Zero-Downtime Patterns & Best Practices

Database migrations are one of the most high-stakes operations in production ChatGPT applications. A single failed migration can cause hours of downtime, corrupt user data, or break backward compatibility with existing MCP servers. Yet most developers approach migrations with ad-hoc scripts and manual testing—a recipe for disaster.

The stakes are even higher for ChatGPT apps because they operate in a distributed environment where multiple MCP server instances may be running different code versions simultaneously. Traditional migration approaches (like taking the database offline, running ALTER TABLE statements, then restarting) simply don't work when you need 99.99% uptime and seamless user experiences.

This guide provides production-ready migration strategies for ChatGPT applications, covering schema migrations, data migrations, and zero-downtime deployment patterns. Whether you're using PostgreSQL, MySQL, or Firestore, you'll learn how to safely evolve your database schema without downtime, data loss, or user disruption.

What you'll learn:

• Expand-contract and dual-write patterns for backward-compatible migrations
• Production-ready Liquibase, Flyway, and Firestore migration implementations
• Zero-downtime migration techniques (shadow traffic, backfill scripts, dual-write)
• Data validation and consistency checking patterns
• Automated rollback procedures with safety guarantees

By the end of this article, you'll have battle-tested migration patterns that handle the unique challenges of distributed ChatGPT applications—from schema versioning across multiple MCP servers to validating data integrity during live traffic.

Understanding Migration Patterns

Before diving into code, let's establish the fundamental migration patterns that enable zero-downtime deployments.

Schema Migrations vs Data Migrations

Schema migrations change the structure of your database (adding columns, creating indexes, modifying constraints). They're typically fast and can be executed as atomic DDL statements.

Data migrations transform existing data to match new schema requirements (backfilling columns, splitting tables, normalizing data). They're often slow and require careful orchestration to avoid locking tables during peak traffic.

The key insight: separate schema changes from data changes. Deploy schema migrations first (ensuring backward compatibility), then backfill data asynchronously in the background.

Expand-Contract Pattern

The expand-contract pattern (also called "parallel change") is the gold standard for zero-downtime migrations:

1. Expand: Add new schema elements (columns, tables, indexes) without removing old ones
2. Migrate: Deploy application code that writes to both old and new schema (dual-write)
3. Backfill: Copy existing data from old schema to new schema
4. Contract: Deploy application code that reads from new schema only
5. Cleanup: Remove old schema elements after validation

This pattern ensures that at every stage, both old and new application versions can coexist—critical for rolling deployments of MCP servers.

Dual-Write Pattern

During the "migrate" phase, your application writes to both old and new schema simultaneously:

// WRONG: Only write to new schema
await db.users.update({ email: newEmail });

// RIGHT: Dual-write to both schemas
await Promise.all([
  db.users.update({ email: newEmail }),           // Old schema
  db.user_profiles.update({ contact_email: newEmail }) // New schema
]);

Dual-write ensures that data remains consistent across schema versions, allowing you to deploy new code gradually without data loss.

Backward-Compatible Changes

Not all schema changes require expand-contract. These changes are inherently backward-compatible:

• Adding nullable columns (existing queries ignore them)
• Adding indexes (improves performance without breaking queries)
• Creating new tables (old code doesn't reference them)

These changes can be deployed immediately without dual-write phases.

Breaking changes (removing columns, renaming fields, changing data types) always require expand-contract patterns.

Schema Migration Tools

Let's implement production-ready schema migrations using industry-standard tools.

Liquibase Configuration (PostgreSQL/MySQL)

Liquibase provides declarative, version-controlled schema migrations with rollback support.

File: db/changelog/changelog-master.xml

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-latest.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext
    http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

    <!-- Migration 001: Expand - Add new email column -->
    <changeSet id="001-add-contact-email-column" author="engineering">
        <preConditions onFail="MARK_RAN">
            <not>
                <columnExists tableName="user_profiles" columnName="contact_email"/>
            </not>
        </preConditions>

        <addColumn tableName="user_profiles">
            <column name="contact_email" type="VARCHAR(255)">
                <constraints nullable="true"/>
            </column>
        </addColumn>

        <createIndex indexName="idx_user_profiles_contact_email"
                     tableName="user_profiles">
            <column name="contact_email"/>
        </createIndex>

        <rollback>
            <dropIndex indexName="idx_user_profiles_contact_email"
                       tableName="user_profiles"/>
            <dropColumn tableName="user_profiles" columnName="contact_email"/>
        </rollback>
    </changeSet>

    <!-- Migration 002: Data - Backfill contact_email from users.email -->
    <changeSet id="002-backfill-contact-email" author="engineering">
        <preConditions onFail="MARK_RAN">
            <columnExists tableName="user_profiles" columnName="contact_email"/>
        </preConditions>

        <sql>
            UPDATE user_profiles up
            SET contact_email = u.email
            FROM users u
            WHERE up.user_id = u.id
              AND up.contact_email IS NULL
        </sql>

        <rollback>
            <sql>UPDATE user_profiles SET contact_email = NULL</sql>
        </rollback>
    </changeSet>

    <!-- Migration 003: Contract - Make contact_email non-nullable -->
    <changeSet id="003-enforce-contact-email-not-null" author="engineering">
        <preConditions onFail="HALT">
            <sqlCheck expectedResult="0">
                SELECT COUNT(*) FROM user_profiles WHERE contact_email IS NULL
            </sqlCheck>
        </preConditions>

        <addNotNullConstraint tableName="user_profiles"
                              columnName="contact_email"
                              columnDataType="VARCHAR(255)"/>

        <rollback>
            <dropNotNullConstraint tableName="user_profiles"
                                   columnName="contact_email"
                                   columnDataType="VARCHAR(255)"/>
        </rollback>
    </changeSet>

    <!-- Migration 004: Cleanup - Drop old users.email column -->
    <changeSet id="004-drop-users-email-column" author="engineering">
        <preConditions onFail="HALT">
            <and>
                <columnExists tableName="user_profiles" columnName="contact_email"/>
                <sqlCheck expectedResult="0">
                    SELECT COUNT(*) FROM user_profiles WHERE contact_email IS NULL
                </sqlCheck>
            </and>
        </preConditions>

        <dropColumn tableName="users" columnName="email"/>

        <rollback>
            <addColumn tableName="users">
                <column name="email" type="VARCHAR(255)">
                    <constraints nullable="false"/>
                </column>
            </addColumn>
        </rollback>
    </changeSet>

</databaseChangeLog>

Key features:

• Preconditions prevent running migrations in invalid states
• Rollback support for every changeset
• Idempotent (safe to run multiple times)
• Atomic (each changeset is a transaction)

Run migrations:

liquibase --changeLogFile=db/changelog/changelog-master.xml update

Flyway Migration (SQL-Based)

Flyway uses sequential SQL files for version-controlled migrations.

File: db/migration/V001__add_contact_email_column.sql

-- Migration V001: Expand - Add new contact_email column
-- Author: engineering
-- Date: 2026-12-25

-- Add nullable contact_email column to user_profiles
ALTER TABLE user_profiles
ADD COLUMN IF NOT EXISTS contact_email VARCHAR(255) NULL;

-- Create index for email lookups
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_user_profiles_contact_email
ON user_profiles(contact_email);

-- Add comment for documentation
COMMENT ON COLUMN user_profiles.contact_email IS
'Primary contact email (migrated from users.email). Part of expand-contract migration.';

File: db/migration/V002__backfill_contact_email.sql

-- Migration V002: Data - Backfill contact_email from users.email
-- Author: engineering
-- Date: 2026-12-25
-- Estimated duration: 30 seconds per 1M rows

-- Backfill contact_email from users.email (idempotent)
UPDATE user_profiles up
SET contact_email = u.email
FROM users u
WHERE up.user_id = u.id
  AND up.contact_email IS NULL;

-- Verify backfill completeness
DO $$
DECLARE
  null_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO null_count
  FROM user_profiles
  WHERE contact_email IS NULL;

  IF null_count > 0 THEN
    RAISE WARNING 'Backfill incomplete: % rows still have NULL contact_email', null_count;
  ELSE
    RAISE NOTICE 'Backfill complete: All rows populated';
  END IF;
END $$;

File: db/migration/V003__enforce_contact_email_not_null.sql

-- Migration V003: Contract - Make contact_email non-nullable
-- Author: engineering
-- Date: 2026-12-25
-- Prerequisites: V002 backfill must be complete

-- Validate no NULL values exist
DO $$
DECLARE
  null_count INTEGER;
BEGIN
  SELECT COUNT(*) INTO null_count
  FROM user_profiles
  WHERE contact_email IS NULL;

  IF null_count > 0 THEN
    RAISE EXCEPTION 'Cannot enforce NOT NULL: % rows have NULL contact_email', null_count;
  END IF;
END $$;

-- Enforce NOT NULL constraint
ALTER TABLE user_profiles
ALTER COLUMN contact_email SET NOT NULL;

-- Add unique constraint (optional, depends on requirements)
-- CREATE UNIQUE INDEX idx_user_profiles_contact_email_unique
-- ON user_profiles(contact_email);

Run migrations:

flyway migrate

Firestore Schema Versioning (TypeScript)

Firestore doesn't have built-in schema versioning, so we implement it manually.

File: src/migrations/firestore-migrator.ts

import { getFirestore, collection, doc, writeBatch, getDocs, query, where } from 'firebase/firestore';

interface MigrationMetadata {
  version: number;
  name: string;
  appliedAt: Date;
  status: 'pending' | 'running' | 'completed' | 'failed';
  error?: string;
}

class FirestoreMigrator {
  private db = getFirestore();
  private migrationsCollection = collection(this.db, '_migrations');

  /**
   * Migration V001: Add contactEmail field to user_profiles
   */
  async migrateV001AddContactEmail(): Promise<void> {
    const migrationVersion = 1;
    const migrationName = 'add_contact_email_field';

    // Check if migration already applied
    if (await this.isMigrationApplied(migrationVersion)) {
      console.log(`Migration V${migrationVersion} already applied, skipping`);
      return;
    }

    // Mark migration as running
    await this.recordMigrationStart(migrationVersion, migrationName);

    try {
      // Fetch all user_profiles documents
      const userProfilesRef = collection(this.db, 'user_profiles');
      const snapshot = await getDocs(userProfilesRef);

      console.log(`Migrating ${snapshot.size} user_profiles documents...`);

      // Process in batches (Firestore limit: 500 writes per batch)
      const batchSize = 500;
      let batch = writeBatch(this.db);
      let batchCount = 0;
      let totalMigrated = 0;

      for (const docSnapshot of snapshot.docs) {
        const data = docSnapshot.data();

        // Skip if contactEmail already exists
        if (data.contactEmail) {
          continue;
        }

        // Backfill contactEmail from email field
        batch.update(docSnapshot.ref, {
          contactEmail: data.email || null,
          _migrationVersion: migrationVersion,
          _migratedAt: new Date()
        });

        batchCount++;
        totalMigrated++;

        // Commit batch when reaching size limit
        if (batchCount >= batchSize) {
          await batch.commit();
          console.log(`Committed batch: ${totalMigrated} documents migrated`);
          batch = writeBatch(this.db);
          batchCount = 0;
        }
      }

      // Commit remaining documents
      if (batchCount > 0) {
        await batch.commit();
        console.log(`Committed final batch: ${totalMigrated} total documents migrated`);
      }

      // Mark migration as completed
      await this.recordMigrationComplete(migrationVersion);
      console.log(`Migration V${migrationVersion} completed successfully`);

    } catch (error) {
      // Mark migration as failed
      await this.recordMigrationFailed(migrationVersion, error.message);
      throw error;
    }
  }

  /**
   * Check if migration has been applied
   */
  private async isMigrationApplied(version: number): Promise<boolean> {
    const migrationDoc = await getDocs(
      query(this.migrationsCollection, where('version', '==', version))
    );
    return !migrationDoc.empty && migrationDoc.docs[0].data().status === 'completed';
  }

  /**
   * Record migration start
   */
  private async recordMigrationStart(version: number, name: string): Promise<void> {
    const migrationRef = doc(this.migrationsCollection, `v${version}`);
    await writeBatch(this.db).set(migrationRef, {
      version,
      name,
      appliedAt: new Date(),
      status: 'running'
    }).commit();
  }

  /**
   * Record migration completion
   */
  private async recordMigrationComplete(version: number): Promise<void> {
    const migrationRef = doc(this.migrationsCollection, `v${version}`);
    await writeBatch(this.db).update(migrationRef, {
      status: 'completed',
      completedAt: new Date()
    }).commit();
  }

  /**
   * Record migration failure
   */
  private async recordMigrationFailed(version: number, error: string): Promise<void> {
    const migrationRef = doc(this.migrationsCollection, `v${version}`);
    await writeBatch(this.db).update(migrationRef, {
      status: 'failed',
      error,
      failedAt: new Date()
    }).commit();
  }
}

// Export singleton instance
export const migrator = new FirestoreMigrator();

Usage:

import { migrator } from './migrations/firestore-migrator';

// Run migration
await migrator.migrateV001AddContactEmail();

Zero-Downtime Migration Techniques

Now let's implement advanced patterns for zero-downtime migrations during live traffic.

Dual-Write Implementation

Dual-write ensures data consistency during expand-contract migrations.

File: src/services/user-profile-service.ts

import { getFirestore, doc, updateDoc, getDoc, writeBatch } from 'firebase/firestore';

interface UserProfile {
  userId: string;
  email?: string;           // OLD schema (deprecated)
  contactEmail?: string;    // NEW schema
  displayName: string;
}

class UserProfileService {
  private db = getFirestore();

  /**
   * Update user email with dual-write pattern
   * Writes to both OLD and NEW schema during migration phase
   */
  async updateUserEmail(userId: string, newEmail: string): Promise<void> {
    const profileRef = doc(this.db, 'user_profiles', userId);

    // Dual-write to both schemas (expand-contract phase)
    await updateDoc(profileRef, {
      email: newEmail,         // OLD schema (will be removed in cleanup phase)
      contactEmail: newEmail,  // NEW schema
      updatedAt: new Date(),
      _dualWriteActive: true   // Flag for monitoring
    });

    // Log dual-write for observability
    console.log(`[DUAL-WRITE] Updated email for user ${userId}: ${newEmail}`);
  }

  /**
   * Read user email with fallback logic
   * Reads from NEW schema, falls back to OLD schema if missing
   */
  async getUserEmail(userId: string): Promise<string | null> {
    const profileRef = doc(this.db, 'user_profiles', userId);
    const snapshot = await getDoc(profileRef);

    if (!snapshot.exists()) {
      return null;
    }

    const data = snapshot.data() as UserProfile;

    // Prefer NEW schema, fallback to OLD schema
    const email = data.contactEmail || data.email || null;

    // Log schema version used (for monitoring migration progress)
    if (data.contactEmail) {
      console.log(`[READ] Using NEW schema for user ${userId}`);
    } else if (data.email) {
      console.log(`[READ] Falling back to OLD schema for user ${userId}`);
    }

    return email;
  }

  /**
   * Batch update emails with dual-write
   * Used for bulk operations (e.g., admin updates, imports)
   */
  async batchUpdateEmails(updates: Array<{ userId: string; email: string }>): Promise<void> {
    const batch = writeBatch(this.db);

    for (const { userId, email } of updates) {
      const profileRef = doc(this.db, 'user_profiles', userId);

      // Dual-write in batch
      batch.update(profileRef, {
        email,          // OLD schema
        contactEmail: email, // NEW schema
        updatedAt: new Date(),
        _dualWriteActive: true
      });
    }

    await batch.commit();
    console.log(`[DUAL-WRITE] Batch updated ${updates.length} user emails`);
  }

  /**
   * Validate dual-write consistency
   * Checks if OLD and NEW schema values match
   */
  async validateDualWriteConsistency(userId: string): Promise<boolean> {
    const profileRef = doc(this.db, 'user_profiles', userId);
    const snapshot = await getDoc(profileRef);

    if (!snapshot.exists()) {
      return true; // No data to validate
    }

    const data = snapshot.data() as UserProfile;

    // Both schemas should match during dual-write phase
    if (data.email && data.contactEmail && data.email !== data.contactEmail) {
      console.error(`[DUAL-WRITE] Inconsistency detected for user ${userId}: email=${data.email}, contactEmail=${data.contactEmail}`);
      return false;
    }

    return true;
  }
}

export const userProfileService = new UserProfileService();

Deployment phases:

1. Phase 1 (Expand): Add contactEmail field (nullable)
2. Phase 2 (Dual-write): Deploy code that writes to both email and contactEmail
3. Phase 3 (Backfill): Run migration script to populate contactEmail from email
4. Phase 4 (Contract): Deploy code that reads from contactEmail only
5. Phase 5 (Cleanup): Remove email field after validation

Shadow Traffic Recorder

Shadow traffic records writes to the new schema without affecting reads, enabling safe validation.

File: src/services/shadow-traffic-recorder.ts

import { getFirestore, doc, setDoc, collection, addDoc } from 'firebase/firestore';

interface ShadowWrite {
  userId: string;
  operation: 'create' | 'update' | 'delete';
  oldSchema: any;
  newSchema: any;
  timestamp: Date;
  success: boolean;
  error?: string;
}

class ShadowTrafficRecorder {
  private db = getFirestore();
  private shadowCollection = collection(this.db, '_shadow_writes');

  /**
   * Record write to NEW schema (without affecting primary write)
   * Used during validation phase before fully committing to new schema
   */
  async recordShadowWrite(
    userId: string,
    operation: 'create' | 'update' | 'delete',
    oldSchemaData: any,
    newSchemaData: any
  ): Promise<void> {
    try {
      // Write to NEW schema (shadow)
      const newProfileRef = doc(this.db, 'user_profiles_v2', userId);

      if (operation === 'delete') {
        await setDoc(newProfileRef, { _deleted: true, _deletedAt: new Date() });
      } else {
        await setDoc(newProfileRef, newSchemaData, { merge: operation === 'update' });
      }

      // Record successful shadow write
      await this.logShadowWrite({
        userId,
        operation,
        oldSchema: oldSchemaData,
        newSchema: newSchemaData,
        timestamp: new Date(),
        success: true
      });

      console.log(`[SHADOW] Successfully wrote to NEW schema for user ${userId}`);

    } catch (error) {
      // Record failed shadow write (but don't throw - primary write succeeded)
      await this.logShadowWrite({
        userId,
        operation,
        oldSchema: oldSchemaData,
        newSchema: newSchemaData,
        timestamp: new Date(),
        success: false,
        error: error.message
      });

      console.error(`[SHADOW] Failed to write to NEW schema for user ${userId}:`, error);
    }
  }

  /**
   * Log shadow write to audit collection
   */
  private async logShadowWrite(shadowWrite: ShadowWrite): Promise<void> {
    await addDoc(this.shadowCollection, shadowWrite);
  }

  /**
   * Compare OLD vs NEW schema data for consistency
   */
  async compareShadowData(userId: string): Promise<{ consistent: boolean; differences: string[] }> {
    const oldRef = doc(this.db, 'user_profiles', userId);
    const newRef = doc(this.db, 'user_profiles_v2', userId);

    const [oldSnapshot, newSnapshot] = await Promise.all([
      getDoc(oldRef),
      getDoc(newRef)
    ]);

    if (!oldSnapshot.exists() || !newSnapshot.exists()) {
      return { consistent: false, differences: ['One or both documents missing'] };
    }

    const oldData = oldSnapshot.data();
    const newData = newSnapshot.data();

    // Compare key fields
    const differences: string[] = [];

    if (oldData.email !== newData.contactEmail) {
      differences.push(`email mismatch: ${oldData.email} vs ${newData.contactEmail}`);
    }

    if (oldData.displayName !== newData.displayName) {
      differences.push(`displayName mismatch: ${oldData.displayName} vs ${newData.displayName}`);
    }

    return {
      consistent: differences.length === 0,
      differences
    };
  }
}

export const shadowRecorder = new ShadowTrafficRecorder();

Usage:

// During validation phase, record writes to NEW schema without affecting primary
await shadowRecorder.recordShadowWrite(
  userId,
  'update',
  { email: newEmail, displayName },  // OLD schema
  { contactEmail: newEmail, displayName } // NEW schema
);

// Later, validate consistency
const { consistent, differences } = await shadowRecorder.compareShadowData(userId);
if (!consistent) {
  console.error('Shadow data inconsistency:', differences);
}

Data Backfill Script

Backfill scripts populate new schema fields from old schema data asynchronously.

File: scripts/backfill-contact-email.ts

import { getFirestore, collection, getDocs, writeBatch, doc, query, where, limit } from 'firebase/firestore';
import { initializeApp } from 'firebase/app';

// Initialize Firebase (use service account for server-side execution)
const firebaseConfig = {
  // ... config from environment
};
initializeApp(firebaseConfig);

interface BackfillProgress {
  totalDocuments: number;
  processedDocuments: number;
  skippedDocuments: number;
  failedDocuments: number;
  startTime: Date;
  endTime?: Date;
}

class ContactEmailBackfiller {
  private db = getFirestore();
  private progress: BackfillProgress = {
    totalDocuments: 0,
    processedDocuments: 0,
    skippedDocuments: 0,
    failedDocuments: 0,
    startTime: new Date()
  };

  /**
   * Backfill contactEmail field for all user_profiles
   * Processes in batches to avoid memory issues and Firestore write limits
   */
  async backfillContactEmail(): Promise<BackfillProgress> {
    console.log('Starting contactEmail backfill migration...');

    // Get total document count
    const userProfilesRef = collection(this.db, 'user_profiles');
    const totalSnapshot = await getDocs(userProfilesRef);
    this.progress.totalDocuments = totalSnapshot.size;

    console.log(`Total documents to process: ${this.progress.totalDocuments}`);

    // Process in batches (Firestore limit: 500 writes per batch)
    const batchSize = 500;
    let batch = writeBatch(this.db);
    let batchCount = 0;

    for (const docSnapshot of totalSnapshot.docs) {
      const data = docSnapshot.data();

      // Skip if contactEmail already exists (idempotent)
      if (data.contactEmail) {
        this.progress.skippedDocuments++;
        console.log(`Skipped ${docSnapshot.id}: contactEmail already exists`);
        continue;
      }

      // Skip if email field is missing (can't backfill)
      if (!data.email) {
        this.progress.skippedDocuments++;
        console.log(`Skipped ${docSnapshot.id}: email field missing`);
        continue;
      }

      try {
        // Backfill contactEmail from email
        batch.update(docSnapshot.ref, {
          contactEmail: data.email,
          _backfillDate: new Date(),
          _backfillVersion: 1
        });

        batchCount++;
        this.progress.processedDocuments++;

        // Commit batch when reaching size limit
        if (batchCount >= batchSize) {
          await batch.commit();
          console.log(`Progress: ${this.progress.processedDocuments}/${this.progress.totalDocuments} documents processed`);
          batch = writeBatch(this.db);
          batchCount = 0;
        }

      } catch (error) {
        this.progress.failedDocuments++;
        console.error(`Failed to backfill ${docSnapshot.id}:`, error);
      }
    }

    // Commit remaining documents
    if (batchCount > 0) {
      await batch.commit();
      console.log(`Committed final batch`);
    }

    this.progress.endTime = new Date();

    // Print summary
    this.printSummary();

    return this.progress;
  }

  /**
   * Print migration summary
   */
  private printSummary(): void {
    const duration = this.progress.endTime
      ? (this.progress.endTime.getTime() - this.progress.startTime.getTime()) / 1000
      : 0;

    console.log('\n=== Backfill Migration Summary ===');
    console.log(`Total documents: ${this.progress.totalDocuments}`);
    console.log(`Processed: ${this.progress.processedDocuments}`);
    console.log(`Skipped: ${this.progress.skippedDocuments}`);
    console.log(`Failed: ${this.progress.failedDocuments}`);
    console.log(`Duration: ${duration.toFixed(2)} seconds`);
    console.log(`Rate: ${(this.progress.processedDocuments / duration).toFixed(2)} docs/sec`);
    console.log('==================================\n');
  }
}

// Execute backfill
const backfiller = new ContactEmailBackfiller();
backfiller.backfillContactEmail()
  .then(progress => {
    console.log('Backfill completed successfully:', progress);
    process.exit(0);
  })
  .catch(error => {
    console.error('Backfill failed:', error);
    process.exit(1);
  });

Run backfill:

ts-node scripts/backfill-contact-email.ts

Data Validation Strategies

Validation ensures migration correctness before proceeding to contract phase.

Migration Validator

File: scripts/validate-migration.ts

import { getFirestore, collection, getDocs, doc, getDoc } from 'firebase/firestore';

interface ValidationResult {
  totalChecked: number;
  passed: number;
  failed: number;
  errors: Array<{ userId: string; issue: string }>;
}

class MigrationValidator {
  private db = getFirestore();

  /**
   * Validate contactEmail migration completeness
   */
  async validateContactEmailMigration(): Promise<ValidationResult> {
    const result: ValidationResult = {
      totalChecked: 0,
      passed: 0,
      failed: 0,
      errors: []
    };

    const userProfilesRef = collection(this.db, 'user_profiles');
    const snapshot = await getDocs(userProfilesRef);

    console.log(`Validating ${snapshot.size} documents...`);

    for (const docSnapshot of snapshot.docs) {
      result.totalChecked++;
      const data = docSnapshot.data();

      // Rule 1: contactEmail must exist
      if (!data.contactEmail) {
        result.failed++;
        result.errors.push({
          userId: docSnapshot.id,
          issue: 'contactEmail field missing'
        });
        continue;
      }

      // Rule 2: contactEmail must match email (during dual-write phase)
      if (data.email && data.email !== data.contactEmail) {
        result.failed++;
        result.errors.push({
          userId: docSnapshot.id,
          issue: `email/contactEmail mismatch: ${data.email} vs ${data.contactEmail}`
        });
        continue;
      }

      // Rule 3: contactEmail must be valid email format
      const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;
      if (!emailRegex.test(data.contactEmail)) {
        result.failed++;
        result.errors.push({
          userId: docSnapshot.id,
          issue: `Invalid email format: ${data.contactEmail}`
        });
        continue;
      }

      result.passed++;
    }

    this.printValidationSummary(result);
    return result;
  }

  /**
   * Print validation summary
   */
  private printValidationSummary(result: ValidationResult): void {
    console.log('\n=== Migration Validation Summary ===');
    console.log(`Total checked: ${result.totalChecked}`);
    console.log(`Passed: ${result.passed} (${((result.passed / result.totalChecked) * 100).toFixed(2)}%)`);
    console.log(`Failed: ${result.failed} (${((result.failed / result.totalChecked) * 100).toFixed(2)}%)`);

    if (result.errors.length > 0) {
      console.log('\nFirst 10 errors:');
      result.errors.slice(0, 10).forEach(err => {
        console.log(`  - User ${err.userId}: ${err.issue}`);
      });
    }

    console.log('====================================\n');
  }
}

// Execute validation
const validator = new MigrationValidator();
validator.validateContactEmailMigration()
  .then(result => {
    if (result.failed === 0) {
      console.log('✅ Migration validation PASSED');
      process.exit(0);
    } else {
      console.error('❌ Migration validation FAILED');
      process.exit(1);
    }
  })
  .catch(error => {
    console.error('Validation error:', error);
    process.exit(1);
  });

Checksum Comparator

File: scripts/checksum-comparator.ts

import { createHash } from 'crypto';
import { getFirestore, collection, getDocs } from 'firebase/firestore';

class ChecksumComparator {
  private db = getFirestore();

  /**
   * Generate checksum for user_profiles collection
   * Used to verify data integrity before/after migration
   */
  async generateCollectionChecksum(collectionName: string): Promise<string> {
    const collectionRef = collection(this.db, collectionName);
    const snapshot = await getDocs(collectionRef);

    // Concatenate all document data (sorted by ID for consistency)
    const sortedDocs = snapshot.docs
      .sort((a, b) => a.id.localeCompare(b.id))
      .map(doc => JSON.stringify({ id: doc.id, data: doc.data() }))
      .join('|');

    // Generate SHA-256 checksum
    const checksum = createHash('sha256').update(sortedDocs).digest('hex');

    console.log(`Checksum for ${collectionName}: ${checksum}`);
    console.log(`Documents: ${snapshot.size}`);

    return checksum;
  }

  /**
   * Compare checksums before/after migration
   */
  async compareChecksums(
    beforeChecksum: string,
    afterChecksum: string
  ): Promise<{ identical: boolean; beforeChecksum: string; afterChecksum: string }> {
    const identical = beforeChecksum === afterChecksum;

    if (identical) {
      console.log('✅ Checksums match: Data integrity verified');
    } else {
      console.error('❌ Checksums differ: Data may have changed during migration');
    }

    return { identical, beforeChecksum, afterChecksum };
  }
}

export const checksumComparator = new ChecksumComparator();

Usage:

// Before migration
const beforeChecksum = await checksumComparator.generateCollectionChecksum('user_profiles');

// Run migration...

// After migration
const afterChecksum = await checksumComparator.generateCollectionChecksum('user_profiles');

// Compare
await checksumComparator.compareChecksums(beforeChecksum, afterChecksum);

Data Consistency Checker

File: scripts/consistency-checker.ts

import { getFirestore, collection, getDocs } from 'firebase/firestore';

interface ConsistencyReport {
  totalDocuments: number;
  consistentDocuments: number;
  inconsistentDocuments: number;
  issues: Array<{ documentId: string; fieldMismatches: string[] }>;
}

class DataConsistencyChecker {
  private db = getFirestore();

  /**
   * Check data consistency between OLD and NEW schema
   */
  async checkDualWriteConsistency(): Promise<ConsistencyReport> {
    const report: ConsistencyReport = {
      totalDocuments: 0,
      consistentDocuments: 0,
      inconsistentDocuments: 0,
      issues: []
    };

    const userProfilesRef = collection(this.db, 'user_profiles');
    const snapshot = await getDocs(userProfilesRef);

    report.totalDocuments = snapshot.size;

    for (const docSnapshot of snapshot.docs) {
      const data = docSnapshot.data();
      const fieldMismatches: string[] = [];

      // Check email vs contactEmail consistency
      if (data.email && data.contactEmail && data.email !== data.contactEmail) {
        fieldMismatches.push(`email (${data.email}) != contactEmail (${data.contactEmail})`);
      }

      if (fieldMismatches.length > 0) {
        report.inconsistentDocuments++;
        report.issues.push({
          documentId: docSnapshot.id,
          fieldMismatches
        });
      } else {
        report.consistentDocuments++;
      }
    }

    this.printConsistencyReport(report);
    return report;
  }

  /**
   * Print consistency report
   */
  private printConsistencyReport(report: ConsistencyReport): void {
    console.log('\n=== Data Consistency Report ===');
    console.log(`Total documents: ${report.totalDocuments}`);
    console.log(`Consistent: ${report.consistentDocuments} (${((report.consistentDocuments / report.totalDocuments) * 100).toFixed(2)}%)`);
    console.log(`Inconsistent: ${report.inconsistentDocuments} (${((report.inconsistentDocuments / report.totalDocuments) * 100).toFixed(2)}%)`);

    if (report.issues.length > 0) {
      console.log('\nInconsistencies detected:');
      report.issues.slice(0, 10).forEach(issue => {
        console.log(`  - Document ${issue.documentId}:`);
        issue.fieldMismatches.forEach(mismatch => {
          console.log(`    * ${mismatch}`);
        });
      });
    }

    console.log('===============================\n');
  }
}

export const consistencyChecker = new DataConsistencyChecker();

Rollback Procedures

Automated rollback ensures safe recovery from failed migrations.

Rollback Automation Script

File: scripts/rollback-migration.sh

#!/bin/bash

# Migration Rollback Automation Script
# Usage: ./rollback-migration.sh <migration_version>

set -euo pipefail

MIGRATION_VERSION=${1:-}
PROJECT_ID="gbp2026-5effc"
BACKUP_DIR="./backups"

if [ -z "$MIGRATION_VERSION" ]; then
  echo "Error: Migration version required"
  echo "Usage: ./rollback-migration.sh <migration_version>"
  exit 1
fi

echo "=== Migration Rollback Script ==="
echo "Migration version: $MIGRATION_VERSION"
echo "Project: $PROJECT_ID"
echo "================================"
echo ""

# Step 1: Verify backup exists
BACKUP_FILE="$BACKUP_DIR/user_profiles_before_v${MIGRATION_VERSION}.json"
if [ ! -f "$BACKUP_FILE" ]; then
  echo "❌ Error: Backup file not found: $BACKUP_FILE"
  exit 1
fi

echo "✅ Backup file found: $BACKUP_FILE"

# Step 2: Confirm rollback
read -p "Are you sure you want to rollback migration v${MIGRATION_VERSION}? (yes/no): " CONFIRM
if [ "$CONFIRM" != "yes" ]; then
  echo "Rollback cancelled"
  exit 0
fi

# Step 3: Stop application (prevent writes during rollback)
echo "Stopping application..."
# kubectl scale deployment chatgpt-app --replicas=0 -n production
echo "✅ Application stopped"

# Step 4: Restore Firestore data from backup
echo "Restoring Firestore data from backup..."
npx -y firestore-import \
  --accountCredentials .vault/service-account-key.json \
  --backupFile "$BACKUP_FILE" \
  --nodePath "user_profiles"

echo "✅ Data restored from backup"

# Step 5: Rollback Liquibase migrations (if applicable)
if [ -f "db/changelog/changelog-master.xml" ]; then
  echo "Rolling back Liquibase changesets..."
  liquibase --changeLogFile=db/changelog/changelog-master.xml \
    rollbackCount 1
  echo "✅ Liquibase rollback complete"
fi

# Step 6: Rollback Flyway migrations (if applicable)
if [ -d "db/migration" ]; then
  echo "Rolling back Flyway migrations..."
  # Flyway doesn't support automatic rollback - use undo migrations
  flyway undo
  echo "✅ Flyway rollback complete"
fi

# Step 7: Verify data integrity
echo "Verifying data integrity..."
ts-node scripts/validate-migration.ts
VALIDATION_EXIT_CODE=$?

if [ $VALIDATION_EXIT_CODE -ne 0 ]; then
  echo "❌ Data validation failed after rollback"
  exit 1
fi

echo "✅ Data validation passed"

# Step 8: Restart application
echo "Restarting application..."
# kubectl scale deployment chatgpt-app --replicas=3 -n production
echo "✅ Application restarted"

# Step 9: Monitor application health
echo "Monitoring application health..."
sleep 10
# kubectl get pods -n production | grep chatgpt-app
echo "✅ Application health check passed"

echo ""
echo "=== Rollback Complete ==="
echo "Migration v${MIGRATION_VERSION} has been rolled back successfully"
echo "========================="

Create backup before migration:

# Export Firestore collection
npx -y firestore-export \
  --accountCredentials .vault/service-account-key.json \
  --backupFile ./backups/user_profiles_before_v1.json \
  --nodePath "user_profiles"

Execute rollback:

chmod +x scripts/rollback-migration.sh
./scripts/rollback-migration.sh 1

Production Migration Checklist

Before running any production migration, verify:

Pre-Migration

• Backup created and verified (Firestore export or database dump)
• Rollback script tested in staging environment
• Migration tested in staging with production-like data volume
• Dual-write code deployed and validated
• Shadow traffic recorder running (if applicable)
• Database indexes created for new schema fields
• Monitoring alerts configured for migration metrics
• Incident response team notified and on standby

During Migration

• Migration started during low-traffic period
• Real-time monitoring of error rates and latency
• Backfill progress tracked (documents/second)
• Data consistency validated every 1000 documents
• Application logs monitored for schema-related errors
• Rollback criteria defined (e.g., >1% error rate → rollback)

Post-Migration

• Data validation script passed (100% consistency)
• Checksum comparison verified (before/after)
• Application error rate unchanged
• Performance metrics unchanged (latency, throughput)
• Smoke tests passed (create/read/update/delete operations)
• Production traffic validated for 24 hours
• Cleanup migration scheduled (remove old schema fields)
• Postmortem documented (issues, learnings, improvements)

Rollback Triggers

Immediately rollback if:

• Error rate increases by >1%
• Data inconsistency detected in validation
• Latency increases by >20%
• Application crashes or restarts
• User-reported data loss or corruption

Conclusion: Build Migration Confidence

Database migrations don't have to be terrifying. With expand-contract patterns, dual-write implementations, and automated validation, you can migrate production ChatGPT applications with zero downtime and zero data loss.

Key takeaways:

1. Always use expand-contract for breaking schema changes
2. Dual-write during migration to ensure backward compatibility
3. Validate everything (checksums, consistency checks, smoke tests)
4. Automate rollbacks (don't rely on manual recovery procedures)
5. Test in staging first with production-like data volumes

The production-ready code examples in this article (Liquibase, Flyway, Firestore migrations, dual-write service, shadow recorder, backfill scripts, validators) give you battle-tested patterns for migrating ChatGPT applications safely.

For more architectural guidance on building production ChatGPT apps, see our comprehensive ChatGPT Applications Guide. To deploy these migrations with zero downtime, explore Zero-Downtime Deployment Strategies and Blue-Green Deployment Patterns.

Ready to build production ChatGPT apps without migration anxiety? Start your free trial and generate deployment-ready MCP servers with built-in migration tooling. From zero to ChatGPT App Store in 48 hours—database migrations included.

---

Related Resources

• ChatGPT Applications Guide: Complete Technical Architecture
• Zero-Downtime Deployments for ChatGPT Apps: Blue-Green & Canary Patterns
• Blue-Green Deployment for ChatGPT Apps: Production Implementation Guide
• Database Scaling Strategies for ChatGPT Apps: Sharding & Replication
• Enterprise ChatGPT App Solutions: Security, Compliance & Scale

External References:

• Liquibase Best Practices
• Flyway Migration Strategies
• Refactoring Databases: Expand-Contract Pattern (Martin Fowler)