# Add Optimistic Locking to SQLModel Updates

Yes. An agent adds a version column to a SQLModel table, makes the update route refuse a stale version with a 409, migrates, tests the conflict, and opens a PR.

[Run this on Rush](https://cloud.getrush.ai/run/add-optimistic-locking/sqlmodel/). Opens the console with this prompt ready to send, in a Project that has your repository.

## What the agent does

The agent reads the update path before it changes it. In a FastAPI app laid out like fastapi/full-stack-fastapi-template, `backend/app/models.py` defines `Item` as a SQLModel table with `id`, `title`, `description`, `created_at` and `owner_id`, with `ItemUpdate` carrying optional `title` and `description` and `ItemPublic` the fields a response returns. `backend/app/api/routes/items.py` handles `PUT /items/{id}`: `session.get(Item, id)`, a 404 and a 403 check, then `item.sqlmodel_update(update_dict)`, `session.add`, `session.commit`, `session.refresh`. Two people editing the same item write whichever request commits last, with no sign to the first that their change was overwritten. `backend/pyproject.toml` pins `sqlmodel>=0.0.46` and `alembic>=1.19.1`, `backend/app/core/db.py` creates the engine with `pool_pre_ping=True`, and the Alembic revisions under `backend/app/alembic/versions` show the project's migration style, the latest adding `created_at` to both tables.

It adds `version: int = Field(default=1)` to `Item`, exposes it on `ItemPublic` so every read carries it, and makes `version` required on `ItemUpdate`, so a client must send back the version it read. The route then issues one conditional statement instead of load-then-save: an `update(Item)` that sets the changed columns and `version = version + 1` where the id matches and the version equals the one sent. A rowcount of one means the write won; zero means someone else committed first, and the route answers `409` with a detail that says the item changed and asks the client to reload. The 404 and 403 checks stay in front, so a stale version on an item you cannot see is still a 403, not a 409.

A migration adds the column with a server default of 1 so existing rows are valid, written in the style of `fe56fa70289e` and placed after it in the chain. On the frontend side, `frontend/src/components/Items/EditItem.tsx` calls `ItemsService.updateItem` with the form data; the agent passes the item's `version` from the row it already holds and shows the 409 through the existing `handleError` toast, so the user sees why the save failed. The generated client in `frontend/src/client` is regenerated from the OpenAPI schema with the repository's `scripts/generate-client.sh`.

Tests go beside the existing ones in `backend/tests/api/routes/test_items.py`, which already cover a successful update, a missing item and insufficient permissions: a first update with the version read succeeds and returns the next version, a second update with the old version answers 409 and leaves the row unchanged, and an update with no `version` is a validation error. The agent starts a PostgreSQL in its sandbox, points `DATABASE_URL` at it, runs `alembic upgrade head` through `scripts/prestart.sh` and then `scripts/tests-start.sh`, which wraps the suite in `coverage`, and finishes with `mypy`, `ty` and `ruff` as `scripts/lint.sh` does.

## Start it with one API call

`POST https://api.phoenix.so/o/{org}/sessions`

```json
{
  "agent": "claude",
  "input": "Add optimistic locking to Item updates. Read backend/pyproject.toml, backend/app/models.py, backend/app/crud.py, backend/app/api/routes/items.py, backend/app/core/db.py, backend/app/alembic/env.py, the revisions under backend/app/alembic/versions, backend/tests/conftest.py, backend/tests/api/routes/test_items.py, backend/scripts and frontend/src/components/Items/EditItem.tsx first. Add a version column to Item with a default of 1, return it on ItemPublic, require it on ItemUpdate, and make PUT /items/{id} a single conditional update that increments the version and answers 409 when the row's version does not match, keeping the 404 and 403 checks ahead of it. Write an Alembic migration with a server default. Pass the version from EditItem and regenerate the client with scripts/generate-client.sh. Add tests for the winning update, the stale update and the missing version. Start a PostgreSQL in the sandbox, run the migration and the test script, then scripts/lint.sh, and open a pull request with the 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

What comes back is a pull request that changes `models.py`, `items.py`, one new Alembic revision, `EditItem.tsx`, the generated client and the item tests. The description shows the conditional update statement, the 409 body, and the test and lint output from the sandbox.

The run's record keeps the migration against a real PostgreSQL and the two competing updates in the tests, so a reviewer can see the second one refused rather than read that it would be.

Before merging, a reviewer decides whether `User` updates should get the same treatment, which the pull request leaves out on purpose, and whether the 409 should return the current row so a client can show a diff instead of only a message.

## Run it on every Linear issue labelled data-integrity

A team makes this a rule rather than a one-off. In the Project for the repository, an automation with the prompt above and a `linear` trigger on `linear.issue.labeled` for the label `data-integrity` starts a run when someone files "two editors overwrote each other's item description". The trigger adds the issue after the prompt, so the run reads which table the issue names and applies the same pattern there.

Each run opens its own pull request on its own branch and links the issue. A table that already carries a version column gets a description saying so and a pull request only if the route still does a load-then-save.

## Why run it on Rush?

Lost updates are silent: no error, no log line, just a change that disappears. The fix is a column, a conditional statement and a status code, but each piece has to agree with the others and with the migration, and the only proof is two writes racing against a real database. The agent does that in its own sandbox with PostgreSQL, keeps the migration run and the test output, and ships the frontend change that makes the 409 visible.

The pull request reads as one statement and three tests, so review is quick, and a team with several services on this template runs them all at once, each in its own sandbox with its own pull request.

## Frequently asked questions

### Why a conditional UPDATE rather than SQLAlchemy's version_id_col?

The mapper option raises `StaleDataError` on commit, which the route would still have to catch and translate. A single `UPDATE ... WHERE id = :id AND version = :version` says exactly what is checked, needs no mapper configuration on a SQLModel class, and its rowcount is the whole answer. The pull request mentions the mapper option as the alternative.

### What does the client send?

The `version` it read, as a required field of the update body. The generated TypeScript client makes it a type error to forget, and `EditItem.tsx` already holds the item it is editing, so the value is at hand. A client that cannot send it is the signal that it should reload before writing.

### Does this change existing rows?

The migration adds the column with a server default of 1, so every row has a valid version without a data backfill, and the downgrade drops the column. Nothing else in the schema moves.

### Can the 409 carry the current item?

Yes, and it is a reasonable follow-up: the route can read the row after the failed update and include it in the detail so the client shows what changed. The agent keeps the first pull request to the conflict check and names this in the description.

## More SQLModel jobs

- [Add Refresh Tokens to a FastAPI App on SQLModel](https://getrush.ai/run/add-refresh-tokens/sqlmodel/)
- [Add Seed Data to a FastAPI SQLModel App](https://getrush.ai/run/add-seed-data/sqlmodel/)
- [Add Soft Delete to a SQLModel and FastAPI App](https://getrush.ai/run/add-soft-delete/sqlmodel/)

## The same job on other stacks

- [Add Optimistic Locking to TypeORM Entities](https://getrush.ai/run/add-optimistic-locking/typeorm/)
- [Add Optimistic Locking to a GORM Model in a Gin API](https://getrush.ai/run/add-optimistic-locking/gorm/)
- [Add Optimistic Locking to a Mongoose API](https://getrush.ai/run/add-optimistic-locking/mongoose/)

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