# Review Database Migrations in a Flask App

Yes. On each pull request that adds an Alembic revision, an agent runs the upgrade and downgrade in a sandbox, compares it to your models and comments.

[Run this on Rush](https://cloud.getrush.ai/run/review-database-migrations/flask/), in a Project that has your repository.

## What the agent does

The agent starts from the pull request branch and reads how this app migrates before it reads the new revision. In a Flask-Migrate app like `miguelgrinberg/microblog`, `requirements.txt` pins `Flask-Migrate 4.0.5`, `alembic 1.12.1`, `Flask-SQLAlchemy 3.1.1` and `SQLAlchemy 2.0.23`, and `migrations/env.py` takes its URL from `current_app.config['SQLALCHEMY_DATABASE_URI']` and its metadata from `current_app.extensions['migrate'].db.metadata`, so the revision only means something with the app importable. `config.py` falls back to `sqlite:///app.db` when `DATABASE_URL` is unset and rewrites a `postgres://` prefix to `postgresql://`, which tells the agent the same revision has to work on SQLite in development and on Postgres in production.

Then it reads the chain. `migrations/versions` holds nine revisions, from `e517276bb1c2_users_table.py` with `down_revision = None` to `834b1a697901_user_tokens.py` at the head, and the agent checks that the new file's `down_revision` points at that head and that no second head appears, which `flask db heads` confirms. It reads the existing revisions as the house style: nearly all of them wrap column and index changes in `with op.batch_alter_table(...)`, the pattern SQLite needs for anything beyond adding a column, and index names go through `batch_op.f('ix_post_timestamp')` so they match what the models declare. A new revision that calls `op.drop_column` directly, as `834b1a697901` already does in its `downgrade`, gets a comment that the downgrade depends on the SQLite version and breaks the convention the rest of the chain follows.

Next it runs the migration rather than reasoning about it. In its sandbox it installs the requirements, exports `FLASK_APP=microblog.py`, runs `flask db upgrade` from an empty database to the new head, then `flask db downgrade -1` and `flask db upgrade` again, so both directions are exercised. It runs `flask db check`, which autogenerates against `app/models.py` and reports whether the models and the migrations still agree, and compares the revision line by line with the `so.mapped_column` declarations it should reflect: a `String(140)` that became `String(280)` in the model but not in the revision, a `nullable=True` column the model marks required, an index the model declares with `index=True` that the revision never creates, or a `unique=True` added to a column that may already hold duplicates.

Last it looks at how the migration will run in production. `boot.sh`, the container entrypoint in the `Dockerfile`, loops on `flask db upgrade` and retries every five seconds until it succeeds before starting gunicorn, so a revision that fails on Postgres keeps the new container from ever serving. The agent flags data migrations that load whole tables into Python, a `NOT NULL` column added without a `server_default` on a table that has rows, and a renamed column written as a drop and an add, which loses the data. It also notes that `tests.py` builds its schema with `db.create_all()` on in-memory SQLite, so the test suite passing says nothing about the migration. Everything goes into one review comment on the pull request; the agent pushes no commits.

## Start it with one API call

`POST https://api.prix.dev/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Review the Alembic migration in this pull request for this Flask app without changing any code. Read requirements.txt, config.py, migrations/env.py, migrations/alembic.ini, every file in migrations/versions, app/models.py, tests.py, boot.sh and the Dockerfile first. Check that the new revision's down_revision is the current head and that `flask db heads` reports one head. Install the requirements, set FLASK_APP=microblog.py, run `flask db upgrade` from an empty SQLite database, then `flask db downgrade -1` and `flask db upgrade` again, and run `flask db check`; paste the commands and their output. Compare each column, index and constraint in the revision with the mapped_column declarations in app/models.py and with the batch_alter_table and batch_op.f naming the existing revisions use. Flag anything that would fail or lose data on SQLite or Postgres: a drop_column outside batch mode, a NOT NULL column added without a server_default, a rename written as drop and add, a unique index on a column that may hold duplicates, or a data migration that loads whole tables. Post one review comment on the pull request with the findings ordered by severity, each with the file and line and a concrete fix, and end with whether you would merge.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app/pull/123"
    }
  ]
}
```

1. **Get an API key.** In the console, open Settings, then API keys, and create a key for your org. Send it in the Authorization header as a Bearer token.
2. **Send the request.** POST the body in request.json to https://api.prix.dev/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
3. **Read what came back.** Follow the run on its live stream or wait for the run.completed webhook, then read the result and its trajectory.

## What comes back

What comes back is a review comment on the pull request. It opens with the commands the agent ran and their output: `flask db heads`, the `flask db upgrade` from an empty database, the `downgrade -1` and the second `upgrade`, and the result of `flask db check`. If any of them failed, the traceback is quoted and the finding at the top of the list explains it.

Below that are the findings in order of severity, each naming the revision file and line, what the model in `app/models.py` says, and the change that makes them agree, written as the `batch_alter_table` call the other revisions in `migrations/versions` use. Deployment risks come next: a migration that `boot.sh` would retry forever on Postgres, a long-running data change, a column drop that destroys data. The comment ends with a merge recommendation and the reasons for it.

A reviewer checks the findings against the revision, decides which ones block the merge, and asks the author for the fixes, or sends a follow-up message to the same session asking the agent to push a corrected revision to the branch, since the sandbox still has the app installed and the database at the new head.

## Run it on every pull request that touches migrations/versions

In the Project that has your repository, add an automation with this prompt, mark it read-only so its runs can comment but never push, and give it a GitHub trigger on `pull_request.opened` and `pull_request.synchronize` with `branch_patterns` set to `main`. Each pull request to main then starts one run with the pull request appended to the prompt as the event, and each new push restarts the review on the new commits. Only work by an owner, member or collaborator fires it; pull requests from forks never do.

The trigger cannot filter on paths, so the prompt tells the agent to stop early when the diff touches nothing under `migrations/versions`, and the automation's `max_runs_per_hour` caps how many reviews a busy day can start.

## Why run it on Rush?

A migration review on a laptop usually means reading the diff, because running `flask db upgrade` means a scratch database, the right environment variables and a willingness to wreck the local `app.db`. In a Rush sandbox the agent starts from an empty database every time, runs the upgrade and the downgrade for real, and the output of each command is in the transcript next to the finding it supports.

The review also carries the context a human reviewer has to rebuild each time: the nine revisions already in the chain, the batch-mode convention they follow, the SQLite fallback in `config.py`, and the retry loop in `boot.sh` that turns a failing migration into a container that never starts. Every pull request gets the same careful check, whether it arrives at noon or at two in the morning.

## Frequently asked questions

### Does the agent change the migration or push to the branch?

No. The automation runs read-only, so the result is a review comment and nothing else. If you want the fix applied, a follow-up message to the same session asks the agent to write the corrected revision and push it, and that run is tied to your choice, not to the trigger.

### The app runs on Postgres in production but the sandbox uses SQLite. Does that matter?

The agent runs the migration on SQLite because `config.py` falls back to it with no `DATABASE_URL`, and it reads the revision for Postgres-specific problems, such as a `NOT NULL` column added to a populated table without a `server_default` or a type change that needs a `USING` clause. If your Project sets `DATABASE_URL` to a Postgres instance the sandbox can reach, the same commands run against that instead.

### Why does it run the downgrade too?

Because `834b1a697901_user_tokens.py` shows how a downgrade can differ from its upgrade: it calls `op.drop_column` outside batch mode, which only newer SQLite builds accept and which every other revision in the chain avoids. A revision you cannot roll back is a one-way door in production, and the agent reports it before it is merged.

### What if the pull request changes app/models.py without a migration?

`flask db check` autogenerates against the models and exits non-zero when they differ from the migration head, and the agent quotes that output and names the columns involved. The comment says a revision is missing rather than guessing what it should contain.

### Will it flag every pull request, even ones with no migration?

The prompt tells the agent to read the diff first and stop with a one-line comment when nothing under `migrations/versions` changed. The automation's `max_runs_per_hour` keeps the cost of those short runs bounded.