I didn't know about user_version or application_id either until just now. I think I'll start setting application_id. Probably not registering it in the SQLite repository - there's no procedure to do that at the top of the file and it looks like almost no one does. I'll just pick a random 32-bit int and assume I don't collide.

I don't think I'll use user_version though. I prefer my current solution: a version table with multiple rows. One for initial installation and one for each schema upgrade with timestamps and the software version that performed the operation. The current version is just the max. If my migration procedure goes badly, I have some clue what happened.

If my application supported schema downgrades, I'd want to have a separate operation_id and schema_version; the max operation_id would win rather than the max schema_version.

application_id is really meant for scenarios where the SQLite database is treated as a document - a file that is intended to be copied around by the user, and opened by the same or another app elsewhere.

According to their mailing list, the way to register your own ID is to submit a patch that adds your ID to the file.

I tried to do that, but maybe the message didn't go through.

Some of the application IDs are made up using hexadecimal (such as Fossil), and some using ASCII (everything else listed in the magic.txt file in the SQLite repository). I have used base thirty-six; I later saw RADIX-50 mentioned, which would be another way.

We also do something very similar but our version table has two columns: module_name and version. Instead of doing central schema management like in a usual monolith, Every module manages its own schema.