ADR-0028: UUID v7 keys and monthly partitions for high-volume tables
- Status: Accepted
- Date: 2026-11-12
Context and problem statement
Section titled “Context and problem statement”Rows are created by the API, by the worker and by database functions, for many companies in the same tables. A few tables only grow: the audit logs, messages, domain events and the log of file reads.
Which keys do tables use, and how are the tables that only grow kept manageable?
Decision drivers
Section titled “Decision drivers”- Keys that the database generates and that the API can also choose before an insert.
- High-volume tables are partitioned by date from their first migration, not converted later.
- Old months of a log can be removed by detaching a partition, without a slow delete.
Considered options
Section titled “Considered options”For keys:
- UUID v7, generated by the native
uuidv7()of PostgreSQL. - Sequential integers, or random UUID v4. Common market alternatives, listed for comparison.
For high-volume tables:
- Range partitions by month on the time column.
- One plain table. Obvious alternative, listed for comparison.
Decision outcome
Section titled “Decision outcome”Chosen: option 1 in both.
Primary keys are uuid DEFAULT uuidv7(). A few link tables use a composite key instead, such as role_permissions. With the same decision came two conventions: instants are timestamptz, and money is an integer number of cents.
Five tables are partitioned by month: audit_log, platform_audit_log, messages, domain_events and file_access_log. Their primary key includes the time column. There is no default partition, so the worker creates the partitions three months ahead every day, and the health check of the API fails when too few future partitions of the audit logs exist.
Consequences
Section titled “Consequences”- Good, because keys are ordered by time, which keeps index writes local, and the API can choose an id before inserting the row.
- Good, because a month of a log is one partition that can be detached.
- Bad, because native
uuidv7()requires PostgreSQL 18. A managed database service has to offer that version, with PostGIS and the other four extensions the schema uses. - Bad, because a row of a partitioned table is addressed by its id and its time together, and code that knows only the id reads every partition.
- Bad, because a row written for a month without a partition is refused. The system depends on the partition routine running.
- Bad, because partitioned tables are written by hand outside the migration generator, see ADR-0004.
More information
Section titled “More information”packages/db/migrations/0002_foundation_security.sql,0007_wave01_security.sql,0029_wave03_messaging_security.sql,0037_wave03_automation_security.sqland0043_wave04_files_security.sql: the partitioned tables.apps/api/src/core/events/maintenance.tsandpackages/db/src/gate.ts(ensureAuditPartitions): the routines that create partitions.apps/api/src/modules/platform/health/health.controller.ts: the health check.packages/db/sql/bootstrap-roles.sql: the extensions.- Concept pages: Audit log, Background work. Reference: Database tables.