Tamper-Evident Audit Logs with Hash Chaining

An audit log that an administrator can edit proves nothing, and this page shows how to make edits detectable without leaving the database. It is a focused part of Tenant Audit Logging Architecture: chaining records per tenant, checkpointing the chain, and verifying it fast enough to run continuously.

Problem Framing

Auditors and enterprise security reviewers ask a specific question about audit logs: what stops someone with database access from deleting the row that records their own action? "We restrict access" is a control, not evidence. Tamper evidence is a cryptographic property — any modification, deletion or reordering must be detectable after the fact, by someone who does not trust the database.

Hash chaining provides it cheaply. Each record stores a digest computed over its own content and the digest of the record before it, so altering record n invalidates every digest from n onward. An attacker who can rewrite the whole chain still cannot match a checkpoint digest that was signed and published earlier — which is why chaining alone is not enough, and periodic signed checkpoints are the other half of the design.

Multi-tenancy adds one important constraint: chain per tenant, not globally. A global chain makes one tenant's verification depend on every other tenant's records, forces a single serialisation point for all audit writes, and means exporting one tenant's verifiable log requires exporting everyone's. Per-tenant chains are independent, parallel, and exportable.

Step-by-Step Guide

1. Give every tenant its own sequence and chain columns

CREATE TABLE audit_record (
  tenant_id   uuid    NOT NULL,
  seq         bigint  NOT NULL,
  occurred_at timestamptz NOT NULL DEFAULT now(),
  actor_id    uuid,
  acting_staff_id uuid,
  action      text    NOT NULL,
  entity      text    NOT NULL,
  entity_id   text,
  payload     jsonb   NOT NULL DEFAULT '{}'::jsonb,
  prev_hash   bytea   NOT NULL,
  record_hash bytea   NOT NULL,
  PRIMARY KEY (tenant_id, seq)
);

-- No updates, no deletes: the writer role gets INSERT only.
REVOKE UPDATE, DELETE ON audit_record FROM audit_writer;
GRANT  INSERT, SELECT ON audit_record TO audit_writer;

The composite primary key gives each tenant an independent sequence space and makes the "next sequence for this tenant" lookup an index seek rather than a scan.

2. Compute the digest over a canonical serialisation

Two systems must derive the same bytes from the same record, forever. That means a fixed field order, a fixed encoding, and a version tag so the format can evolve without invalidating history.

// digest.ts
import { createHash } from 'node:crypto';

export function recordHash(r: AuditRecord, prev: Buffer): Buffer {
  const canonical = [
    'v1',
    r.tenantId,
    String(r.seq),
    r.occurredAt.toISOString(),           // fixed precision, always UTC
    r.actorId ?? '',
    r.actingStaffId ?? '',
    r.action,
    r.entity,
    r.entityId ?? '',
    stableStringify(r.payload),           // keys sorted, no incidental whitespace
  ].map((f) => `${Buffer.byteLength(f)}:${f}`).join('|');

  return createHash('sha256').update(prev).update(Buffer.from(canonical, 'utf8')).digest();
}

Length-prefixing each field is what stops two different records from serialising to identical bytes — the classic concatenation ambiguity that quietly voids the whole guarantee.

3. Append under a per-tenant lock so the chain cannot fork

Two concurrent writers reading the same head produce two records claiming the same predecessor. A per-tenant advisory lock serialises appends for that tenant only, leaving other tenants unaffected.

// append.ts
export async function appendAudit(client: PoolClient, rec: NewAuditRecord) {
  await client.query('BEGIN');
  try {
    // Per-tenant lock: contention is scoped to one tenant's audit stream.
    await client.query('SELECT pg_advisory_xact_lock(hashtext($1))', [`audit:${rec.tenantId}`]);

    const { rows } = await client.query(
      `SELECT seq, record_hash FROM audit_record
       WHERE tenant_id = $1 ORDER BY seq DESC LIMIT 1`, [rec.tenantId]);

    const seq  = rows.length ? Number(rows[0].seq) + 1 : 1;
    const prev = rows.length ? rows[0].record_hash : Buffer.alloc(32);   // genesis
    const full = { ...rec, seq, occurredAt: new Date() };
    const hash = recordHash(full, prev);

    await client.query(
      `INSERT INTO audit_record
         (tenant_id, seq, occurred_at, actor_id, acting_staff_id, action, entity, entity_id, payload, prev_hash, record_hash)
       VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11)`,
      [full.tenantId, seq, full.occurredAt, full.actorId, full.actingStaffId,
       full.action, full.entity, full.entityId, full.payload, prev, hash],
    );
    await client.query('COMMIT');
    return { seq, hash };
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  }
}

4. Publish signed checkpoints outside the database's blast radius

A checkpoint is the head digest, the sequence it covers, a timestamp, and a signature — written somewhere the application's database credentials cannot alter.

// checkpoint.ts
export async function publishCheckpoint(tenantId: string, signer: Signer) {
  const { rows } = await db.query(
    `SELECT seq, record_hash FROM audit_record
     WHERE tenant_id = $1 ORDER BY seq DESC LIMIT 1`, [tenantId]);
  if (!rows.length) return null;

  const body = {
    v: 1,
    tenantId,
    upToSeq: Number(rows[0].seq),
    headHash: rows[0].record_hash.toString('hex'),
    at: new Date().toISOString(),
  };
  const signature = await signer.sign(Buffer.from(JSON.stringify(body)));

  await objectStore.putImmutable(                      // object lock / WORM bucket
    `audit-checkpoints/${tenantId}/${body.upToSeq}.json`,
    JSON.stringify({ ...body, signature: signature.toString('base64') }),
  );
  return body;
}

Hourly is a reasonable default cadence. The window between checkpoints is exactly the window in which an attacker with full database control could rewrite history undetectably, so pick it against how much undetected tampering you can tolerate, not against storage cost — the objects are a few hundred bytes each.

5. Verify continuously, not only when an auditor asks

-- Full-chain verification for one tenant, computed in the database.
WITH RECURSIVE chain AS (
  SELECT tenant_id, seq, prev_hash, record_hash,
         (prev_hash = '\x0000000000000000000000000000000000000000000000000000000000000000'::bytea) AS ok
  FROM   audit_record WHERE tenant_id = $1 AND seq = 1
  UNION ALL
  SELECT a.tenant_id, a.seq, a.prev_hash, a.record_hash,
         (a.prev_hash = c.record_hash) AS ok
  FROM   audit_record a JOIN chain c
    ON   a.tenant_id = c.tenant_id AND a.seq = c.seq + 1
)
SELECT seq FROM chain WHERE NOT ok ORDER BY seq LIMIT 1;
-- expected: no rows. The first row returned is the exact point of tampering.

Verification

Prove that the mechanism actually detects the three tampering shapes: modification, deletion, and reordering.

// chain.spec.ts
it('detects a modified record', async () => {
  await seedChain('acme', 5);
  await raw.query(`UPDATE audit_record SET payload = '{"x":1}' WHERE tenant_id=$1 AND seq=3`, ['acme']);
  await expect(verifyChain('acme')).resolves.toMatchObject({ ok: false, firstBadSeq: 3 });
});

it('detects a deleted record', async () => {
  await seedChain('acme', 5);
  await raw.query(`DELETE FROM audit_record WHERE tenant_id=$1 AND seq=3`, ['acme']);
  await expect(verifyChain('acme')).resolves.toMatchObject({ ok: false, firstBadSeq: 3 });
});

it('detects a chain rebuilt after the last checkpoint', async () => {
  await seedChain('acme', 5);
  const cp = await publishCheckpoint('acme', signer);         // covers seq 5
  await rebuildChainWithout('acme', 3);                        // attacker recomputes 3..5
  await expect(verifyAgainstCheckpoint('acme', cp)).resolves.toBe(false);
});

Run the chain verifier on a schedule — nightly for every tenant, and on demand for any tenant under investigation — and alert on any non-empty result. A verifier that only runs during an audit tells you about tampering months late.

Failure Modes & Gotchas

FAQ

Is hash chaining enough on its own for SOC 2 or ISO 27001? It addresses the integrity half of the control convincingly, which is usually the part that is weakest. Auditors also want restricted access to the audit store, retention that matches policy, and evidence that verification actually runs — so pair the chain with an append-only writer role, documented retention, and a scheduled verifier whose results are retained. The chain is what turns "we restrict access" into a claim you can demonstrate.

How does this interact with a tenant's right to erasure? Keep the record and remove the identifiability. If personal data lives in payload, verification breaks the moment you redact it — so store only a salted digest of any personal identifier in the chained payload, and keep the reversible mapping in a separate table you can delete. Erasing the mapping then removes the personal data while leaving every chained digest intact.

What signing key should checkpoints use? One that the application cannot reach: a key management service key with a signing-only grant to the checkpoint job, or an offline key for very high assurance. If the same credentials that can rewrite the audit table can also sign checkpoints, the checkpoint adds no security at all — it merely adds a step for the attacker.

Does chaining slow down the write path? The hash itself is negligible — a few microseconds over a small canonical string. The cost that matters is the per-tenant lock and the read of the current head, which together add roughly one indexed lookup per audit write. For the event classes worth auditing that is entirely acceptable, because those are a small fraction of total traffic. It becomes a problem only if the chain is applied to high-volume application logs, which is a reason to keep the audited set narrow rather than a reason to skip chaining.