Day 34: Audit Logging

Audit Logging is the practice of writing a structured, tamper-resistant record of *who* did *what*, *to which thing*, *when*, *from where*, and *with what result* every time a security-relevant or business-relevant...

Intro

Audit Logging is the practice of writing a structured, tamper-resistant record of who did what, to which thing, when, from where, and with what result every time a security-relevant or business-relevant action happens. Unlike application logs (which help developers debug), audit records are evidence: auditors, fraud investigators, regulators, and customers rely on them. In our insurance claims system, it is the record that proves adjuster “A. Rao” raised claim CLM-1042’s payout from 4,000 to 40,000 at 02:14 from an unusual IP, and that nobody edited that record afterwards.

Why we need this

• Compliance. Insurance is regulated. Depending on jurisdiction and data, you may fall under SOX, GDPR/DPDP-style privacy laws, HIPAA (for health claims), PCI DSS (for card payments), or local insurance-regulator rules. Most of them require you to show who accessed or changed sensitive data and to retain that evidence for a defined period.
• Fraud and insider-threat detection. Claims fraud is often an insider or a colluding party: a payout edited just below an approval limit, a claim approved by its own creator, mass viewing of customer records.
• Non-repudiation and disputes. “I never approved that claim” must be answerable with facts.
• Incident forensics. After a breach you must reconstruct what the attacker read and changed, not only that they got in.
• Operational accountability. In a microservices system a business action crosses many services; each needs a consistent way to say “this identity did this”.

Application logs cannot serve these needs because they are verbose, sampled, rotated after days, editable by anyone with infrastructure access, and written for developers.

What problem it solves

Problem (from the topic list): compliance, fraud detection, and security need non-repudiable records.

Without audit logging:

• A claim payout changes and nobody can say who changed it, from what value, or why. The only trace is the current row in the database.
• Auditors ask “show me every user who viewed claimant medical notes last quarter” and the team greps through rotated text logs, finds gaps, and fails the audit.
• Logs that do exist say “Claim updated” with no user, no old value, and no correlation id.
• A privileged DBA or developer edits or deletes log lines to hide a mistake (or fraud), and no one can detect it.
• Each microservice invents its own format, so cross-service questions cannot be answered.

When it is needed (and when it is NOT)

Needed when:

• You handle money, personal data, health data, or contractual decisions (claims approval, payout, policy changes, underwriting overrides).
• You have privileged roles (adjusters, supervisors, admins, support staff) who can see or change other people’s data.
• A regulation, contract, or customer security questionnaire demands it.
• You expose administrative or “break-glass” operations.

Not needed / overkill when:

• Recording every internal technical event (cache hit, health probe, retry). That is telemetry, not audit; use logs and metrics.
• Purely public, anonymous, read-only content with no sensitive actions.
• A throwaway prototype or a personal tool with one user.
• When the business needs replayable state rather than a security record; that is Event Sourcing (Day 9), a different pattern (see Section 8). Audit logging is not a substitute for it and vice versa.

A common mistake is auditing everything “just in case”. Audit volume and cost grow fast, and a noisy audit trail is hard to review. Define audited events deliberately.

How to identify the problem (key signals)

1. Someone asks “who changed this claim?” and the honest answer is “we don’t know”.
2. Support tickets or disputes such as “I did not approve this” cannot be resolved with evidence.
3. An audit or security review finding: “insufficient traceability of privileged actions”.
4. Log lines like Claim 1042 updated with no user id, no old/new values, no correlation id.
5. The database has ModifiedBy and ModifiedOn columns, but they only hold the last change; history is lost on every update.
6. Anyone with database or log-store write access can alter or delete existing records, and nothing detects it.
7. Different services log the same business action in different shapes, so cross-service queries are impossible.
8. Retention of logs is measured in days, but the regulation requires years.

Flow Diagram

Audit rows are written in the same transaction, then exported to search and immutable storage.

flowchart LR
    U["Adjuster action"] --> API["Claims API - actor from JWT"]
    API --> TX["Same transaction"]
    TX --> BIZ[("Claim change")]
    TX --> AUD[("Append-only ledger audit table")]
    AUD --> OB["Outbox"] --> SB{{"Service Bus"}}
    SB --> LA["Log Analytics - search + alerts"]
    SB --> WORM[("Immutable Blob - WORM")]
    LA --> SENT["Sentinel / anomaly alerts"]

Level 1: Beginner

Analogy. A bank’s CCTV plus a signed visitor book. The visitor book (audit log) records who came in, what they did, and when, with a rule that you never tear out pages. Tearing pages out is itself suspicious. Application logs are the janitor’s notes about the building’s plumbing: useful, but nobody signs them.

Every audit record answers the “5 W’s”:

Minimal working example (.NET 10, C# 14). A record, a writer interface, and a console demo. The store here is in-memory to show shape; real stores follow later.

using System.Text.Json;

public sealed record AuditEntry(
    Guid Id,
    DateTime OccurredAtUtc,
    string ActorId,
    string Action,
    string EntityType,
    string EntityId,
    string Outcome,
    string? CorrelationId,
    string? Details);

public interface IAuditWriter
{
    Task WriteAsync(AuditEntry entry, CancellationToken ct = default);
}

// Demo-only. Append-only by design: no Update, no Delete methods exist.
public sealed class InMemoryAuditWriter : IAuditWriter
{
    private readonly List<AuditEntry> _entries = [];
    public IReadOnlyList<AuditEntry> Entries => _entries;

    public Task WriteAsync(AuditEntry entry, CancellationToken ct = default)
    {
        _entries.Add(entry);
        return Task.CompletedTask;
    }
}

public static class Demo
{
    public static async Task Main()
    {
        var writer = new InMemoryAuditWriter();

        await writer.WriteAsync(new AuditEntry(
            Guid.NewGuid(), DateTime.UtcNow,
            ActorId: "adjuster-rao",
            Action: "Claim.Approve",
            EntityType: "Claim",
            EntityId: "CLM-1042",
            Outcome: "Success",
            CorrelationId: "c7f1",
            Details: JsonSerializer.Serialize(new { Amount = 4000m })));

        foreach (var e in writer.Entries)
            Console.WriteLine($"{e.OccurredAtUtc:O} {e.ActorId} {e.Action} {e.EntityId} -> {e.Outcome}");
    }
}

Key beginner rules: (1) audit is business/security events, not debug output; (2) the record is written by the system, never typed in by the user; (3) records are never edited or deleted by application code; (4) never write passwords, tokens, full card numbers or raw medical text into an audit record.

Level 2: Intermediate

In a real .NET + Angular + database application, you want auditing to be automatic and hard to forget, and atomic with the business change.

6.1 SQL Server table (append-only)

CREATE TABLE dbo.AuditEntry
(
    Id             UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    OccurredAtUtc  DATETIME2(3)     NOT NULL,
    ActorId        NVARCHAR(100)    NOT NULL,
    ActorRole      NVARCHAR(50)     NULL,
    Action         NVARCHAR(100)    NOT NULL,
    EntityType     NVARCHAR(100)    NOT NULL,
    EntityId       NVARCHAR(100)    NOT NULL,
    Outcome        NVARCHAR(20)     NOT NULL,
    IpAddress      NVARCHAR(45)     NULL,
    CorrelationId  NVARCHAR(64)     NULL,
    OldValues      NVARCHAR(MAX)    NULL,   -- JSON
    NewValues      NVARCHAR(MAX)    NULL    -- JSON
);
CREATE INDEX IX_AuditEntry_Entity ON dbo.AuditEntry (EntityType, EntityId, OccurredAtUtc);
CREATE INDEX IX_AuditEntry_Actor  ON dbo.AuditEntry (ActorId, OccurredAtUtc);

-- The application login can only INSERT and SELECT. No UPDATE/DELETE.
DENY UPDATE, DELETE ON dbo.AuditEntry TO [claims_app_role];
GRANT INSERT, SELECT ON dbo.AuditEntry TO [claims_app_role];

On SQL Server 2022+ and Azure SQL Database you can go one step further with an append-only ledger table, which makes tampering cryptographically detectable (see Level 3 and Section 9):

CREATE TABLE dbo.AuditEntryLedger
(
    Id            UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
    OccurredAtUtc DATETIME2(3)     NOT NULL,
    ActorId       NVARCHAR(100)    NOT NULL,
    Action        NVARCHAR(100)    NOT NULL,
    EntityType    NVARCHAR(100)    NOT NULL,
    EntityId      NVARCHAR(100)    NOT NULL,
    Outcome       NVARCHAR(20)     NOT NULL
)
WITH (LEDGER = ON (APPEND_ONLY = ON));

PostgreSQL equivalent. Use the same columns (uuid, timestamptz, jsonb), REVOKE UPDATE, DELETE from the app role, and a trigger as defence in depth:

CREATE OR REPLACE FUNCTION audit_entry_block_mutation() RETURNS trigger AS $$
BEGIN
  RAISE EXCEPTION 'audit_entry is append-only';
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_audit_entry_no_update
BEFORE UPDATE OR DELETE ON audit_entry
FOR EACH ROW EXECUTE FUNCTION audit_entry_block_mutation();

Note: a table owner or superuser can drop the trigger. Row-level protection on PostgreSQL is a speed bump, not proof; pair it with shipping records to immutable storage (Section 9).

6.2 Automatic capture with an EF Core SaveChangesInterceptor

Audit rows are added in the same SaveChanges call, so they commit or roll back together with the claim change. This avoids the “changed but not audited” gap.

using System.Text.Json;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.ChangeTracking;
using Microsoft.EntityFrameworkCore.Diagnostics;

public interface IAuditable { }   // marker: only these entities are audited

public sealed class Claim : IAuditable
{
    public int Id { get; set; }
    public string PolicyNumber { get; set; } = "";
    public decimal PayoutAmount { get; set; }
    public string Status { get; set; } = "Submitted";
}

public sealed class AuditRow
{
    public Guid Id { get; set; } = Guid.NewGuid();
    public DateTime OccurredAtUtc { get; set; } = DateTime.UtcNow;
    public string ActorId { get; set; } = "";
    public string Action { get; set; } = "";
    public string EntityType { get; set; } = "";
    public string EntityId { get; set; } = "";
    public string Outcome { get; set; } = "Success";
    public string? CorrelationId { get; set; }
    public string? OldValues { get; set; }
    public string? NewValues { get; set; }
}

public interface ICurrentUser
{
    string ActorId { get; }
    string? CorrelationId { get; }
}

public sealed class AuditSaveChangesInterceptor(ICurrentUser user) : SaveChangesInterceptor
{
    private static readonly HashSet<string> Sensitive = ["Ssn", "BankAccount"];

    public override ValueTask<InterceptionResult<int>> SavingChangesAsync(
        DbContextEventData eventData,
        InterceptionResult<int> result,
        CancellationToken ct = default)
    {
        var ctx = eventData.Context;
        if (ctx is null) return ValueTask.FromResult(result);

        var rows = new List<AuditRow>();
        foreach (var e in ctx.ChangeTracker.Entries<IAuditable>()
                     .Where(e => e.State is EntityState.Added or EntityState.Modified or EntityState.Deleted)
                     .ToList())
        {
            rows.Add(new AuditRow
            {
                ActorId = user.ActorId,
                CorrelationId = user.CorrelationId,
                Action = $"{e.Metadata.ShortName()}.{e.State}",
                EntityType = e.Metadata.ShortName(),
                EntityId = e.Properties.First(p => p.Metadata.IsPrimaryKey()).CurrentValue?.ToString() ?? "",
                OldValues = e.State == EntityState.Added ? null : Snapshot(e, original: true),
                NewValues = e.State == EntityState.Deleted ? null : Snapshot(e, original: false)
            });
        }

        ctx.Set<AuditRow>().AddRange(rows);   // same transaction as the business change
        return ValueTask.FromResult(result);
    }

    private static string Snapshot(EntityEntry e, bool original) =>
        JsonSerializer.Serialize(e.Properties
            .Where(p => !Sensitive.Contains(p.Metadata.Name))   // never log secrets/PII you don't need
            .ToDictionary(
                p => p.Metadata.Name,
                p => original ? p.OriginalValue : p.CurrentValue));
}

Caveats worth teaching: for an Added entity with a database-generated key, CurrentValue is a temporary value at SavingChanges time. If you need the final id, capture it in SavedChangesAsync (a second save) or use client-generated GUID keys. Also, ExecuteUpdate/ExecuteDelete and raw SQL bypass the change tracker and therefore bypass this interceptor.

Registering it and resolving the user from the JWT:

using System.Security.Claims;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddHttpContextAccessor();
builder.Services.AddScoped<ICurrentUser, HttpCurrentUser>();
builder.Services.AddScoped<AuditSaveChangesInterceptor>();
builder.Services.AddDbContext<ClaimsDbContext>((sp, o) => o
    .UseSqlServer(builder.Configuration.GetConnectionString("Claims"))
    .AddInterceptors(sp.GetRequiredService<AuditSaveChangesInterceptor>()));

var app = builder.Build();
app.Run();

public sealed class HttpCurrentUser(IHttpContextAccessor accessor) : ICurrentUser
{
    private HttpContext? Ctx => accessor.HttpContext;
    public string ActorId =>
        Ctx?.User.FindFirstValue("oid")                 // Microsoft Entra object id
        ?? Ctx?.User.FindFirstValue(ClaimTypes.NameIdentifier)
        ?? "system";
    public string? CorrelationId => System.Diagnostics.Activity.Current?.TraceId.ToString();
}

public sealed class ClaimsDbContext(DbContextOptions<ClaimsDbContext> options) : DbContext(options)
{
    public DbSet<Claim> Claims => Set<Claim>();
    public DbSet<AuditRow> AuditRows => Set<AuditRow>();

    protected override void OnModelCreating(ModelBuilder b) =>
        b.Entity<AuditRow>().ToTable("AuditEntry");
}

6.3 Business-level audit for actions that are not row changes

Reads of sensitive data and denied attempts are not EF changes. Audit them explicitly from an endpoint filter or service:

app.MapGet("/api/claims/{id:int}/medical-notes", async (
        int id, ClaimsDbContext db, ICurrentUser user, IAuditWriter audit, CancellationToken ct) =>
    {
        await audit.WriteAsync(new AuditEntry(
            Guid.NewGuid(), DateTime.UtcNow, user.ActorId,
            "Claim.ViewMedicalNotes", "Claim", id.ToString(),
            "Success", user.CorrelationId, null), ct);

        return Results.Ok(/* notes */);
    })
    .RequireAuthorization("ClaimsAdjuster");

(IAuditWriter here is the interface from Level 1, implemented over ClaimsDbContext or a queue.)

6.4 Angular: audit trail viewer for supervisors (Angular 22, standalone, signals)

import { Component, inject, input, signal, effect } from '@angular/core';
import { HttpClient } from '@angular/common/http';
import { DatePipe } from '@angular/common';

interface AuditItem {
  occurredAtUtc: string;
  actorId: string;
  action: string;
  outcome: string;
}

@Component({
  selector: 'app-claim-audit',
  imports: [DatePipe],
  template: `
    <h3>Audit trail for {{ claimId() }}</h3>
    @if (error()) { <p role="alert">Could not load audit trail.</p> }
    <table>
      <thead><tr><th>When (UTC)</th><th>Who</th><th>Action</th><th>Result</th></tr></thead>
      <tbody>
        @for (i of items(); track i.occurredAtUtc + i.action) {
          <tr>
            <td>{{ i.occurredAtUtc | date: 'medium' : 'UTC' }}</td>
            <td>{{ i.actorId }}</td><td>{{ i.action }}</td><td>{{ i.outcome }}</td>
          </tr>
        }
      </tbody>
    </table>`
})
export class ClaimAuditComponent {
  private http = inject(HttpClient);
  claimId = input.required<string>();
  items = signal<AuditItem[]>([]);
  error = signal(false);

  constructor() {
    effect(() => {
      this.error.set(false);
      this.http.get<AuditItem[]>(`/api/claims/${this.claimId()}/audit`).subscribe({
        next: d => this.items.set(d),
        error: () => this.error.set(true)
      });
    });
  }
}

Important point for the team: the Angular client never writes audit records. A browser is untrusted; a malicious user could skip or forge them. The client only displays the trail and forwards a correlation id header. Audit is written server-side where identity and data are trusted. Restrict the audit-read endpoint to supervisor/auditor roles, and audit those reads too.

Level 3: Advanced

7.1 Tamper evidence: hash chaining

An append-only permission is not proof. A DBA can bypass permissions. To make edits detectable, chain each record to the previous one:

using System.Security.Cryptography;
using System.Text;

public static class AuditChain
{
    public static string ComputeHash(string prevHash, AuditEntry e)
    {
        var payload = string.Join('|',
            prevHash, e.Id, e.OccurredAtUtc.ToString("O"), e.ActorId,
            e.Action, e.EntityType, e.EntityId, e.Outcome, e.Details ?? "");
        return Convert.ToHexString(SHA256.HashData(Encoding.UTF8.GetBytes(payload)));
    }

    public static bool Verify(IEnumerable<(AuditEntry Entry, string Hash)> ordered, string genesis = "GENESIS")
    {
        var prev = genesis;
        foreach (var (entry, hash) in ordered)
        {
            if (ComputeHash(prev, entry) != hash) return false;
            prev = hash;
        }
        return true;
    }
}

Changing or deleting a middle record breaks every later hash. Periodically publish the latest hash (“anchor”) somewhere the DB admin cannot change (immutable blob, a ledger service, a separate subscription). This is what Azure SQL ledger and Azure confidential ledger do for you natively, so prefer them over hand-rolled chains in production.

7.2 Performance and scalability

• Do not audit on the request’s critical path with a slow store. Writing to a database in the same transaction is fine for state changes (it is one extra insert). For high-volume reads, put audit events on a queue and write asynchronously.
• Do not lose audit events to make things fast. If you go async, use a durable channel (Service Bus, or Transactional Outbox (Day 12)) rather than fire-and-forget in memory. A crash between the business commit and the async publish must not drop the record.
• Partition and index by (EntityType, EntityId, time) and (ActorId, time). Partition by month for retention.
• Hot/warm/cold. Recent months in the operational store; older data in cheap immutable storage (Parquet/JSON in blob), queryable on demand.
• Payload size. Store diffs of changed fields, not entire aggregates.

7.3 Security

• Least privilege. The app identity can insert; it cannot update or delete. Reading the audit store is a separate role.
• Separation of duties. The people who administer the app must not be able to administer the audit store.
• PII minimisation. Audit a reference (claimant id), not the content (diagnosis text). Mask or hash sensitive fields. Privacy law (right to erasure) can conflict with immutability; design retention and pseudonymisation with your legal team rather than assuming immutability always wins.
• Log injection. Treat all user-supplied text as data. Use structured JSON serialisation, never string concatenation into a log line.
• Trusted time and identity. Use server UTC time, never client time. Take identity from the validated token, never from a request body field.

7.4 Failure modes

7.5 Common mistakes

1. Treating application logs as audit logs.
2. Storing passwords, tokens, full PANs or raw health notes in audit records.
3. Auditing everything, so reviewers drown in noise.
4. Giving the app account UPDATE/DELETE on the audit table.
5. Letting the client (Angular) submit audit records.
6. Not auditing reads of sensitive data and denied attempts.
7. No retention policy, or a retention shorter than the regulation.
8. No process that anyone actually reviews the trail or alerts on anomalies.

Level 4: Expert and Architect view

8.1 Design trade-offs and alternatives

Audit log vs neighbours: application logging (Day 32) is for developers and is lossy; metrics (Day 33) are aggregates; distributed tracing (Day 31) follows a request but is sampled; audit logging is complete, business-meaningful, and retained. Use the trace/correlation id as the key that links them.

8.2 Patterns it combines with

• Transactional Outbox (Day 12): durable async delivery of audit events to a central store.
• Domain Event (Day 11): handlers translate domain events (ClaimApproved) into audit entries with business meaning.
• Access Token (Day 38): the validated JWT claims supply the actor identity that flows into every record.
• API Gateway (Day 19): edge-level audit of authentication failures, throttling, and denied requests.
• Distributed Tracing (Day 31) and Log Aggregation (Day 32): correlation ids tie an audit record to the traces and logs of the same request.
• Microservice Chassis (Day 40): ship the audit client once, in the shared library, so every service emits the same format.
• Externalized Configuration (Day 39): audit sink endpoints and retention are configuration, secrets in Key Vault.

8.3 ADR (suitable for architecture review)

ADR-034: Audit logging for the Claims platform

Status: Proposed

Context: The Claims platform lets adjusters approve claims, change payouts, and view claimant health data. We must demonstrate who did what to regulators and support fraud investigations. Current application logs lack actor identity, old/new values, and are retained for 14 days.

Decision:

1. Every service records audit events with a shared schema (actor, action, entity, outcome, correlation id, old/new values for state changes) through the platform chassis library.
2. State-change audit rows are written in the same database transaction as the change (EF Core interceptor). Reads of sensitive data and denied actions are audited explicitly.
3. The audit table lives in Azure SQL as an append-only ledger table; the application identity has INSERT and SELECT only.
4. Events are also exported (via outbox and Service Bus, or Azure SQL Auditing) to a central Log Analytics workspace for search and alerting, and to an immutable Blob container (locked time-based retention) for long-term evidence. Retention: aligned to the regulatory requirement, confirmed with Legal.
5. Sensitive values are referenced by id, never copied into audit payloads.

Alternatives considered: application logs only (rejected: not tamper-evident, short retention); full Event Sourcing (rejected for now: much larger change, does not cover reads); a third-party audit SaaS (deferred: data residency review pending).

Consequences: + defensible evidence, + uniform investigations; - extra storage cost, - small write overhead, - schema discipline required; - needs a review process and owners for alerts.

Azure implementation

Service and SKU names below were checked against Microsoft documentation. Prices change, so confirm on the Azure pricing pages for your region and currency.

9.1 Services

9.2 Configuration outline

1. Azure SQL: create the audit table as LEDGER = ON (APPEND_ONLY = ON); enable ledger digest storage pointing to a storage account with an immutability policy or to Azure Confidential Ledger; grant the app’s managed identity only INSERT/SELECT.
2. Azure SQL Auditing: enable at server level, send to a Log Analytics workspace (searchable) and a storage account (retention).
3. Managed identity: run the API on App Service/Container Apps/AKS with a managed identity, and connect to SQL with Microsoft Entra authentication, so no passwords exist to leak, and the audited identity is real.
4. Immutable Blob: create a storage account and container for audit exports, add a time-based retention policy, test it unlocked, then lock it (Microsoft recommends locking within 24 hours). A locked policy cannot be deleted and its retention can only be extended. Do not enable features documented as incompatible with immutability (e.g. SFTP, NFS 3.0, point-in-time restore on that account).
5. Log Analytics: choose the workspace table plan by need: Analytics for logs you query and alert on frequently; Basic or Auxiliary for high-volume, low-touch data (cheaper ingestion, restricted query features). Set interactive and total (archive) retention to meet the regulation.
6. Diagnostic settings: send Entra logs, Activity Log, Key Vault, and SQL auditing to the workspace.
7. Alerts: KQL alert rules, e.g. payout changed by more than a threshold, or one user viewing an unusual number of claimants.

A sample KQL query (assuming a custom table ClaimsAudit_CL created through the Logs Ingestion API):

ClaimsAudit_CL
| where TimeGenerated > ago(1d) and Action_s == "Claim.ViewMedicalNotes"
| summarize Views = count() by ActorId_s, bin(TimeGenerated, 1h)
| where Views > 50

9.3 Pricing and tier considerations

• Log Analytics / Azure Monitor Logs is billed mainly by data ingested (per GB), plus retention beyond the included period and archive/restore/search-job charges. Pay-as-you-go Analytics ingestion is in the low single-digit USD per GB range in most regions; commitment tiers (starting at 100 GB/day) give discounts; Basic and Auxiliary plans cost less per GB. Check the Azure Monitor pricing page.
• Azure SQL Database: ledger tables have no separate SKU charge, but the extra history/ledger views and digest storage consume database and storage space. Auditing to a storage account or Log Analytics has its own storage/ingestion cost.
• Blob immutable storage has no extra fee for the feature itself; you pay normal storage cost, and tiering to Cool or Archive lowers cost. Note that data under a locked retention policy cannot be removed early, so size the retention carefully; it is a cost commitment.
• Service Bus: Standard is enough for most audit transport; Premium for isolation, larger messages, or private-networking needs. Event Hubs for high volume streaming.
• Cost control: audit only defined events, send diffs not full objects, route high-volume/low-value data to Basic/Auxiliary tables or straight to storage, and set sensible retention tiers.

9.4 Reference architecture (text)

1. The Angular 22 app authenticates users with Microsoft Entra ID and calls the Claims API through Azure API Management or Application Gateway.
2. The .NET 10 Claims API runs on Azure Container Apps (or AKS/App Service) with a managed identity. The chassis library attaches actor identity and correlation id to every request.
3. On a state change, the EF Core interceptor writes the audit row in the same transaction into an append-only ledger table in Azure SQL Database. Explicit audit events (reads, denials) are written the same way.
4. An outbox publisher forwards audit events to Azure Service Bus; a small Audit Ingestion worker writes them to Log Analytics (custom table) and appends JSON lines to an immutable Blob container (locked time-based retention).
5. Azure SQL Auditing additionally records every database access (including admins) to Log Analytics and storage. SQL ledger digests are stored in immutable storage or Azure Confidential Ledger, and a scheduled job verifies the ledger.
6. Entra sign-in/audit logs and Azure Activity Log stream into the same workspace. Microsoft Sentinel and Azure Monitor alerts detect anomalies and notify the security team. Auditors get read-only access to a dedicated workbook.
7. Secrets and keys are in Key Vault; everything is deployed with IaC so audit configuration is itself reviewed and versioned.

Teaching guide for my team

10.1 Two-minute explanation for a beginner

“When a claim’s payout changes, we need a receipt that proves who changed it, from what to what, and when. That receipt is an audit record. It is different from the debug logs we write for ourselves: an audit record is evidence, so we write it from the server, we never edit it, and we keep it for as long as the law says. If you can’t answer ‘who did this?’ after an incident, you don’t have audit logging.”

10.2 Five-minute explanation for an intermediate developer

Start with the 5 W’s schema. Show that ModifiedBy/ModifiedOn columns fail because they only keep the last change. Show the EF Core interceptor writing the audit row in the same transaction, and explain why that guarantees no “changed but not audited” state. Cover what the interceptor misses (reads, raw SQL, bulk updates) and how explicit audit calls and database-level auditing fill the gaps. Explain least privilege (INSERT only), then tamper evidence (ledger or hash chain plus immutable export). Finish with PII: audit references, not content. Connect to correlation ids so an audit row leads to the trace and logs.

10.3 Hands-on exercise

Task: In the sample Claims API, add auditing for Claim changes.

1. Create the AuditEntry table with a DENY on UPDATE/DELETE for the application role.
2. Implement the SaveChangesInterceptor from Section 6.2 and register it.
3. Add an endpoint GET /api/claims/{id}/audit restricted to a Supervisor role.
4. Change a claim’s PayoutAmount from 4000 to 40000 through the API.
5. Try to UPDATE an audit row with the application login.
6. Bonus: implement the hash chain and show that editing a middle row makes Verify return false.

Expected outcome: one audit row for the change with actor, action Claim.Modified, and old/new values showing PayoutAmount 4000 to 40000; the UPDATE on the audit table is rejected with a permission error; the supervisor sees the trail while a normal adjuster gets 403; with the bonus, Verify returns false after tampering.

10.4 Interview-style questions

1. How is an audit log different from an application log? Application logs are for developers: verbose, sampled, short-lived, editable. Audit logs are evidence: structured, complete for defined events, tamper-resistant, retained per regulation, and record actor, action, target, time, and outcome.
2. Why write the audit row in the same transaction as the business change? So that either both are committed or neither is. Writing it afterwards risks a committed change with no record if the process crashes. For async delivery, use an outbox so the event cannot be lost.
3. How would you make an audit trail tamper-evident and not just append-only by permission? Chain records with hashes or use ledger tables, publish periodic digests/anchors to storage the DBA cannot alter (immutable Blob or Azure Confidential Ledger), restrict privileges and separate duties, and verify the chain on a schedule.

Mastery checklist

• I can explain the difference between audit logs, application logs, metrics, and traces, and give a scenario for each.
• I can define the audit schema (actor, action, entity, outcome, time, correlation id, old/new values) and justify each field.
• I can implement automatic change capture in EF Core and explain what it misses.
• I can guarantee atomicity between the business change and its audit record, including the async case with an outbox.
• I can enforce append-only and least privilege in SQL Server or PostgreSQL, and explain why that alone is not tamper proof.
• I can design tamper evidence with ledger tables or hash chains plus immutable storage.
• I can decide what NOT to audit and what NOT to put in the payload (PII, secrets), and handle the retention/erasure conflict with Legal.
• I can design the Azure setup: SQL ledger, SQL Auditing, Log Analytics, immutable Blob, alerts, and estimate its cost drivers.

Key takeaway

Audit logging is evidence, not diagnostics: record who did what and when, write it on the server in the same transaction as the change, make it append-only and tamper-evident, and keep it only as long as, and only as detailed as, the rules and the risk require.