# 0004 — ULIDs, integer minor units, UTC

**Status:** Accepted · 2026-08-01

## Context

Three data decisions that are cheap now and effectively unfixable once hundreds
of independent installations hold years of records.

## Decision

**Identity.** Every table carries both a `BIGINT` auto-increment primary key —
internal, for compact indexes and fast joins — and a `CHAR(26)` ULID, which is
the *only* identifier ever exposed in a URL, an API payload, a QR code or an
export. Route model binding uses the ULID, so a controller cannot accidentally
accept or leak an internal id.

ULID over UUIDv4: time-ordered, so a unique index on it does not fragment; 26
characters instead of 36; and it does not leak a sequential record count the way
an auto-increment id does. A competitor should not be able to read a clinic's
monthly patient volume off a URL.

**Money.** `BIGINT` in minor units plus a currency column on the owning
document, wrapped in an immutable `App\Support\Money\Money`. Never a float —
floating-point money is a defect waiting for an audit. Allocation uses the
largest-remainder method so split parts sum exactly back to the whole, which is
the property that makes a cashier's drawer reconcile. Currencies with zero or
three decimal places (JPY, KWD, BHD) are handled from day one.

**Time.** All timestamps stored UTC; the clinic timezone is a setting applied at
the presentation edge. Appointments additionally store their originating
timezone, because a DST shift otherwise silently moves historical appointments.

**Numbering.** Medical record, invoice and prescription numbers come from a
locked counter row (`sequences`), allocated inside the transaction that creates
the document. `MAX(number) + 1` produces duplicates the first time two
receptionists register a patient in the same second.

## Consequences

**Good.** None of these can be retrofitted cheaply, and all three are now
impossible to get wrong by default — the traits and value objects make the
correct thing the easy thing.

**Bad.** An extra 26-byte column and unique index per table. Negligible against
the cost of exposing sequential ids or discovering a rounding drift in an
invoice ledger two years in.

**Note on `sequences.branch_id`.** NOT NULL, defaulting to 0 for system-wide
counters, and carrying no foreign key — MySQL treats NULLs as distinct in a
unique index, so a nullable column would let two rows for the same counter
coexist and `SELECT ... FOR UPDATE` would lock the wrong one, reintroducing the
duplicate invoice numbers the table exists to prevent.