Can't most db systems just create a view over the data where archived_at is null, and this view is the table you use for 99% of your business needs (except auditing, undelete, ...)?
The challenges of soft delete
91–100 of 157 posts
Re: The challenges of soft delete
#92Earlier quoted context omitted.
Imo there should be some retention period for moderation but then hard deletion after that. Why would a moderator need to look up a deleted post a year after it was deleted?
"Hi SchemaLoad, I'm Officer John from the Department of Not Letting Children Be Abused. I'm following up on something one of your users posted three years ago. Can you tell me the IP address(es) associated with the following deleted posts: A B C D"
Think of the children! We can't have privacy because children might be abused if we have privacy!
Re: The challenges of soft delete
#93Can't most db systems just create a view over the data where archived_at is null, and this view is the table you use for 99% of your business needs (except auditing, undelete, ...)?
I'd go for two views - one, as you describe, that gives you the "active" records and another that gives you the "inactive" records.
Re: The challenges of soft delete
#94Soft deletes are an example of where engineers unintentionally lead product instead of product leading engineering. Soft delete isn’t language used by users so it should not be used by engineers when making product facing decisions. “Delete” “archive” “hide” are the type of actions a user typically wants, each with their own semantics specific to the product. A flag on the row, a separate table, deleting a row, these…
> Soft delete isn’t language used by users so it should not be used by engineers when making product facing decisions. Users generally don’t even know what a database record is. There is no reason that engineers should limit their discussions of implementation details to terms a user might use. > “Delete” “archive” “hide” are the type of actions a user typically wants, each with their own semantics specific to the pr…
> Users might say they want “delete”, but then also “undo”, and suddenly we’re talking about soft delete semantics.
I've worked for a company where some users managed very personal informations on behalf of other users, like, sometimes, very intimate data and I always fought product on soft deletion.
Users are adults, and when part of their job is being careful with the data _they_ manage and _they_ are legally responsible for, I don't feel like the software owes them anything else than a clear information about what is going to happen when they click on "CONFIRM DELETION".
"Archive" is a good pattern for those use cases. It's what have been used for decades for OS "Recycle Bin". Why not call it Delete if you really want to but in this case, bring a user facing "Recycle Bin" interface and be clear than anything x days old will be permanently deleted.
Re: The challenges of soft delete
#95Why deleted_at? We have soft_deleted as boolean which excludes data from all queries and last_updated which a particular query can use if it needs to. If over 50% of your data is soft deleted then it's more like historical data for archiving purposes and yes, you need to move it somewhere else. But then maybe you shouldn't use soft delete for it but a separate "archive" procedure?
Are you asking why we wouldn’t use 'last_updated' to store when the record was deleted? One reason is that you might want to know when it was last updated before it was deleted.
No, more like why you'd use a more expensive filter to hide soft deleted data, instead of just a flag.
Re: The challenges of soft delete
#96Privacy regulations make soft delete unviable in many of the cases where it's useful.
The opposite is true in countries where there are data retention laws. Soft-delete is mandatory in those cases.
In practice when I discuss retention requirements in my country (EU), the issue is the _maximum_ retention limit - after which data must be deleted. A minimum retention limit (e.g. business records for tax purposes) is almost never an issue. Systems that need soft-delete, bi-temporal state, etc. typically already have it, whereas actually deleting stuff is an afterthought.
I guess I'm saying the former is usually a functional requirement in the first place, and the latter is a non-functional (compliance) requirement.
Re: The challenges of soft delete
#97That's why adding a DELETE FROM ... RETENTION UNTIL for SQL would be very nice, combining both hard and soft delete with an internal TTL to reduce the impact
Re: The challenges of soft delete
#98This might stem from the domain I work in (banking), but I have the opposite take. Soft delete pros to me: * It's obvious from the schema: If there's a `deleted_at` column, I know how to query the table correctly (vs thinking rows aren't DELETEd, or knowing where to look in another table) * One way to do things: Analytics queries, admin pages, it all can look at the same set of data, vs having separate handling for h…
Soft deletes in banking are just a Band-Aid to the much bigger problem of auditability. You may keep the original record by soft deleting it, but if you don't take care of amends, you will still lose auditability. The correct way is to use EventSourcing, with each change to an otherwise immutable state being recorded as an Event, including a Delete (both of an Event and the Object). This is even more problematic from a performance sense, but Syncs and Snapshots are for that exact purpose - or you can back the main table with a separate events table, with periodic "reconstruct"s.
Re: The challenges of soft delete
#99Earlier quoted context omitted.
Are you asking why we wouldn’t use 'last_updated' to store when the record was deleted? One reason is that you might want to know when it was last updated before it was deleted.
No, more like why you'd use a more expensive filter to hide soft deleted data, instead of just a flag.
Checking whether `deleted_at is null` should be extremely cheap, and it avoids the duplication and desynchronisation of having both “deleted” and “deleted_at”.
Re: The challenges of soft delete
#100Earlier quoted context omitted.
Deleting data is also a very easy way to not get GDPR compliance issues. Data is a cost and a risk, and should be minimised to what is actually relevant. Storage is the least part of the cost.
Not an issue if you're not building SaaS
Depends on your jurisdiction I suppose. If you are in EU it's a question if you have PII or not - if you are a SaaS or not is totally irrelevant.