Soft Deletes & Audit Trails

Use Case: Soft Deletes & Audit Trails

The everyday problem: A user deletes an order, an admin removes a customer, a record is gone. Then: "can we undo that?", "who deleted this and when?", "what did this record look like last Tuesday?", "the auditors need a history of all changes to financial records". A hard DELETE destroys the data irrecoverably and leaves no trace of who did what. Many businesses cannot afford that — for recovery, for accountability, for compliance (GDPR, SOX, HIPAA all touch on this), or simply for debugging "how did this record get into this state?". The challenge is removing records without truly destroying them (so they can be recovered and excluded from normal views) and recording who changed what, when, and how — without these mechanisms corrupting your queries, leaking deleted data, or bloating the system.

Five approaches, spanning soft deletion (marking instead of removing) and audit trails (recording changes). They are often combined — soft delete is itself a form of audit — but address distinct needs.

---

Way 1: Soft Delete with a Flag + Global Query Filter

Best used for: The common case of "deleted records should disappear from the app but remain recoverable" — orders, users, content — where a simple flag plus automatic filtering keeps deleted rows out of normal queries.

The concept: Instead of deleting the row, you set an IsDeleted flag (and usually a DeletedAt timestamp). A global query filter in EF Core then automatically excludes flagged rows from every query, so the rest of your code behaves as if the record is gone — without you remembering to add WHERE IsDeleted = false everywhere.

// The entity carries soft-delete metadata
public class Order
{
    public int Id { get; set; }
    public bool IsDeleted { get; set; }
    public DateTimeOffset? DeletedAt { get; set; }
    public string? DeletedBy { get; set; }
}

// Global query filter — applied automatically to EVERY query for this entity
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Order>().HasQueryFilter(o => !o.IsDeleted);
    // Now _db.Orders.ToList() silently excludes soft-deleted rows everywhere.
}

// "Delete" = set the flag (intercept SaveChanges to make this automatic)
public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    foreach (var entry in ChangeTracker.Entries<Order>().Where(e => e.State == EntityState.Deleted))
    {
        entry.State = EntityState.Modified; // convert the delete into an update
        entry.Entity.IsDeleted = true;
        entry.Entity.DeletedAt = DateTimeOffset.UtcNow;
        entry.Entity.DeletedBy = _currentUser.Id;
    }
    return await base.SaveChangesAsync(ct);
}

// Recover a soft-deleted record, or query including deleted ones when needed:
var includingDeleted = await _db.Orders.IgnoreQueryFilters()
    .Where(o => o.Id == id).FirstAsync(ct);

Why it works: The global query filter is the key — it applies the IsDeleted exclusion automatically to every query, so soft-deleted rows vanish from the application's view without scattering filter conditions throughout the codebase (and without the risk of forgetting one and leaking deleted data). Intercepting SaveChanges turns ordinary Remove() calls into flag-setting updates, so existing delete code keeps working but no longer destroys data.

Benchmark note: Soft delete via flag + global filter is the standard .NET approach and works cleanly, but it has well-known sharp edges. Unique constraints: a unique index on, say, Email now conflicts when you soft-delete a user and someone re-registers with that email — the deleted row still occupies the unique value. Fix with a filtered unique index (WHERE IsDeleted = false) so only live rows are constrained. Foreign keys and cascades: soft-deleting a parent does not soft-delete children automatically — you must handle the cascade yourself, or you get orphaned-looking data. Query filters and joins: the filter applies per entity, so a join can still surface a deleted related row unless that entity is also filtered, and filters can be accidentally bypassed. Performance: the table keeps growing with dead rows, so heavily-deleted tables benefit from a partial index on the filter or periodic archival of old soft-deleted rows. Used with these caveats handled, it is the right default; ignored, they cause subtle data leaks and constraint failures.

---

Way 2: Audit Columns (who/when on every row)

Best used for: Basic accountability on every record — capturing created-by/created-at and modified-by/modified-at — answering "who last touched this and when?" with minimal infrastructure.

The concept: Every auditable entity carries four columns — CreatedAt, CreatedBy, ModifiedAt, ModifiedBy — populated automatically by intercepting SaveChanges. It does not keep history (only the latest values), but it answers the most common accountability questions cheaply and is the foundation more complete auditing builds on.

// A base type or interface for auditable entities
public interface IAuditable
{
    DateTimeOffset CreatedAt  { get; set; }
    string?        CreatedBy  { get; set; }
    DateTimeOffset? ModifiedAt { get; set; }
    string?        ModifiedBy  { get; set; }
}

// Populate audit columns automatically in SaveChanges — no per-entity code
public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    var now  = DateTimeOffset.UtcNow;
    var user = _currentUser.Id ?? "system";

    foreach (var entry in ChangeTracker.Entries<IAuditable>())
    {
        switch (entry.State)
        {
            case EntityState.Added:
                entry.Entity.CreatedAt = now;
                entry.Entity.CreatedBy = user;
                break;
            case EntityState.Modified:
                entry.Entity.ModifiedAt = now;
                entry.Entity.ModifiedBy = user;
                break;
        }
    }
    return await base.SaveChangesAsync(ct);
}

Why it works: Centralising audit-column population in SaveChanges means every insert and update is stamped automatically and consistently — no developer has to remember to set these fields, and they cannot be forgotten or set inconsistently. It answers "who created/last-modified this and when" for every row at almost zero cost.

Benchmark note: Audit columns are the cheapest, most universally useful auditing — four columns and one interceptor give you basic accountability everywhere, and they cost essentially nothing (a few bytes per row, set during the save you were doing anyway). Their limitation is that they keep only the current state's metadata, not history: you know who last modified a record, but not what it looked like before, nor the sequence of changes. For "who last touched this" that is sufficient; for "show me every change over time" or "what did this look like on a past date" you need a full change history (Way 3) or temporal tables (Way 4). The _currentUser dependency requires flowing the acting user into the DbContext (via a scoped service reading the HTTP context), and falling back to "system" for background operations. Start here; layer deeper auditing where compliance or debugging demands it.

---

Way 3: Full Change-History Audit Table

Best used for: Compliance and detailed accountability — recording every change to tracked entities as separate history records: what changed, from what to what, by whom, when. Required in regulated domains (finance, healthcare).

The concept: On every save, you write an audit record capturing the operation (insert/update/delete), the entity and key, the old and new values (often as JSON), the user, and the timestamp — into a dedicated audit table. This produces a complete, queryable history of all changes, independent of the live data, satisfying "prove every change to this record" requirements.

public class AuditEntry
{
    public long   Id          { get; set; }
    public string EntityType  { get; set; } = "";
    public string EntityKey   { get; set; } = "";
    public string Action      { get; set; } = ""; // Insert / Update / Delete
    public string? OldValues  { get; set; }        // JSON snapshot before
    public string? NewValues  { get; set; }        // JSON snapshot after
    public string  ChangedBy  { get; set; } = "";
    public DateTimeOffset ChangedAt { get; set; }
}

// Capture changes from the ChangeTracker during SaveChanges
public override async Task<int> SaveChangesAsync(CancellationToken ct = default)
{
    var audits = new List<AuditEntry>();
    var now  = DateTimeOffset.UtcNow;
    var user = _currentUser.Id ?? "system";

    foreach (var entry in ChangeTracker.Entries()
        .Where(e => e.State is EntityState.Added or EntityState.Modified or EntityState.Deleted)
        .Where(e => e.Entity is not AuditEntry)) // don't audit the audit table
    {
        var oldValues = entry.State != EntityState.Added
            ? JsonSerializer.Serialize(entry.OriginalValues.Properties
                .ToDictionary(p => p.Name, p => entry.OriginalValues[p]))
            : null;
        var newValues = entry.State != EntityState.Deleted
            ? JsonSerializer.Serialize(entry.CurrentValues.Properties
                .ToDictionary(p => p.Name, p => entry.CurrentValues[p]))
            : null;

        audits.Add(new AuditEntry
        {
            EntityType = entry.Entity.GetType().Name,
            EntityKey  = entry.Properties.First(p => p.Metadata.IsPrimaryKey()).CurrentValue?.ToString() ?? "",
            Action     = entry.State.ToString(),
            OldValues  = oldValues,
            NewValues  = newValues,
            ChangedBy  = user,
            ChangedAt  = now
        });
    }

    var result = await base.SaveChangesAsync(ct);
    if (audits.Count > 0) { AuditEntries.AddRange(audits); await base.SaveChangesAsync(ct); }
    return result;
}

Why it works: Capturing old and new values from EF Core's change tracker on every save produces a complete, independent record of what changed, how, by whom, and when — a true audit trail that can be queried to reconstruct the full history of any record and satisfy compliance demands for change accountability. Because it reads the change tracker, it captures changes automatically regardless of which code path made them.

Benchmark note: A full audit table is what regulated industries require, and it answers questions audit columns cannot: the complete change history, point-in-time reconstruction, and "who changed field X from A to B on this date". The costs are real: it roughly doubles writes (every change also writes an audit record), the audit table grows large and fast (plan partitioning and archival from day one), and serializing values adds overhead. Decide deliberately what to audit — auditing every table including high-churn logging tables is wasteful; audit the entities where accountability matters (financial, personal, security-relevant data). Storing values as JSON is flexible but makes querying specific field changes harder; some designs store per-field change rows instead for queryability at the cost of volume. This is the workhorse of compliance auditing, but scope it to where it earns its cost.

---

Way 4: Temporal Tables (database-native history)

Best used for: Automatic, complete row-versioning maintained by the database itself — point-in-time queries ("what did this row look like on a past date") without writing any audit code — where your database supports it (SQL Server temporal tables; PostgreSQL via extensions/triggers).

The concept: SQL Server system-versioned temporal tables automatically keep a full history of every row version in a linked history table, maintained by the database engine on every change. You query the current data normally, and use special FOR SYSTEM_TIME syntax to see the data as it was at any past moment — all without application code writing history.

-- SQL Server: a system-versioned temporal table maintains history automatically
CREATE TABLE Products
(
    Id        INT PRIMARY KEY,
    Name      NVARCHAR(200) NOT NULL,
    Price     DECIMAL(18,2) NOT NULL,
    -- Period columns the engine manages:
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.ProductsHistory));
-- Every UPDATE/DELETE automatically writes the prior version to ProductsHistory.

-- Point-in-time query: what did products look like last Tuesday?
SELECT * FROM Products FOR SYSTEM_TIME AS OF '2024-06-04T00:00:00';

-- Full history of one product over a period:
SELECT * FROM Products
    FOR SYSTEM_TIME BETWEEN '2024-01-01' AND '2024-12-31'
    WHERE Id = 42 ORDER BY ValidFrom;

// EF Core maps temporal tables — query history with LINQ (SQL Server provider):
var asOf = await _db.Products
    .TemporalAsOf(new DateTime(2024, 6, 4))
    .ToListAsync(ct);

var history = await _db.Products
    .TemporalAll()
    .Where(p => p.Id == 42)
    .OrderBy(p => EF.Property<DateTime>(p, "ValidFrom"))
    .ToListAsync(ct);

Why it works: The database engine maintains the complete version history automatically and atomically with each change — there is no application code to write, no SaveChanges interception, and no risk of a code path bypassing the audit (because the database enforces it at the storage level). Point-in-time queries reconstruct any past state directly, which application-level auditing can only approximate.

Benchmark note: Temporal tables are the most robust history mechanism because the database guarantees it — history cannot be missed, bypassed, or inconsistently captured, and point-in-time queries are first-class. For "what did this look like at time T" and complete row history, nothing application-level matches it. The trade-offs: it is database-specific (SQL Server has it natively; PostgreSQL needs extensions or trigger-based approaches; not portable across providers), the history table grows continuously (set a retention policy — SQL Server supports automatic history cleanup), it captures row versions but not the who and why by default (you still need a user/reason column, often combined with audit columns from Way 2 to record who made each change), and it adds storage and some write overhead. Where you are on a supporting database and need bulletproof history with point-in-time queries, temporal tables are excellent; combine with audit columns to capture the actor alongside the engine-maintained versions.

---

Way 5: Event Sourcing (the changes ARE the data)

Best used for: Domains where the history of changes is the core asset and you want the complete sequence of events as the source of truth — financial ledgers, order lifecycles, anything where "how did we get to this state" is as important as the state itself. The most powerful and most demanding approach.

The concept: Instead of storing current state and auditing changes to it, you store the sequence of events that happened (OrderPlaced, ItemAdded, OrderShipped) as the primary data. Current state is derived by replaying the events. The audit trail is not an add-on — it is the data model, giving a perfect, immutable history by construction.

// Events are the source of truth — append-only, never updated or deleted
public abstract record OrderEvent(Guid OrderId, DateTimeOffset OccurredAt, string By);
public record OrderPlaced(Guid OrderId, DateTimeOffset OccurredAt, string By, int CustomerId) : OrderEvent(OrderId, OccurredAt, By);
public record ItemAdded(Guid OrderId, DateTimeOffset OccurredAt, string By, int ProductId, int Qty) : OrderEvent(OrderId, OccurredAt, By);
public record OrderShipped(Guid OrderId, DateTimeOffset OccurredAt, string By, string Tracking) : OrderEvent(OrderId, OccurredAt, By);

// Append events to the store (never mutate existing ones)
public async Task AppendAsync(OrderEvent evt, CancellationToken ct)
    => await _eventStore.AppendAsync(evt.OrderId, evt, ct);

// Current state is REBUILT by replaying the event history
public Order Rebuild(IEnumerable<OrderEvent> events)
{
    var order = new Order();
    foreach (var e in events.OrderBy(e => e.OccurredAt))
    {
        order = e switch
        {
            OrderPlaced p  => order with { Id = p.OrderId, CustomerId = p.CustomerId, Status = "Placed" },
            ItemAdded   a  => order with { Items = order.Items.Append(new(a.ProductId, a.Qty)).ToList() },
            OrderShipped s => order with { Status = "Shipped", Tracking = s.Tracking },
            _ => order
        };
    }
    return order; // the current state, derived from full history
}
// The complete, immutable audit trail is inherent — every change is an event, forever.

Why it works: When events are the source of truth, the audit trail is perfect and automatic because the events are the record — every change that ever happened is stored immutably, current state is just a projection of them, and you can reconstruct the state at any past point by replaying events up to that moment. Nothing can be changed without a new event, so the history is complete and tamper-evident by design.

Benchmark note: Event sourcing gives the most complete and powerful history possible — perfect audit, time-travel to any past state, and the ability to derive new projections from old events — which is why it suits ledgers and lifecycle-critical domains. But it is the most demanding approach by a wide margin: it is a fundamentally different architecture, not a feature you bolt on. Rebuilding state by replaying events needs snapshots for performance once event counts grow (replaying thousands of events per read is slow). Querying current state requires maintaining read-model projections (often via CQRS), adding eventual consistency. Schema evolution is hard (old events were serialized in an old shape you must keep deserializing forever). The learning curve and operational complexity are significant. Adopt event sourcing only where the event history is genuinely central to the domain and the team is prepared for the model — for most applications needing "soft delete plus an audit trail", Ways 1–4 deliver what is needed at a fraction of the cost. It is the most capable and the least appropriate as a default.

---

Choosing the right approach

• Recoverable deletes, hide from normal views → Soft delete flag + global filter (Way 1)
• Basic "who/when" accountability everywhere → Audit columns (Way 2)
• Full change history for compliance → Audit table capturing old/new values (Way 3)
• Automatic row history + point-in-time queries → Temporal tables (Way 4, DB-dependent)
• History is the core domain asset → Event sourcing (Way 5) — powerful but demanding

The decision spine: these stack by how much history you need. For recoverable deletion, soft-delete with a global query filter (Way 1) — handling its sharp edges (filtered unique indexes, cascades, growth). For "who last touched this", add audit columns (Way 2) — cheap and universal. For "prove every change" compliance, add a change-history audit table (Way 3). For automatic, database-guaranteed row history with time-travel queries, temporal tables (Way 4) if your database supports them — combined with audit columns to capture the actor. And only where the change history is the domain (ledgers, lifecycle-critical records) and the team can take on the complexity, event sourcing (Way 5). Most applications land on a combination of Ways 1–3, which together provide recoverable deletes, basic accountability, and a compliance-grade audit trail without the heavy machinery. Two rules throughout: scope auditing to where it earns its cost (auditing everything is wasteful), and flow the acting user into the DbContext so the "who" is always captured. The throughline connects to earlier use cases — like soft delete's interaction with unique constraints (validation/idempotency) and audit volume (large writes) — reinforcing that these patterns rarely live in isolation.

Note: this is an informational engineering overview. Auditing and data-retention approaches must align with your legal and compliance obligations (e.g. GDPR right-to-erasure can conflict with indefinite audit retention — seek guidance). Always validate soft-delete filters and audit completeness against your own requirements.