> What I haven't seen much of is a trigger-based system which moves the deleted record into an entirely new table.

I've used https://github.com/nearform/temporal_tables before for that, has been pretty solid. Still need to manually keep the schema in sync, though.