For audit trails in rails, I still like papertrail. https://github.com/paper-trail-gem/paper_trail. It provides the ability to restore records as well as auditing abilities.
Soft deletion probably isn't worth it
211–220 of 514 posts
Re: Soft deletion probably isn't worth it
#212Soft deletion by storing the row in JSON won’t survive months of schema migrations. If restoring a record is rare you don’t want to have to find out that there’s no way to map the old data to the new table when it matters.
There are cases where you shouldn’t be deleting or updating data; auditable and non-repudiation systems for some regulatory compliance come to mind. Best to use patterns that don’t require those operations.
Soft deletion does come at a cost. Choose carefully!
Re: Soft deletion probably isn't worth it
#213DB2 has implemented temporal tables which can automatically capture all changes to the primary table.
Re: Soft deletion probably isn't worth it
#214Earlier quoted context omitted.
> "the engineer queried the wrong table and thus got incorrect results." The difference is that the path of least resistance, the most obvious method - just query the damn table - is incorrect. Bad design can certainly make a system more error prone.
In the example from this thread the names of the table and view are reasonably clear, so even in a hypothetical project without any external documentation of naming conventions or engineering processes (code review, etc.) the most obvious thing would be to query blah instead of blahwithdeleted . Of course, this is extremely hypothetical, and in any real project where you're generating a financial report you absolutel…
"reasonably clear" are famous last words. It ignores tons of evidence on how commercial software development works. Everything is "reasonably clear" in isolation, but not when you throw in thousands of other "reasonably clear" things developers are supposed to keep track while not missing tight deadlines. Fact is that if you add opportunities for people to screw up then you will make people screw up, regardless of how "reasonably clear" or "obvious" the system is.
Maybe you need more work experience because believing that it is implausible for someone to accidentally query the customer_with_deleted table over the customer view is incredibly naive. Likewise, people generating financial reports can have detailed understanding of data modelling but scarily often don't. Give them extra opportunities to fuck up and they will take them every time. KISS
Re: Soft deletion probably isn't worth it
#215The ONLY reason that you should avoid soft deletion is that deleting things permanently in a soft-deletion-based system is hard and error prone.
GDPR, among other regulations, requires that you be able to do this sometimes; and it requires that the data REALLY BE GONE.
But I really think that soft deletion should be the default unless you think you’ll be fielding user data deletion requests.
Re: Soft deletion probably isn't worth it
#216I've been a software dev since the 90s and at this point, I've learned to basically do things like audit trails and soft deletion by default, unless there's some reason not to. Somebody always wants to undelete something, or examine it to see why it was deleted, or see who changed something, or blah blah blah. It helps the business, it helps you as developer by giving you debug information as well as helping you to c…
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.
Re: Soft deletion probably isn't worth it
#217Earlier quoted context omitted.
Is not there any attempt to improve the soft deletion at the engine/SQL level? I can see it as a possible feature request.
There's the idea of temporal tables, https://pgxn.org/dist/temporal_tables/ It's not a standard (I think) but it'd let you do a cascading delete and then be able to go and look at the old objects as they were at time of deletion too. You'd need to do things very differently to show a list of deleted objects though.
It appears that there's been an attempt at standardizing temporal features in SQL in the SQL:2011 standard: https://en.wikipedia.org/wiki/SQL:2011
Re: Soft deletion probably isn't worth it
#218Earlier quoted context omitted.
Postgres can do it, you're correct, but in my experience it rarely happens with any view that's even slightly non-trivial even on recent versions of Postgres. Most views with a join break predicate pushdown. It greatly reduces the usecases of views in practice.
I haven’t had any problems with this at all and I’ve been using joins in my views for years. Are you using CTEs in your views?
Postgres 12 fixed the CTE issue.
Re: Soft deletion probably isn't worth it
#219Earlier quoted context omitted.
Postgres can do it, you're correct, but in my experience it rarely happens with any view that's even slightly non-trivial even on recent versions of Postgres. Most views with a join break predicate pushdown. It greatly reduces the usecases of views in practice.
I haven’t had any problems with this at all and I’ve been using joins in my views for years. Are you using CTEs in your views?
Postgres docs on CTEs: https://www.postgresql.org/docs/current/queries-with.html
Re: Soft deletion probably isn't worth it
#220Earlier 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.
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.