# Add Soft Delete to SQLAlchemy Models

Yes. An agent adds a deleted_at mixin, a session-level filter that hides deleted rows from every query, an Alembic migration and tests, then opens a PR.

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

## What the agent does

The agent maps every place a session is made before it adds a filter, because a soft-delete filter that misses one of them leaks deleted rows. In an async SQLAlchemy 2.0 project like teamhide/fastapi-boilerplate, `pyproject.toml` asks for `sqlalchemy` `^2.0.25` with the `asyncio` extra and `alembic` `^1.13.1` over `aiomysql`. `core/db/session.py` defines `Base(DeclarativeBase)`, a writer and a reader engine, and a `RoutingSession` whose `get_bind` sends flushes and `Update`, `Delete` and `Insert` statements to the writer and everything else to the reader. That class is passed as `sync_session_class` in two places: the `async_scoped_session` that writes go through, and the `session_factory()` context manager that `UserSQLAlchemyRepo` opens for each read. The only model, `User` in `app/user/domain/entity/user.py`, already takes a `TimestampMixin` from `core/db/mixins/`, and the single migration in `migrations/versions/` creates the `user` table with unique constraints on `email` and `nickname`.

It adds a `SoftDeleteMixin` beside the timestamp one, with a nullable `deleted_at` column typed `Mapped[datetime | None]`, and registers one `do_orm_execute` listener on `RoutingSession` itself. For every ORM `SELECT`, the listener attaches `with_loader_criteria(SoftDeleteMixin, lambda cls: cls.deleted_at.is_(None), include_aliases=True)`, unless the statement was given the execution option `include_deleted=True`. Listening on the session class is what covers both construction sites at once, so the four read methods in the repository need no `where` clause added and a query someone writes next month is filtered without them remembering to. `User` inherits the mixin, and an Alembic revision generated with `alembic revision --autogenerate` adds the column and an index on it; `migrations/env.py` already swaps `aiomysql` for `pymysql` to run it.

Two things in this codebase need a decision the filter cannot make. First, nothing deletes a user today: the service has `create_user`, `login`, `is_admin` and a list, and `user_router` has no `DELETE`. The agent adds a `delete_user` use case wrapped in the existing `@Transactional()` decorator, a repository method that sets `deleted_at` instead of emitting a `DELETE`, and a route under `/api/v1/user` behind the same `IsAdmin` dependency as the list route. Second, the unique constraints. `create_user` calls `get_user_by_email_or_nickname` to refuse duplicates; once that read is filtered it no longer sees a deleted account, the insert goes ahead, and MySQL rejects it on the unique index with an integrity error the API does not handle. MySQL has no partial unique indexes to exempt deleted rows, so the agent makes the duplicate check pass `include_deleted=True`, which keeps a deleted user's email and nickname reserved, and writes the alternatives into the pull request.

Then it runs the suite against a real database, which is how this project tests: `make test` sets `ENV=test`, and the `session` fixture in `tests/conftest.py` applies Alembic to `head` on a `fastapi_test` database and truncates tables afterwards. The sandbox has no Docker, so the agent installs it with `sudo apt-get install -y docker.io`, starts `dockerd`, and brings up the MySQL 8.0.33 and Redis 6.2.6 services from `docker/docker-compose.yml` with `docker-compose` v1, since the v2 plugin is not in that package. New tests sit next to `tests/app/user/adapter/output/persistence/sqlalchemy/test_user.py`: a deleted user is absent from the list, from lookup by id and from login; the same row comes back with `include_deleted=True`; a second sign-up with a deleted user's email is refused with the existing duplicate exception; and the revision upgrades and downgrades cleanly.

## Start it with one API call

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

```json
{
  "agent": "claude",
  "input": "Add soft delete to this SQLAlchemy project. Read pyproject.toml, core/db/session.py, core/db/transactional.py, core/db/mixins/timestamp_mixin.py, app/user/domain/entity/user.py, the user repository, service and router under app/user, migrations/env.py, the existing revision, tests/conftest.py, tests/support/test_db_coordinator.py, the Makefile and docker/docker-compose.yml first. Add a SoftDeleteMixin with a nullable deleted_at column and apply it to User. Register a do_orm_execute listener on RoutingSession that adds with_loader_criteria for the mixin (deleted_at IS NULL, include_aliases=True) to every ORM select unless the statement has the execution option include_deleted=True, so both the scoped session and session_factory() are covered. Generate an Alembic revision for the column and an index. There is no delete path today: add a delete_user use case with @Transactional(), a repository method that sets deleted_at, and an admin-only DELETE route. Keep the duplicate email and nickname check correct by reading with include_deleted=True, and explain the other options for the unique constraints in the pull request. Start MySQL and Redis as docker/docker-compose.yml defines them, run make test, add tests for list, lookup, login, include_deleted, duplicate sign-up after delete and the migration up and down, 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 mixin and the listener under `core/db/`, the new revision under `migrations/versions/`, the mixin on `User`, the delete use case threaded through the abstract repository, its SQLAlchemy implementation, the adapter, the service and the router, and the new tests. The description shows the SQL the filtered `select` emits, quotes the `make test` output, and has a section on uniqueness that sets out three choices: keep deleted emails reserved (what the diff does), scrub the unique columns on delete, or replace the constraints with composite ones that include a generated column.

A reviewer checks the filter's reach before anything else: that a raw `text()` query or a Core `select` on the table, neither of which the ORM criteria touch, is not used anywhere that should hide deleted rows. They check the `include_deleted` escape is used only in the duplicate check. They read the migration for lock behaviour on a large `user` table. And because reads go to the reader engine and the delete goes to the writer, they consider whether a replica that lags could show a just-deleted user for a moment, which the pull request flags as a property of the existing routing and not of this change.

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

After the mixin and listener are merged, extending soft delete to another model is a short, repeatable change: inherit the mixin, generate a revision, decide what its unique constraints should do, add tests. In the console, add an automation on the Project with a `linear` trigger on `linear.issue.labeled` and the label `soft-delete`, and a prompt that says to apply the existing `SoftDeleteMixin` to the model the issue names. The issue is placed after the prompt as the event, so the model name comes from the person who filed it. The trigger needs a Linear connection for the automation's runner and fires only for a label applied by someone in your Rush org.

Each labelled issue gets its own run and its own pull request with its own migration, so two models requested in one day do not end up sharing a revision file. If your backlog lives elsewhere, a handler on your side can send the same request to `POST /o/{org}/sessions` with the ticket text appended and a `request_id` that is a UUID v5 of the ticket id.

## Why run it on Rush?

Soft delete fails in the queries nobody thought about, and the only way to find them is to run the application's own paths against a real database with a deleted row in it. The agent brings up the same MySQL and Redis versions the repository's compose file names, in its own sandbox, applies the migration through Alembic exactly as the test fixture does, and keeps each command and its output in the run's record. The reviewer gets the emitted SQL and the test log, both of which show more than a description of intent would.

It also surfaces the decision that usually arrives as a production bug. The unique-constraint collision here is invisible in a code read of the mixin alone; it appears when a sign-up is attempted after a delete. Because the run writes and executes that test, the pull request arrives with the question already asked and one answer implemented, and the reviewer chooses with the trade-offs in front of them.

## Frequently asked questions

### Do I have to add a filter to every query?

No. The `do_orm_execute` listener adds the `deleted_at IS NULL` criteria to every ORM `select` issued through `RoutingSession`, including relationship loads and aliased entities. Existing repository methods are unchanged, and new ones are filtered by default.

### How do I query deleted rows on purpose?

Pass the execution option: `select(User).execution_options(include_deleted=True)`. The listener skips the criteria for that statement only. The pull request uses it in one place, the duplicate email and nickname check.

### What happens to unique columns when a row is soft deleted?

The row is still in the table, so its `email` and `nickname` still occupy the unique indexes. This change keeps them reserved and makes the duplicate check see deleted rows, so a sign-up gets the normal duplicate error. Releasing them needs either scrubbing those columns on delete or a different constraint, and both are described in the pull request.

### Does it work with the async session and the reader and writer split?

Yes. Both the scoped async session and `session_factory()` use `RoutingSession` as their sync session class, and the listener is registered on that class. The soft delete itself is an `UPDATE`, which `get_bind` already routes to the writer engine.

## More SQLAlchemy jobs

- [Fix "Can't load plugin: sqlalchemy.dialects:postgres" in a Flask App](https://getrush.ai/run/fix-cant-load-plugin-sqlalchemy-dialects-postgres/sqlalchemy/)
- [Fix "ArgumentError: Column expression, FROM clause, or other columns clause element expected" on SQLAlchemy 2](https://getrush.ai/run/fix-column-expression-or-from-clause-expected/sqlalchemy/)
- [Fix "MovedIn20Warning: The declarative_base() function is now available as sqlalchemy.orm.declarative_base()"](https://getrush.ai/run/fix-declarative-base-movedin20warning/sqlalchemy/)

## The same job on other stacks

- [Add Soft Delete to a sqlc Project](https://getrush.ai/run/add-soft-delete/sqlc/)
- [Add Soft Delete to a SQLModel and FastAPI App](https://getrush.ai/run/add-soft-delete/sqlmodel/)
- [Add Soft Delete to a Supabase Table](https://getrush.ai/run/add-soft-delete/supabase/)

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