Common approach for the last few decades was to add "event log" tables that get updated on triggers in every INSERT, UPDATE and DELETE. Basically, for every ta

Common approach for the last few decades was to add "event log" tables that get updated on triggers in every INSERT, UPDATE and DELETE. Basically, for every table "things" (id INT, name VARCHAR, ...) you'd also have a "things_history" (operation VARCHAR, id INT, name VARCHAR, ...) This is basically a slight inverse of your model, but pretty much the same implementation: your "materialized views" are actually the target tables, and auto-maintained tables give you the source of truth for audit etc.