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 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.
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.
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!
> Yeah. As far as a user-facing "Undelete" button existing or being used... that's very rare in my experience.
This is pretty common in my experience, but often with slightly different terminology. I've seen "delete"/"undelete" but also "move to trash"/"restore from trash" or "mark for deletion"/"unmark for deletion" and even "archive"/"restore from archive" where deleted/marked/archived objects were excluded from normal business processes and were hidden in the UI unless the user went to a special page where they could search, view, and restore.
I've also seen chat functionality where users had the ability to delete messages but admin users had the ability to view the full history of a chat, including deleted messages. (The software was used by businesses to communicate with their customers, who were also businesses, so supervisors being able to read deleted chat messages was appropriate.)
In my experience soft delete is basically free if you do it from the start. I've never regretted soft delete but have always regretted the lack of it. However, it's easier if you also have a data retention policy from the start so you're forced to create automated cleanup processes and maintain them as you go along.
This is pretty common in my experience, but often with
slightly different terminology. [...]
I've also seen chat functionality where users had
the ability to delete messages but admin users had
the ability to view
In my experience soft delete is basically free if you do it from the start.
How would you set up a secure audit trail that didn't rely on the application and/or database at some level?
Blogged about one possible implementation using Debezium a while ago here: https://debezium.io/blog/2019/10/01/audit-logs-with-change-d...
Disclaimer: I work on Debezium
So many LOB applications (the part of the iceberg under the water) have modestly sized databases with a relatively consistent number of users. The data is small, but often complex, awkwardly structured, highly relational, etc. The challenges these applications face are different, but real.
An example of something that would be incredibly useful here but completely untenable for a Facebook scale system would a rich rewind mechanism. You can go back in time 15 minutes or execute a query like:
RESTORE my_db TO LAST POINT WHERE EXISTS (SELECT * FROM ACCOUNTS WHERE ID=1234)
How much trouble would that kind of query have saved you in your career? At some point this would be cost prohibitive but I bet 50% or more of all apps never reach that point.
It allows you to query a system (some databases support this) in a temporal context. This is useful in financial services where you have market data baked into a price you've offered to a client, where the underlying market data might have been later corrected by a vendor.
So you can query how the world looked last tuesday 4pm; but also - given what we now know, how ought the world have looked last tuesday 4pm.
Interesting stuff.
A long time ago, I worked on a such a system that provided service-based billing for an industry where rates were very fluid. Having this "at any point in time" view of key data was essential for many things.
https://www.cockroachlabs.com/docs/stable/as-of-system-time....
I haven't used this feature, is it possible to twist this to find a time associated with a table state so that you can then query the table at that time (allowing you to answer a question like what was the value of X the last time Y was true)?
SELECT * FROM employees
AS OF TIMESTAMP
TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE last_name = 'Chung';I, for one, have undeleted things tons of times, taking them of the trash can before emptying it, undoing the delete action (in apps where this is possible), and so on.
The exceptions are when there is a well tested query that affects a single account or something. Like GDPR
But did they though?
"Although I’ve never seen an undelete work in practice, soft deletion wasn’t completely useless because we would occasionally use it to refer to deleted data – usually a manual process where someone wanted to see to a deleted object for purposes of assisting with a support ticket or trying to squash a bug."
You cannot just keep user information forever "just in case" they are useful again.
To be honest, in the age of modern overprovisioned storage drives that remap blocks frequently, I'm not really sure you can implement genuine "hard" deletes without choosing significantly unorthodox hardware (or destroying a drive every time you need a single bit erased), no matter how much you want to in software. One of those details that I'm both surprised and unsurprised doesn't seem to have been addressed legally. I feel like a court ought to at least buy this aspect of the argument, so maybe they'll buy that it can be difficult in terms of the software too? Who knows. My guess is that a reasonable court would accommodate something that's reasonable for a given company, but there are lots of variations that could fall into that category.
The law is clear, you can't keep user data if the user decides that you should no longer have them. It's your own responsibility to find a way to do that.
> To be honest, in the age of modern overprovisioned storage drives that remap blocks frequently, I'm not really sure you can implement genuine "hard" deletes without choosing significantly unorthodox hardware (or destroying a drive every time you need a single bit erased), no matter how much you want to in software. One of those details that I'm both surprised and unsurprised doesn't seem to have been addressed legally. I feel like a court ought to at least buy this aspect of the argument, so maybe they'll buy that it can be difficult in terms of the software too? Who knows. My guess is that a reasonable court would accommodate something that's reasonable for a given company, but there are lots of variations that could fall into that category.
The deletion should be done in reasonable terms, i.e. you can't reasonably get the data back. Of course when you delete something from an hard drive being that an SSD or HDD till that block is not reused the data is recoverable, but that is another thing.
By the way, it's either not really difficult to secure erase user data: what you need is not to erase all the data of the user, is to encrypt all the data of the user with a symmetric key algorithm (such as AES, that is super fast) and only store the key in a system that gives you secure erase capability. When the user deletes his account you don't have to delete all the data but just the encryption key.
When the GDPR rules came out our company (pretty small at the time) pulled out all the stops to get ourselves into compliance, due to the above and other teeth put into the regulations. It seemed utterly irrational not to. Were we the exception, or the rule?
Probably almost all companies actually, including yourself (just to a different extent) - what did you do about the storage remapping thing I mentioned? Did it come up/did you guys discuss it? Do you believe you're in compliance despite your hardware (most likely) not guaranteeing erasure or overwriting of existing data? I'm curious how your assessment of that went, because I doubt one can be in strict compliance without guarantees from the hardware.
"Oh, but the key is still on the hard drive!" you would likely complain. No... the key could be stored any number of places that won't leave a copy. If you store the key on a TPM/HSM, then tell the TPM to delete the key, short of finding out that the TPM manufacturer failed to perform their duty in erasing the key, and simultaneously discovering a 0-day exploit to go in and retrieve that deleted key, it is gone. Full stop. It is unreasonable to ask everyone to do something impossible like prove that their TPMs are working properly, when even the TPM manufacturers cannot definitively prove this, so that is clearly not what the law is asking. You might as well ask companies to prove that they've never been hacked. How can anyone prove that?
Conceptually, one of the simplest ways to comply with this would be to perform database failover once a month, since you have a month to comply with these requests. Before the failover, you would ensure that all data is deleted that has been requested to be deleted. Then you bring up a new database replica, stream the remaining data to it, failover, and then destroy the key stored in the TPM on the primary and any other replicas. All that deleted data is now gone forever.
There are more complicated architectures where you could have per-user keys which are stored on an encrypted volume, and then you would be managing a handful of keys in the TPM to handle rotation of the encrypted volumes. Once a month, you copy the per-user keys that haven't been deleted onto a new encrypted volume, then tell the TPM to delete the key for the old encrypted volume: garbage collecting the deleted keys in an unrecoverable fashion. This avoids the hassles with having to failover a database so frequently, although having that level of automation around database failover has advantages too.
I'm sure some companies are content with treating the deleted data as erased, as long as their drives are properly encrypted. A rogue employee could potentially recover the data while the hard drives and the keys are still together, but the hard drives will be worthless once they're separated from the keys after they fail and are thrown away. A rogue employee could have stolen the data before it was deleted anyways, so how do you prove that rogue employees don't exist? Is this level of erasure good enough? This one I'm a little iffy on, but it still completely resolves your issues with the block remapping.
GDPR makes reference to "taking account of available technology and the cost of implementation", so I think it is fair to say to that GDPR is not asking you to erase any butterfly-effect waves that have propagated out from the read/write head of your hard drive. If the data is not recoverable by any known means, that is the purpose of the law, isn't it?
I will repeat that I'm not a lawyer, but your argument feels like a strawman designed to paint the Right to be Forgotten as something that no one can comply with. Your comments come off as snarky and acting like you think you're so clever. As if no one has ever thought about these problems before.
You are overly dismissive of the technical challenges of actually deleting data, particularly in large systems. Technically naive solutions like having separate encryption keys for individual entities sounds good but it doesn't actually address the problem.
Per user keys are also very useful for use cases like a personal file storage service, even if traditional RDBMS don’t work well with them. Techniques are situationally dependent.
I was not being dismissive of the existence of technical challenges, but rather dismissive of comments like:
> To be honest, in the age of modern overprovisioned storage drives that remap blocks frequently, I'm not really sure you can implement genuine "hard" deletes without choosing significantly unorthodox hardware (or destroying a drive every time you need a single bit erased), no matter how much you want to in software.
Which seem to completely deride the possibility of compliance.
Compliance is feasible, even if it has challenges.
Naively, it sounds like individual entity keys does solve the problem of deletion, but that your argument is that the tradeoffs aren't generally worth it?
Since the introduction of the Regulation, companies must now refrain from the collection of personal data if they cannot satisfy the rights of the data subject, including the right to erasure (which is obviously not an absolute right).
If the technical solutions aren't performant or scalable enough for data controllers, then the result is that they cannot collect personal data. The idea that companies have in the past rejected solutions which protect the rights of data subjects because they were unwilling to accept the technical tradeoffs is exactly why strong regulation such as the GDPR is required.
Frankly, your comment reads as contemptuous to the rights of the end-user. Thankfully, the EU offers protection to its residents from people with that attitude.
> Once a month, you copy the per-user keys that haven't been deleted onto a new encrypted volume, then tell the TPM to delete the key for the old encrypted volume: garbage collecting the deleted keys in an unrecoverable fashion. This avoids the hassles with having to failover a database so frequently
This ignores the existence of database indexes.
What I was saying (I thought quite explicitly) was that strict compliance to GDPR appears unreasonably burdensome to most companies, and thus most companies stop at a line they draw earlier (that line frequently being the lack of hardware guarantees re: block remapping). This is not the same as saying no one can comply with GDPR, and I had no intention of making GDPR look impossible to comply with. Quite the contrary in fact—I believe its current requirements can and should be complied with quite cheaply and efficiently, but the most effective path to that requires support from hardware manufacturers (and possibly others) that customers and regulators currently don't demand.
This is not a take-down of GDPR or a malicious strawman mockery of its requirements as you're (quite unkindly and wrongly) imputing me with. If anything, it was a plea for customers, regulators, and/or lawmakers to realize they need to expand their GDPR demands to require that vendors of other parts of the hardware/software stack (particularly storage device manufacturers) provide better support for GDPR compliance.
Also, this quote:
> To be honest, in the age of modern overprovisioned storage drives that remap blocks frequently, I'm not really sure you can implement genuine "hard" deletes without choosing significantly unorthodox hardware (or destroying a drive every time you need a single bit erased), no matter how much you want to in software.
That quote feels incompatible with your statement that…
> Quite the contrary in fact—I believe its current requirements can and should be complied with quite cheaply and efficiently
How can it be complied with cheaply and easily if it can’t be complied with without hardware support no matter how much software wants to? Surely you can see my confusion. Your last comment is far more in line with what I would expect to see in a discussion like this.
Regardless, I think I understand the argument in your last comment better now, but I still think destroying keys is the most effective way to achieve compliance. Even if a storage device manufacturer guaranteed that they would attempt to erase the specific block you want erased, there are problems with whether that data is still recoverable by reading the block repeatedly.
Expensive, maybe, but not risky.
If you're rolling the dice on a GPDR fine, the expected value of vague compliance is still largely positive, while the expected value of actual compliance is still slightly negative.
The expected value of the fine needs to be a larger negative than the expected value of vague compliance.
You don't even need to raise the fine value (which is a percentage/scale of revenue), you just need to make the probability of paying it approach 1.
In fact, with a higher probability of paying the fine, you could even lower the actual fine and still have it have a larger negative expected value than vague compliance.
This is not the purpose of cookies. The purpose of cookies is to store state on the browser side. Does GDPR interpret all possibilities of "cookies" as the storage and communication of privacy-violating data? Because that would be extremely unfortunate
It leads to having to do crazy things like individual keyed encryption per user, escrow hilarity, etc.
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.
Far from a magic cure, but by building it into the app from inception, it seems more possible.
A single AES encryption block is 16 bytes; internal storage of records is managed in terms of individual bits if you don't encrypt them. You also can't compress data encrypted in this way, which is table stakes for databases. The encryption key schedule alone for a single record will be much larger than the typical record! Requiring a per-user encryption key has the unfortunate effect of bloating database storage and memory requirements by at least 10x, while massively reducing computational throughput.
The design of delete-efficient database kernels is an interesting unsolved problem in computer science. No one knows how to do it. People that think there are trivial solutions have not studied the problem adequately. It hasn't been a priority to solve this problem, but its reputation for being theoretically difficult is well-deserved.
GDPR does differentiate between structured data (I believe it uses the term “identifiable records” or similar), and huge heap of unstructured data where an individuals data can’t be quickly retrieved as it’s own atomic unit. With much stricter requirements for anything structured.
So for data on a HDD that could be recovered, but is an unstructured mess. You’re probably ok, as long as you took reasonable steps to protect the data, like full disk encryption (which could occurs below the file system, and thus allow you to recover data deleted from the FS with a full disk scan, as long as you still had the keys). If you just had peoples data unencrypted on a HDD, and didn’t securely erase it before dispose, then that’s probably still a GDPR violation.
Instead of hard-deleting a structured record for Bob Smith, can you leave the record intact and scrub it of identifying data so that I don't e.g. break all of the records for orders that Bob Smith made?
The various laws (GPDR, and similar ones in my jurisdiction) abuse the hell out of vague descriptions like "personal identifying data".
For example, scrubbing a customer record of all names, phone numbers, contact info, etc and leaving only the ID in the record still makes it possible to personally identify the person by:
1. Looking through the orders records to build up a profile depending on what they order,
2. Then using the delivery records from the courier company to get an address,
3. Then using existing public information to repopulate all the fields in the customer record.
In any given business, no matter how big or small, simply removing all references to a customers personal information will still leave the customer personally identifiable. This process can even be automated.
Pseudonymous data is not anonymous data.
But yes, the GDPR obviously allows for legal requirements of record keeping. It does however require you to delete the data once those requirements no longer apply (i.e. you have to ensure data is still deleted after those several years, you can't just not delete it because the deletion time is years in the future).
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.
It’s almost impossible to guarantee instant delete, folks just care if it’s gone in a documented and/or reasonable amount of time, and predictably.
Correct?
You can’t just declare that it take years to delete data, GDPR sets reasonable limits on how long a company can delay true deletion.
Various types of data have various retention times for various reasons, some being legal reasons.
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 computer systems.
A company can't respond to a tax audit with "well we lost a billion dollars this year but all our customers wanted their accounts deleted so we can't provide any documentation of it." I mean, you aren't even going to get to the audit if you can't fill out your taxes in the first place because you had to delete all your financial records.
Most computer systems/software products in existence aren't free media apps, yet the "privacy circlejerk" constantly talk in generalities as if they are. I'm sick of this, most applications have a business justification for using some form of soft delete for at least some items.
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’s for any identification of an individual, regardless of usage. Most “media apps” don’t have any other law that’s in effect, so GDPR is mandating what you need to do. (IANAL but worked on implementing GDPR with lawyers).
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.
You can ask, but the right to have data erased is not an absolute right. In fact there's an enumerated list of the circumstances in which the data must be deleted but data controllers are otherwise under no specific obligation to delete data, and there are even a series of criteria which dis-apply that obligation.
Data controllers absolutely must delete data by default. The basic rule of GDPR is literally "privacy by default" (or "data minimization"), not "data hoarding by default". There are plenty of exemptions that grant companies the right (or rather require them) to delay the deletion by amounts of time as long as many years (e.g. for tax purposes), but they are only permitted to maintain only the absolute minimum required to fulfill these obligations and they must delete the data as soon as possible once these obligations have been fulfilled.
If you are obligated to keep a set of PII for 10 years, you must delete it in 10 years, unless you (still) have consent to keep it beyond that time limit.
It can't be "an absolute right... unless", it's either absolute or it's not.
And if you read Article 14 the first paragraph says that "The data subject shall have the right to obtain from the controller the erasure of personal data concerning him or her without undue delay and the controller shall have the obligation to erase personal data without undue delay where one of the following grounds applies" (emphasis mine) and then lists six grounds. Further to that, paragraph 3 dis-applies paragraph 1 for more reasons than "another law states you can keep it".
>The company doesn’t own your data, you do.
Data controllers do own the data (insofar as one can "own" data), but data subjects have rights over the processing of those data that limit what the controllers may do with them.
A user has a long history of participating on your forum and other users have quoted their messages far and wide. Collectively all of the messages posted on your forums (with or without timestamps) reveal a few PII about the user. Do you have to delete those?
The user filed a bug report about a functionality not working, do you have to delete the text of the bug report?
Arguably if your user table look like [user_id, creation_date, deletion_date, status, account_type] then this table does not contain any PII.
Assuming that user content is not automatically PII, whose responsibility is it to track where PII can be?
Sometimes users doxx themselves (like mistakenly sharing tax return forms instead cat pics), in such a case it is the user responsibility to signal this to you.
If the user filed an issue saying "when I insert my name (Abe Cox) the input validation fails" is it you or the user that need to read through all the issues to find this case?
My point is that GDPR + right to be forgotten cannot make it look like you never had an account at all, especially without user assistance.
Technically yes.
> The user filed a bug report about a functionality not working, do you have to delete the text of the bug report?
Depends of the privacy policy of the bug tracking application. If it's stated that the report shouldn't contain any personal data you don't have to delete it. If the user asks for deletion stating that it contain personal data you have to delete it.
> Arguably if your user table look like [user_id, creation_date, deletion_date, status, account_type] then this table does not contain any PII.
It doesn't, as long you no longer have any way to associate user_id to the user personal data, you can keep it.
> Sometimes users doxx themselves (like mistakenly sharing tax return forms instead cat pics), in such a case it is the user responsibility to signal this to you.
In that situation if the user asks for deletion of the content you have to provide it. You are obviously not asked to monitor errors about the user.
For example, if you created a social media app and later left the company, it could still be determined that if you had an account it likely has one of the lowest user ID values if those were assigned sequentially. So even if your name was removed from it and none of your posts provided any identifying information about you, the user ID could still qualify as PII.
Likewise if the user ID is public and non-PII content associated with that ID is linked elsewhere identifying the author of that content (e.g. a news article embedding a post) the identity of the author is still compromised.
The problem is not just that users may submit PII in unexpected places, the problem is that even if the data type could not possibly allow them to, metadata beyond your control may actually still taint your supposedly anonymous data.
As an extreme example, if user A takes a screenshot if their own user setting page on a image sharing site, then sends it to user B that uploads it to the same site. Who is responsible for this piece of data? does share-a-pic.web need to scan all images for the username of a user that is deleting an account?
This is a convoluted example where the answer is obviously no, but it shows that a purely principled answer might be unsuitable.
Especially since non PII data you own mixed with non PII data someone else owns can become PII and you do not know what data others have.
(you could also replace the user ID in all locations with a new tagged UUID to preserve referential integrity in the DB, but there not much more than 2^32 humans in total, it s very hard not to be deanonymizable)
That way log and system context is available for debugging, but the system no longer holds any PII. It does delete some logs, but anonymizing data seemed better than hard deletion. It wasn't hard to do, it took legal longer to approve than writing and testing the code and has come in handy a few times for debugging errors.
Edit: typo
So yeah, you're right that not deleting user data just because it's inconvenient is wrong and illegal, but that doesn't mean there aren't legitimate usecases for soft deletions of users.
Users can retract willing consent, but willing consent is not the only thing that allow companies to store user data.
Soft deletion of users whom had an account with transactions on it is fine
Art 17 comma b explain this quite clearly
Gdpr is amazingly readable.
Soft delete immediately when requested. Hard delete (if still soft deleted) when GDPR requires it.
Besides it's not like all data is the same. Use what's appropriate when it's appropriate. Nobody's saying "you must always soft delete, and then immediately tweet the audit trail, print that tweet, put that printout onto a rocket, and launch it to the moon for later retrieval. with a blockchain for extra GDPR hate".
There's a grace period of 30 days. We hard-remove after 2 weeks allowing a user to change their mind if they deleted it by accident. More than enough.
This is the actual text of the law[1]:
> The data subject shall have the right to obtain from the controller the erasure of personal data concerning him or her without undue delay
There is a one month limit of the right of access[2] and right to be informed[3], which flow into some of the articles of the law; and certain articles also mention a month's limit on being informed, e.g. from article 12 parts 3 and 4 [4]:
> The controller shall provide information on action taken on a request under Articles 15 to 22 to the data subject *without undue delay and in any event within one month* of receipt of the request. That period may be extended by two further months where necessary, taking into account the complexity and number of the requests. The controller shall inform the data subject of any such extension within one month of receipt of the request, together with the reasons for the delay. Where the data subject makes the request by electronic form means, the information shall be provided by electronic means where possible, unless otherwise requested by the data subject. If the controller does not take action on the request of the data subject, the controller shall inform the data subject without delay and at the latest within one month of receipt of the request of the reasons for not taking action and on the possibility of lodging a complaint with a supervisory authority and seeking a judicial remedy.
I've marked out those phrases because they show the language of the law that you will see again and again in the articles - without undue delay and within one month. The latter does not obviate the former.
Hence, and in fact, there is no part of the law that explicitly gives you a month to delete something. If you could delete something right now but you leave it hanging around because you have arbitrarily chosen a job to run once a month and there's a leak in that time, that would be undue delay.
[1] https://gdpr-info.eu/art-17-gdpr/
[2] https://gdpr-info.eu/issues/right-of-access/
If you want to go to court and argue that the user should've know that pressing a big red button with "DELETE!" on it really means soft delete, be my guest.
The 30 days wasn't particularly taken from the regulation, the actual grace period for the deletion should be 90 days in total, but I'm not a lawyer nor certain about it.
What I am certain about is that your original thesis, which is soft deletes being illegal under gdpr, is complete bullshit
Who is "you" here? Perhaps if you were less defensive you'd notice that I'm not the person you originally replied to, which, by the way, makes your response nonsensical.
Edit: I'll add, that the only person who is likely to get anywhere near to "complete bullshit" is going to be the one who writes "but I'm not a lawyer nor certain about it" and doesn't provide a shred of evidence to back up their, frankly completely ignorant, notions.
90 days grace? Risible.
Also note, if you use a soft-deleted column, indexes need to be keyed by that column as well if you want to access non-deleted objects quickly. That's extra complexity.
but that is less of a cost than having to add "deleted=False"
predicates in all of your queries.
It also depends on if you're designing an app for this from the ground up or if you're trying to retrofit an existing app with 90 million different hardcoded queries.
Also depends on what you want to do with the deleted records. Do you want to do things with them? Maybe after a user is deleted, you want them to be able to log in, but you would prefer them to see a "sorry - your account has been deleted" message instead of "user not found." Maybe you want your support staff to be able to look at deleted users. Etc. Now you need to update your app logic so that it's flexible enough to look at "users_deleted" and "users". Which may be at least as onerous as messing with the WHERE clause on every single query.
To be clear, I don't hate the "another table" solution. It's the right choice in a lot of situations IMO.
It's like people have forgotten what views are.
Nonsense like a "deleted" column on your main data table just seems silly.
Sure, you have to be conscious of adding "where deleted_at = NULL" (or the opposite) to all your queries. If you're going through an ORM this is usually trivial e.g. in ActiveRecord it can be a default scope on your model.
On a proper database you can do your "undeleted" with triggers
and it's relatively trivial.
If I was retrofitting an existing application with soft-deletes I would definitely favor this solution.
There are some obvious advantages over a "deleted" column on your main table for sure.
One drawback is that you now have to keep two table definitions in sync. You also have to add conditional logic in a lot of places to handle whether a given piece of code is looking at a normal user from the "users" table or a deleted user from the "users_deleted" table. Instead of messing with the WHERE clause on every query, now you're messing with the table name on every query. So that's a wash IMO.
Of course, you can have your cake and eat it too. You can e.g. keep your deleted crap in the main table (which makes certain things easier) and then hard-delete or archive it to another table after N days.
And several of these "columns" were actually JSON blobs that probably should've been in their own table. But what do I know. I left!
Once you have an existing tirefire, it's a matter of damage control and "what is the least bad way to accomplish new functionality?" and the answer to that will always be unique for each unique tirefire.
I don't actually like using a "deleted" column, my standard table has a status column, and deleted is one of those states, along with active/pending/suspended/etc, as the needs dictate. This way I get soft deletes for basically free both in the schema, but also in the queries (which would generally default to active), so it's not really the spaghetti that the author discusses.
Suppose I soft-delete an active, pending, or suspended user using your scheme.
Now I need to un-delete the user. What status should they have? We don't know.
This is another "best practice" I've learned over the years. Those flags/statuses you think are mutually exclusive? Maybe they aren't. Or maybe they are now but won't always be. It's usually easier in the long run to give each status its own column even if you think they'll always be mutually exclusive. Because I mean, what are you really saving? A few bytes per record? In most cases that's not worth it.
EDIT: Soft delete is a trivial piece of code when the framework has a well defined transaction system for its ORM. It's not really related to Rails per se. Your statement is extremely disingenuous, while trying to look smart. Audit trails _can_ be(but don't have to be) more complex, especially when the framework uses a lot of stored procedures to handle operations. But other than that these frameworks are specifically designed to REDUCE complexity of such operations, dependency costs - which are huge in node.js, specifically because you can mix and match anything into everything.
Node.js people tend to stitch together XSS solutions, random templating solutions based on their frontend work, even basic salting of auth passwords becomes unpredictable because you have 30 options on minimal auth libraries.
But yes nothing is ever free. If you want to use Rails you still have to learn ruby and the framework and a basic understand of how ActiveRecord builds queries if you want to be writing performant code. And the same applies to Laravel, Django, or whatever of the 50 patchwork node.js solutions you want to base your code on.
Also you're comparing language/runtime with an actual framework and then dogging those users...
If you want to compare Rails with Node/PHP then I'd suggest comparing with things like Laravel (PHP), Adonis (Node) and you'll find everything you can do in Rails is done in Node/PHP too.
There's a reason ORMs are so popular.
Raw SQL can be a bit problematic if it is spread out in the entire code base with little structure ("littered"), however that is relatively easy to fix by centralizing the queries with a repository pattern. It is much harder to fix an ORM ten years later.
Do we know why ORM's are popular? I don't think it has to do with that ORM's are good, rather that developers want to avoid SQL because lack of experience, but that is me guessing based on my prejudice (I was myself in that category as a junior).
I have written in the past why ORM's are bad https://news.ycombinator.com/item?id=27407690
Anyways I appreciate the name drop of Adonis. I'll have to give it a fair shake when I'm not feeling quite as cynical because I _really_ do want to find a solid Node framework in this area.
To me a framework which doesn't come with a clear way to do translations, background jobs, query builder, ORM, migrations, asset bundling, validations, error handling, etc it is not full stack. It is just "server code compatible" at best.
Later I've found out the author of the framework has fake accounts pretending to be a happy user of it, and that was the end of it for me.
So, not comparable at all in my opinion.
Which is funny because Laravel give you this for free as well through it's ORM. Soft deletes are an easily solved problem with $table->softDeletes() in your migration.
"Soft deletion" and "audit trail" are technical terms we developers come up for solutions the business wants but maybe hasn't asked for yet. It's not really a soft deletion it's a "deactivate" or "hide". Likewise, it's not an audit trail it's a "history" or "undo". Most of the time your stakeholders and users actually want these features, but don't ask because they perceive this as more expensive to build then just a "delete" button.
Depends on the industry. The one I work in audit trail is a well-defined and mandatory business concern.
I think Datomic is neat, and I’d like to use it, but it is prohibitively expensive for a personal or hobby project. Personal projects are where I get excited about tech, and when I’m excited I’m more likely to adopt it in my day job.
They’re really shooting themselves in the foot by not having a one-click install free tier or a self hosted option.
I really like the concept of datomic though.
I can't wait until Postgres has this kind of functionality baked in. It's such a nice feature.
“I’m sorry sir, I didn’t think that clicking that button would actually delete something!”
“What exactly did you think that button would do?”
Soft deletions are awesome
But... your experiences are also real and I believe you when you say that your experiences DO support your conclusion. :)
The "soft delete" design decision is usually pretty impactful and is in my experience potentially much more of a pain in the butt to implement later if you haven't included it from day 0.
Audit trails and soft-deletes are also crazy useful for developers (both for debugging and for general cover-thine-ass utility) even if end users never touch them.
Whereas, tags are easier to tack on later and are not intrinsically useful to developers.
> 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's a mysql-compat database that is versioned and forkable - basically imagine git and mysql had a baby. Every transaction creates an entry in the commit log that lets you see when, how, and why the database changed. And just like git you can `dolt checkout <hash>` to see what the data was like at some point in time, or `dolt checkout -b temp-branch <hash>` to make exploratory changes to some historical version, or `dolt revert` to revert a transaction... etc.
There is a lot more power that comes with making the entire database versioned and forkable by default. For example it makes it much easier to recover from catastrophic bad code pushes, etc.
note: Dolt was forked from Noms, my previous project, but I don't work for Dolt or have a stake. Just a fan.
Yes.
To note, with GDPR there's now legal reasons to do so regarding user personal data. That can be the moment the devs realize they actually can't delete the data, because they soft deleted for so long, many relations are now interlocked and the data model needs to be changed to give a starting point to the deletion cascade.
My lesson from that was to at least have one test deleting a mock user that spans the maximum data breadth of the service . We caught a bunch of these loops in test at dev time and that was pretty great.
My lesson from that was to at least have one test
deleting a mock user that spans the maximum data
breadth of the service . We caught a bunch of these
loops in test at dev time and that was pretty great.
In my experience this happens “rarely”, not “always”.
It can happen, and in some ultra-rare cases the impact of not being able to recover some data might be huge (company-ending, even), and engineers are good at worrying about such edge cases. That’s why we habe protective measures like soft deleting and event sourcing - because of nightmare edge cases, not because we are always having to actually use them. It’s driven by engineers avoiding their worst nightmare: having to say “I’m sorry, I cannot solve this problem for you. The data is gone.” It’s a peace-of-mind thing, not an everyday-need thing.
IMO soft deletion is a hack trying to fix problems in CRUD, which is a hack in itself.
CRUD attempts to model everything in the universe as a collections of mutable items, loosely based on RDBMS/SQL.
Event Sourcing is more realistic: it models everything as a append-only logs of immutable events/facts, which preserves both the historical data and, more importantly, the original intent.
Unlike CRUD, Event Sourcing is technology agnostic.
I don't think soft delete is wrong per se, but it is something that should be native to the database engine.
This allows you to do a `select * from users where deleted_at is null` to get the active records, but also know when the user was deleted if you need to audit / rollback.
- How many records are there? Enough that performance will be an issue? If I'm planning on keeping things in the same table, can I utilize database features like indexes and partitions to mitigate any perf issues? (For some access patterns, partitions might solve 100% of your perf concerns)
- How common is deletion? Are we expecting 1% of the records to be soft-deleted, or more like 90%? If it's the latter, you may not want all those records clogging up your main table.
- Is this greenfield development, or am I adding soft-delete to an existing app? Greenfield favors "same table" soft delete; if you're retrofitting an existing app it may be better to keep deleted stuff in a separate table so that you don't break existing functionality.
- What do you want to do with the soft-deleted records? Are there times when you want to treat them just like regular records, e.g. "Give me a list of all the users who joined last week, even if we've deleted their accounts?" If the answer is "yes" then a lot of those things will probably be easier if you keep deleted and non-deleted in a single table.
But there’s always something that needs to be undeleted. You can either have an easy way to do it or restore an entire DB backup and cross query the missing records. Soft deletes are a lot easier.
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 cover your ass when you are blamed for some data loss bug that was really user error.
Soft deletion has obvious drawbacks but is usually far less work than implementing equivalent functionality out-of-stream, with verbose logging or some such.
Retrofitting your app and adding soft deletion and audit trails after the fact is usually an order of magnitude more work. Can always add it pre-launch and leave it turned off.
If performance is a concern, this is usually something that can be mitigated. You can e.g. have a reaper job that runs daily and hard-deletes everything that was soft-deleted more than n days ago, or whatever.