We Added deleted_at to Every Table. Now Nothing Is Ever Really Deleted.

Kevin Tung

7 min read

·

Oct 5, 2026

--

1

Listen

Share

A deleted_at column quietly changes every query, unique constraint, and privacy promise. Decide why you keep deleted data, then pick partial indexes, views, purge jobs, or archive tables.

Press enter or click to view image in full size

It started with a reasonable postmortem action item.

A support engineer deleted the wrong customer account. The row was gone, the customer was furious, and restoring meant a painful point-in-time recovery into a scratch database followed by hand-copying rows back. The action item read: “Make deletes reversible.”

The fastest way to do that was a column. Add deleted_at, teach the ORM to append WHERE deleted_at IS NULL to every query, and make it policy that every new table gets the column too. It shipped in a sprint. For a few months, it felt like free insurance.

Then the bills started arriving, from places nobody had connected to that decision. A user deleted their account, tried to sign up again with the same email, and couldn’t. A revenue report written in raw SQL counted deleted orders. A batch job sent a marketing email to people who had deleted their accounts. And a privacy request asked us to delete someone’s data, and we realized we never actually deleted anything.

The details are a composite, but I’ve seen each of those scars in real systems. The column was cheap. What it did to the data model was not.

The Naive Pattern

In PostgreSQL, it usually looks like this:

ALTER TABLE users ADD COLUMN deleted_at timestamptz;
-- "delete"
UPDATE users SET deleted_at = now() WHERE id = $1;
-- every read, via the ORM's default scope
SELECT * FROM users WHERE id = $1 AND deleted_at IS NULL;

Several assumptions come along for free:

• Every reader goes through the ORM and its default scope.
• Unique constraints still mean what they meant before.
• Related rows will get handled somehow.
• Keeping data longer is always safer.
• Restoring is just setting deleted_at back to NULL.

What Production Actually Does

Somebody forgets the filter. Raw SQL, reports, background jobs, a second service reading the same database, or an “unscoped” call that a developer added for one admin screen. Deleted records turn up in revenue numbers, exports, search results, and emails. The readers most likely to skip the ORM (analytics, integrations, bulk jobs) tend to have the largest blast radius.

Uniqueness breaks. The deleted row still holds its email, slug, or username. A user can’t sign up again, and someone “fixes” it by renaming deleted emails to deleted-123-alice@example.com, which is now a second bug.

Children get orphaned. The user is soft-deleted, but their addresses and payment methods aren’t. They’re still reachable through a join or a child-level API that never checks the parent.

Foreign keys stop protecting you. Nothing is ever actually deleted, so ON DELETE CASCADE and RESTRICT never fire. You keep referential integrity on paper and lose logical integrity in practice.

Tables bloat. On a long-lived table, most of the rows can be dead. Every index carries them, scans read past them, and each useful index ends up needing a partial variant.

Erasure becomes a promise you can’t keep. Privacy regulations in many jurisdictions give people a right to have their data erased. A row with a timestamp on it hasn’t been erased. Neither have its copies in search indexes, caches, the warehouse, or backups.

Restore turns out to be undefined. Bring a user back, and do their projects come back too? What if another user has since taken their username? A naive “undo” can corrupt state, or fail silently, and end up worse than the original delete.

The naive model is “soft delete means reversible delete.” The model that holds up in production is different: soft delete adds a new lifecycle state, and every reader, constraint, and retention rule has to understand it.

Start From the Reason, Not the Column

The first question I ask in any “let’s add soft delete” review is why do you need to keep deleted data? The answer usually points to a better tool than deleted_at:

• Undo within a short window: time-bounded soft delete, around 30 days, with a scheduled hard purge.
• Audit (“who deleted what, and when”): an append-only audit log. Then hard delete the row.
• Legal hold or regulatory retention: a separate retention store with its own access controls and its own expiry.
• Analytics and history: warehouse snapshots or change data capture. Keep the transactional tables lean.
• Accidental bulk deletes: backups, point-in-time recovery, and guarded tooling for destructive operations.
• A real business state: if “deleted” actually means archived, closed, or cancelled, use a status column with that meaning. A cancelled subscription isn’t deleted. It’s cancelled.

Many requests for soft delete turn out to be one of the other items on that list.

If You Soft Delete, Make It Explicit

Sometimes a trash can with an undo window really is the feature. When it is, I want four things.

Partial unique indexes, so only live rows compete for unique values. In PostgreSQL, you replace the plain unique constraint with this:

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

Deleted rows drop out of the uniqueness check, so the same email can sign up again, while two live accounts still can’t share one.

A view for every reader outside the application. An ORM scope protects only the code that uses the ORM. Reports, jobs, and other services should read a view and lose access to the base table entirely:

CREATE VIEW active_users AS
  SELECT id, email, name, created_at
  FROM users
  WHERE deleted_at IS NULL;
GRANT SELECT ON active_users TO reporting;
REVOKE ALL ON users FROM reporting;

List the columns explicitly. A SELECT * in a view is expanded when the view is created, so new columns won't appear automatically anyway. Being explicit also keeps fields like deleted_at out of reports.

A purge schedule. Soft delete without a purge job is indefinite retention that nobody approved. Hard-delete rows once the restore window passes. PostgreSQL’s DELETE has no LIMIT, so batch it through a subquery and keep every transaction short:

func purgeDeleted(ctx context.Context, db *sql.DB, window time.Duration) (int64, error) {
    const q = `
DELETE FROM users
WHERE id IN (
  SELECT id FROM users
  WHERE deleted_at < now() - make_interval(secs => $1)
  ORDER BY deleted_at
  LIMIT 1000
)`
    var total int64
    for {
        if err := ctx.Err(); err != nil {
            return total, err // stop cleanly between batches
        }
        res, err := db.ExecContext(ctx, q, window.Seconds())
        if err != nil {
            return total, fmt.Errorf("purge batch: %w", err)
        }
        n, err := res.RowsAffected()
        if err != nil {
            return total, fmt.Errorf("rows affected: %w", err)
        }
        total += n
        if n == 0 {
            return total, nil // nothing left past the window
        }
    }
}

Back it with a partial index on deleted_at WHERE deleted_at IS NOT NULL so each batch can find its rows without scanning the whole table. Children need to be purged first, or covered by ON DELETE CASCADE.

Defined restore semantics for each entity: what comes back with it, what happens when its unique values have been taken, and who’s allowed to restore it.

The Archive Table Pattern

For most business entities, I’d rather not keep dead rows in live tables at all. When something is deleted, move it and its children into archive tables in the same transaction, then hard-delete them from the live tables:

-- users_archive: LIKE users, plus archived_at and archived_by
BEGIN;
WITH moved AS (
  DELETE FROM addresses WHERE user_id = $1 RETURNING *
)
INSERT INTO addresses_archive
SELECT moved.*, now(), $2 FROM moved;
WITH moved AS (
  DELETE FROM users WHERE id = $1 RETURNING *
)
INSERT INTO users_archive
SELECT moved.*, now(), $2 FROM moved;
COMMIT;

DELETE ... RETURNING inside a CTE moves exactly the rows that were deleted, with no window between reading and deleting. What you get:

• Live tables stay small, and their constraints keep their original meaning.
• No reader can forget a filter, because there isn’t one.
• Restore becomes an explicit operation with explicit conflict handling.
• Archives get their own retention, access controls, and purge job.

The cost is a second set of tables that has to track the live schema. When users gains a column, users_archive needs it too, so put that check in your schema review.

Erasure Is a Pipeline, Not an UPDATE

A real “delete my data” request has to reach every copy: live tables, archives, search indexes, caches, the warehouse, logs where that’s feasible, and backups. Backups are usually handled through expiry, or by encrypting per-user data with keys you can destroy. Track each request as a job with steps and a completion status, and you’ll be able to answer “is it actually gone?” honestly. I’ll stay general on the legal specifics, because they vary by jurisdiction and your counsel should own them. The engineering obligation is the same everywhere: know where every copy lives.

Rules of Thumb

• Ask why you’re keeping deleted data before you decide how.
• If “deleted” has business meaning, it’s a status, not deleted_at.
• Soft delete without a purge job is retention by accident.
• Every unique constraint on a soft-deleted table should become a partial unique index.
• An ORM scope can’t protect readers who don’t use the ORM. Give them a view.
• Archive tables keep live tables honest.
• Restore is a feature with semantics, not an UPDATE back to NULL.
• An audit log answers “who deleted it” without keeping the row alive.
• Erasure has to reach every copy, including the ones in other systems.

Discussion

How many readers of your soft-deleted tables bypass the ORM, and are you sure every one of them filters deleted rows? Has a “restore” ever done more damage than the original delete?

A deleted_at column doesn't make deletes safe. It makes them invisible, to some readers, some of the time. Decide why you're keeping the data, then choose a mechanism that tells the truth.