Soft deletion probably isn't worth it

I've definitely seen soft delete work in practice. A couple things: for small data sets you can implement the naive deleted_at you can hide the records from your users by forcing them to use a view. You can also handle updates on the view to prevent data conflicting with deleted data if you need to.

For foreign key constraints you can set the foreign key to null and orphan the records if the relation is deleted. You could also hard delete them in this case. It depend on your use case.

When the data volume grows or the ratio of soft deleted to normal records is high, you should consider another solution. One solution you suggested, moving the record to a deleted table is a fine one.

The other solution that I've used successfully is to journal your deletions in another table or system. For smaller volumes having an audit table Journaling the data and storing the pk, fkeys, and a serialized version of the record, json works great in postgres, works well. For large volumes or frequent deletions something like Kafka or PubSub work better.

You may very well find others interested in consuming your audit journal to track changes. Updates and even inserts fit great in the more general case.

Ha, and then there is the opposite regulation that you have to delete user data.

That doesn't solve the foreign key problem. You can still easily have a reference to a record that is "deleted"

> Instead, we rolled forward by creating a new app, and helping them copy environment and data from the deleted app to it. So even where soft deletion was theoretically most useful, we still didn’t use it

I don't get this statement. You wouldn't have had the env or data without soft delete? You did use it!

I would say, soft delete isn't a tick the box and done solution as many ORMs make it.

You need to consider the data model, and adjust your queries to that.

It may make sense for a product to be deleted, but orderlines still able to access it to display product name etc.

With blob data, I tend to move that to a "bin" with a 30-60 day grace period. Customers know quickly reporting, we can fully recover, while outside that time they'll have to provide images etc. It's a decent compromise.

Reuse of unique fields is the sticking point I run into often, as mysql interprets null as not clashing with other nulls so composite uniques using the ID and deletion date don't work.

Which is a decentralized, distributed ledger, so... banking! :)