Tutorial: an audit trail for insurance policies

An insurer has to answer questions like these, sometimes years later: what exactly did this policy say when the claim was filed? Who raised the premium, and why? In this tutorial you give an ASP.NET Core API those answers by adding Hindsight to it.

You will:

1. make the Policy entity temporal;
2. record who made each change and why;
3. generate the migration and look at what it creates;
4. write through the API as usual;
5. ask point-in-time and audit questions.

It takes about 15 minutes. You need an ASP.NET Core minimal API project with EF Core, and a PostgreSQL database you can migrate. A throwaway one is enough:

docker run -d --name insurance-db -e POSTGRES_PASSWORD=postgres -p 5432:5432 postgres:17

1. Add the packages

dotnet add package Hindsight.EntityFrameworkCore.PostgreSQL
dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL
dotnet add package Microsoft.EntityFrameworkCore.Design
dotnet tool install --global dotnet-ef

2. The entity

A policy has a number, a status, a premium and a start date:

namespace InsuranceSample;

public enum PolicyStatus
{
    Draft,
    Active,
    Suspended,
    Cancelled,
}

public sealed class Policy
{
    public int Id { get; set; }
    public required string Number { get; set; }
    public PolicyStatus Status { get; set; }
    public decimal Premium { get; set; }
    public DateOnly EffectiveFrom { get; set; }

    /// <summary>Excluded from versioning — changes to this property alone do not create a history row.</summary>
    public DateTimeOffset UpdatedAt { get; set; }
}

UpdatedAt is bookkeeping. It changes often and says nothing about the policy itself, so it will be kept out of the history.

3. Make it temporal

using Hindsight;
using Microsoft.EntityFrameworkCore;

namespace InsuranceSample;

public sealed class InsuranceDbContext(DbContextOptions<InsuranceDbContext> options) : DbContext(options)
{
    public DbSet<Policy> Policies => Set<Policy>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Policy>(e =>
        {
            e.ToTable("policies");
            e.Property(p => p.Status).HasConversion<string>();
            e.IsTemporal(t => t.Exclude(p => p.UpdatedAt));
        });
    }
}

IsTemporal gives Policy a history table. Exclude(p => p.UpdatedAt) leaves that column out of the history, and a save that changes only UpdatedAt creates no new version.

4. Record who and why

Hindsight stores a change context with every version: the user, a correlation id and a reason. It gets them from an IChangeContextProvider, which you write. This one reads the signed-in user and the current trace id:

public sealed class HttpChangeContextProvider(IHttpContextAccessor accessor) : IChangeContextProvider
{
    // Called once per SaveChanges that writes a temporal entity. Read per-request state here,
    // not in the constructor: the provider is registered as a singleton.
    public ChangeContext GetChangeContext(DbContext context)
    {
        var user = accessor.HttpContext?.User;
        return new ChangeContext
        {
            ChangedBy = user?.FindFirst("sub")?.Value,
            ChangedByName = user?.Identity?.Name,
            CorrelationId = Activity.Current?.TraceId.ToString(),
            Extra = """{"source":"web"}""",
        };
    }
}

Register it together with the context:

builder.Services.AddHttpContextAccessor();
builder.Services.AddSingleton<HttpChangeContextProvider>();

builder.Services.AddDbContext<InsuranceDbContext>(options => options
    .UseNpgsql(builder.Configuration.GetConnectionString("Insurance"))
    .UseHindsight(hindsight => hindsight
        .UseHistoryWriter(HistoryWriter.Trigger)
        .WithChangeContext<HttpChangeContextProvider>()));

Two choices here:

• HistoryWriter.Trigger: history is written by a PostgreSQL trigger, so even a raw SQL UPDATE run by hand gets recorded. See Choosing a history writer.
• A singleton provider: it reads the request's user when it is called, so one instance serves every request. See Change context for why it should not be scoped.

5. Generate the migration

dotnet ef migrations add AddPolicyHistory
dotnet ef database update

The migration's C# creates policies and policies_history. Hindsight adds two more things when the migration is turned into SQL: a GiST index on the history table's period, and the trigger. Print the full SQL to review it:

dotnet ef migrations script

The generated trigger, for a policies table

This is the exact output from Hindsight's own test suite, for a policy table with id, number, premium and status columns:

CREATE OR REPLACE FUNCTION policies_history_write() RETURNS trigger
LANGUAGE plpgsql AS $hindsight$
DECLARE
    _now timestamp with time zone := now();
    _closed_at timestamp with time zone;
    _changed_by text := nullif(current_setting('hindsight.changed_by', true), '');
    _changed_by_name text := nullif(current_setting('hindsight.changed_by_name', true), '');
    _correlation_id text := nullif(current_setting('hindsight.correlation_id', true), '');
    _reason text := nullif(current_setting('hindsight.reason', true), '');
    _extra jsonb := nullif(current_setting('hindsight.extra', true), '')::jsonb;
BEGIN
    IF (TG_OP = 'INSERT') THEN
        INSERT INTO policies_history (id, number, premium, status, valid_from, valid_to, operation, changed_by, changed_by_name, correlation_id, reason, extra)
        VALUES (NEW.id, NEW.number, NEW.premium, NEW.status, _now, 'infinity'::timestamp with time zone, 1, _changed_by, _changed_by_name, _correlation_id, _reason, _extra);
        RETURN NEW;
    ELSIF (TG_OP = 'UPDATE') THEN
        IF (NEW.id, NEW.number, NEW.premium, NEW.status) IS NOT DISTINCT FROM (OLD.id, OLD.number, OLD.premium, OLD.status) THEN
            RETURN NEW;
        END IF;
        UPDATE policies_history SET valid_to = GREATEST(_now, valid_from + INTERVAL '1 microsecond') WHERE id = OLD.id AND valid_to = 'infinity'::timestamp with time zone RETURNING valid_to INTO _closed_at;
        INSERT INTO policies_history (id, number, premium, status, valid_from, valid_to, operation, changed_by, changed_by_name, correlation_id, reason, extra)
        VALUES (NEW.id, NEW.number, NEW.premium, NEW.status, COALESCE(_closed_at, _now), 'infinity'::timestamp with time zone, 2, _changed_by, _changed_by_name, _correlation_id, _reason, _extra);
        RETURN NEW;
    ELSIF (TG_OP = 'DELETE') THEN
        UPDATE policies_history SET valid_to = GREATEST(_now, valid_from + INTERVAL '1 microsecond') WHERE id = OLD.id AND valid_to = 'infinity'::timestamp with time zone RETURNING valid_to INTO _closed_at;
        INSERT INTO policies_history (id, number, premium, status, valid_from, valid_to, operation, changed_by, changed_by_name, correlation_id, reason, extra)
        VALUES (OLD.id, OLD.number, OLD.premium, OLD.status, COALESCE(_closed_at, _now), COALESCE(_closed_at, _now), 3, _changed_by, _changed_by_name, _correlation_id, _reason, _extra);
        RETURN OLD;
    END IF;
    RETURN NULL;
END;
$hindsight$;


CREATE OR REPLACE TRIGGER policies_history_trg AFTER INSERT OR UPDATE OR DELETE ON policies FOR EACH ROW EXECUTE FUNCTION policies_history_write();

6. Write through the API

The endpoints look like any other EF Core code. The only addition is WithReason, which attaches a reason to the next save:

app.MapPost("/policies", async (Policy policy, InsuranceDbContext db) =>
{
    db.Policies.Add(policy);
    await db.SaveChangesAsync();
    return Results.Created($"/policies/{policy.Id}", policy);
});

app.MapPost("/policies/{id:int}/activate", async (int id, string reason, InsuranceDbContext db) =>
{
    var policy = await db.Policies.SingleAsync(p => p.Id == id);
    policy.Status = PolicyStatus.Active;

    using (db.WithReason(reason))
    {
        await db.SaveChangesAsync();
    }

    return Results.NoContent();
});

app.MapPut("/policies/{id:int}/premium", async (int id, decimal premium, string reason, InsuranceDbContext db) =>
{
    var policy = await db.Policies.SingleAsync(p => p.Id == id);
    policy.Premium = premium;

    using (db.WithReason(reason))
    {
        await db.SaveChangesAsync();
    }

    return Results.NoContent();
});

app.MapDelete("/policies/{id:int}", async (int id, string reason, InsuranceDbContext db) =>
{
    var policy = await db.Policies.SingleAsync(p => p.Id == id);
    db.Policies.Remove(policy);

    using (db.WithReason(reason))
    {
        await db.SaveChangesAsync();
    }

    return Results.NoContent();
});

Create a policy, activate it and raise its premium:

API=http://localhost:5000

curl -X POST $API/policies -H 'Content-Type: application/json' \
     -d '{"number":"ACME-1001","premium":1200,"effectiveFrom":"2026-10-01"}'
curl -X POST "$API/policies/1/activate?reason=Signed%20by%20the%20customer"
curl -X PUT  "$API/policies/1/premium?premium=1350&reason=Risk%20re-assessment"

policies holds one row, as before. policies_history now holds three versions of it:

operation	status	premium	valid_from	valid_to	reason
1 (insert)	Draft	1200	10:00:00	10:01:00
2 (update)	Active	1200	10:01:00	10:02:00	Signed by the customer
2 (update)	Active	1350	10:02:00	infinity	Risk re-assessment

7. Ask the questions

What did the policy say at a given moment?

// GET /policies/1/as-of?at=2026-09-01T10:00:00Z
app.MapGet("/policies/{id:int}/as-of", async (int id, DateTimeOffset at, InsuranceDbContext db) =>
    await db.Policies.AsOf(at).SingleOrDefaultAsync(p => p.Id == id) is { } policy
        ? Results.Ok(policy)
        : Results.NotFound());

AsOf reads the version that was current at that instant. A claim filed at 10:01:30 sees the active policy at the old premium of 1200; a policy that did not exist yet, or was already deleted, returns 404.

Who changed it, and why?

// GET /policies/1/history
app.MapGet("/policies/{id:int}/history", async (int id, InsuranceDbContext db) =>
    await db.History<Policy>()
        .Where(v => v.Entity.Id == id)
        .Select(v => new
        {
            v.ValidFrom,
            v.ValidTo,
            v.Operation,
            v.ChangedByName,
            v.Reason,
            v.Entity.Status,
            v.Entity.Premium,
        })
        .ToListAsync());

History<Policy>() returns every version with its period and change context, newest first:

[
  { "validFrom": "2026-09-14T10:02:00Z", "operation": 2, "changedByName": "alice", "reason": "Risk re-assessment", "status": 1, "premium": 1350 },
  { "validFrom": "2026-09-14T10:01:00Z", "operation": 2, "changedByName": "alice", "reason": "Signed by the customer", "status": 1, "premium": 1200 },
  { "validFrom": "2026-09-14T10:00:00Z", "operation": 1, "changedByName": "alice", "reason": null, "status": 0, "premium": 1200 }
]

What was deleted, and by whom?

After a DELETE /policies/1?reason=Duplicate, the policy is gone from policies, and AsOf stops finding it from that instant on. Its history stays, ending in a tombstone that records the delete:

// GET /policies/deleted
app.MapGet("/policies/deleted", async (InsuranceDbContext db) =>
    await db.History<Policy>()
        .Where(v => v.Operation == VersionOperation.Delete)
        .Select(v => new { v.Entity.Id, v.Entity.Number, DeletedAt = v.ValidFrom, v.ChangedByName, v.Reason })
        .ToListAsync());

What you built

• Every change to a policy is kept, with who made it and why, in the same transaction as the change.
• Point-in-time and audit questions are ordinary LINQ queries.
• The history schema is in your migrations and evolves with the entity. Removing a property later keeps its history column; see Evolving a temporal entity.

Next steps

• Rules for historical queries: what composes with AsOf, and what throws.
• Trust model: what the change-context columns do and do not prove.
• Transactions: using Hindsight with your own transactions, retries and TransactionScope.