Implementing Soft Deletes in SQLAlchemy: Keeping Audit Trails Without Orphaning Foreign Keys in Multi-Tenant Systems

Implementing Soft Deletes in SQLAlchemy: Keeping Audit Trails Without Orphaning Foreign Keys in Multi-Tenant Systems

I've shipped soft deletes three times now. The first two times, I broke production.

The first attempt was naive: add an is_deleted boolean to every model, filter it in queries, call it a day. That lasted until a foreign key constraint violation happened at 2 AM because I'd soft-deleted a parent record while child records still pointed to it. The second attempt used database views to hide deleted records—which worked until I realized the views were breaking SQLAlchemy's relationship loading and my audit queries were silently returning incomplete data.

CitizenApp needed soft deletes for regulatory compliance (finance customers demand audit trails), but also needed referential integrity and point-in-time recovery without corrupting the schema. Here's the production pattern I built after those failures.

Why Soft Deletes Matter in Multi-Tenant SaaS