What is soft delete? It is marking a row as deleted, usually with a deleted_at timestamp, instead of removing it, so normal queries skip the row and one UPDATE brings it back. It earns its place where a customer can delete something by mistake, and it breaks quietly in 3 places: forgotten filters, unique constraints and erasure requests.
What is soft delete, and how is it different from a hard delete
Soft delete means marking a database row as deleted, usually with a deleted_at timestamp, without removing it. The application hides marked rows and a single UPDATE restores them. A hard delete removes the row, and only a retained copy such as a backup brings it back. To the database, a soft-deleted row still exists.
Recoverable deletion is one of the checks in the data consistency checklist for SaaS, and it’s the one that matters the week a customer asks for something back.
A hard delete is the SQL DELETE: the row is removed, and only a copy (a backup, or point-in-time recovery) can bring it back. A soft delete is an UPDATE that sets a marker, and the application agrees to treat marked rows as gone. The marker can be a boolean such as is_deleted, but I prefer a nullable deleted_at timestamp, because it also says when; add deleted_by where the app has users, so it also says who.
The catch sits in one sentence: a soft-deleted row still exists as far as the database is concerned. It still takes up storage, still holds its unique values, still satisfies its foreign keys, and any query that doesn’t filter it out returns it. Every problem later on this page comes from that. Soft deletion moves the job of forgetting from the database to your code.
Put side by side, the difference between soft delete and hard delete shows up in four places:
| Hard delete | Soft delete | |
|---|---|---|
| What the database does | Runs DELETE; any ON DELETE action on child tables runs with it | Runs UPDATE ... set deleted_at = now(); the row stays, and no ON DELETE action runs |
| What the user sees | The record is gone | The record is gone, as long as every read path filters it |
| Can it be undone | Only from a retained copy: a backup or point-in-time recovery | Yes: an UPDATE sets deleted_at back to null |
| What it costs | The old row version waits for VACUUM to make its space reusable | The row keeps its storage, its unique values and its foreign keys until a purge removes it |
Soft delete in cloud storage and mailboxes: the same words, a different feature
Cloud providers use the same words for a setting on stored files. Google Cloud Storage’s soft delete keeps deleted or overwritten objects and buckets in a soft-deleted state for a set period, during which they can be restored, and it is on by default for buckets that support it, with a default retention duration of 7 days. Azure Blob Storage’s soft delete keeps a deleted blob, snapshot or version for a retention period you set, during which you can restore it, and deletes it permanently once that period expires. Microsoft also uses the term for directory objects in Microsoft Entra and for Exchange mailboxes. All of these are provider features. They are useful for uploaded files and unrelated to the rows in your database; the bucket side belongs to the object storage security checklist.
What goes wrong without it, and what goes wrong with it
Soft delete fails in two directions: when it’s missing, and when it’s added carelessly. The usual story on the missing side: a customer deleted data by accident, the app ran a real DELETE, and the deleted record is gone forever with no undo anywhere in the product. The only route back is a copy, which is the recovery ladder in how to undo a DELETE in Postgres, and that ladder has rungs only if the backups exist, the ground covered by the database backup checklist for startups. A cascade rule can make it worse by deleting child rows the user never meant to lose; which foreign keys cascade is a question for the database review checklist.
| What happened | Which side | The control on this page |
|---|---|---|
| A project deleted by mistake cannot be brought back from inside the app | Without soft delete | The column, the default filter and the restore path |
Deleting a parent removed its child rows through ON DELETE CASCADE | Without soft delete | Children decided per relation, marked with the parent’s timestamp |
| A list, export, count or search forgot the filter and showed deleted data, sometimes to another user | With it, done carelessly | A default filter in a view, a policy or an ORM scope |
| A customer could not reuse a name or an email because the deleted row still held the unique value | With it, done carelessly | A partial unique index |
| A report counted deleted rows alongside live ones | With it, done carelessly | The default filter, plus the list of paths it misses |
| Personal data the user asked to erase sat in the table for years | With it, done carelessly | The purge job and the erasure path |
Picture the first row as a case. A customer deletes a project by mistake and asks for it back the same day, but the delete was permanent and the only copy is in the backup. Restoring that copy in place would take the whole database offline and roll every other customer back with it; on Supabase, the backup docs say the project is inaccessible while a restore runs. A backup restores a database, not one customer’s click. The lesson I take from it: anything a customer can delete with a click wants soft delete.
The objections to the pattern are real too. thoughtbot, a development agency, has published a post arguing against it: developers have to remember to exclude deleted records, including in raw SQL; dependent records make it trickier; indexes get more complicated and uniqueness constraints need updating; and an undeleted record can collide with a newer one. Instead, it suggests asking whether regular backups, or a UI that makes accidental deletes less likely, would do. Most objections match a row of the table above, and each has a control in the steps below. Backups alone leave the scenario above unsolved.
When a deleted record should be recoverable, and when it must really go
Soft delete belongs where the product promises an undo or a recovery window: data a person can remove with a click and later want back, such as projects, documents and team members. A flag never satisfies an erasure request, because a flagged row is still personal data you hold.
Whether each kind of data, from sessions to invoices, gets a hard delete or a soft delete is already a table in the data-loss article on AI-built apps, in its section on choosing deletion behavior by data class. It leaves open two alternatives that fit some tables better than a deleted_at column. One is an archive table the deleted row is moved into. The move out of the original table is a DELETE, so any ON DELETE CASCADE on the row’s children fires, and as I read it, children the move didn’t copy are gone with it. The other is a status column: when “archived” is a state the user picks in the product, it isn’t a deletion at all, and it gets its own value and its own screens.
The legal edge, once. Under the EU GDPR, a soft-deleted row is still personal data you hold, because the row still identifies the person; that is my reading, not a quote. Article 17 of the GDPR gives a person the right to erasure “without undue delay” where one of its listed grounds applies, and its paragraph 3 sets out exceptions, such as compliance with a legal obligation. Article 5(1)(e) adds storage limitation: personal data shall be kept in a form that identifies people “for no longer than is necessary” for the purposes it is processed for. So a flag doesn’t meet an erasure request, and hiding an identifiable row isn’t erasing it. What erasure means for each category of data is set out in a GDPR delete request runbook. This is not legal advice.
In the Production Hardening Sprint this is deliverable 4.9: we implement soft deletion where the product needs recovery, consistently with account-erasure and retention rules.
How to add it to a live table, in seven steps
Adding soft deletes to a table takes 7 steps: choose the tables, add a nullable deleted_at column, make the filter the default, free unique values with partial indexes, decide what happens to children, build a permissioned restore, and schedule a purge with the erasure path around it.
- 01 Pick the tables where a person can delete something and want it back
- 02 Add a nullable deleted_at column, and switch the delete handler from DELETE to UPDATE
- 03 Make the filter the default, so no read path has to remember it
- 04 Free unique values and scope the hot indexes to live rows
- 05 Decide, per relation, what happens to children
- 06 Build the restore path and decide who may use it
- 07 Schedule the purge and wire the erasure path around it
In plain SQL, a soft delete comes down to one column, one UPDATE in place of the DELETE, and a filter every read inherits; the rest of the steps make that hold on a live app. The database and framework behavior below comes from each one’s own documentation, named where it’s used. The partial unique index, the filtered view and the scheduled purge are already written out as SQL in the data-loss article’s “Make soft delete real” section; here they are named, not reprinted, and the steps add what that section leaves out.
The column and the migration
Add deleted_at timestamptz, nullable and with no default, plus deleted_by where the app has users. PostgreSQL doesn’t rewrite the table for this: with no column constraints, “NULL is used as the DEFAULT”, and “In neither case is a rewrite of the table required.” No rewrite still means a lock. PostgreSQL’s ALTER TABLE reference says “An ACCESS EXCLUSIVE lock is acquired unless explicitly noted”, so the statement itself is quick, but it can queue behind a long-running query and hold up the queries behind it while it waits (my reading). My working rule is to run it at a quiet time with a short lock timeout of a few seconds, and retry if it gives up:
set lock_timeout = '5s'; -- a few seconds; rough value, my working rule
alter table projects
add column deleted_at timestamptz,
add column deleted_by uuid;Change the app’s delete handler in the same release, once the column exists: update projects set deleted_at = now(), deleted_by = $2 where id = $1 and deleted_at is null replaces the DELETE. Rollback planning for the change sits with the database migration checklist.
Exclude deleted rows from default queries
Deleted rows are best excluded by default in one of three places: a view the app reads from, a row-level security policy that includes deleted_at is null, or an ORM default scope. A filter each query has to remember gets forgotten, and raw SQL, exports and search indexes still need their own check.
My reading: a where deleted_at is null that every developer, and every AI-generated query, has to remember will eventually be missing from one of them. So put the condition where reads inherit it without anyone remembering.
| Where the default lives | How | What it still misses |
|---|---|---|
| A view per table | The app reads active_projects, a view that selects rows where deleted_at is null; Supabase’s own note on soft deletes takes this route | Writes still go to the table; Supabase’s example view does not set security_invoker |
| A row-level security policy | The select policy’s using clause includes deleted_at is null, on Supabase or plain Postgres | A soft delete that asks for the updated row back (the RETURNING trap below) |
| Laravel | The SoftDeletes trait excludes soft-deleted models from all query results; withTrashed brings them back | Anything that bypasses the model, such as raw SQL |
| TypeORM | With @DeleteDateColumn set, the default scope is “non-deleted” | Anything that bypasses the repository, such as raw SQL |
| Django | A custom manager overriding get_queryset() | Related-object access such as choice.question, which uses the base manager rather than your default manager |
| EF Core | A global query filter set with HasQueryFilter | Any query that disables the filter for itself |
| Prisma ORM 8 | Soft delete is listed as not available; the docs say to add a nullable deletedAt field and filter on it, and middleware runs code around every query | Every query the filter was not added to |
The view row follows Supabase’s note on soft deletes: add deleted_at, create a view of active rows, query the view, and restore by setting the column back to null. The security_invoker note in the table’s first row is the caveat the data-loss article’s “Make soft delete real” section spells out before such a view is exposed. The framework rows come from Laravel’s soft deleting docs, EF Core’s global query filters, TypeORM’s entity docs, Django’s managers docs and Prisma’s ORM 8 docs.
The policy route has one trap. In Supabase’s docs, for an update policy “The using clause decides which existing rows can be updated” and “The with check clause decides what the resulting row is allowed to look like”. PostgreSQL’s CREATE POLICY reference adds that if a data-modifying query has a RETURNING clause, updated rows must satisfy the table’s SELECT policies to be returned, and if one doesn’t, “an error will be thrown”. So a soft delete that asks for the marked row back fails under a select policy that includes deleted_at is null, because the row it just marked no longer passes (my reading).
None of the three places covers everything. Check each of these one at a time: raw SQL in reports, exports, the search index, caches, background jobs, counts on dashboards, and joins that reach the table from the other side. A list page that joins through several filtered tables is also where the N+1 query problem and its fix comes up next.
Unique values, indexes and children
A soft-deleted row still occupies its unique values, so a partial unique index that covers only rows where deleted_at is null lets a name be reused. Children get the parent’s timestamp in the same transaction, so a restore brings back exactly that set.
PostgreSQL’s partial indexes page documents the pattern: a unique index over a subset of a table “enforces uniqueness among the rows that satisfy the index predicate, without constraining those that do not.” The data-loss article’s first piece is exactly that index on a projects slug. The same where deleted_at is null also suits the indexes your busiest reads use, since a partial index “contains entries only for those table rows that satisfy the predicate”, and the queries must carry the same condition for the planner to use it. I add those once deleted rows are a real share of the table; that part is my working rule, not the docs’.
Children need a decision per relation. Either deleting the parent marks the children with the same deleted_at value in one transaction, so a restore can find exactly that set, or the children stay as they are and are hidden through the parent. My working rule is the first, because it makes the restore exact. A database ON DELETE CASCADE acts when a referenced row “is being deleted”, and a soft delete is an UPDATE, so the cascade never runs. That is the point, and also the trap: nothing marks the children unless your code does.
Soft-deleted rows aren’t orphans, so the junk-row sweep in the database cleanup checklist must skip them. They still hold customer data at rest, so the choices in database encryption for a small SaaS cover them too.
The restore path, and who may use it
Restore is set deleted_at = null on the parent and on the children that share its timestamp, in one transaction. Decide who can run it: the user for a short undo window, an admin after that, with the permission checked on the server rather than by hiding a button. Plan for the name being reused in the meantime: the partial unique index will reject the restored row, because it now satisfies the index predicate again, so restore it under a suffixed name such as “Roadmap (restored)”. Write both the delete and the restore to the audit log, with who and when, following audit logging best practices. The same column can also carry an account deletion feature, which has its own rules for what goes and when.
The purge job, and the erasure path around it
A purge job hard-deletes rows whose deleted_at is older than the recovery window the retention schedule sets. Without it the table only grows and the recovery window becomes forever. An erasure request does not wait for the window: the deletion path removes the data at once.
The scheduled DELETE itself is the third piece of the data-loss article’s “Make soft delete real”. What it needs in production is to run in batches, so no single run holds locks on a large table for long, and on its own connection, so it never holds a slot the app’s requests need from connection pooling in Postgres. How long the window lasts belongs in the retention schedule, and a data retention policy template for a small SaaS already has a row for soft-deleted records. An erasure request skips the queue: the deletion path hard-deletes or anonymizes that person’s rows at once, soft-deleted ones included, as part of the data subject request handling checklist.
How to verify it
Soft delete is verified with one test entity that has children: delete it, confirm the row remains and every list, search, export and direct request excludes it, reuse its name, restore it with its children, then prove the purge job and the erasure path really remove it.
Run all seven checks in staging on the same test project with a few child rows, so one test restore of a soft-deleted record also proves the delete, the filter and the unique index around it. Each check says what a pass looks like and what to keep as evidence.
- 01 Delete the project in the UI, then open its table in your database dashboard: the row is still there with deleted_at set. Evidence: a screenshot.
- 02 Check every list, search, export, count and API read, then request the project's id directly, first as its owner and then as a second user in another tenant with that user's own session: nothing returns the row, whether the API answers not found or an empty result. Evidence: the list of read paths and what each returned.
- 03 Create a new project with the same name: it is allowed. Evidence: the new row.
- 04 Restore the first project: it comes back under a suffixed name, because its name is now taken, with the same children and nothing else. Evidence: row counts before and after.
- 05 Run the purge job with a seeded row whose deleted_at is older than the window: that row is gone. Evidence: the job output and a query that finds nothing.
- 06 Run the erasure path on a seeded user: their soft-deleted rows go too, at once, without waiting for the window. Evidence: the query that returns none.
- 07 Open the audit log: the delete and the restore both appear. Evidence: the two entries.
The old row for check 5 belongs in the seed script, next to the rest of the realistic fake data for staging.
The sprint verifies deliverable 4.9 the same way: we delete and restore a test entity and verify normal queries exclude deleted records.
Where the sprint does this
Deliverable 4.9’s delivery and verify lines are above; two neighbors carry the rest of this page. Deliverable 12.2 implements authenticated export and deletion workflows covering related systems and documented retention exceptions, and 12.5 documents retention periods and handling rules for logs, user history, and abandoned records. The production readiness report delivers the result for every scope item, the work completed, and its verification evidence, and deliverable 13.1 is verified by accounting for all 123 IDs, keeping failures visible until resolved and explaining genuine non-applicable items. Legal advice and certification are separate services; we implement and document the technical data-handling controls. Each of these lines is in the published scope.
Common questions about soft and hard deletes
Is soft delete a good practice?
Yes, for data a person can delete by mistake and want back, and no for data that must be permanently removed, such as revoked sessions or anything under an erasure request. My verdict comes with conditions: it is only good practice with a default filter, unique handling and a purge job. The objections to it, from forgotten filters to restores that collide, are real, and each one has a control in the steps above. The data-loss article’s data class table draws the line for each kind of record.
Which is faster, DELETE or truncate?
TRUNCATE is faster at emptying a table. PostgreSQL’s docs say it has the same effect as an unqualified DELETE on each table, “but since it does not actually scan the tables it is faster”, and it reclaims disk space immediately. It removes all rows, so it is the wrong tool for anything selective, and it has nothing to do with soft delete, which never removes a row at all.
What happens when you DELETE data?
In PostgreSQL the row isn’t removed from disk on the spot: in the docs’ words, “an UPDATE or DELETE of a row does not immediately remove the old version of the row”, and PostgreSQL’s routine vacuuming docs describe VACUUM later marking that space available for reuse. The same applies to the UPDATE a soft delete runs. That is also why a disk can stay full after a big delete, which the data-loss article’s FAQ on a still-full Postgres disk takes further. Copies also live on in backups and point-in-time recovery until they age out (my reading); how that meets an erasure request is the GDPR delete request runbook’s subject.
Is there any way to permanently delete?
Yes: a hard DELETE, a purge job for the rows you soft-deleted, and time, because backups keep their copies until they age out on the retention schedule. For a person who asked for erasure, the deletion path removes their data at once instead of waiting for the purge, and the GDPR delete request runbook covers what happens to the backup copies.
The checks in this guide show you where the app is open. The sprint below closes those gaps, tests the result and writes the evidence down.
Built it with AI. Now it has to hold up for real customers.
The Production Hardening Sprint takes the app you already have and builds the production foundation underneath it. Authentication and access rules, payments that stay consistent, error handling, monitoring, backups, automated tests and a documented handover. Our engineers work inside your existing codebase for ten working days. All 123 deliverables are included, and you get the evidence for each one.
See the Production Hardening Sprint → 
$2,500 fixed price · 10 working days · One codebase