# Natural Key or Surrogate Key? The First Real Fork in the CVEs Table

Designing the `cves` table, I hit the first real decision that wasn't obvious: what's the primary key?

CVE IDs look like a gift. They're unique, human-readable, and already assigned by someone else. `CVE-2026-86793` tells you more at a glance than any random string ever could. Using it as the primary key felt like the default answer, and defaults deserve a second look before they go into a schema.

## The case for the natural key

It's simpler. There's one less column and one less thing to explain. When you `SELECT` from a join table, the IDs mean something to you without a lookup. Plenty of well-built schemas do exactly this.

## Why I didn't

NVD data isn't perfectly static. Entries get rejected. Entries get renamed. Rarely, but it happens. And a primary key is the one value everything else in the database points at. If the thing being pointed at can change, every reference to it has to change with it.

So I split the two jobs:

```sql
CREATE TABLE cves (
    id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    cve_id  TEXT UNIQUE NOT NULL,
    ...
);
```

`id` is the internal identity, generated by the database, meaningless on purpose. `cve_id` is the label the outside world uses, still unique, still required, just no longer load-bearing.

## Where it paid off

The `UNIQUE` on `cve_id` isn't decoration. The ingestion service's upsert runs on it:

```sql
ON CONFLICT (cve_id) DO UPDATE SET ...
```

Re-run the sweep and existing CVEs refresh instead of duplicating, and the surrogate key never notices. Two keys, two jobs.

It also set up the tables that came later. `alerts.related_cve_id` and the advisory join table both point at `cves(id)`, the UUID. Those edges are the actual graph, and none of them depends on what NVD decides to call something next year.

## The honest cost

It isn't free. Every join shows a UUID instead of a readable ID, so a human debugging in `psql` has to hop back through `cves` to see which CVE a row means. The advisory service has to resolve natural IDs to UUIDs before it can create an edge. That's a real extra step, and I'm paying it on purpose.

Slightly more setup. Zero fragility if an ID ever changes under me. I'll take that trade.