# 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 Rush](https://cloud.getrush.ai/run/add-not-null-constraint/alembic/). Opens 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`

```json
{
  "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`.

## 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

- [Fix Alembic Multiple Heads After a Merge](https://getrush.ai/run/fix-multiple-heads/alembic/)
- [Review Alembic Migrations in Every Pull Request](https://getrush.ai/run/review-database-migrations/alembic/)
- [Squash Alembic Migrations into One](https://getrush.ai/run/squash-migrations/alembic/)

## More jobs like this: Hand off a long task

- [Add Desktop Notifications to a Chrome Extension](https://getrush.ai/run/add-notifications/chrome-extension/)
- [Add Desktop Notifications to an Electron App](https://getrush.ai/run/add-notifications/electron/)
- [Set Up npm Trusted Publishing for a Node.js Package](https://getrush.ai/run/add-npm-trusted-publishing/nodejs/)

## Keep reading

- [How a Rush run works on your repository, from request to pull request](https://getrush.ai/cloud/)
- [Compare cloud agents, agent sandboxes and AI code review](https://getrush.ai/compare/)
- [Rush pricing: Free, Pro and Max](https://getrush.ai/pricing/)
