Why the audit request arrives late.
Audit is rarely in the first version of a line-of-business application. The schema is designed around the current state of things: an order has a status, a customer has an address, a price has a value. Overwriting it on update is what a CRUD application does, and for years nobody notices what was lost.
Then the question arrives from outside the engineering team. An auditor wants to know who approved a change and when. A customer disputes an invoice and sales needs the price in force on the day. A regulator asks for every modification to a record over a period.
Each is a question about the past, and a table that holds only the present cannot answer it. The database has no record of the previous value; the application log, where one exists, has the request but not the data; memory is not evidence. That is when an audit trail becomes a requirement.
Option one: EF Core interceptors and audit tables.
EF Core has supported SaveChanges interception since version 5.0. An ISaveChangesInterceptor runs before and after every SaveChanges call and can read the original and current value of every property on each tracked entity, then write the difference to an audit table with key, state, timestamp, and whatever user the application knows about. Microsoft’s documentation shows the pattern and calls it a simplistic example rather than a robust auditing solution.
It records what changed, property by property, on any database EF Core supports, in days. What it misses matters more:
- Intent
- It sees that Status went from Active to Cancelled, not that the customer cancelled because the delivery was late; the reason lives in the request, not the entity.
- Anything that bypasses the change tracker
- ExecuteUpdate and ExecuteDelete, added in EF Core 7.0, have no interaction with the change tracker. Raw SQL, stored procedures, and other applications writing to the same database never call SaveChanges.
- Deletes without context
- A deleted entity is recorded with its last values; rows removed by a database cascade are not, unless they were loaded.
Option two: SQL Server temporal tables.
A system-versioned temporal table is a SQL Server feature, available in every version still under support and in Azure SQL, that keeps every previous version of a table’s rows. The table gains two datetime2 period columns and a history table of the same shape; on every update or delete the engine copies the previous row version there, stamped with the UTC begin time of the transaction. EF Core has mapped temporal tables through IsTemporal() since version 6.0 and exposes the history through TemporalAsOf and its sibling query operators.
The point-in-time query is the reason to choose it: FOR SYSTEM_TIME AS OF a date returns the table as it was, through joins, which is what the invoice dispute actually asks. A HISTORY_RETENTION_PERIOD, available from SQL Server 2017, lets a background task delete aged history; the default is infinite.
It records state, not intent, and nothing about who or why unless your own columns carry a modified-by value. Adding an identity or computed column requires SYSTEM_VERSIONING to be switched off and on again; TRUNCATE TABLE is not permitted while it is on; and any write made while versioning is off, a bulk load included, leaves no history. Check the considerations page on learn.microsoft.com before relying on it.
Option three: event sourcing.
Event sourcing changes what the system stores. Instead of a current-state row that gets overwritten, the write model appends events that describe what happened in business terms: OrderCancelled with a reason and the user who cancelled it. Current state is rebuilt by replaying the events, so the event stream is the audit trail rather than a copy of it.
That is the one property the other two options cannot offer: intent. An event named StatusUpdated is not event sourcing, it is CRUD with extra steps; the discipline is naming events for the business decision, which means the business must be able to name them. Point-in-time questions are answered by replay; reporting by projections into read models.
The costs are real. Events are a contract that must be versioned when the business changes its mind, and the modeling takes a team that understands aggregates and consistency boundaries. Reference and supportive data stay CRUD; a catalog gains nothing from a history of itself. Event sourcing belongs inside one bounded context that needs it, never as a whole-system architecture, and it is a separate decision from CQRS. A well-built CRUD model with a temporal table beats a badly built event store.
Choosing between them.
The decision is about which question you are being asked. Work down the list; the first match is usually the answer.
- You need something defensible this quarter
- A temporal table on the tables in question. A schema change, not an application change; it captures writes from every source, stored procedures included.
- The database is not SQL Server, or you cannot change the schema
- An EF Core interceptor writing to an audit table, on the understanding that anything outside SaveChanges is not covered and must be listed.
- The question is who and why, not what
- An interceptor can stamp the user; only the application can record the reason, and only event sourcing makes the reason structural.
- The business describes the process as a sequence of decisions
- Orders, claims, approvals, contracts: contexts where users argue about what was decided and when are where event sourcing pays for itself.
- The pain is reporting, not audit
- A read model or a reporting database, fed from any of the three. History and reporting are different problems that arrive in the same meeting.
- The data is reference data
- Leave it as CRUD; add modified-by and modified-at columns if you must.
Retrofitting an existing application.
An application in production does not get a clean sheet. Meet the compliance requirement first with the cheapest option that covers the writes you actually have, then decide separately whether any part of the system deserves an event-sourced core.
That means a temporal table on the audited tables where the database is SQL Server, or an interceptor and audit table where it is not, plus an inventory of every write path that bypasses it: ExecuteUpdate calls, raw SQL, stored procedures, scheduled jobs, other applications. That inventory is a finding in itself: it is also the list of places where business rules have leaked out of the domain model.
Event sourcing is then reserved for the one bounded context where the business genuinely thinks in events and the questions are about intent. Rebuilding that context inside the existing codebase, with the rules in one place and projections feeding the existing reports, is the work on our .NET Core application modernization page. Deciding which context that is, if any, is what the Legacy Software Assessment is for: it inventories write paths, audit gaps, and rules per layer before anyone commits to a design.
Three ways to keep history, side by side.
| Option | What it records | Who and why | Point-in-time query | Bypass risk | Effort |
|---|---|---|---|---|---|
| EF Core interceptor and audit table | Before and after values per tracked entity | User if the application stamps it; never the reason | Reconstructed from audit rows; no native query | High: ExecuteUpdate, raw SQL, stored procedures | Days; any database EF Core supports |
| SQL Server temporal table | Every row version, with UTC period columns | Neither, unless your own columns carry a modified-by value | Native: FOR SYSTEM_TIME AS OF, through joins | Low: every write, any source, while versioning is on | Days; SQL Server or Azure SQL only |
| Event sourcing | Business events with intent, in order, per aggregate | Both, inside the event itself | Replay to any point; projections for reporting | None inside the context; nothing else writes state | Months; modeling discipline, event versioning |
Common questions
Do SQL Server temporal tables record which user made a change?
No. The engine records only the period columns: the UTC start and end of each row version. If you need the user, add a modified-by column the application sets on every write; the history then versions it with the row.
Can an EF Core interceptor catch every change to the database?
Only changes that go through SaveChanges. ExecuteUpdate and ExecuteDelete, raw SQL, stored procedures, and other applications writing to the same database never reach the change tracker, so the interceptor never sees them. List those paths before relying on it.
Is event sourcing the same as an audit log?
No. An audit log is a record kept beside the data; the event stream is the data. That is why it can carry intent, and why it is a larger commitment: the events are a contract, and current state exists only by replaying them.
Can we add temporal tables without changing the application?
Usually. Converting an existing table is an ALTER TABLE that adds the period columns and switches SYSTEM_VERSIONING on; the application keeps reading and writing as before. EF Core can make the same change through a migration.
Does an audit trail require CQRS or microservices?
No. All three options work inside a single deployable application on one database. CQRS is a separate decision about separating reads from writes, and event sourcing is a separate decision again; you can adopt either without the other, and neither needs the cloud.
Which option should we start with?
The one that covers the writes you actually have, at the lowest cost, in the time allowed. For most .NET applications on SQL Server that is a temporal table. Event sourcing is a design decision for one bounded context, made after the compliance question is answered.
Not sure where to start?
Start with a fixed-scope Legacy Software Assessment: a read-only review of your application’s source, database, configuration, logs, and architecture, ending in findings with evidence and a written recommendation. Quoted after the free 1-hour consultation; creditable toward a subsequent modernization project.