The audit log that won finance teams' trust

A bare append-only table, three rules for what goes in it, and a React side panel showing the history of every number. The single feature that decided whether the dashboard got used during month-end close — or got bypassed for the same spreadsheet they were trying to replace.

The first dashboard launch went badly

The first finance dashboard I built launched on a Monday. By Wednesday, the team had reverted to their old spreadsheet. The reason was simple and surprising: a number on the dashboard didn't match what someone remembered from the previous week. They couldn't tell whether the dashboard was wrong, the source data had changed, or someone had edited something. Three plausible explanations, none of them disprovable. So they stopped trusting it.

Finance teams will not adopt a tool whose numbers they cannot trace.

The second dashboard launch — same client, six weeks later — included an audit log from day one. The audit log was used almost daily during the first month-end close. After that, adoption was permanent. Same product, same UX, same charts. One feature decided it.

The schema

The whole thing is one table, deliberately minimal:

CREATE TABLE audit_log (
  id           bigserial   PRIMARY KEY,
  occurred_at  timestamptz NOT NULL DEFAULT now(),
  actor_id     bigint      REFERENCES users(id),
  actor_name   text        NOT NULL,    -- denormalised snapshot
  event_type   text        NOT NULL,    -- 'expense.approved', 'invoice.edited', ...
  entity_type  text        NOT NULL,    -- 'expense', 'invoice', 'reconciliation_match'
  entity_id    bigint      NOT NULL,
  before       jsonb,                   -- previous state, NULL on creation
  after        jsonb,                   -- new state, NULL on deletion
  comment      text,                    -- optional human note
  metadata     jsonb                    -- IP, user agent, request ID
);

CREATE INDEX ON audit_log (entity_type, entity_id, occurred_at DESC);
CREATE INDEX ON audit_log (actor_id, occurred_at DESC);
CREATE INDEX ON audit_log (occurred_at DESC);

Three things to note:

1. It's append-only. No UPDATE, no DELETE. There's no audit_log retraction. Bad entries stay; you add a correcting entry next to them.
2. actor_name is denormalised on purpose. If a user is renamed or deleted, the log still says who did what at the time. Joining to users at read time would let history rewrite itself, which defeats the point.
3. before and after are JSONB. They store the entity's full state on either side of the change. Five years from now, if the entity schema has migrated four times, the audit row still tells you exactly what changed.

The three rules for what gets logged

Logging everything floods the table and makes the UI useless. Logging too little makes it untrustworthy. The rules I settled on:

Rule 1: Log every state change to a financial entity

Expense submitted, approved, denied. Invoice created, edited, voided. Reconciliation match accepted, overridden, undone. The bar is: "would a finance person ever ask 'when did this change?'" If yes, log it.

Rule 2: Log every override

Automatic matches accepted silently are not logged (too noisy). Automatic matches the user changed manually are logged — and the log entry shows what the system suggested vs what the user picked. This is the most-used audit query during reconciliation work.

Rule 3: Don't log reads

Read tracking is a separate concern with different ergonomics — typically a different table, often only retained for 30 days, often not surfaced in the user UI. Mixing reads into the change log doubles the row count for no audit benefit.

The Node side: a single wrapper, not scattered logger calls

The mistake is to litter auditLog.create(...) calls through every controller. Half the time you forget; the other half, the entity changes but the log doesn't (and there's no test for that). The fix is to centralise:

// services/expenseService.js
async function updateExpense(id, patch, ctx) {
  return await db.transaction(async tx => {
    const before = await tx.expense.findById(id);
    const after  = await tx.expense.update(id, patch);

    await tx.auditLog.create({
      actor_id:    ctx.user.id,
      actor_name:  ctx.user.name,
      event_type:  'expense.updated',
      entity_type: 'expense',
      entity_id:   id,
      before, after,
      comment:     ctx.body.comment ?? null,
      metadata:    { ip: ctx.ip, requestId: ctx.requestId }
    });

    return after;
  });
}

Two non-negotiables: the audit write is in the same database transaction as the entity write (so they can never disagree), and the wrapper is the only place state-change happens (so logging cannot be forgotten).

The React side: a side panel, not a separate page

The audit log only earns trust if it's one click away from the number. Putting it on a separate page kills the workflow — by the time the user navigates there, they've forgotten what they were checking.

The pattern that worked: every entity row has a small "History" button. Click → a side panel slides in from the right showing all audit entries for that entity, newest first. Each entry shows: actor, time, what changed (a JSON diff between before and after), and the optional comment. No paging unless an entity has > 50 entries, which in practice almost never happens.

// HistoryPanel.jsx
function HistoryPanel({ entityType, entityId, onClose }) {
  const { data, isLoading } = useAuditLog(entityType, entityId);

  return (
    <Drawer onClose={onClose}>
      <h2>History</h2>
      {isLoading ? <Skeleton /> : data.map(e => (
        <AuditEntry key={e.id} entry={e} />
      ))}
    </Drawer>
  );
}

function AuditEntry({ entry }) {
  const diff = computeDiff(entry.before, entry.after);
  return (
    <article>
      <header>
        <strong>{entry.actor_name}</strong> · {formatRelative(entry.occurred_at)}
        <Badge>{entry.event_type}</Badge>
      </header>
      <FieldDiff diff={diff} />
      {entry.comment && <Note>{entry.comment}</Note>}
    </article>
  );
}

computeDiff() is small — about 30 lines — and just walks the keys of before and after, listing what's added, removed, or changed. The visual is two columns: red strikethrough on the left for old, green on the right for new. Familiar to anyone who's ever read a git diff.

What this isn't

Two honest limits:

• It's not compliance. SOC 2, SOX, GDPR data-subject requests — those need encrypted at-rest storage, retention policies, and access-control on the log itself. The audit log described here is a trust feature, not a compliance feature. If your client needs both, design them together from day one.
• It's not a time machine. The log records changes; it does not let you "show me the dashboard as it was on March 15." That's a separate concept (event sourcing) with very different ergonomics. Most finance dashboards don't need it.

The wider pattern

The audit log is one of five patterns I reuse on every finance dashboard. The full set — denormalised reporting view, freshness badge, server aggregation, CSV export, audit log — is in the React + Node finance dashboard playbook, with the case study on the work page.