What is a soft delete — why the «Delete» button usually deletes nothing

Hit "Delete account" in almost any service and your data isn't going anywhere. The database just gets a mark: this row counts as deleted. The row is still there, the fields are still there, everything sits exactly where it sat.
That's a soft delete. And it isn't developer greed — it's how you avoid losing data forever because of one stray click.
How it works
You add one column to the table — usually deleted_at, the time of deletion. Empty means the row is alive. A date means it counts as deleted.
Then instead of DELETE FROM posts WHERE id = 42 the app runs UPDATE posts SET deleted_at = now() WHERE id = 42. And every read query gets an extra clause: WHERE deleted_at IS NULL.
That's all. No magic. If you write SQL by hand, it's literally one more condition per query.
Why bother:
- Undo. A user deleted the wrong project — restored in a second, no trip to the backups.
- Relations don't break. Delete an author and their comments still carry a
user_idthat no longer exists. Half the app trips over dangling references. With a soft delete the reference stays valid. - History. An order is deleted, but last month's report should still contain it. Otherwise your revenue changes retroactively.
Trap one: the one filter you forgot
A soft delete runs on discipline: WHERE deleted_at IS NULL has to be everywhere. Miss it in one query and deleted rows surface right there.
The nasty part is that they don't surface immediately or app-wide. The post list is clean, but deleted items show up in search or in an email digest — because that query was written later and never got the filter.
So you don't write the filter by hand. You attach it once at the data-access layer:
- in an ORM — as a global condition on the model (libraries call it a scope, a filter or middleware);
- in raw SQL — as a
VIEWthat already includes the filter, and you only ever query the view.
The rule: if remembering the filter is your job, one day you'll forget. Let the code remember.
Trap two: uniqueness breaks forever
This one is worth learning before production rather than during it.
You have a UNIQUE constraint on email so two accounts can't share an address. A user deletes their account — but the row stays. Then they change their mind and want to sign up again with the same address.
They can't. The database says the address is taken. Taken by their own deleted row, which they can't see and can't fix. Support is equally baffled — there's no such user in the interface.
The fix is a partial unique index: uniqueness applies only to living rows. In Postgres it looks like this:
CREATE UNIQUE INDEX users_email_active
ON users (email)
WHERE deleted_at IS NULL;
Two live accounts on one address are still impossible, and deleted ones stop getting in the way. If your database has no partial indexes, the other option is to append a marker to the address on deletion so the original is freed.
Adding an index like this to a live table is done with a migration, like any other schema change.
Trap three: "deleted" and "deleted" are different things
A soft delete is about the interface. About "get it out of my sight, I changed my mind."
Then there's real deletion — when a user asks you to erase their data, and legally you have to actually erase it. A deleted_at mark does nothing for that requirement: the data is still fully in the database.
So real apps run both mechanisms:
- The user hits "Delete" — a mark is set, the data can still come back.
- Thirty days pass (or an erasure request arrives) — a background job wipes the rows for real.
That also solves bloat: without the second step the table hoards junk for years, and indexes and queries slowly get heavier.
How to reach deleted rows later
Here's the question that stumps people: if the filter is global on the model, how does the admin panel show a trash bin, and how do you restore anything?
You need a way to deliberately switch the filter off for one query. Nearly every library has that lever — the name varies (withDeleted, unscoped, withTrashed), but the meaning is the same: "this time, show me everything."
From there the logic is simple:
- normal code never touches that lever and only sees live rows;
- the trash bin and admin panel call it explicitly and show deleted items;
- restoring is an
UPDATEthat setsdeleted_atback toNULL.
Worth testing restore on related data, by the way. If deleting a project also deleted its tasks, restoring the project should bring the tasks back too — otherwise the user gets an empty shell and assumes the data is gone.
Should every table have a soft delete?
No, and slapping deleted_at everywhere "just in case" is a common mistake. It belongs where deletion is painful and reversible: accounts, projects, documents, orders. Logs, caches, temporary tokens and sessions don't need it — those genuinely should be deleted.
How is this different from a trash bin?
It isn't, really — the trash bin in an interface usually is a soft delete, shown to the user. The only difference is whether people can see the deleted list and restore things themselves. The mechanism underneath is the same.
Doesn't a backup solve the same problem?
No. A backup is a snapshot of the whole database as of yesterday; restoring a single row from it is slow and risky, and you can lose recent data doing it. A soft delete returns one specific record in a second with no downtime. Different tools — you want both.
The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.





