The good thing about autoincrementing unsigned 64-bit integers is that 1) it's insanely fast, 2) SQLite is doing it automatically, 3) seriously, why don't they have it on all tables yet? SQLite guarantees no collisions within a table.
Doing homegrown ID generation is how such bugs are being introduced in the first place. Your application level trigger got bypassed, oops. Your check was experimentally disabled and left like that for a year until people started noticing, oops.
If you make an SQLite-backed application and it has a bug like this, I can safely bet $500 it's not going to be SQLite that has the bug.
Just don't expose the numeric IDs in links, and generate your UUIDs in tables where you need to point to rows in an outside-accessible link. This is database 101, for crying out loud.