Thanks for your time reading this.

I would like to develop, essentially, a simple graph editor. The use case involves diffing and merging graphs in a source code control management environment or equivalent. I cannot rely on users coming up with unique names for nodes while making their additions to a graph, because historically they caused collisions often enough. Based on my limited experience it seems like a UUID based node identifier would be adequate enough. But 128 bits seems too long for SQLite to merge with a row id.

Q1. If one vacuums an SQLite database, is it suitable for source control, or should I consider a separate export/import format for that purpose, because of unreproducible layout or any other reason?

Q2. Is there a standard SQLite community alternative for UUIDs that fits in row ids while staying reasonably collisionless? They seem like a primary primary key pick otherwise. Some ideas and why I am not satisfied with them:

• Twitter-made snowflake id fits in 63 bits with huge timestamp error margin, but relies on central user enumeration which may be annoying in practice, wI don't see extending that part enough to mitigate this problem while keeping enough of the timestamp and fitting into 64 bits
• without row id tables may be problematic if nodes are big, and they probably will be
• some sort of internal table remapping uuid prefixes to whatever that is actually stored in the document sounds like easy source of bugs
• separating node ids for diff/merge from db primary key sidesteps most of this, but with that done I cannot use e.g. session extension to implement diff/merge
• not caring and deploying a rowid table with implicit primary key index may actually not work with sessions either

Any already obvious criticism unrelated to the questions is also welcome.

Thanks in advance.

Thank you for your feedback.

I understand that I would need to support diffing and merging on database files myself, tools or not. Still, a question remains if the likes of git and fossil can handle a "snapshot" database efficiently.

To the best of my understanding, this is related to how different two databases with identical current content may be, and normally I know the answer to be "very much" since SQLite developers felt the need to make the special content hash tool. But I was wondering how much VACUUM changes things here?

As for the primary key remapping, the approach you described makes perfect sense. It is just a shame that the session extension seemingly cannot be utilized here as is, it seems to do something really close to what I will need to do.

The case where two users add equivalent nodes is, indeed, where other identification schemes would prevail, but it is not that different from one user deciding later some nodes are (or just may be, while experimenting) redundant. So I will need to do something about that regardless.

The problem I see with Snowflake ID is not "decades" by themselves. If it was, one could just chop off miliseconds instead, or even replace timestamp entirely with sequencing numbers. Rather, the trouble comes from the user identity, or, in Snowflake terms, machine ID, which Twitter can arbitrarily assign to servers, while for user applications that is not ideal. PRNGs occasionally get skewed, which, when combined with birthday paradox, makes it hard to estimate a safe length that doesn't go for something close to a MAC address anyway.