Audit logs are append-only records of system events — who did what, when. UUID v7 is a strong choice for audit log event IDs because its timestamp prefix imposes natural chronological order, its randomness makes IDs opaque (no enumerable sequence), and its 128-bit width avoids the coordination problem of distributed integer sequences. See UUID v4 vs v7 for the B-tree performance context.
Append-only audit log table
-- PostgreSQL — audit_events table
CREATE TABLE audit_events (
id uuid PRIMARY KEY DEFAULT uuidv7(), -- PostgreSQL 18+
actor_id uuid NOT NULL,
resource text NOT NULL,
action text NOT NULL,
payload jsonb,
created_at timestamptz NOT NULL DEFAULT now()
);
-- Chronological range query — uses the uuid primary key index
SELECT * FROM audit_events
WHERE id >= '018fbe3a-0000-7000-8000-000000000000' -- lower bound UUID v7
AND id < '018fbe3c-0000-7000-8000-000000000000' -- upper bound UUID v7
ORDER BY id;
Because UUID v7 bytes sort lexicographically in creation order, ORDER BY id is equivalent to ORDER BY created_at — and the primary key B-tree index serves both. No secondary index on created_at is required for time-range queries. See UUID in Databases for the index fragmentation detail.
Deriving UUID v7 time bounds
// Build a UUID v7 lower bound from a timestamp (for range queries)
function uuidV7FromMs(ms: number): string {
const hex = ms.toString(16).padStart(12, '0');
// version nibble = 7, variant = 80, remaining bits = 0
return `${hex.slice(0,8)}-${hex.slice(8,12)}-7000-8000-000000000000`;
}
const since = uuidV7FromMs(Date.now() - 24 * 60 * 60 * 1000); // last 24h
// SELECT * FROM audit_events WHERE id >= $1 ORDER BY id
This approach avoids a separate indexed created_at column while still supporting efficient time-windowed queries. See Time Precision for the 48-bit millisecond timestamp detail.
Node.js audit event emitter
import { v7 as uuidv7 } from 'uuid';
import { Pool } from 'pg';
const db = new Pool();
interface AuditEvent {
actorId: string;
resource: string;
action: string;
payload?: Record<string, unknown>;
}
async function logEvent(event: AuditEvent): Promise<string> {
const id = uuidv7();
await db.query(
'INSERT INTO audit_events (id, actor_id, resource, action, payload) VALUES ($1,$2,$3,$4,$5)',
[id, event.actorId, event.resource, event.action, event.payload ?? null]
);
return id; // return the event ID for correlation
}
// Usage
const eventId = await logEvent({
actorId: currentUserId,
resource: 'invoice:inv_42',
action: 'UPDATE',
payload: { field: 'status', from: 'draft', to: 'sent' },
});
Returning the event ID on every write lets callers attach it to API responses as a correlation handle — clients can reference it in support queries. This mirrors the idempotency key pattern — see UUID as Idempotency Keys.
Multi-tenant partitioning
In a multi-tenant SaaS system, audit events are typically partitioned by tenant. UUID v7 works naturally here:
-- Partition by tenant, sort by UUID v7 within each partition
CREATE TABLE audit_events (
id uuid NOT NULL DEFAULT uuidv7(),
tenant_id uuid NOT NULL,
action text NOT NULL,
created_at timestamptz NOT NULL DEFAULT now(),
PRIMARY KEY (tenant_id, id) -- composite key — tenant first for locality
) PARTITION BY HASH (tenant_id);
The composite primary key (tenant_id, id) keeps each tenant’s rows co-located on disk and sorted by time within the partition. See UUID in Multi-Tenant SaaS for the full partitioning strategy.
Further reading
- UUID v4 vs v7 — B-tree ordering and performance
- UUID in Databases — storage and indexing
- UUID vs Auto-Increment Primary Keys
- UUID in Multi-Tenant SaaS
- UUID Security — what v7 discloses
- Time Precision — UUID v7 millisecond timestamp
- UUID as Idempotency Keys
External references
Frequently asked questions
Why use UUID v7 instead of auto-increment for audit log IDs?
Auto-increment integers leak row counts, making it trivial for an attacker to enumerate records or estimate event volume. UUID v7 provides opaque IDs with no enumerable sequence while still sorting chronologically — you get the ordering benefit of integers without the information disclosure. The 48-bit timestamp also embeds the event time directly in the ID, removing the need to query a separate created_at column for time-range scans. See UUID vs Auto-Increment.
Can I use UUID v7 to detect gaps or tampering in an audit log?
UUID v7 IDs are not sequential integers, so a missing ID does not indicate a gap — an attacker could delete a row without leaving a detectable hole. For tamper-evident logs, combine UUID v7 IDs with a hash chain: each row stores sha256(previous_id || payload). UUID v7 makes the chronological order verifiable; the hash chain makes deletion detectable. See UUID Security for what UUID v7 discloses about timing.