Designing an Audit Log System: Immutable Events, Efficient Querying, and Compliance at Scale

Most teams add audit logging reactively: a compliance review flags the gap, or a security incident reveals that you have no record of who changed what. By the time you are retrofitting it, you are working around an existing schema, an existing permission model, and existing client code that was never designed with observability in mind.

The result is usually a audit_logs table with a jsonb column, a created_at timestamp, and a user_id. It looks like an audit log. It is not one. It cannot answer the questions auditors actually ask, it cannot be queried efficiently at scale, and it provides no protection against tampering.

This article covers what a production audit log system actually requires: schema design that supports real queries, append-only guarantees, tamper-evidence for compliance, retention policies that do not kill your storage budget, and the specific requirements of SOC 2 and HIPAA.

---

What an Audit Log Is Actually For

An audit log serves three distinct purposes, and conflating them leads to design mistakes.

The first is operational debugging. Who changed this record? What was the previous value? Which API call triggered this state transition? This is the most frequent use case and the one that shapes your query patterns.

The second is security investigation. Did a user access records they should not have accessed? Was there a privilege escalation? Did someone export bulk data? This requires coverage of read events, not just writes.

The third is compliance evidence. Demonstrating to an auditor that your system enforces access controls, retains records for the required period, and protects against tampering. This is where the structural requirements get specific.

All three require the same underlying foundation: an append-only, tamper-evident, queryable record of events.

---

Schema Design

The schema is where most implementations fail. The common mistake is a generic catch-all table:

-- What most teams build (and regret)
CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  action VARCHAR(255),
  resource_type VARCHAR(255),
  resource_id VARCHAR(255),
  data JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

This seems flexible. In practice it is un-queryable at scale. The data column forces full-document scans for anything specific, action becomes an inconsistent string field that different engineers populate differently, and there is no structure for before/after state comparison.

A better schema enforces structure at the data layer:

CREATE TABLE audit_events (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  -- When
  occurred_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  -- Who
  actor_type    TEXT NOT NULL CHECK (actor_type IN ('user', 'service', 'system')),
  actor_id      TEXT NOT NULL,
  actor_email   TEXT,
  -- What
  action        TEXT NOT NULL,          -- 'record.created', 'record.updated', 'record.deleted', 'record.viewed'
  -- On what
  resource_type TEXT NOT NULL,          -- 'patient', 'invoice', 'user', 'api_key'
  resource_id   TEXT NOT NULL,
  -- Change detail
  before        JSONB,                  -- NULL for creates and reads
  after         JSONB,                  -- NULL for deletes and reads
  changed_fields TEXT[],               -- subset of changed keys for fast filtering
  -- Context
  tenant_id     UUID NOT NULL,
  request_id    TEXT,                   -- correlate with application logs
  ip_address    INET,
  user_agent    TEXT,
  -- Tamper evidence
  prev_hash     TEXT,                   -- hash of the previous row in the chain
  row_hash      TEXT NOT NULL,          -- hash of this row's content
  -- Retention
  retention_class TEXT NOT NULL DEFAULT 'standard'
);

The before and after columns answer the question auditors always ask first: what changed? Storing both states avoids the need to reconstruct history by replaying events. changed_fields is a denormalized array of field names that changed, enabling index-supported queries like “show me all events where the status field changed.”

The prev_hash and row_hash columns implement a hash chain for tamper evidence, covered below.

---

Inserting Events

Every audit event must be written atomically with the operation it records. The most important implementation constraint is that audit events cannot be optional callbacks that run after the main operation completes.

interface AuditEvent {
  actorType: "user" | "service" | "system";
  actorId: string;
  actorEmail?: string;
  action: string;
  resourceType: string;
  resourceId: string;
  before?: Record<string, unknown>;
  after?: Record<string, unknown>;
  changedFields?: string[];
  tenantId: string;
  requestId?: string;
  ipAddress?: string;
  userAgent?: string;
}

async function recordAuditEvent(
  tx: DatabaseTransaction,
  event: AuditEvent
): Promise<void> {
  // Get the hash of the most recent event for this tenant to chain to
  const prevRow = await tx.queryOne<{ row_hash: string } | null>(
    `SELECT row_hash
     FROM audit_events
     WHERE tenant_id = $1
     ORDER BY occurred_at DESC, id DESC
     LIMIT 1
     FOR UPDATE`,
    [event.tenantId]
  );

  const prevHash = prevRow?.row_hash ?? "genesis";

  // Compute a hash over the event content plus the previous hash
  const rowContent = JSON.stringify({
    actorType: event.actorType,
    actorId: event.actorId,
    action: event.action,
    resourceType: event.resourceType,
    resourceId: event.resourceId,
    before: event.before ?? null,
    after: event.after ?? null,
    tenantId: event.tenantId,
    prevHash,
  });

  const rowHash = await sha256(rowContent);

  await tx.execute(
    `INSERT INTO audit_events (
      actor_type, actor_id, actor_email,
      action, resource_type, resource_id,
      before, after, changed_fields,
      tenant_id, request_id, ip_address, user_agent,
      prev_hash, row_hash
    ) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15)`,
    [
      event.actorType, event.actorId, event.actorEmail ?? null,
      event.action, event.resourceType, event.resourceId,
      event.before ? JSON.stringify(event.before) : null,
      event.after ? JSON.stringify(event.after) : null,
      event.changedFields ?? null,
      event.tenantId, event.requestId ?? null,
      event.ipAddress ?? null, event.userAgent ?? null,
      prevHash, rowHash,
    ]
  );
}

Call recordAuditEvent inside the same database transaction as the operation being audited. If the main operation rolls back, the audit event rolls back with it. If you write the audit event after committing the main transaction, you will silently drop events on process crash.

async function updateRecord(
  db: DatabaseClient,
  params: {
    recordId: string;
    tenantId: string;
    actorId: string;
    updates: Partial<PatientRecord>;
    requestContext: RequestContext;
  }
): Promise<void> {
  await db.transaction(async (tx) => {
    const before = await tx.queryOne<PatientRecord>(
      "SELECT * FROM patient_records WHERE id = $1 AND tenant_id = $2 FOR UPDATE",
      [params.recordId, params.tenantId]
    );

    if (!before) {
      throw new Error("Record not found");
    }

    const changedFields = (Object.keys(params.updates) as (keyof PatientRecord)[])
      .filter(k => before[k] !== params.updates[k]);

    await tx.execute(
      "UPDATE patient_records SET updated_at = NOW(), ... WHERE id = $1",
      [params.recordId]
    );

    const after = { ...before, ...params.updates };

    await recordAuditEvent(tx, {
      actorType: "user",
      actorId: params.actorId,
      action: "record.updated",
      resourceType: "patient_record",
      resourceId: params.recordId,
      before,
      after,
      changedFields,
      tenantId: params.tenantId,
      requestId: params.requestContext.requestId,
      ipAddress: params.requestContext.ipAddress,
    });
  });
}

---

Efficient Querying

Audit logs are write-heavy and read-seldom, but when you need to read them, you need specific queries to perform in milliseconds rather than minutes.

The indexes that matter most:

-- Tenant isolation is always the first filter
CREATE INDEX idx_audit_events_tenant_occurred
  ON audit_events (tenant_id, occurred_at DESC);

-- Entity history: "show me all events for this patient record"
CREATE INDEX idx_audit_events_resource
  ON audit_events (tenant_id, resource_type, resource_id, occurred_at DESC);

-- Actor history: "show me everything this user did"
CREATE INDEX idx_audit_events_actor
  ON audit_events (tenant_id, actor_id, occurred_at DESC);

-- Field-level filtering: "show me all events where status changed"
CREATE INDEX idx_audit_events_changed_fields
  ON audit_events USING GIN (changed_fields);

-- Action filtering: "show me all deletions"
CREATE INDEX idx_audit_events_action
  ON audit_events (tenant_id, action, occurred_at DESC);

A typical audit query in TypeScript:

interface AuditQueryParams {
  tenantId: string;
  resourceType?: string;
  resourceId?: string;
  actorId?: string;
  action?: string;
  changedField?: string;
  from: Date;
  to: Date;
  limit?: number;
  cursor?: string; // occurred_at::text || ':' || id for keyset pagination
}

async function queryAuditEvents(
  db: DatabaseClient,
  params: AuditQueryParams
): Promise<{ events: AuditEvent[]; nextCursor: string | null }> {
  const conditions: string[] = ["tenant_id = $1", "occurred_at BETWEEN $2 AND $3"];
  const values: unknown[] = [params.tenantId, params.from, params.to];
  let paramIndex = 4;

  if (params.resourceType) {
    conditions.push(`resource_type = $${paramIndex++}`);
    values.push(params.resourceType);
  }

  if (params.resourceId) {
    conditions.push(`resource_id = $${paramIndex++}`);
    values.push(params.resourceId);
  }

  if (params.actorId) {
    conditions.push(`actor_id = $${paramIndex++}`);
    values.push(params.actorId);
  }

  if (params.action) {
    conditions.push(`action = $${paramIndex++}`);
    values.push(params.action);
  }

  if (params.changedField) {
    conditions.push(`$${paramIndex++} = ANY(changed_fields)`);
    values.push(params.changedField);
  }

  if (params.cursor) {
    // Keyset pagination: more efficient than OFFSET at large page numbers
    const [cursorTime, cursorId] = params.cursor.split(":");
    conditions.push(
      `(occurred_at, id) < ($${paramIndex++}::timestamptz, $${paramIndex++}::uuid)`
    );
    values.push(cursorTime, cursorId);
  }

  const limit = params.limit ?? 50;
  const query = `
    SELECT * FROM audit_events
    WHERE ${conditions.join(" AND ")}
    ORDER BY occurred_at DESC, id DESC
    LIMIT ${limit + 1}
  `;

  const rows = await db.query<AuditEvent>(query, values);

  const hasMore = rows.length > limit;
  const events = hasMore ? rows.slice(0, limit) : rows;
  const lastEvent = events[events.length - 1];
  const nextCursor = hasMore && lastEvent
    ? `${lastEvent.occurredAt.toISOString()}:${lastEvent.id}`
    : null;

  return { events, nextCursor };
}

Use keyset pagination rather than OFFSET. At page 500 of an audit log, OFFSET causes a full scan of 25,000 rows before returning 50. Keyset pagination uses the index regardless of how deep into the result set you are.

---

Tamper Evidence

For SOC 2 and HIPAA, you need to be able to demonstrate that audit records have not been altered after the fact. The hash chain approach above provides this: each row’s hash is computed over its content plus the hash of the previous row, forming a linked chain where modifying any historical row invalidates all subsequent hashes.

Verifying the chain is a background job:

async function verifyAuditChain(
  db: DatabaseClient,
  tenantId: string,
  from: Date,
  to: Date
): Promise<{ valid: boolean; firstViolation: string | null }> {
  const events = await db.query<{
    id: string;
    prevHash: string;
    rowHash: string;
    actorType: string;
    actorId: string;
    action: string;
    resourceType: string;
    resourceId: string;
    before: unknown;
    after: unknown;
  }>(
    `SELECT id, prev_hash, row_hash, actor_type, actor_id, action,
            resource_type, resource_id, before, after
     FROM audit_events
     WHERE tenant_id = $1 AND occurred_at BETWEEN $2 AND $3
     ORDER BY occurred_at ASC, id ASC`,
    [tenantId, from, to]
  );

  let expectedPrevHash = "genesis";

  for (const event of events) {
    if (event.prevHash !== expectedPrevHash) {
      return { valid: false, firstViolation: event.id };
    }

    const recomputedContent = JSON.stringify({
      actorType: event.actorType,
      actorId: event.actorId,
      action: event.action,
      resourceType: event.resourceType,
      resourceId: event.resourceId,
      before: event.before,
      after: event.after,
      tenantId,
      prevHash: event.prevHash,
    });

    const recomputedHash = await sha256(recomputedContent);

    if (recomputedHash !== event.rowHash) {
      return { valid: false, firstViolation: event.id };
    }

    expectedPrevHash = event.rowHash;
  }

  return { valid: true, firstViolation: null };
}

Run this verification daily and alert on any failure. A violation does not necessarily mean malicious tampering. It can result from a bug, a database restore from a different point in time, or a migration that modified rows. All of these require investigation.

For stronger tamper evidence, periodically write root hashes of audit segments to an external, append-only store (an object storage bucket with versioning and object lock enabled, for example). This makes it infeasible to alter both your database and the external record simultaneously.

---

Architecture Tradeoffs

Decision	Option A	Option B	When it matters
Storage backend	Main application Postgres	Separate audit Postgres	High write volume justifies isolation at 100K+ events/day
Schema approach	Typed columns + JSONB for detail	Pure JSONB	Typed columns enable index-supported queries; pure JSONB trades flexibility for query performance
Read events	Log all reads	Log reads only for sensitive resources	HIPAA requires PHI read logging; logging all reads in high-traffic systems can 10x event volume
Retention tiers	Single retention period	Tiered by sensitivity class	Compliance often requires different retention for different data classes
Hash chain scope	Per-tenant chain	Global chain	Per-tenant is simpler and allows parallel writes; global chain requires a serialization bottleneck
Write path	Synchronous within transaction	Async via outbox	Sync is correct-by-default; async reduces write latency but introduces delivery lag

---

Retention Policies

Retention is where operational costs and compliance requirements collide. SOC 2 does not mandate a specific retention period. Common control frameworks suggest one to seven years depending on the sensitivity class. HIPAA requires six years from the date of creation or last use.

A tiered retention model:

type RetentionClass =
  | "standard"      // 1 year: routine operational events
  | "security"      // 3 years: login, permission changes, access denials
  | "compliance"    // 7 years: PHI access, financial operations, consent changes
  | "legal_hold";   // indefinite: events flagged by legal team

const RETENTION_DAYS: Record<RetentionClass, number | null> = {
  standard:    365,
  security:    1095,
  compliance:  2555,
  legal_hold:  null, // never delete
};

async function classifyAuditEvent(action: string, resourceType: string): Promise<RetentionClass> {
  if (["user.login", "user.login_failed", "api_key.created", "permission.granted"].includes(action)) {
    return "security";
  }
  if (resourceType === "patient_record" || action.startsWith("consent.") || action.startsWith("payment.")) {
    return "compliance";
  }
  return "standard";
}

async function purgeExpiredEvents(db: DatabaseClient): Promise<number> {
  const results = await Promise.all(
    (Object.entries(RETENTION_DAYS) as [RetentionClass, number | null][])
      .filter(([, days]) => days !== null)
      .map(([retentionClass, days]) =>
        db.execute(
          `DELETE FROM audit_events
           WHERE retention_class = $1
           AND occurred_at < NOW() - INTERVAL '${days} days'
           AND retention_class != 'legal_hold'`,
          [retentionClass]
        )
      )
  );

  return results.reduce((sum, r) => sum + r.rowsAffected, 0);
}

Run the purge job on a schedule. Before deleting, consider archiving compliance-class events to cold storage (S3 Glacier or equivalent) rather than discarding them. Some regulatory contexts require records to be recoverable even if not immediately queryable.

---

What SOC 2 and HIPAA Actually Require

SOC 2 (Security, Availability, and Confidentiality criteria) requires evidence of:

• Logical access controls (who has access to what, and when was it granted or revoked)
• Monitoring of privileged access (admin actions are logged and reviewed)
• Detection of unauthorized access attempts
• Retention of logs sufficient to investigate security incidents

Your audit log needs to capture: user login and logout, permission changes, admin operations, and access to sensitive configuration. The auditor will ask for a sample of events and verify that the system can produce them.

HIPAA (45 CFR §164.312(b)) requires audit controls that record and examine access to electronic PHI. Specifically:

• All access to PHI, not just modifications (read events matter)
• The identity of the person accessing the record
• The date and time of access
• The action performed

HIPAA does not specify technical implementation, but it does require that logs are retained for six years and are reviewed. The review requirement is often satisfied by automated alerting on anomalous patterns (bulk exports, off-hours access, access from new IP ranges) rather than manual log review.

Both frameworks require that audit logs cannot be altered by the users or services they are auditing. Your application database user should not have UPDATE or DELETE privileges on the audit_events table. Use a separate database user for audit writes.

---

Production Considerations

Preventing Application Users from Modifying Audit Records

Grant your application role only INSERT and SELECT on audit_events. Revoke UPDATE and DELETE. Create a separate maintenance role for the purge job that can only delete rows older than the retention threshold.

-- Application role: insert and read only
GRANT INSERT, SELECT ON audit_events TO app_role;
REVOKE UPDATE, DELETE ON audit_events FROM app_role;

-- Maintenance role: can only delete expired rows (enforced by RLS)
CREATE POLICY audit_delete_policy ON audit_events
  FOR DELETE TO maintenance_role
  USING (
    retention_class = 'standard'   AND occurred_at < NOW() - INTERVAL '365 days' OR
    retention_class = 'security'   AND occurred_at < NOW() - INTERVAL '1095 days' OR
    retention_class = 'compliance' AND occurred_at < NOW() - INTERVAL '2555 days'
  );

Partitioning for Scale

At high event volumes, a single audit_events table becomes a performance problem for the purge job. Range partitioning by month makes it possible to drop old partitions in milliseconds rather than deleting row by row:

CREATE TABLE audit_events (
  -- same schema as above
) PARTITION BY RANGE (occurred_at);

CREATE TABLE audit_events_2026_03
  PARTITION OF audit_events
  FOR VALUES FROM ('2026-03-01') TO ('2026-04-01');

Dropping an old partition is DROP TABLE audit_events_2024_01, which completes in milliseconds regardless of row count.

Separate Storage for High-Volume Systems

If audit events are being written at a rate that measurably impacts your application database’s write throughput, move them to a dedicated Postgres instance. The audit database has different operational characteristics: write-heavy, read-seldom, never joins with application tables. Isolating it prevents audit write spikes from affecting query latency on your main tables.

The write path changes slightly: audit events are written to the audit database rather than the application transaction. This means audit writes are no longer atomic with the operation they record. Compensate with the outbox pattern: write a pending audit event to the application database inside the main transaction, then a background worker delivers it to the audit database and marks it as delivered. This preserves atomicity at the cost of a small delivery lag.

---

Closing

An audit log that cannot answer “who changed this, when, and from what value to what value” is not an audit log. It is a timestamp table. The schema decisions made at the start determine whether you can answer those questions efficiently at scale or whether you will be running full-table scans under the pressure of a compliance review.

Build the write path atomically, enforce immutability at the database privilege layer, add the indexes that match your actual query patterns, and implement tamper verification as a routine background job rather than something you run only when asked. The storage cost is modest. The operational confidence is not.

When a security incident or compliance audit arrives, you want to already know the answer to “did we log that?” The time to find out is not during the review.