Best practices for allowing rollback of individual customers on a shared database Imagine having multiple customers (as an example) on a shared database, and needing a backup system so that a single customer can be rolled back without affecting the other customers. - It's not important whether the customers themselves can order the rollback or only an administrator of sorts, but for a host of reasons is having a database for each customer not an option.
Edit:
There would be a customer table, but the customer's data will be spread across many tables. Maybe because a customer can add stuff only they can access.
\Edit
How can this best be achieved?
I have considered the following options (don't mind the order):
#1
Making a regular database backup and building a mechanism that clears all relevant rows in all relevant tables and then reads the customer's data from the backup-file and insert it into the database.
This mechanism will have to be maintained as the database changes.
#2
Making a shadow-table for each relevant table.
The needed mechanism will essentially be the same as for #1, but it won't be necessary to read from an external backup-file. The mechanism and shadow-tables will have to be maintained as the database changes.
This will work better than #1 if customers need be able to order rollback autonomously.
Any option I'm missing?
Anything I should be mindful about?
Most importantly: Any best practice for doing this sort of thing?
I'm using PostgreSQL in case that includes anything that can help.
Mimic the transaction system of PostgreSQL:

Every affected table has a column visible_from and visible_to, type timestamp with time zone.

When you update or delete a table, set visible_to on the old version of the row to the current timestamp rather than actually updating or deleting it.

When you update or insert a table, add a new row with visible_from set to the current timestamp.


This way you can determine the state of the table at any given point in time. Rolling back is now a matter of deleting some rows and clearing visible_to on others.
Periodically a job removes data with visible_to older than a certain grace period.