Database Schema Design for Soft Deletes and Audit Trails
Soft deletes — a deleted_at timestamp instead of an actual DELETE — are the right default for anything users might need to recover or that compliance requires retaining, which on client projects has meant almost every user-facing entity: orders, accounts, documents. The catch is that every single query against that table now needs to filter WHERE deleted_at IS NULL, and forgetting it even once means deleted records quietly reappear in lists, exports, or reports.
We stopped relying on developers remembering that filter and instead used the ORM's built-in soft-delete support (Eloquent's SoftDeletes trait, in our Laravel projects) which applies the filter automatically to every query unless explicitly overridden with withTrashed(). That default-safe behavior caught what manual filtering couldn't — a new engineer joining the project doesn't need to know the convention exists to accidentally follow it correctly.
Unique constraints are the sharper edge case people miss. A unique constraint on email for a users table breaks the moment you soft-delete a user and someone else tries to register with that same email — the database sees an existing row and rejects it, even though that row is 'deleted' from the application's perspective. We solved this with a partial unique index (UNIQUE (email) WHERE deleted_at IS NULL in Postgres) so the constraint only applies to live rows.
For genuinely sensitive data — anything with legal retention requirements around right-to-erasure requests — soft delete alone isn't sufficient, since the data is still sitting in the table and in every backup. We built a separate hard-delete process for those specific cases, triggered manually after a retention or legal hold period, that actually removes the row and any PII from soft-deleted rows past a defined age, keeping only an anonymized audit record of the fact that a deletion occurred.
The audit trail complement to soft deletes is recording who deleted what and when, not just that deleted_at is set. We added deleted_by and a deletion_reason field on tables where that context mattered for support and compliance purposes, populated from the authenticated user context at delete time — a detail that seemed minor until the first time a support ticket asked 'who deleted this order and why' and we actually had an answer instead of a guess.
