The requirement is user should be able to see the history of what happened for a page/post. Kind of like git commit history for a file but here is database single record.
Our current architecture is legacy and does not want to do massive refactor. I was looking for some 3rd party vendor/library that i can hook into. So it will store all data updates and deletes. Then i can get a history for all fields and should be able to provide a nice interface to user see what got changed in each update.
Well, I don't know about a 3rd party library that manages snapshots (and do you really want to snapshot on each change?), but one simple way to do what you want, is to use database triggers. The idea is you can execute some SQL when a change occurs (i.e. an insert, update or delete) on your source table. So, one way to capture such changes is to execute an insert into a separate audit table in your trigger(s), so that every single insert, update or delete to your source table, is recorded as a new record in the audit table. You need to also record the action that was performed (insert, update or delete) and optionally the timestamp, auto-incrementing column, or information about the user that made the change. As such, your audit table will have all the fields of your source table, plus some additional fields.
This audit table will just keep growing, so you might need to eventually think about what you do with very old history. You may eventually need some kind of archive process.
