ADR 0005/0006 — ULID IDs and Unix-epoch timestamps

Two D1 conventions decided together because they serve the same end: cheap creation-ordered listings and trivial time-travel reconstruction. IDs are TEXT (never auto-increment integers); audit timestamps are INTEGER. This page records what goes in those columns.

ULIDs for every primary key

Every primary key is a ULID: 26 characters, Crockford base32, lexicographically sortable, with the first 48 bits encoding the millisecond timestamp and the remaining 80 bits random. IDs are generated in the Worker at insert time through one shared helper, generateId() in src/shared/db/ids.ts. The database never assigns IDs.

• Time-sortable — ORDER BY id DESC is most-recent-first without a separate created_at index, which the version-history and admin-list patterns lean on.
• No coordination — generated in the isolate with no round trip to D1.
• Unguessable enough — 80 random bits put enumeration well out of reach for a single-tenant, authenticated system.
• URL-safe and compact — 26 characters versus 36 for UUID, which adds up across the unboundedly growing entity_versions table; Crockford base32 has no ambiguous characters when an editor reads an ID off a URL.

The acknowledged trade-off: the 48-bit timestamp leaks the creation millisecond, accepted for this threat model. Tests mock the generator for deterministic fixture IDs.

Unix-epoch INTEGER seconds for timestamps

created_at, updated_at, and changed_at are INTEGER Unix-epoch seconds. SQLite's built-in unixepoch() is the column default, so application code that omits the value still gets a correct timestamp.

created_at INTEGER NOT NULL DEFAULT (unixepoch()),
updated_at INTEGER NOT NULL DEFAULT (unixepoch())

Where application code sets a timestamp (for example entity_versions.changed_at on a versioned write) it uses the matching TypeScript helper nowSeconds() in src/shared/db/time.ts. Conversion to a human-readable date happens only at the boundary — API serialisation and admin rendering. The schema and the repository always speak in epoch seconds.

This makes time-travel a plain integer comparison: reconstructing site-wide state at a target timestamp is a single grouped query over entity_versions WHERE changed_at is at or before the target. No datetime casts, no timezone reasoning, and INTEGER indexes stay small. Storage is eight bytes per timestamp instead of twenty-plus for ISO text — material in the largest-growth table in the schema.

Considered and rejected

• UUIDv4 for IDs — not time-sortable, forcing a created_at index on every table that wants creation-ordered listings.
• UUIDv7 for IDs — the closest alternative; same shape but ten characters longer, and ULID had broader Workers-compatible packages. A switch would be a format rename, not a schema change.
• KSUID for IDs — functionally near-identical to ULID with a smaller ecosystem and no positive case over it.
• INTEGER milliseconds for timestamps — finer granularity, but loses the free unixepoch() default and would force every insert to set the value explicitly; entity_versions already orders by version_number per entity.
• ISO 8601 TEXT timestamps — readable in raw queries but 2.5x larger, needs datetime() casts for correct ordering, and readability is solved at the boundary by the admin UI.

The entity_versions table these conventions optimise for is written by one path only — see ADR 0007 on versioned writes.