Building an Audit Trail Your Database Admin Can’t Quietly Rewrite

Append-only tables are necessary but not sufficient. Here’s how to make tampering detectable before your compliance auditor has to tell you.

Someone with database write access modifies a record in your audit_events table. The created_at timestamp stays untouched. The actor_id column stays untouched. Everything else in the database looks exactly as it did before, because nothing else was touched.

Your compliance audit runs the next morning. It scans the logs. Passes.

You will never know it happened.

This is the gap most audit implementations leave open. Not accidentally. By design, because the standard implementation is a table with an INSERT trigger and the assumption that "append-only" means something at the database level when it usually doesn't.

What Most Audit Implementations Actually Look Like

The pattern is almost universal. Create a table:

CREATE TABLE audit_events (
    id          BIGSERIAL PRIMARY KEY,
    user_id     UUID NOT NULL,
    action      VARCHAR(100) NOT NULL,
    resource    VARCHAR(100) NOT NULL,
    resource_id TEXT NOT NULL,
    before      JSONB,
    after       JSONB,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

Wire up triggers on the tables you care about. Insert a row on every change. Call it done.

This approach is tamper-evident in name only. Any database user with UPDATE or DELETE privileges on audit_events can rewrite history. A senior engineer handling an incident. A DBA performing maintenance. A contractor with temporary elevated access. Your application's own service account if you've given it broad privileges. None of these actors leave a trace in the audit log itself, because the audit log is the thing being modified.

The problem isn’t that bad actors are common. The problem is that compliance frameworks like SOC 2 don’t accept “we trust our staff” as a control. They ask: how would you detect it if someone tampered? Most teams have no answer.

Why This Became Urgent After July 2025

For a few years, the pragmatic answer to this problem was: use a purpose-built ledger database. AWS built QLDB exactly for this use case. Amazon Quantum Ledger Database provided a cryptographically verifiable transaction log where every committed change was hash-chained to the previous block, and the entire history was represented by a single 256-bit digest you could audit without reading every row.

QLDB reached end of support on July 31, 2025.

AWS’s own migration guide pointed teams to Aurora PostgreSQL as the recommended replacement. Which means: if you want cryptographic verification of your audit trail, you’re now building it yourself on a standard relational database. That guide exists to show you the path. This article shows you how to make it production-worthy.

Requirements Before We Build Anything

Functional requirements:

• Record who did what to which resource and when
• Support queries by actor, resource, time range and action type
• Store before/after state for data change events
• Verify the chain hasn’t been tampered with on demand

Non-functional requirements:

• Write latency under 10ms per event (audit writes are on the hot path for many operations)
• Queryable without full table scans for common access patterns
• The verification proof must be computable without locking the table
• No dependency on a separate system to enforce tamper detection

Scale sketch:

A SaaS with 50,000 active users performing an average of 20 significant actions per day generates around 1 million audit events per day. At 1KB per event (including JSONB diff), that’s roughly 30GB per month before compression. Nothing exotic, but enough that you need to think about partitioning from day one and not add it later.

Layer 1: Structural Enforcement With PostgreSQL Row Level Security

The first line of defense is making unauthorized writes structurally harder. PostgreSQL Row Level Security lets you create per-command policies on a table, meaning you can allow INSERT while providing no UPDATE or DELETE policy at all.

-- Enable RLS on the audit table
ALTER TABLE audit_events ENABLE ROW LEVEL SECURITY;

-- Allow the application role to insert
CREATE POLICY audit_insert_policy ON audit_events
    FOR INSERT
    TO app_role
    WITH CHECK (true);

-- No UPDATE or DELETE policies are created.
-- Without a permissive policy, those operations are denied by default.
-- The critical flag most RLS tutorials skip:
-- FORCE ROW LEVEL SECURITY applies policies even to the table owner.
-- Without this, the owner role bypasses RLS entirely.
ALTER TABLE audit_events FORCE ROW LEVEL SECURITY;

A few things this does not protect against:

• PostgreSQL superusers bypass RLS unconditionally. FORCE ROW LEVEL SECURITY has no effect on superusers. This is by design in PostgreSQL's security model.
• Direct filesystem access to data files bypasses everything
• A superuser who grants themselves the table owner role before the change and reverts after

RLS prevents careless or opportunistic modification. It does not prevent a determined actor with superuser access. For that, you need the second layer.

Layer 2: Cryptographic Hash Chaining

Hash chaining links every row to the row before it through a cryptographic digest. Modify any row in the chain, and every subsequent hash becomes invalid. You can verify the entire chain from any checkpoint forward, and tampering anywhere is detectable.

The schema change is small:

ALTER TABLE audit_events ADD COLUMN prev_hash TEXT NOT NULL DEFAULT '';
ALTER TABLE audit_events ADD COLUMN row_hash  TEXT NOT NULL DEFAULT '';

The hash for each row is computed over the immutable fields of that row plus the previous row’s hash. Because the previous hash is embedded, you cannot recompute a valid chain after the fact without control of the entire sequence.

The Trigger That Computes the Hash

This runs before each insert:

-- Requires pgcrypto extension
CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE OR REPLACE FUNCTION compute_audit_hash()
RETURNS TRIGGER AS $$
DECLARE
    last_hash TEXT;
    hash_input TEXT;
BEGIN
    -- Retrieve the most recent hash in the chain
    SELECT COALESCE(row_hash, '')
    INTO last_hash
    FROM audit_events
    ORDER BY id DESC
    LIMIT 1;
    IF last_hash IS NULL THEN
        last_hash := '';
    END IF;
    NEW.prev_hash := last_hash;
    -- Hash input includes all immutable fields plus the chain link
    hash_input := NEW.id::TEXT
        || '|' || NEW.user_id::TEXT
        || '|' || NEW.action
        || '|' || NEW.resource
        || '|' || NEW.resource_id
        || '|' || NEW.created_at::TEXT
        || '|' || last_hash;
    NEW.row_hash := encode(
        digest(hash_input, 'sha256'),
        'hex'
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER audit_hash_trigger
    BEFORE INSERT ON audit_events
    FOR EACH ROW
    EXECUTE FUNCTION compute_audit_hash();

Every row now carries two fields: prev_hash (what the chain looked like before this row) and row_hash (the cryptographic proof that this row's content is unchanged and follows the previous row). Tampering with user_id, action, resource_id, or any other immutable field on any existing row invalidates every hash that follows.

The Concurrent Insert Problem

The trigger has a race condition. Two inserts happening simultaneously both query SELECT row_hash FROM audit_events ORDER BY id DESC LIMIT 1. They both get the same previous hash. Both compute row_hash based on that shared value. When they both succeed, you have a fork in the chain: two rows claiming to follow the same predecessor. Verification will catch it, but you've lost the chain's consistency.

Three approaches to handle this:

Option 1: Advisory lock on insert

-- Wrap the insert in application code with a session-level advisory lock
SELECT pg_advisory_lock(12345);  -- 12345 is your audit table's lock ID
INSERT INTO audit_events (...);
SELECT pg_advisory_unlock(12345);

This serializes all audit writes through a single lock. Simple to implement. Becomes a throughput bottleneck at high write volume (thousands of events per second).

Option 2: Dedicated audit writer service

Route all audit inserts through a single application process that serializes writes internally. The audit writer accepts events via a queue and inserts them one at a time. The queue absorbs bursts. This is the pattern that survives real load without lock contention. It also means you can retry failed inserts without breaking the chain.

Option 3: Per-session chains

Track the chain per session or per user rather than globally. Each actor gets their own hash chain. Verification becomes: check that no chain was modified, and check that no rows were deleted from any chain. This trades global ordering for horizontal scalability. Not suitable if you need a total order of all events for compliance.

For most production use cases, Option 2 is the right answer. The queue can be anything: a Go channel, a Redis list, a database-backed job queue you already operate. The audit writer becomes a single-threaded consumer that inserts sequentially.

Verifying the Chain in Go

Verification reads the chain in order and recomputes each hash. Any mismatch is a tamper signal.

package audit

import (
    "crypto/sha256"
    "database/sql"
    "encoding/hex"
    "fmt"
    "strings"
)
type AuditRow struct {
    ID         int64
    UserID     string
    Action     string
    Resource   string
    ResourceID string
    CreatedAt  string
    PrevHash   string
    RowHash    string
}
// VerifyChain checks integrity of audit_events between startID and endID.
// Returns nil if the chain is intact, an error describing the first break found.
func VerifyChain(db *sql.DB, startID, endID int64) error {
    rows, err := db.Query(`
        SELECT id, user_id, action, resource, resource_id,
               created_at::TEXT, prev_hash, row_hash
        FROM audit_events
        WHERE id BETWEEN $1 AND $2
        ORDER BY id ASC
    `, startID, endID)
    if err != nil {
        return fmt.Errorf("query failed: %w", err)
    }
    defer rows.Close()
    var prevHash string
    for rows.Next() {
        var r AuditRow
        if err := rows.Scan(
            &r.ID, &r.UserID, &r.Action, &r.Resource,
            &r.ResourceID, &r.CreatedAt, &r.PrevHash, &r.RowHash,
        ); err != nil {
            return fmt.Errorf("scan failed: %w", err)
        }
        // Verify the prev_hash pointer is correct
        if r.PrevHash != prevHash {
            return fmt.Errorf(
                "chain broken at row %d: prev_hash mismatch (expected %s, got %s)",
                r.ID, prevHash, r.PrevHash,
            )
        }
        // Recompute this row's hash
        parts := []string{
            fmt.Sprintf("%d", r.ID),
            r.UserID,
            r.Action,
            r.Resource,
            r.ResourceID,
            r.CreatedAt,
            r.PrevHash,
        }
        hashInput := strings.Join(parts, "|")
        sum := sha256.Sum256([]byte(hashInput))
        computed := hex.EncodeToString(sum[:])
        if computed != r.RowHash {
            return fmt.Errorf(
                "tamper detected at row %d: stored hash %s, computed %s",
                r.ID, r.RowHash, computed,
            )
        }
        prevHash = r.RowHash
    }
    return rows.Err()
}

Run this verification on a schedule (daily, or triggered by compliance events) rather than on every read. Store the last verified checkpoint so you’re not always scanning from the beginning.

Checkpoint Anchoring

The chain is only as trustworthy as the start of your verification range. If a tamperer modifies row 1, then recomputes all subsequent hashes, your verification will pass.

The defence is periodic anchoring. After each verification pass, write the terminal row_hash to an external, immutable location: an append-only S3 bucket with Object Lock enabled, a write-once timestamped email to your audit inbox, a signed entry in a separate database. When you verify later, you verify that the chain from the anchor point forward is consistent with the anchor hash you stored.

This is what QLDB’s “digest” concept did automatically. Its 256-bit digest represented the root of a Merkle tree over all committed transactions. You could verify any individual entry with a Merkle proof rather than scanning the full chain. Building Merkle verification on top of PostgreSQL is possible but significantly more complex. It’s the right choice if you’re verifying millions of rows on a hot path. For most teams, sequential verification against stored checkpoints is sufficient.

Trade-off Comparison

RLS-only approach

• What it prevents: accidental or opportunistic modification by non-superuser roles
• What it doesn’t prevent: modification by superusers, direct filesystem access
• Query performance: identical to a normal table
• Operational overhead: low
• Use when: you need a basic access control layer with minimal complexity

Hash chaining

• What it prevents: undetected modification of any row, by anyone, after the fact
• What it doesn’t prevent: deletion of the entire table (backup and monitoring handles this)
• Query performance: inserts are slightly slower due to hash computation; reads are unchanged
• Operational overhead: requires a verification job and checkpoint anchoring strategy
• Use when: you need cryptographic tamper evidence for compliance or forensic use

Application-level append-only log

• What it prevents: modification by any database actor
• What it doesn’t prevent: modification of the log store itself
• Query performance: depends on the store (time-series DBs like ClickHouse are fast for reads, slower for relational queries)
• Operational overhead: another system to operate
• Use when: you already operate a separate audit store and want to avoid PostgreSQL for high-volume writes

Cloud-native alternatives post-QLDB:

• Azure SQL Ledger Tables: built-in cryptographic verification, similar to QLDB’s approach, worth evaluating if you’re already in Azure
• Aurora PostgreSQL with hash chaining: the path AWS itself recommends post-QLDB
• Google Cloud Spanner: audit logging built-in, but query patterns differ significantly

Partitioning From Day One

Before you deploy any of this, set up time-based partitioning on audit_events. Querying a year of audit records without partitioning is painful. Retrofitting partitioning after the table has 100 million rows is worse.

CREATE TABLE audit_events (
    id          BIGSERIAL,
    user_id     UUID NOT NULL,
    action      VARCHAR(100) NOT NULL,
    resource    VARCHAR(100) NOT NULL,
    resource_id TEXT NOT NULL,
    before      JSONB,
    after       JSONB,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    prev_hash   TEXT NOT NULL DEFAULT '',
    row_hash    TEXT NOT NULL DEFAULT ''
) PARTITION BY RANGE (created_at);

-- Create monthly partitions
CREATE TABLE audit_events_2026_04
    PARTITION OF audit_events
    FOR VALUES FROM ('2026-04-01') TO ('2026-05-01');
-- Index per partition for common query patterns
CREATE INDEX ON audit_events_2026_04 (user_id, created_at);
CREATE INDEX ON audit_events_2026_04 (resource, resource_id, created_at);

The hash chain works across partitions. Your verification query just needs to order by id ASC across all partitions and PostgreSQL handles the rest.

Where to Put the Application Layer

Hash computation in a database trigger is convenient but has a drawback: the trigger runs inside the transaction that inserts the row, which means database CPU is doing cryptographic work on every insert. At low volumes this is fine. At high volumes (thousands of inserts per second), offload hash computation to the application layer instead.

The trade-off is that application-layer hashing requires the audit writer service to be the serialization point: you cannot allow concurrent application instances to compute hashes independently and then race to insert.

For most architectures: start with the trigger approach. Profile. Move to application-layer hashing when PostgreSQL CPU becomes a constraint on the audit table specifically.

Gaps This Design Doesn’t Close

A few things this won’t solve, and you should know that upfront:

The schema itself can be altered. A sufficiently privileged attacker can add a column to audit_events, change a column type to allow null where you had NOT NULL, or truncate the table entirely. Schema-level protection requires a separate control: regular schema snapshots with hash comparison, or a separate monitoring process that alerts on DDL changes to audit tables.

created_at can be set to any value on insert. If you need event timestamps to be trustworthy, consider using PostgreSQL's clock_timestamp() inside the trigger rather than DEFAULT NOW(). The difference: clock_timestamp() returns the actual wall clock time at the moment of function execution, not the transaction start time. The difference matters in bulk inserts.

The application can lie. Hash chains verify that the stored data hasn’t changed since it was written. They don’t verify that the application wrote the correct data in the first place. An application bug (or a malicious application) can write misleading audit entries, and the chain will happily preserve the lie intact. This is out of scope for the database layer; it’s an application integrity problem.

Closing

The goal was never to build an unbreakable system. It doesn’t exist. What you’re building is a system where breaking it leaves evidence, and where the evidence is hard to remove without being caught.

Append-only is table stakes. A hash chain is the thing that makes “append-only” mean something to someone who has to sign a compliance report.

Start with the RLS policy. Add the chain when the compliance requirements call for it. Run weekly verification. Anchor the checkpoints somewhere immutable. The cost is measured in hours to implement and milliseconds per insert. The alternative is an audit trail that audits nothing.

If you’re building a dedicated audit writer service and want to go deeper on the queue design or checkpoint anchoring patterns, drop a comment below.