Five tables in Munchable have a text primary key that Postgres does not generate. The schema says so, in as many words:

// ULID, generated app side (lexically sortable by time). No DB default. id: text('id').primaryKey(),

Support tickets, the messages on them, reward settlement rows, taxonomy entries and product revisions. Here is the whole generator.

const ENC = '0123456789ABCDEFGHJKMNPQRSTVWXYZ'; export function ulid(now: number = Date.now()): string { let ts = ''; let t = now; for (let i = 0; i < 10; i++) { ts = ENC[t % 32] + ts; t = Math.floor(t / 32); } const bytes = crypto.getRandomValues(new Uint8Array(16)); let rand = ''; for (let i = 0; i < 16; i++) rand += ENC[bytes[i] % 32]; return ts + rand; }

Ten characters of millisecond timestamp, sixteen of randomness, twenty-six total. There is a perfectly good ulid package and this does not use it, which is a judgement call rather than a principle: the dependency would be fine, and twenty lines I can read in full are also fine, and for a value that appears in a public URL I would rather the second thing.

Four details in those lines are load-bearing.

The alphabet is Crockford base32, and it matters that it is sorted. 0-9 then A-Z with I, L, O and U removed. Removing those four is the famous part, because they are what makes a code read aloud over the phone ambiguous. The part that matters here is that the string is in ascending character order, which is why a plain byte-wise sort of two ULIDs is a sort by the time they were made. Postgres order by id needs to know nothing about the format.

Ten characters is 50 bits for a 48-bit timestamp. Date.now() fits with room to spare, and the loop builds the digits from least significant backwards so the most significant lands first and the lexical order is the numeric order.

Sixteen bytes in, sixteen characters out, 80 bits of entropy. Each byte contributes five bits and three are thrown away, which looks wasteful until you notice the alternative. % 32 is normally the bug in code like this, because the usual modulo over a range that does not divide the byte space skews the distribution towards the low values. 256 is exactly eight times 32, so every character is equally likely and there is no bias to correct. It only works because both numbers are powers of two, which is worth a comment in any code that does it on purpose.

There is no monotonic counter. The spec describes an optional factory that increments the random half when two ULIDs land in the same millisecond, so they sort in creation order even then. This one does not have it. Two ids made in the same millisecond sort relative to each other at random, which I will come back to, because something does depend on it and it turns out not to need it.

Why not a database default

The obvious question: Postgres can generate ids, so why is the app doing it? Two reasons, and the second is the real one.

The id is needed before the insert, because it is written into other rows in the same transaction. Opening a support ticket writes a ticket and its first message together:

export async function createTicket(input: CreateTicketInput): Promise<CreatedTicket> { const id = ulid(); const threadToken = crypto.randomUUID().replace(/-/g, ''); // ... await db.transaction(async (tx) => { await tx.insert(supportTickets).values({ id, /* ... */ threadToken }); await tx.insert(supportMessages).values({ id: ulid(), ticketId: id, /* ... */ }); }); return { id, ref: ticketRef(id), subject, email, threadToken, hasAccount: input.userId !== null }; }

You can do this with a returning clause and a default, of course. But the caller also needs the id to build the URL it emails out and the reference it prints, and once two or three things in one function need a value that the database is the authority on, having the application be the authority instead removes a round trip and a class of "which of these is the real id" confusion.

The honest cost: Postgres does not validate the format. A bad migration could put anything in that column and the database would accept it. I know of no way to have both, short of a check constraint describing base32, and that trade reads differently in a codebase with one writer than it would in a larger one.

The part worth the post

A sortable id leaks its creation time by construction. Anybody holding one can read the first ten characters as a timestamp. That is a feature in a log and a problem in a URL, and these ids are in URLs: a ticket is readable at /support/<id> by the account that owns it.

So the id cannot be the thing that authorises reading the ticket, and more interestingly, it cannot be the thing that authorises writing to it. Inbound email replies thread onto a conversation by a plus-addressed recipient, and that address needs a token:

/** * Opens a ticket with its first message, in one transaction. The ticket id is a * ULID so the admin queue's keyset tiebreak sorts in creation order, and the * thread token is a separate random value: the id appears in URLs the user can * see, so it must not double as the secret that authorises threading an inbound * email onto the conversation. * * Returns the thread token to the CALLER (not to any client) because the route * needs it to build the Reply-To address of the acknowledgement email. */

Two identifiers, one row, different jobs. The ULID is public, sortable and shown to people. The thread token is a UUID with the dashes stripped, is never returned to a client, and exists only to be the local part suffix of a Reply-To address. Collapsing them into one value would have been the obvious economy, and it would have meant that seeing a ticket URL in a screenshot was enough to inject a message into that conversation by email.

The matching check on the way in refuses everything that is not exactly our mailbox:

export function threadTokenFromAddress(address: string): string | null { const match = /^([a-z0-9._-]+)\+([a-z0-9]{16,64})@(.+)$/i.exec(address.trim().toLowerCase()); if (!match) return null; const [, local, token, domain] = match; if (local !== SUPPORT_LOCAL || domain !== SUPPORT_DOMAIN) return null; return token; }

Shape, local part and domain all have to agree before the token is even looked up, because mail arrives at a catch-all and the recipient header is attacker-chosen.

The reference code is six characters of the random half

People do not read twenty-six character ids out loud, so emails and the admin panel show a short reference:

/** * The short human reference printed in emails and shown in the panel, derived * from the ticket id rather than stored. ULIDs are 26 characters of Crockford * base32; the last six are from the random half, so a reference is distinctive * without being a guessable handle for the ticket itself (the full id is still * required to read one). */ export function ticketRef(id: string): string { return `MU-${id.slice(-6).toUpperCase()}`; }

Derived, not stored, so it cannot drift from the id. Taken from the end rather than the start, because the first ten characters are the timestamp and two tickets opened in the same minute would share a prefix, which is the opposite of what a reference code is for. Six base32 characters is thirty bits, which is not a lot, and it does not need to be: nothing is ever looked up by it. It is for a human saying "this is about MU-7KQ2VB" while the system works from the full id.

Where the non-monotonic ids turn out to be fine

The admin queue paginates with a keyset, and the ULID is the tiebreaker:

// The queue is a compound keyset on (last_message_at DESC, id DESC). The id is // the unique tiebreaker: ordering on last_message_at alone would let two // tickets that share a millisecond straddle a page boundary and be skipped, so // a ticket could silently never appear in the queue at all. The cursor // therefore carries both halves. '|' is the delimiter; a ULID never contains one. function encodeCursor(lastMessageAt: Date, id: string): string { return `${lastMessageAt.toISOString()}|${id}`; }

This is the bug that keyset pagination exists to prevent, and it is worth being precise about why it is a bug rather than a cosmetic wobble. Order by a non-unique column alone, page by where col < :last, and two rows sharing a value at a page boundary can both be excluded: one was the last row of page one, the other is skipped on page two because it is not strictly less than the cursor. The row does not appear late. It never appears.

The tiebreaker needs to be unique and stable. It does not need to be in creation order, which is exactly what a ULID without a monotonic counter cannot promise within one millisecond. Two tickets opened in the same millisecond will page correctly and might be shown in either order relative to each other, and nobody will ever notice or care. That is the whole reason the twenty-line version is enough, and it is the kind of requirement worth writing down, because the next person to read "sorts in creation order" in that comment could reasonably go looking for the monotonic factory and conclude something is missing.

The delimiter note is the sort of thing I like finding in a comment. '|' is safe as a cursor separator because of the alphabet: Crockford base32 is digits and uppercase letters, so a pipe cannot appear in the id half, so splitting on the first one is unambiguous. Change the id format and that cursor silently becomes ambiguous, which is why the reason sits next to the code rather than in a commit message.

The pages with no ids at all

For contrast, the public half of the site has no generated identifiers anywhere. A recipe page lives at a slug, an ingredient answer lives at the question somebody would type, and both come from modules in the repository rather than rows in a table. Nothing there is created at a moment in time, so nothing there needs an id that remembers when. The about page covers who is behind all of it.