Dealing with a separate table is still hard (I know because we do this). What happens when you do a migration or need to shard something and want consistent partitioning across your data? You have to consider your one off table that everyone inevitably forgets about. I agree that a deleted_at column is too big of a liability for compliance reasons though
Soft deletion probably isn't worth it
321–330 of 514 posts
Re: Soft deletion probably isn't worth it
#322I just wanted to touch on the fact that eliding soft-deleted rows from queries is really, really easy - this article makes it out to be a constant headache but here's my suggested approach. ALTER TABLE blah ADD COLUMN deleted_at NULL TIMESTAMP; ALTER TABLE blah RENAME TO blahwithdeleted; CREATE VIEW blah (SELECT * FROM blahwithdeleted WHERE deleted_at IS NULL); And thus your entire application just needs to keep SELE…
This is not a solution. It introduces a leaky abstraction which sooner or later will lead to errors. Sure, all code you write will access the view and not the table. But how can you ensure all other code in the organisation uses the view? Perhaps you add some access control to the table so that only authorized users can read directly from it, but that's even more technical overhead. Then you have foreign keys. If you…
Give it the ol obvious name - table_including_soft_deleted_rows_dont_query_directly.
Re: Soft deletion probably isn't worth it
#323The example the author gives is… frankly awful. I can’t think of a single case where you’d want to remove the invoices of a customer you delete. Ever. In fact, the opposite is more likely to be a big problem, accidentally cascading your delete to your financial records! Using a soft delete, your invoices won’t “disappear” because your app WILL have a view for looking at just the invoices. Source: I built a bookkeepin…
> I can’t think of a single case where you’d want to remove the invoices of a customer you delete. Ever.
CCPA will require you to delete the invoices. And I would love for all platforms to support deleting everything, including invoices, considering some things are illegal in other places and if there's proof of you buying said illegal thing, you can get in serious trouble (think gay dating apps in the UAE).
But I don't really agree with the author on his take about soft deletions.
Re: Soft deletion probably isn't worth it
#324Earlier quoted context omitted.
The author uses the "no one ever undeleted anything" as the primary justification. 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. Either a complaint finally gets around to me as to why the user wanted their account deleted (e.g. feature not working) and it helps to figure out why. Or they're returning and want things set up l…
It may be convenient, but under the GDPR is illegal. When an user deletes an account, all the personal data associated with that user must be deleted (or anonymize it in a way that it's no longer possible to associate it back to the particular user). You cannot just keep user information forever "just in case" they are useful again.
Any reasonably big system will require you to invest in that anonymization. Doing cascading hard deletes and making sure any missing references don’t trip up your system or mess up reporting is much more difficult than just flagging fields that need to be scrambled with randomized data (most libraries have fake data generators that make doing these updates easy)
Long before GDPR was a thing we had this implemented allowing us to securely analyze production dumps.
This is one of those things that I think libraries need to mature to support and then it will be effortless. Right now it requires a modest, but hardly overwhelming, engineering effort to implement.
Re: Soft deletion probably isn't worth it
#325Someday we'll have a database that handles this for us. We'll specify whether a particular table should have an audit trail. The system will know about foreign keys and related tables, and save them as well. Everything will be configurable, of course. Internally, the system will save the relevant data using the write-ahead log. Restoring deleted data will be easy, a simple command. Purging data that should disappear forever will be another command. This is all very possible.
Someday.
I'm embarrassed to admit how many decades I've been waiting for this.
Re: Soft deletion probably isn't worth it
#326Earlier quoted context omitted.
It may be convenient, but under the GDPR is illegal. When an user deletes an account, all the personal data associated with that user must be deleted (or anonymize it in a way that it's no longer possible to associate it back to the particular user). You cannot just keep user information forever "just in case" they are useful again.
I wonder what percentage of companies who even appear to comply with deletion requests actually do full deletion in practice. I suspect it's small, knowing how many things are coded to fake-delete for convenience. Businesses also tend to keep cold data backups around. (Maybe backups are exempted? I don't know.) There might even be cases where ostensibly deleted data can still be recovered from a disk, if they haven't…
The really hard part is dealing with the data still in backups.
It leads to having to do crazy things like individual keyed encryption per user, escrow hilarity, etc.
Re: Soft deletion probably isn't worth it
#327Earlier quoted context omitted.
>under the GDPR [soft delete] is illegal This is obviously not always true. Any European can't, for example, delete their online account with their mortgage company and demand that the mortgage company deletes the records saying that they owe them money so they can get their house for free. Nor can anyone call up their previous employer and require them to delete all the work they ever did from their company's comput…
> Nor can anyone call up previous employer and require them to delete all the work they ever did from their company’s computer systems. You can’t delete IP as this is not covered by GDPR, but you sure can ask them to delete your identification data from their records as GDPR also works for employees not just customers. It’s a problem with for example vcs. Unless there’s another law in ruling, GDPR is the baseline. It…
The right to erasure to is not unlimited (few things in GDPR are as absolute as people imagine). Yes, is personal data is retained based on consent, that consent can be withdrawn and the data must be deleted. But there are many other bases for collecting and retaining data as well.
The examples in the GP were mostly around the data being necessary for fulfilling a contract (can't request deletion of your mortgage records) or meeting legal obligations (can't delete tax records). But there's also legitimate interest.
Legitimate interest is vague and open to interpretation, but I'd be really surprised if there were any decisions from a DPA suggesting that a VCS commit log would be problematic. The legitimate interest seems really strong in that case: being able to audit who actually made each change, e.g. for security reasons.
Re: Soft deletion probably isn't worth it
#328Earlier quoted context omitted.
User deleting an account is just one way that stuff gets deleted. There are other deletion scenarios where it is appropriate to keep the information after its deleted.
Yeah. Basically anything that an employee can mess up, should be reversible IMO. But actions such as deleting an account should have the option of "YES, DELETE IT PERMANENTLY, THIS CAN'T BE UNDONE"
Often it’s mark for deletion after some number of days.
Re: Soft deletion probably isn't worth it
#329Earlier quoted context omitted.
User deleting an account is just one way that stuff gets deleted. There are other deletion scenarios where it is appropriate to keep the information after its deleted.
Yeah. Basically anything that an employee can mess up, should be reversible IMO. But actions such as deleting an account should have the option of "YES, DELETE IT PERMANENTLY, THIS CAN'T BE UNDONE"
The only way that would make sense in if it was a cancelable timed deletion. People make mistakes. People change their minds. People get hacked.
Re: Soft deletion probably isn't worth it
#330There's a very legitimate case that I've seen made for soft-deletion in several different situations: foreign keys related to "created-by" columns. Hard-deleting a user who created an object that remains in use after they're gone would trigger referential integrity complaints on those columns. Without being able to reference a "deactivated" user's primary key in such a situation, you'd have to come up with some counterintuitive system for revisiting such objects. And the result (short of removing the foreign key) would be to give you inaccurate information about who created the object. Maybe one of you smarter people has already thought of an elegant way to handle this, but I've never seen one that satisfies my taste.