Use PostgreSQL 18 UUIDv7 for AI Agent Event IDs

PostgreSQL 18 can generate UUIDv7 identifiers with uuidv7(). They are useful primary keys for append-heavy agent-event tables because their timestamp-leading layout is broadly ordered by creation time while remaining globally unique. Keep a separate authoritative event timestamp and per-stream sequence, however: UUIDv7 is an identifier, not a globally strict event clock or an idempotency guarantee.

Understand what PostgreSQL 18 added

PostgreSQL 18 introduced uuidv7([shift interval]), plus uuid_extract_timestamp(uuid) and uuid_extract_version(uuid). The official UUID functions documentation says PostgreSQL’s generator combines a Unix timestamp at millisecond precision, a sub-millisecond timestamp, and random data. The optional shift changes the computed timestamp by an interval.

UUIDv7 is standardized by RFC 9562, which places a 48-bit Unix timestamp in the most significant portion and defines version and variant bits. The remaining layout supports monotonicity methods and random data. Treat values from other generators according to their implementation; PostgreSQL warns that an extracted timestamp may not exactly equal the generation time.

PostgreSQL’s native uuid type remains a 128-bit value and accepts multiple textual forms. The UUID type reference documents native generation for versions 4 and 7.

Separate identity, occurrence time, and order

An agent event usually needs four different concepts:

Do not derive all four from event_id. Clock skew, imports, retries, offline clients, and events created by another UUIDv7 implementation can violate that assumption. For replay, enforce a sequence within the workflow or aggregate. For investigation, retain both occurrence and recording time.

Create an append-only event table

This baseline keeps the event ID opaque while making replay and deduplication explicit:

CREATE TABLE agent_events (
  tenant_id uuid NOT NULL,
  event_id uuid PRIMARY KEY DEFAULT uuidv7(),
  stream_id uuid NOT NULL,
  stream_seq bigint NOT NULL CHECK (stream_seq > 0),
  event_type text NOT NULL CHECK (event_type <> ''),
  occurred_at timestamptz NOT NULL,
  recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(),
  source_idempotency_key text,
  payload jsonb NOT NULL,
  actor_id uuid,

  CONSTRAINT agent_events_stream_order
    UNIQUE (tenant_id, stream_id, stream_seq)
);

CREATE INDEX agent_events_stream_replay
  ON agent_events (tenant_id, stream_id, stream_seq);

CREATE INDEX agent_events_recorded_at
  ON agent_events (tenant_id, recorded_at, event_id);

CREATE UNIQUE INDEX agent_events_source_dedup
  ON agent_events (tenant_id, source_idempotency_key)
  WHERE source_idempotency_key IS NOT NULL;

The partial index lets many events omit a source key while preventing reuse of a present key within a tenant. Do not use UNIQUE NULLS NOT DISTINCT for this optional key: it would allow only one null key per tenant.

Allocate stream_seq atomically with the append. A unique constraint detects a race but does not choose which writer wins. Use a locked stream-head row, compare-and-set expected version, or a single database function that increments the head and inserts the event in one transaction. See event-sourced AI agents for replay and concurrency design.

Query by the field that answers the question

For replay, use the stream sequence:

SELECT event_id, stream_seq, event_type, occurred_at, payload
FROM agent_events
WHERE tenant_id = $1 AND stream_id = $2
ORDER BY stream_seq;

For ingestion investigations, use recorded_at, event_id. For business-time reporting, use occurred_at and define late-arrival semantics. Ordering by UUIDv7 can be a useful creation-order approximation, but it is not a replacement for the explicit order required by a deterministic state machine.

Use extraction for validation and diagnostics:

SELECT
  event_id,
  uuid_extract_version(event_id) AS uuid_version,
  uuid_extract_timestamp(event_id) AS embedded_time,
  recorded_at
FROM agent_events
WHERE event_id = $1;

Do not expose embedded timestamps as authoritative event time. Do not use uuidv7(shift) to forge historical business identity during a backfill. If a test needs a shifted UUID, label it test data; production imports should preserve original IDs or generate new ingestion IDs while retaining source identity separately.

Enforce tenant isolation independently

A UUID is not a secret and does not authorize access. Always scope reads and mutations by tenant and current principal. Consider PostgreSQL row-level security for defense in depth, and ensure policies also cover maintenance roles and background agents. Never trust the tenant ID supplied in an agent prompt or tool argument.

Keep payload validation versioned by event_type. JSONB accepts structurally valid JSON, not your domain schema. Validate before insert and reject oversized or unrecognized events. Sensitive prompt or tool content may require field-level minimization, encryption, and shorter retention; an append-only table can otherwise become a permanent data leak.

Audit events should record actor, policy version, external receipt, and an integrity strategy appropriate to the threat model. Our forensic audit-log guide covers tamper evidence and restricted access.

Migrate without rewriting identity carelessly

If an existing event table uses UUIDv4, keeping old IDs is usually safer than replacing every primary and foreign key. PostgreSQL can store v4 and v7 in the same uuid column. Change the default for new rows:

ALTER TABLE agent_events
  ALTER COLUMN event_id SET DEFAULT uuidv7();

Then audit consumers that assume one version. uuid_extract_version can distinguish standard variants. Do not add a constraint requiring version 7 until every legitimate importer and historical row satisfies it.

For a new parallel identifier, add a nullable column, write both for new events, backfill only with a documented identity rule, create indexes concurrently where operationally appropriate, validate consumers, and then decide whether the new field becomes primary. Rebuilding large primary keys can lock, write WAL, and cascade into foreign-key indexes; rehearse on a production-sized clone.

Benchmark locality instead of repeating folklore

Timestamp-leading IDs are expected to produce a more insertion-local B-tree workload than uniformly random UUIDv4, but your result depends on concurrency, storage, checkpoints, table width, fill factor, and index set. PostgreSQL’s B-tree documentation describes the index method; it does not promise a workload-specific speedup.

Run a paired test:

1. Create identical v4 and v7 tables and indexes.
2. Generate the same concurrent insert schedule and payload sizes.
3. Start from equivalent database and storage state.
4. Run long enough to cross checkpoints and steady state.
5. Measure insert latency distributions, throughput, WAL bytes, index size, buffer reads/writes, and replication lag.
6. Repeat trials with randomized order.
7. Test replay and time-range queries, not only inserts.
8. Publish hardware, PostgreSQL patch, configuration, row count, clients, and uncertainty.

Do not claim a universal improvement from one laptop run. The benefit may be irrelevant if payload and vector indexes dominate storage, while the semantic clarity of explicit sequence and time remains valuable either way.

Roll out with compatibility checks

Canary the new default, monitor constraint failures and index/WAL behavior, verify replicas and logical consumers, and keep rollback simple: restoring the v4 default should not invalidate existing v7 rows. Confirm backup, restore, CDC, ORM, and warehouse tooling treat UUID values opaquely before expanding.