Soft deletion probably isn't worth it

Here's a toy implementation:

https://www.db-fiddle.com/f/n3ux4s7mcZ2554738T14QR/2

I worked at a large company as it was implementing GDPR compliance, and I can attest that it was a nightmare of drudgery. I think it's interesting that our entire tech stack is designed to assume the business has a right to keep user data for as long as they want (forever), and compliance with GDPR delete requests is like a one-off exception implemented at a very sketchy redaction task that has to be careful not to leave broken foreign keys and such around.

It's a nightmare to do now, but our tech needs to change such that we assume data might be a hot potato that you need to be able to get rid of, and not some immutable asset that you can hang onto forever.

Box has a well defined schedule for the various stages of trashing. some of them are user configurable. i would call this workflow expected behavior of the application. this sort of soft delete is something you design in intentionally knowing it’s a customer use case. there’s many other objects in Box that do not need this workflow. i think soft deletes don’t need to be available for all tables but some it’s immensely helpful

> But the technique has some major downsides. The first is that soft deletion logic bleeds out into all parts of your code. All our selects look something like this:

A view solves that problem. Make a view that only has the non-deleted stuff. Give it ON UPDATE and ON INSERT triggers so that it walks, talks, swims, and quacks like a table. Voila, no more code bleeding.

> Another consequence of soft deletion is that foreign keys are effectively lost.

Bit trickier, you have a few options:

* Make the constraint include that the foreign object has `deleted_at IS NULL`.

* Add a trigger that automatically marks as deleted (I'm assuming a setup where you'd ordinarily use ON DELETE CASCADE) anything that refs row X when you mark row X as deleted. If you prefer commit failure ON DELETE instead, triggers can do that too.

> GDPR scaremongering

You don't have to delete records from backup tapes either (SOURCE: I read the whole thing). Using soft delete is actually making life easier for you - you presumably _do_ have certain data storage requirements (for audit trails and the like), and now you can just have the one canonical database that contains it all. When its time to prune an entire customer/user into oblivion, it's simpler to do that then - just `DELETE` the right rows away (actual DELETE, not UPDATE SET deleted_at).

Yes, GDPR has something to say about keeping data around where you have no feasible auditing or any other reason to have it, but that's a red herring: You don't want your database tables to grow humongous with 99% of the rows 'deleted'. That view with some indexes can do a lot but it isn't magic. Presumably you want a cleanup task that, every month or so, DELETEs anything with a deleted_at value that's older than a month or what not. This fully takes care of your GDPR requirements as far as unreasonable data retention goes: A script automatically runs to wipe out all rows in all tables whose deleted_at is too long ago, and then reports that it did this so that you have the audit trail.

So, for your requirement to delete specific records upon request, it's easier. For your requirement to not keep unneccessary data around beyond reasonable bounds, it's a simple script.

> Here’s a snippet from one that I wrote recently which keeps all foreign keys satisfied by removing everything as part of a single operation

If you set your constraints explicitly to checking only at the end of a commit this isn't at all difficult the way the author says it is. Just delete what you wanna delete, commit at the end, and poof - all is well. You can force postgres specifically into a 'yeah yeah do not check any constraints until I commit' mode if you don't want to change your ON DELETE clauses.

> data deletion has non-data sideeffects

That depends on the use case. It feels like a bit of a strawman argument - obviously if the delete action does irreversible damage, marking the database row using a soft delete is rather silly. Of course. Most delete operations are nothing like that though.

> Alternative: A deleted records table

Author's previous point about non-data sideeffects kills this just as badly. But, sure, this isn't a bad idea. However, most of the complaints about soft deletion apply in a different fashion to this model. For example, if you have reference constraints, and using ON DELETE CASCADE, you need to do a heck of a lot of copying. You don't just 'copy' the row you want to delete, you also have to copy every row of every table that refs your table with ODCascade constraints to its 'deleted' variant first, and only then can you delete the lot.

> Hard deleting old records for regulatory requirements gets really, really easy: DELETE FROM deleted_record WHERE deleted_at It is _exactly_ as simple to do this if you use soft-delete. Bit of an own goal.

Author's got the right idea (soft delete needs some thought), but the technical aspects are a swing and a miss, I think. However, some database make some of these solutions hard. As far as I remember, they don't all support ON UPDATE/ON INSERT rules on views, for example. Fortunately, postgres supports all of this stuff.

    I think this is the part they miss. I've never 
    undeleted a user either, but there have been many 
    times I've gone back to look at something.

Yeah. As far as a user-facing "Undelete" button existing or being used... that's very rare in my experience.

What's much more common is a user accidentally deletes some data. They deny they made an error. The developers are blamed. You then have to go on a wild goose chase figuring out if it was possible for the app to actually screw up in that way. There's usually no definitive answer, and even if there is, management can't understand it. And regardless of how any of that plays out, you still probably have to try and recover the data from backups or something.

Alternately, maybe it was the app's fault. Still plays out nearly the same!

Soft deletes and/or audit trails save you from all of that.

    Though you really shouldn't be relying on a 
    database for an audit trail. It might help 
    find some issues, but things actually used 
    for security shouldn't be writable so easily. 

I mean, at some level you need to trust the database right?

Been ages since I did it, but it's usually possible to set up a "secure" audit trail with use of database permissions. For example, the application's DB credentials can have SELECT and INSERT permissions on the audit trail table, but no UPDATE or DELETE perms.

How would you set up a secure audit trail that didn't rely on the application and/or database at some level? Even if it lives outside of the database, that data came from the database.

Not a rhetorical question. Genuinely curious!