karawaci.kode

2026-09-23 · 11 min

Audit Log Immutable Database Pattern: Building Tamper-Proof Records for Compliance in 2026

Your finance app passed the OJK audit last year. It might not pass the next one if your audit logs can be quietly edited by anyone with a psql prompt and the right permissions.

This is not a theoretical risk. I have seen it firsthand: a fintech startup near Gading Serpong that stored audit data in a regular table with full UPDATE/DELETE privileges for their application user, thinking row-level security was enough. It is not. A compromised app credential, a rogue migration, or a careless UPDATE * SET with no WHERE clause — any of these can silently erase or alter the history that compliance auditors will ask for.

An immutable audit log is not a luxury for large enterprises. It is table stakes for any application that touches money, identity, or regulated data.

What “immutable” actually means at the database level

Immutability does not mean the data physically cannot be changed — it means that any change is detectable and requires extraordinary access to execute. You are building a system where:

  1. Normal application credentials cannot UPDATE or DELETE audit rows
  2. Any tampering with a past record breaks a verifiable chain
  3. A separate verification process regularly checks that chain

The cheapest version of this is a PostgreSQL table with a restrictive role. The more robust version adds cryptographic chaining on top. I will show both.

Schema design: append-only from the start

Start with the table definition. The key constraint: no UPDATE, no DELETE allowed at the application role level.

-- Create a dedicated role for audit log writes (INSERT only)
CREATE ROLE audit_writer NOLOGIN;
CREATE ROLE audit_reader NOLOGIN;

-- The audit log table
CREATE TABLE audit_events (
    event_id        UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    actor_id        TEXT NOT NULL,
    actor_ip        INET,
    action          TEXT NOT NULL,  -- 'user.updated', 'payment.created', etc.
    resource_type   TEXT NOT NULL,
    resource_id     TEXT NOT NULL,
    before_state    JSONB,
    after_state     JSONB,
    correlation_id  UUID,           -- links to your tracing system
    prev_hash       TEXT,           -- SHA-256 of the previous row
    current_hash    TEXT NOT NULL   -- SHA-256 of this row's content
);

-- Revoke everything, grant only INSERT
REVOKE ALL ON audit_events FROM PUBLIC;
GRANT INSERT ON audit_events TO audit_writer;
GRANT SELECT ON audit_events TO audit_reader;

-- Block UPDATE and DELETE even for table owner within app context
-- (superuser can still modify, but that's a different threat model)
CREATE RULE no_update_audit AS ON UPDATE TO audit_events DO INSTEAD NOTHING;
CREATE RULE no_delete_audit AS ON DELETE TO audit_events DO INSTEAD NOTHING;

The RULE approach is blunt but effective for stopping accidental writes. For a stricter setup, use row-level security combined with a policy that only allows INSERT from a specific role.

Do not put occurred_at as a user-supplied value. The column default now() is set by the database, not the application. If your application passes a timestamp, strip it — you want the database clock, not the application clock, which can be manipulated.

Cryptographic chaining: making tampering visible

The schema above prevents casual tampering. The hash chain makes sophisticated tampering detectable.

The idea: each audit row includes the hash of the row before it. If you modify any past row, all hashes after it become invalid. A nightly verification job catches this.

// audit-log.service.ts
import { createHash } from "crypto";
import { db } from "./db"; // your pg client

interface AuditEvent {
  actorId: string;
  actorIp: string;
  action: string;
  resourceType: string;
  resourceId: string;
  beforeState?: Record<string, unknown>;
  afterState?: Record<string, unknown>;
  correlationId?: string;
}

async function computeHash(
  content: string,
  prevHash: string | null
): Promise<string> {
  const input = `${prevHash ?? "GENESIS"}:${content}`;
  return createHash("sha256").update(input).digest("hex");
}

export async function writeAuditEvent(event: AuditEvent): Promise<void> {
  // Get the most recent hash to chain from
  const lastRow = await db.oneOrNone<{ current_hash: string }>(
    `SELECT current_hash FROM audit_events ORDER BY occurred_at DESC LIMIT 1`
  );

  const prevHash = lastRow?.current_hash ?? null;

  // Build the content to hash — deterministic serialization is important
  const content = JSON.stringify({
    actorId: event.actorId,
    action: event.action,
    resourceType: event.resourceType,
    resourceId: event.resourceId,
    afterState: event.afterState ?? null,
  });

  const currentHash = await computeHash(content, prevHash);

  await db.none(
    `INSERT INTO audit_events
       (actor_id, actor_ip, action, resource_type, resource_id,
        before_state, after_state, correlation_id, prev_hash, current_hash)
     VALUES
       ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)`,
    [
      event.actorId,
      event.actorIp,
      event.action,
      event.resourceType,
      event.resourceId,
      event.beforeState ? JSON.stringify(event.beforeState) : null,
      event.afterState ? JSON.stringify(event.afterState) : null,
      event.correlationId ?? null,
      prevHash,
      currentHash,
    ]
  );
}

One practical note: if you have concurrent writes, computing prev_hash from the last row can produce collisions — two writers both reading the same current_hash as their prev_hash. For high-throughput systems, solve this with a dedicated sequence and a SKIP LOCKED pattern, or use a single-writer queue per audit stream. For most OLTP systems writing a few hundred audit events per second, a serialized write via a queue worker is fine.

Verification job: the actual compliance proof

The hash chain is only valuable if you verify it. Otherwise it is just expensive decoration.

// audit-verify.job.ts — run this nightly via pg_cron or an external scheduler
export async function verifyAuditChain(): Promise<{
  valid: boolean;
  firstBrokenAt?: string;
}> {
  const rows = await db.any<{
    event_id: string;
    occurred_at: string;
    actor_id: string;
    action: string;
    resource_type: string;
    resource_id: string;
    after_state: Record<string, unknown> | null;
    prev_hash: string | null;
    current_hash: string;
  }>(
    `SELECT event_id, occurred_at, actor_id, action, resource_type,
            resource_id, after_state, prev_hash, current_hash
     FROM audit_events
     ORDER BY occurred_at ASC`
  );

  let expectedPrevHash: string | null = null;

  for (const row of rows) {
    const content = JSON.stringify({
      actorId: row.actor_id,
      action: row.action,
      resourceType: row.resource_type,
      resourceId: row.resource_id,
      afterState: row.after_state ?? null,
    });

    const expectedHash = await computeHash(content, expectedPrevHash);

    if (expectedHash !== row.current_hash) {
      return { valid: false, firstBrokenAt: row.event_id };
    }

    expectedPrevHash = row.current_hash;
  }

  return { valid: true };
}

Run this as a scheduled job and alert your on-call channel if valid is false. A broken chain is a P1 incident — someone or something touched the audit log after the fact. You do not need to know who immediately; you need to know it happened.

Partitioning for retention and archival

After about 12 months in production, an audit table for a moderately busy fintech can grow to tens of millions of rows. PostgreSQL range partitioning by month keeps query performance manageable and lets you archive old partitions to cold storage.

-- Re-create as a partitioned table
CREATE TABLE audit_events (
    event_id        UUID NOT NULL DEFAULT gen_random_uuid(),
    occurred_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
    actor_id        TEXT NOT NULL,
    action          TEXT NOT NULL,
    resource_type   TEXT NOT NULL,
    resource_id     TEXT NOT NULL,
    before_state    JSONB,
    after_state     JSONB,
    correlation_id  UUID,
    prev_hash       TEXT,
    current_hash    TEXT NOT NULL
) PARTITION BY RANGE (occurred_at);

-- Create partitions manually or via pg_partman
CREATE TABLE audit_events_2026_09
    PARTITION OF audit_events
    FOR VALUES FROM ('2026-09-01') TO ('2026-10-01');

CREATE TABLE audit_events_2026_10
    PARTITION OF audit_events
    FOR VALUES FROM ('2026-10-01') TO ('2026-11-01');

When a partition ages past your hot retention window (typically 90 days), detach it, dump it to S3 with pg_dump --format=custom, verify the dump, and drop the partition. The S3 copy is your cold archive. OJK’s 5-year retention requirement is satisfied by S3 Glacier with lifecycle policies — you are not expected to keep 5 years of data in live PostgreSQL.

What to log and what to skip

Not everything needs an audit entry. Logging every database read is wasteful and produces noise that buries the signal.

Log these: authentication events (login, logout, failed attempts), mutations to regulated entities (user profiles, account balances, transaction state changes), permission and role changes, configuration changes in the admin panel, and data exports.

Skip these: read-only API requests where no state change occurred, internal health checks, pagination and filtering operations on public data.

The action field should be a verb-noun pair from a controlled vocabulary: user.created, user.updated, payment.initiated, payment.status_changed, role.assigned. Free-form strings here will make your audit queries a nightmare in six months. Define the enum in your application code and enforce it.

Querying for compliance reports

When an auditor asks “show me all changes to account X between January and March,” you need this to be fast.

-- Index for compliance queries
CREATE INDEX audit_resource_lookup
    ON audit_events (resource_type, resource_id, occurred_at DESC);

CREATE INDEX audit_actor_lookup
    ON audit_events (actor_id, occurred_at DESC);

-- Query: all changes to a specific account
SELECT
    event_id,
    occurred_at,
    actor_id,
    actor_ip,
    action,
    before_state,
    after_state
FROM audit_events
WHERE resource_type = 'account'
  AND resource_id = '550e8400-e29b-41d4-a716-446655440000'
  AND occurred_at BETWEEN '2026-01-01' AND '2026-04-01'
ORDER BY occurred_at ASC;

With the composite index this query runs in milliseconds even on a table with 50M rows, because PostgreSQL can use the index to satisfy all three predicates.

The operational reality

One thing that rarely appears in blog posts: you will eventually need to explain this system to a non-technical compliance officer. The cryptographic chain sounds impressive but confuses auditors who are not engineers. Prepare a one-page diagram showing: who can write to the audit table (app service only, via INSERT), who can read it (audit service, read-only replica), what the verification job does, and where cold archives live.

That diagram will save you hours in the actual audit room. I learned this after a particularly long afternoon at a meeting in a BSD office tower where half the time was spent explaining why “blockchain-like” does not mean we actually used a blockchain.

The pattern itself is not complex. The discipline required to maintain it — resisting the urge to “just quickly fix” a bad audit entry, keeping the verification job monitored, rotating the cold archive credentials annually — that is where most implementations quietly fall apart.

Build the system once, correctly. Then build the operational wrapper around it that makes sure it stays correct.

Ditulis oleh Reza Pradipta