SaaS Audit Trail in 2026: Append-Only Event Log, Immutable Records, and Compliance Export

An audit trail records who did what, to which resource, and when — forever, in a form that cannot be tampered with after the fact. It's required for SOC 2 Type II, GDPR compliance (demonstrating lawful processing), and enterprise customer procurement. Without it, you lose deals and fail audits.

This post builds the complete audit trail: append-only PostgreSQL schema with immutability enforcement, actor/resource/action event model, efficient querying for the admin UI, compliance CSV/JSON export, and retention policies.

---

The Event Model

Every audit event answers six questions:

WHO:      actor_type (user/system/api_key) + actor_id
DID WHAT: action (member.invited, subscription.upgraded, data.exported)
TO WHAT:  resource_type (member, subscription, project) + resource_id
WHEN:     occurred_at (immutable timestamp)
WHERE:    ip_address, user_agent
CONTEXT:  workspace_id, before/after state diff

---

Database Schema

-- Append-only audit log — rows are never updated or deleted (by application code)
CREATE TABLE audit_events (
  id              UUID NOT NULL DEFAULT gen_random_uuid(),
  occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),

  -- Workspace scope
  workspace_id    UUID NOT NULL,

  -- Actor (who performed the action)
  actor_type      TEXT NOT NULL CHECK (actor_type IN ('user', 'system', 'api_key', 'support')),
  actor_id        TEXT NOT NULL,  -- userId, 'system', apiKeyId, supportAgentId
  actor_email     TEXT,           -- Denormalized: actor's email at time of event
  actor_name      TEXT,           -- Denormalized: actor's name at time of event

  -- Action
  action          TEXT NOT NULL,  -- e.g. 'member.invited', 'subscription.upgraded'
  category        TEXT NOT NULL,  -- e.g. 'membership', 'billing', 'settings', 'data'

  -- Resource (what was acted upon)
  resource_type   TEXT,           -- e.g. 'member', 'project', 'invoice'
  resource_id     TEXT,           -- ID of the resource
  resource_name   TEXT,           -- Human-readable name at time of event

  -- Metadata
  ip_address      INET,
  user_agent      TEXT,
  request_id      TEXT,           -- Correlate with application logs

  -- State change (what changed)
  before_state    JSONB,          -- Sanitized snapshot before change
  after_state     JSONB,          -- Sanitized snapshot after change
  metadata        JSONB,          -- Any additional context

  -- Immutability enforcement (see below)
  PRIMARY KEY (workspace_id, occurred_at, id)  -- Partition-friendly PK
) PARTITION BY RANGE (occurred_at);

-- Monthly partitions (pre-create for current + next 3 months, automate creation)
CREATE TABLE audit_events_2027_02 PARTITION OF audit_events
  FOR VALUES FROM ('2027-02-01') TO ('2027-03-01');
CREATE TABLE audit_events_2027_03 PARTITION OF audit_events
  FOR VALUES FROM ('2027-03-01') TO ('2027-04-01');
CREATE TABLE audit_events_2027_04 PARTITION OF audit_events
  FOR VALUES FROM ('2027-04-01') TO ('2027-05-01');

-- Indexes for common query patterns
CREATE INDEX idx_audit_workspace_time
  ON audit_events(workspace_id, occurred_at DESC);

CREATE INDEX idx_audit_actor
  ON audit_events(workspace_id, actor_id, occurred_at DESC);

CREATE INDEX idx_audit_resource
  ON audit_events(workspace_id, resource_type, resource_id, occurred_at DESC);

CREATE INDEX idx_audit_action
  ON audit_events(workspace_id, action, occurred_at DESC);

---

Immutability Enforcement

-- Prevent UPDATE and DELETE on audit_events (application-level immutability)
CREATE OR REPLACE FUNCTION prevent_audit_mutation()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'Audit events are immutable. Operation % is not permitted.', TG_OP;
END;
$$;

CREATE TRIGGER audit_events_immutable
  BEFORE UPDATE OR DELETE ON audit_events
  FOR EACH ROW EXECUTE FUNCTION prevent_audit_mutation();

-- Also revoke DELETE from application role (defense in depth)
REVOKE DELETE ON audit_events FROM app_role;
REVOKE UPDATE ON audit_events FROM app_role;

-- Grant only INSERT and SELECT
GRANT INSERT, SELECT ON audit_events TO app_role;

---

TypeScript Audit Logger

// lib/audit/logger.ts
import { db } from "@/lib/db";
import { headers } from "next/headers";

// Canonical action names — use constants to avoid typos
export const AUDIT_ACTIONS = {
  // Membership
  MEMBER_INVITED:          "member.invited",
  MEMBER_ROLE_CHANGED:     "member.role_changed",
  MEMBER_REMOVED:          "member.removed",
  MEMBER_JOINED:           "member.joined",

  // Billing
  SUBSCRIPTION_UPGRADED:   "subscription.upgraded",
  SUBSCRIPTION_CANCELLED:  "subscription.cancelled",
  PAYMENT_FAILED:          "subscription.payment_failed",
  INVOICE_DOWNLOADED:      "invoice.downloaded",

  // Data
  DATA_EXPORTED:           "data.exported",
  DATA_IMPORTED:           "data.imported",

  // Settings
  WORKSPACE_RENAMED:       "workspace.renamed",
  SSO_CONFIGURED:          "sso.configured",
  API_KEY_CREATED:         "api_key.created",
  API_KEY_REVOKED:         "api_key.revoked",

  // Auth
  USER_LOGIN:              "user.login",
  USER_LOGIN_FAILED:       "user.login_failed",
  USER_PASSWORD_CHANGED:   "user.password_changed",
  USER_2FA_ENABLED:        "user.2fa_enabled",
} as const;

export type AuditAction = (typeof AUDIT_ACTIONS)[keyof typeof AUDIT_ACTIONS];

export interface AuditEventInput {
  workspaceId: string;
  actor: {
    type: "user" | "system" | "api_key" | "support";
    id: string;
    email?: string;
    name?: string;
  };
  action: AuditAction | string;
  category: string;
  resource?: {
    type: string;
    id: string;
    name?: string;
  };
  before?: Record<string, unknown>;
  after?: Record<string, unknown>;
  metadata?: Record<string, unknown>;
  ipAddress?: string;
  userAgent?: string;
  requestId?: string;
}

// Sanitize sensitive fields before storing
const REDACTED_KEYS = new Set([
  "password", "password_hash", "secret", "token", "api_key",
  "stripe_key", "private_key", "credit_card", "ssn",
]);

function sanitize(obj: Record<string, unknown> | undefined): Record<string, unknown> | undefined {
  if (!obj) return undefined;
  return Object.fromEntries(
    Object.entries(obj).map(([k, v]) => [
      k,
      REDACTED_KEYS.has(k.toLowerCase()) ? "[REDACTED]" : v,
    ])
  );
}

export async function logAuditEvent(input: AuditEventInput): Promise<void> {
  // Non-blocking: don't let audit logging fail the main request
  db.auditEvent.create({
    data: {
      workspaceId:  input.workspaceId,
      actorType:    input.actor.type,
      actorId:      input.actor.id,
      actorEmail:   input.actor.email,
      actorName:    input.actor.name,
      action:       input.action,
      category:     input.category,
      resourceType: input.resource?.type,
      resourceId:   input.resource?.id,
      resourceName: input.resource?.name,
      beforeState:  sanitize(input.before) ?? undefined,
      afterState:   sanitize(input.after) ?? undefined,
      metadata:     input.metadata ?? undefined,
      ipAddress:    input.ipAddress,
      userAgent:    input.userAgent,
      requestId:    input.requestId,
    },
  }).catch((err) => console.error("Audit log write failed:", err));
}

// Convenience: log from Server Action with request context
export async function logFromServerAction(
  input: Omit<AuditEventInput, "ipAddress" | "userAgent" | "requestId">
): Promise<void> {
  const hdrs = await headers();
  logAuditEvent({
    ...input,
    ipAddress:  hdrs.get("x-forwarded-for")?.split(",")[0],
    userAgent:  hdrs.get("user-agent") ?? undefined,
    requestId:  hdrs.get("x-request-id") ?? undefined,
  });
}

---

Using the Audit Logger

// app/actions/members.ts
"use server";

import { getWorkspaceContext } from "@/lib/auth/workspace-context";
import { logFromServerAction, AUDIT_ACTIONS } from "@/lib/audit/logger";
import { db } from "@/lib/db";

export async function removeMember(memberId: string) {
  const ctx = await getWorkspaceContext();
  if (!ctx || ctx.role !== "owner") throw new Error("Unauthorized");

  const member = await db.workspaceMember.findUnique({
    where: { id: memberId },
    include: { user: { select: { email: true, name: true } } },
  });

  if (!member) throw new Error("Member not found");

  await db.workspaceMember.delete({ where: { id: memberId } });

  // Audit log: who removed whom
  await logFromServerAction({
    workspaceId: ctx.workspaceId,
    actor: {
      type: "user",
      id:    ctx.userId,
      email: ctx.email,
    },
    action:   AUDIT_ACTIONS.MEMBER_REMOVED,
    category: "membership",
    resource: {
      type: "member",
      id:   memberId,
      name: member.user?.name ?? member.user?.email,
    },
    after: { removedUserId: member.userId, removedEmail: member.user?.email },
  });
}

---

Audit Log Query API

// app/api/audit/route.ts
import { NextRequest, NextResponse } from "next/server";
import { getWorkspaceContext } from "@/lib/auth/workspace-context";
import { db } from "@/lib/db";
import { z } from "zod";

const QuerySchema = z.object({
  page:         z.coerce.number().min(1).default(1),
  limit:        z.coerce.number().min(1).max(100).default(50),
  actorId:      z.string().optional(),
  resourceType: z.string().optional(),
  resourceId:   z.string().optional(),
  action:       z.string().optional(),
  category:     z.string().optional(),
  from:         z.string().datetime().optional(),
  to:           z.string().datetime().optional(),
});

export async function GET(req: NextRequest) {
  const ctx = await getWorkspaceContext();
  if (!ctx) return NextResponse.json({ error: "Unauthorized" }, { status: 401 });
  if (ctx.role !== "owner" && ctx.role !== "admin") {
    return NextResponse.json({ error: "Forbidden" }, { status: 403 });
  }

  const params = QuerySchema.safeParse(
    Object.fromEntries(req.nextUrl.searchParams)
  );
  if (!params.success) {
    return NextResponse.json({ error: "Invalid params" }, { status: 400 });
  }

  const { page, limit, actorId, resourceType, resourceId, action, category, from, to } = params.data;

  const where = {
    workspaceId:  ctx.workspaceId,
    ...(actorId      && { actorId }),
    ...(resourceType && { resourceType }),
    ...(resourceId   && { resourceId }),
    ...(action       && { action }),
    ...(category     && { category }),
    ...(from || to)  && {
      occurredAt: {
        ...(from && { gte: new Date(from) }),
        ...(to   && { lte: new Date(to) }),
      },
    },
  };

  const [events, total] = await Promise.all([
    db.auditEvent.findMany({
      where,
      orderBy: { occurredAt: "desc" },
      skip: (page - 1) * limit,
      take: limit,
      select: {
        id: true, occurredAt: true,
        actorType: true, actorId: true, actorEmail: true, actorName: true,
        action: true, category: true,
        resourceType: true, resourceId: true, resourceName: true,
        ipAddress: true, metadata: true,
        // Omit before/after from list view (included in detail endpoint)
      },
    }),
    db.auditEvent.count({ where }),
  ]);

  return NextResponse.json({
    events,
    pagination: { page, limit, total, pages: Math.ceil(total / limit) },
  });
}

---

Compliance Export

// app/api/audit/export/route.ts
import { NextRequest, NextResponse } from "next/server";
import { getWorkspaceContext } from "@/lib/auth/workspace-context";
import { db } from "@/lib/db";
import { stringify } from "csv-stringify/sync";
import { logFromServerAction, AUDIT_ACTIONS } from "@/lib/audit/logger";

export async function GET(req: NextRequest) {
  const ctx = await getWorkspaceContext();
  if (!ctx || ctx.role !== "owner") {
    return NextResponse.json({ error: "Forbidden" }, { status: 403 });
  }

  const format = req.nextUrl.searchParams.get("format") ?? "csv";
  const from   = req.nextUrl.searchParams.get("from");
  const to     = req.nextUrl.searchParams.get("to");

  const events = await db.auditEvent.findMany({
    where: {
      workspaceId: ctx.workspaceId,
      ...(from || to) && {
        occurredAt: {
          ...(from && { gte: new Date(from) }),
          ...(to   && { lte: new Date(to) }),
        },
      },
    },
    orderBy: { occurredAt: "asc" },
  });

  // Log the export itself
  await logFromServerAction({
    workspaceId: ctx.workspaceId,
    actor:    { type: "user", id: ctx.userId, email: ctx.email },
    action:   AUDIT_ACTIONS.DATA_EXPORTED,
    category: "data",
    resource: { type: "audit_log", id: ctx.workspaceId },
    metadata: { format, from, to, eventCount: events.length },
  });

  if (format === "json") {
    return new NextResponse(JSON.stringify(events, null, 2), {
      headers: {
        "Content-Type": "application/json",
        "Content-Disposition": `attachment; filename="audit-log-${Date.now()}.json"`,
      },
    });
  }

  // CSV export
  const csv = stringify(events, {
    header: true,
    columns: [
      "id", "occurredAt", "actorType", "actorEmail", "actorName",
      "action", "category", "resourceType", "resourceId", "resourceName",
      "ipAddress", "requestId",
    ],
  });

  return new NextResponse(csv, {
    headers: {
      "Content-Type": "text/csv",
      "Content-Disposition": `attachment; filename="audit-log-${Date.now()}.csv"`,
    },
  });
}

---

Retention Policy

-- Cron job: delete old partitions beyond retention period
-- (Much faster than row-level DELETE — partition drop is O(1))

-- For GDPR: retain for 2 years, then delete
-- Drop partitions older than 24 months:
DO $$
DECLARE
  partition_name TEXT;
  cutoff_year    INTEGER := EXTRACT(YEAR FROM now() - INTERVAL '24 months')::INTEGER;
  cutoff_month   INTEGER := EXTRACT(MONTH FROM now() - INTERVAL '24 months')::INTEGER;
BEGIN
  FOR partition_name IN
    SELECT tablename FROM pg_tables
    WHERE schemaname = 'public'
      AND tablename LIKE 'audit_events_%'
      AND tablename < format('audit_events_%s_%s',
          cutoff_year, LPAD(cutoff_month::TEXT, 2, '0'))
  LOOP
    EXECUTE format('DROP TABLE IF EXISTS %I', partition_name);
    RAISE NOTICE 'Dropped partition: %', partition_name;
  END LOOP;
END $$;

---

Cost and Timeline

---

See Also

• SaaS Audit Logging — Trigger-based automatic DB change capture
• PostgreSQL Triggers Audit — DB-level audit triggers
• SaaS GDPR Data Export — Full DSAR export pipeline
• SaaS Role-Based Access — Who can view audit logs

---

Working With Viprasol

We build audit trail systems for SaaS products pursuing SOC 2 Type II certification and enterprise sales. Our team has shipped immutable audit logs that satisfy auditor requirements and pass enterprise security reviews.

What we deliver:

• Append-only PostgreSQL schema with UPDATE/DELETE prevention triggers
• Actor/resource/action event model with canonical action constants
• Sensitive field sanitization before storage
• Admin query UI with filtering by actor, resource, action, and date range
• Compliance export in CSV and JSON for auditors
• Partition-based retention policy (drop old partitions in O(1))

Explore our SaaS development services or contact us to build your audit trail.