ULID primary keys

Status: Accepted, stated in the project's written engineering rules. This record covers three linked rules: primary keys are ULIDs stored in a UUID column, every record carries the same audit columns, and human-readable business keys come from core_sequences. The base class is explained on base model and ids.

Context¶

The project's written engineering rules ask for keys that sort by creation time and carry a time prefix, and for business keys made of a prefix and a counter, some reset yearly. The docstring of new_ulid states the property the code relies on: the value is time-sortable, globally unique, and compatible with PostgreSQL's native uuid type (src/cluster_erp/core/ulid.py:8-14). Further rationale is not determined from code.

Decision¶

• BaseModel.id is a UUID primary key whose default is new_ulid (src/cluster_erp/core/models.py:20-24).
• BaseModel adds tenant_id, created_at, created_by, updated_at, updated_by, deleted_at, deleted_by, deactivation_reason, deactivation_note, and version (src/cluster_erp/core/models.py:25-78).
• Business keys come from next_code, which reads and advances one core_sequences row per entity type (src/cluster_erp/core/sequence.py:112-120, src/cluster_erp/core/sequence.py:154-171).

The flowchart shows how a new record gets its keys.

flowchart TD
 new["new object added to the session"] --> flush["before_flush"]
 flush --> pk["_ensure_pk assigns new_ulid if id is empty"]
 pk --> stamp["created_by stamped from the request actor"]
 svc["service needs a business key"] --> seq["next_code(session, entity_type)"]
 seq --> yearly{"yearly_reset?"}
 yearly -->|yes| y["PREFIX-YEAR-00001"]
 yearly -->|no| n["PREFIX-000001"]

In prose: the change-history listener assigns the ULID before the insert, so the history row of a new record carries its id (src/cluster_erp/core/change_history.py:273-281). A service that needs a business key calls next_code. For yearly entities the code contains the year and a five-digit counter; for the others a six-digit counter (src/cluster_erp/core/sequence.py:86-109, src/cluster_erp/core/sequence.py:168-171). The counter restarts when the calendar year changes (src/cluster_erp/core/sequence.py:194-197).

Consequences¶

• Ids are created in Python, not in the database. The listener materialises them early so the history is complete (src/cluster_erp/core/change_history.py:273-278).
• version starts at 1 on every row (src/cluster_erp/core/models.py:73-78).
• 19 entity types reset their counter each year, listed in YEARLY_ENTITIES (src/cluster_erp/core/sequence.py:88-108, counted from the set). All other entity types count continuously, for example parties with prefix PTY (src/cluster_erp/core/sequence.py:15).
• Missing sequence rows are seeded at start-up by seed_sequences (src/cluster_erp/core/sequence.py:123-151).

Where the code enforces it¶

Not determined from code: a test that every table uses a ULID key. None was found in tests/ (searched for new_ulid and ulid).

Related pages¶

• Decision records
• Base model and ids
• Change history
• No hard delete

Verified against commit 92f8051 on 2026-11-13.