Soft deletion - marking a row as deleted rather than removing it - is one of the most common patterns in application databases, and one of the least examined. It solves a genuine problem: users delete things by mistake, and support wants to undo it. It also changes the meaning of every query in the system, and that consequence is rarely priced in at the time.
The immediate effect is that every query is now wrong by default. Any SELECT that forgets the deleted_at IS NULL condition returns deleted rows, and it will look correct in testing because nothing has been deleted yet. This is the single most common bug the pattern produces, and the defence is structural rather than diligent: a view, a scope in the ORM, or row-level security that filters it, so the safe behaviour is what you get without thinking.
Uniqueness constraints break in a way people do not anticipate. If a user deletes an account with a given email and signs up again, a unique index on email rejects them, because the old row is still there. The fix is a partial unique index covering only non-deleted rows, and it is worth doing at the moment you add soft deletion rather than when a customer cannot re-register.
Then there is the question of what deletion means when a parent is soft-deleted. The database's foreign keys know nothing about it, so orphaned children remain visible and joins return rows referencing an invisible parent. This has to be handled deliberately - cascade the soft delete, or filter through the relationship - and it is the source of the strangest bug reports these systems produce.
Retention makes it a legal question rather than only a technical one. Under India's DPDP rules and GDPR alike, a user's request to erase their data means erased, not hidden behind a flag. A soft-deleted row is still personal data you hold and still needs a lawful basis. So the pattern needs a second stage: soft delete for a grace period during which recovery is possible, then genuine deletion by a scheduled job - including from analytics warehouses, log files and backups, which is where the compliance gap usually is.
The alternative worth considering for anything auditable is an append-only design: never update or delete, record events, and derive current state. It is more work up front and gives you complete history, natural audit trails and the ability to answer what a record looked like on a given date - which regulated businesses eventually need and cannot reconstruct from a table with a deleted_at column.
