# Add Soft Delete to a SQLModel and FastAPI App

Yes. An agent adds a deleted_at column to a SQLModel table, an Alembic migration, filtered reads and tests, and opens a pull request for review.

[Run this on Rush](https://cloud.getrush.ai/run/add-soft-delete/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 model and every query that touches it before it changes either. In a project like fastapi/full-stack-fastapi-template, `backend/app/models.py` defines `Item` as a `table=True` SQLModel with a UUID `id`, a `created_at` that defaults to `get_datetime_utc`, and an `owner_id` foreign key declared with `ondelete="CASCADE"`; `User.items` is a `Relationship` with `cascade_delete=True`. The routes in `backend/app/api/routes/items.py` query the table in seven places: `read_items` builds a list statement and a separate `select(func.count())` for superusers and the same pair for owners, and `read_item`, `update_item` and `delete_item` each call `session.get(Item, id)`. Deletion is `session.delete(item)`. In `users.py`, removing a user runs an explicit `delete(Item)` first.

The change to the model is one field, `deleted_at: datetime | None = Field(default=None)`, and the agent lets Alembic write the migration. From `backend/` it runs `alembic revision --autogenerate`, which works here because `app/alembic/env.py` points `target_metadata` at `SQLModel.metadata`. With the SQLModel 0.0.47 that `uv.lock` pins, the generated column type is `sqlmodel.sql.sqltypes.UTCDateTime()`, and the migration template already imports that module, so the file runs as written. The new revision sits on top of `fe56fa70289e`, the one that added `created_at`. The agent applies it with `alembic upgrade head`, steps back with `alembic downgrade -1`, applies it again, and runs `alembic check` to confirm the models and the database agree.

Then the behaviour. `delete_item` sets `deleted_at` and commits, and still returns the same `Message`. Both list statements and both count statements gain `col(Item.deleted_at).is_(None)`, because a list that hides a row while `count` still includes it breaks any pager built on the two. The three `session.get` calls are the part a quick patch misses: a primary-key lookup applies no filter, so a deleted item would still be readable and editable by id. The agent routes them through one helper that returns the item only when `deleted_at` is `None`, and the routes answer 404 otherwise, including a second delete of the same item. Deleting a user stays a hard delete, and the foreign key's cascade removes that user's items whether or not they were soft-deleted. `ItemPublic` is left alone, so the OpenAPI schema and the client that `scripts/generate-client.sh` builds from it do not change.

To run the suite the agent needs PostgreSQL, which the repository gets from the `db` service in `compose.yml`. The sandbox has no Docker, so it installs PostgreSQL from the system packages, creates the `app` database and sets `DATABASE_URL`, then follows the CI job in `.github/workflows/test-backend.yml`: `uv run bash scripts/prestart.sh` to migrate and seed the first superuser, and `uv run bash scripts/tests-start.sh`, which runs pytest under coverage with `FASTAPI_ENV=development`. New tests in `backend/tests/api/routes/test_items.py` use the existing `create_random_item` helper and token fixtures: a deleted item answers 404 on read, update and a repeat delete; it is missing from the list and from `count` for its owner and for a superuser; and the row is still in the table with `deleted_at` set. It finishes with `scripts/lint.sh`, which runs mypy in strict mode, ty and ruff.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add soft delete for items in this FastAPI and SQLModel backend. Read backend/app/models.py, backend/app/api/routes/items.py, backend/app/api/routes/users.py, backend/app/crud.py, backend/app/alembic/env.py and its versions, backend/tests/conftest.py, backend/tests/api/routes/test_items.py, backend/scripts/ and .github/workflows/test-backend.yml first. Add deleted_at: datetime | None to the Item table model and generate the migration with alembic revision --autogenerate from backend/; check upgrade, downgrade and alembic check. Make DELETE /items/{id} set deleted_at and keep its response. Exclude soft-deleted rows from both list statements and both count statements in read_items, and make read, update and a repeat delete of a soft-deleted item answer 404 through one shared helper, since session.get applies no filter. Keep user deletion as a hard delete that removes the user's items. Do not add deleted_at to ItemPublic or change the OpenAPI schema. There is no Docker here: install PostgreSQL from the system packages, create the app database, set DATABASE_URL, then run uv run bash scripts/prestart.sh and uv run bash scripts/tests-start.sh. Add tests for each rule, including that the row still exists with deleted_at set and that count leaves it out. Keep total coverage at or above the 90 percent CI requires, run scripts/lint.sh, and open a pull request.",
  "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 the field on `Item`, one new file under `backend/app/alembic/versions/`, the changed routes with their shared lookup helper, and the new tests. The description lists every query against the table and what was done with each, including the ones deliberately left as hard deletes. It pastes the pytest summary and the coverage table from the sandbox, the output of the upgrade and downgrade, and the result of `scripts/lint.sh`.

A reviewer reads the migration first. It adds a nullable column with no default and no backfill, so on PostgreSQL it does not rewrite the table and existing rows read as not deleted. They then check the list of queries against their own knowledge of the code: any place that reads items outside these routes, such as a report or an export added since, needs the same filter. They confirm the CI job passes with its `--fail-under=90` coverage step; when this page was written the unchanged suite stood at 92 percent, measured on a fresh checkout, so new branches without tests would not have much room.

They also decide what soft delete is for. This change keeps rows, and nothing yet restores or purges them. If the point is an undo, a restore route is a small follow-up; if it is retention, a scheduled purge of rows older than a set age is the other half, and the description lists both as not done.

## Run it on every Linear issue labelled soft-delete

Soft delete tends to be asked for one table at a time, each after someone loses data they wanted back. In the console, add an automation on the Project that holds the backend with a `linear` trigger on `linear.issue.labeled` and the label `soft-delete`, and save this prompt with the model name left to the issue: tell the agent to apply the pattern to the table the issue names. When a person in your Rush org applies the label, the issue is added after the prompt and the run opens a pull request with that table's migration and tests. The runner of the automation needs a Linear connection, made in the console under Settings → Connections.

## Why run it on Rush?

The risky part of soft delete is the query nobody remembered, and the proof that none was missed is a suite run against a real database after a real migration. The agent has its own sandbox for that: PostgreSQL installed for the run, the migration applied forward and back, and every test run with coverage. The run keeps each command and its output, so the pytest summary in the pull request is the one the sandbox printed.

It also leaves the developer's machine out of it. Nobody's local `app` database gets a half-applied revision, and the compose stack on a laptop is not tied up while the suite runs. Several tables can be converted at once, one run and one branch each, and each pull request carries its own migration to review in isolation.

## Frequently asked questions

### Why not filter soft-deleted rows globally instead of in each query?

SQLAlchemy can add a criterion to every `select` for a model through a session event and `with_loader_criteria`, and that suits a codebase with dozens of queries. It is also easy to forget it is there, and a `session.get` that finds the object already loaded in the session returns it without running a query at all. With seven query sites in one file, explicit filters are easier to read and to test, and the description says so.

### What happens to a unique column when the row is only soft-deleted?

The value stays taken. `Item` has no unique column, so nothing changes here, but `User.email` is declared with `unique=True`, which is one reason this request leaves users as hard deletes. Soft-deleting a table with a unique column needs a partial unique index that covers only rows where `deleted_at` is null.

### Can it run the tests without Docker?

Yes. The tests need a PostgreSQL the backend can reach through `DATABASE_URL`, and it does not matter whether a container or a system service provides it. The pull request states which PostgreSQL version the sandbox installed, since `compose.yml` pins `postgres:18` and the two may differ.

## More SQLModel jobs

- [Add updated_at Timestamps to SQLModel Tables](https://getrush.ai/run/add-updated-at-timestamps/sqlmodel/)
- [Migrate a SQLModel and FastAPI App to Async Sessions](https://getrush.ai/run/migrate-to-async/sqlmodel/)
- [Add API Key Authentication to a FastAPI App on SQLModel](https://getrush.ai/run/add-api-key-auth/sqlmodel/)

## The same job on other stacks

- [Add Soft Delete to a Supabase Table](https://getrush.ai/run/add-soft-delete/supabase/)
- [Add Soft Delete to a tRPC and Prisma App](https://getrush.ai/run/add-soft-delete/trpc/)
- [Add Soft Delete to TypeORM Entities](https://getrush.ai/run/add-soft-delete/typeorm/)

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