All jobs

Add Constraint Naming Conventions to Alembic Migrations

Yes. An agent adds a SQLAlchemy naming convention, pins the names old migrations relied on, renames live constraints in a new revision, and opens a PR.

Run this on RushOpens the console with this prompt ready to send, in a Project that has your repository.

What the agent does

The agent reads the migration history before the models, because the history shows which names the database really has. In a backend like fastapi/full-stack-fastapi-template, backend/app/alembic/env.py sets target_metadata = SQLModel.metadata and nothing gives that metadata a naming convention. The first revision, e2412789c190, creates user and item with unnamed sa.PrimaryKeyConstraint("id") and an unnamed sa.ForeignKeyConstraint, and an index named through op.f("ix_user_email"). Revision d98dd8ec85a3 then drops item_owner_id_fkey, a name nobody wrote: it is what PostgreSQL calls an unnamed foreign key. It recreates user_pkey, item_pkey and item_owner_id_fkey by hand. Revision 1a31ce608336 drops that foreign key and recreates it with op.create_foreign_key(None, 'item', 'user', ...), and its downgrade drops item_owner_id_fkey again, trusting PostgreSQL to have chosen the same name. The chain ends at fe56fa70289e.

Adding the convention is one assignment, and the agent places it where it takes effect: SQLModel.metadata.naming_convention is set at the top of backend/app/models.py, before the first table=True class, to the five-key dictionary Alembic's documentation gives for ix, uq, ck, fk and pk. The harder part follows from how Alembic applies it. The documentation says the convention reaches every operation through target_metadata and names any constraint that is otherwise unnamed. So once the convention exists, replaying the old revisions on an empty database would create pk_user and fk_item_owner_id_user in the first revision, and d98dd8ec85a3 would then fail trying to drop item_owner_id_fkey. The agent closes that gap by writing the historical names into the old revisions: the three unnamed constraints in e2412789c190 and the None in 1a31ce608336 get the PostgreSQL names, wrapped in op.f() so the convention leaves them alone. An existing database is unaffected, since those revisions have already run there.

A new revision on top of fe56fa70289e then moves the live names to the convention. Alembic has no rename-constraint operation, so it uses op.execute with ALTER TABLE ... RENAME CONSTRAINT for user_pkey, item_pkey and item_owner_id_fkey, and the downgrade renames them back. ix_user_email already matches the ix pattern and stays. This revision is written by hand, and the agent says why: Alembic's documentation lists anonymously named constraints among the things autogenerate cannot detect, so no generated diff would have produced it.

The proof is a replay. The compose file runs postgres:18 and the sandbox has no Docker, so the agent installs PostgreSQL with sudo apt-get install -y postgresql, states the version it got, and creates the role and database that DATABASE_URL in .env expects. From the base commit it runs alembic upgrade head on an empty database and saves the constraint names from pg_constraint. On its branch it upgrades a second empty database to fe56fa70289e and compares: the names must be identical to the saved ones. It upgrades the first database, the one built by the old files, to the new head, and both must now show the convention's names. It runs alembic downgrade -1 and back, alembic check, an autogenerate against an added throwaway column to see that new operations come out named, and the backend tests through scripts/tests-start.sh.

Start it with one API call

POST https://api.phoenix.so/o/{org}/sessions with this body:

{
  "agent": "claude",
  "input": "Add a SQLAlchemy constraint naming convention to this backend and bring its Alembic migrations in line. Read backend/alembic.ini, backend/app/alembic/env.py, script.py.mako, every file under backend/app/alembic/versions, backend/app/models.py, backend/app/core/db.py, backend/scripts/prestart.sh, .env, compose.yml and .github/workflows/test-backend.yml first. Set the convention from Alembic's documentation on SQLModel.metadata before any table model is defined. Because Alembic applies the convention to unnamed constraints in every operation, give each unnamed constraint in the existing revisions the name PostgreSQL originally chose, wrapped in op.f(), so replaying history on an empty database produces the same names as before. Add one hand-written revision on the current head that renames the live primary key and foreign key constraints to the convention with ALTER TABLE ... RENAME CONSTRAINT, with a downgrade that renames them back. Install PostgreSQL in your sandbox and say which version. Prove it: build one database from the base commit and one from your branch up to the old head and compare pg_constraint names, upgrade both to the new head and compare again, run downgrade and upgrade, alembic check, and the backend tests. Open a pull request with the name tables and every command's output.",
  "resources": [
    {
      "url": "https://github.com/your-org/your-app"
    }
  ]
}
  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.phoenix.so/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

A pull request with one assignment in backend/app/models.py, explicit names added to two old revision files, and one new revision. Its description holds three small tables of constraint names: a database built from the base commit, a database built from the branch up to the old head, and both after the new revision. It also carries the output of the downgrade and upgrade, alembic check, the sample autogenerate showing a named operation, and the test run, plus the PostgreSQL version the sandbox used.

A reviewer's first question is whether editing applied revisions is acceptable, and the first two tables answer it: the edits change no name on any database, old or new. They then check the rename revision against any constraints production has that the migrations do not know about, and merge. From then on, autogenerated revisions name their constraints, and a downgrade no longer depends on guessing what PostgreSQL called something.

Run it on every terminal hand-off

A developer who has just written op.drop_constraint and had to look the name up in psql sends this body from the terminal, with the repository in resources, and goes back to the feature. The run derives every name from the revisions that created it, so the pinned names are the ones the database has and not ones a convention would have liked.

A request_id, a UUID, in the body makes a retried start return the same run. When the turn ends, GET /o/{org}/sessions/{id} names the pull request. A follow-up to that session can ask for a different pattern, such as a shorter foreign key name, or for a test that fails when a model adds a constraint the convention cannot name.

The use case this job follows, with its automation and trigger: Hand off a long task.

Why run it on Rush?

This change is safe only if history replays to the same schema, and the only way to know is to replay it against a real PostgreSQL, twice, and compare catalogs. The agent does that in its own sandbox and keeps each alembic command and query result in the run's record, so the pull request shows the names side by side where a description would ask for trust.

The same request fits a project on plain SQLAlchemy models with a Base.metadata, one with check and unique constraints that need the ck and uq patterns exercised, and one with several schemas. Each run reads that project's revisions for the names they assumed, which is where a convention added late usually breaks.

Frequently asked questions

Is it safe to edit migrations that have already been applied?

Here the edits only write down names that were previously implicit. A database that already ran those revisions is untouched, and an empty database replays to the same names as before. The pull request shows both comparisons, which is the evidence a reviewer should ask for before accepting any edit to history.

Why can't autogenerate produce the rename?

Alembic's documentation lists anonymously named constraints among what autogenerate cannot detect, and it has no operation for renaming a constraint. The agent writes the revision by hand with ALTER TABLE ... RENAME CONSTRAINT and verifies it against the catalog.

Does the convention work with SQLModel?

SQLModel tables register on SQLModel.metadata, an ordinary SQLAlchemy MetaData. Setting its naming_convention before the table classes are defined is enough, and env.py already passes that metadata to Alembic. The sample autogenerate in the pull request shows the result.

What if production has constraints the migrations never created?

The run can only see what the revisions describe, and it says so. Before merging, compare the first name table in the pull request with a query of pg_constraint on production. Any extra constraint needs its own rename line or a decision to leave it.

More Alembic jobs

More jobs like this: Hand off a long task

Keep reading