The Complete Guide to Audit Log Schema Design

1. Why audit log schema matters

Every B2B SaaS app eventually needs audit logs. An enterprise buyer asks "can we see who did what?" and suddenly you're designing a schema from scratch under deadline pressure.

The schema you choose determines everything downstream: whether your SOC 2 auditor is satisfied, whether your search queries are fast, and whether your customers can actually use the logs you provide.

Get it wrong and you'll be migrating data, re-indexing tables, and apologizing to auditors. Get it right and audit logging becomes a competitive advantage — the feature that closes enterprise deals.

2. The anatomy of an audit event

An audit event answers five questions:

1. Who performed the action? (the actor)
2. What did they do? (the action)
3. What was affected? (the resource/target)
4. When did it happen? (the timestamp)
5. What was the result? (success or failure)

This is the "5W" model. Every field in your schema should map to one of these questions. If a field doesn't answer one of these, it belongs in metadata — not at the top level.

3. Required fields (the non-negotiables)

These fields must be present on every single audit event. If any of these are missing, your audit trail has gaps that will concern auditors.

4. Recommended fields (what auditors love)

These fields aren't strictly required but dramatically improve your audit trail's usefulness for security investigations and compliance reviews.

5. The complete schema

Here's a TypeScript interface that combines the required and recommended fields into a production-ready audit event schema:

interface AuditEvent {
  // Required fields
  event_id: string;        // UUIDv7 — time-ordered unique ID
  timestamp: string;       // ISO 8601 in UTC
  actor: {
    id: string;            // Internal user ID
    email: string;         // Human-readable identifier
    type?: string;         // "user" | "api_key" | "system"
  };
  action: string;          // "resource.verb" format
  resource: {
    type: string;          // "document", "user", "project"
    id: string;            // Specific instance ID
    name?: string;         // Human-readable name (optional)
  };
  outcome: "SUCCESS" | "FAILURE" | "DENIED";

  // Recommended fields
  tenant_id: string;       // Multi-tenant isolation
  source_ip?: string;      // Client IP address
  user_agent?: string;     // Browser/client info
  request_id?: string;     // Correlation ID
  metadata?: Record<string, unknown>;  // Action-specific context
  previous_hash?: string;  // SHA-256 hash chain link
}

And here's how you'd log an event using the Trailbase SDK:

import { TrailbaseClient } from '@trailbase/sdk';

const trailbase = new TrailbaseClient({
  apiKey: process.env.TRAILBASE_API_KEY,
});

await trailbase.log({
  action: 'document.delete',
  actor: { id: 'user_123', email: 'alice@company.com' },
  resource: { type: 'document', id: 'doc_456' },
  outcome: 'SUCCESS',
  metadata: {
    filename: 'quarterly-report.pdf',
    size_bytes: 2_450_000,
    deleted_by_admin: false,
  },
});

6. Tamper-proofing with hash chains

A SHA-256 hash chain links each event to the previous one. If any event is modified or deleted, the chain breaks — providing cryptographic evidence of tampering.

Event 1: hash = SHA-256(event_data)
Event 2: hash = SHA-256(event_data + event_1_hash)
Event 3: hash = SHA-256(event_data + event_2_hash)
...

// To verify integrity:
// Recompute each hash and compare.
// If any hash doesn't match → tampering detected.

This is the same principle behind blockchain, but without the consensus overhead. Trailbase computes hash chains automatically — every event includes a previous_hash field that you can independently verify.

For a deeper dive, see our post: SHA-256 Hash Chains Explained.

7. Indexing for query performance

Audit logs are write-heavy but read-critical when an incident occurs. Your indexing strategy should optimize for the most common query patterns:

Always include tenant_id as the first column in every index. This ensures query isolation in multi-tenant architectures and prevents full table scans.

8. Common mistakes

After reviewing hundreds of audit log implementations, here are the most common mistakes we see:

9. Audit logs vs application logs

10. Retention strategies

Different compliance frameworks mandate different retention periods:

Use tiered storage to manage costs: hot storage (fast queries, recent data), warm storage (slower queries, older data), and cold storage (archival, compliance-only access). Trailbase handles this automatically with configurable retention tiers.

FAQ