Soft deletion probably isn't worth it

> Furthermore, the cost of an error is potentially massive. Someone new at the company makes a revenue report based in the billed Invoices and does not realize they should query the view and not the table... Not great if 90% of all invoices belong to soft-deleted customers!

I'm not sure I buy this argument. It's certainly conceivable for that to happen, but no more so than any other case of "the engineer queried the wrong table and thus got incorrect results." There's never going to be any technical way of preventing this: if you have access to multiple sets of numbers, and you want to sum up one set of numbers but mistakenly sum up the other set of numbers, you're going to get the wrong answer!

We use soft-deletes extensively at our startup. Here's a couple reasons:

- Feature creep. "Sometimes our users accidentally hit the delete button, or change their minds a minute later. We want to give them a way to undo the deletion." Or "I know we said last quarter that we users want to delete stuff, but they also want to see a list of everything they've deleted in the past." Soft-deletes handle feature-creep a lot better than hard-deletions

- It simplifies foreign-keys management. If you want to hard-delete something that some other entity is referencing, you'll have to hard-delete or modify that other entity first. And potentially repeat this process recursively for their own references. This is a pain. One could argue that if you really want to delete something, you should be deleting all children as well. Such arguments are highly domain specific, and very bad universal claims. We've seen some use-cases where such pedantry is not necessary

- It makes it easier to recover from mistakes and bugs. Customer deleted something accidentally and emailed you begging for help? Your code has a bug causing stuff to get deleted when it shouldn't be? You'll be thankful you did a soft-delete and not a hard-delete. Is it going to solve every single problem where the data has system-wide ripple effects in a unicorn sized organization? No. But it'll still solve a number of problems where the data impact is more localized

- It makes debugging easier. You have a clear record of everything that used to exist. You don't have to go digging through your logs to find something that used to exist but has now been deleted

- Speed. All of the above problems can be solved in other ways too. The author suggests putting all deleted data in a "deleted records table." So now you need to maintain a 2nd table for every table that you may want to delete stuff from. All schema updates will need to be mirrored on this 2nd table. And you'll need to write and maintain code to populate this deleted-records-table every time you delete stuff from the original table. All doable and straight-forward but takes time away from other things you could be doing instead

The main benefit from hard-deletions is data compliance and liability. Ie, being able to tell privacy-conscious customers that you actually deleted their data. If you're handling any sensitive data, you should definitely do hard-deletions at some point for this reason. But the other reason the author gave - "it's annoying having to check for `deleted_at` when writing SQL queries" - seems pretty minor compared to the benefits.

A problem (unless something has changed, my context is Oracle from some time ago) is that NULL values are not indexed. So the "WHERE deleted_at IS NULL" could trigger a full table scan. It can also cause row migration when the NULL value is eventually filled in. Unless you explicitly need the deleted date, it's probably better to use a non-nullable Y/N for this.

If you do want to retain the deleted records for any purpose (audit, compliance etc.,) it is better to design a DELETED table to maintain the history (just as suggested in the article towards the end).

Once your main tables start getting to the order of tens of millions of records, the filtering by 'deleted_at is NULL' or 'deleted_at is NOT NULL' gets in the way of query performance.

NULL is also not indexed. So, that throws the spanner in the works sometimes (depending on the query).

Yes, but banks tend to have websites with accounts and those accounts need to be deactivated when a customer should no longer have access (or, even more finicky, specific accounts for a client need to be deactivated or activated as they change their usage).

All this essentially forces the use of some sort of soft deletion. (Activation flags are sort of just a more complicated form of soft deletion).