Step 2 · Migrate and Apply

Goal

Write a safe, tested, rollback-ready migration and apply it without downtime or data loss.

Instructions

You are in workflow step 2 of the data-cycle. The data model design is approved. Your job is to write the migration, test it, and apply it safely to production.

Tasks to Perform

1. Write the Migration File

Follow the naming convention already used in the project:

# Find existing migration files and understand the naming convention
find . -path "*/migrations/*" | grep -v node_modules | grep -v vendor | sort | tail -10

Write the migration:

# Rails
rails generate migration AddCustomerAddresses

# Laravel
php artisan make:migration create_customer_addresses_table

# Alembic (Python/SQLAlchemy)
alembic revision --autogenerate -m "add_customer_addresses"

# Flyway: create V{version}__{description}.sql
# Liquibase: add changeset to changelog.xml

Include both up and down (rollback) in every migration:

// Laravel example
public function up(): void
{
    Schema::create('customer_addresses', function (Blueprint $table) {
        $table->id();
        $table->foreignId('customer_id')->constrained()->cascadeOnDelete();
        $table->string('line_1');
        $table->string('line_2')->nullable();
        $table->string('city', 100);
        $table->char('country', 2);
        $table->string('postcode', 20);
        $table->boolean('is_default')->default(false);
        $table->timestamps();

        $table->index('customer_id');
        // Partial unique index (one default per customer) — add via raw SQL
    });

    DB::statement("
        CREATE UNIQUE INDEX customer_addresses_default_unique
        ON customer_addresses(customer_id)
        WHERE is_default = TRUE
    ");
}

public function down(): void
{
    Schema::dropIfExists('customer_addresses');
}

2. Test on Staging with Production-Scale Data

Never run a migration on production data you haven't first tested at production scale:

# Copy production data to staging (anonymise PII first)
pg_dump production_db --no-owner --no-privileges | psql staging_db

# Time the migration on staging
\timing        # in psql
-- run migration here

# For automated timing:
time php artisan migrate --path=path/to/migration.php 2>&1

# If the migration takes > 5 seconds on staging data, plan accordingly
# Consider: CONCURRENTLY for indexes, batching for DML, maintenance window

3. Write Rollback Steps

Before proceeding, document:

## Rollback Plan

**Migration:** XXXX_create_customer_addresses_table

**Time to run on production data:** < 100ms (table create)

**Rollback:**
```sql
DROP TABLE IF EXISTS customer_addresses;

Or via framework:

php artisan migrate:rollback --step=1

Risk level: Low — new table, no changes to existing tables, application code is additive

**4. Apply to Production**

```bash
# Before migrating:
#   1. Confirm staging test passed
#   2. Confirm rollback plan is ready
#   3. Prefer off-peak traffic times (even for "safe" migrations)
#   4. Have two people on the call for SEV-affecting migrations

# Apply
php artisan migrate          # Laravel
rails db:migrate             # Rails
alembic upgrade head         # Python/Alembic
flyway migrate               # Flyway

# Verify immediately after
php artisan migrate:status   # or framework equivalent

5. For Dangerous Migrations (large tables, column drops, renames)

Follow the expand–contract pattern:

Phase 1:  Add new column nullable             ← this migration
Phase 2:  Deploy code writing to both columns ← code deploy
Phase 3:  Backfill existing rows              ← data migration
Phase 4:  Add NOT NULL + remove old column    ← later migration

For indexes on large tables:

-- Always CONCURRENTLY on tables with > 1M rows
-- Cannot be run inside a transaction block
CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id);

For data backfill (never a single UPDATE on millions of rows):

# See the database-migrations skill for the batched backfill pattern
# skills/database-migrations.md §3

Exit Criteria

• [ ] Migration file written with both up and down methods
• [ ] Migration tested on staging with production-scale data
• [ ] Timing confirmed — runbook updated if it takes > 10 seconds
• [ ] Rollback plan documented and tested
• [ ] Migration applied to production
• [ ] migrate:status or equivalent confirms migration applied cleanly
• [ ] No error spikes in monitoring for 5 minutes post-migration

Next Step

→ Proceed to Step 3 · Validate and Monitor