The “Primary Key War” is one of those eternal battles in software engineering, right up there with tabs vs. spaces or monoliths vs. microservices. If you browse Hacker News long enough, you’ve seen the cycles: auto-incrementing integers are insecure; UUIDv4 ¹ fragments your indexes; Snowflakes ² require complex infrastructure.

Recently, the industry seems to have found a ceasefire ^{3 4 5 6 7} in UUIDv7 ⁸.

However, solving the database index problem is only half the battle. We still have to deal with the human problem. How do we make IDs copy-pasteable, distinct, and safe from transcription errors?

At Alcazar Security, we decided against exposing our primary keys directly. Instead, we architected a split-ID strategy: UUIDv7 for the database, and a Prefixed, Checksummed, Base32-encoded UUIDv4 for the API.

Here is how we arrived at our format: u_14wrt1xnprkj3nj7wtndz74037rkw

---

Part 1: The Database Layer (UUIDv7)

Let’s get the database storage out of the way first. If you are using PostgreSQL, you probably shouldn’t be using UUIDv4 as a primary key anymore.

Because UUIDv4 is purely random, inserts are scattered across the B-tree index. As your table grows larger than memory, you end up loading random pages from disk just to check uniqueness or insert a row, killing your write throughput (a phenomenon known as write amplification).

Enter UUIDv7. UUIDv7 embeds a timestamp in the first 48 bits, making it monotonically increasing. This allows B-tree indexes to behave like they are handling integers—appending new writes to the “right side” of the tree—while keeping the global uniqueness properties of a 128-bit space.

Decision: We use UUIDv7 for all Primary and Foreign Keys. It’s standard, index-friendly, and basically free performance optimization.

Why not expose UUIDv7 publicly?

If UUIDv7 is so great, why not send it to the frontend? Side-channel metadata.

UUIDv7 leaks the exact creation time of the resource. While often harmless, in a security or privacy-focused context, this metadata can be dangerous. It allows attackers or competitors to correlate user creation times with specific marketing campaigns or outages. We take the stance that public IDs should be opaque.

---

Part 2: The Public Layer (Stripe-style Prefixes)

We need a public ID that maps to the internal UUIDv7 but reveals nothing. A standard UUIDv4 stored in a separate column works, but raw UUIDs are DX (Developer Experience) poison.

Consider these two IDs:

1. f47ac10b-58cc-4372-a567-0e02b2c3d479
2. pi_3LKQhvGUcADgqoEM3bh6pslE (Stripe)

Stripe’s engineering team wrote the definitive guide on this: Designing APIs for humans: Object IDs ^{9 10}.

The Case for Prefixes

We adopted 2-3 character prefixes for all public resources (u_ for users, p_ for projects, in_ for integrations, …).

1. Human Readability: Developers can instantly identify the object type (e.g., cus_ for Customer) without checking a dashboard or database.
2. Easier Debugging: Prefixes prevent common errors, such as passing an Account ID where a Customer ID is expected, and simplify log analysis.
3. Polymorphic Lookups: They allow a single API endpoint to distinguish between different object types (like Cards vs. Sources) stored in different tables based on the ID’s prefix.

---

Part 3: The Formatting (Crockford Base32)

We have a prefix (u_) and a payload (128 bits of random data). How do we encode it?

Hexadecimal (standard UUID) is inefficient. Base64 is compact but contains special characters (+, /, =) that play poorly in URLs and file paths.

We chose Crockford’s Base32 ¹¹, largely inspired by ULID ¹².

Why Crockford’s Base32?

1. Human-Transcription Safe: It excludes the letters I, L, O, and U.
   • 1 vs l vs I is a nightmare in sans-serif fonts.
   • 0 vs O is a classic source of error.
   • (U is excluded to reduce the likelihood of accidental obscenity generation).
2. Case Insensitive: u_14WRT... and u_14wrt... are identical.
3. The “Double Click” Test: Try double-clicking a standard UUID: f47ac10b-58cc-4372-a567-0e02b2c3d479. Most operating systems select only one segment (e.g., 58cc). Now try our format: u_14wrt1xnprkj3nj7wtndz74037rkw. The whole string is selected. It sounds trivial, but it saves thousands of cumulative hours of developer frustration.

---

Part 4: The Twist (The Checksum)

We could have stopped there. prefix + base32(uuidv4) is a perfectly valid strategy. However, we realized that manual ID transcription (e.g., support tickets, shell scripts) was a vector for error.

We decided to burn 3 extra characters to append a redundancy check.

We calculate a checksum (using a CRC-16 ¹³ or Fletcher’s Checksum ¹⁴) of the binary payload and append it to the Base32 string.

The Cost: +12% length (3 characters). The Benefit: 99.998% detection of typos.

If a user types u_14wrt1xnprkj3nj7wtndz74037rkq (swapping the last char), our API doesn’t even hit the database. It rejects the request with 400 Bad Request: Invalid ID checksum. This prevents a class of bugs where a typo accidentally references a valid (but wrong) object, or—more likely—causes a database scan for an ID that doesn’t exist.

The Final Anatomy

Our “id_better” format looks like this:

ID = Prefix + Base32(UUIDv4) + CheckSum

Example: u_14wrt1xnprkj3nj7wtndz74037rkw

1. u_: Prefix. Tells us this is a User.
2. 14wrt...: Payload. A 128-bit UUIDv4 encoded in Base32.
3. rkw: Checksum. Ensures integrity.

Implementation details

In our PostgreSQL schema, the setup is straightforward. We don’t need fancy custom types; we lean on the standard uuid type for storage efficiency.

CREATE TABLE users (
    -- Internal PK: UUIDv7. Efficient B-tree sorting.
    id secret_uuid PRIMARY KEY DEFAULT uuid_generate_v7(),

    -- Public ID: UUIDv4. Random, unguessable.
    -- We index this for the API lookups.
    uuid uuid UNIQUE NOT NULL DEFAULT gen_random_uuid(),

    email varchar(255) NOT NULL,
    created_at timestamptz DEFAULT now()
);

The Application Layer

When the backend receives GET /users/u_14wrt1xnprkj3nj7wtndz74037rkw:

1. Strip prefix u_.
2. Verify checksum (if invalid, errors immediately).
3. Decode Base32 to standard UUID Hex.
4. Query: SELECT * FROM users WHERE uuid = decoded_uuid.

When the backend returns data:

1. Fetch uuid.
2. Encode to Base32 + Checksum.
3. Prepend u_.
4. Send JSON.

Summary

We believe IDs are a user interface, not just a database implementation detail.

By separating the Physical ID (UUIDv7 for DB performance) from the Logical ID (Base32 UUIDv4 for public exposure), we gain the best of both worlds. We keep our B-trees fragmented-free and fast, while giving our developers and users IDs that are readable, clickable, and typo-resistant.

It’s a little more work upfront than id: serial, but your future debugging self will thank you.