Catalog My learning Blog
Full-Stack Developer Course: React, .NET and SQL Server · Phase 7 · Lesson 64 · 7 min read

.NET Cross-Cutting Concerns: Audit Logs and History

On this page
  1. Learning Objective
  2. Concept Explanation
  3. Worked Example: An Auditing Interceptor, a Temporal Table and a Tamper-Evident Chain
  4. Real-World Examples
  5. Review Questions & Answers

Learning Objective

Implement auditing so you can answer who changed what and when, stamp entities automatically with an EF Core interceptor, use SQL Server temporal tables for row history, design an audit trail that is trustworthy and privacy-aware, and monitor security-relevant events.

Concept Explanation

Some requirements cut across every feature: knowing who did something, when, and to what. Compliance frameworks require it, incident response depends on it, and customers ask for it, yet it is often bolted on late and inconsistently. The most reliable approach is to make it automatic and central instead of relying on each handler to remember. Two complementary mechanisms serve different questions. Audit stamps on each row, such as CreatedAt, CreatedBy, UpdatedAt, and UpdatedBy, answer who last touched this. A history of changes, recording each version of a row or each business event, answers what did it look like before, and who changed it from what to what.

EF Core interceptors make stamping automatic. A SaveChangesInterceptor sees every entity the change tracker is about to write, and it can set the timestamp and user fields on those implementing a small marker interface, without any handler code. The same hook can write audit entries describing each change: entity type, key, operation, the changed properties with old and new values, the acting user, the correlation identifier, and the time. The identity must come from the authenticated principal, never from the request body, and background jobs need a clear system identity so their actions are attributed rather than anonymous.

SQL Server offers temporal tables, which let the database keep the full history itself. Declaring a table system-versioned makes SQL Server maintain a history table automatically, and you can query the table as it was at any past time with FOR SYSTEM_TIME, or list every version of a row. That gives row-level history with no application code and cannot be skipped by any code path, including manual SQL, which is a strength. It does not record who made a change, so combine it with application-level audit entries or session context to capture the actor. For high-value actions, business-level audit events, such as role changed or export downloaded, are more meaningful to a reader than raw column diffs.

An audit trail is only useful if it is trustworthy and safe. Make it append-only: the application account should be able to insert audit rows but not update or delete them, and important logs should be shipped to a separate system that application administrators cannot alter. For tamper evidence, chain entries by including the hash of the previous entry in each new one. Do not record secrets or full personal data in audit payloads; store identifiers and changed field names, and mask values. Plan retention for the legal period, and remember that privacy laws may require deletion or anonymization of personal data, which needs to be reconciled with immutability by keeping identifiers and not content. Finally, close the loop with monitoring: log security events, failed logins, authorization denials, and privilege changes, and alert on spikes, because a log nobody reads is only forensic evidence after the damage.

NeedMechanismNotes
Who last changed a rowAudit stamp columns set by an interceptorAutomatic, cannot be forgotten by handlers
What each change wasAudit entries written on SaveChangesOld and new values, actor, correlation id
Row history and point-in-time queriesSQL Server temporal tablesEnforced by the database for every code path
High-value business eventsExplicit domain audit eventsReadable: role changed, data exported
Trustworthy trailAppend-only, hash chain, separate storeApplication account cannot edit history
DetectionSecurity event logging and alertsFailed logins, denials, privilege changes

Worked Example: An Auditing Interceptor, a Temporal Table and a Tamper-Evident Chain

The interceptor stamps and records changes. The DDL makes tasks temporal. The chain function links audit entries by hash.

C#
public interface IAuditable { DateTimeOffset CreatedAt { get; set; } string CreatedBy { get; set; }
                              DateTimeOffset UpdatedAt { get; set; } string UpdatedBy { get; set; } }

public sealed class AuditInterceptor(ICurrentUser user, TimeProvider clock, ICorrelation correlation) : SaveChangesInterceptor
{
    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData data, InterceptionResult<int> result, CancellationToken ct = default)
    {
        var db = data.Context!;
        var now = clock.GetUtcNow();
        var actor = user.Id?.ToString() ?? "system";               // background jobs are attributed to "system"
        var entries = new List<AuditEntry>();

        foreach (var e in db.ChangeTracker.Entries().Where(e => e.State is EntityState.Added or EntityState.Modified or EntityState.Deleted).ToList())
        {
            if (e.Entity is IAuditable a)
            {
                if (e.State == EntityState.Added) { a.CreatedAt = now; a.CreatedBy = actor; }
                if (e.State != EntityState.Deleted) { a.UpdatedAt = now; a.UpdatedBy = actor; }
            }
            if (e.Entity is AuditEntry) continue;

            var changed = e.State == EntityState.Modified
                ? e.Properties.Where(p => p.IsModified && !Sensitive(p.Metadata.Name)).Select(p => p.Metadata.Name).ToArray()
                : [];
            entries.Add(new AuditEntry
            {
                Id = Guid.CreateVersion7(), At = now, Actor = actor, Operation = e.State.ToString(),
                EntityType = e.Metadata.ClrType.Name, EntityKey = e.Property("Id").CurrentValue?.ToString() ?? "",
                ChangedFields = string.Join(',', changed),        // names only: never secrets or full personal data
                CorrelationId = correlation.Id,
            });
        }
        db.Set<AuditEntry>().AddRange(entries);
        return base.SavingChangesAsync(data, result, ct);
    }

    private static bool Sensitive(string name) => name is "PasswordHash" or "Email" or "Token" or "SecurityStamp";
}

builder.Services.AddDbContext<AppDb>((sp, o) => o.UseSqlServer(cs).AddInterceptors(sp.GetRequiredService<AuditInterceptor>()));
SQL
-- SQL Server keeps every version of every row, for all code paths, including manual SQL.
ALTER TABLE dbo.Tasks ADD
    ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START HIDDEN CONSTRAINT DF_Tasks_ValidFrom DEFAULT SYSUTCDATETIME(),
    ValidTo   DATETIME2 GENERATED ALWAYS AS ROW END   HIDDEN CONSTRAINT DF_Tasks_ValidTo   DEFAULT CONVERT(DATETIME2, '9999-12-31 23:59:59.9999999'),
    PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo);
ALTER TABLE dbo.Tasks SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.TasksHistory, HISTORY_RETENTION_PERIOD = 2 YEARS));

-- What did this task look like last Tuesday? Every version in a window?
SELECT * FROM dbo.Tasks FOR SYSTEM_TIME AS OF '2026-09-15T10:00:00' WHERE Id = @id;
SELECT Id, State, ValidFrom, ValidTo FROM dbo.Tasks FOR SYSTEM_TIME ALL WHERE Id = @id ORDER BY ValidFrom;

-- The application login can append audit rows but never rewrite history.
GRANT INSERT, SELECT ON dbo.AuditEntries TO notes_app;
DENY  UPDATE, DELETE ON dbo.AuditEntries TO notes_app;
Code
// Tamper evidence: each entry includes the hash of the previous one, so editing history breaks the chain.
public static string Link(string previousHash, AuditEntry e) =>
    Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(
        $"{previousHash}|{e.Id}|{e.At:O}|{e.Actor}|{e.Operation}|{e.EntityType}|{e.EntityKey}|{e.ChangedFields}")));

// Security events go to structured logs and alert rules: failed logins, 403 spikes, role changes, exports.
log.LogWarning("Authorization denied for {Actor} on {Resource} {ResourceId}", actor, "Project", projectId);

Test the audit itself: change a task through the API, then assert that an audit entry exists with the right actor and changed field names, and that the entry contains no password hash or email value.

An EF Core interceptor stamps entities and writes audit entries with actor, correlation id and changed field names. SQL Server temporal tables keep every row version, and audit rows are append-only with a hash chain.
An EF Core interceptor stamps entities and writes audit entries with actor, correlation id and changed field names. SQL Server temporal tables keep every row version, and audit rows are append-only with a hash chain.

Real-World Examples

  • The Payment Card Industry Data Security Standard requires, in its Requirement 10, that access to system components and cardholder data be logged and monitored, with audit trails that are protected from modification and retained for at least a year with three months immediately available. It is a widely applied baseline for what a trustworthy audit log means in practice.
  • The OWASP Top 10 has long included a category for security logging and monitoring failures, noting that breaches often go undetected for months because failed logins, access control failures, and suspicious activity are not logged or alerted on. Building auditing and alerting into the platform rather than each feature addresses the category systematically.
Key TakeawaysMake auditing automatic and central, so no developer has to remember it: stamp entities with an EF Core interceptor. Record changed field names with the actor and correlation identifier, and use SQL Server temporal tables for enforced row history. Keep the trail append-only and tamper-evident, avoid recording secrets or full personal data, define retention, and log and alert on security events so the trail is used for detection and not only forensics.

Review Questions & Answers

Handlers will inevitably forget, or different developers will do it differently, leaving gaps that are discovered during an incident. An interceptor sees every save through the DbContext and applies the same logic to every auditable entity, so coverage is complete by construction and the code exists in one tested place.

Database-enforced history that covers every code path, including manual SQL and scripts, with the ability to query any past state using FOR SYSTEM_TIME. Application auditing captures the actor and business context but can be bypassed by code that skips the interceptor. Combining both provides completeness and meaning.

The audit store is another place where data lives and is often accessible to more people and systems, so copying passwords, tokens, or personal data into it multiplies exposure and complicates privacy obligations. Field names, identifiers, and masked values show what changed without duplicating the sensitive content.

Each entry includes a hash computed over the previous entry's hash and its own contents. If someone edits or deletes an earlier entry, its hash changes and every later link no longer matches, so verification exposes the alteration. Storing the latest hash or the chain externally prevents an insider from recomputing the whole chain.