← Notebook

Audit trails as product infrastructure

A log helps you debug. An audit trail has to answer, months later, who asked, who approved, and what went live.

audit trailsevidencedata integrity

Three months after a change went live, someone asks who approved it.

You look in the logs. The application logs were deleted after thirty days. The load balancer's request log, kept longer, says POST /api/changes/42/approve 200, next to a session ID that expired long ago. The database says the change is published, which is where it ended, not how it got there. Nobody can say who asked for it, who approved it, or whether what went live is what was approved.

Everything that happened was written down somewhere, for a while, in a form meant for something else. This note is about recording it on purpose: as part of the change itself, in a form that can still be trusted when the question finally arrives. It assumes you have built a workflow where actions have consequences — the maker–checker note builds one — and that you think of logging as output for debugging.

A log is not an audit trail#

A log is written for the people running the system, to diagnose it: what the code did, where it slowed down, why it failed. Logs can be lossy without harm. They are sampled, deleted after a few weeks, free-form, and written from anywhere. A missing line makes debugging harder, and nothing more.

An audit trail is written for someone who later needs to establish what happened in the business: who did what, to which thing, when, and what the system decided. It is evidence. A missing entry means a question that has to be answered cannot be.

The two get confused because both are lines with timestamps. They answer different questions for different readers, and they fail differently, so they need different engineering.

What makes a record evidence#

A record is evidence only if it can be trusted not to have been lost and not to have been changed. Each half has a concrete mechanism.

Not lost: the event is written in the same transaction as the change it describes. A transaction groups writes so that all of them take effect or none do. Written afterwards — after the commit, or by a background queue — an event can be lost when a process dies between the two, and the change then happened with no record. Written before, it can record a change that then failed.

Not changed: the table is append-only. Rows can be added and read, and never updated or deleted. A mistake is corrected by adding a new event that records the correction, so the trail shows both the mistake and its correction; a column such as corrects, holding the earlier event's id, ties the two together. The database enforces that rule, not the application's good behaviour, because the application is one of the things the trail needs protecting from.

The event#

One row for each thing that happened. Alongside it, a minimal changes table stands in for the business data the events describe:

sql
CREATE TABLE changes (
  id         text PRIMARY KEY,
  status     text NOT NULL,
  revision   integer NOT NULL
);

CREATE TABLE audit_events (
  id              bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  occurred_at     timestamptz NOT NULL DEFAULT now(),
  actor_id        text NOT NULL,
  action          text NOT NULL,
  subject_id      text NOT NULL,
  revision        integer,
  outcome         text NOT NULL,
  reason          text,
  correlation_id  text NOT NULL,
  details         jsonb NOT NULL DEFAULT '{}'
);

Each column answers a question someone will eventually ask:

  • Who: actor_id, a stable ID. Never a name or an email address, which both change. Services are actors too, with IDs of their own, so an automated step is never anonymous.
  • Did what: action, named in the domain's words. change.approve says what happened; UPDATE changes makes every future reader reverse-engineer it.
  • To what: subject_id, and the revision that was acted on.
  • With what result: outcome, and a reason when it was refused. Refusals are evidence too. A run of them points to a confusing screen, a stale role, or someone probing.
  • When, and in what order: occurred_at comes from the database's clock, so every event shares one clock rather than each caller's. id is numbered by the database as events are written, so it gives their order even when two share a timestamp.
  • As part of what: correlation_id, explained below.
  • Anything else worth keeping: details, a small JSON object (jsonb is PostgreSQL's JSON type), limited by the redaction rule further down.

Write it with the change#

The transaction helper from the maker–checker note, again:

ts
import type { Pool, PoolClient } from "pg";

export async function inTransaction<T>(
  pool: Pool,
  work: (tx: PoolClient) => Promise<T>
): Promise<T> {
  const tx = await pool.connect();
  try {
    await tx.query("BEGIN");
    const result = await work(tx);
    await tx.query("COMMIT");
    return result;
  } catch (error) {
    await tx.query("ROLLBACK");
    throw error;
  } finally {
    tx.release();
  }
}

Recording an event is an insert that must run on the transaction's connection, tx. The redact call is explained further down:

ts
type AuditEvent = {
  actorId: string;
  action: string;
  subjectId: string;
  revision: number | null;
  outcome: "succeeded" | "refused";
  reason: string | null;
  correlationId: string;
  details: Record<string, unknown>;
};

export async function record(tx: PoolClient, event: AuditEvent) {
  await tx.query(
    `INSERT INTO audit_events
       (actor_id, action, subject_id, revision,
        outcome, reason, correlation_id, details)
     VALUES ($1, $2, $3, $4, $5, $6, $7, $8)`,
    [
      event.actorId, event.action, event.subjectId, event.revision,
      event.outcome, event.reason, event.correlationId,
      redact(event.action, event.details)
    ]
  );
}

And one function that moves a change to its next status and records that it did, in the same transaction:

ts
type Actor = { id: string };
type Verb = "submit" | "approve" | "publish";

// Each verb moves a change from one status to the next.
const moves: Record<Verb, [from: string, to: string]> = {
  submit: ["draft", "awaiting_review"],
  approve: ["awaiting_review", "approved"],
  publish: ["approved", "published"]
};

type Request = {
  verb: Verb;
  changeId: string;
  correlationId: string;
  details?: Record<string, unknown>;
};

export async function act(
  pool: Pool,
  actor: Actor,
  request: Request
): Promise<boolean> {
  const [from, to] = moves[request.verb];
  return inTransaction(pool, async (tx) => {
    const { rows } = await tx.query<{ revision: number }>(
      `UPDATE changes SET status = $1
        WHERE id = $2 AND status = $3
        RETURNING revision`,
      [to, request.changeId, from]
    );
    const done = rows.length > 0;

    // Recorded either way: a refusal changes nothing, but it happened.
    await record(tx, {
      actorId: actor.id,
      action: `change.${request.verb}`,
      subjectId: request.changeId,
      revision: done ? rows[0].revision : null,
      outcome: done ? "succeeded" : "refused",
      reason: done ? null : `was_not_${from}`,
      correlationId: request.correlationId,
      details: request.details ?? {}
    });
    return done;
  });
}

act leaves out the guards from the maker–checker note to keep the focus here; a real system would run a transition check first. That note lets only the author publish. Here a deployment service publishes once a change is approved, so the check would need a rule that lets that named service publish an approved change, and the trail records the service like any other actor. What matters is the shape. RETURNING makes the update report the rows it changed, so an empty result means the change was not in the expected status. The update and the event share tx, so they commit together. If the event cannot be written, the update is rolled back with it, and the change never happened.

History that cannot be rewritten#

Append-only has to be enforced where the data lives. A trigger is a function the database runs automatically when a table is modified; this one is written in PL/pgSQL, PostgreSQL's built-in procedural language. It runs before any update or delete on the events table, and refuses it:

sql
CREATE FUNCTION refuse_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION '% is append-only: % refused', TG_TABLE_NAME, TG_OP;
END;
$$;

CREATE TRIGGER audit_events_append_only
  BEFORE UPDATE OR DELETE ON audit_events
  FOR EACH ROW EXECUTE FUNCTION refuse_change();

CREATE TRIGGER audit_events_no_truncate
  BEFORE TRUNCATE ON audit_events
  FOR EACH STATEMENT EXECUTE FUNCTION refuse_change();

TRUNCATE empties a table without touching rows one at a time, so a row trigger never sees it; it needs a trigger of its own. Together they bind every connection that uses the table, however privileged, including the application's own. What they cannot stop is someone removing them: the table's owner can drop a trigger, and a superuser can switch triggers off. So give the application a database role of its own, one that neither owns the table nor is a superuser, and grant it only INSERT and SELECT on it. Corrections can now only be what they should be: new events.

Attach the same function to the revisions table from the maker–checker note, and revisions become immutable as well.

One ID across services#

A single user action rarely stays in one place. Approving a change in the web application queues a deployment; a separate worker picks up the job and publishes it. Each writes its own events, at different times, from different machines.

A correlation ID ties them together. It is created once, where the user's request enters your system, and passed along with everything that request causes: in the payload of each job it queues, and in a header, commonly X-Correlation-Id, on each call to another service. Every event written along the way carries it. Searching for one correlation ID returns the whole chain, across every service that took part.

Record enough, not everything#

It is tempting to store the whole request in each event, to be safe. That is how audit trails end up holding passwords, access tokens and personal data, copied into a table built never to delete anything.

Decide what each action may keep, and drop everything else before it is written. This is redaction at the source. Cleaning up later, in a log pipeline or a nightly job, fails the first time a field appears that the cleanup does not know about, and on an append-only table there is no later.

ts
// The only details each action may keep. Anything else is dropped
// here, before it can reach the table.
const keep: Record<string, string[]> = {
  "change.submit": ["title", "summary"],
  "change.approve": ["comment"],
  "change.publish": ["deploymentId"]
};

function redact(action: string, details: Record<string, unknown>) {
  const allowed = keep[action] ?? [];
  return Object.fromEntries(
    Object.entries(details).filter(([field]) => allowed.includes(field))
  );
}

This is an allow-list: it names what to keep. A deny-list names what to remove, so it has to anticipate every secret anyone will ever add to a request. An allow-list fails safe: a new field is dropped until someone decides it belongs in the evidence.

Reconstruction is the test#

A trail is complete if someone outside the team can answer, from the trail alone, who asked for a change, who approved it, and what went live. Start with the query that returns one subject's history in order:

ts
type Entry = {
  id: string;
  actorId: string;
  action: string;
  revision: number | null;
  outcome: string;
  reason: string | null;
  correlationId: string;
};

export async function history(
  pool: Pool,
  subjectId: string
): Promise<Entry[]> {
  const { rows } = await pool.query<Entry>(
    `SELECT id, actor_id AS "actorId", action, revision, outcome,
            reason, correlation_id AS "correlationId"
       FROM audit_events
      WHERE subject_id = $1
      ORDER BY id`,
    [subjectId]
  );
  return rows;
}

Reading the trail is itself a protected read. It reveals more about the people and decisions behind a change than the change does, so put a policy check in front of history, as the first note in this series describes, before anyone can call it.

Then run the whole story against a real database, and ask the questions:

ts
import assert from "node:assert/strict";
import { randomUUID } from "node:crypto";
import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL
});

// Readable IDs keep the example legible; real ones should be opaque.
const maya: Actor = { id: "maya" };
const chen: Actor = { id: "chen" };
const omar: Actor = { id: "omar" };
const deployer: Actor = { id: "service:deployer" };

// Each request starts its own correlation ID unless it continues one.
const step = (verb: Verb, correlationId = randomUUID()): Request =>
  ({ verb, changeId: "c-42", correlationId });

await pool.query(
  `INSERT INTO changes (id, status, revision)
   VALUES ('c-42', 'draft', 3), ('c-43', 'draft', 1)`
);

// Maya submits. The request carries a token the trail must not keep.
await act(pool, maya, {
  ...step("submit"),
  details: { title: "Raise the transfer limit", apiToken: "tok_9f2c" }
});

// Chen approves. Omar, a moment later, is refused, and that is kept.
const approval = randomUUID();
await act(pool, chen, step("approve", approval));
await act(pool, omar, step("approve"));

// The deployment service runs the job the approval queued. The job
// carried the approval's correlation ID, so this event does too.
await act(pool, deployer, {
  ...step("publish", approval),
  details: { deploymentId: "dep-118" }
});

// History cannot be edited, even through the connection that wrote it.
await assert.rejects(
  pool.query("UPDATE audit_events SET actor_id = 'omar'"),
  /append-only/
);
await assert.rejects(
  pool.query("DELETE FROM audit_events"),
  /append-only/
);

// The token never reached the trail: search every event as text.
const leaks = await pool.query(
  `SELECT id FROM audit_events
    WHERE audit_events::text LIKE '%tok_9f2c%'`
);
assert.equal(leaks.rows.length, 0);

// If the event cannot be written, the change does not happen either.
const broken = { verb: "submit", changeId: "c-43" } as Request;
await assert.rejects(act(pool, maya, broken)); // no correlation ID
const c43 = await pool.query(
  "SELECT status FROM changes WHERE id = 'c-43'"
);
assert.equal(c43.rows[0].status, "draft");

// The trail alone answers: who asked, who approved, what went live.
const trail = await history(pool, "c-42");
const last = (action: string) => trail
  .filter((e) => e.action === action && e.outcome === "succeeded")
  .at(-1);
const asked = last("change.submit");
const approved = last("change.approve");
const live = last("change.publish");

assert.equal(asked?.actorId, "maya");
assert.equal(approved?.actorId, "chen");
assert.equal(live?.revision, approved?.revision);
assert.equal(live?.correlationId, approved?.correlationId);

await pool.end();

The trail for c-42, as history returns it, with correlation IDs shortened:

text
id  actor             action          rev  outcome    correlation
1   maya              change.submit   3    succeeded  be51b631…
2   chen              change.approve  3    succeeded  96249525…
3   omar              change.approve       refused    e9d54fbc…
4   service:deployer  change.publish  3    succeeded  96249525…

Omar's attempt is there, refused, with its reason: was_not_awaiting_review. The approval and the deployment share a correlation ID, although different services wrote them. And the revision that went live is the revision Chen approved, which is the question this series has been building toward.

What it costs#

Every consequential write now writes twice, inside a transaction. For most applications that is cheap; at very high write volumes it is a real cost, worth measuring.

Append-only means the table only grows. Decide how long events must be kept — regulation often decides for you — and plan how old ones leave, since the table now refuses deletes. Splitting it by month, which PostgreSQL calls partitioning, lets a whole month be detached and archived without updating or deleting a single row. Decide this when you create the table, because an existing table cannot be converted in place. The primary key must then include the column the table is split on, as PRIMARY KEY (id, occurred_at), and each month's table needs its own TRUNCATE trigger, since one on the parent does not fire when a single month is truncated.

It also collides with deleting someone's personal data on request. Storing an opaque ID, one that means nothing outside your system, rather than a name or an email address helps: delete the person's record elsewhere, and the actor IDs no longer say who the person was. Free-text details, such as a comment, can still name someone, which is one more reason to keep the allow-list short.

And a trigger protects the trail from the application, not from whoever controls the database: the table's owner can drop the trigger, and a superuser can switch triggers off. If your threat includes your own administrators, also copy events somewhere they cannot alter, such as a separate account or storage that only accepts new writes.

Nobody asks for an audit trail until the day they need one. On that day, it is the only thing that can answer.