Soft Delete vs Hard Delete: What 'Deleted' Should Really Mean in Your Schema

"Deleted" should never mean one thing in your schema — it should map to whichever of three distinct product behaviors you're actually building: hiding something from a user's view, archiving it for audit and recovery, or erasing it irrecoverably to satisfy a legal request. Conflating these into one boolean is the most common deletion-modeling mistake.

Soft delete (a nullable deleted_at timestamp) means "hidden, still here." Hard delete means "erased, unrecoverable." Most real products need both, plus a middle "archive" state — and the choice per entity is a product decision, not a database default.

Delete Is Three Different Features, Not One

"Delete" in most products is really three separate features wearing one button: hide (recoverable by the end user, self-service), archive (recoverable by the team, inside an audit or dispute window), and erase (irreversible, recoverable by no one). Each implies different schema, retention, and access-control decisions — and shipping only one of them usually means you've under-built the other two.

Most teams reach for soft delete by default because it's cheap to implement. But "cheap to implement" and "matches what the user expects" are not the same claim. A user who deletes a draft comment expects it gone from their own view immediately — that's a hide. A finance team that needs to prove an invoice existed six months after a customer "deleted" it needs an archive. A user invoking their right to erasure needs an actual erase.

There's a subtler modeling question underneath all three behaviors: once deleted_at is set, is that row still the same entity, or has it quietly become a different kind of thing — a tombstone that only exists to keep foreign keys intact?

Get this wrong and you end up with bugs like a "deleted" project still counting toward a plan's seat limit, or a soft-deleted user still triggering notifications. Our guide on what actually counts as an entity is worth revisiting here: a soft-deleted row often behaves differently enough from an active one that it deserves an explicit state in your model, not just a flag bolted onto the old one.

This is the same distinction that Jobs to Be Done thinking forces you to make explicit: the job "get this off my screen" is not the same job as "make sure this never surfaces again," even though both get triggered by the same click. Our Jobs to Be Done complete guide walks through separating the job the user is hiring a feature to do from the button that happens to trigger it — delete is a textbook case.

There's also an emotional dimension worth naming: the moment right after someone hits delete is one of the highest-anxiety points in your product, right up there with a failed payment. Whether they get a forgiving "Undo" toast or a cold, silent disappearance shapes trust far more than the feature itself. Our guide to the customer journey's emotion curve is a useful frame for deciding which delete moments deserve a soft landing and which don't.

The deleted_at Pattern: How Soft Delete Actually Works

The standard soft-delete pattern adds one nullable column, deleted_at, to a table: NULL means the row is active, and any timestamp means it was deleted (and precisely when). It's popular because it's a plain UPDATE instead of a destructive DELETE, and it preserves every foreign key pointing at that row.

ALTER TABLE projects
  ADD COLUMN deleted_at TIMESTAMPTZ NULL;

-- "delete"
UPDATE projects SET deleted_at = now() WHERE id = 42;

-- "restore"
UPDATE projects SET deleted_at = NULL WHERE id = 42;

A nullable timestamp beats a plain is_deleted boolean for one simple reason: it carries a when, not just a whether. That "when" is what makes retention jobs, restore windows, and audit trails possible in the first place. In practice, teams pair it with a couple of companion columns:

• deleted_by — which user or service account performed the deletion, for audit trails
• deletion_reason — free text or an enum, useful when support needs to explain "why is this gone" months later
• purge_after — an explicit date the row is scheduled for hard deletion, separate from when it was soft-deleted

This is foundational enough that it belongs in your data model from day one, not bolted on after the first support ticket about a vanished record. If you're building out the rest of your schema alongside this decision, our data modeling complete guide covers where deletion policy fits next to primary keys, normalization, and relationship design.

Why Not Just Move Deleted Rows to a Separate Table?

Some teams avoid deleted_at entirely by moving "deleted" rows into a shadow projects_archive table. It genuinely solves the query-filtering problem below, at the cost of duplicating your schema and every foreign key relationship. It's a reasonable choice for high-volume, append-only entities (logs, events) and a poor one for entities with active relationships (users, orders) that you'd need to reconstruct on restore.

The Query and Uniqueness Complications Nobody Mentions in the Design Review

The moment you add deleted_at, every query against that table needs a WHERE deleted_at IS NULL filter — and forgetting it once reintroduces deleted rows into a report, a search result, or someone else's dashboard. Worse, a UNIQUE constraint on a column like email now blocks a user from re-registering their own address, because the deleted row still occupies it.

The Query Problem

Martin Fowler's widely-read note on the SoftDelete pattern makes a sharp point: the risk isn't the delete operation itself, it's every future query someone writes without knowing the convention exists. A junior engineer six months from now, or a data analyst querying the warehouse directly, has no built-in reason to remember AND deleted_at IS NULL.

Common places this bites teams in practice:

1. Default scopes in ORMs. Rails' paranoia/discard gems and Laravel's SoftDeletes trait auto-filter deleted rows — until you drop to raw SQL or a reporting tool that bypasses the ORM entirely.
2. Joins across two soft-deletable tables. You need the filter on both sides of the join, or a deleted parent row can silently resurrect in a report via its still-active children.
3. Aggregate counts and analytics. "Total users" quietly becomes "total users ever created" unless every downstream dashboard and warehouse model re-applies the filter.
4. Index performance. A table that's 40% soft-deleted rows makes every "active only" query scan more data than it needs to; a partial index (WHERE deleted_at IS NULL) keeps it fast.

The Uniqueness Problem

A UNIQUE constraint assumes one active row per value. Once deleted rows stick around, that assumption breaks: a canceled account can't re-register with the same email, and a renamed project can't reuse a slug someone previously deleted. This is arguably the single most common soft-delete bug that reaches production.

PostgreSQL's own documentation recommends the partial index as the idiomatic fix, and it's worth setting up before launch rather than after the first duplicate-email support ticket:

CREATE UNIQUE INDEX users_email_active_uniq
  ON users (email)
  WHERE deleted_at IS NULL;

Cascading Soft Deletes to Child Records

A foreign key with ON DELETE CASCADE handles hard deletes for you automatically — soft delete gives you no equivalent, because the database has no idea deleted_at is supposed to mean "gone." When you soft-delete a project, its tasks, comments, and attachments don't follow along unless something explicitly makes them.

This matters most at restore time. If a user restores a soft-deleted project but its tasks were hard-purged last week by an unrelated cleanup job, the restore quietly produces a broken, empty project instead of the one they expected back. Two workable patterns:

• Cascade the soft delete explicitly, setting deleted_at on every dependent row inside the same transaction, so a restore later can reverse the exact same set.
• Cascade the check instead of the delete — treat a child row as effectively deleted whenever its parent is, via a view or computed column, without touching the child rows at all.

Neither is free: the first adds write amplification to every delete; the second adds a join to every read. Choose based on whether that entity is deleted or read more often.

GDPR, Retention, and When "Soft" Delete Becomes a Legal Liability

Under GDPR's right to erasure (Article 17) and similar rules like California's CCPA, "the user deleted it" is not a legal defense if the record is still sitting in your database with a timestamp on it. Regulators look at whether the data is actually irretrievable — not whether it's hidden from the app's UI.

GDPR requires erasure requests to be handled "without undue delay," generally interpreted as within one month, extendable by up to two further months for complex cases. The CCPA gives businesses roughly 45 days to fulfill a verified deletion request, with one 45-day extension available. Neither law cares that your app calls the record "deleted" if a deleted_at timestamp is the only thing that changed.

A soft-deleted row is, legally, still processed personal data. If an entity holds PII and is subject to erasure requests, deleted_at alone almost never satisfies the request on its own.

A few practical implications worth building into your data model directly:

• Separate the erasure path from the hide path. A user hiding their own comment and a user invoking a legal right to erasure should not share the same deleted_at UPDATE — the second one needs to actually null or hash PII columns (name, email, address) while possibly keeping a non-identifying row shell for accounting or fraud purposes.
• Borrow NIST's vocabulary. NIST SP 800-88, written for media sanitization, distinguishes clear, purge, and destroy — a useful ladder for deciding what "erased" must mean once you account for backups, search indices, logs, and any data warehouse or analytics copies.
• Decide what "erased everywhere" actually covers. Row-level erasure in your primary database is the easy 20%; backups, event streams, and third-party processors (email tools, analytics, support platforms) are the harder 80%, and regulators expect an answer for all of it.
• Look at how vendors productize the archive tier. Salesforce's Recycle Bin, for example, holds deleted records for a defined window (around 15 days) before permanent purge — a concrete example of turning "archive" into a real, time-boxed feature rather than an open-ended deleted_at with no purge job behind it.

Making the Delete Decision Per Entity, Not Per Database

There is no single right answer to "should this table soft-delete or hard-delete" — the right approach is to decide per entity, based on whether it holds PII, whether users expect an undo, and whether audit or compliance needs a durable trail. A financial transaction, a draft comment, and a user profile each deserve a different answer, even inside the same application.

A simple checklist, applied entity by entity:

1. Does this entity contain PII subject to erasure requests? If yes, plan a real erase path (nulling or hashing), not just a hide.
2. Do users expect to undo this themselves? If yes, soft delete with a visible restore UI — a trash view, not a silent flag.
3. Does compliance or audit need proof this existed? If yes, archive with an immutable trail, potentially event-sourced rather than mutated in place.
4. Does this table carry unique constraints that will collide? If yes, add the partial index or purge job before this ships, not after the first bug report.
5. Is "deleted" really one of several statuses, not a boolean at all? Entities that move through draft → active → archived → deleted often model better as a status field than a bolt-on timestamp — our piece on enum vs. lookup table for status fields walks through when a deleted_at column should really be one value in a broader status model instead.

This is exactly the kind of decision that's easy to skip when a team is moving fast, and expensive to retrofit once real user data is on the line — which is why it belongs on the entity relationship diagram itself, not buried three files deep in application code. Our take on why the data model is the product, and you should draw the ERD first argues the same point: decisions like this one are cheapest to get right before a single line of app code exists.

Where Prodinja Fits

This is also a decision you can make visible rather than implicit. In Prodinja's Data Modelling tool, you can add a deleted_at attribute directly on an entity and set its optionality — nullable versus required — and watch that choice flow straight into the generated SQL DDL. Instead of a soft-delete convention that lives only in a code comment or an engineer's memory, the decision sits in the schema artifact everyone on the team can see and review.

Key Takeaways

• "Delete" is three features, not one: hide (user-recoverable), archive (team-recoverable, audit-friendly), and erase (recoverable by no one). Decide which one you're actually building before you write a migration.
• The deleted_at nullable timestamp is the standard soft-delete pattern — it beats a plain boolean because it carries when, which is what makes restore windows and purge jobs possible.
• Every query against a soft-deletable table must filter deleted_at IS NULL, including joins, aggregates, and anything outside your ORM's default scope — this is where soft delete quietly gets expensive.
• Unique constraints break with soft delete unless you add a partial unique index (or an equivalent) scoped to active rows — plan this before launch, not after a duplicate-email ticket.
• GDPR and CCPA erasure requests are not satisfied by hiding a row. PII-bearing entities need a real erase path with a defined deadline (roughly one month under GDPR, roughly 45 days under CCPA).
• The decision belongs per entity, not per database — and it belongs on the schema itself, where the whole team can see it, rather than in a convention only one engineer remembers.