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 with this body:
{
"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"
}
]
}- 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.
- 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.
- 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.
The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.
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
The same job on other stacks
- Add Soft Delete to a sqlc Project (sqlc)
- Add Soft Delete to a SQLModel and FastAPI App (SQLModel)
- Add Soft Delete to a Supabase Table (Supabase)