4 ms·
This looks like it works pretty well to just 'dump' data changes, and if needs be to visualize them somewhere, but the data is unstructured and long-term there
by Cthulhu_ 5y ago
This looks like it works pretty well to just 'dump' data changes, and if needs be to visualize them somewhere, but the data is unstructured and long-term there may no longer be any relationship to older data.
Which may be fine for your use case, don't change anything if it works for you.
In my use case, I also have a requirement that a user needs to be able to revert changes - an undo function, for edits and a 'deleted' state. I've been looking into Temporal Tables for that, where every table has a sibling 'history' table with a pair of timestamps - valid from and valid to. This allows you to query the database - including related rows in other tables - at a certain point in time, and to revert to a previous state without losing any intermediate steps (copy rows at timestamp X, insert as the 'current' version).
This is a built-in feature in a lot of modern enterprisey databases, but I'm constrained to sqlite. But, using triggers I've been able to make it work in a POC.
Whether it'll work in my 'real' application is yet to be determined, I've got a lot of work to do still - I need to get rid of my ORM and redo my data access to something more manual, unfortunately.