Soft Delete vs Temporal vs Events

Most systems eventually need history.

"Who changed this?"
"What did it look like last week?"
"Can we undo that?"

Three common answers:

Soft Delete          Temporal Tables          Event Sourcing
────────────         ───────────────          ──────────────
IsDeleted = true     System-versioned         Append-only events
                     tables

They solve different problems.

---

Quick Comparison

---

1. Soft Delete

Orders
─────────────────────────────
Id | Amount | IsDeleted | DeletedAt
1  | 100    | 0         | null
2  | 250    | 1         | 2026-09-20

How it works

public class Order
{
    public int Id { get; set; }
    public decimal Amount { get; set; }
    public bool IsDeleted { get; set; }
    public DateTime? DeletedAt { get; set; }
}

// Delete becomes an update
order.IsDeleted = true;
order.DeletedAt = DateTime.UtcNow;
await db.SaveChangesAsync();

With EF Core Global Query Filter

modelBuilder.Entity<Order>()
    .HasQueryFilter(o => !o.IsDeleted);

Every normal query automatically hides deleted rows.

Pros

• Extremely simple
• Easy to restore
• Works with any database

Cons

• History is limited (only “deleted or not”)
• Unique constraints become painful (Email + IsDeleted)
• Tables fill with dead rows
• No real “what did it look like on date X?”

When to use

Simple apps
Admin “hide” instead of hard delete
Low audit requirements

---

2. Temporal Tables (SQL Server)

Orders (current)
─────────────────────────────
Id | Amount | ValidFrom          | ValidTo
1  | 120    | 2026-09-01 10:00   | 9999-12-31

Orders History (auto)
─────────────────────────────
Id | Amount | ValidFrom          | ValidTo
1  | 100    | 2026-08-01 09:00   | 2026-09-01 10:00

SQL Server maintains the history automatically.

Enable it

CREATE TABLE Orders
(
    Id INT PRIMARY KEY,
    Amount DECIMAL(18,2),
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.OrdersHistory));

Query past state

-- What did order 1 look like on 15 Aug?
SELECT * FROM Orders
FOR SYSTEM_TIME AS OF '2026-08-15'
WHERE Id = 1;

With EF Core

modelBuilder.Entity<Order>().ToTable(tb => tb.IsTemporal());

Pros

• True time travel with almost no code
• Database guarantees consistency
• Great for audit (“who changed what and when”)
• Transparent to most application code

Cons

• SQL Server (and some other DBs) only
• History table grows
• Schema changes need care
• Not a full domain event log

When to use

Strong audit requirements
“What did this record look like last Tuesday?”
You already use SQL Server
Want history without changing domain model much

---

3. Event Sourcing

Events (append-only)
────────────────────────────────────────
Id | StreamId | Type            | Data              | Time
1  | Order-42 | OrderCreated    | { amount: 100 }   | ...
2  | Order-42 | AmountChanged   | { amount: 120 }   | ...
3  | Order-42 | OrderCancelled  | { reason: "..." } | ...

Current state is rebuilt by replaying events.

Events ──replay──▶ Order Aggregate (current state)

Mental model

Traditional          Event Sourcing
───────────          ──────────────
Store state          Store facts
Overwrite            Append only
“What is”            “What happened”

Pros

• Complete history of every change
• Natural audit + debugging
• Time travel is trivial
• Enables powerful patterns (projections, CQRS)

Cons

• Highest complexity
• Need event store + projections
• Schema evolution of events is hard
• Overkill for most CRUD apps

When to use

Complex domains (finance, logistics, workflows)
You already do CQRS
Need perfect audit + replay
Business cares about “why” not just “what”

---

Decision Guide

Need history?
      │
      ▼
Only “hide” records?
      │
 ┌────┴────┐
 Yes      No
  │        │
  ▼        ▼
Soft     Need real time travel?
Delete         │
          ┌────┴────┐
         Yes       No
          │         │
          ▼         ▼
     Temporal    Maybe just
     Tables      audit log table
          │
          ▼
     Complex domain +
     full event history?
          │
          ▼
     Event Sourcing

---

Side-by-Side Reality

---

Hybrid Patterns (Common in Real Systems)

Soft Delete + Temporal Tables
─────────────────────────────
Use soft delete for business “cancel”
Use temporal for technical audit

Temporal Tables + Audit Events
─────────────────────────────
Temporal for row history
Domain events for important business moments

CQRS + Event Sourcing (write) + Temporal (read)
────────────────────────────────────────────
Events for the write side
Temporal or projections for fast queries

---

Final Takeaway

Soft Delete        →  “Don’t show this anymore”
Temporal Tables    →  “Remember every version of this row”
Event Sourcing     →  “Remember every fact that ever happened”

Most systems should start with Soft Delete or Temporal Tables.
Move to Event Sourcing only when the domain actually needs it.