Designing a SaaS Audit Log Customers Trust: Hash-Chained and Queryable

A SaaS audit log customers trust records who did what, to which record, and when; is written in the same database transaction as the change; cannot be updated or deleted by the application; and is hash-chained, so each entry carries a fingerprint of the one before and any edit or deletion shows up on verification. Each tenant's admins should be able to search and export their own history.

This is engineering guidance for multi-tenant SaaS on PostgreSQL, with a working schema and SQL. The pattern is the one RAITHub runs on Sundor Skin, a B2B wholesale platform it built: an append-only, hash-chained audit log, written in the same transaction as the change it records and partitioned by month. The code below is a simplified version of that pattern for this article, not Sundor Skin's source. For where the audit log sits among the other B2B foundations, see the multi-tenant SaaS guide.

Why do B2B customers ask about audit logs?

Because after an incident, or during a security review, they need to answer "who changed this, and can we prove it?" The OWASP Top 10's A09:2025 Security Logging and Alerting Failures tells teams to "ensure all transactions have an audit trail with integrity controls to prevent tampering or deletion, such as append-only database tables or similar". Security questionnaires ask the same thing in plainer words, which is covered in answering your first security questionnaire without SOC 2.

An audit log is not the same as application logs. Application logs are for your engineers, are sampled and rotated, and live in a logging service. An audit log is a business record for your customer: every entry is kept for an agreed period, it is tenant-scoped, and it is shown in the product.

What levels of audit logging are there?

Level	What it guarantees	What it does not	When it is enough
Log lines in your logging service	Engineers can debug	Completeness, retention, customer access	Never, as a customer-facing audit trail
Audit table, written after the change	A searchable history	A crash between the change and the log leaves a gap; the app can edit rows	Internal admin tools with low stakes
Same transaction, append-only	No change without its entry; the app role cannot edit or delete	A database superuser could still alter rows unnoticed	Most B2B SaaS at launch
Append-only and hash-chained	Any edit, deletion or reordering is detectable	Rewriting the whole chain, unless the latest hash is anchored elsewhere	Money, stock, credit, permissions, regulated customers
Hash-chained with external anchoring	Even a full rewrite is detectable against the anchored hash	Nothing practical, at the cost of an extra process	Customers who audit you formally

The RAITHub website's own admin area keeps a plain audit log of admin actions, which fits its low stakes. Sundor Skin, which handles orders, tier pricing and buyer credit, uses the hash-chained level.

What should a SaaS audit log record?

The OWASP Logging Cheat Sheet frames it as "when, where, who and what" for each event. For a multi-tenant product, that becomes:

• Who: the actor's ID and type (a user, your own support staff, a system job or an API key), plus the IP address and a request ID to join it to application logs.
• What: an action code in resource.action form, such as invoice.approve or member.role_change, the target type and ID, and the fields that changed with before and after values.
• When and where: a server timestamp and the tenant.

Record permission and role changes, logins and failed logins, exports, settings changes, anything that touches money or stock, and every time your own staff access a customer's tenant. The action codes can match your permission codes, which makes the log easy to read against your roles; see SaaS authorization and RBAC design.

Leave secrets out. OWASP lists what should not be recorded directly, including "authentication passwords", "session identification values", "access tokens", "encryption keys and other primary secrets" and payment card data. For a changed password, log that it changed, never the value.

What does an audit log schema look like in PostgreSQL?

One table partitioned by month, and a small table holding the latest hash of each tenant's chain:

CREATE TABLE audit_log (
  tenant_id    uuid        NOT NULL,
  seq          bigint      NOT NULL,  -- position in this tenant's chain
  occurred_at  timestamptz NOT NULL,
  actor_id     uuid,                  -- NULL for system jobs
  actor_type   text        NOT NULL,  -- 'user' | 'staff' | 'system' | 'api_key'
  action       text        NOT NULL,  -- 'invoice.approve'
  target_type  text        NOT NULL,
  target_id    text        NOT NULL,
  changes      jsonb       NOT NULL DEFAULT '{}',  -- {"status": ["draft", "approved"]}
  ip           inet,
  request_id   text,
  prev_hash    bytea       NOT NULL,
  hash         bytea       NOT NULL,
  PRIMARY KEY (tenant_id, seq, occurred_at)
) PARTITION BY RANGE (occurred_at);

CREATE TABLE audit_log_2026_12 PARTITION OF audit_log
  FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');

CREATE INDEX ON audit_log (tenant_id, occurred_at DESC);
CREATE INDEX ON audit_log (tenant_id, target_type, target_id);

-- One row per tenant: the tip of its chain.
CREATE TABLE audit_chain_head (
  tenant_id uuid   PRIMARY KEY REFERENCES tenants (id),
  seq       bigint NOT NULL DEFAULT 0,
  hash      bytea  NOT NULL
);

Monthly partitions are what make retention cheap. PostgreSQL's partitioning documentation notes that dropping or detaching a partition "is far faster than a bulk operation" and avoids "the VACUUM overhead caused by a bulk DELETE". Create next month's partition ahead of time with a scheduled job, or inserts will fail on the first of the month.

Each tenant gets its own chain. That means two tenants writing at the same moment never wait on each other, and a customer's export is a complete, verifiable chain on its own.

How do you hash-chain an audit log?

Each entry's hash is SHA-256 over the previous entry's hash plus a fixed, canonical encoding of the entry's own fields. PostgreSQL has a built-in sha256(bytea) function, per its binary string functions reference, so the whole chain can live in the database:

-- The canonical form: a JSON array with a fixed field order. Timestamps are
-- converted to UTC so the text never depends on the session's time zone.
CREATE FUNCTION audit_row_hash(prev bytea, r audit_log) RETURNS bytea
LANGUAGE sql STABLE AS $$
  SELECT sha256(prev || convert_to(jsonb_build_array(
    r.tenant_id, r.seq, r.occurred_at AT TIME ZONE 'UTC', r.actor_id, r.actor_type,
    r.action, r.target_type, r.target_id, r.changes, r.ip, r.request_id
  )::text, 'UTF8'))
$$;

CREATE FUNCTION audit_append(e audit_log) RETURNS void
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, pg_temp AS $$
DECLARE
  head audit_chain_head;
BEGIN
  -- Lock this tenant's chain tip. Concurrent writers for the same tenant queue here.
  SELECT * INTO head FROM audit_chain_head WHERE tenant_id = e.tenant_id FOR UPDATE;
  IF NOT FOUND THEN
    RAISE EXCEPTION 'no audit chain for tenant %', e.tenant_id;
  END IF;

  e.seq         := head.seq + 1;
  e.occurred_at := now();
  e.prev_hash   := head.hash;
  e.hash        := audit_row_hash(head.hash, e);

  INSERT INTO audit_log SELECT (e).*;
  UPDATE audit_chain_head SET seq = e.seq, hash = e.hash WHERE tenant_id = e.tenant_id;
END
$$;

-- When a tenant is created, start its chain from a genesis hash.
INSERT INTO audit_chain_head (tenant_id, hash)
VALUES ($1, sha256(convert_to($1::text, 'UTF8')));

Three choices in this code matter. The canonical form uses jsonb, whose text output is stable for equal values, so the same row always hashes the same way. The timestamp is converted to UTC before hashing. And the FOR UPDATE lock on the chain tip gives each tenant a strict order, so no two entries claim the same predecessor.

How do you write the audit entry in the same transaction as the change?

Call the append function inside the transaction that makes the change. If the change rolls back, so does its entry; if the entry fails, the change does not happen.

await client.query('BEGIN')
await client.query("SELECT set_config('app.tenant_id', $1, true)", [session.tenantId])

await client.query(
  "UPDATE invoices SET status = 'approved', approved_by = $1 WHERE id = $2",
  [session.userId, invoiceId],
)

await client.query(
  'SELECT audit_append(jsonb_populate_record(NULL::audit_log, $1::jsonb))',
  [JSON.stringify({
    tenant_id: session.tenantId,
    actor_id: session.userId,
    actor_type: 'user',
    action: 'invoice.approve',
    target_type: 'invoice',
    target_id: invoiceId,
    changes: { status: ['draft', 'approved'] },
    ip: clientIp,
    request_id: requestId,
  })],
)

await client.query('COMMIT')

Writing the log from a message queue or an event after commit is common and weaker: a crash between the two leaves a change with no entry. The same-transaction write is what Sundor Skin does.

How do you stop anyone editing the audit log?

Take the rights away from the application, then add a trigger as a second barrier.

-- The app role can read (row-level security scopes it to one tenant) but not write directly.
REVOKE ALL ON audit_log, audit_chain_head FROM app_user;
GRANT SELECT ON audit_log TO app_user;
GRANT EXECUTE ON FUNCTION audit_append(audit_log) TO app_user;

-- Even the owner cannot update or delete rows without disabling this first.
CREATE FUNCTION audit_log_immutable() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_log is append-only';
END
$$;

CREATE TRIGGER audit_log_no_change BEFORE UPDATE OR DELETE ON audit_log
  FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();

audit_append runs as SECURITY DEFINER, so it inserts with its owner's rights while the app role holds none; the pinned search_path stops a caller from swapping in their own functions. Add a row-level security policy on audit_log so each tenant reads only its own entries, as in the PostgreSQL row-level security guide. A superuser can still disable the trigger. That is what the hash chain is for: it does not prevent tampering, it makes tampering visible.

How do you verify the chain has not been tampered with?

Recompute every hash and check each entry points at the one before it:

SELECT seq, 'broken' AS problem
FROM (
  SELECT a.seq, a.hash, a.prev_hash,
         lag(a.hash) OVER (ORDER BY a.seq) AS expected_prev,
         lag(a.seq)  OVER (ORDER BY a.seq) AS previous_seq,
         audit_row_hash(a.prev_hash, a)    AS recomputed
  FROM audit_log a
  WHERE a.tenant_id = $1
) c
WHERE hash <> recomputed                                        -- a field was edited
   OR (expected_prev IS NOT NULL AND prev_hash <> expected_prev)  -- an entry was removed or reordered
   OR (previous_seq IS NOT NULL AND seq <> previous_seq + 1)      -- a gap in the sequence
ORDER BY seq;

An empty result means the chain is intact. Also compare the last entry's hash with audit_chain_head, which catches entries deleted from the end. Run verification on a schedule and alert on any row returned.

To catch a full rewrite by someone with database superuser access, anchor the chain outside the database: once a day, copy each tenant's latest hash to storage the database administrators cannot overwrite, or include it in the export customers download. When you drop old partitions for retention, record the hash of the last dropped entry first, so verification can start from there.

How should customers search and export their audit log?

• In the product. A filterable view for the tenant's admins: by user, action, target and date range. The (tenant_id, occurred_at) and (tenant_id, target_type, target_id) indexes serve those queries.
• Per record. A "history" panel on an invoice or order, which is where most people actually look.
• Export. CSV or JSON including seq, prev_hash and hash, with the canonical encoding documented, so a customer's own auditor can verify it independently.
• Retention. State it in the contract and the product. Monthly partitions make "keep 24 months" a scheduled drop, not a delete job.

Why RAITHub for audit logging?

Because RAITHub runs this design in production, not only on paper.

• Hash-chained audit log live. Sundor Skin: append-only, hash-chained, written in the same transaction as each change and partitioned by month, on a 146-table PostgreSQL core with row-level security, 88 permission codes, 12 staff roles and 530+ automated tests. See the Sundor Skin case study.
• Separation of duties in the database. On Sundor Skin, conflicting permissions are declared and enforced in the database, so no single role holds both sides of a sensitive pair.
• Honest about certification. RAITHub is not SOC 2 or ISO 27001 certified. It signs DPAs and SCCs and follows your controls.

Adding an audit trail to an existing backend is a well-bounded piece of work; see the API and backend development service or the SaaS development service.

When you don't need us

• An internal tool with a few trusted admins. A plain, append-only table written in the same transaction is enough.
• You need a certified audit. Engage an accredited auditor; RAITHub builds and tests the controls but does not certify them.
• You want a managed audit-log product. If you would rather buy than build, evaluate a vendor against the verification and export points above.

If a customer has asked "can you prove who changed this?", send RAITHub your current schema and how changes are written today, and book the free 15-minute technical audit.

Last reviewed: 29 September 2026. SQL written for PostgreSQL 18; the schema is illustrative, not Sundor Skin's source.

Frequently asked questions

What is a hash-chained audit log?

An audit log where each entry stores a SHA-256 hash of its own contents combined with the previous entry's hash. Changing, deleting or reordering any entry breaks every hash after it, so verification reveals the tampering.

Does a hash chain prevent tampering?

No. It makes tampering detectable. Prevention comes from permissions: the application role cannot update or delete audit rows. To detect a full rewrite by a database superuser, copy the latest hash somewhere they cannot change.

Should the audit log be in the same database as the application?

Yes, for the write. Writing the entry in the same transaction as the change guarantees there is never a change without its entry. You can replicate or export entries elsewhere afterwards for anchoring and long-term storage.

What should never go into an audit log?

Secrets and sensitive values: passwords, session IDs, access tokens, encryption keys and payment card data, as the OWASP Logging Cheat Sheet lists. Record that a password or key changed, not the value.

How long should SaaS audit logs be kept?

As long as your contracts and your customers' own obligations require, which varies by industry and country. This is general information; confirm the period with your adviser. Monthly partitions make the chosen period cheap to enforce.

Why partition the audit log by month?

Because audit tables only grow. Monthly partitions let you drop a whole month when it passes the retention period, which PostgreSQL's documentation says is far faster than a bulk DELETE and avoids its VACUUM overhead.