Balancing Soft Delete with Data Consistency

You add a column like isDeleted, flip it to true, and suddenly life feels safer. No data loss. Easy rollback. No angry product manager asking why something is gone forever.

At first, it works beautifully.

Until your data starts depending on other data.

That’s when soft delete stops being a convenience and starts quietly breaking things. Not with errors or crashes, but with something worse: incorrect history.

The real problem isn’t that records are deleted. It’s that mutable data is being treated as historical truth.

I ran into this while working on a simple-looking system. Dormitories, rooms, beds, bookings. Nothing fancy. But one small change exposed a flaw that soft delete alone cannot fix.

This article is not about how to implement soft delete. It’s about what happens after you do.

The Illusion of Safety

Soft delete feels like the responsible choice.

Instead of removing rows from the database, you keep them around. You tell yourself this is safer. If something goes wrong, you can always bring the data back. Audits become easier. Mistakes feel reversible.

From a code perspective, it’s almost too easy:

isDeleted Boolean @default(false)

Then you update your queries to filter out deleted records, and you move on.

Most of the time, this works. Especially when your data is flat or loosely connected.

The illusion starts when you believe that keeping records automatically preserves correctness.

Soft delete protects existence, not meaning.

A deleted record still exists in the database, but the moment you stop treating it as part of the active system, you’ve changed how the rest of your data behaves. Relationships don’t disappear. They just become awkward.

And the more your data model relies on relationships, the faster this illusion breaks.

You won’t notice it during development.
You won’t see it in unit tests.
You’ll see it when someone asks a very simple question:

“Why does this old record look wrong?”

That’s usually when you realize something deeper is broken.

Where Things Start to Break

Soft delete works best when records are independent.

A user gets deleted.
A tag gets deleted.
A draft gets deleted.

No other piece of data needs them to explain the past.

Problems begin when records stop being standalone and start representing structure.

Structure is different. Other data relies on it to make sense.

Orders rely on products.
Bookings rely on rooms.
Seats rely on flights.
Beds rely on dormitories.

When you soft delete something structural, you’re not just hiding a row. You’re changing the shape of the system that older data was built on.

This is where data consistency starts leaking.

At first, it’s subtle. A missing label here. A null value there. A join that suddenly returns less data than expected.

So you patch it.

You add conditional checks. You show placeholders. You ignore deleted records in reports.

But none of this fixes the core issue. It only hides it.

Because historical data is still asking a very simple question:

“What did the world look like at the time this happened?”

And your system can no longer answer that honestly.

This becomes painfully clear when a structural change is made for a perfectly valid reason.

Which brings me to the dormitory problem.

The Dormitory Problem

Imagine a dormitory with 15 beds.

Each bed is a record in the database. Bookings reference beds. Everything is clean and relational.

Over time, a few guests stay in beds 13, 14, and 15. Their bookings are completed, paid for, and archived. This is now historical data.

Later, an admin decides to reduce the dormitory capacity to 10 beds. Maybe the room layout changed. Maybe regulations changed. It’s a valid business decision.

To reflect this, beds 11–15 are soft deleted.

No rows are removed. isDeleted is set to true. The system still “has” the data.

But now ask a simple question:

“What happens to the old bookings?”

Those bookings still point to beds 13, 14, and 15. Beds that no longer exist in the active system.

On the surface, nothing crashes.

But cracks start to show:

Booking history pages can’t display bed details properly
Reports skip deleted beds and show incomplete data
Admins see “missing” information and lose trust in the system
Developers add more conditions to patch the UI

The bookings are correct.
The beds existed.
The stays happened.

Yet the system can no longer describe that reality accurately.

That’s the real failure.

Why Soft Delete Isn’t the Real Problem

It’s tempting to blame soft delete here. But soft delete is just the messenger.

The real issue is treating mutable structure as historical truth.

Dormitory capacity is configuration.
Bed availability is configuration.
Room layout is configuration.

Bookings are not.

A booking is a fact. It already happened. It should never depend on something that can change later.

When a booking depends on live structural data, you’re asking the present to explain the past. And the present has already moved on.

Soft delete didn’t break your data.
Your mental model did.

Better Ways to Model This

There’s no single “correct” solution, but there are safer patterns.

1. Never Truly Remove Structural Entities

Beds don’t disappear. They become inactive.

You stop assigning them to new bookings, but you never treat them as gone. Historical records can still reference them without hacks or fallbacks.

This keeps joins simple and history intact.

2. Version Your Structure

Instead of one mutable dormitory, you create versions.

Bookings reference the version that was active at the time of booking. Capacity changes create a new version, not a rewrite of reality.

This is powerful, but heavier to implement.

3. Snapshot What Matters

For historical records, store what you actually need.

Instead of depending on joins:

Store bed number
Store room name
Store capacity at the time

This accepts duplication in exchange for truth.

History becomes self-contained.

The Rule I Follow Now

I don’t decide soft delete strategies first anymore.

I ask one question:

“Can this data change after something depends on it?”

If the answer is yes, history should not rely on it.

A few simple rules guide me now:

Historical records must survive structural changes
Soft delete is about visibility, not correctness
If data explains the past, freeze it or snapshot it

Closing Thoughts

Soft delete solves deletion.

It does not solve meaning.

If your system cannot explain what happened yesterday after a change today, the problem isn’t missing rows. It’s broken history.

And no amount of isDeleted = true can fix that.