Phoenix application code and Ecto migration rollback: keeping schema and code in sync after schema changes

Answer

Enforce reversible migrations, add a CI check that validates the schema against the code after a rollback, and treat dropped columns/tables as either compensated or blocked until the dependent code is removed.

1. Policy on reversibility

Adopt a rule that every migration must be reversible. Implement it with change/0 whenever possible; if a migration needs custom SQL or irreversible operations, provide an explicit up/0/down/0 pair that exactly inverses each step. Log any migration that cannot be expressed this way as a policy violation and block its merge.

2. Automated CI check

Add a job that:

1. Checks out the code at the target commit.
2. Loads the committed structure.sql into a temporary database (e.g., PostgreSQL Docker container).
3. Runs the application’s test suite against that database.
4. Executes a custom Mix task that iterates over all schemas and calls Ecto.Schema.field_info/2 for each field; any :error causes the task to exit non‑zero, indicating missing columns or tables.

If any step fails, the build is rejected.

3. Handling a migration that drops a column/table still referenced

Two options:

• Compensating migration: Before dropping, create a separate migration that re‑adds the column/table with a safe default (or nil) and a note that it is only for rollback safety. The original drop migration remains reversible.
• Irreversible migration with guard: Mark the migration as irreversible, add a guard in the down/0 function that raises if the dependent schema/module is still present, and document a manual restore procedure (e.g., backup restore or manual SQL).

Explanation (likely)

The team is unsure whether to treat all migrations as reversible or to allow irreversible steps and rely on manual recovery. Enforcing reversibility eliminates ambiguity and lets automated tooling detect mismatches early.

Confirmed facts

• Ecto’s change/0 can automatically reverse only a subset of schema operations; custom SQL via execute/1 must be manually inverted.
• Dropping columns or tables entails data loss; without a compensating step the rolled‑back schema will miss expected fields, causing runtime errors.
• Rollback safety depends on the database’s support for transactional DDL (PostgreSQL supports it; MySQL does not for many DDL ops) and on the Ecto version (3.x provides more reversal helpers than 2.x).

Steps needed for this case

1. Declare the reversibility policy in the project’s contribution guide and enforce it via a CI lint step that runs mix ecto.migrator.is_reversible?/1 on new migrations.
2. Implement the CI validation job described above; it will catch schema/code drift after any mix ecto.rollback.
3. When a migration must drop a column/table, either add a compensating migration that re‑adds it with a default, or make the migration irreversible with a runtime guard and documented manual restore.

Verification

After running mix ecto.rollback VERSION=n locally:

1. Run mix ecto.dump and diff the output against the committed structure.sql; zero differences confirm schema sync.
2. Run the full test suite; any failure related to missing associations or fields indicates a mismatch.
3. Execute the custom Mix task that checks Ecto.Schema.field_info/2 for all schema fields; a non‑zero exit signals missing columns.