could someone please provide me with suggestions to somehow save all the records of a table, for example, a table that changes constantly, I want to save all the states that had that record, when it was modified, and so on. For example, I was thinking of a generic table that has as fields table, user, action, date and a json that contains the historical record of all the fields.
You should provide more information about the DB engine you use. You could have as an alternative to implement a trigger at the DB level when these CRUD events occur.
Triggers are triggered according to the settings before an event on a table; It would be ideal to not worry about it from the system. The ideal of the table that stores the historical as an audit would be identical and include a unique id in it, and both tables should have at least user_create, date_create and user_update, date_update to make a trace to the record.
If I was thinking about something like that, but what I do not like is that for each table I would have to create an identical audit table. That's why I was thinking about a generic audit table for all the tables, that has its auditorium data, and a field that has a JSON that would contain all the historical data of all the tables. And well with the trigger all the tables could make an insert to that generic table.
The problem with that in relational DBs would be how expensive it would be to do a search, the less you should consider the fields [ID, TABLE_REFERENCE, ID_REFERENCE, DATA_JSON] it is still expensive to search in case you need to verify the recent trace of a record. What I would recommend would be to validate the volume and variability of the records, because if it is low it could be an option to consider.