SaaS Audit Logging: Immutable Audit Trails, Event Sourcing, and SOC2/GDPR Compliance

Audit logs answer the question: "Who did what, when, and from where?" In a SaaS product, this matters for three overlapping reasons — security incident investigation, customer trust (enterprise buyers demand it), and regulatory compliance (SOC2, GDPR, HIPAA all have audit requirements). The mistake most teams make is treating audit logs as an afterthought — appending to a mutable table that anyone with database access can edit. A compliant audit trail requires tamper evidence, retention policies, and structured querying.

This post covers the full implementation: schema design, HMAC-based tamper detection, application-layer event capture, SOC2/GDPR compliance mapping, and alerting on anomalous activity.

What Goes in an Audit Log

Not everything is worth logging. Audit events fall into three categories:

Always log:

• Authentication events (login, logout, failed attempts, MFA changes)
• Authorization changes (role grants, permission changes, API key creation/revocation)
• Data exports (who downloaded what data, when)
• Billing events (plan changes, payment method updates)
• User/account management (invitations, deletions, seat changes)
• Configuration changes (SSO settings, webhook endpoints, integrations)

Log for sensitive products:

• Record-level access for PII (who viewed which customer record)
• Bulk operations (mass delete, bulk update)

Don't log:

• Read queries on non-sensitive data (it's noise and storage)
• Application debug events (that's application logging, not audit logging)
• Health checks and monitoring pings

---

Database Schema

-- Append-only audit log — no UPDATE or DELETE permissions on this table
CREATE TABLE audit_events (
  id              UUID        PRIMARY KEY DEFAULT gen_random_uuid(),
  sequence_num    BIGINT      NOT NULL GENERATED ALWAYS AS IDENTITY,
  
  -- Who
  actor_id        UUID,       -- NULL for system events
  actor_email     TEXT,       -- Denormalized: user may be deleted later
  actor_ip        INET,
  actor_user_agent TEXT,
  
  -- What
  event_type      TEXT        NOT NULL,     -- 'user.login', 'role.granted', etc.
  event_version   INTEGER     NOT NULL DEFAULT 1,
  resource_type   TEXT,                     -- 'User', 'Account', 'ApiKey', etc.
  resource_id     TEXT,                     -- ID of the affected resource
  
  -- Context
  tenant_id       UUID        NOT NULL,
  session_id      TEXT,
  request_id      TEXT,
  
  -- Payload
  before_state    JSONB,      -- Snapshot before the change (for mutations)
  after_state     JSONB,      -- Snapshot after the change
  metadata        JSONB       NOT NULL DEFAULT '{}',
  
  -- Tamper detection
  payload_hash    TEXT        NOT NULL,     -- HMAC-SHA256 of event content
  chain_hash      TEXT        NOT NULL,     -- HMAC of (payload_hash || prev_chain_hash)
  
  -- Timestamps
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  
  -- Retention
  retain_until    TIMESTAMPTZ,  -- NULL = retain forever; set for GDPR erasure
  
  CONSTRAINT audit_events_sequence_unique UNIQUE (sequence_num)
);

-- Partition by month for retention management
CREATE INDEX idx_audit_tenant_time  ON audit_events (tenant_id, occurred_at DESC);
CREATE INDEX idx_audit_actor        ON audit_events (actor_id, occurred_at DESC) WHERE actor_id IS NOT NULL;
CREATE INDEX idx_audit_resource     ON audit_events (resource_type, resource_id, occurred_at DESC);
CREATE INDEX idx_audit_event_type   ON audit_events (event_type, occurred_at DESC);

-- Revoke UPDATE and DELETE from application role
REVOKE UPDATE, DELETE ON audit_events FROM app_role;
-- Grant only INSERT and SELECT
GRANT INSERT, SELECT ON audit_events TO app_role;

-- Materialized view for fast compliance queries (refresh daily)
CREATE MATERIALIZED VIEW audit_event_summary AS
SELECT
  tenant_id,
  event_type,
  DATE_TRUNC('day', occurred_at) AS event_date,
  COUNT(*) AS event_count,
  COUNT(DISTINCT actor_id) AS unique_actors
FROM audit_events
GROUP BY tenant_id, event_type, DATE_TRUNC('day', occurred_at);

CREATE UNIQUE INDEX ON audit_event_summary (tenant_id, event_type, event_date);

---

HMAC Chain for Tamper Detection

The chain hash links each event to the previous one. If any event is modified or deleted, all subsequent chain hashes become invalid — making tampering detectable.

// src/lib/audit/chain.ts
import crypto from 'crypto';

const HMAC_SECRET = process.env.AUDIT_HMAC_SECRET!;
// Store this secret outside the database — HSM or AWS Secrets Manager

interface AuditEventContent {
  id: string;
  sequenceNum: number;
  tenantId: string;
  actorId?: string;
  eventType: string;
  resourceType?: string;
  resourceId?: string;
  beforeState?: unknown;
  afterState?: unknown;
  occurredAt: string;
}

export function computePayloadHash(event: AuditEventContent): string {
  const canonical = JSON.stringify({
    id: event.id,
    seq: event.sequenceNum,
    tenant: event.tenantId,
    actor: event.actorId ?? null,
    type: event.eventType,
    resource: `${event.resourceType ?? ''}:${event.resourceId ?? ''}`,
    before: event.beforeState ?? null,
    after: event.afterState ?? null,
    at: event.occurredAt,
  });

  return crypto
    .createHmac('sha256', HMAC_SECRET)
    .update(canonical)
    .digest('hex');
}

export function computeChainHash(
  payloadHash: string,
  prevChainHash: string
): string {
  return crypto
    .createHmac('sha256', HMAC_SECRET)
    .update(`${payloadHash}:${prevChainHash}`)
    .digest('hex');
}

// Genesis hash for the first event
export const GENESIS_HASH = crypto
  .createHmac('sha256', HMAC_SECRET)
  .update('GENESIS')
  .digest('hex');

// Verify integrity of a chain segment
export async function verifyChainIntegrity(
  tenantId: string,
  fromSequence: number,
  toSequence: number
): Promise<{ valid: boolean; firstInvalidSequence?: number }> {
  const events = await db.auditEvent.findMany({
    where: {
      tenantId,
      sequenceNum: { gte: fromSequence, lte: toSequence },
    },
    orderBy: { sequenceNum: 'asc' },
  });

  let prevChainHash =
    fromSequence === 1
      ? GENESIS_HASH
      : (await db.auditEvent.findFirst({
          where: { tenantId, sequenceNum: fromSequence - 1 },
          select: { chainHash: true },
        }))?.chainHash ?? GENESIS_HASH;

  for (const event of events) {
    const expectedPayloadHash = computePayloadHash({
      id: event.id,
      sequenceNum: event.sequenceNum,
      tenantId: event.tenantId,
      actorId: event.actorId ?? undefined,
      eventType: event.eventType,
      resourceType: event.resourceType ?? undefined,
      resourceId: event.resourceId ?? undefined,
      beforeState: event.beforeState,
      afterState: event.afterState,
      occurredAt: event.occurredAt.toISOString(),
    });

    const expectedChainHash = computeChainHash(expectedPayloadHash, prevChainHash);

    if (
      event.payloadHash !== expectedPayloadHash ||
      event.chainHash !== expectedChainHash
    ) {
      return { valid: false, firstInvalidSequence: event.sequenceNum };
    }

    prevChainHash = event.chainHash;
  }

  return { valid: true };
}

---

Audit Event Writer

// src/lib/audit/writer.ts
import { db } from '../db';
import { computePayloadHash, computeChainHash, GENESIS_HASH } from './chain';
import crypto from 'crypto';

export interface AuditEventInput {
  tenantId: string;
  actorId?: string;
  actorEmail?: string;
  actorIp?: string;
  actorUserAgent?: string;
  eventType: string;
  resourceType?: string;
  resourceId?: string;
  beforeState?: Record<string, unknown>;
  afterState?: Record<string, unknown>;
  sessionId?: string;
  requestId?: string;
  metadata?: Record<string, unknown>;
  retainUntil?: Date;
}

export async function writeAuditEvent(input: AuditEventInput): Promise<string> {
  const id = crypto.randomUUID();
  const occurredAt = new Date();

  // Get previous chain hash for this tenant (atomic via DB transaction)
  return db.$transaction(async (tx) => {
    const prevEvent = await tx.auditEvent.findFirst({
      where: { tenantId: input.tenantId },
      orderBy: { sequenceNum: 'desc' },
      select: { chainHash: true, sequenceNum: true },
    });

    const prevChainHash = prevEvent?.chainHash ?? GENESIS_HASH;
    // sequenceNum is GENERATED ALWAYS AS IDENTITY — we don't set it manually

    const payloadHash = computePayloadHash({
      id,
      sequenceNum: (prevEvent?.sequenceNum ?? 0) + 1, // Estimate for hash (actual is DB-generated)
      tenantId: input.tenantId,
      actorId: input.actorId,
      eventType: input.eventType,
      resourceType: input.resourceType,
      resourceId: input.resourceId,
      beforeState: input.beforeState,
      afterState: input.afterState,
      occurredAt: occurredAt.toISOString(),
    });

    const chainHash = computeChainHash(payloadHash, prevChainHash);

    await tx.auditEvent.create({
      data: {
        id,
        tenantId: input.tenantId,
        actorId: input.actorId,
        actorEmail: input.actorEmail,
        actorIp: input.actorIp,
        actorUserAgent: input.actorUserAgent,
        eventType: input.eventType,
        resourceType: input.resourceType,
        resourceId: input.resourceId,
        beforeState: input.beforeState ?? undefined,
        afterState: input.afterState ?? undefined,
        sessionId: input.sessionId,
        requestId: input.requestId,
        metadata: input.metadata ?? {},
        payloadHash,
        chainHash,
        occurredAt,
        retainUntil: input.retainUntil,
      },
    });

    return id;
  });
}

---

Application Integration

Middleware: Auto-Capture Auth Events

// src/middleware/audit.middleware.ts
import { NextRequest, NextResponse } from 'next/server';
import { writeAuditEvent } from '../lib/audit/writer';
import { getServerSession } from 'next-auth';

// Map of route patterns to event types for automatic capture
const AUDITED_ROUTES: Array<{
  method: string;
  pattern: RegExp;
  eventType: string;
  resourceType?: string;
}> = [
  { method: 'POST', pattern: /^\/api\/auth\/login/, eventType: 'user.login' },
  { method: 'POST', pattern: /^\/api\/auth\/logout/, eventType: 'user.logout' },
  { method: 'DELETE', pattern: /^\/api\/users\/([^/]+)/, eventType: 'user.deleted', resourceType: 'User' },
  { method: 'POST', pattern: /^\/api\/invitations/, eventType: 'user.invited', resourceType: 'Invitation' },
  { method: 'POST', pattern: /^\/api\/api-keys/, eventType: 'api_key.created', resourceType: 'ApiKey' },
  { method: 'DELETE', pattern: /^\/api\/api-keys\/([^/]+)/, eventType: 'api_key.revoked', resourceType: 'ApiKey' },
];

export async function auditMiddleware(req: NextRequest): Promise<void> {
  const route = AUDITED_ROUTES.find(
    (r) => r.method === req.method && r.pattern.test(req.nextUrl.pathname)
  );

  if (!route) return;

  const session = await getServerSession();
  const match = req.nextUrl.pathname.match(route.pattern);
  const resourceId = match?.[1];

  // Fire-and-forget (don't block the request)
  writeAuditEvent({
    tenantId: session?.user?.tenantId ?? 'system',
    actorId: session?.user?.id,
    actorEmail: session?.user?.email ?? undefined,
    actorIp: req.headers.get('x-forwarded-for')?.split(',')[0].trim() ?? req.ip,
    actorUserAgent: req.headers.get('user-agent') ?? undefined,
    eventType: route.eventType,
    resourceType: route.resourceType,
    resourceId,
    sessionId: session?.user?.sessionId,
    requestId: req.headers.get('x-request-id') ?? undefined,
  }).catch((err) => console.error('Audit write failed:', err));
}

Service Layer: Explicit Audit with Before/After State

// src/services/role.service.ts
import { writeAuditEvent } from '../lib/audit/writer';
import { db } from '../lib/db';

export async function grantRole(
  tenantId: string,
  actorId: string,
  targetUserId: string,
  role: string
): Promise<void> {
  const before = await db.userRole.findFirst({
    where: { userId: targetUserId, tenantId },
    select: { role: true },
  });

  await db.userRole.upsert({
    where: { userId_tenantId: { userId: targetUserId, tenantId } },
    update: { role },
    create: { userId: targetUserId, tenantId, role },
  });

  await writeAuditEvent({
    tenantId,
    actorId,
    eventType: 'role.granted',
    resourceType: 'User',
    resourceId: targetUserId,
    beforeState: before ? { role: before.role } : null,
    afterState: { role },
    metadata: { targetUserId },
  });
}

---

SOC2 and GDPR Compliance Mapping

SOC2 CC7.2 — System Monitoring

-- SOC2 CC7.2: Detect and respond to security events
-- Query: Failed login attempts > 5 in 10 minutes (brute force detection)
SELECT
  actor_email,
  actor_ip,
  COUNT(*) AS failed_attempts,
  MIN(occurred_at) AS first_attempt,
  MAX(occurred_at) AS last_attempt
FROM audit_events
WHERE event_type = 'user.login.failed'
  AND occurred_at >= NOW() - INTERVAL '10 minutes'
GROUP BY actor_email, actor_ip
HAVING COUNT(*) > 5
ORDER BY failed_attempts DESC;

-- SOC2 CC7.2: Privilege escalation
SELECT
  a.actor_email,
  a.resource_id AS target_user_id,
  a.before_state->>'role' AS old_role,
  a.after_state->>'role' AS new_role,
  a.occurred_at
FROM audit_events a
WHERE a.event_type = 'role.granted'
  AND a.after_state->>'role' = 'admin'
  AND a.occurred_at >= NOW() - INTERVAL '7 days'
ORDER BY a.occurred_at DESC;

GDPR Article 30 — Records of Processing Activities

// src/lib/audit/gdpr.ts — generate Article 30 report
export async function generateArticle30Report(
  tenantId: string,
  fromDate: Date,
  toDate: Date
): Promise<Article30Report> {
  const [dataExports, userDeletions, accessEvents] = await Promise.all([
    db.auditEvent.findMany({
      where: {
        tenantId,
        eventType: { in: ['data.exported', 'report.downloaded', 'api.data.accessed'] },
        occurredAt: { gte: fromDate, lte: toDate },
      },
      select: { actorEmail: true, eventType: true, metadata: true, occurredAt: true },
    }),
    db.auditEvent.findMany({
      where: {
        tenantId,
        eventType: 'user.deleted',
        occurredAt: { gte: fromDate, lte: toDate },
      },
    }),
    db.auditEvent.count({
      where: {
        tenantId,
        eventType: { startsWith: 'pii.' },
        occurredAt: { gte: fromDate, lte: toDate },
      },
    }),
  ]);

  return {
    tenantId,
    reportPeriod: { from: fromDate, to: toDate },
    dataExports: dataExports.length,
    userDeletions: userDeletions.length,
    piiAccessEvents: accessEvents,
    generatedAt: new Date(),
  };
}

// GDPR Right to Erasure: anonymize actor references in audit log
// (We keep the event for integrity but remove PII)
export async function anonymizeActorInAuditLog(userId: string): Promise<void> {
  // Can't DELETE audit events — instead we overwrite PII fields
  // This requires a special elevated role that has UPDATE permission only on these fields
  await db.$executeRaw`
    UPDATE audit_events
    SET
      actor_email = '[deleted]',
      actor_ip = NULL,
      actor_user_agent = NULL
    WHERE actor_id = ${userId}
  `;

  // Note: actor_id (UUID) is kept for chain integrity — it's pseudonymous
}

---

Real-Time Alerting on Anomalous Activity

// src/lib/audit/alerting.ts
import { writeAuditEvent } from './writer';

interface AlertRule {
  id: string;
  name: string;
  query: () => Promise<Array<Record<string, unknown>>>;
  severity: 'low' | 'medium' | 'high' | 'critical';
}

const ALERT_RULES: AlertRule[] = [
  {
    id: 'brute-force-login',
    name: 'Brute force login attempt',
    severity: 'high',
    query: () => db.$queryRaw`
      SELECT actor_email, actor_ip, COUNT(*) as attempts
      FROM audit_events
      WHERE event_type = 'user.login.failed'
        AND occurred_at >= NOW() - INTERVAL '10 minutes'
      GROUP BY actor_email, actor_ip
      HAVING COUNT(*) > 5
    `,
  },
  {
    id: 'bulk-data-export',
    name: 'Unusual bulk data export',
    severity: 'critical',
    query: () => db.$queryRaw`
      SELECT actor_email, COUNT(*) as export_count
      FROM audit_events
      WHERE event_type IN ('data.exported', 'report.downloaded')
        AND occurred_at >= NOW() - INTERVAL '1 hour'
      GROUP BY actor_email
      HAVING COUNT(*) > 20
    `,
  },
  {
    id: 'admin-role-grant',
    name: 'Admin role granted outside business hours',
    severity: 'high',
    query: () => db.$queryRaw`
      SELECT *
      FROM audit_events
      WHERE event_type = 'role.granted'
        AND after_state->>'role' = 'admin'
        AND EXTRACT(HOUR FROM occurred_at AT TIME ZONE 'UTC') NOT BETWEEN 8 AND 18
        AND occurred_at >= NOW() - INTERVAL '15 minutes'
    `,
  },
];

export async function runAlertRules(): Promise<void> {
  for (const rule of ALERT_RULES) {
    const matches = await rule.query();
    
    if (matches.length > 0) {
      console.warn(`🚨 Alert: ${rule.name}`, { severity: rule.severity, matches });
      
      // Send to PagerDuty / OpsGenie / Slack
      await sendSecurityAlert({
        ruleId: rule.id,
        name: rule.name,
        severity: rule.severity,
        matches,
      });
    }
  }
}

// Run alert rules every 5 minutes via cron
// 0,5,10,15,20,25,30,35,40,45,50,55 * * * * node dist/jobs/audit-alerts.js

---

Retention Policy

-- Automated retention: delete events past retain_until date
-- Run nightly
DELETE FROM audit_events
WHERE retain_until IS NOT NULL
  AND retain_until < NOW()
  AND event_type NOT IN (
    -- Never auto-delete compliance-critical events
    'user.login', 'role.granted', 'data.exported', 'user.deleted'
  );

-- Default retention by event category:
-- Auth events: 2 years (SOC2 requirement)
-- Data exports: 3 years
-- Billing: 7 years (tax/accounting)
-- General: 90 days

---

Cost Reference

---

See Also

• SaaS GDPR Engineering: Right to Erasure and Data Portability
• SaaS Security Checklist: Authentication, Authorization, and Data Protection
• Security Incident Response: Playbooks and PostMortem Process
• Event-Driven Microservices with Kafka
• PostgreSQL JSONB Patterns for Semi-Structured Data

---

Working With Viprasol

Building a SaaS product that needs SOC2 Type II or GDPR compliance? Audit logging infrastructure is one of the first things auditors check. We design and implement tamper-evident audit trails, automated alert rules, and compliance reporting that satisfies both internal security teams and external auditors.

Talk to our team → | Explore our SaaS engineering services →