> I like SQLite’s decision to make integer primary keys not marked as auto increment to simply be aliases for its rowid which is not guaranteed to be unique for the table’s lifetime.
That to me sounds like a good way to amplify a bug whereby a foreign key has not been defined¹ where it should be. I hope there are copious warnings about it in the docs and when creating a table that way.
--------
[1] Or can't be as the referrer is in an external system, though in that case you probably want a separate external ID, possibly a UUID of some sort.