Skip to content

ADR-0028: UUID v7 keys and monthly partitions for high-volume tables

  • Status: Accepted
  • Date: 2026-11-12

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?

  • 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.

For keys:

  1. UUID v7, generated by the native uuidv7() of PostgreSQL.
  2. Sequential integers, or random UUID v4. Common market alternatives, listed for comparison.

For high-volume tables:

  1. Range partitions by month on the time column.
  2. One plain table. Obvious alternative, listed for comparison.

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.

  • 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.
  • packages/db/migrations/0002_foundation_security.sql, 0007_wave01_security.sql, 0029_wave03_messaging_security.sql, 0037_wave03_automation_security.sql and 0043_wave04_files_security.sql: the partitioned tables.
  • apps/api/src/core/events/maintenance.ts and packages/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.