Suppose your API serves an order at /orders/1042. It works and the tests pass. Then the URL leaves your hands.
A curious user changes 1042 to 1041 and presses Enter. A competitor places one order on Monday and another on Friday, subtracts the two numbers, and now knows your weekly volume. Another team copies 1042 into their own table, and a row number that only meant something inside one database becomes part of somebody else's data.
None of that is a bug in the URL. The bug is asking one column to do two different jobs. On a platform of thirteen NestJS services I split those jobs: every row the outside world can point at gets a bigint primary key for the database and a UUIDv7 for everything else.
What does /orders/1042 leak?
Three things, and only the first is about security.
Your neighbours. A sequential id invites counting. If one endpoint forgets an ownership check, a loop finds it before any pentester does:
for id in $(seq 1 5000); do
code=$(curl -s -o /dev/null -w '%{http_code}' -H "Authorization: Bearer $TOKEN" "$API/orders/$id")
[ "$code" = 200 ] && echo "readable: $id"
doneYour volume. Two ids and two dates give you a growth rate. Estimating a total from serial numbers is the classic German tank problem, and your order counter is the serial number.
Your storage layout. Once another service stores 1042, you can no longer renumber, merge two databases or move rows between shards without breaking its references. The id used to be an implementation detail. Now it's a contract.
There's a quieter fourth problem: 1042 doesn't exist until the database hands it out. Code that needs the id before the insert, say for an event, has to wait for that round trip.
What is an ID actually for?
Think of a hotel.
Inside, everything runs on room numbers. Housekeeping lists, minibar charges and maintenance tickets all say "412". Room numbers are short, sequential and physical: 412 is next to 413, and staff walk straight to it.
Outside, you get a booking reference. The website issues it before any room is assigned, and it goes into your confirmation email, to the airport shuttle and to your travel agent. If a pipe bursts in 412 and they move you to 518, your booking reference stays the same.
Print room numbers on the confirmations instead and you get every problem above: guests knocking on 411, strangers counting rooms, and a renovation that breaks every booking.
A database identifier has the same two jobs, and they want different things:
| Requirement | Join key (room number) | Public handle (booking reference) |
|---|---|---|
| Size | as small as possible | small enough |
| Order of new values | strictly increasing is ideal | roughly increasing helps |
| Who creates it | one central counter is fine | anyone, before the row exists |
| Can outsiders count it? | doesn't matter | must not |
| Survives storage changes? | doesn't matter | must |
No single type wins every row of that table, so I stopped asking one column to carry both.
Why not hash the bigint, or use UUIDs everywhere?
Three fixes come up every time, and each solves only part of it.
Encode the bigint with Hashids, Sqids or a reversible cipher. The URL stops looking like a counter, but a counter is still underneath: one central sequence, ids that exist only after the insert, a public value welded to a storage key. Change the encoding and every old link breaks.
UUIDv4 everywhere. Unguessable and mintable anywhere, but fully random, so every insert lands at a random spot in the index. That gets expensive once the index outgrows memory. And every foreign key is now 16 bytes.
UUIDv7 everywhere. Honestly fine for small services. But it doubles the width of every internal foreign key and its indexes, and it makes the public handle and the join key the same column again.
The platform's conventions settle it in one line: local primary and join keys are BIGINT; public and cross-service references are application-generated UUIDv7 values.
What does "bigint inside" look like in a schema?
Here's an addressable table and one of its internal children in Prisma, trimmed to the fields that matter:
model Order {
id BigInt @id @default(autoincrement())
publicId String @unique @db.Uuid
orderNumber String @unique @db.VarChar(40)
tenantPublicId String @db.Uuid
customerPublicId String? @db.Uuid
version Int @default(1)
createdAt DateTime @default(now()) @db.Timestamptz(3)
history OrderStatusHistory[]
@@index([customerPublicId, createdAt(sort: Desc), publicId(sort: Desc)])
}
model OrderStatusHistory {
id BigInt @id @default(autoincrement())
orderId BigInt
toStatus String @db.VarChar(30)
createdAt DateTime @default(now()) @db.Timestamptz(3)
order Order @relation(fields: [orderId], references: [id])
}Read it as the hotel:
idis the room number: 8 bytes, only goes up, used by every child row.OrderStatusHistory.orderIdis 8 bytes too, and so is every entry in an index on it.publicIdis the booking reference. PostgreSQL's nativeuuidtype stores it as 16 binary bytes, which RFC 9562 recommends over 36 characters of text.orderNumberis a third id, for people reading it aloud. It's never a join key.tenantPublicIdandcustomerPublicIdare other services' booking references: indexed UUIDs with no foreign key, because the rows they point at live in another database.OrderStatusHistoryhas nopublicIdat all. Nobody outside points at one history row; they read history through its order.
That last rule matters most. A table gets a public handle only when something outside needs one of its rows. In the order service's schema, 43 of 50 tables have a bigint id, and only 23 carry a publicId.
One PostgreSQL detail keeps this honest: unlike InnoDB, its secondary indexes point at a row's physical location, not at the primary key. A wide key costs you in its own index, in the foreign keys that reference it and in their indexes, not everywhere. With dozens of child tables, that's still plenty.
What is UUIDv7?
A UUIDv7 is a 128-bit UUID whose first 48 bits are a Unix timestamp in milliseconds. RFC 9562 lays it out like this:
48 bits 4 bits 12 bits 2 bits 62 bits
unix_ts_ms | ver | rand_a | var | rand_bBecause the timestamp comes first, an id minted in a later millisecond sorts after one minted earlier. The other 74 bits are random, so nobody can count them. It's a booking reference that starts with the date: unguessable, but filed in time order.
Here's the shared generator, minus its input checks:
import { randomBytes } from 'node:crypto';
export function createUuidV7(now = Date.now()): string {
const bytes = randomBytes(16);
for (let i = 0; i < 6; i += 1) {
bytes[i] = Number((BigInt(now) >> BigInt((5 - i) * 8)) & 0xffn); // 48-bit ms, big-endian
}
bytes[6] = (bytes[6] & 0x0f) | 0x70; // version 7
bytes[8] = (bytes[8] & 0x3f) | 0x80; // RFC 9562 variant
return format(bytes);
}
function format(bytes: Buffer): string {
const hex = bytes.toString('hex');
return `${hex.slice(0, 8)}-${hex.slice(8, 12)}-${hex.slice(12, 16)}-${hex.slice(16, 20)}-${hex.slice(20)}`;
}There's no counter, so two ids from the same millisecond sort randomly against each other. Nothing relies on strict order: lists sort by createdAt and use publicId only as a tie-breaker, which is what the composite index in the schema is for.
The price of putting time first is that anyone holding the id can read it:
const createdAt = (id: string) => new Date(parseInt(id.slice(0, 8) + id.slice(9, 13), 16));
createdAt(createUuidV7()); // now, to the millisecondUsually harmless, but not always.
Why does the application mint the ID instead of the database?
Because the id has to exist before the row does.
When the order service creates an order, it writes the order, its saga record and an outbox event in one transaction, and the event is keyed by the order's public id. If the database minted the id, the code would have to insert, read the id back and only then build the event. Minting it first means the row, the event and the response share one value from the top of the handler. The transactional outbox article covers the rest of that transaction.
There's a practical reason too. The repository's Compose stack and CI run PostgreSQL 17, which has no built-in UUIDv7 generator; uuidv7() arrived in PostgreSQL 18. RFC 9562 notes that one monolithic database can give the best ordering, but with thirteen databases and a browser that mints its own UUIDv7 idempotency keys, that advantage doesn't apply.
What is a derived UUIDv7, and when do you want one?
Some ids shouldn't be random at all. When the order outbox gives up on an event, it writes a dead-letter event. If that dead letter gets a random id and the failure path runs twice, you get two dead letters for one failure.
So the shared library has a second function: the same source id plus the same label always produce the same UUIDv7.
import { createHash } from 'node:crypto';
export function createDerivedUuidV7(source: string, discriminator: string): string {
const bytes = createHash('sha256').update(`${source}-${discriminator}`).digest().subarray(0, 16);
Buffer.from(source.replaceAll('-', ''), 'hex').copy(bytes, 0, 0, 6); // keep the source's timestamp
bytes[6] = (bytes[6] & 0x0f) | 0x70;
bytes[8] = (bytes[8] & 0x3f) | 0x80;
return format(bytes);
}
// In the order outbox: one dead letter per failed event, however often this runs.
const deadLetterId = createDerivedUuidV7(event.eventId, deadLetterEventType);At the hotel, it's the late-checkout request for booking K7QX2M, always filed as K7QX2M-LATE. File it twice and the duplicate is obvious. In the database, a unique constraint does the noticing. A projection derives admin-notification ids from incoming event ids the same way, so a replay can't notify anyone twice.
Copying the first 48 bits from the source puts the derived id right next to its parent in the index. Keeping the version at 7 means every z.uuidv7() check in the system accepts it. A purist would say RFC 9562 expects the remaining bits to be random, so a hash-filled UUID is closer to version 8. Fair; I chose validator compatibility over purity.
A derived id is predictable by design, which is fine only because no id here is treated as a secret. More on that shortly.
Why do random UUIDs hurt a B-tree index?
A B-tree keeps its keys sorted across leaf pages, and where a new key goes depends only on its value.
Picture the hotel's archive of booking slips, filed in order across a hundred drawers. With random references, today's bookings go into drawers all over the room. With references that start with the date, they all go into the last drawer, which sits on the clerk's desk.
UUIDv4 is the first archive. Each insert lands on a random leaf page, so once the index outgrows memory, most inserts first wait for a page read from disk. Full pages split roughly in half. More distinct pages change between checkpoints, so more full-page images go into the write-ahead log. The working set is the whole index.
UUIDv7 is the second archive. New values are larger than almost everything in the index, so inserts pile onto the rightmost leaf pages, which stay in cache. When PostgreSQL extends a B-tree at the right edge, it packs leaf pages to the fillfactor, 90 percent by default, instead of halving them. The working set is the right edge.
RFC 9562 says the difference "can be one order of magnitude or more". I haven't benchmarked it on this platform, so treat my case as structural. You can see the shape yourself on PostgreSQL 18:
CREATE EXTENSION IF NOT EXISTS pgstattuple;
CREATE TABLE ids_v4 (id uuid PRIMARY KEY);
CREATE TABLE ids_v7 (id uuid PRIMARY KEY);
INSERT INTO ids_v4 SELECT gen_random_uuid() FROM generate_series(1, 1000000);
INSERT INTO ids_v7 SELECT uuidv7() FROM generate_series(1, 1000000);
SELECT 'v4' AS kind, index_size, avg_leaf_density FROM pgstatindex('ids_v4_pkey')
UNION ALL
SELECT 'v7', index_size, avg_leaf_density FROM pgstatindex('ids_v7_pkey');Compare leaf density and index size. That's page shape, not I/O; the I/O cost only appears once the index no longer fits in memory. And the damage stays in the indexes: a PostgreSQL heap isn't sorted by any key, so a random id doesn't scatter the table itself.
Where do the public ID and the bigint meet?
Public id in, public id out. The bigint never leaves the repository.
Follow GET /orders/{orderId}. The gateway passes the path through (routing is covered in REST at the edge, gRPC and Kafka inside). The controller rejects anything that isn't a UUIDv7 before a query runs:
export const orderPublicIdSchema = z.uuidv7();
@Get(':orderId')
@Authenticated()
async get(
@ZodParam('orderId', orderPublicIdSchema) orderId: string,
@CurrentUser() user: AuthenticatedUser,
) {
return this.orders.getForActor(orderId, user);
}The service loads by public id, then checks who's asking. Condensed:
async getForActor(orderId: string, actor: AuthenticatedUser): Promise<OrderData> {
const order = await this.orders.findByPublicId(orderId); // unique index on publicId
const ownsOrder = order?.placedByAccountPublicId === actor.sub;
if (!order || !(ownsOrder || this.hasScopedReadAccess(order, actor))) {
throw new NotFoundException(ORDER_ERROR.NOT_FOUND);
}
return mapOrderData(order); // the response's orderId is publicId; id is never copied
}The lookup uses the unique index on publicId, and joins to child rows run on bigint foreign keys that nobody writes by hand. The ownership check compares public id with public id: the order's account reference against the JWT sub. And an order you may not see returns 404, not 403, because a 403 would confirm the id exists.
Across services, the rule moves up a level. customerPublicId has no foreign key; it's validated at the contract boundary and reconciled through events or jobs, since each service owns its database (see database per service and sagas). Kafka events carry the order's public id as aggregateId, and the relay uses it as the message key unless the event sets another partition key.
Is an unguessable ID enough to protect a resource?
No. RFC 9562 says implementations should not assume UUIDs are hard to guess and must not use them as security capabilities, identifiers whose mere possession grants access. Ids leak through screenshots, logs and support tickets, and derived ids are predictable on purpose.
The platform's decision record is blunt: non-sequential ids make enumeration harder but don't prevent insecure direct object references (IDOR); authorization is the control. OWASP's IDOR cheat sheet agrees. At the hotel, the receptionist still checks your passport.
What does the second ID cost?
- Two unique indexes on every addressable table, the UUID one twice as wide. Every insert maintains both.
- One extra index probe per read to resolve the public id before any join can use the
bigint. - Discipline. Someone will eventually return
idin a response. Naming helps:idis internal,publicIdis ours to share,customerPublicIdbelongs to someone else. JavaScript helps by accident: Prisma returnsBigIntcolumns asbigint, andJSON.stringifythrowsTypeError: Do not know how to serialize a BigInt, so a strayidtends to fail loudly in development. - The timestamp is public. If creation time is sensitive for some resource, give it a UUIDv4 and accept the index cost.
- Cross-service integrity becomes eventual. Validation and reconciliation replace the foreign key, and both are code you write and test.
When is one ID enough?
- One application, one database, no public API: use
bigintand skip the second column. - A small service with a few tables whose ids travel: UUIDv7 as the only primary key is perfectly reasonable.
- Tables nothing joins to: even here, the inbox table that records consumed events is keyed by the incoming event's UUID alone.
What would I do differently?
The decision held up. The rollout is where I'd change things, and the decision record itself calls adoption partial.
Delete the v4 default. The identity service never got the split: more than half of its tables use a UUID primary key, so an account's id is both room number and booking reference. Those columns still say @default(uuid()), which in Prisma generates a random v4. Every path that creates an account now passes createUuidV7() explicitly, but the default remains a silent fallback. Forget the id in one new path and a v4 lands in a column the conventions say is v7, with no error. Without the default, Prisma makes id required in the create input, and forgetting it becomes a compile error.
Validate at one door. Validation is per route. In the same order controller, most routes use @ZodParam('orderId', z.uuidv7()) and one parses the path by hand. The decision record forbids claiming the gateway validates every path id until integration tests prove it. I'd add one shared validation step at the edge from the first endpoint.
Brand the types. orderPublicId: string and customerPublicId: string look identical to the compiler. A branded PublicId<'Order'> makes mixing them a compile error: cheap on day one, tedious to retrofit.
Pin the two generators and measure. The backend and the browser each carry their own UUIDv7 function, so one contract test should run against both. And I'd run the index experiment against a realistic schema before writing "faster" in any design doc.
Finally, keep createdAt. The time inside a UUIDv7 comes from whichever machine minted it. It's a locality hint, not a clock.
Key takeaways
- An identifier does two jobs: a join key inside and a handle outside. Give each its own column once a service has many joins and its ids travel.
bigintinside: 8 bytes, increasing, never leaves the repository.- UUIDv7 outside: inserts stay at the right edge of the index, outsiders can't count it, and the application can mint it before the row exists.
- Derive the id deterministically when a retry or replay must produce the same id.
- An id is never a permission. Check access on every read and answer 404 for what the caller can't see.
- Remove defaults that silently mint the wrong kind of id.
The version column in that schema is where the next part starts: idempotency keys and optimistic locking for safe retries.