This is largely based on How to version control a record in a database and http://www.sqlitetutorial.net/sqlite-trigger/ :
You will need to find your business rules about what to version and what to update when. For example, if a customer changes her address, which orders do you want to update/change. Certainly you do not want to update orders with the new address when these orders have been fulfilled and paid a year ago already. But you certainly want do update the address in orders that have not already shipped. But for orders that have been shipped but not yet arrived, changing the delivery address will create lots of confusion for all parties involved.
Also, for auditing purposes, you may want to track all changes to certain fields of data. But for other regulatory reasons like GDPR, you maybe want to have a set date on when such changes expire.
So, before deciding on what versioning scheme to use, consider how the versioning will be used later in queries.
The approach that can most easily be tacked onto an existing schema is to have for each table mytable
a second table mytable_log
that gets filled through database triggers and stores the old row and the new row and the (database) user and action. That way, whatever your application does to the database is automatically recorded in the log without much impact on existing queries. You will need to find a way to communicate the acting user to the database, either through their database login or through an additional variable or stored procedure.
The other approach is to do away with the consolidated/current table and keep the history and the current state in the same table, and have views that reflect the current state of the world. This means you can do away with the database triggers completely and also revoke all delete/update permissions on the tables for the users. The downside is that you will have your program logic select the newest version of each customer (or whatever), which can be done through window functions, but will be slower than having a consolidated table around. Also, in this case, you need to take care when actually expunging a row from the database (say, for GDPR reasons). Even if a row is more than 10 years old, it may still be current because there is no newer version of the row available.