Why soft deletes are evil and what to do instead

This looks great from an academic point of view, but there's at least 2 problems that concern me:

1. If you have a distributed system, soft deletes can allow you to stop a process gracefully.. e.g.: if making a user causes provisioning of resources, if a process re-queries for the user and can't find it, you don't know if it's eventual consistency creation delay or it was created-then-deleted. Obviously you can re-query the master node, or retry, etc. Point is there's more code for every such record and depending on the code structure, you might have to go back through the chain of return code before you realize you need to repeat the query strongly consistentl.

2. If you have a HUGE delete, it might put a strain on your DB if you're relying on it to do all the work. In this example, deleting a group deleted an array of member entities. If each member entity had arrays of entities, you might be deleting (m|b|t)illions of records. While that should still be fast, will that make a hiccup in your processes for 1, 3, 4, 30 seconds? Many cloud systems force transaction limits per second, so you should have a controlled deletion process anyways

What you say is true but it doesn't take into account end-users. I always do soft deletes because I have to support undeletes. Mistakes happen and I get rewind an audit log to fix things.

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.

From the tone of the article, I'm sure the author would stipulate that there isn't a perfect solution to this problem and which solution you choose will always be contingent. The author's proposed solution works fine until you have schema changes, then there's a good chance you can no longer use your audit log to roll back changes. Sure, you might be able to go in and do something manually to account for schema changes, but this becomes increasingly untenable as the schema changes pile up. There are no easy answers.

How will audit log prevent serializing entire graphs of data? Basically, your audit log becomes a mess. This feels like kicking the can into the audit log.

Uh, no.

In your scenario, Users cannot exist without being a member of one and only one Group. We'll accept that as a business requirement. It's a peculiar requirement, but fine, we can work with that.

The application user attempts to delete a particular Group that has one or more User records with foreign keys pointing back to it. Furthermore, the User entity has a constraint that the Group foreign key cannot be NULL - what should happen?

The API that you're using to access your database - you're not directly accessing your database, are you? - should detect that the delete failed, query that Users exist and return that condition. The application then knows that Users exist in the Group attempting to be deleted.

You have 2 choices at this point, and one of those choices has an option:

1. Inform the application user of the situation and inform them they must delete all the Users prior to deleting the Group

2. Inform the application user of the situation and inform them they must delete all the Users prior to deleting the Group, or they can force delete the Users

Option 1 - If the application user requests to force delete the Users then delete them and then delete the Group

Option 2 - If the application user requests to force delete the Users then move those Users to an Ungrouped Users table (so-called soft delete) and then delete the Group.

That's it. Those are your options. WRT to auditing, all I expect to be audited is the Group delete request: who made the request, when was the request made, what Group was requested to be deleted, was a force delete requested, and what was the result.

If you need to enable undo functionality, then there are other ways of achieving that which are completely separate from audit logging as undo is a separate concern from auditing. Keep the concerns separate in your implementation.

In short, soft deletes aren't "evil", and in fact, may be a business requirement.

This kinda misses the point, usually you don't want a "delete" in your app, you want an "archive". E.g. given the example it's often desirable to still know that a user used to be part of a group, or to later un-archive the group. That's what soft-deletes are good for.

I'd also recommend against doing audit logging on the application level if you want any kind of coverage, it's to easy to forget or work around. Database triggers or a CDC solution like Debezium [1] have more certainty and as a bonus don't require adding the audit functionality to each individual model.

[1] https://debezium.io/