Can a deleted record come back? Designing soft delete, trash and permanent deletion

The answer depends on which layer was in play at the moment of deletion, and in most teams those layers were never separated. Four mechanisms get conflated: the few-second undo bar in the interface, soft delete that hides a row while leaving it in the table, the job that actually empties the trash after a set number of days, and restoring from backup as the last resort. If you do not separate them up front, the only tool you have when someone calls and says "we deleted it by mistake" is a backup. A backup was never designed to bring back one row.

Decide which accident you are covering

There are four scenarios and they need four different mechanisms.

First, a user deletes the wrong row and notices three seconds later. What is needed here is an undo button in the interface. It can be solved without touching the database at all.

Second, a customer record deleted three months ago is needed again during a reconciliation. That is an archive and audit trail question, not an undo question.

Third, a bulk operation runs with the wrong filter and removes 4,000 rows, and nobody notices until the next day. Restoring these one by one does not work; you need to reverse them as a batch.

Fourth, a customer asks you to delete their account and their data. Here the requirement is the opposite of recovery: the data has to actually leave every copy.

A single deleted_at column does not cover all four. Make it a product decision and write it as a table: scenario, mechanism, window, who is allowed to trigger it.

Soft delete: keep the row, remove the visibility

The common approach is to leave the row in place and mark it. Make the column a nullable timestamp (deleted_at) rather than a boolean. The reason is practical: knowing when something was deleted is the only way to compute a retention window and say "purge everything older than 30 days." Who deleted it belongs in the audit trail, not in the record.

The payoff is real. The product line on an invoice, the agent on a closed support ticket, the address on a past order, all of them stay intact. Hard delete the parent and half of those relationships either error out or render with a blank name.

The bill comes in four parts.

Bill 1: every query now owes you a filter

The moment you adopt soft delete, every read in the system takes on the responsibility of excluding deleted rows. That filter gets forgotten somewhere, and the somewhere is usually the most visible place: the monthly report, the spreadsheet export, the admin panel, search results.

ORMs help, within limits worth knowing. In Hibernate, @Where was deprecated in 6.3 in favour of @SQLRestriction, and the docs are explicit that such restrictions are always applied, cannot be disabled and cannot be parameterized. @SoftDelete, added in 6.4, is still marked incubating, and combining its converter with a timestamp strategy is not supported.

Rails hides a subtler trap. Per the default_scope documentation, the scope applies when building and creating records (Article.new.published returns true), but it is not applied while updating or deleting a record unless you pass all_queries: true. So your soft-delete scope can work perfectly on reads while a bulk update_all quietly touches rows that are supposed to be gone.

The practical rule: do not leave the filter to the good intentions of application code. Point your reporting and export layer at a view that already excludes deleted rows, and keep direct access to the raw table in a small number of places.

Bill 2: your unique constraint now lies

The classic scene: a user deletes their account, comes back a month later with the same email address, and the system says the address is already in use. The record looks deleted, but the row is still there and the unique index still counts it.

In PostgreSQL the fix is a partial unique index. The documentation spells out the behaviour: it enforces uniqueness among the rows that satisfy the index predicate without constraining those that do not. So CREATE UNIQUE INDEX ... ON users (email) WHERE deleted_at IS NULL means only live rows compete.

The second half of that page is less widely known and holds the real surprise. A partial index can only be used by a query if the planner can see that the query's WHERE condition mathematically implies the index predicate. If your queries do not carry deleted_at IS NULL explicitly, the index sits there unused. Matching also happens at planning time rather than run time, so a parameterized condition will never establish that implication.

MySQL has no partial indexes. The usual workaround there is a generated column holding a constant for live rows and the deletion timestamp for deleted ones, with the unique index built on that column. Less elegant, same effect.

Bill 3: cascading is now your job

ON DELETE CASCADE does not fire on a soft delete. You mark the customer as deleted and the orders, addresses and user accounts stay exactly where they were, ready to show up in the next listing.

Two approaches exist. Explicit cascade marks the dependent rows inside the same transaction. Derived visibility keeps no flag on the child and checks the parent instead. The second adds a join to every query and gets expensive on large tables; the first is fast but makes recovery harder, because you lose track of which deletion removed which rows.

The middle ground works best: cascade explicitly, but stamp every deletion with a batch id and write it onto the affected rows. Reversing that bulk delete with the wrong filter then becomes a single update. Without that column, the same job means guessing at timestamp ranges.

Bill 4: the table grows and nobody notices

Deleted rows never leave, so tables and indexes keep growing. Six months in, a third of your most-queried table can consist of rows nobody can see. Partial indexes (WHERE deleted_at IS NULL) recover part of that cost by covering only live rows. For the rest, build a job that moves rows to an archive table past a certain age, and check while designing the data model whether that move is even possible.

Trash is a product feature, not a column

Soft delete is an implementation detail. Trash is something the user sees. People should be able to view what they deleted, know how long they have, and restore it without opening a support ticket.

Looking at what large products do helps when picking the window. Google auto-deletes items in Drive trash after 30 days, a change that took effect on October 13, 2020, with a further 25-day window in which admins can restore items removed from a user's trash. A deleted Microsoft Entra ID user stays suspended for 30 days and can be restored with all its properties; once the window closes, permanent deletion starts automatically and cannot be stopped. GitHub's Enterprise Server documentation keeps a deleted repository on disk for 90 days.

The rest of that GitHub page teaches more than the number does. A restored repository comes back detached from its fork network, release attachments and team permissions are not restored, and the repository may not even be available for restoration until an hour after deletion. The lesson generalizes: a restore rarely returns the identical object. Wherever you document the window, document what does not come back with it. Having the user discover that during a recovery creates a second incident on top of the first.

A three-second undo beats a confirmation dialog

"Are you sure?" is the first thing a user stops reading. It does not prevent deletion, it adds a click.

What works better: perform the action immediately, show an undo bar for five to ten seconds, and commit the real work when that timer expires. It does not force people to think before clicking, because it stops putting the moment of thought in the wrong place.

Friction should rise as reversibility falls. Deleting one row can be one click; deleting an account or 4,000 rows justifies typing the name by hand, a second approver, or a delay before execution. Object stores draw the same line: settings like S3 MFA delete exist precisely to slow down operations you cannot take back. Who is allowed to delete what is part of your authorization model, not a separate flag.

Never reuse the identifier of a deleted record

Microsoft documents this trap directly: creating a new user with the same address instead of restoring the deleted one produces a new object id, and systems holding the old id start failing with access errors.

In your own system the same thing happens more quietly. The identifier of a deleted record lives outside it: in an integration's mapping table, in the accounting software, in a link someone emailed last week. Handing that identifier to a new record creates silent data crossover.

The right move is a tombstone. The row stays, its status is deleted, and a request carrying the old identifier gets an explicit "this record was removed" response or a redirect to whatever replaced it. It is the same pattern used when merging duplicate records.

Permanent deletion is a chain, not a DELETE

Trash does not empty itself. You need a scheduled job that works in batches, can be restarted safely after an interruption, and records what it removed. It is subject to exactly the same rules as any other background job.

The hard part is how long the chain is. Really deleting a record means the dependent rows, the files in object storage, the search index, caches, copies already shipped to the warehouse, and traces in email and support tools. A deletion that stops halfway is the worst state of all: a record that appears in search results and 404s when opened.

Object storage holds one more surprise. Per AWS documentation, a DELETE without a version id in a versioning-enabled bucket does not remove the object; it places a delete marker on top while the object remains as a version, and a GET returns 404 along with an x-amz-delete-marker: true header. Actually removing it requires deleting by version id. That behaviour saves you on the recovery side and bites you on the deletion side: files you believe are gone may still be sitting there.

Backups are the last resort, not a row-recovery tool

Point-in-time recovery works well on managed databases. On Amazon RDS the automated backup retention period runs from 1 to 35 days, transaction logs ship to S3 every five minutes, and you can restore to any second within the retention window, up to roughly the last five minutes.

The trap is procedural rather than technical. You do not rewind the production database for a single row, because you would also rewind everything written since. The move is to restore a separate instance from the backup and pull the row from there. Learning how many minutes that takes on the day of the incident is expensive. Rehearse it once and write the number down.

Then there is the matter of believing you have backups. GitLab's January 31, 2017 incident report is still the clearest document on this: while repairing replication, an engineer removed a data directory on the wrong server and roughly 4.5 GB of about 300 GB was left. Five recovery paths were then tried in turn. LVM snapshots ran only once every 24 hours, the regular backups were producing files a few bytes in size, pg_dump failed silently because it ran against the wrong version binaries, Azure disk snapshots were not enabled on the database servers, and the S3 bucket was empty. What saved the data was a six-hour-old copy an engineer had taken by chance for unrelated work, and everything created in those six hours (roughly 5,000 projects, 5,000 comments and 700 user accounts) was gone. The rule from the backup and disaster recovery side holds here too: an untested backup is no backup.

Records that cannot be deleted, and the side effect of restoring

Some records cannot go. An issued invoice, an accounting entry, a payment record has to stay. What you do there is not deletion but anonymization: personal fields are replaced with a fixed value while amounts and relationships survive. Avoid any transformation that can be reversed; a hash over a small value space is concealment, not deletion. The legal framing of deletion requests is a separate conversation to have with counsel. The engineering question you have to answer is narrower: which tables get deleted, which get anonymized, and which fields stay untouched.

One side effect catches almost everyone. Restoring from a backup brings back records deleted after that backup was taken. If your restore procedure has no "re-run the deletion queue" step, a disaster recovery drill quietly resurrects data you had already removed.

An undo path you have not tested does not exist

Write four tests and run them on every release. Is a deleted record absent from listings, reports, search and exports? Does a restored record come back with its dependent rows? Can someone register again with the email address of a deleted user? Does the permanent deletion job run end to end in an environment that resembles production?

The second one gets skipped most often. Teams write the soft delete, never exercise the restore path, and find out the code is incomplete during the first real recovery request.

The first step that fits in this week

Three queries and one table. First query: how many rows have deleted_at IS NOT NULL, and what is the oldest deletion date? If that date is a year old, your trash has never been emptied and the purge job does not exist. Second: what share of your largest table is made up of deleted rows. Third: are last month's deletions recorded in the audit trail, with the previous values attached?

The table has four rows: the four scenarios from the start of this piece, with the mechanism that covers each one, how many days its window lasts, and who is authorized to use it. Half a day of work. When it is done, your answer to the "we deleted it by mistake" phone call stops being a guess and becomes a procedure.