Love the idea! Read only the beginning of the first "giant blog post" so maybe it's discussed there, but in Sqlite doesn't WAL mode give you a kind of super simple, super basic temporal data system? If you somehow kept all WAL entries 'as is', it should be possible to reconstruct the state of the db at any point in the past?
Thanks!
> but in Sqlite doesn't WAL mode give you a kind of super simple, super basic temporal data system
Yes-ish.
For one, it is unitemporal. For another, it is for SQLites own transactions, not of the individual datums. Litestream is the way to replicate WAL into an object store / elsewhere. litestream-vfs is also looking good!
https://litestream.io/ , https://litestream.io/reference/vfs/ (announced here: https://fly.io/blog/litestream-vfs/ )
I'm trying to emulate the data assert/redact properties of append-only bitemporal-by-design data stores like XTDB. The giant blog post builds up the intuition from scratch. Or at least tries to.
So my system is going to be: bitemporal data, enforced by SQLite schema and application checks, along with WAL replication for point-in-time transaction backup/restore. Both are entirely distinct, and unaware of each other.