Everyone wants soft delete deleted_at instead. Everywhere. Many ORMs even force this behavior.
Why? Why can't we just delete records anymore? I'm sure some have specific reasons for this, but I'm trying to find the broader reason that everyone has jumped on this bandwagon.
Maybe I'm too YAGNI, but I prefer delete by default, unless justified. Not the inverse...
Imagine you run a shop with a list of products. People can order and receive products from you. View past orders and get invoices and recepts.
One day you have a product that has reached end of life, or isn't selling well. So naturally you delete it from the store.
If you actually deleted this item, all the old invoices wouldn't be able to refer to it. Old customers couldn't claim warranty on it. You couldn't create old sales reports that made sense.
Turns out that this problem extends to most tables. Old clients? Users? Products? Invoices? In most cases outright deletion makes many other things invalid that you want to keep. So it's nice when the ORM makes meeting this requirement easy for you.
All this in addition to the ability to recover from an accidental or temporary deletion.
So what happens if a bank employee accidentally deletes an email in Microsoft Exchange? This is can not happen due to the policies in place. All emails are stored in a hidden folder for as long as necessary, ensuring that no data is lost.
FK cascading and DELETE, when done poorly, can... well, cascade, and delete lots of things the end user did not foresee. Like nuking half the database levels of bad.
`deleted_at' flags avoid that problem; combine it with row-level security in your DB (like postgres) and you can hide the rows permanently as well: from orms and non-superuser roles alike with a simple policy not to show any row where `deleted_at` is not null.
The ability to change your interface is what's alluring, not the mere fact that you have a record of changes. Though the audit trail is nice too.
Funnily enough you don't get to keep an audit trail while also losing the data. It's a trade off and businesses prefer the model that suits their business.
I think there's a certain amount of "you must keep records pertaining to financial transactions for N years" going on combined with (more recently, I suppose) abuse/spam protection.
Old-school solution - keep the records, in off-line hard drives in a fire-proof safe, with each one labeled "physically destroy on $Date".
Now, what do you do with standard backups such as the ones which mingle data which is 1 day, 1 year, and 5, 10, and 20 years old?
Someone accidentally deletes their company profile as they thought they were deleting the duplicate. They expect to be able to get it back.
Someone wipes out all the config files to get revenge on their employer. That employer is going to get in touch with support to get them back and they expect them back, even if you have to dig through backups and will make support's life difficult if they do not.
Also managing “on delete” is complicated to hook everywhere, much easier to hook a flag and touch nothing else implicitly.