What the agent does
The agent reads the data layer before it designs the table, because an audit log is only as good as its coverage of the writes. In a backend laid out like fastapi/full-stack-fastapi-template, backend/app/models.py defines User and Item as SQLModel tables with UUID primary keys, a created_at set by get_datetime_utc, and Item.owner_id with ondelete="CASCADE". The writes are in backend/app/api/routes/items.py (create, update, delete) and users.py (create, update me, change password, delete me, signup, update by id, delete by id), plus crud.py for the ones the login and signup paths share. SessionDep and CurrentUser in api/deps.py give every route a session and the caller. Migrations live in backend/app/alembic/versions, five of them chained from e2412789c190 to fe56fa70289e, and alembic/env.py points target_metadata at SQLModel.metadata. CI in .github/workflows/test-backend.yml brings up Postgres with docker compose up -d --wait db mailpit, runs scripts/prestart.sh (alembic upgrade head), then scripts/tests-start.sh, and fails under 90% coverage.
The table goes in models.py beside the others: AuditLog with id, created_at, actor_id (nullable, since a signup has no caller yet), action such as item.update, entity and entity_id, and a changes JSON column holding the fields that changed, before and after, with hashed_password never written. The agent runs alembic revision --autogenerate -m "add audit_log" so the file chains from fe56fa70289e, then reads the generated script, because autogenerate puts a sa.JSON where Postgres should have JSONB and emits no index on (entity, entity_id); it fixes both by hand, the way the earlier revisions in the directory were adjusted.
Recording is one function, crud.record_audit(session, actor, action, obj, before, after), called inside the same session as the write and before the session.commit() each route already has, so the row and the change land in one transaction or neither does. Each route in items.py and users.py takes the model's model_dump() before sqlmodel_update and after, diffs them, and passes the result. Delete routes record the final state as before; the cascade delete of a user's items is written as one user.delete row with the item ids, not one row per item, which the pull request says in a sentence. Password changes record the action with an empty diff.
Reading the log is a new superuser route, GET /audit-log/ with skip and limit and optional entity_id, under Depends(get_current_active_superuser) like GET /users/, returning AuditLogsPublic with data and count to match the shape ItemsPublic uses. In its sandbox the agent runs docker compose up -d --wait db, uv run bash scripts/prestart.sh to apply the migration, and uv run bash scripts/tests-start.sh. It adds tests under tests/api/routes/test_audit_log.py in the pattern of test_items.py: an item update writes one row whose changes names only title; a normal user gets 403 on the read route; the conftest.py teardown that deletes Item and User is extended to delete AuditLog first. It runs uv run bash scripts/generate-client.sh so the frontend's generated client knows the route, and quotes coverage report staying over 90%.
Start it with one API call
POST https://api.phoenix.so/o/{org}/sessions with this body:
{
"agent": "claude",
"input": "Add an audit log to this FastAPI and SQLModel backend. Read backend/app/models.py, backend/app/crud.py, backend/app/api/routes/items.py, backend/app/api/routes/users.py, backend/app/api/deps.py, backend/app/alembic/env.py, the files in backend/app/alembic/versions, backend/tests/conftest.py and .github/workflows/test-backend.yml first. Add an AuditLog table with actor_id, action, entity, entity_id, created_at and a JSONB changes column; generate the Alembic revision from the latest head and fix its types and add an index on (entity, entity_id). Add a crud.record_audit function and call it in every create, update and delete route in the same transaction, diffing before and after and never writing hashed_password. Add a superuser-only GET /audit-log/ route with skip, limit and entity_id, returning data and count. Start the database with docker compose, run scripts/prestart.sh and scripts/tests-start.sh, add tests for one recorded update and a 403 for a normal user, extend the conftest teardown, run scripts/generate-client.sh, keep coverage above 90 percent, and open a pull request quoting the results.",
"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
What comes back is a pull request with a new model and public schema in models.py, one hand-corrected Alembic revision chained from the current head, a record_audit function in crud.py, a call in each of the nine write routes, a new audit_log.py route registered in api/main.py, two tests, one line in conftest.py, and the regenerated frontend client. The description lists the routes and what each records, so a reviewer can check coverage of the writes without reading every diff hunk.
The test output is quoted: the whole suite green under coverage run, the two new tests named, and the coverage percentage after the change. The migration is shown applying from fe56fa70289e with the JSONB type and the index in the script, not the autogenerated JSON.
Before merging, a reviewer settles two policies the agent left as notes: how long rows are kept, since nothing deletes them yet and a retention job is a separate change, and whether changes for a user update should mask email the way it already skips the password hash.
Run it on every Linear issue labelled agent
An audit log tends to arrive as a compliance ask, which makes it a good first label to automate. An automation in the backend's Project with the prompt above and a linear trigger on linear.issue.labeled for the label agent starts a run when someone files "we need to know who changed a customer's item" and labels it. The trigger adds the issue after the prompt, so the run reads which models the issue names and records those writes first.
Every run has its own sandbox, its own Postgres from docker compose, and its own branch, and opens one pull request that links the issue. A later issue to add a retention sweep runs the same way and chains its revision after the one this run wrote, because each run reads the versions directory before generating.
The use case this job follows, with its automation and trigger: Turn a Linear label into a PR.
Why run it on Rush?
The hard part of an audit log is not the table, it is the nine routes that each have to record in the same transaction as their write, and the autogenerated migration that looks right and is not. Done by hand on a Friday, one route is missed and the log is trusted anyway. In a Rush sandbox the agent reads every write path first, records inside the existing commits, corrects the revision it generated, and proves the coverage with a test before anyone reviews.
The record of the run keeps the migration output, the test run and the coverage report, and the pull request quotes them, so the reviewer checks the route list against the diff instead of taking the word "complete" on trust. For every backend a team keeps on this template, the request runs in parallel, one sandbox and one pull request each.
Frequently asked questions
Why not a SQLAlchemy event listener instead of calls in each route?
A listener on after_flush would catch every write, but it has no caller: the request's user lives in the route's dependency, not in the session. The explicit call passes the actor and names the action, and the pull request lists the routes so a reviewer can see none were skipped.
Does the log row land even if the write fails?
No. record_audit adds the row to the same session before the route's existing session.commit(), so the write and the log commit together or roll back together. The test for an item update checks there is exactly one row after the request.
What does the migration look like after the fix?
The autogenerated file is kept but edited: the changes column is postgresql.JSONB rather than sa.JSON, and an op.create_index on (entity, entity_id) is added with a matching drop in downgrade. The revision id chains from the current head, so alembic upgrade head applies it in order.
Who can read the log?
Only superusers. The read route depends on get_current_active_superuser, the same dependency that guards listing users, and a test asserts a normal user's token gets 403. Writes are recorded for everyone, including signups, where actor_id is null.
More SQLModel jobs
The same job on other stacks
- Add an Audit Log of Row Changes to a Supabase Project (Supabase)
- Add an Audit Log to a Django REST Framework API from a Linear Issue (Django REST Framework)
- Add an Audit Log to a Fastify API (Fastify)