What the agent does
The agent reads the migration history before touching it. In a FastAPI project laid out like fastapi/full-stack-fastapi-template, backend/app/alembic/versions/ holds five revisions in a single chain: e2412789c190 creates user and item with integer ids and the ix_user_email index, 9c0a54914c78 sets a length of 255 on four string columns, d98dd8ec85a3 replaces every integer id with a UUID by adding new_id columns, filling them with uuid_generate_v4() and swapping the primary and foreign keys, 1a31ce608336 makes item.owner_id non-nullable with ondelete='CASCADE', and fe56fa70289e adds created_at to both tables. Every file has a downgrade, and the UUID one is the longest: it recreates integer columns, creates sequences with CREATE SEQUENCE IF NOT EXISTS, and rebuilds item.owner_id from the old user ids. Nothing runs any of those downgrades today. scripts/prestart.sh runs alembic upgrade head, the Test Backend workflow runs that against the compose db and then scripts/tests-start.sh, and pyproject.toml excludes alembic from ruff and mypy, so a downgrade that fails is found the day someone needs it.
The tests need their own database, because backend/tests/conftest.py keeps a session-scoped Session open on the app database and deletes Item and User rows at the end, and a downgrade to base drops those tables under it. The agent connects with psycopg to the server named in DATABASE_URL, creates app_migrations if it does not exist, and builds an Alembic Config from backend/alembic.ini with sqlalchemy.url set to that database. env.py ignores that option today, since get_url() returns settings.DATABASE_URL, so the agent changes it to prefer config.get_main_option("sqlalchemy.url") when one is set and fall back to the settings otherwise, which leaves prestart.sh exactly as it was.
The stairway test is one parametrised function. ScriptDirectory.from_config(cfg).walk_revisions() lists the revisions newest first; reversed, each one becomes a case that runs command.upgrade(cfg, rev.revision), command.downgrade(cfg, rev.down_revision or "base") and command.upgrade(cfg, rev.revision) again, so a downgrade that leaves a constraint, index or sequence behind fails on the step that follows. A second test goes to head, inserts one user and one item with the SQLModel classes from backend/app/models.py, downgrades two steps to d98dd8ec85a3, upgrades back and asserts that the item still points at the user, which is the part of the UUID migration's downgrade most likely to rot. A third asserts that after upgrade head the schema matches the models, so a model change without a revision fails here too.
The new module lives at backend/tests/alembic/test_migrations.py and runs with the rest of the suite: scripts/test.sh is coverage run -m pytest tests/, and [tool.coverage.run] sets source = ["app"], so test files do not lower the coverage the workflow checks with --fail-under=90. The agent runs the suite against the compose database the way the workflow does, with docker compose up -d --wait db mailpit after installing Docker in the sandbox with sudo apt-get install -y docker.io and the Compose v2 plugin, which that package does not include, and starting dockerd, quotes the per-revision results, and leaves the five migration files untouched unless a downgrade fails, in which case the fix is its own commit with the failing output above it.
Start it with one API call
POST https://api.phoenix.so/o/{org}/sessions with this body:
{
"agent": "claude",
"input": "Write rollback tests for the Alembic migrations. Read backend/alembic.ini, backend/app/alembic/env.py, every file under backend/app/alembic/versions/, backend/tests/conftest.py, backend/scripts/prestart.sh, backend/scripts/test.sh and .github/workflows/test-backend.yml first. Make env.py honour sqlalchemy.url from the Alembic config when it is set, falling back to settings.DATABASE_URL. Add backend/tests/alembic/test_migrations.py that creates a separate database on the same server, runs upgrade, downgrade and upgrade for every revision in order, round-trips one user and one item through the UUID migration's downgrade, and checks the head schema against the models. Run scripts/test.sh against the compose database, quote the results, fix any downgrade that fails in its own commit, and open a pull request.",
"resources": [
{
"url": "https://github.com/your-org/your-app"
}
]
}- 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.
- Send the request. POST the body in request.json to https://api.phoenix.so/o/{org}/sessions, with your repository as the resources URL. The run starts in its own cloud sandbox.
- 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 pull request that adds backend/tests/alembic/__init__.py and backend/tests/alembic/test_migrations.py and makes one small change to backend/app/alembic/env.py. The migration files are unchanged unless a downgrade failed, and then that fix is a separate commit whose message quotes the error.
The description holds the pytest output with one line per revision for the stairway cases, the round-trip test, the schema check, and the coverage table scripts/test.sh prints, plus a note on where the throwaway database comes from and that it is dropped and recreated on each run. The repository's Test Backend workflow runs the new module on the branch with everything else.
Before merging, a reviewer decides whether the d98dd8ec85a3 downgrade is something the team would ever run in production, given that it reassigns integer ids from fresh sequences rather than restoring the originals, and whether that migration should say so in its docstring. The agent names it and leaves the file alone.
Run it on every nightly
In the Project that holds the repository, create an automation with this page's prompt generalised to any project that has an alembic/versions directory and a schedule trigger with every set to 1d and the team's timezone. Each night's run reads the revisions as they are that day, so a migration merged in the afternoon gets its first downgrade run before anyone depends on it, and a run whose stairway passes with nothing new to add opens no pull request and says so in its answer.
The automation's notify_each_run can stay off, so the nightly results arrive as one Slack message a day that each run updates, while a failed run still arrives on its own. A migration whose downgrade breaks shows up the next morning with the failing revision named, rather than during an incident.
The use case this job follows, with its automation and trigger: Write the missing tests.
Why run it on Rush?
A rollback test only means something against the same Postgres the app uses, with uuid-ossp, sequences and ondelete behaviour exactly as the migrations assume, and it has to run on a database no other test holds open. The sandbox brings up the compose database, creates the throwaway one, runs every revision both ways and keeps the output, which is more than a laptop run usually records.
The run's record shows each alembic command and what Postgres said back, so a reviewer sees which step of which revision failed rather than a summary that the downgrades work. Across several services on this template the same request runs at once, one sandbox each, and each pull request carries that service's own revision chain.
Frequently asked questions
Why a separate database rather than the one the other tests use?
backend/tests/conftest.py opens a session-scoped Session on the app database and the db fixture is autouse, so dropping user and item during a downgrade would break every other module. A second database on the same server costs nothing and is created by the test module itself.
Does this change how migrations run in production?
No. scripts/prestart.sh still runs alembic upgrade head with no sqlalchemy.url set, so env.py falls through to settings.DATABASE_URL as before. The config option is read only when a caller sets it, which only the tests do.
What if a downgrade genuinely cannot be reversed?
Then the test says which revision and which statement, and the team chooses between fixing the downgrade, or marking that revision as forward-only by raising in downgrade with a message and skipping it in the stairway with a listed exception. The agent takes the first path when the fix is mechanical and otherwise asks in the pull request.
How long does the suite take now?
Each revision runs three Alembic commands on empty tables, so the module adds a few seconds against a local Postgres. The agent quotes the pytest durations for the new module so the reviewer sees the real cost on this schema.