Comment by bbkane
4 months ago
Maybe I could convert every table into a "log" table (i.e., append only similar to your blog) and add a timestamp column to each table (or UUID). Then I also store all of these UUIDs in another table to keep track of the global order. To "undo" I look up the previous UUID, find entries with that UUID, and append to the log table the values that make the state the same as the previous UUID....
This isn't as general as your setup, but I think it would work ok with my foreign key issue.
I believe UUIDv7 does not (cannot) guarantee total order. I am using it as a method to manage multiple timelines---each "fact" lives on its own virtual timeline (identified by a "valid_id", which is a UUIDv7). My DB system does not automatically disambiguate conflicting facts (because it cannot --- it simply does not have the meta-information). The users of the system can manually audit and fix inconsistencies by making correcting (append-only) entries.
For your case, I feel like your best bet would be to create the global order in the first place... i.e. force all writes through a single (and single-threaded) writer process, and fetch reads off one or more independent reader processes (I'm doing this with WAL-mode SQLite).
But I could be totally wrong --- this is my first foray into the freaky world of temporal data systems :D
As this is a single-user CLI tool I don't need any concurrency. So I can probably use the timestamp directly instead of a UUID and not worry about multiple timelines.
On thinking more this morning I think I can keep my original tables and add append-only "log" tables with triggers to keep them up to date when the main tables change. That doesn't slow down my main tables performance (they only contain the current data) and also allows me to incrementally change my db (adding new tables + triggers is easier than changing existing ones).
I've made https://github.com/bbkane/enventory/issues/122 to track this idea, though I'm not sure when (or even if) I'll have time to really play with the idea (much less "productionalize" it- add support for all enventory events, tab completion on the CLI, tests, UI, etc...).
But I'm at least tracking it for when I get time and motivation! Thanks for writing the post and replying to my comment.