Right, let’s talk about the great “delete-but-not-really” debate. You’re not a monster; you don’t want to permanently nuke user data. Maybe it’s for legal compliance (GDPR, CCPA), maybe it’s for audit trails, or maybe you’ve just learned the hard way that users have a hilarious tendency to beg for their data back six months after angrily telling you to delete everything. So we implement soft deletes. But how? There are two main camps, and they’re at war in the corridors of every engineering department.

The most common pattern you’ll see is the deleted_at column. The idea is beautifully simple: instead of DELETE FROM users WHERE id = 123, you UPDATE them, setting a deleted_at timestamp. It’s the database equivalent of moving a file to the trash bin instead of hitting shift-delete.

-- Instead of this violence:
DELETE FROM users WHERE id = 666;

-- You do this civilized update:
UPDATE users
SET deleted_at = NOW()
WHERE id = 666;

To get your active records, you just filter out the “deleted” ones in every single query. This is the first gotcha, and it’s a doozy.

-- You must ALWAYS remember this. Forget once and it's a bad day.
SELECT * FROM users WHERE deleted_at IS NULL;

The deleted_at Quagmire

The deleted_at pattern is seductive because it’s so easy to bolt onto an existing system. But it introduces a massive cognitive load. Every query, everywhere, against that table now needs to account for the soft delete state. Every join, every application function, every reporting script. You will forget. I have forgotten. The universe guarantees it.

This also plays horribly with uniqueness constraints. Need to ensure user emails are unique? Tough luck. User “alice@example.com” deletes her account, and now a new “alice@example.com” can’t sign up because the old, “deleted” one still lurks in the table, violating the unique constraint. The workaround is ghastly: you have to create partial indexes that only consider non-deleted rows, which is PostgreSQL’s specialty but can be a nightmare elsewhere.

-- PostgreSQL example to allow unique emails only for non-deleted users
CREATE UNIQUE INDEX idx_users_unique_email ON users (email)
WHERE deleted_at IS NULL;

The Archival Table Alternative

The other school of thought says, “If it’s deleted, it shouldn’t be in the same table.” This approach uses a dedicated archival table. The delete operation becomes a transactional two-step: INSERT into the users_archive table, then DELETE from the main users table.

BEGIN;

-- Copy the soon-to-be-deleted record to the archive
INSERT INTO users_archive (id, email, name, deleted_at)
SELECT id, email, name, NOW()
FROM users
WHERE id = 666;

-- Then, actually delete it from the main table
DELETE FROM users WHERE id = 666;

COMMIT;

The beauty of this is your main table remains pristine. All your queries stay simple; no more WHERE deleted_at IS NULL littered everywhere. Uniqueness constraints just work. Query performance is often better because the main table is smaller and doesn’t need to maintain indexes for a bunch of dead data.

The downside? It’s more complex to set up. You need to manage the archive table schema, and any process that truly deletes the record is now two operations. It also makes restoring a record a bit more involved than just setting a timestamp to NULL.

The Restoration Conundrum

How you restore a record tells you everything about which pattern you’ve chosen. With deleted_at, it’s trivial:

UPDATE users SET deleted_at = NULL WHERE id = 666;

Done. With an archival table, it’s a reverse ETL operation:

BEGIN;
INSERT INTO users (id, email, name)
SELECT id, email, name FROM users_archive WHERE id = 666;
DELETE FROM users_archive WHERE id = 666;
COMMIT;

It’s more code, but it’s also more explicit and auditable. The archive table becomes a perfect, append-only log of deletions.

The Verdict: It Depends (Sorry)

For net-new projects where data integrity is paramount, I lean heavily towards archival tables. The mental model is cleaner, and it enforces discipline from the start. The deleted_at pattern is a pragmatic patch for existing systems, a debt you incur knowing that the interest payments are all those potentially buggy queries.

Whichever you choose, for the love of all that is holy, abstract it away. Don’t let your application code raw-dog UPDATE statements everywhere. Build a data access layer or use an ORM scope that automatically applies the deleted_at IS NULL filter. Make the soft delete a single method call, like user.soft_delete(), that handles the archival logic. Your future self, desperately debugging at 2 AM, will thank present-you for this one merciful act.