61–70 of 109 posts
Often other tables have foreign keys to the record to be deleted. What’s a good pattern for treating those related tables for every time a relationship row gets deleted/removed as per the OP blog post?
Cascade can be a bit of a footgun, as this can trigger a waterfall of deletes.
My question when people implement a generic feature like this, is why doesn’t the database do it? And many tunes, as is the case with soft delete, it does. For example, Redshift tombstones records and you can choose when to “vacuum” them up (actually delete them). Usually if you’re changing the way a primary function of the database works, like delete, it’s probably not a good move.
To take a stab at it: This is one of those things where business requirements trump the technical implementation details. Prevailing theory is that actual deletes are bad because you can’t do historical analysis, recovery etc on the data. Say a customer stops using a service for a year but comes back: it’s a big win if you can (at least optionally) restore their data, so the theory goes. That’s why tricks like this e…
It's around historical data, especially in a work scenario.
For example a worker might create a thread and then 38 other workers reply to it. There could be a lot of great information in this thread. It could also be referenced in 5 other threads and external sources (docs, etc.).
If the worker leaves the company, should you really delete them in such a way where all of the threads they've created get deleted in a cascading fashion? I'm all for privacy and I would want to see that happen in most public communities but for private work, I don't know. That changes everything.
I've seen a number of tools keep the user around and visibly label them as "Jane Smith (Deactivated)". I think that's a lot cleaner than having a special reserved "Deactivated" user and then you change all of the user_id FKs to that user before deleting the person who left. If you do it that way you lose the context of who posted the original thing which has a lot of value in a long running project.
But all of the above hints at using deleted_at most likely. It keeps everything in tact at the DB level and then the app layer chooses what to do for each resource type. What other options do you really have if you want to keep things working exactly the same after deleting someone except for maybe showing an indicator that they're not around anymore?
Earlier quoted context omitted.
I’m also not sure about the foreign key restrictions on generated columns (and glancing at the docs I don’t see anything about it on there) but for all intents and purposes they are real columns so I’d imagine it probably works. Apparently they run after the before triggers, I’m not totally sure where foreign keys are checked but probably after that? As an aside, they’re a great feature. We’re using them to generate…
I haven't tested it but it might be the case that it'd need to be a stored generated column to be referenced like that but that shouldn't be a big deal.
There's at least one factor which I don't see consideration for here. Under GDPR, CCPA/CCRA, and a number of other privacy laws, if you're going to retain data related to people you need to provide those people some way of retrieving that data or requesting that it be erased. Putting the data into a "deleted_record" table doesn't remove that obligation. So, if you're going to have a bunch of "deleted" records hanging…
Does it still count as your data if there is no normal way to retrieve/access it in the software?
If you say "yes", here's what this implies: if you have deleted the data, but it's still on the disk because the drive heads haven't wiped it yet (it's just been deallocated), then it's still accessible.
So, whenever there's a GDPR request, you should run disk recovery software? (The answer is no; you'd have to butt up against pretty thick lawyers and judges to be fined for this)
If you have an audit table that is automatically deleted after a while, and the audit table cannot be accessed as part of normal operations, then IMO you will be able to argue that it's not part of data that should be "reasonably accessed" via GDPR.
When you look at the spirit of the law, it also does make sense (disclaimer: I am a HUGE proponent of GDPR). What matters is that users have access to the data that the company has access to, and is able to correct and delete it. If the data is not normally accessible, and will soon be deleted, then it doesn't matter.
> I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes. Sounds like the problem is not soft deleting, but applying soft deleting to _everything_ without thought. Then he goes on to suggest an alternative that is even more complicated. Just includ…
Often other tables have foreign keys to the record to be deleted. What’s a good pattern for treating those related tables for every time a relationship row gets deleted/removed as per the OP blog post?
Really depends on the related data, but there‘s the option to use ON DELETE CASCADE|RESTRICT and set null or set default. Cascade can be a bit of a footgun, as this can trigger a waterfall of deletes.
Earlier quoted context omitted.
or you can create view for every table that supports soft deletion and ensure all of your read-only queries are using those table CREATE VIEW current_customers AS SELECT * FROM customers where deleted_at is null; SELECT * FROM current_customers JOIN ... Of course, this comes with its downsides e.g. views need to be recreated in every migration and there might be some complex join operations that might not work.
It may be work sometime in the future, but having to prefix every query with a null check sounds bonkers. A view can be optimised to do all that for you. I made the what the flip expression reading that they did a prefix on everything.
There's at least one factor which I don't see consideration for here. Under GDPR, CCPA/CCRA, and a number of other privacy laws, if you're going to retain data related to people you need to provide those people some way of retrieving that data or requesting that it be erased. Putting the data into a "deleted_record" table doesn't remove that obligation. So, if you're going to have a bunch of "deleted" records hanging…
This is an area where technologists and lawyers will end up disagreeing and fighting about boundaries etc. Does it still count as your data if there is no normal way to retrieve/access it in the software? If you say "yes", here's what this implies: if you have deleted the data, but it's still on the disk because the drive heads haven't wiped it yet (it's just been deallocated), then it's still accessible . So, whenev…
Setting up row level security policies to exclude rows `where deleted_at is not null` solves most of the issues with the discarded solution. Of course it would be crazy to have a system where you will need any extra where clauses for the default queries being made. You can even make a simple function that sets a statement level variable checked in the rls policy so that deleted rows can be included or only deleted ro…