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

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_01J0Y1Z2A3B4C5D6E7F8G9H0JK"
  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)
  ingested_at: string; // RFC 3339, server-assigned (when it was stored); signed as well
  actor:   { type: "user" | "api_key" | "system"; id: string; name?: string };
  targets: { type: string; id: string; name?: string }[]; // what it was done to; [] when nothing
  context?: { location?: string; user_agent?: string };  // where it came from; nothing else goes here
  metadata?: Record<string, string | number | boolean>;  // scalars only, see below
}

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,
  ingested_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  actor        JSONB       NOT NULL,
  targets      JSONB       NOT NULL DEFAULT '[]',
  context      JSONB       NOT NULL DEFAULT '{}',
  metadata     JSONB       NOT NULL DEFAULT '{}',
  payload_hash BYTEA       NOT NULL,   -- SHA-256 of the canonical event bytes
  signature    BYTEA       NOT NULL,   -- Ed25519 over those same canonical bytes
  PRIMARY KEY (occurred_at, id)
) PARTITION BY RANGE (occurred_at);

-- The gapless contract: one sequence per org, no duplicates. Postgres will not
-- accept a unique index on a partitioned table that leaves out the partition
-- key, so the per-org counter lives in a small unpartitioned table. Every
-- write takes its seq from here, in the same transaction as the insert.
CREATE TABLE audit_seq (
  org_id   TEXT   PRIMARY KEY,
  last_seq BIGINT NOT NULL DEFAULT 0
);

-- Reads are almost always one tenant, newest first.
CREATE INDEX audit_events_org_seq ON audit_events (org_id, seq DESC);

-- 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();

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, createPrivateKey, randomBytes, sign } from "node:crypto";

// 1. Canonicalize: deterministic JSON. Same input produces the same bytes,
//    always. Drop nulls, sort keys at every level, no whitespace. Timestamps
//    are normalized before this point and metadata holds scalars only, so a
//    float can never serialize two ways.
function stableStringify(value: unknown): string {
  if (Array.isArray(value)) return "[" + value.map(stableStringify).join(",") + "]";
  if (value && typeof value === "object") {
    const obj = value as Record<string, unknown>;
    return (
      "{" +
      Object.keys(obj)
        .filter((k) => obj[k] !== null && obj[k] !== undefined)
        .sort()
        .map((k) => JSON.stringify(k) + ":" + stableStringify(obj[k]))
        .join(",") +
      "}"
    );
  }
  return JSON.stringify(value);
}

function canonicalize(event: object): Uint8Array {
  return new TextEncoder().encode(stableStringify(event));
}

// 2. Hash the canonical bytes. This is the row's payload_hash.
function payloadHash(event: object): Buffer {
  return createHash("sha256").update(canonicalize(event)).digest();
}

// 3. Sign the canonical bytes (not the hash) with THIS tenant's private key,
//    never a shared key. A verifier rebuilds the same bytes from the event
//    and checks them against the tenant's published public key.
function signEvent(event: object, tenantPrivateKey: Uint8Array) {
  // A raw 32-byte Ed25519 seed, wrapped in the PKCS#8 header node:crypto expects.
  const key = createPrivateKey({
    key: Buffer.concat([
      Buffer.from("302e020100300506032b657004220420", "hex"),
      Buffer.from(tenantPrivateKey),
    ]),
    format: "der",
    type: "pkcs8",
  });
  return {
    payload_hash: payloadHash(event),
    signature: sign(null, canonicalize(event), key),
  };
}

// Demo with a throwaway seed. In production the seed is generated once per
// tenant, stored encrypted and decrypted only inside the signing service.
const tenantPrivateKey = randomBytes(32);

const { payload_hash, signature } = signEvent(
  {
    id: "aevt_01J0Y1Z2A3B4C5D6E7F8G9H0JK",
    org_id: "org_8472",
    seq: 42,
    action: "user.role.updated",
    occurred_at: "2026-09-22T08:14:07.000Z",
    ingested_at: "2026-09-22T08:14:07.312Z",
    actor: { type: "user", id: "u_42", name: "ada@acme.com" },
    targets: [{ type: "user", id: "u_77" }],
    metadata: { from_role: "member", to_role: "admin" },
  },
  tenantPrivateKey,
);
console.log(payload_hash.toString("hex"));
console.log(signature.toString("hex"));

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. Map it to the event id it minted, in
-- the SAME transaction that reserves the sequence number, so a crash mid-write
-- cannot burn a sequence number and leave a gap that later looks like tampering.
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)
);

BEGIN;

-- 1. Reserve the key. Uniqueness makes a redelivered or retried event a no-op:
--    0 rows inserted means a retry, so read the stored event_id back, return
--    it to the client and stop here instead of writing a second row.
INSERT INTO audit_idempotency (org_id, idempotency_key, event_id)
VALUES ($1, $2, $3)
ON CONFLICT (org_id, idempotency_key) DO NOTHING;

-- 2. Take the org's next seq. The row lock serializes writers for the org and
--    a rollback hands the number back, so the sequence stays gapless.
UPDATE audit_seq SET last_seq = last_seq + 1 WHERE org_id = $1
RETURNING last_seq;

-- 3. Canonicalize, hash and sign with that seq and ingested_at included, then
--    write the row. Same transaction, so all three land or none do.
INSERT INTO audit_events (id, org_id, seq, action, occurred_at, ingested_at,
                          actor, targets, context, metadata,
                          payload_hash, signature)
VALUES ($3, $1, $4, $5, $6, $7, $8, $9, $10, $11, $12, $13);

COMMIT;

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 public proof. No API key, no account. That is the point.
curl https://api.invoance.com/v1/proof/audit/aevt_01J0Y1Z2A3B4C5D6E7F8G9H0JK

# The verdict is recomputed on the server from the stored row, against the
# tenant's registered key rather than the key on the row:
# {
#   "organization": {
#     "name": "Northwind Legal",
#     "issuer_name": "Northwind Legal Ltd",
#     "primary_domain": "northwindlegal.example",
#     "domain_verified": true,
#     "logo_url": "https://cdn.example.com/northwind/logo.svg"
#   },
#   "org": { "org_id": "aorg_01J0XW9K3RQ5T7V8Y2C4E6G8HM", "name": "Acme Robotics" },
#   "event": {
#     "event_id": "aevt_01J0Y1Z2A3B4C5D6E7F8G9H0JK",
#     "org_id": "aorg_01J0XW9K3RQ5T7V8Y2C4E6G8HM",
#     "seq": 42,
#     "schema_id": "invoance.audit/1",
#     "action": "user.signed_in",
#     "occurred_at": "2026-09-22T08:14:07.000Z",
#     "ingested_at": "2026-09-22T08:14:07.312Z",
#     "payload_hash": "df929158c7ce2107eff769fbcd58376c1d84dee0b5212b314f6f423dd20534d3",
#     "signature": "c6a2bf3bc3895915ead5f0b99eaf84534d4e8b9aa68bef8b0508cfd473f06e694d76d2bc330b83cfa613e49e7d2490d0413285017acfb78746bcc1524d05160d",
#     "signing_public_key": "bee215a9d2a0170176a88733056d8a0ae5372b0da8238796bef4a0b75d4c0974"
#   },
#   "verification": {
#     "valid": true,
#     "reason": null,
#     "schema": "invoance.audit/1",
#     "payload_hash": "df929158c7ce2107eff769fbcd58376c1d84dee0b5212b314f6f423dd20534d3",
#     "key_source": "tenant_keys"
#   }
# }

# Hold a copy of the event (from get, an export or a stream delivery)? Post it
# and the server rebuilds the canonical bytes from YOUR copy. Change one field
# in event.json and run it again: match_result and signature_valid turn false.
# That is the test no plaintext log can pass.
curl -X POST https://api.invoance.com/v1/proof/audit/aevt_01J0Y1Z2A3B4C5D6E7F8G9H0JK/verify \
  -H "Content-Type: application/json" \
  -d @event.json

• 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.

The hidden complexity: why this is a quarter, not a weekend

The version above is the honest core, and it is already more than a weekend. The reason audit logs quietly become a multi-month project is everything that surrounds that core once real customers depend on it.

You will need a query API with keyset pagination that stays fast as the table grows past tens of millions of rows, which means getting partition pruning and indexes right, not just adding a LIMIT. You will need access scoping so a read-only audit key cannot write events and a writer cannot read another tenant's. You will need filtering and search your customers actually use, exports they can hand to their own auditors, and, the moment you sell to a security team, the ability to stream events into their SIEM. Enterprise buyers increasingly want an embeddable audit-log viewer inside your product, which is a frontend project of its own. Underneath all of it sits key management: generating, encrypting, storing, and eventually rotating per-tenant signing keys without invalidating every signature you have ever produced, which is a genuinely hard problem most teams underestimate.

None of this is exotic. All of it is real engineering time, on a system where bugs are not cosmetic. They are integrity failures in the one place you promised integrity. That is the calculation to make honestly before committing a quarter of roadmap to it.

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.

import { InvoanceClient } from "invoance";

// Reads INVOANCE_API_KEY from the environment.
const client = new InvoanceClient();

// One call, right where the action happens. organizationId is the end-customer
// org you registered once with client.audit.orgs.create; the Idempotency-Key
// makes a retry a no-op that replays the first response, not a second row.
const occurredAt = new Date().toISOString();

const result = await client.audit.events.ingest({
  organizationId: "org_8472",
  action: "user.role.updated",
  occurredAt,
  actor: { type: "user", id: "u_42", name: "ada@acme.com" },
  targets: [{ type: "user", id: "u_77" }],
  metadata: { from_role: "member", to_role: "admin" },
  idempotencyKey: "role-update-u_77-" + occurredAt,
});
console.log(result.event_id, result.ingested_at);

• 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.