How to Build Audit Logs for a SaaS App: The Complete Engineering Guide
Most SaaS audit logs are just an events table with a timestamp, and they fall apart the moment someone has a reason to doubt them. This guide shows how to build audit logs that hold up: the schema, append-only storage, idempotent ingest, retention, and the tamper-evidence and verification layer that turns a log into evidence. Then where the build-versus-buy line actually is.
Most SaaS audit logs are just an events table with a timestamp
Almost every SaaS app has something it calls an audit log. Usually it is a table named events or activity_log, with a user ID, an action string, a JSON blob, and a created_at column. It works. It shows up in the admin panel. Everyone moves on.
Then the company moves upmarket. A prospect's security team sends a questionnaire. An enterprise contract asks for tamper-evident audit trails with defined retention. A SOC 2 auditor asks how you prove a log entry has not been altered since it was written. A customer disputes who changed a setting, and their counsel asks you to produce records. Suddenly that events table is load-bearing in a way it was never designed for.
The gap is not features. It is trust. An audit log that lives in a database you control, with timestamps from a clock you control, that any engineer with production access can edit, is a story you are asking someone to believe. That is fine for debugging. It is not fine when someone has a reason to doubt you, which is exactly when an audit log earns its keep.
This guide builds an audit log that holds up under that scrutiny. We will work through the event schema, append-only storage, idempotent ingestion, retention, and the part most teams skip entirely: making each record cryptographically verifiable by someone who does not trust you. Then we will be honest about how much of this is worth building yourself.
Decide what an audit event is before you write any code
The schema is the decision you will live with longest, so copy one that already won. The shape that has converged across AWS CloudTrail and the OCSF standard is a small set of fields: who did it (actor), what they did (action), what it was done to (target), the surrounding context, free-form metadata, and when it happened (occurred_at). Mirror those names. A bespoke schema feels productive on day one and becomes a migration you dread on day five hundred, especially if you ever want to stream into a customer's SIEM that already speaks one of those formats.
Two rules make the rest of the system possible. First, action is a stable, dotted verb like user.role.updated or billing.plan.changed, not a free-text sentence. You will filter, alert, and aggregate on it. Second, keep metadata values to scalars: strings, integers, and booleans, with no nested objects and no floats. This looks pedantic until you reach verification, where a float that serializes as 1.0 on one machine and 1 on another silently breaks every signature. Scalars serialize deterministically, and that determinism is the whole reason for the rule.
occurred_at should be supplied by the caller, because the moment that matters is when the action happened in your application, not when your logging pipeline got around to storing it. Make it part of the primary key together with the event ID and you also get natural time-ordering for free.
// Field names mirror the OCSF convention so you are not
// locked into a bespoke schema you will regret in two years.
interface AuditEvent {
id: string; // server-minted, sortable (ULID), e.g. "aevt_01HXY..."
org_id: string; // the tenant the event belongs to (always scope by this)
seq: number; // per-org, gapless, server-assigned. The deletion detector.
action: string; // dotted verb: "user.role.updated"
occurred_at: string; // RFC 3339, client-supplied (when it happened, not when stored)
actor: { id: string; type: "user" | "api_key" | "system"; name?: string };
target: { id: string; type: string; name?: string };
context: { ip?: string; user_agent?: string; request_id?: string };
metadata: Record<string, string | number | boolean>; // scalars only, see below
}Key insight. Borrow the OCSF field names even if you never integrate with it. The cost is zero, and it keeps a future SIEM export, or a migration onto managed infrastructure, from becoming a rewrite.
Make storage append-only, and mean it
An audit log has exactly one write pattern: insert. It is never updated and never deleted in the normal course of business. Encode that as a hard constraint in the database, not as a convention the application promises to honor, because the threat you are defending against includes people who have application access.
In Postgres, that means a BEFORE UPDATE and BEFORE DELETE trigger that raises an exception on any attempt to mutate a row. Retention deletes, when they eventually come, go through a single explicit, audited path that is the only thing allowed to bypass the guard, never your normal code. Partition the table by month from the start. Audit logs only grow, and monthly range partitions are what let you prune queries to a time window and archive or drop old data cheaply later. Retrofitting partitioning onto a 200-million-row table in production is a bad weekend.
A unique index on (org_id, seq) enforces the gapless sequence we will rely on shortly. Index for how you will actually read: almost always scoped to one tenant, filtered by action and time, paginated newest-first.
-- Range-partition by month so old data is cheap to archive and queries prune.
CREATE TABLE audit_events (
id TEXT NOT NULL,
org_id TEXT NOT NULL,
seq BIGINT NOT NULL,
action TEXT NOT NULL,
occurred_at TIMESTAMPTZ NOT NULL,
actor JSONB NOT NULL,
target JSONB NOT NULL,
context JSONB NOT NULL DEFAULT '{}',
metadata JSONB NOT NULL DEFAULT '{}',
payload_hash BYTEA NOT NULL, -- SHA-256 of the canonical event
signature BYTEA NOT NULL, -- Ed25519 over payload_hash
PRIMARY KEY (occurred_at, id)
) PARTITION BY RANGE (occurred_at);
-- The gapless contract: one sequence per org, no duplicates.
CREATE UNIQUE INDEX audit_events_org_seq ON audit_events (org_id, seq);
-- Block edits and deletes at the database, not in application code.
CREATE OR REPLACE FUNCTION audit_no_mutate() RETURNS trigger AS $fn$
BEGIN
RAISE EXCEPTION 'audit_events is append-only (op=%, id=%)', TG_OP, OLD.id
USING ERRCODE = 'restrict_violation';
END;
$fn$ LANGUAGE plpgsql;
CREATE TRIGGER audit_block_update BEFORE UPDATE ON audit_events
FOR EACH ROW EXECUTE FUNCTION audit_no_mutate();
CREATE TRIGGER audit_block_delete BEFORE DELETE ON audit_events
FOR EACH ROW EXECUTE FUNCTION audit_no_mutate();Key insight. Restricting DELETE permissions in the application is not a control an auditor will credit. A trigger in the database that blocks mutation for everyone, including the application role, is. Put the guarantee where the threat cannot reach it.
Append-only by policy is not the same as provable
Here is the uncomfortable part. Even a perfectly append-only table proves nothing to an outsider. A database administrator can disable a trigger, edit a row, and re-enable it. A cloud provider's staff can touch the storage volume underneath. When you hand an auditor a CSV export of your audit table, the only thing backing it is your word that nobody did any of that. In an adversarial setting, your word is precisely what is in question.
The fix is the same cryptography that backs TLS and code signing. At write time you canonicalize the event into deterministic bytes, hash those bytes with SHA-256, and sign the hash with a private key. Store the hash and the signature on the row. Now the record carries its own integrity: anyone holding the event and your public key can confirm the bytes have not changed since you signed them, without trusting your database, your clock, or you.
Use a separate signing key per tenant rather than one global key. It limits blast radius, lets you attribute a signature to a specific organization, and means a verifier can pin a customer's key independently. Keep the private keys encrypted at rest and never let them leave the backend; the signature is produced server-side.
Canonicalization is the unglamorous detail that makes all of this work. The same event has to produce the same bytes every time, on every machine, or signatures will not verify. That means sorting object keys, normalizing the timestamp to a single format, dropping nulls, and the scalar-only metadata rule from earlier. Two systems that disagree on how to render a number cannot agree on a signature.
import { createHash } from "node:crypto";
import { signAsync } from "@noble/ed25519";
// 1. Canonicalize: deterministic JSON. Same input produces the same bytes,
// always. Sort keys, drop nulls, normalize the timestamp, reject floats.
function canonicalize(event: object): Uint8Array {
return new TextEncoder().encode(stableStringify(event));
}
// 2. Hash the canonical bytes.
function payloadHash(event: object): Buffer {
return createHash("sha256").update(canonicalize(event)).digest();
}
// 3. Sign the hash with THIS tenant's private key (never a shared key).
async function signEvent(event: object, tenantPrivateKey: Uint8Array) {
const hash = payloadHash(event);
const signature = await signAsync(hash, tenantPrivateKey);
return { payload_hash: hash, signature };
}Key insight. Traditional logs ask the examiner to trust your systems. A signed record removes the need for trust: the mathematics verify themselves. That is the line between a log and evidence.
Gapless sequencing: how you catch deletions, not just edits
Signatures catch one kind of tampering: modifying an event. They are blind to another: deleting one outright. A removed row leaves no signature to fail, because it is simply gone. If your audit log can be quietly truncated, an attacker, or an embarrassed insider, does not edit the incriminating event. They delete it.
The defense is a per-organization sequence number, assigned server-side, strictly increasing, with no gaps. Event 41 is followed by 42 is followed by 43. To verify integrity, a reader pulls an organization's events and confirms the sequence is contiguous. A missing number is a deleted event, full stop. This is why the unique index on (org_id, seq) matters, and why the sequence must be assigned transactionally at write time, never from a non-transactional counter that could skip.
One honest limit worth stating plainly: a gapless sequence plus signatures catches edits and middle-deletions, but not truncation of the most recent events. If the last ten events are removed, the remaining sequence still looks contiguous. Closing that gap requires periodically publishing a signed checkpoint of the latest sequence number and a root hash to somewhere outside the database, so the head of the log is externally witnessed. Most homegrown logs never get this far, which is worth knowing before you assume yours is airtight.
Ingestion has to be idempotent
Audit events are generated in the same code paths that do real work, and those paths retry. A request times out, a job is redelivered, a network blip triggers a client retry. If each attempt writes a row, your audit log fills with duplicates, and duplicates in an audit log are their own integrity problem: which one is real?
Require an idempotency key on every write and make the database enforce uniqueness. A redelivered event with the same key becomes a no-op that returns the original event, not a second row. The two-line version is an ON CONFLICT DO NOTHING on a unique key. The robust version maps each idempotency key to the event ID it minted, so a retry returns the same ID a client can store. Reserve the sequence number and the idempotency record in the same transaction as the insert, so a crash mid-write cannot burn a sequence number and leave a gap that later looks like tampering.
-- The client sends an Idempotency-Key. Uniqueness makes a redelivered
-- or retried event a no-op instead of a second row.
INSERT INTO audit_events (id, org_id, seq, action, occurred_at,
actor, target, context, metadata,
payload_hash, signature)
VALUES (...)
ON CONFLICT (org_id, seq) DO NOTHING;
-- Map the key to the minted event id so a retry returns the original.
-- Write this in the SAME transaction that reserves the sequence number.
CREATE TABLE audit_idempotency (
org_id TEXT NOT NULL,
idempotency_key TEXT NOT NULL,
event_id TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
PRIMARY KEY (org_id, idempotency_key)
);Retention and cost: audit logs only grow
An audit log is the one table in your system that never shrinks and that you are often contractually forbidden from trimming. At enterprise volume it will be your largest table within a year. Plan for that on day one or pay for it later.
The pattern that works is tiered retention. Keep a hot window in Postgres, recent enough to serve the dashboard and API fast, typically a few months. Age older events into cheap object storage as compressed, append-only files, and verify the archive by reading it back and re-hashing before you delete the hot copy. Past the contractual retention limit, purge. Drive the windows off the customer's plan rather than hardcoding them, and never let a retention job delete an event it has not first confirmed is safely archived. This is also where the append-only trigger needs its single, audited exception: the retention worker is the only writer allowed to delete, and that path is logged.
Done well, retention is invisible. Done badly, it is either a runaway storage bill or, worse, an audit log that quietly dropped the records you needed.
The verification layer is the entire point
Everything so far exists to support one capability: letting someone who does not trust you confirm what happened. If you build the storage and skip this, you have a nicer log, not evidence.
A verifier needs three checks, and none of them should require access to your systems. Recompute the SHA-256 of the canonical event and confirm it matches the stored hash, which proves the content is unchanged. Verify the Ed25519 signature against the tenant's published public key, which proves you issued it. Scan the per-organization sequence for gaps, which proves nothing was deleted. Expose this behind an endpoint that needs no authentication, so an auditor or a customer's counsel can verify a record themselves, and the trust question collapses into a one-second technical check.
This maps directly onto how records are actually challenged. Under Federal Rules of Evidence 902(14), electronically stored records are self-authenticating when produced by a process that reliably generates and authenticates them. A signed, independently verifiable audit event is built for exactly that provision. Without it, log-based evidence needs expert testimony about your system's integrity, which is expensive and easy for the other side to attack.
# Fetch an event's proof. No API key, no account. That is the point.
curl https://api.invoance.com/v1/audit/events/aevt_01HXY7K3.../verify
# Response, re-derived independently from the stored columns:
# {
# "valid": true,
# "payload_hash": "ef2d127de37b942baad06145e54b0c619a1f223...",
# "key_source": "tenant_keys",
# "reason": null
# }
# Tamper with any field and re-verify: valid becomes false.
# That is the test no plaintext log can pass.- How Invoance verification works— The recompute-hash, check-signature, scan-sequence flow, and how offline verification against a published tenant key works in practice.
- API endpoint reference— The ingest, read, verify, and integrity-scan endpoints, with request and response shapes.
Build versus buy, without the hand-waving
Build it yourself when the audit log is genuinely internal: a convenience feature in your admin panel, nothing external depends on it, and no auditor or counterparty will ever scrutinize it. At that bar, an append-only table with good indexes is the right amount of engineering, and adding signatures would be over-building.
Build on infrastructure the moment the audit log faces anyone outside your company. If you are selling to enterprises, pursuing SOC 2, operating in a regulated industry, exposing the trail to your own customers, or you simply need a record that holds up when someone has a reason to doubt it, the verifiability layer is not optional, and it is the part that is expensive and easy to get subtly wrong. Rebuilding canonicalization, per-tenant signing, gapless sequencing, an external checkpoint, verification endpoints, retention, and key rotation is months of work that is not your product.
This is the gap Invoance is built for. Every audit event is canonicalized, signed with a per-tenant Ed25519 key, sequenced, and stored append-only, and every event is independently verifiable through a public endpoint with no account required. That last property is the difference from a typical logging product: not just that the data is stored, but that anyone can prove it was not altered. The same signing-and-verification infrastructure already runs in production behind Invoance's Event Ledger, so you are building on a proven path, not a promise.
- Event Ledger: signed, append-only event records— The live product built on the same signing and verification core this guide describes.
- Why traditional audit logs fail under scrutiny— The companion piece on why database logs lose credibility in an audit or legal proceeding.
- Pricing and plan tiers— Free tier for development, with retention and volume scaling by plan.
What it looks like as one API call
From your application, an audit event is a single HTTP call placed where the action happens, alongside your existing logging. It does not change your data model or your response shape, and it carries an idempotency key so retries are safe.
Because the API is plain HTTP and JSON, it works from any language; the example below is Node, but Python, Go, or a raw curl are identical in shape. The event comes back with a server-minted ID and sequence number, signed and stored. From then on, that ID is the handle you give an auditor or a customer.
The verification step is the one to demo before you commit. Hand an evaluator an event ID, let them hit the public verify endpoint and watch it return valid, then change a byte and watch it return invalid. That is usually the moment the trust conversation ends, and it is the same flow whether you are on the free tier or an enterprise plan: the infrastructure is identical, only the limits and retention scale.
// One call, right where the action happens. Safe to retry.
await fetch("https://api.invoance.com/v1/audit/events", {
method: "POST",
headers: {
"Authorization": "Bearer " + process.env.INVOANCE_API_KEY,
"Idempotency-Key": crypto.randomUUID(),
"Content-Type": "application/json",
},
body: JSON.stringify({
action: "user.role.updated",
occurred_at: new Date().toISOString(),
actor: { id: "u_42", type: "user", name: "ada@acme.com" },
target: { id: "u_77", type: "user" },
metadata: { from_role: "member", to_role: "admin" },
}),
});- Start free— Create a tenant, generate an API key, and emit your first signed audit event in minutes.
- Compare plans— Retention windows, event volume, and signing limits by tier.
Append-only, signed records of business events for audits, compliance, and regulatory proof, independently verifiable.
Recommended
Why Traditional Audit Logs Fail Under Regulatory Scrutiny
Your application logs record what happened. But in an audit or legal proceeding, the first question is not what your logs say, it is whether anyone can trust your logs. Traditional logging has a fundamental integrity problem that most teams do not address until it is too late.
How to Export Audit Logs for Enterprise Customers: Signed, Verifiable, Audit-Ready
Any system can dump audit logs to a CSV. The problem is that a CSV is a file you are asking an auditor to trust. This guide shows how to export audit logs as signed, independently verifiable records: paginated from a real API, with a per-record Ed25519 signature and a gapless sequence that proves nothing was dropped or altered.
Event Ledger: Immutable Compliance Records for Business Events
Logs can be edited. Databases can be modified. The Event Ledger is different, every event is hashed with SHA-256, signed with Ed25519, and stored in an append-only ledger that cannot be altered after ingestion.
How to Tell If a PDF Has Been Edited or Backdated (And How to Actually Prove It Wasn't)
A PDF's Created and Modified dates are just editable text, and a clean re-save erases the very traces forensics looks for. You cannot reliably detect tampering or backdating after the fact. Here is why, and how to prove a document is unchanged with a hash, a signature, and a timestamp its author never controlled.