Making Mutable Data Auditable in Production

  • SCD2
  • Data Modeling
  • Schema Design

Mutable record history tracking sounds pretty simple in theory, but it gets difficult in practice. I ran into an example of this recently when implementing a payment system with certain mutable records that still needed a history of changes of those.

We are talking about people here, for example company owners, representatives, directors etc, in other words a mutable person record.

At first, this seemed to be a typical case of CRUD. Easy and clean design that could be prototyped on a Monday and shipped on Tuesday.

However, once I had started working on integration with external systems (KYC/AML) and documenting what capabilities the system should have in production, I found out that there are a couple of things needed here, which are not API-oriented.

  1. What is the “current” person record state?
  2. What was the exact person record state “on a specific date”?

The first question is rather obvious. The second question destroys the idea of updating the record in-place.

The fact is that if we use plain update, then the previous version is lost and we cannot reconstitute it later. And in payment systems, “previous version” is sometimes the most important information.

This is where SCD2 kicks in: keeping current view of the data, keeping the historical view and querying both in a regular manner.

I’m learning this pattern while implementing it, so don’t copy everything you read here as-is ;)

The core issue

A regular UPDATE person SET ... pattern is perfect if all we care about is the most recent value. Most applications live in this space.

Not so in payment systems. Person information changes over time. The user fixes a spelling mistake in his name. Verification status transitions from pending to verified to requires_review. External references are added weeks after the initial creation of an entry. We’ve overwritten the history, and now that an auditor comes knocking, asking what data we sent to the KYC provider on March 3rd, we have no choice but to grep through our application logs and pray they were well structured and not deleted from Datadog :’(

So, for such a system, history support becomes crucial right from the start.

Picking an alternative approach

Well, to tell the truth, at first I was not very interested in using SCD2. I was unaware of this method and my initial desire for an MVP would be to use something familiar. So, I did research on other methods and how they would impact our read and migration operations.

  • Audit log table: what about creating a separate person_audit table, and making a copy of the record before any mutation is made? This may sound feasible, but again, in this case, the current state and historical state will be separated in two different tables, normally with different schema evolution overtime. Any schema change will require a two-table migration. History reading will be a two-table query. This will be fine if all you want from history is a post-mortem investigation, but my requirements were different.

  • Event sourcing: it is a good solution, but it requires architecture decision, too. In my case, there was no need to replay event streams or model each change as a domain event. I needed versioned snapshots of the person record with “current” and “as-of” semantics. Event sourcing seemed like more surface area than the problem required.

  • Soft deletes with an additional modified_at column. It provides a last update timestamp but no history. So in any case, we will have one row per person and will not be able to say what does this record look like at some specific date?

The SCD2 approach matched my requirements because it stores the history within the table and has the following logic:

  • every change adds a new row
  • old row gets a valid_to timestamp and closes the period of validity
  • new row has valid_to = NULL and it means that it is a current state

With one table, I can write two simple queries: “what is current?” and “what did this record look like at time t?”

The schema

Here’s a minimal slice of the schema from our system:

CREATE TABLE person (
  id UUID PRIMARY KEY,
  identity_id TEXT NOT NULL,
  version INTEGER NOT NULL,

  valid_from TIMESTAMPTZ NOT NULL,
  valid_to TIMESTAMPTZ,
  is_current BOOLEAN GENERATED ALWAYS AS (valid_to IS NULL) STORED,
  superseded_by UUID REFERENCES person(id),

  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email TEXT,
  verification_status TEXT,
  change_reason TEXT,

  CONSTRAINT uq_person_identity_version UNIQUE (identity_id, version),
  CONSTRAINT chk_person_valid_period CHECK (valid_from < valid_to OR valid_to IS NULL)
);

CREATE UNIQUE INDEX uq_person_current
  ON person (identity_id)
  WHERE valid_to IS NULL;

It looks like a very simple schema, yet there are some interesting fields:

  • id vs identity_id: by far, the most critical decision in this schema design. identity_id is the stable ID of a person through all their versions. It says who is this. id is an ID of a particular version row. It says which version snapshot of a person are we interested in. All foreign keys from any other related table will reference id, i.e., they will reference a specific version, not an abstract person concept.

  • version: an integer, incremented each time when a new row appears for a given identity_id. Together with unique constraint (identity_id, version), it guarantees you don’t accidentally create two version rows for the same identity. Also, it allows you to easily answer how many times has the person changed without scanning timestamps.

  • valid_from and valid_to: this is the period in which the row was a current version of a person. If valid_to = NULL, the row is current. Check constraint valid_from < valid_to OR valid_to IS NULL prevents logically impossible intervals where a row expires before becoming active.

  • is_current: this is a generated column which is based on valid_to IS NULL. We could omit it and use valid_to IS NULL in all our filters, and it will be just fine. However, I’ve included it for clarity in queries, as it is practically free to maintain, being handled by the Postgres automatically.

  • superseded_by: the forward link from the superseded version to the newer one. Strictly speaking, not mandatory for implementing SCD2 properly – we can always find the next version by doing a query on identity_id with the next version number. Having it, however, makes traversal in both directions possible without sorting.

  • change_reason: free form text field holding the explanation of why the record was changed, e.g. “User has corrected name”, “KYC verification has been updated by provider” or something along these lines. From my experience, it is the kind of feature we will appreciate in six months when we will try to understand what was the purpose of version 4.

  • partial unique index uq_person_current: this one actually serves its purpose in making sure that there is no more than one row where valid_to IS NULL for the same identity_id. In simple words, there is always one current state possible. And if our writing mechanism fails and tries to create two states at once, the database will prevent this attempt from happening.

Before moving to tricky parts concerning related tables, it is necessary to define the two main operations we perform on SCD2: write and read. Of course, the latter operation pays off, but it works only if the former is done strictly.

Write semantics

The read side of SCD2 is easy. The write side is where we either get it right or spend weeks debugging subtle data corruption.

The rule is simple: never do an in-place update of mutable person data on the current row. Instead, close the current row and open a new one. ALWAYS IN TRANSACTION

BEGIN;

-- close the current row
UPDATE person
SET valid_to = GREATEST(valid_from + INTERVAL '1 microsecond', $effective_at)
WHERE id = $current_row_id;

-- insert the new version
INSERT INTO person (
  id, identity_id, version, valid_from,
  first_name, last_name, email, change_reason
)
VALUES (
  $new_id, $identity_id, $next_version, $effective_at,
  $first_name, $last_name, $email, $reason
);

-- link old row to new row
UPDATE person
SET superseded_by = $new_id
WHERE id = $current_row_id;

COMMIT;

The GREATEST clause in step 1 might seem strange. It seemed strange to me, too. But it does solve a real problem: if there are two updates occurring quickly after each other (within the same millisecond), $effective_at might be equal to valid_from, thus valid_from = valid_to. This will violate the check constraint, and our transaction will fail. The microsecond safeguard ensures the window remains positive even for this extreme case. The code might be defensive, but it prevents a category of bugs that only appear when the system is under heavy load.

All three steps need to be in the same transaction. If step 1 completes and step 2 fails, we’ll close the only existing row, so there won’t be any row representing the current state of the person. Stale data is problematic, whereas no current row means a broken system.

Read semantics

Once the write path is strict, reads are almost boring.

Current state:

SELECT *
FROM person
WHERE identity_id = $1
  AND valid_to IS NULL;

Point-in-time lookup (“what did we know on March 3rd?”):

SELECT *
FROM person
WHERE identity_id = $1
  AND valid_from <= $2
  AND (valid_to > $2 OR valid_to IS NULL);

And the full version history:

SELECT *
FROM person
WHERE identity_id = $1
ORDER BY version;

There are special handling or joins against audit tables here, instead all our queries are against one table with a time range filter.

The real complexity

The person table itself was actually the easy part. Things start to get complicated when other tables reference person.id.

Remember that in this domain, a “person” can be a beneficial owner, a representative, a director and so on. Thus we have some kind of person_role table:

CREATE TABLE person_role (
  id UUID PRIMARY KEY,
  person_id UUID NOT NULL REFERENCES person(id),
  role_type TEXT NOT NULL,
  active_to TIMESTAMPTZ
);

This table says “person X has role Y”. The twist is that person_id references a version row, not the identity of a person. Which means that when a new person version appears, all existing role rows will still reference an old person version.

That becomes a problem if our application joins roles to the current person row (where valid_to IS NULL). The role hasn’t been deleted. It’s still in the database. But it now points at a row that’s no longer current so it can disappear from our query results.

This is a real footgun I came across while working with this design.

My fix was straightforward: when a new person version is created, reassign active related rows to the new version ID, in the same transaction.

newPerson, err := personRepo.Update(ctx, params)
if err != nil { return err }

if newPerson.ID != currentPerson.ID {
    err = personRepo.ReassignRolePerson(ctx, roleID, newPerson.ID, &actorID)
    if err != nil { return err }
}

this is a tiny piece of code with a huge significance for correctness. If we skip this step just once on a related table, we will get a data loss effect that will appear only in production if somebody updates a person who has roles too.

The alternative would be to use identity_id as foreign key references in all related tables, so these references always refer to the person concept and not its particular version. But I decided against it since sometimes I need to know the version of a person for assigning roles.

Outro

Thus, SCD2 turned out to match the problem in shape, since we could answer both “what’s current?” and “what was on date X?” using the same table without an additional synchronized audit model.

This comes at a cost (and looks scary), but the gap between “works” and “almost works” came down to being careful about certain rules in the database. But with that, reading stays easy enough and the model is resilient to the dirty world of onboarding data arriving late or getting changed later.