Database Migrations

This guide explains how to use the migration system in Pivot to run one-time data transformations or backfills across environments.

Overview

The migration system provides:

• Idempotent execution - Migrations can be run multiple times safely; already-completed migrations are automatically skipped
• Status tracking - All migration runs are recorded in Firebase at /migrations/{migrationName}
• Failure handling - Execution stops on first failure to prevent cascading issues
• Environment isolation - Migrations run against a specific environment (development, staging, or production)

Environments and Deployment Policy

We have three environments:

Environment	Database URL	Manual Runs?
Development	Local or dev project	Yes - for testing
Staging	pivot-not-production-project	No - automated only
Production	pivot-inc	No - automated only

Important Rules

1. Development - You can run migrations manually to test your changes
2. Staging - Migrations are run automatically via CI/CD when code is merged. Never run manually.
3. Production - Migrations are run automatically via CI/CD after staging succeeds. Never run manually.

This ensures:

• All migrations are tested in development first
• Staging and production are always in sync with the codebase
• No accidental manual changes to shared environments
• Full audit trail through CI/CD logs

Architecture

functions/migrations/
├── migration.interface.ts      # TypeScript interfaces
├── run-migrations.ts           # Migration runner script
└── 001-link-stripe-subscriptions.ts  # Example migration

Migration Interface

Every migration must implement this interface:

export interface Migration {
  /** Unique identifier for this migration (used as key in /migrations/{name}) */
  name: string

  /** Human-readable description of what this migration does */
  description: string

  /** The migration logic - should be idempotent */
  run: () => Promise<void>
}

Migration Record

The runner stores execution results in Firebase:

export interface MigrationRecord {
  status: 'completed' | 'failed'
  runAt: number      // Unix timestamp
  duration: number   // Execution time in ms
  error?: string     // Error message if failed
}

Creating a New Migration

Step 1: Create the Migration File

Create a new file in functions/migrations/ with a numbered prefix:

// functions/migrations/002-your-migration-name.ts
import * as admin from 'firebase-admin'
import { Migration } from './migration.interface'

const PREFIX = '[002-your-migration-name]'

export const yourMigration: Migration = {
  name: 'your-migration-name',
  description: 'Brief description of what this migration does',

  run: async () => {
    console.log(`${PREFIX} Starting migration...`)

    // Your migration logic here
    // - Fetch data from Firebase or external APIs
    // - Transform the data
    // - Write updates back to Firebase

    console.log(`${PREFIX} Migration completed`)
  }
}

Step 2: Register the Migration

Add your migration to run-migrations.ts:

// Import all migrations here (order matters!)
import { linkStripeSubscriptionMigration } from './001-link-stripe-subscriptions'
import { yourMigration } from './002-your-migration-name'

const migrations: Migration[] = [
  linkStripeSubscriptionMigration,
  yourMigration
  // Add new migrations here in order
]

Step 3: Configure Environment Variables

Create a .env file in functions/ (or update the existing one):

# For staging
RTDB_URL=https://pivot-not-production-project.firebaseio.com

# For production
# RTDB_URL=https://pivot-inc.firebaseio.com

Step 4: Run the Migration

cd pivot/functions

# Set the service account credentials
export GOOGLE_APPLICATION_CREDENTIALS="/path/to/service-account.json"

# Run all pending migrations
npx ts-node ./migrations/run-migrations.ts

Example: Stripe Subscription Migration

The 001-link-stripe-subscriptions migration demonstrates best practices:

export const linkStripeSubscriptionMigration: Migration = {
  name: 'link-stripe-subscriptions',
  description: 'Links existing Stripe subscriptions to Pivot companies by matching on name + email',

  run: async () => {
    console.log(`${PREFIX} Starting synchronization...`)

    // 1. Fetch data from external API
    const subscriptions = await fetchStripeSubscriptions()
    if (!subscriptions.length) {
      console.log(`${PREFIX} No active subscriptions found`)
      return
    }

    // 2. Fetch data from Firebase
    const companies = (await admin.database().ref('Companies').once('value')).val()
    if (!companies) {
      console.log(`${PREFIX} No companies found`)
      return
    }

    // 3. Match and prepare updates
    const updates: { [key: string]: any } = {}
    let matchedCount = 0

    for (const subscription of subscriptions) {
      const customer = subscription.customer as Stripe.Customer
      // ... matching logic ...
      if (matchedCompany) {
        updates[`Companies/${companyId}/stripeSubscriptionId`] = subscription.id
        updates[`Companies/${companyId}/stripeCustomerId`] = customer.id
        matchedCount++
      }
    }

    // 4. Apply updates in a single batch
    if (Object.keys(updates).length) {
      await admin.database().ref().update(updates)
      console.log(`${PREFIX} Updated ${matchedCount} companies`)
    }
  }
}

Key Patterns

1. Early exits - Return early if there's nothing to process
2. Batch updates - Collect all updates and apply them in a single ref().update() call
3. Logging - Use a prefix for all log messages to identify the migration
4. Statistics - Track and report counts (matched, skipped, failed)

Running Migrations in Development

Use this to test your migration locally before pushing code.

Setup

The development environment credentials are stored in pivot-devops:

File	Path
Service Account	pivot-devops/github/secrets/pivot-dev-firebase-sa.json
Environment Variables	pivot-devops/github/secrets/env.dev

Development database: https://pivot-dev-59310-default-rtdb.firebaseio.com

Running the Migration

cd pivot/functions

# Copy the dev environment file (contains RTDB_URL and other config)
cp ../pivot-devops/github/secrets/env.dev .env

# Set the service account credentials
export GOOGLE_APPLICATION_CREDENTIALS="../pivot-devops/github/secrets/pivot-dev-firebase-sa.json"

# Run migrations
npx ts-node ./migrations/run-migrations.ts

Staging and Production Deployment

Migrations for staging and production are handled automatically by CI/CD:

1. Merge to main - Your migration code is merged via PR
2. CI/CD triggers - The pipeline detects new migrations
3. Staging runs first - Migrations execute against staging
4. Production follows - After staging succeeds, production runs automatically

Never run migrations manually against staging or production. This ensures:

• Consistent, reproducible deployments
• Proper audit trails
• No risk of running untested code

Checking Migration Status

Migration status is stored in Firebase at /migrations/{migrationName}:

{
  "migrations": {
    "link-stripe-subscriptions": {
      "status": "completed",
      "runAt": 1736286943336,
      "duration": 8150
    }
  }
}

You can check this directly in the Firebase Console or via a script.

Best Practices

1. Always test in staging first - Run migrations in staging before production
2. Make migrations idempotent - They should produce the same result if run multiple times
3. Use descriptive names - The name field becomes the Firebase key
4. Log progress - Include detailed logging for debugging
5. Handle edge cases - Check for null/missing data before processing
6. Batch database writes - Use ref().update() with multiple paths instead of individual writes
7. Keep migrations small - Each migration should do one thing well

Troubleshooting

Migration Already Completed

If a migration shows as completed but you need to re-run it:

1. Delete the migration record from Firebase: /migrations/{migrationName}
2. Re-run the migration runner

Migration Failed

If a migration fails:

1. Check the error in Firebase: /migrations/{migrationName}/error
2. Fix the issue in your migration code
3. Delete the failed record from Firebase
4. Re-run the migration runner

Database URL Issues

If you see "Firebase error. Please ensure that you have the URL configured correctly":

Environment	Database URL
Development	https://pivot-dev-59310-default-rtdb.firebaseio.com
Staging	https://pivot-not-production-project.firebaseio.com
Production	https://pivot-inc.firebaseio.com

Note: Development uses -default-rtdb but staging and production do not.