Put a UUID on the invoice before you need it

The claim

Give every business record a stable, public-safe identifier at the moment it is created, separate from its database primary key. The auto-incrementing integer is fine inside your database and dangerous everywhere else: it leaks your volume to competitors, it collides when you merge datasets, and it forces you to expose a number that means something to your internal counting. A UUID column costs one line in a migration and removes an entire category of future pain.

What the sequential integer tells the world

An order confirmation reading /orders/1042 tells any customer exactly how many orders you have taken. Place two orders a week apart, watch the number jump from 1042 to 1119, and you have just measured a competitor's weekly volume from their own confirmation email. This is not hypothetical — it is a standard step in competitive research, and the technique has a name: the German-tank estimation problem, solved during the Second World War by reading serial numbers.

The integer causes operational trouble too. When you acquire a company and merge their orders table into yours, every ID collides. When a staging export leaks into production, or a test row gets created in the wrong environment, the sequences drift and you can no longer trust that ID 5000 is the same record everywhere.

The pattern: two identifiers, two jobs

CREATE TABLE orders (
  id     bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  public_id uuid NOT NULL DEFAULT gen_random_uuid(),
  ...
);
CREATE UNIQUE INDEX orders_public_id ON orders (public_id);

The bigint stays internal: it is your foreign key target, small and fast to join on. The public_id is what appears in URLs, emails, API responses, and support tickets. Nothing outside your database ever sees the integer, so nothing outside your database can count with it.

Choose the UUID version deliberately

Random UUIDs (version 4) leak nothing, which is the point, but purely random values scatter across a B-tree index and hurt insert performance on large tables because each new row lands in a random leaf page. If you are generating millions of rows, UUID version 7 — timestamp-prefixed — keeps the privacy of a random suffix while remaining roughly time-ordered, so inserts stay local in the index:

-- Postgres 18 ships gen_uuid_v7(); on earlier versions,
-- generate v7 in the application and pass it in.

For most businesses the insert cost of v4 is invisible and v4 is the correct default. Reach for v7 only when you have measured index write amplification on a genuinely large table. Do not reach for a sequential integer disguised as a public ID — that reintroduces the exact leak you removed.

When you want short and friendly instead

A UUID is 36 characters and no human reads one over the phone. For records a customer quotes to support — order numbers, ticket numbers — a shorter opaque code reads better. Generate it from a defined alphabet that excludes ambiguous characters:

alphabet: 23456789ABCDEFGHJKLMNPQRSTUVWXYZ   (no 0/O, 1/I/L)
length 8 = ~1 trillion values

Excluding 0/O and 1/I/L is not cosmetic: it removes the transcription errors that generate support tickets. Store this alongside the UUID, not instead of it — the UUID guarantees uniqueness at scale, the short code is the human-facing convenience.

The rule that makes it safe to expose

A public ID being unguessable is not authorisation. Anyone with the link can try it, and if your endpoint returns the order to whoever presents the ID, you have built an access-control hole with a longer key. The public ID removes enumeration — nobody can loop from 1 to 10000 — but every endpoint must still check that the requesting user owns the record. Treat the identifier as a username, never as a password.

Retrofitting an existing table

You can add this to a table that already has millions of rows without downtime, using the expand-migrate-contract pattern: add the nullable column, backfill in batches, add the unique index concurrently, then start writing the value on new rows. The old integer-based URLs keep working during the transition because you have not removed anything — you have only added a second, safer way to address the same record.

ALTER TABLE orders ADD COLUMN public_id uuid;
UPDATE orders SET public_id = gen_random_uuid()
  WHERE public_id IS NULL AND id IN (SELECT id FROM orders WHERE public_id IS NULL LIMIT 5000);
-- repeat, then:
CREATE UNIQUE INDEX CONCURRENTLY orders_public_id ON orders (public_id);

The decision

Adding a public identifier to a new table costs one column and one index. Adding it to a five-year-old system that has leaked sequential IDs across ten thousand invoices, a public API, and a partner integration costs a migration, a compatibility layer, and a conversation with every integrator. The line goes in the first migration or it goes in the expensive one later. There is no version of this where waiting is cheaper.