Why do we audit data? When & how would we want to retrospectively view what information has changed over time? Answering these questions will help you zero in on an auditing approach that works for your project.
Audacia has successfully implemented Temporal tables (or system-versioned tables) in one of our software development projects, Olympus. This is a SQL Server feature that Microsoft added Entity Framework (EF) Core support for in version 6. Here we detail Audacia's journey in replacing our existing auditing solution with temporal table auditing on Olympus.
Temporal tables have been available since SQL Server 2016 and offer built-in support for viewing data at any given time. To do this, the table is created with PERIOD information, along with two datetime2 columns which are automatically set when changes are made to the record.
Our existing solution
This solution was to utilise EF Core's ChangeTracker by overriding the SaveChanges method, which pre-dates EF Core 6 and has been used with great success on many projects at Audacia. At the point of saving, a record would be inserted into a SQL table Audit.AuditEntries:
It's important to understand the following about this table:
- This is not entity-specific, and uses the
FullTypeNameas a discriminator to know which entity (or table) the record represents, andPrimaryKeyValuesto know which records have been changed. - The
Datacolumn details the changes as a delta i.e before/after, so you immediately know what's changed.
Why we changed
Audacia uses the latest technologies in .NET releases and ensures that existing projects are updated to the latest LTS version. When Microsoft announced it was implementing Temporal Table support in EF Core 6.0, we were keen to try this to see what potential benefits it could have.
Our initial assumption was that Temporal Table support would give us less code to maintain, given that we would be leveraging a pre-existing feature in SQL server. We decided to use an internal project, Olympus (more information here), as a case study, as it was already using EF Core 6.0 and has the above Audit.AuditEntries solution in place.
How temporal tables work
In SQL Server, a temporal table looks like the below:
This differs to a 'standard' SQL Server table in that:
- Each table that is system-versioned has a corresponding history table with a matching schema of the parent table.
- The table includes Period Start / End columns representing the time range at which that version of the entity was active.
When a system-versioned record is saved in SQL, a row will be visible in the history table, with a PeriodEnd of the time the save occurred. The 'active' record in the main table now has a PeriodStart equalling the same date time. It's important to note that this is a SQL Server feature, which Microsoft then added support for in EF Core by popular demand.
On the .NET side the API was updated to allow a developer to query historic information for a DbSet.
// Go back in time to see what the DbSet looked like at that point
var lastWeekAllowances = _dbContext.LeaveAllowances
.TemporalAsOf(DateTime.Today.AddDays(-7));
// Find all historic versions of a given entity
const int myAllowanceId = 5;
var versionsOfMyAllowance = await _dbContext.LeaveAllowances
.TemporalAll()
.ToListAsync(a => a.Id == myAllowanceId, cancellationToken);
This approach to auditing can be considered as 'snapshot' as opposed to our existing implementation, which is more of a 'delta' approach.
Software development project: Olympus implementation
To set a table up as system-versioned, you can use the IsTemporal method on a single entity's configuration:
builder.ToTable(
"LeaveAllowance",
Schemas.People,
b => b.IsTemporal());
We decided to use a more generic approach by overriding OnModelCreating in our DbContext to set this up for all entities by default:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.ApplyConfigurationsFromAssembly(GetType().Assembly);
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
entity.SetIsTemporal(true);
}
}
It wouldn't be too much development work to opt out of this behaviour on a per-entity basis if we were to introduce a check for an IIgnoreAudit interface to the above code, for example.
Key points for implementing temporal table auditing
Changing your audit mechanism is very impactful. Conscious decisions are made to audit data due to the importance of knowing the what/when/who of information changing. The importance of migrating existing data must be considered, and is not necessarily straightforward. An auditing solution that works for the business as well as one with minimal technical hindrances can be invaluable in the security and robustness of your application. Therefore, investing time and resource in implementing the right approach is usually more than worthwhile.
In this section we'll cover the main things we learned from our implementation and that we believe are worth evaluating if you're currently considering temporal table auditing.
Less code to maintain
A smaller codebase is more manageable, and having less code executed as part of the save reduces the risk of it failing for whatever reason. Temporal tables have been available from SQL Server 2016 and are well-supported in that respect compared to a custom implementation from ourselves as a company.
Easy audit trail implementations
Using the TemporalAll method for a single record will give you all versions of that record since it was created. This would facilitate showing an end user some sort of audit trail for a record without too much overhead from a development perspective. Similarly, restoring previous versions would be a simple task compared to a delta-style auditing approach.
Everything is audited
It may be that you have an entity with some properties that either never change, or you wouldn't care if they did for whatever reason. For example, if your application doesn't allow a user's date of birth to be amended, storing the date of birth for every version of every record might seem unnecessary.
Introduces a SQL Server dependency
As mentioned previously, system-versioned tables are pre-existing SQL Server features. One of the great features of Entity Framework as an ORM is that it's largely database provider agnostic. However, implementing temporal table auditing will mean that you have to use SQL as your database provider. The introduction of a dependency is important to consider, even if you are comfortable with it (e.g. if it’s an appropriate database provider for your application).
As you can see, the method to set a table as temporal belongs to the SqlServerEntityTypeExtensions class:
Single table vs table per entity
Separating the auditing out so that it's stored per-table lessens the risk of performance issues when querying audit information for a single entity, as opposed to storing all audit information in one place. However, we've lost the ability to run queries across all entities. For example, we can use the UserId column in the existing approach to quickly find out what information a user changed in a given time period. In our use case, this didn't matter, but before implementing you should consider what you might lose from a functionality perspective.
GDPR considerations
By default, SQL server will not allow you to update data in a history table to ensure it always reflects what the data looked like at any given period of time. This can cause complications when storing PII and actioning a user's right to be forgotten, as you would be required to anonymise audit records containing PII.
It is possible to change data in a history table by setting SYSTEM_VERSIONING = OFF as Microsoft explains here, but keep in mind this would be an extra consideration.
Schema changes
Because system-versioned tables reflect the same metadata as its parent table, schema changes need to be carried out to the history table when required. In EF Core, we handle this with code-first migrations. The nature of this functionality causes complications in a number of ways, which can all be reduced down to how the migration can be applied to the history table. A common example of this is making an optional column non-nullable, and having NULL values in your history table. Steps would need to be taken to update this historic data (mentioned above), which can slow down day-to-day development tasks.
Conclusion
From our experience with this feature, we have found temporal table auditing surprisingly straightforward to implement. In our case, migrating existing information wasn't necessary, so we can't speak to any challenges that may have been involved. As a quick way to implement a snapshot-style auditing approach for your system, temporal tables are an excellent solution. However, the control you have over this auditing, for example dictating auditing on a per-column basis, is limited. Knowing what you will use your auditing for is vital to ensure you select the most appropriate solution, as changing tack later on can snowball into an expensive rework.
At Audacia, we're always extremely interested in the latest .NET features we can use. Having internal projects to use as case studies provides valuable insight into why we might choose temporal table auditing as standard going forward.
Audacia is a leading software development company based in the UK, headquartered in Leeds. Audacia has established a reputation as a critical technology partner with long-standing relationships; helping customers automate their processes by delivering intuitive and reliable technology solutions.
Want to learn more about the services we offer at Audacia? Get in touch today on 0113 543 1300, or email info@audacia.co.uk
