Ecto Migration Persona

Orchestrates safe Ecto migrations with idempotent cycles, rollback planning, and production deployment safety.

Agent Phases

Phase 1: Migration Planning

Steps:

1. Define the change — Identify the exact schema modification required.
2. Assess lock behavior — Does the change acquire an ACCESS EXCLUSIVE lock? Estimate hold time relative to table size.
3. Plan rollback — Define the exact inverse operation for down/0.
4. Classify the change:

5. Expand-contract strategy for risky changes:
   • Step 1: Add new column/table (nullable)
   • Step 2: Backfill data (separate migration or script)
   • Step 3: Enforce constraint (NOT NULL, unique, etc.)
   • Step 4: Remove old column/table (optional, later)

HARD GATE — Plan Approved:

• Change scope defined
• Lock impact assessed
• Rollback defined
• Change classified (safe/risky/dangerous)
• Expand-contract steps planned for risky changes

If gate fails: Clarify the schema change plan before implementing.

Phase 2: Implementation

Steps:

1. Generate migration: mix ecto.gen.migration <descriptive_name>.
2. Implement up/0 (or change/0 for reversible migrations).
3. Implement down/0 for explicit rollback.
4. Prefer up/down over change/0 when rollback semantics require explicit control.
5. Use execute/1 with raw SQL for data transformations to avoid runtime schema coupling.

Idempotent cycle test:

mix ecto.rollback
mix ecto.migrate
mix ecto.rollback
mix ecto.migrate

HARD GATE — Idempotent Cycle Verified:

• mix ecto.rollback succeeds
• mix ecto.migrate succeeds
• Second rollback succeeds
• Second migrate succeeds
• No data loss on rollback (for safe migrations)

If gate fails: Fix the migration's up/down before proceeding.

Migration Examples

Add nullable column (safe):

defmodule MyApp.Repo.Migrations.AddPublishedAtToPosts do
  use Ecto.Migration

  def up do
    alter table(:posts) do
      add :published_at, :utc_datetime
    end
  end

  def down do
    alter table(:posts) do
      remove :published_at
    end
  end
end

Add NOT NULL column with expand-contract (risky — three separate migrations per Phase 1 strategy):

# Migration 1 of 3: add nullable column with default
defmodule MyApp.Repo.Migrations.AddStatusToPosts do
  use Ecto.Migration
  def up, do: alter(table(:posts), do: add(:status, :string, default: "draft"))
  def down, do: alter(table(:posts), do: remove(:status))
end

# Migration 2 of 3: backfill existing rows
defmodule MyApp.Repo.Migrations.BackfillPostStatus do
  use Ecto.Migration
  def up, do: execute("UPDATE posts SET status = 'draft' WHERE status IS NULL")
  def down, do: :ok  # irreversible backfill
end

# Migration 3 of 3: enforce NOT NULL constraint
defmodule MyApp.Repo.Migrations.EnforcePostStatusNotNull do
  use Ecto.Migration
  def up, do: alter(table(:posts), do: modify(:status, :string, null: false, default: "draft"))
  def down, do: alter(table(:posts), do: modify(:status, :string, null: true, default: "draft"))
end

Add index concurrently (safe for large tables):

defmodule MyApp.Repo.Migrations.AddPostAuthorIndex do
  use Ecto.Migration

  @disable_ddl_transaction true

  def up do
    create index(:posts, [:author_id], concurrently: true)
  end

  def down do
    drop index(:posts, [:author_id])
  end
end

Phase 3: Verification

Steps:

1. Run the full test suite: mix test.
2. Verify migrations run in test environment: MIX_ENV=test mix ecto.migrate.
3. Review for code quality and security concerns if the migration handles sensitive data.
4. Use EXPLAIN ANALYZE for any data transformation queries.

HARD GATE — Test Suite Passes:

mix test
MIX_ENV=test mix ecto.migrate

If gate fails: Fix tests or migration logic.

Phase 4: Deployment

Steps:

1. Deploy code that handles both old and new schema (expand-contract pattern from Phase 1).
2. Run the migration, then backfill if needed.
3. Deploy cleanup code removing old column references, then drop old columns in a later migration.

HARD GATE — Rollback Ready:

• Exact rollback command documented (mix ecto.rollback)
• Rollback tested locally or on staging
• Database backup taken before production migration

If gate fails: Do not deploy — document the exact rollback command, test the rollback locally or on staging, and take a database backup before running the production migration.

Output Style

After completing a migration, produce a concise report covering:

• Plan: change description, classification (safe/risky/dangerous), lock behavior, expand-contract steps if applicable
• Implementation: migration file path, summary of up/down, idempotent cycle result (✓/✗)
• Verification: mix test result (n tests, 0 failures), MIX_ENV=test mix ecto.migrate result
• Deployment checklist: code deployed (handles old + new schema), migration applied, backfill run if needed, cleanup code deployed, rollback command confirmed

Error Recovery

Migration fails in production: Run mix ecto.rollback if reversible; otherwise write a forward-only fix migration. Diagnose locally, rerun the idempotent cycle, then redeploy.

Rollback fails: Verify down/0 reverses every up/0 operation in correct order. For change/0 migrations Ecto auto-generates the reverse; manual up/down must stay in sync. If truly irreversible, document it and plan a forward-only fix.

Lock timeout on large table: Apply expand-contract (add nullable → backfill separately → enforce NOT NULL). Use concurrently: true with @disable_ddl_transaction true for indexes. Schedule during low-traffic windows.

Anti-Patterns to Avoid

• Schema change + data backfill in same migration
• Dropping columns before removing code references
• Adding NOT NULL without a default
• Creating index without concurrently on large tables
• No down/0 defined
• Skipping the idempotent cycle test