Soft Delete vs Hard Delete in SaaS | Coding Capybaras

Photo by Maksym Kaharlytskyi on Unsplash

Soft delete vs hard delete is not really a choice between two options — almost every SaaS that survives contact with real customers ends up running both, on different tables, with a scheduled job connecting them. A soft delete marks a row as gone by setting a deleted_at timestamp while the data stays in the table. A hard delete removes the row. Soft delete buys you recoverability, audit trails, and referential sanity. It costs you query complexity, index headaches, and a genuine legal problem when a customer invokes their right to erasure. Here's how to decide per table, and what the law actually demands.

TL;DR

• Soft delete is a deleted_at column; hard delete is DELETE FROM. Most real SaaS apps use both, plus a purge job between them.
• A soft delete is not erasure under GDPR. Article 17 obliges you to actually erase "without undue delay," and you have one month to respond — a flag doesn't satisfy it.
• The hidden cost isn't storage, it's correctness: every query and every unique constraint now needs to know about deleted_at, and the one you forget is the one that leaks data.
• PostgreSQL partial unique indexes solve the "user deleted their account, can't sign up again with the same email" bug cleanly.
• Default to soft delete with a purge window for customer-facing content, hard delete for junk, and anonymize for records you're legally required to keep.

What's the actual difference between soft delete and hard delete?

A hard delete is the one the database gives you: DELETE FROM projects WHERE id = $1. The row is gone. Foreign keys cascade or block. Storage is reclaimed on the next vacuum. It's simple, and simple is genuinely valuable.

A soft delete is an application convention: you add a nullable timestamp column, set it instead of removing the row, and make every read filter it out.

-- hard delete
DELETE FROM projects WHERE id = $1;

-- soft delete
UPDATE projects SET deleted_at = now() WHERE id = $1;

-- and now every single read has to care
SELECT * FROM projects
WHERE workspace_id = $1
  AND deleted_at IS NULL;

Use a nullable timestamptz rather than a boolean is_deleted. It costs the same and tells you when, which you will want the first time a customer asks what happened to their data. This is the same reasoning behind always reaching for timestamptz over timestamp, which I covered alongside six other SaaS database schema mistakes.

The tradeoff most people lead with is storage. Ignore that one. Postgres handles millions of tombstoned rows without noticing, and if you're at the scale where it matters you have a data engineer. The real tradeoff is correctness surface area.

With hard deletes, a deleted row cannot appear anywhere, because it doesn't exist. With soft deletes, a deleted row appears everywhere by default, and stays hidden only where you remembered to filter. Every new query is a new chance to leak. Every aggregate — counts, sums, "you have 14 projects" — is a new chance to be quietly wrong. Every JOIN inherits the problem from both sides.

That's the actual decision. Not disk space. How many places in your codebase are now responsible for knowing a piece of trivia that the database used to enforce for you.

The third option, which deserves to be in the conversation from the start: anonymization. You keep the row, keep its structure and its foreign keys, and destroy the parts that identify a person. The invoice survives for your accountant; the name and email on it become deleted-user-8812. For a lot of SaaS tables this is the answer that satisfies everyone at once, and it's the one founders discover last.

What privacy law actually requires

I'm not a lawyer and this isn't legal advice — if you're handling health data, children's data, or anything in a regulated industry, get an actual professional. But the shape of the obligation is worth understanding, because it constrains your schema.

Under GDPR Article 17, an individual can require you to erase their personal data, and you must do so "without undue delay" when one of the listed grounds applies — for example, when the data is no longer necessary for the purpose you collected it, or when they withdraw the consent you were relying on. The UK regulator's guidance on the right to erasure puts a hard clock on it: you have one month to respond, extendable by two further months if the request is complex or the person has sent you several, and you must tell them what you did.

A deleted_at flag does not satisfy that. The row is still there, still readable, still in your backups, still one bad WHERE clause away from a support agent's screen. Hiding is not erasing.

But the right isn't absolute, and the exceptions are the reason you can't just hard-delete everything either. Article 17(3) carves out processing that's necessary to comply with a legal obligation, and for "the establishment, exercise or defence of legal claims." The ICO's own example is an ex-employee asking an employer to erase everything — the employer can refuse, because it's legally required to keep salary records for the tax authority. Your version of that is invoices and payment records, which your jurisdiction almost certainly requires you to retain for years regardless of what the customer wants.

California works similarly. The California Attorney General's CCPA overview describes a right to delete that is "subject to certain exceptions (such as if the business is legally required to keep the information)." The full exception list lives in the statute itself at Civil Code §1798.105(d), and the backup handling is in the CCPA regulations at 11 CCR §7022(d) — which lets a business delay deletion from an archived or backup system until that system is next restored or used, rather than exempting it permanently. Different wording from the ICO, same practical shape: act on live systems now, let backups resolve on their own cycle.

Backups are where founders panic, so here's the practical position. The ICO is clear that a valid request means you "will have to take steps to ensure erasure from backup systems as well as live systems" — but it also recognizes that the request may be fulfilled instantly on live systems while the data persists in backups until it's overwritten. The requirement in the meantime is to put backup data "beyond use": don't touch it for any other purpose, let it age out on your established schedule, and tell the person that's what happens. In practice that usually means adjusting your retention policy rather than surgically editing a nightly snapshot — but "we have backups" is not by itself an answer.

Which produces the actual design constraint:

1. Something must be able to really erase personal data, on a deadline you can hit.
2. Something must be able to retain financial and legal records even after the person is gone.
3. Those two things have to operate on the same customer without contradicting each other.

Soft delete alone fails requirement one. Hard delete alone fails requirement two. Hence the hybrid. If you're mapping this across your whole app, I've written a founder-level walkthrough of GDPR basics for indie SaaS, and the mechanics of actually shipping the flow live in account deletion and data export.

The costs of soft delete nobody warns you about

The unique constraint bug

This one bites everybody, usually in week one of having users.

You have UNIQUE (email) on your users table. Someone deletes their account — soft delete, so the row stays with their email on it. Three months later they come back, try to sign up with the same email, and hit a constraint violation on a row they can't see and your support agent can't find.

The wrong fix is moving the uniqueness check into application code, SELECT then INSERT. That's a time-of-check-to-time-of-use race: two concurrent signups both pass the check, both insert, and now you have duplicate accounts and a database that was supposed to prevent exactly this.

The right fix is a partial unique index. PostgreSQL's partial index support lets you attach a WHERE clause to the index itself, so uniqueness is enforced over a subset of rows:

-- drop the naive constraint
ALTER TABLE app_users DROP CONSTRAINT app_users_email_key;

-- uniqueness that only applies to live rows
CREATE UNIQUE INDEX app_users_email_active
  ON app_users (email)
  WHERE deleted_at IS NULL;

Now one active user can hold an email address, any number of deleted ones can hold it historically, and the guarantee stays in the database where race conditions can't reach it. On a table with live traffic, build it with CREATE UNIQUE INDEX CONCURRENTLY — the plain form takes a lock that blocks writes for the duration. The Postgres docs call out this pattern directly — using WHERE with UNIQUE to enforce uniqueness over part of a table — and the index is smaller and faster as a bonus, because it only contains rows your queries actually look at.

The forgotten filter

Every SELECT needs AND deleted_at IS NULL. You will forget one. It will be in an export, or an admin view, or a count on the dashboard.

Three defenses, in increasing order of reliability.

Use your ORM's scoping so the filter is the default rather than something you remember. In Drizzle you'd wrap the common read:

const activeProjects = (workspaceId: string) =>
  db.select().from(projects).where(
    and(
      eq(projects.workspaceId, workspaceId),
      isNull(projects.deletedAt),
    ),
  );

Create a view for the live rows and point read paths at it, so the filter lives in one place:

CREATE VIEW projects_active AS
  SELECT * FROM projects WHERE deleted_at IS NULL;

One gotcha: Postgres expands that * when the view is created, not when it's queried. Columns you add to projects later won't appear in projects_active until you re-run CREATE OR REPLACE VIEW. Put that line in the same migration as any column addition, or list the columns explicitly and let the migration fail loudly instead of drifting quietly.

Best of all, push it into row-level security so the database refuses to hand over tombstoned rows no matter how the query was written. If you're on Supabase this composes naturally with the policies you already have — the mechanics are in the Supabase row level security tutorial.

The cascade that doesn't

ON DELETE CASCADE is a hard-delete feature. Soft-delete a workspace and its projects, tasks, and files all remain cheerfully undeleted, because nothing actually happened at the database level. You now own cascade logic in application code, and it has to be transactional, and it has to be idempotent, because the first version will fail halfway through at some point.

This is the strongest practical argument for keeping soft delete shallow. Soft-delete the top-level object the user thinks about. Let everything underneath be hard-deleted by the purge job when the window closes.

The AI-assistant trap

Worth naming, since most readers here are building with Claude Code or Cursor: an assistant writing a new query against your schema has no way to know that deleted_at is load-bearing. It will produce a perfectly reasonable SELECT that includes deleted rows, and it will look right in review.

The fix is documenting the convention where your assistant will actually read it — a line in CLAUDE.md stating that every read from a soft-deleted table filters deleted_at IS NULL, and which tables those are. Or, better, making it structurally impossible via views and RLS, so correctness doesn't depend on anyone remembering.

The hybrid pattern most SaaS apps end up with

Here's the lifecycle that emerges once you've absorbed all of the above. Three states, two scheduled transitions.

flowchart TD
    A[Active row] -->|User deletes| B[Soft deleted<br/>deleted_at = now]
    B -->|User restores<br/>within window| A
    B -->|Purge job<br/>after 30 days| C{Legally<br/>required to keep?}
    B -->|Erasure request<br/>skips the window| C
    C -->|No| D[Hard delete<br/>row removed]
    C -->|Yes| E[Anonymize<br/>PII stripped, record kept]
    D --> F[Backups aged out<br/>beyond use meanwhile]
    E --> F

The purge window is the important piece and the part people skip. Soft delete without a scheduled purge isn't a deletion strategy, it's a growing pile of personal data you've promised to protect and forgotten you have.

Thirty days is a reasonable default: long enough for "I didn't mean to do that," short enough that your exposure is bounded and your erasure deadline is comfortable. Say the number out loud in your interface — "deleted projects are recoverable for 30 days, then permanently removed" — and put the same number in your privacy policy. The ICO is explicit that you must be clear with people about what happens to their data, and a stated window is the easiest way to be clear.

The erasure branch matters too. A GDPR request isn't a normal delete and shouldn't ride the normal queue. It skips the window, purges immediately, and triggers the anonymize-or-remove decision on everything else the person touched. Build that path deliberately, test it on a real account, and know how long it takes — because a month sounds generous right up until you're writing the script on day 29.

A minimal purge job, running nightly:

// runs on a cron; keep it boring, batched, and idempotent
const cutoff = new Date(Date.now() - 30 * 24 * 60 * 60 * 1000);

const expired = await db
  .select({ id: projects.id })
  .from(projects)
  .where(lt(projects.deletedAt, cutoff))
  .limit(500); // batch, so one slow night can't lock the table

const ids = expired.map((p) => p.id);
if (ids.length) {
  // object storage FIRST — if this fails, the DB rows survive and we retry
  const blobs = await db
    .select({ key: projectFiles.storageKey })
    .from(projectFiles)
    .where(inArray(projectFiles.projectId, ids));
  await storage.remove(blobs.map((b) => b.key));

  await db.transaction(async (tx) => {
    await tx.delete(projectFiles).where(inArray(projectFiles.projectId, ids));
    await tx.delete(projectTasks).where(inArray(projectTasks.projectId, ids));
    await tx.delete(projects).where(inArray(projects.id, ids));
  });
}

Note the ordering: delete the blobs before the rows. If you drop the database rows first and the storage call then fails, you've lost the only record of which files to clean up, and the customer's data is still sitting in your bucket.

Log what it removed. When a customer asks where their data went eighteen months from now, that log is your answer.

What to soft delete, what to hard delete, what to anonymize

Deciding per table takes about twenty minutes and saves you a migration later. The questions are: would a customer ever want this back, does anything downstream depend on it existing, and are you required to keep it?

| Table | Strategy | Why | | --- | --- | --- | | User accounts | Soft delete → anonymize | Undo window, but billing history must survive the person | | Projects, documents, customer content | Soft delete → hard delete after 30 days | Accidental deletion is common and recoverable | | Comments and messages | Soft delete → hard delete | Threads break visibly when a parent vanishes | | Invoices and payment records | Never delete — anonymize only | Tax and accounting retention obligations | | Audit logs | Never delete | The point of an audit log is that it can't be edited | | Sessions, tokens, password resets | Hard delete | Ephemeral by nature; keeping them is a security liability | | Uploaded files | Hard delete on purge, both DB row and object storage | Storage costs money and orphaned blobs are a real leak | | Analytics events | Hard delete or aggregate out | Pseudonymize on purge; keep the counts, drop the identifiers |

Two rows there are worth dwelling on.

Sessions and tokens should be hard-deleted, always. A soft-deleted session token is a live credential with a flag on it. The only thing standing between it and an attacker is a WHERE clause. Delete the row.

Uploaded files need the object-storage half. Purging the database row while the file sits in an S3 bucket means you've told the customer their data is gone and it isn't. This is a common gap, and it's the kind of thing that turns an ordinary support question into an incident — worth checking against your SaaS security incident plan before you need it.

For the user-account row, anonymization usually looks like this:

UPDATE app_users
SET email      = 'deleted-' || id || '@example.invalid',
    full_name  = 'Deleted user',
    avatar_url = NULL,
    deleted_at = now()
WHERE id = $1;

Foreign keys stay intact. The invoice still joins. Nobody's name is on it. Use a domain reserved for exactly this — .invalid is set aside by the IETF and can never resolve — so a stray email job can't accidentally send somewhere real.

One last connection worth making: whichever strategy you pick, the moment it becomes visible to customers is when they cancel. What you say on the way out, what you promise about their data, and what actually happens thirty days later all need to agree. That's as much a product decision as a schema one, and it belongs in your customer offboarding and cancel flow.

Frequently asked questions

Is soft delete an anti-pattern?

It's an anti-pattern when applied universally and without a purge window — that's how you end up with a table where most rows are invisible and every query is a liability. Applied selectively to customer-facing content, with a documented retention window and a scheduled job that finishes the job, it's a well-understood pattern with real benefits.

Does a soft delete satisfy GDPR's right to erasure?

No. Article 17 requires erasure, and the data in a soft-deleted row is still present and still processable. Soft delete is a fine intermediate state during your recovery window, but an erasure request has to end in the data actually being removed or irreversibly anonymized — and Article 12(3) gives you one month to respond and tell the person what you did.

Do I have to delete data from my backups?

You have to take steps toward it, but regulators are pragmatic. The ICO's position is that you can fulfill the request on live systems immediately and let backups age out on their normal schedule, as long as the backup data is put "beyond use" — not accessed for any other purpose — and you tell the individual that's what happens.

How long should my purge window be?

Thirty days is a sensible default for most SaaS products. Shorter windows reduce your data exposure; longer windows recover more accidental deletions. Whatever you pick, state it in the interface and in your privacy policy, and make sure the scheduled job actually runs — an unstated or unenforced window is worse than none.

What about ON DELETE CASCADE with soft deletes?

Cascades only fire on real deletes, so soft-deleting a parent leaves every child row untouched. Either handle the cascade explicitly in a transaction in application code, or keep soft delete shallow — flag only the top-level object, and let the purge job hard-delete the children, where real cascades work normally.

Can I add soft delete later, or do I need it from day one?

You can add it later; it's one of the friendlier migrations. Add the nullable column, swap the unique constraints for partial indexes, and update reads — ideally behind a view or RLS policy so you only change one place. The painful part isn't the schema, it's auditing every existing query, which gets worse the longer you wait.

Soft delete vs hard delete: how to choose per table

Soft delete vs hard delete stops being a debate once you accept that you need both plus a scheduled job between them. Soft-delete what a customer might want back, hard-delete what's ephemeral or dangerous to keep, anonymize what the law requires you to retain after the person is gone, and put a stated purge window between the first state and the last. Then write the erasure path deliberately, because one month goes quickly and it's the one deadline in your schema that someone else sets.

If you want this already wired up — deleted_at columns, partial unique indexes, RLS policies, and a purge job that runs on a schedule — Coding Capybaras is the free Next.js and Supabase boilerplate I built so non-technical founders don't have to discover these tradeoffs the expensive way.