Day 25: The Audit Trail and the Before and After Values of a Change
By the time someone asks who changed the amount on this record last month, it is usually months later.
Whoever is asking has neither the screen as it looked then nor the build that was running, only the few rows left behind at the time. Who logged in, who changed which field from what to what, who looked at which record: every decision in the auditing layer has to assume that scene first, including the ones that look like nothing more than saving space.
This post covers:
- Why the name is copied in as well, rather than storing an id and joining back for it
- How one column encodes the whole outcome of a login, and why the failure reason is kept apart from the line shown to the user
- Why the access record covers only single record reads, and why it is the one of the three that is off by default
- Why a change is one row per save, why that write sits outside the transaction, and what happens when it fails
- How what changed is stored as a DiffGram, and why there is only one place it can be captured
Three tables and the columns they share
Recording every call is not workable. Reads far outnumber writes, and the volume would go straight through the roof.
The questions auditing has to answer come down to three, one question one table:
- Who logged in and what came of it -
st_log_login - Who looked at which record -
st_log_access - Who did what to which record, and which fields went from what to what -
st_log_change
The three tables live in the log database category. The whole thing is off by default, and each of the three has a sub switch of its own.
Whichever table it is, every row starts with the same set of shared columns. To record who did something, the intuitive approach is to store a row identifier for the user and join back for the name when it needs displaying.
That does not work here, and not for performance reasons. log is a database category of its own, physically separate from common and company, and it can be moved to another machine, or split by year into several databases. A join across databases is not slower, it is absent. A column that only means something once joined is, here, a column that means nothing.
So every piece of identity is stored as two columns, an id in one and a display name in the other (excerpt from AuditEntry.cs):
columns.Add(new AuditColumn("user_id", UserId));
columns.Add(new AuditColumn("user_name", UserName));
columns.Add(new AuditColumn("company_id", CompanyId));
columns.Add(new AuditColumn("company_name", CompanyName));
// access_token / trace_id / client_ip / source ...
columns.Add(new AuditColumn("api_key_id", ApiKeyId));
columns.Add(new AuditColumn("api_key_name", ApiKeyName));
This data is not normalized, and deliberately not. A user changes their name, a company changes its letterhead, and the old rows keep the name as it was then, which no longer lines up with st_user as it stands today.
That is exactly what auditing wants: a record is meant to state how things were at that moment, not how they are today. The same trade-off would be a defect elsewhere; here it is the specification.
Within the key columns, the id is not a secret, so it can go into an audit row; the secret itself the framework does not hold in plain text once it has been issued, so it cannot.
This group of columns is most useful on the login table: a run of failed logins concentrated in one application, and the same run spread across several applications, read as two different things.
The login record
The simplest of the three, and it comes on with the master switch. What it adds to the shared columns is an event value, plus a line giving the reason for a failure.
The event value carries the outcome itself, rather than there being a separate column for whether it succeeded. Login succeeded, login failed, account locked, logout, and a session opened for a user by a trusted local call: five values, each of them an outcome. Without that extra column, the case where the event value and the outcome column disagree cannot arise.
The failure reason column and the line shown on the login screen are not doing the same job. The on-screen message is always terse and does not tell the user whether the account does not exist or the password was wrong, which is the usual practice: the moment the two can be told apart, the login endpoint becomes a tool for testing whether an account exists.
The audit column has no such concern. Its reader is someone going back over the records afterwards, and the more specific it is, the more use it is.
The access record
Pulling up a record to look at it is itself something to be recorded. Who pulled up the full details of which customer, and when, matters as much in a compliance question as who changed it.
What it adds to the shared columns is which form, plus the key of that record. Pulling up the detail of a single record loads the whole record anyway, so who plus which record is the unit of one row.
It is the only one of the three that is off by default, because its volume tracks the number of queries. The deployment level switch can turn it on across the board, and it can also be turned on one form at a time: the customer master is worth recording, a code table is not.
The row is written by the single record read; list queries and lookup windows leave no record.
The change record
One save on a form, one row in the audit. That row says who, when, which record on which form, and whether it was an insert, an update or a delete, and it carries a sensitive flag.
The same row also holds the change detail: which fields changed, and what each one went from and to, all of it in a single column. The next section covers where that column comes from.
Insert, update or delete is decided by the state of the master row. Changing only the detail without touching a character of the master still counts as an update, because that whole record did change.
With the master switch off, none of it is recorded. Once it is on, the change axis is on by default just as login is, and a per form rule can override that default: change and access each have a three state value, follow the deployment level default, force recording, or force no recording.
Writing sits outside the transaction
The audit row is written outside the transaction. A save that succeeded has succeeded, and a failure to write the audit row does not drag the save down with it: the framework logs an error and the business operation finishes as normal.
Master and detail must share a transaction, while change auditing accepts not being atomic. The two decisions go opposite ways because the broken states look different. A missing detail leaves an incomplete document and raises nothing; a missing audit row means one record fewer, with the data itself correct.
Not blocking the business does not mean rows may be dropped at will. Every way the write can fail has a route out:
- Normal - Goes into a bounded in-memory queue, and a background batch writes it to the log database
- The queue is full - Switches to a synchronous write on the spot rather than being dropped
- The log database cannot be written to - Lands as a file if a fallback file is configured, and if not, all that is left is that one error
For a queue that must not drop anything, the right behavior at saturation is not to wait, it is to take another route.
One more thing turns up only at this layer: an audit write is itself a database access, and the framework records slow database accesses separately, which is another audit write. The fix is not to run that detection when the log database is the one being hit, and the loop breaks there.
With a mechanism that cuts across every layer, the easiest thing to miss is not who it fails to cover, it is that it covers itself.
The change detail
The usual way to record what changed is a field by field comparison, one row per field. Its strength is queries at field level: how many times a given field was changed over the past year, and by whom, is one SQL statement away.
The cost is that the row count is the number of changes multiplied by the number of fields, the comparison logic has to be written from scratch, and that query strength goes unused on most of the questions people actually ask, which are what changed on this document.
The framework uses what DataSet already has: GetChanges() returns the change set, and WriteXml in DiffGram mode writes it as one piece of XML carrying the new and the old values together, with the field structure as it stood at the time attached in front, all wrapped in the same outer element.
One save writes one row, the old and new values of the master and of every detail are inside that one column, and there is no custom comparison code at all.
Change the status and the freight on an order, and what that column stores has this shape:
<AuditChanges>
<xs:schema id="form" ...> ...the types of sys_id / status / freight... </xs:schema>
<diffgr:diffgram ...>
<form>
<Order diffgr:id="Order1" diffgr:hasChanges="modified">
<status>Confirmed</status>
<freight>41.90</freight>
</Order>
</form>
<diffgr:before>
<Order diffgr:id="Order1">
<status>Draft</status>
<freight>32.38</freight>
</Order>
</diffgr:before>
</diffgr:diffgram>
</AuditChanges>
Capture and write fall on either side of the save: the change set is taken before the save, and the trail is written after it. That position is pinned from both sides by DoBeforeSave and DoSave.
What follows is what it looks like in code (excerpt from FormBusinessObject.Write.cs):
bool auditChange = ChangeAuditEnabled();
using var changes = auditChange ? args.DataSet.GetChanges() : null;
var (rowKey, changeKind) = auditChange ? ExtractMasterChange(args.DataSet, masterTableName) : (null, ChangeKind.Update);
DoSave(context);
if (auditChange && changes != null)
WriteChangeAudit(changeKind, rowKey, AuditDiffGram.Serialize(changes), masterTableName, ...);
That boolean is also cost control: with it off, GetChanges is never called at all.
Getting the position wrong raises nothing, it just leaves things out. Move it after DoSave and, once the database write succeeds, the row states and the original values are cleared together, GetChanges() comes back null, and that save does not exist in the trail at all. Move it before DoBeforeSave and what gets recorded are the values from before the computed fields and the rules were filled in.
Delete is the case where no change set is available; the approach is to load a snapshot from before the delete, mark every row as deleted, and run the same GetChanges, which yields the full contents of that record as it stood before it was removed.
Reading it back rests on that structure: read the structure out first, then read the DiffGram into it, and the result is a real DataSet, with which fields changed derived by comparing the original and current values.
The structure is carried by the row itself rather than taken from today's definition, so three years later, when that table has two more columns, one fewer, or a changed type, a trail row from three years ago still reads back as it was then.
The ones written earlier do not carry this structure, and the framework tells old from new by that outermost element and takes a different route for each, without a single old row having to be migrated.
Back to Northwind
The case study turns auditing on, which brings login and change with it while access stays off, and switches the background write to synchronous so that it can be demonstrated at all.
The rule table holds three rows: orders and customers force access on, and categories force change off.
Change the contact and the city on one customer, and st_log_change gains a row:
user_id= democompany_id= NORTHWINDuser_name= Demo Usercompany_name= Northwind Traderssource= Customer.Saveprog_id= Customerchange_kind= 2is_sensitive= 1row_key= sys_rowid of the ALFKI rowapi_key_id= (empty)api_key_name= (empty)
In the column that holds the change itself, contact_name and city each have a before and an after value. The 1 in is_sensitive comes from the rule row for customers.
Force off and force on were each checked once: change a category and the data goes in while the trail gains nothing; the single record read axis goes the other way, with the deployment level off, and one read each on an order and a customer leaves one row each. Same deployment, same code, and the whole difference between recorded and not recorded sits in those three rows.
The two key columns are empty, and empty for a good reason: the case study has not issued a single key, so the validator never went looking for one and there is no id to record. A mechanism that has not taken effect leaves a trace in the audit that is not zero rows, it is two missing columns on every row.
The trail is there, and the case study cannot read it. The audit query endpoints declare Encrypted, and a plain text request comes back with This API requires encoded or encrypted transmission.
Go again by the route that performs a key exchange, get past that gate, and what comes back is Not authorized to read the audit log. The reason is that the case study has no permission model at all. The check on audit reads does not hang off FormSchema; it takes a reserved ProgId straight to the authorization service.
Comments
No comments yet. Start the discussion.