temporal-tables-setup

Temporal Tables / Auditing Setup

Add change history to entities — either SQL Server temporal tables (automatic, queryable point-in-time) or an application-level audit log (DB-agnostic, captures who/what) — depending on the database and requirements.

Input — point it at your project

Works on a target: a file, folder, project, or GitHub URL. Detect provider with grep -rn "UseSqlServer\|UseNpgsql" --include=*.cs <target>. SQL Server → temporal tables are the cleanest; Postgres/others → interceptor-based audit log.

Option A — SQL Server temporal tables

modelBuilder.Entity<Order>().ToTable("Orders", t => t.IsTemporal(tt =>
{
    tt.HasPeriodStart("ValidFrom");
    tt.HasPeriodEnd("ValidTo");
    tt.UseHistoryTable("OrdersHistory");
}));

Query history / point-in-time:

var asOf = await db.Orders.TemporalAsOf(timestamp).ToListAsync();
var changes = await db.Orders.TemporalAll().Where(o => o.Id == id)
    .OrderBy(o => EF.Property<DateTime>(o, "ValidFrom")).ToListAsync();

Option B — application audit log (DB-agnostic, captures "who")

A SaveChangesInterceptor that writes an AuditEntry per changed entity:

public class AuditInterceptor(ICurrentUser user) : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData e, InterceptionResult<int> r, CancellationToken ct)
    {
        var ctx = e.Context!;
        foreach (var entry in ctx.ChangeTracker.Entries()
                     .Where(x => x.State is EntityState.Added or EntityState.Modified or EntityState.Deleted))
        {
            ctx.Add(new AuditEntry {
                Entity = entry.Metadata.GetTableName(), KeyValues = /* pk */,
                Action = entry.State.ToString(), Changes = /* changed props */,
                User = user.Id, At = DateTime.UtcNow });
        }
        return base.SavingChangesAsync(e, r, ct);
    }
}

Choosing

• Temporal tables: automatic, perfect point-in-time, SQL Server only, doesn't record who.
• Audit log: any DB, records who/why, but you maintain it and it's queryable as data, not as table state.
• You can combine: temporal for state history + a light audit log for actor/reason.