this is funny 🙂
some time ago, about 6 or 7 years back, I was working on the project where we implement similar feature to version control the data. and in very similar way.
each table where we needed to have historical data, has 2 columns at the end of the table
StartDate and EndDate. StartDate was filled in when record was inserted. and EndDate was filled
when record was deleted AKA "Soft Delete"
when ever we needed to change some info in the record that required history keeping, the process was like , copy the record -> date stamp enddate in the original record -> change the new record.
we also had some tables where additional columns kept the EditDate and editByUserID info.
so we can see who changed the record and when.
later on, we also had implemented the data retention feature ,again similar to temporal tables, where a trigger on the table would store any changes to data in a history table.
as in, what record, what column(s) changed , the old value and new value and userID.
not sure how flexible this new functionality is, but it is a good option to have.