Why do we even need Soft Deletes?

Before we talk about how, let’s talk about why.

We’ve all been there. A user clicks Delete, realizes they made a huge mistake, and frantically calls support. If you did a Hard Delete (actually DELETE FROM table), that data is gone. The only hope is a messy database backup restoration that might be hours old.

Soft Deletes are your safety net. They are the database equivalent of the “Recycle Bin” on your desktop. The data is hidden from the user, but it’s still sitting there in the DB, waiting to be rescued.

The Old Way

For a long time we use to implement soft deletes like this:

{
  "name": "John Doe",
  "isDeleted": true,
  "deletedAt": "2023-10-27T10:00:00Z"
}

We used to add a boolean (isDeleted) and a timestamp (deletedAt).

Honestly? This is redundant.

Having two fields that track the same state is asking for trouble. It creates the possibility of “impossible states”—like having isDeleted: false but a valid date in deletedAt. Which one do you trust?

The Nullable Timestamp

My current approach is to drop the boolean entirely. The deletedAt field is the single source of truth.

  • Active Record: deletedAt is null.
  • Deleted Record: deletedAt has a date.

The query for active items changes from checking a boolean to just checking existence: Query: “Give me everything where deletedAt is null.”

The “Restore” Dilemma

I often see developers overthink the reverse operation. “If I un-delete this, do I need a restoredAt field? Do I keep the old deletedAt for history?”

My advice is to Keep the operational state simple.

If you want to restore a record, don’t add a restoredAt field. That just clutters your document with zombie data. To restore a record, simply set deletedAt back to null.

It brings the record back to the “Active” state immediately. It’s clean and simple.

“But I need the history!”

If you reset deletedAt to null, you lose the evidence that the user ever deleted the account.

The golden rule is to not use your operational table as an audit log.

If you need to know who deleted it, when they restored it, and why, that belongs in a separate History Collection or Audit Table. This is a separate topic and will be covered in a later post.

The MongoDB Implementation

In MongoDB, we keep the main collection fast by using a Partial Index. This means we only index the active records (where deletedAt is null). This keeps the index tiny and the queries fast.

// Only index the active records
db.users.createIndex({ email: 1 }, { partialFilterExpression: { deletedAt: null } });

Then, you stream the history into a separate collection (like audit_logs). You can use a database trigger or handle this in your application code.

Appendix: The Postgres Approach

If you are on SQL, the logic is identical. Avoid the boolean column.

1. The Table Add a nullable timestamp.

ALTER TABLE users ADD COLUMN deleted_at TIMESTAMP WITH TIME ZONE DEFAULT NULL;

2. The Performance Trick Just like Mongo, Postgres lets you index only the active rows. This is much faster than indexing a boolean column where 99% of rows are “false.”

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

3. The Restore Simple and straightforward.

UPDATE users SET deleted_at = NULL WHERE id = 123;