What is Hindsight?

Hindsight gives an EF Core entity a system-versioned history. Every insert, update and delete keeps the previous state as a version in a history table, together with the exact period during which that version was the current row. You read the history back with LINQ.

That answers questions an ordinary table cannot:

• What did this policy look like when the claim was filed? (AsOf)
• How has this premium changed over time? (AllVersions)
• Which states was it in during Q3? (FromTo and ContainedIn)
• Who changed it, when, and why? Who deleted it? (History<T>)

// Make an entity temporal; the next migration adds its history table.
modelBuilder.Entity<Policy>().IsTemporal();

// What did this policy look like when the claim happened?
var policy = await db.Policies
    .AsOf(claim.OccurredAt)
    .SingleAsync(p => p.Id == claim.PolicyId);

// Who changed it, when, and why?
var audit = await db.History<Policy>()
    .Where(v => v.Entity.Id == claim.PolicyId)
    .Select(v => new { v.ValidFrom, v.ChangedBy, v.Reason, v.Entity.Status, v.Entity.Premium })
    .ToListAsync();

How it works

• One history table per temporal entity, next to the main table. It is part of the EF Core model, so dotnet ef migrations add creates it and keeps it in step as the entity changes.
• A history writer fills it on every change: a PostgreSQL trigger generated by the migration (recommended), or a SaveChangesInterceptor in your application. See Choosing a history writer.
• Change context, meaning the user, the correlation id and the reason, comes from your application through an IChangeContextProvider and is stored on every version. See Change context.
• Historical queries read from the history table and return read-only snapshots. See Querying history.

The main table stays exactly as it was: same columns, indexes, foreign keys and queries.

Compared with the alternatives

	Hindsight	EF Core temporal tables	temporal_tables extension	Audit.NET
PostgreSQL	✅	❌ SQL Server only	✅	✅
Needs superuser or an extension	❌ plain user, PostgreSQL 14+	—	✅ C extension	❌
History schema in EF Core migrations	✅	✅	❌ by hand	❌
Point-in-time queries as typed LINQ	✅	✅	❌ raw SQL	❌
Who, why, correlation id	✅ from the application¹	❌	❌ the trigger sees rows only	✅
Half-open [from, to) periods, infinity for current	✅	✅	✅	—

¹ Asserted by the application, not guaranteed by the database. See the trust model.

When Hindsight is not the right tool

• You need application time, as in "this tariff applies from March 1". Hindsight records when the database held a row (system time), not when a fact was true in the real world. See Concepts.
• You want to restore an entity to an old version with one call. History is read-only; you copy values back yourself.
• Your database is not PostgreSQL. There are no plans for other providers.
• You need an audit trail that people with database access cannot forge. The change context is application-asserted; see the trust model.

The complete list is in Limitations.

Requirements

• .NET 10 and EF Core 10 with Npgsql.EntityFrameworkCore.PostgreSQL.
• PostgreSQL 14 or later, and a regular database user. No superuser, no extensions.

Continue with Getting started.