Getting started

This page takes an EF Core application on PostgreSQL to its first historical query. The examples use the Policy entity from the repository's sample project:

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; }
}

1. Install the package

dotnet add package Hindsight.EntityFrameworkCore.PostgreSQL

2. Mark the entity as temporal

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Policy>().IsTemporal();
}

3. Enable Hindsight on the context

services.AddDbContext<AppDbContext>(options => options
    .UseNpgsql(connectionString)
    .UseHindsight(hindsight => hindsight.UseHistoryWriter(HistoryWriter.Trigger)));

UseHindsight adds a history table to the model for every temporal entity and installs the history writer that fills it. HistoryWriter.Trigger writes history with a database trigger, which also catches changes that bypass SaveChanges. It is the recommended writer; see Choosing a history writer.

Choose the writer explicitly

UseHindsight() on its own uses HistoryWriter.Interceptor, which records only changes made through SaveChanges. Pass UseHistoryWriter(HistoryWriter.Trigger) unless your database role cannot create functions and triggers.

4. Add a migration

dotnet ef migrations add MakePoliciesTemporal
dotnet ef database update

For a policies table, the migration creates:

• policies_history, with the entity's columns plus the period columns valid_from / valid_to and the change-context columns (see the full column list);
• two indexes on it that historical queries use;
• with the trigger writer, a policies_history_write() function and a policies_history_trg trigger on policies.

5. Save changes as usual

var policy = new Policy { Number = "ACME-1001", Status = PolicyStatus.Draft, Premium = 1_200m };
db.Policies.Add(policy);
await db.SaveChangesAsync(); // version 1 opens

policy.Status = PolicyStatus.Active;
await db.SaveChangesAsync(); // version 1 closes, version 2 opens

Nothing about writing changes. Each SaveChanges that touches a temporal entity closes the current version and opens a new one:

version	status	valid_from	valid_to	operation
1	Draft	09:00:00	09:05:00	1 (insert)
2	Active	09:05:00	infinity	2 (update)

6. Read the history

// The policy as the database held it last Monday (null if it did not exist yet).
var then = await db.Policies
    .AsOf(lastMonday)
    .SingleOrDefaultAsync(p => p.Id == policyId);

// Every version: when it was current, and what happened.
var versions = await db.History<Policy>()
    .Where(v => v.Entity.Id == policyId)
    .Select(v => new { v.ValidFrom, v.ValidTo, v.Operation, v.Entity.Status })
    .ToListAsync();

Historical queries are always no-tracking, and their results cannot be saved back.

Next steps

• Concepts: versions, periods and tombstones, in five minutes.
• Tutorial: an audit trail for insurance policies: record who changed what, through an ASP.NET Core API.
• Change context: store the user, the correlation id and the reason with every version.