Later that day, I learned of a non-technical person crafting a product using only LLMs. Hearing they got hacked did not shock me. I even saw a post where someone had overwritten a couple of months of work done by an LLM! They could have averted that whole mess had they known about version control 😂.

I am incredibly bullish on the LLM trend. Leveraging different LLMs in distinct scenarios is the new literacy. Getting the most out of them requires concrete knowledge. And understanding what you do not know is crucial!

This story shows the pitfalls of vibe-coding with primary keys.

The Task

Let’s start with a simple task.

Imagine we need to build a system for managing monthly passes for a public transport company. We will vibe-code an MVP based on some initial agreement. We will rework the MVP once we have agreed upon additional requirements. I am sure some of you have gone through similar processes many times 🤓.

If you are interested in my other story regarding a different transport problem, you can take a look here:

Engineer’s Notes: QR Codes & Stress-Free Travel

I put the following prompt into two distinct LLMs with reasoning. Let’s call them LLM A and LLM B.

I am designing a distributed system for managing monthly passes for a public transport company. Can you prepare the database skeleton using SQL?

LLM A provided primary keys with the serial pseudo type, while the other proposed the integer type with automatic incrementation.

-- the response from the LLM A only, edited for brevity
CREATE TABLE Customers (
    customer_id SERIAL PRIMARY KEY
    -- other fields
);

CREATE TABLE MonthlyPasses (
    pass_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    -- other fields
    FOREIGN KEY (customer_id)
        REFERENCES Customers(customer_id)
);

CREATE TABLE Transactions (
    transaction_id SERIAL PRIMARY KEY,
    pass_id INT NOT NULL,
    -- other fields
    FOREIGN KEY (pass_id)
        REFERENCES MonthlyPasses(pass_id)
);

First of all, what’s the difference? There is no one SQL standard; the language has different flavors! From all the flavors, LLM A implicitly decided to use PostgreSQL, LLM B didn’t, and I chose to be salty about them 😤.

The serial pseudo type enforces a sequence of natural numbers without zero. However, the Postgres maintainers seem to have deprecated it in favor of the identity columns.

So, the LLMs proposed solutions with sequenced integers. Do I agree with them?

No, I don’t!

The solutions would work, but they wouldn’t be efficient long-term when the system grows in users and needs to rely on standard properties of distributed systems 🤷‍♂️.

Why am I not happy with sequenced integers?

Firstly, if someone registers as a user and receives an ID of 1234, they know the company has that many customers. For a public transport company, this might be acceptable. After all, not all customers pay for their monthly passes every month.

However, knowing the ID of a recently purchased monthly pass might give off the presumed revenue! Access to such information in real-time might not be permissible even for a public transport company.

Secondly, someone can peek at previously registered users with sequenced integers as IDs. It usually means accessing data by exploiting vulnerabilities; knowing IDs beforehand makes it easier.

In my experience, transactions that incremented the sequence but otherwise failed do not restore that sequence during the rollback 🥲! This results in tables full of gaps, where you might think someone has removed some rows, while in reality, they were never there!

Additionally, you would need to wait for the database to give you the entity ID. If your business logic relies on subsequent operations with that ID, you cannot execute them in parallel.

For instance, if you register a user, you cannot immediately write their user photo into external storage under a fixed path containing the user ID without knowing it.

What happens if you accidentally send multiple requests with the same request body? Without locking mechanics or additional DB constraints, you could create duplicate users, monthly passes, etc.

While operating on sequenced numbers, you cannot tell if a particular ID corresponds to a user or a monthly pass because it contains no information. This creates a potential for buggy code, and I’m pretty sure LLMs would not think of this use case.

Furthermore, the sequences are separate from one another. You cannot prove that a monthly pass ID belongs to a particular user if you have both IDs. It might seem like overengineering, but if you can calculate proofs, you could save a lot of requests to the database!

Lastly, if you had integration tests, the IDs of entities would change upon each run, making debugging hard!

Before discussing my solutions, I want to highlight the benefits of using sequenced integers 😏.

They are short and thus easy to remember and copy, and they do not blow up the database indices.

My solutions

We need to make a rational decision about the chosen solution. We already know what we don’t want and why: sequenced integers. What topics have we not discussed?

Idempotent IDs

Do we want our IDs to be random or idempotent?

I usually choose idempotence unless I have a specific reason not to.

If we derive idempotent IDs from the data that is the source of truth, by definition, the same data sets make up identical IDs. As mentioned, this allows us to allow only one request with the same body, ensures stable tests, and enables parallel operations if they require an ID!

We can create idempotent IDs using hash functions. An example of such an algorithm is SHA-256 or RIPEMD-160. A considerable alternative is the UUIDv5 standard. You should decide based on realistic factors concerning at least collision space and usability. You could use LLM to understand the issues in your use case.

You must feed the algorithm with stable data, like a string or a buffer.

If you run an algorithm over a JSON, you must ensure the serializer sorts the properties. An example of a library that enforces stability is json-stable-stringify. You cannot use objects with circular references, and you should always select the minimal subset of the data that fully describes an entity! Please take a look at the exemplary code below.

import {
    createHash,
} from 'node:crypto';

import stableStringify from 'json-stable-stringify';

const calculateHashDigest = (obj: unknown): string | null => {
    const s = stableStringify(obj);

    if (s === undefined) {
        return null;
    }

    return createHash('sha256')
        .update(s)
        .digest('base64url');
};

For instance, a user might provide a residence address, but you shouldn’t use that to create an ID. They can change where they live at any time 😏.

In our case, I would choose idempotence for user and monthly pass IDs. The volatile entities, like transaction IDs, require some randomness.

Random IDs

If you need random IDs, you can generate a sequence of random bytes of a chosen length. You should determine the length by considering your use case and collision space. Alternatively, you could use UUIDv4.

Orderable IDs

Do we need to sort the IDs by their creation time? Or can we rely on creation timestamps written to separate columns?

Let me ponder upon the pros and cons 🤔.

If we put the creation time into the IDs, each would have to start with a few bytes indicating that time. The number of bytes depends on the chosen precisions (days, minutes, seconds). If we had a creation timestamp column, we could drop it and extract that information from the ID.

On the other hand, this pattern does not work with idempotence. I could argue that we would expose the timestamp in the ID to the outside world, but I do not see any attack vector unless we write milliseconds in. With milliseconds, we could expose the system to fingerprinting and timing attacks.

What are the industry standards for orderable IDs?

I would mention Snowflake ID and UUIDv6. Unlike other solutions, snowflakes are 64-bit integers represented as characters. UUIDv6 binds the timestamp and the machine’s MAC address together.

In our case, I would consider it for transaction IDs. However, the client should make the final decision 😉.

Derived IDs

Do we need to derive an ID from another ID? In other words, do we want some entities to hold IDs created from IDs of other entities?

Imagine two users asking for a monthly pass with the same configuration. If we use only the configuration to calculate idempotent pass IDs, we will have the same ID for two different passes 😑.

We have two choices:

1. Prepend the calculated ID with the user ID and consider the pass ID.
2. Calculating the pass ID with the user ID fed into the hash algorithm.

Let’s discuss the pros and cons of each solution.

If each pass ID starts with the fixed-length user ID, we don’t need to create another column and index to host the user ID. If we want to fetch passes belonging to a particular user, we can use the index associated with the primary key. We will ask for IDs starting with the user ID.

Whoever interacts with such a pass ID will immediately know to whom it belongs. Because of the design requirements, we need no additional verification if it belongs to someone 😏.

On the other hand, such an ID will occupy significantly more space than the regular one, impacting the index size and query speed.

If we require a user ID when feeding the data to calculate the pass ID, we end up with the normal-length ID. Unlike the previous solution, we have to add a column to track to whom the pass belongs.

In our case, I would require the input of other stakeholders to reach a decision.

UUID

You might be confused by the different versions of UUID that I mentioned in this story.

In general, all UUIDs have 128 bits represented over 36 ASCII characters. It is a design choice, you need to transform 128 bits (16 bytes) into 32 characters using hexadecimal representation. Additionally, we require four hyphens put in fixed places, growing the number of characters to 36.

For instance, the following string is a random UUIDv4:

27cdda48-f537–4d33–969f-d2877992d567

Different standards reserve different numbers of bits for encoding versions, up to 4 bits.

You could represent the same amount of information using just 22 characters with the base64url conversion, which makes me not inclined to use it 🙃.

Subjectively, the most important versions are:

1. UUIDv4 — producing a random string,
2. UUIDv5 — producing a hash digest,
3. UUIDv6 — producing an orderable string from a timestamp and a MAC address.

My take

What I wrote in this story is not secret knowledge. I showed that there are other viable solutions when it comes to primary keys. If you had known the other options existed, you could have made a different LLM prompt and arrived at results similar to those I proposed 😉.

Initially, I mentioned that fundamental architectural choices may impact future refactorings. If we built the MVP with sequential integer primary keys, any paradigm changes would take a lot of time.

For instance, introducing UUID v5 fields would require changing the backend models, fixtures, parsers, and tests, not to mention schema and data migration on all environments. We would also have to change the parsers on the frontend.

If that sounds easy, you probably did not consider the opportunity costs. Even after the refactoring, the code would not wholly reap the benefits of calculating the UUID immediately on the data 😏.

If the code waited on the DB to get the entity ID to do something else, you would have to find and parallelize such spots. If the code had a locking mechanism preventing identical requests from creating many entities, you would have to make it obsolete. LLM would not help without the proper context because such code is correct. It is just not required from an architectural standpoint!

Imagine you must revise a dozen design decisions, eventually requiring rewriting the MVP 🙃.

A better prompt

Can you get better results with a refined prompt? Yes, it doesn’t require much effort, but you must be specific about the requirements. Please check the example below.

I am designing a distributed system for managing monthly passes for a public transport company. Can you prepare the database skeleton using SQL? Ensure the system will scale for tens of thousands of users simultaneously. Avoid data duplication for entire rows. Remove all possible points of congestion and explain your decisions.

I added three sentences that provide adequate context. People without much experience would not ask to remove congestion points, as this requirement is particular. As you can see, informing the LLM that we are building a distributed system is insufficient.

-- an exemplary response from the LLM, edited for brevity
CREATE TABLE users (
    user_id UUID PRIMARY KEY
    -- other columns
);

CREATE TABLE monthly_passes (
    pass_id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    -- other columns
    CONSTRAINT fk_user
        FOREIGN KEY (user_id)
        REFERENCES users(user_id)
);

CREATE TABLE transactions (
    transaction_id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    pass_id UUID,
    -- other columns
    CONSTRAINT fk_user_tx
        FOREIGN KEY (user_id)
        REFERENCES users(user_id),
    CONSTRAINT fk_pass_tx
        FOREIGN KEY (pass_id)
        REFERENCES monthly_passes(pass_id)
);

You can run it using an LLM of your choice and see the results. In my case, I got three tables for users, monthly passes, and transactions, each with a UUID primary key. The returned rationale made sense, as the LLM stated that multiple nodes can generate such IDs independently 😎.

I hope you enjoyed reading the story, and it allowed you to see an alternative point of view on the vibe-coding phenomena. If you are interested in the nitty-gritty details of the primary keys, you can check out my other story:

Primary Keys in Distributed Systems

Thank you!

Why will vibe-coding hurt your primary keys? was originally published in Level Up Coding on Medium, where people are continuing the conversation by highlighting and responding to this story.