Soft Deletes Aren't Free: The True Cost of deleted_at

There's a moment in every product's life — usually within the first year — when someone deletes something important and asks, very politely, if you could un-delete it. And in that moment a decision gets made, often in a single standup: "let's just add a deleted_at column instead of really deleting." It feels prudent. It feels reversible. It feels free.

I want to walk through why it isn't free, because I've now spent a meaningful fraction of my career paying installments on that one nullable timestamp — and because the honest alternatives are better than their reputation.

The clause that colonizes your codebase

The core problem with soft deletes is disarmingly simple: the rows are still there. The database doesn't know they're "deleted" — that's a fiction maintained entirely by discipline. Which means every single query against the table, forever, must remember to say:

WHERE deleted_at IS NULL

Every query. The admin dashboard. The nightly export. The analyst's ad-hoc join. The new microservice reading the same database. The COUNT(*) in the investor metrics email. Miss it once and deleted users get a marketing blast, deleted orders inflate revenue, or a "removed" comment resurfaces in search. These bugs are quiet, embarrassing, and — my least favorite property — they're correct-looking. The query reads fine. It just answers a different question than the one you asked.

Rails folks reach for default_scope { where(deleted_at: nil) } (or a gem that does it), and it genuinely helps — until it doesn't. Default scopes leak into places you didn't intend (including, delightfully, into unscoped bugs where you needed the deleted rows and silently didn't get them), they don't apply to raw SQL, and they don't exist at all for the reporting tool, the data warehouse sync, or the intern with database credentials. The invariant lives in one application's ORM configuration, and your data has more readers than that.

The constraint casualties

Here's the cost that surprises teams the most: soft deletes quietly break your integrity toolkit.

Unique constraints stop meaning what they say. A user deletes their account; later they sign up again with the same email. The old row still exists, so UNIQUE (email) rejects the new signup. Congratulations, your unique index now enforces "no one may ever return." The fix is a partial index — and it's a genuinely good fix, worth knowing:

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

But notice what happened: uniqueness is now conditional, "delete then re-add" creates duplicate emails distinguishable only by a timestamp, and every future unique constraint on this table needs the same asterisk.

Foreign keys lose their meaning. Other tables still reference the "deleted" row — the database sees nothing wrong, because nothing was deleted. Is an order pointing at a soft-deleted customer an orphan? Valid history? A bug? The schema can no longer say; the answer lives in tribal knowledge, which is where answers go to die.

And the physical costs tick along: tables grow forever, indexes are padded with rows that 99% of queries must skip, backups and vacuums carry the dead weight, and — increasingly non-optional — privacy law asks you to actually delete personal data, at which point "deleted" rows that still exist become a compliance finding rather than a convenience.

One reviewing note for the current era: soft-delete implementations are a place where AI-generated code is reliably half right. Ask an assistant to "add soft deletes" and you'll typically get the column, the scope, and a tidy destroy override — and no partial unique index, no answer for foreign keys, no touch on the three raw-SQL reports that now silently include ghosts. The generated diff looks complete because the visible feature works. The review job is to ask about everything the diff didn't touch.

What are we actually trying to do?

Soft deletes usually stand in for one of three real requirements, and each has a more honest tool:

"We need undo." Then build undo: a short grace period (a scheduled_for_deletion_at and a background job that hard-deletes after 30 days), or a trash-can table the row moves to. Undo is a temporary state with an expiry — modeling it as a permanent row contradicts the requirement.

"We need history for audit or debugging." Then keep history, not corpses: an audit_logs or event table recording what was deleted, by whom, when, with a JSON snapshot. Auditors get a better answer than a flagged row ever gave them, and your live tables stay live.

"We need old data for analytics." Then archive: move rows to orders_archive (or the warehouse) at deletion time, in the same transaction. Analysts query the archive explicitly; production queries never see it; nobody needs a WHERE clause to tell the living from the dead.

class Order < ApplicationRecord
  def archive_and_destroy!
    transaction do
      ArchivedOrder.create!(source_id: id, payload: attributes,
                            archived_by: Current.user&.id)
      destroy!
    end
  end
end

The shared principle: separate the live data from the dead data structurally, instead of mixing them and filtering forever. Structure enforces itself; filters require perfect attendance.

Where soft deletes genuinely earn their keep

Fairness requires the other side. Soft deletes are a reasonable choice when the "deleted" state is a true domain state with visible behavior: content moderation where a removed post still shows a tombstone; anything with a user-facing trash can and restore button; short-lived flags awaiting a retention job. In those cases you aren't faking deletion — "removed but present" is the actual requirement, and a status column (often more honest than a bare timestamp: status = 'removed') models it well. Even then: partial unique indexes from day one, decided foreign-key semantics, and a written retention policy for when the rows really go.

What I push back on is soft delete as the default — applied to every table by gem configuration, as if deletion were always a mistake waiting to be regretted. Data lifecycle deserves the same design attention as data shape. Deciding how data dies is part of modeling how it lives.

Before you add that column

• deleted_at means every query on that table needs a filter, forever, in every tool and language that touches the database. Price that honestly.
• Unique constraints need partial indexes the moment soft deletes arrive — and re-signup flows need a real answer, not an error.
• Foreign keys to soft-deleted rows are a question your schema can no longer answer. Write down the answer somewhere that survives.
• Match the tool to the requirement: grace periods for undo, audit logs for history, archive tables for analytics.
• If "deleted but visible" is genuinely your domain, model it as an explicit status — and still schedule the day the rows actually leave.

Deletion isn't a failure mode to be engineered around. Done deliberately, it's one of the healthiest things a dataset can do.