Store the Fact: Why Alerts Get Real Foreign Keys

The system I reviewed for reference computes CVE-to-CVE relationships on the fly, at query time. Nothing stored, everything derived when someone asks. For that use case it works fine.

For the alert layer in Radar, I went the other way, and the reason comes down to one question: is this relationship derived, or is it a fact?

The two kinds of relationships

Some relationships are opinions the data can re-form at any time. Two CVEs that share a weakness class and an affected product look related, and if the data changes, the answer changes. Computing that at query time is honest, because there's nothing to remember.

Other relationships are things that happened. When an alert gets triaged against a specific CVE, that's an event. Someone, or something, made a decision at a point in time. Recomputing it later means re-deriving a decision that already exists, and possibly getting a different answer than the one that was acted on.

What the table looks like

CREATE TABLE alerts (
    id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    raw_payload           JSONB,
    ...
    related_cve_id        UUID REFERENCES cves(id),
    related_technique_id  UUID REFERENCES techniques(id),
    severity              TEXT,
    status                TEXT NOT NULL DEFAULT 'new',
    created_at            TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

related_cve_id and related_technique_id are real foreign keys. Two details matter here:

• They're nullable on purpose. An alert arrives unlinked. Linking is the triage step's job, so "no link yet" is a valid state, not a missing value.

• The database enforces them. An alert can't point at a CVE that doesn't exist. The relationship is checked when it's written, not trusted when it's read.

Where I still compute

I didn't swap one rule for another. The Threat Graph's similarity edges (CVEs that share both a CWE and an affected product) are still computed at query time, because they are derived. The edges that come from published advisories are stored in join tables, because "this advisory covers this CVE" is something I decided, at a specific moment.

Derived stays derived. Facts get stored.

The audit angle

The same thinking is why raw_payload sits next to those keys as JSONB: the alert exactly as it was received, untouched. Triage systems live and die on audit trails. Being able to show what came in, and what it was linked to, without either one being reconstructed after the fact is the whole point.

Honest status

The columns exist and the constraints are live. What populates them is playbook-engine, which I haven't built yet. Right now every alert sits unlinked, exactly as designed. The schema is the decision. The engine is the next build.

Which CVE an alert got linked to is a fact that happened. Store the fact.