DEV Community

Cover image for Day 25: The Audit Trail and the Before and After Values of a Change
Jeff Tsai
Jeff Tsai

Posted on Originally published at hackmd.io

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:

  1. Why the name is copied in as well, rather than storing an id and joining back for it
  2. 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
  3. Why the access record covers only single record reads, and why it is the one of the three that is off by default
  4. Why a change is one row per save, why that write sits outside the transaction, and what happens when it fails
  5. How what changed is stored as a DiffGram, and why there is only one place it can be captured

1. 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:

The question to answer Where it lands
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 (Connections, Transactions and Query Conditions in the Data Access Layer covered how logs can occupy a database to themselves), 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));
Enter fullscreen mode Exit fullscreen mode

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.


2. 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.


3. 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. The category table in that post on the data access layer described log as the record of operations, logins and changes, and operations is this axis.

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.


4. 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.

Extension Points on a Business Object and When to Override Them put this decision next to its opposite: 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:

Situation Behavior
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.


5. 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>
Enter fullscreen mode Exit fullscreen mode

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, and the post on business object extension points explained why. 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, ...);
Enter fullscreen mode Exit fullscreen mode

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      = demo          company_id   = NORTHWIND
user_name    = Demo User     company_name = Northwind Traders
source       = Customer.Save prog_id      = Customer
change_kind  = 2             is_sensitive = 1
row_key      = sys_rowid of the ALFKI row
api_key_id   = (empty)       api_key_name = (empty)
Enter fullscreen mode Exit fullscreen mode

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 of the kind The API Payload Security Pipeline: Order, Protection Levels and Keys described 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, so the opening described in The Three Axes of Permissions: Action, Row and Column, where a form that declares nothing skips the whole thing, does not exist here. Writing hangs off a boolean switch and reading hangs off a permission model, and a deployment with only the write side turned on is exactly the one that most needs telling to connect the read side up.


In closing

An audit trail has no readers at all on the day it is written, and it only ever grows, until it has to be moved to another machine and split by year into several databases. That is exactly why the identity columns copy the names in as well: by then, a join is not a question of being slow, there is simply no such route.

So not one of the decisions in this post is accepted on today's date. What gets recorded decides what can be asked on that day; each row being complete in itself decides whether it can be understood on that day; that column carrying its own structure decides whether it can still be read back after the table has been revised; and that write outside the transaction decides whether it was ever written down at all.

The four posts in this chapter have looked at the same call from four angles: as bytes to be serialized, as bytes to be protected, as a request to be judged for permissions, and as an event to be recorded. The bottom line across all four is the same: everything the caller sends up is a claim, not a fact.

Nothing that can determine the outcome is the caller's to decide; and whatever is to be recorded has to be recorded as what the caller actually sent. The two read as opposites, and are in fact two sides of the same line.

The next post, Numeric Semantics, Multiple Currencies and Units of Measure, changes level, to the values that have been stored and recorded themselves: what a number actually stands for inside the system.


📚 Part of the thirty-post series ERP Architect's Notes: Designing a Definition-Driven Framework

📘 Original article on HackMD, in Traditional Chinese:
👉 https://hackmd.io/@jeff377/day-25-audit-trail

📢 You are welcome to republish this, please credit the source.
📬 Follow for more engineering notes:
Facebook | HackMD | GitHub | NuGet

Top comments (0)