How do you design schema for tracking change history in NoSQL? I'm thinking of trying a document store db for a project that needs to keep track of changes. I'd like to prototype with firebase, but I think the schema/implementation would be the same in MongoDb

The (simplified) setup is that I have three tables/collections Project, Asset and User. A (logged-in) User can change (Create, Update or Delete) a Project or an Asset. The Asset can be assigned to (maximum one) Project.

Any time the Asset or Project has any changes made, I'd like to capture the User that made the change, when they made it, and in most cases, the previous state.
Add three attributes to the project and asset documents - WrittenWhen, WrittenBy and IsActive. Populate these in the "real" data.

When a record changes copy it but with IsActive flag flipped. Retain the original values for WrittenWhen and WrittenBy. The original record is updated with the amending user's id and the time stamp. I do it this way around so any other record holding this record's magic id retains that pointer.

When reading only look for records with the right value of IsActive.

IsActive can be omitted if the archive records are written to a separate collection / namespace / database / whatever your technology supports.




  Thank you Michael, Couple concerns I have with this implementation; Size - this may have a lot of redundant data in it, possibly 100 or more times data than needed would be stored, with many deployments using mongodb this may not be an issue, but with the BaaS the like of firebase, this can make a big difference. My other concern is that it doesn't know which data point was edited unless you diff with previous, and with possibility of multiple users editing multiple data points, to show a "last updated by * on *" it may take a lookup of many older records – Daniel Mar 10 at 16:16 


The points you raise are valid. An alternative design would be to store only the changed values in the history store. A record there would then be (primary key field(s), name of changed field, old value, new value, written by, written when). Storing only the parts that changed leads directly to two new problems.

One, you have a mandatory overhead of separating the changed parts for every single write, as opposed to deferring the diff costs until needed. Typically we want OLTP writes to be fast and audit reporting is not performance critical. So it would seem reasonable to me to defer that work to when the report is required. Of course the cost of writing the whole record will be more than of writing the shorter diff-only record. You can benchmark your infrastructure to see if that is significant.

Two, by storing only deltas you loose the data context in which a change occured. By this I mean by only storing deltas we can read a single audit record to see that, say, field 53 changed from "A" to "B". What values did all the other fields have when that change occurred? To find out you have to read backwards through history until you find the most recent value for the other fields in which you're interested, potentially all the way to the "live" record. Again, I don't know if this is a valid use case for your scenario, or if change frequency and audit read frequency would make it a problem, but it is something worth considering.

To show "last updated by .. on .. " with either scheme is a single record lookup of the last history record written because both ways store this information on every history record. To find what changed with the full record stored will require a second read and a diff, which is not needed with a delta store. To reconstruct a historical record will be light work with full records and processing-heavy with deltas.

I don't know your requirements or constraints well enough to fully evaluate one design over the other. My preference would be for the one I documented due to ease of implementation and (likely) performance. I will note, however, that there are no free lunches. The work has to be done somewhere, sometime.