What the agent does
The agent reads the migration chain and the test setup before it writes a test, because a migration test has to share the suite's database without breaking the suite. In a backend laid out like fastapi/full-stack-fastapi-template, backend/alembic.ini points script_location at app/alembic, and app/alembic/env.py sets target_metadata = SQLModel.metadata from app.models, reads the URL from settings.DATABASE_URL, and passes compare_type=True in both offline and online mode. app/alembic/versions/ holds five revisions in a single chain: e2412789c190 creates user and item with integer ids and a unique index on user.email, 9c0a54914c78 adds string lengths, d98dd8ec85a3 replaces the integer ids with UUIDs, 1a31ce608336 makes item.owner_id non-null and recreates its foreign key with ondelete='CASCADE', and fe56fa70289e adds a nullable created_at to both tables. The models in app/models.py are User and Item, both table=True, with UUID primary keys, created_at, and a cascade_delete=True relationship. tests/conftest.py has a session-scoped db fixture that calls init_db and deletes Item and User rows at the end, and scripts/test.sh runs coverage run -m pytest tests/ followed by coverage report; test-backend.yml brings up postgres:18 with docker compose up -d --wait db mailpit, runs scripts/prestart.sh, which is alembic upgrade head then initial_data.py, runs the tests, and fails under 90% coverage. pyproject.toml excludes alembic from ruff and mypy, pins alembic>=1.19.1 and sqlmodel>=0.0.46, and requires Python 3.14. Nothing in tests/ touches a migration.
The agent writes tests/alembic/test_migrations.py around a module-scoped fixture that creates its own throwaway database on the same Postgres server, CREATE DATABASE from a connection to the configured one, so the suite's database, already at head after prestart.sh, is never downgraded under the other tests. It builds an Alembic Config from alembic.ini and sets sqlalchemy.url to the scratch database, then uses alembic.command rather than a subprocess so failures carry a Python traceback. The first test runs upgrade head from an empty database and asserts ScriptDirectory.get_current_head() equals the revision stamped in alembic_version, which also proves there is exactly one head. The second runs downgrade base and asserts no table but alembic_version remains, which catches a downgrade that drops a constraint under a name it never created. The third runs upgrade head again, then inserts one User and one Item through the models and deletes the user, asserting the item is gone, which is what 1a31ce608336's ondelete='CASCADE' promises and the models' cascade_delete=True assumes.
The fourth test is the one that matters most as the models grow: with the scratch database at head, it calls Alembic's autogenerate comparison, compare_metadata from alembic.autogenerate against SQLModel.metadata, and asserts the result is empty, so a model change without a revision fails the suite with the missing operation named, the same check alembic check makes from the command line but inside pytest with the suite's own output. Because env.py uses compare_type=True, the test passes the same flag, so a type change counts. A fifth test walks the chain one revision at a time, upgrade +1 from base to head, so a revision that only works when applied together with its neighbour is caught. The fixture drops the scratch database at the end, and the tests are marked so coverage reports them without counting the migration files, which pyproject.toml already excludes from linting.
To check it, the agent installs Docker in its sandbox first (sudo apt-get install -y docker.io, then starts dockerd), starts the postgres:18 container that docker-compose.yml describes with docker run and the same environment, since the docker compose plugin is not in that package, and runs uv run bash scripts/prestart.sh as the workflow does, then uv run bash scripts/tests-start.sh so the new tests run with the rest and the coverage threshold is still met, and uv run bash scripts/lint.sh so mypy, ty, ruff check and ruff format accept the new file. It then adds a temporary column to Item, runs the suite again to show the drift test fails naming the column, and reverts it.
Start it with one API call
POST https://api.phoenix.so/o/{org}/sessions with this body:
{
"agent": "claude",
"input": "Write tests for the Alembic migrations of this backend. Read backend/alembic.ini, backend/app/alembic/env.py, every file under backend/app/alembic/versions, backend/app/models.py, backend/app/core/db.py, backend/tests/conftest.py, backend/scripts/prestart.sh, backend/scripts/test.sh, backend/scripts/lint.sh, backend/pyproject.toml and .github/workflows/test-backend.yml first. Add backend/tests/alembic/test_migrations.py with a module-scoped fixture that creates and drops a scratch database on the configured Postgres server so the suite's database is never downgraded. Using alembic.command, test that upgrade head from empty lands on the single head, that downgrade base leaves only alembic_version, that upgrade head again works and a cascade delete from user to item behaves as the models promise, that compare_metadata against SQLModel.metadata with compare_type is empty at head, and that the chain applies one revision at a time. Install Docker, start the Postgres container the compose file describes, run prestart.sh, tests-start.sh and lint.sh, prove the drift test fails on a temporary model column and revert it, and open a pull request with the output.",
"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
The pull request adds backend/tests/alembic/__init__.py and backend/tests/alembic/test_migrations.py and nothing else. Its description pastes the pytest output with the five new tests, the coverage report line showing the threshold still met, the lint script's output, and the failing run from the temporary column with the drift test's message naming it.
The Test Backend workflow runs on the pull request with the new tests inside the same tests-start.sh step, so a reviewer sees them pass on a fresh postgres:18 after prestart.sh, and the pre-commit workflow shows ruff, mypy and ty accept the file. The run's record keeps the CREATE DATABASE and DROP DATABASE statements the fixture issued.
Before merging, a reviewer decides whether the scratch database should also be used for the revision-by-revision walk or whether that test may stay on the same database as the others, whether the cascade test belongs here or with the CRUD tests, and whether to run the migration tests in their own workflow step so their failure is easy to tell from the API tests.
Run it on every Sunday night
Create an automation in the Project with a schedule trigger, a cron such as 0 2 * * 0 with your timezone, and a prompt that asks for tests on whatever the week added under app/alembic/versions and the models it touched. Each run opens one pull request on its own branch, so the first revision of the week that lacks a downgrade or drifts from the models is found before Monday rather than during a deploy.
A run that finds nothing new under versions/ and no model change ends with a short note and no pull request. A retried slot is deduped, so one week's schedule cannot open two pull requests, and the automation has its own max_runs_per_hour cap.
The use case this job follows, with its automation and trigger: Write the missing tests.
Why run it on Rush?
Migrations are the one part of a backend that runs once against production and never again locally, so a downgrade that was never tried or a model that drifted from its last revision stays invisible until it is expensive. The agent brings up the same Postgres the workflow uses, in its own sandbox, and runs the chain up, down and up again with the output kept.
The pytest run, the coverage line and the deliberate failure on a temporary column stay with the run and are pasted into the pull request, so the review is reading evidence rather than trusting a description. Several services can get the same tests at once, each against its own chain.
Frequently asked questions
Will the tests interfere with the existing suite's database?
No. The fixture creates a scratch database on the same server, points Alembic's sqlalchemy.url at it, and drops it afterwards, so the database that prestart.sh migrated and the session-scoped db fixture uses is never touched. The existing tests run exactly as before.
How is this different from running alembic check in CI?
The drift test calls the same comparison, but inside pytest, with the suite's output and the suite's database lifecycle, and it sits beside four tests that alembic check does not make: the downgrade, the re-upgrade, the cascade behaviour and the revision-by-revision walk. A team with a drift step in CI keeps it; the tests add the rest.
What if a downgrade is not meant to be reversible?
Then the test says which revision refuses, and the pull request asks you whether to mark that revision as the floor of the downgrade test. The agent does not edit any revision; it reports what the chain does today.
Does this slow the suite down?
By a few upgrades and downgrades of a five-revision chain on a local Postgres, which is small next to the API tests that already run. The revision-by-revision walk is the slowest of the five, and the pull request names it if you would rather keep it out of the default run.