Tamper-evident audit trails in PostgreSQL with hash chaining

Why normal audit logs are easy to dispute

An audit trail is the record you fall back on when something looks wrong: a strange refund, a permission change nobody remembers, or a customer record that “disappeared”. If the audit trail can be edited, it stops being evidence and turns into another piece of data someone can rewrite.

Many “audit logs” are just regular tables. If rows can be updated or deleted, the story can be updated or deleted too.

A key distinction: blocking edits is not the same as making edits detectable. You can reduce changes with permissions, but anyone with enough access (or a stolen admin credential) can still alter history. Tamper-evidence accepts that reality. You might not prevent every change, but you can make changes leave an obvious fingerprint.

Normal audit logs get disputed for predictable reasons. Privileged users can “fix” the log after the fact. A compromised app account can write believable entries that look like normal traffic. Timestamps can be backfilled to hide a late change. Or someone deletes only the most damaging lines.

“Tamperevident” means you design the audit trail so that even a small edit (changing one field, removing one row, reordering events) becomes detectable later. You’re not promising magic. You’re promising that when someone asks, “How do we know this log is real?”, you can run checks that show whether the log was touched.

Decide what you need to prove

A tamper-evident audit trail is only useful if it answers the questions you’ll face later: who did what, when they did it, and what changed.

Start with the events that matter to your business. Data changes (create, update, delete) are the baseline, but investigations often hinge on security and access too: logins, password resets, permission changes, and account lockouts. If you handle payments, refunds, credits, or payouts, treat money movement as first-class events, not as a side effect of an updated row.

Then decide what makes an event credible. Auditors usually expect an actor (user or service), a server-side timestamp, the action taken, and the object affected. For updates, store before and after values (or at least the sensitive fields), plus a request id or correlation id so you can tie many small database changes back to one user action.

Finally, be explicit about what “immutable” means in your system. The simplest rule is: never update or delete audit rows, only insert. If something is wrong, write a new event that corrects or supersedes the old one, and keep the original visible.

Build an append-only audit table

Keep audit data separate from your normal tables. A dedicated audit schema reduces accidental edits and makes permissions easier to reason about.

The goal is simple: rows can be added, but never changed or removed. In PostgreSQL, you enforce that with privileges (who can do what) and a couple of safety rails in your table design.

Here is a practical starting table:

CREATE SCHEMA IF NOT EXISTS audit;

CREATE TABLE audit.events (
  id            bigserial PRIMARY KEY,
  entity_type   text        NOT NULL,
  entity_id     text        NOT NULL,
  event_type    text        NOT NULL CHECK (event_type IN ('INSERT','UPDATE','DELETE')),
  actor_id      text,
  occurred_at   timestamptz NOT NULL DEFAULT now(),
  request_id    text,
  before_data   jsonb,
  after_data    jsonb,
  notes         text
);

A few fields are especially useful during investigations:

• occurred_at with DEFAULT now() so time is stamped by the database, not the client.
• entity_type and entity_id so you can follow one record across changes.
• request_id so one user action can be traced across multiple rows.

Lock it down with roles. Your application role should be able to INSERT and SELECT on audit.events, but not UPDATE or DELETE. Keep schema changes and stronger permissions for an admin role that isn’t used by the app.

Capture changes with triggers (clean and predictable)

If you want a tamper-evident audit trail, the most reliable place to capture changes is the database. Application logs can be skipped, filtered, or rewritten. A trigger fires no matter which app, script, or admin tool touches the table.

Keep triggers boring. Their job should be one thing: append one audit event for every INSERT, UPDATE, and DELETE on tables that matter.

A practical audit record usually includes the table name, operation type, primary key, before and after values, a timestamp, and identifiers that let you group related changes (transaction id and a correlation id).

Correlation ids are the difference between “20 rows updated” and “This was one button click.” Your app can set a correlation id once per request (for example, in a DB session setting), and the trigger can read it. Store txid_current() too, so you can still group changes when the correlation id is missing.

Here is a simple trigger pattern that stays predictable because it only inserts into the audit table (adjust names to match your schema):

CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger AS $$
DECLARE
  corr_id text;
BEGIN
  corr_id := current_setting('app.correlation_id', true);

  INSERT INTO audit_events(
    occurred_at, table_name, op, row_pk,
    old_row, new_row, db_user, txid, correlation_id
  ) VALUES (
    now(), TG_TABLE_NAME, TG_OP, COALESCE(NEW.id, OLD.id),
    to_jsonb(OLD), to_jsonb(NEW), current_user, txid_current(), corr_id
  );

  RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

Resist the urge to do more inside triggers. Avoid extra queries, network calls, or complex branching. Small triggers are easier to test, faster to run, and harder to argue with during a review.

Add hash chaining so edits leave fingerprints

An append-only table helps, but someone with enough access can still rewrite past rows. Hash chaining makes that kind of tampering visible.

Add two columns to each audit row: prev_hash and row_hash (sometimes called chain_hash). prev_hash stores the hash of the previous row in the same chain. row_hash stores the hash of the current row, calculated from the row data plus prev_hash.

What you hash matters. You want a stable, repeatable input so the same row always produces the same hash.

A practical approach is to hash a canonical string built from fixed columns (timestamp, actor, action, entity id), a canonical payload (often jsonb, because keys are stored consistently), and the prev_hash.

Be careful with details that can change without meaning, like whitespace, JSON key order in plain text, or locale-specific formatting. Keep types consistent and serialize in one predictable way.

Chain per stream, not per whole database

If you chain every audit event in a single global sequence, writes can become a bottleneck. Many systems chain within a “stream”, such as per tenant, per entity type, or per business object.

Each new row looks up the latest row_hash for its stream, stores it as prev_hash, then computes its own row_hash.

-- Requires pgcrypto
-- digest() returns bytea; store hashes as bytea
row_hash = digest(
  concat_ws('|',
    stream_key,
    occurred_at::text,
    actor_id::text,
    action,
    entity,
    entity_id::text,
    payload::jsonb::text,
    encode(prev_hash, 'hex')
  ),
  'sha256'
);

Snapshot the chain head

For faster reviews, store the latest row_hash (the “chain head”) periodically, such as daily per stream, in a small snapshot table. During an investigation, you can verify the chain up to each snapshot instead of scanning the entire history at once. Snapshots also make it easier to compare exports and spot suspicious gaps.

Concurrency and ordering without breaking the chain

Hash chaining gets tricky under real traffic. If two transactions write audit rows at the same time and both use the same prev_hash, you can end up with forks. That weakens your ability to prove a single, clean sequence.

First decide what your chain represents. One global chain is easiest to explain but has the highest contention. Multiple chains reduce contention, but you must be clear about what each chain proves.

Whichever model you pick, define a strict order with a monotonic event id (usually a sequence-backed id). Timestamps aren’t enough because they can collide and can be manipulated.

To avoid race conditions when computing prev_hash, serialize “get last hash + insert next row” for each stream. Common approaches are locking a single row that represents the stream head, or using an advisory lock keyed by the stream id. The goal is that two writers to the same stream can’t both read the same last hash.

Partitioning and sharding affect where “the last row” lives. If you expect to partition audit data, keep each chain fully contained within one partition by using the same partition key as the stream key (for example, tenant id). That way, tenant chains stay verifiable even if tenants later move across servers.

How to verify the chain during an investigation

Hash chaining only helps if you can prove the chain still holds when someone asks. The safest approach is a read-only verification query (or job) that recomputes every row’s hash from stored data and compares it to what’s recorded.

A simple verifier you can run on demand

A verifier should: rebuild the expected hash for each row, confirm each row links to the previous one, and flag anything that looks off.

Here is a common pattern using window functions. Adjust column names to match your table.

WITH ordered AS (
  SELECT
    id,
    created_at,
    actor_id,
    action,
    entity,
    entity_id,
    payload,
    prev_hash,
    row_hash,
    LAG(row_hash) OVER (ORDER BY created_at, id) AS expected_prev_hash,
    /* expected row hash, computed the same way as in your insert trigger */
    encode(
      digest(
        coalesce(prev_hash, '') || '|' ||
        id::text || '|' ||
        created_at::text || '|' ||
        coalesce(actor_id::text, '') || '|' ||
        action || '|' ||
        entity || '|' ||
        entity_id::text || '|' ||
        payload::text,
        'sha256'
      ),
      'hex'
    ) AS expected_row_hash
  FROM audit_log
)
SELECT
  id,
  created_at,
  CASE
    WHEN prev_hash IS DISTINCT FROM expected_prev_hash THEN 'BROKEN_LINK'
    WHEN row_hash IS DISTINCT FROM expected_row_hash THEN 'HASH_MISMATCH'
    ELSE 'OK'
  END AS status
FROM ordered
WHERE prev_hash IS DISTINCT FROM expected_prev_hash
   OR row_hash IS DISTINCT FROM expected_row_hash
ORDER BY created_at, id;

Beyond “broken or not,” it’s worth checking for gaps (missing ids in a range), out-of-order links, and suspicious duplicates that don’t match real workflows.

Record verification results as immutable events

Don’t run a query and bury the output in a ticket. Store verification outcomes in a separate append-only table (for example, audit_verification_runs) with run time, verifier version, who triggered it, the range checked, and counts for broken links and hash mismatches.

That gives you a second trail: not only is the audit log intact, but you can show that you’ve been checking it.

A practical cadence is: run after any deploy that touches audit logic, nightly for active systems, and always before a planned audit.

Common mistakes that break tamper-evidence

Most failures aren’t about the hash algorithm. They’re about exceptions and gaps that give people room to argue.

The fastest way to lose trust is allowing updates to audit rows. Even if it’s “just this once,” you’ve created both a precedent and a working path for rewriting history. If you need to correct something, add a new audit event that explains the correction and keep the original.

Hash chaining also fails when you hash unstable data. JSON is a common trap. If you hash a JSON string, harmless differences (key order, whitespace, number formatting) can change the hash and make verification noisy. Prefer a canonical form: normalized fields, jsonb, or another consistent serialization.

Other patterns that undermine a defensible trail:

• Hashing only the payload and skipping the context (timestamp, actor, object id, action).
• Capturing changes only in the application and assuming the database matches forever.
• Using one database role that can write business data and also alter audit history.
• Allowing NULLs for prev_hash inside a chain without a clear, documented rule.

Separation of duties matters. If the same role can insert audit events and also modify them, tamper-evidence becomes a promise instead of a control.

Quick checklist for a defensible audit trail

A defensible audit trail should be hard to change and easy to verify.

Start with access control: the audit table must be append-only in practice. The application role should insert (and usually read), but not update or delete. Schema changes should be tightly restricted.

Make sure each row answers the questions an investigator will ask: who did it, when it happened (server-side), what happened (clear event name plus operation), what it touched (entity name and id), and how it connects (request/correlation id and transaction id).

Then validate the integrity layer. A quick test is to replay a segment and confirm each prev_hash matches the prior row’s hash, and each stored hash matches the recomputed one.

Operationally, treat verification like a normal job:

• Run scheduled integrity checks and store pass/fail results and ranges.
• Alert on mismatches, gaps, and broken links.
• Keep backups long enough to cover your retention window, and lock down retention so audit history can’t be “cleaned up” early.

Example: spotting a suspicious edit in a compliance review

A common test case is a refund dispute. A customer claims they were approved for a $250 refund, but the system now shows $25. Support insists the approval was correct, and compliance wants an answer.

Start by narrowing the search using a correlation id (order id, ticket id, or refund_request_id) and a time window. Pull the audit rows for that correlation id and bracket them around the approval time.

You’re looking for the full set of events: request created, refund approved, refund amount set, and any later updates. With a tamper-evident design, you’re also checking whether the sequence stayed intact.

A simple investigation flow:

• Pull all audit rows for the correlation id in time order.
• Recompute each row’s hash from its stored fields (including prev_hash).
• Compare computed hashes to stored hashes.
• Identify the first row that differs and see whether later rows also fail.

If someone edited a single audit row (for example, changing amount from 250 to 25), that row’s hash will no longer match. Because the next row includes the previous hash, the mismatch usually cascades forward. That cascade is the fingerprint: it shows the audit record was altered after the fact.

What the chain can tell you: an edit happened, where the chain first broke, and the scope of affected rows. What it can’t tell you by itself: who made the edit, what the original value was if it was overwritten, or whether other tables were also changed.

Next steps: roll it out safely and keep it maintainable

Treat your audit trail like any other security control. Roll it out in small steps, prove it works, then expand.

Start with actions that would hurt you most if disputed: permission changes, payouts, refunds, data exports, and manual overrides. Once those are covered, add lower-risk events without changing the core design.

Write down the contract for your audit events: which fields are recorded, what each event type means, how the hash is computed, and how to run verification. Keep that documentation next to your database migrations, and keep the verification procedure repeatable.

Restore drills matter because investigations often start from backups, not the live system. Regularly restore to a test database and verify the chain end-to-end. If you can’t reproduce the same verification result after a restore, your tamper-evidence will be hard to defend.

If you’re building internal tools and admin workflows with AppMaster (appmaster.io), standardizing audit event writes through consistent server-side processes helps keep the event schema and correlation ids uniform across features, which makes verification and investigations much simpler.

Schedule maintenance time for this system. Audit trails usually fail quietly when teams ship new features but forget to add events, update the hash inputs, or keep verification jobs and restore drills running.