All jobs

Make a Nullable Column NOT NULL with an Alembic Migration

Yes. An agent backfills the existing rows, writes an Alembic migration that sets NOT NULL without a long table lock, tests it both ways 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

A column that should never be empty but was added as nullable is a common leftover, and tightening it is three changes that must agree: the data, the schema and the model. The agent finds all three. In a project like fastapi/full-stack-fastapi-template, revision fe56fa70289e added created_at to the user and item tables as DateTime(timezone=True) with nullable=True and no backfill, so every row that existed when it ran has a null. backend/app/models.py declares the field as datetime | None with a Python-side default_factory, which fills new rows from the application and nothing else. The response models UserPublic and ItemPublic carry the same optional type, and the generated client in frontend/src/client/types.gen.ts has created_at?: string | null. The nulls are not harmless: read_items and the user list order by created_at descending, and PostgreSQL sorts nulls first in a descending order, so the oldest rows appear at the top.

The migration it writes has an order that matters. First a backfill of the null rows. Their true creation time is unknown, so the agent uses one fixed timestamp, declared as a constant at the top of the revision and defaulting to the create date of the revision that added the column, since those rows were written before that revision reached the database; the pull request states that this is a stand-in and not a fact. Then a server default of now(), so a row inserted by anything other than the application, a script or a manual fix, also gets a value. Then the constraint.

Setting the constraint is where a simple migration hurts a live table. ALTER COLUMN ... SET NOT NULL takes an exclusive lock and scans every row to check it. The agent writes the sequence PostgreSQL offers to avoid that: add a CHECK (created_at IS NOT NULL) constraint as NOT VALID, validate it in a second step that reads the table without blocking writes, then set NOT NULL, which PostgreSQL accepts without a scan when a valid check already proves it, and drop the check. The steps must commit separately, or the lock taken to add the check is still held while the table is scanned. env.py here runs each revision in one transaction, so the agent puts the steps in op.get_context().autocommit_block() and writes each one so the revision can be rerun if it stops partway. The downgrade makes the column nullable again and removes the default, and its docstring says the backfilled values stay.

It then changes the model to datetime with the server default declared, the two public models to match, and regenerates the client with scripts/generate-client.sh so the frontend types lose the null. To test against real nulls it installs PostgreSQL in the sandbox with sudo apt-get install -y postgresql, upgrades only as far as 1a31ce608336, inserts users and items, upgrades to fe56fa70289e so those rows gain a null created_at, and then applies the new revision. It checks no null remains and an insert without the column succeeds, runs the downgrade and upgrade again, runs alembic check for drift, and runs the backend tests with bash scripts/tests-start.sh.

Start it with one API call

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

{
  "agent": "codex",
  "input": "Make created_at NOT NULL on the user and item tables and open a pull request. Read backend/app/models.py, every revision under backend/app/alembic/versions, backend/app/alembic/env.py, the routes that read or order by created_at, backend/scripts, scripts/generate-client.sh and the generated client types first. Write one new Alembic revision on top of the current head that, for each table: backfills rows where created_at is null with a single fixed timestamp held in a constant at the top of the file, defaulting to the create date of the revision that added the column; sets a server default of now(); adds a CHECK (created_at IS NOT NULL) constraint as NOT VALID, validates it, sets the column NOT NULL and drops the check. Run those steps in an autocommit block so each commits on its own and the validation does not run under the lock that added the check, and make every step safe to rerun if the revision stops partway. Write a downgrade that restores nullability and removes the default, and say in its docstring that backfilled values are kept. Update the SQLModel models and the public response models so created_at is required, declare the server default on the model, and regenerate the frontend client. Install PostgreSQL in the sandbox and test with real null rows: upgrade to the revision before the column existed, insert rows, upgrade to the revision that added it, then apply the new revision. Verify no nulls remain, an insert that omits created_at succeeds, downgrade and upgrade both work, and alembic check reports no drift. Run the backend tests with scripts/tests-start.sh. In the pull request description state the backfill value and why, the lock each step takes, and what a team with a very large table should do differently.",
  "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 new revision, the model change and the regenerated client types. The description walks through the revision step by step with the lock each statement takes, states the backfill timestamp and that it is an approximation, and shows the checks that ran: the count of null rows before and after, the insert without the column, the downgrade and second upgrade, alembic check, and the test summary.

A reviewer decides the backfill value, which is a product question as much as a technical one. A fixed past timestamp puts the old rows at the bottom of a newest-first list, where they belong; using the migration time would put them at the top. They check the model's server default matches what the migration set, or the next autogenerate will report a difference, and they look at the client diff to confirm nothing in the frontend relied on the field being absent.

For a table too large to backfill in one statement, the description says what to change: run the backfill in batches from a script before deploying the revision, so the migration's own update finds nothing to do, and keep the rest as written. The agent does not guess the size of your table; it writes the revision so that either route works.

Run it on every terminal hand-off

Constraint clean-ups are found while doing something else: a developer notices the optional type in a response model and knows it should not be. From an MCP client, create_session with this prompt, the table and column changed to the ones at hand, and the repository in resources starts the run, and the developer stays on their branch. wait_for_sessions returns the result when the turn ends and get_session_changes shows the revision before the pull request is even opened in a browser.

A team working through a backlog of such columns sends one POST /o/{org}/sessions per column from a script, each with its own request_id, a fresh UUID, so a retried call returns the run it already started. Runs past the key's concurrency limit are accepted and wait as queued.

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

Why run it on Rush?

The risky part of this change is the data, and the only honest test uses rows that are actually null. Producing them means rewinding a database to before the column existed, inserting, and migrating forward again, which nobody wants to do to the database they develop against. In its own sandbox the agent builds that history from scratch, runs the migration in both directions, and leaves the commands and their output in the session for the reviewer.

The change also crosses three layers, migration, model and generated client, and it is easy to ship two of them. Because the run regenerates the client and runs the test suite after the schema change, a mismatch between layers shows up in the pull request's diff or in the test output, not later in a browser.

Frequently asked questions

Why not just set nullable=False and autogenerate?

Autogenerate emits a single alter_column with nullable=False. On a table that still has nulls it fails, and on a clean but large table it locks out reads and writes while every row is checked. The backfill, the validated check constraint and committing the steps separately are what make it safe, and none of that is generated.

Does the NOT VALID check constraint approach work on every PostgreSQL version?

The skip of the full scan when a valid check exists arrived in PostgreSQL 12. On older servers the sequence still runs correctly but the final step scans the table. The agent states the server version it tested on in the pull request.

What happens to rows inserted while the migration runs?

The server default is set before the constraint is added, and the application already supplies a value for every insert, so new rows arrive with a timestamp throughout. Once the unvalidated check exists, PostgreSQL enforces it for new writes even before validation finishes.

Can the downgrade restore the nulls?

No, and it should not try. A downgrade returns the schema to its earlier shape; the values written by the backfill remain. The revision's docstring says so, so nobody expects a round trip to reproduce the old data exactly.

More Alembic jobs

More jobs like this: Hand off a long task

Keep reading