I agree that I’ve fall out of love with soft deletes, but I don’t agree with their alternative of creating an audit log in the application layer. Instead, what

I agree that I’ve fall out of love with soft deletes, but I don’t agree with their alternative of creating an audit log in the application layer. Instead, what I like to do is create the audit trail entirely within the database using triggers. This means that if any other components of your system that connect to the DB, or any humans directly executing SQL, will also be part of the audit log without even being aware of it. You can do it using archive tables that exactly match the schema of the “real” tables, only without the constraints. You can also do it with a table that has a JSON column to store a json version of any arbitrary row. Just make a trigger so that when there’s a delete, a copy of the previous row state is recorded with a timestamp. If you want, you can go further and record every insert an update to get a complete history of all database activity. In this case add an additional column to record whether the activity was an insert, update, or delete. Complete perfect audit trail, and no software other than your database, and database migration system, have a single line of code about it. Just be aware if the database is write-heavy this audit log could get out of hand in terms of storage. Then you’ll have to use the usual strategies to deal with that. Hopefully you probably won’t be querying it very often, so you don’t need to waste time and resources with lots of indexes on it.