Switch to UUID for all keys/IDs

[ctron]

Currently we have partly UUID and i32 for ID style fields.

The i32 has the advantage, that is can use auto-increment. Which is nice, but also discouraged but some security policies, because ID generation should not be guessable.

Using UUIDs should yield the same performance, can be auto-created by the database as well, but has another feature: it is possible to create a new ID outside the database.

This can come in handy when batch inserting stuff which relates to each other. Having the database assign IDs, it's a back and forth between the application and the database to insert new stuff. Using random UUIDs (v4, v7) or other ways, unique IDs can be created outside the database and relationships (but those IDs) can be created up-front. Allowing to batch ingest a lot of data.

This comes in handy ingesting document which have a list of other stuff (like SBOMs -> packages, advisories -> vulnerabilities, …).

A problem is how to migrate from existing i32 to UUIDs in the database. While it may be possible to come up with a database migration, that might be quite tricky and complex, as the primary key and foreign key fields need to change type and value.

My proposal would thus be, to do it now, ignoring all DB migrations. Just replacing the type. And from there on, never look back and never use i32 again.

[bobmcwhirter]

I agree.

fwiw, I did a migration from i32->UUID and it kinda did suck.