Naive approach:
  1. Deploy new code
  2. Run migrations

Problems:
  - Old pods (still running during rolling deploy) read new schema → crash
  - Rename column → old code references old name → data loss or errors
  - Add NOT NULL column without default → existing rows violate constraint → migration fails
  - Lock entire table for ALTER → queries queue up → timeout cascade

Phase 1 — EXPAND (backward compatible):
  - Add new column as nullable (or with default)
  - Add new table
  - Old code ignores new column, new code writes both old and new

Phase 2 — MIGRATE DATA:
  - Backfill new column from old column
  - Can run as a background job or as part of the migration

Phase 3 — CONTRACT (remove old):
  - Deploy code that only uses the new column
  - Drop old column once all pods use new code
  - Old code no longer running → safe to drop

Example: rename Orders.DeliveryAddress → Orders.ShippingAddress

❌ BAD (one step, breaks rolling deploy):
  ALTER TABLE Orders RENAME COLUMN DeliveryAddress TO ShippingAddress;

✓ GOOD (three steps across three deploys):

  Deploy 1 (Expand):
    ALTER TABLE Orders ADD COLUMN ShippingAddress TEXT;
    -- New code writes both DeliveryAddress AND ShippingAddress
    -- Old code still reads DeliveryAddress → no breakage

  Deploy 2 (Migrate):
    UPDATE Orders SET ShippingAddress = DeliveryAddress WHERE ShippingAddress IS NULL;
    -- All rows now have ShippingAddress populated
    -- New code reads ShippingAddress, writes only ShippingAddress
    -- Old code still reads DeliveryAddress → still fine

  Deploy 3 (Contract):
    ALTER TABLE Orders DROP COLUMN DeliveryAddress;
    -- Old code is gone → safe to drop

// Migration: add nullable column with default — safe under rolling deploy
public partial class AddShippingAddress : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Step 1: add nullable — existing rows get NULL, no table lock on PostgreSQL
        migrationBuilder.AddColumn<string>(
            name:      "ShippingAddress",
            table:     "Orders",
            nullable:  true,
            defaultValue: null);

        // Step 2: backfill in the same migration (or separate migration for large tables)
        migrationBuilder.Sql("""
            UPDATE "Orders"
            SET "ShippingAddress" = "DeliveryAddress"
            WHERE "ShippingAddress" IS NULL
            """);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(name: "ShippingAddress", table: "Orders");
    }
}

// Application code during the transition period: write both columns
public class Order
{
    public string? DeliveryAddress  { get; set; }   // old — will be dropped later
    public string? ShippingAddress  { get; set; }   // new
}

// In the command handler:
order.DeliveryAddress = command.Address;   // keep old for backward compat
order.ShippingAddress = command.Address;   // populate new

-- PostgreSQL: adding a NOT NULL column with a default locks the whole table
-- Bad for tables with millions of rows

-- SAFE approach for large tables:
-- 1. Add nullable
ALTER TABLE Orders ADD COLUMN IsArchived BOOLEAN;

-- 2. Backfill in batches (no full table lock)
DO $$
DECLARE
  batch_size INT := 10000;
  last_id    INT := 0;
BEGIN
  LOOP
    UPDATE Orders
    SET IsArchived = FALSE
    WHERE Id > last_id AND IsArchived IS NULL
    LIMIT batch_size
    RETURNING Id INTO last_id;

    EXIT WHEN NOT FOUND;
    PERFORM pg_sleep(0.01);  -- small pause to reduce I/O pressure
  END LOOP;
END;
$$;

-- 3. Set NOT NULL once all rows are backfilled (cheap — just validates constraint)
ALTER TABLE Orders ALTER COLUMN IsArchived SET NOT NULL;
ALTER TABLE Orders ALTER COLUMN IsArchived SET DEFAULT FALSE;

// In EF Core migrations — split across two migrations
// Migration 1: add nullable, backfill
migrationBuilder.AddColumn<bool>("IsArchived", "Orders", nullable: true);
migrationBuilder.Sql("UPDATE \"Orders\" SET \"IsArchived\" = false WHERE \"IsArchived\" IS NULL");

// Migration 2 (separate deploy): make NOT NULL
migrationBuilder.AlterColumn<bool>("IsArchived", "Orders", nullable: false, defaultValue: false);

# Build a migration bundle
dotnet ef migrations bundle \
  --project src/Infrastructure \
  --startup-project src/Api \
  --output ./migrations-bundle \
  --self-contained

# Run in a Kubernetes init container or CI/CD step
./migrations-bundle --connection "Host=prod-db;Database=app;Username=app;Password=***"

# Kubernetes: run migration bundle as an init container
spec:
  initContainers:
  - name: db-migrate
    image: myapp-migrations:latest   # image containing the bundle
    command: ["./migrations-bundle"]
    env:
    - name: ConnectionStrings__Default
      valueFrom:
        secretKeyRef:
          name: db-secrets
          key: connection-string
  containers:
  - name: api
    image: myapp-api:latest
    # API starts only after migrations complete

// Program.cs — ONLY for development environments
if (app.Environment.IsDevelopment())
{
    using var scope = app.Services.CreateScope();
    var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    await db.Database.MigrateAsync();
}

// In production: use migration bundle in CI/CD or init container
// Never call MigrateAsync() in production app startup:
//   - Multiple pods start simultaneously → race condition
//   - App fails to start if migration fails → complete outage

EF Core does not support automatic rollback of data migrations.
Schema changes (ADD COLUMN, ADD TABLE) can be rolled back.
Data migrations (UPDATE, backfill) cannot — data is already mutated.

Safe rollback checklist:
  ✓ The Down() method of every migration must be correct and tested
  ✓ Phase 1 (expand) migrations are always safe to roll back (just drop the new column)
  ✓ Phase 3 (contract) migrations are NOT safe to roll back once data is dropped
  ✓ Before Phase 3: take a DB backup

Script to generate rollback SQL:
  dotnet ef migrations script   \
    --idempotent \
    --output rollback.sql

// Mark a migration as applied without running it (emergency workaround)
// Use when you've manually applied SQL and EF Core doesn't know about it
dotnet ef database update --target-migration <MigrationName>

// List all applied migrations
dotnet ef migrations list

// Creating an index locks the table in PostgreSQL — use CONCURRENTLY
// EF Core does not support CONCURRENTLY natively — use raw SQL

migrationBuilder.Sql(
    "CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_customer_id ON \"Orders\" (\"CustomerId\")",
    suppressTransaction: true);   // CONCURRENTLY cannot run inside a transaction

Recommended pipeline:

  PR merge to main:
    1. Build migration bundle
    2. Run bundle against a staging DB (validates migration succeeds)
    3. Run application tests against migrated staging DB

  Production deploy:
    1. Run migration bundle against prod DB (init container or deploy job)
    2. Wait for migrations to complete
    3. Deploy new application pods (rolling update)

  Rollback:
    1. Stop rolling deploy
    2. Run rollback.sql generated by 'ef migrations script'
    3. Redeploy old application version

  Never:
    - Run migrations from application startup in production
    - Run Phase 3 (DROP) migrations without a backup
    - Rename columns in a single migration