5 ms·

I used to work where (not a service for the general public) there was an "is deleted" flag for everything, but every now and then a client would insist that dat

I used to work where (not a service for the general public) there was an "is deleted" flag for everything, but every now and then a client would insist that data be really deleted, and depending on who it was and how they asked, we might go and do it, which was a huge hassle and would cause no end of problems down the line. On the other hand, "is deleted" flags end up causing issues when you forget to put "where not is_deleted" in your queries. Lately I've faced kind of an inverse situation - I have a system that I can't control where things are permanently deleted once in a while for multiple reasons (rogue users, aging out of old versions) and so as I accumulate information in a little data warehouse for reporting, I decided to implement an "is deleted" flag there. Eventually though, deleting from the source was turned off because it's really not necessary.

latch 7y ago
You can use row level security so that forgetting the filter isn't an issue.
- dorgo 7y ago
  as far as I understand this would only work for users who don't have access to "deleted" rows. Are access rules a good way to handle this? Serious question. My solution would be to have views as "guards" for every table.
  - latch 7y ago
    Views also work. I don't understand what you mean re "access". RLS is what removes access to those rows, that's the point of RLS. In postgresql, anyways, superusers aren't subject to RLS and the table owner, by default, isn't either. But RLS can be enforced for the table owner by a single alter statement.
dorgo 7y ago
>On the other hand, "is deleted" flags end up causing issues when you forget to put "where not is_deleted" in your queries. My solution would be a view for every table. Are there drawbacks? Other solutions?
- capableweb 7y ago
  Not sure about drawbacks but another solution would be to change the point where you do the queries instead. So you have a "user" object that is largely saved the same as most other "crud" objects, so you have a layer for those, add the flag there.
- perl4ever 7y ago
  Eh, the situation that I've been in, if I recall correctly, is that one has read/write access to all data for reporting, but not the ability to create views (or stored procedures etc) to share. Where I am now, (as far as Oracle goes) you can't even create your own tables under your own schema. A view requires a meeting with a DBA and their manager and really special, compelling arguments.
  - lilyball 7y ago
    Can you just have a general policy where every table that has an is_deleted flag also automatically gets a view, and clients must use the view unless they have a particular need to access deleted data?
- dbbk 7y ago
  Most ORMs have built-in support for this. For example Laravel's Eloquent automatically filters out `deleted_at` records
lostapathy 7y ago
The discard gem for rails has put a lot of thought into this and works pretty well, all things considered. It's not perfect, but I find using a library (either directly for inspiration) is often a shortcut to learning about a lot of edge cases from doing your own things. https://github.com/jhawthorn/discard https://github.com/jhawthorn/discard
- nimchimpsky 7y ago
  a library ? You literally just need a boolean variable in your datastore of choice.
Terr_ 7y ago
I also worked at a similar place, and fantasized about rewriting everything so that soft-deletion wasn't a per-row technical detail, but was instead an explicit modeled business-flow. Perhaps stored as a flag on an Aggregate Root (like "Customer" or "Project") at a much coarser level of detail. Sure, your queries still need to account for it, but at least you don't have a potential patchwork of inconsistent flags. P.S.: Random advice to anybody working on enterprisey stuff: 1. "Deletion" is too vague and broad. I strongly suggest you call it "deactivation" and some other word like "purging." 2. Deactivation is typically what a company actually wants, even if they don't know to ask for it. By phrasing it that way you also encourage stakeholders to think about "reactivation" before it becomes an architectural problem. 3. True purging is rare, and tends to be related to either disk-space issues or legal requirements. In the latter case, you'll want an audit-trail or tombstone of some sort, meaning it's still a real workflow and not just an easy SQL DELETE statement or something.
netsharc 7y ago
I've been on both sides of this insisting, if a company annoyed me too much (e.g. headhunters mailing too frequently) I'd drop the "data privacy laws" (nowadays GDPR) bomb and ask for my data to be deleted. On the other side, a customer got really pissed off by an online shop we maintained for a client, and asked for his data to be annihilated, we thought "What a douche.".
mekster 7y ago
> would cause no end of problems down the line That's just how badly the system is designed. Write a script to delete all relevant data and the user and it would be a simple op. > forget to put "where not is_deleted" Use views.

latch 7y ago

You can use row level security so that forgetting the filter isn't an issue.

dorgo 7y ago
as far as I understand this would only work for users who don't have access to "deleted" rows. Are access rules a good way to handle this? Serious question. My solution would be to have views as "guards" for every table.
- latch 7y ago
  Views also work. I don't understand what you mean re "access". RLS is what removes access to those rows, that's the point of RLS. In postgresql, anyways, superusers aren't subject to RLS and the table owner, by default, isn't either. But RLS can be enforced for the table owner by a single alter statement.

dorgo 7y ago

>On the other hand, "is deleted" flags end up causing issues when you forget to put "where not is_deleted" in your queries. My solution would be a view for every table. Are there drawbacks? Other solutions?

capableweb 7y ago
Not sure about drawbacks but another solution would be to change the point where you do the queries instead. So you have a "user" object that is largely saved the same as most other "crud" objects, so you have a layer for those, add the flag there.
perl4ever 7y ago
Eh, the situation that I've been in, if I recall correctly, is that one has read/write access to all data for reporting, but not the ability to create views (or stored procedures etc) to share. Where I am now, (as far as Oracle goes) you can't even create your own tables under your own schema. A view requires a meeting with a DBA and their manager and really special, compelling arguments.
- lilyball 7y ago
  Can you just have a general policy where every table that has an is_deleted flag also automatically gets a view, and clients must use the view unless they have a particular need to access deleted data?
dbbk 7y ago
Most ORMs have built-in support for this. For example Laravel's Eloquent automatically filters out `deleted_at` records

lostapathy 7y ago

The discard gem for rails has put a lot of thought into this and works pretty well, all things considered. It's not perfect, but I find using a library (either directly for inspiration) is often a shortcut to learning about a lot of edge cases from doing your own things. https://github.com/jhawthorn/discard https://github.com/jhawthorn/discard

nimchimpsky 7y ago
a library ? You literally just need a boolean variable in your datastore of choice.

Terr_ 7y ago

I also worked at a similar place, and fantasized about rewriting everything so that soft-deletion wasn't a per-row technical detail, but was instead an explicit modeled business-flow. Perhaps stored as a flag on an Aggregate Root (like "Customer" or "Project") at a much coarser level of detail. Sure, your queries still need to account for it, but at least you don't have a potential patchwork of inconsistent flags. P.S.: Random advice to anybody working on enterprisey stuff: 1. "Deletion" is too vague and broad. I strongly suggest you call it "deactivation" and some other word like "purging." 2. Deactivation is typically what a company actually wants, even if they don't know to ask for it. By phrasing it that way you also encourage stakeholders to think about "reactivation" before it becomes an architectural problem. 3. True purging is rare, and tends to be related to either disk-space issues or legal requirements. In the latter case, you'll want an audit-trail or tombstone of some sort, meaning it's still a real workflow and not just an easy SQL DELETE statement or something.

netsharc 7y ago

I've been on both sides of this insisting, if a company annoyed me too much (e.g. headhunters mailing too frequently) I'd drop the "data privacy laws" (nowadays GDPR) bomb and ask for my data to be deleted. On the other side, a customer got really pissed off by an online shop we maintained for a client, and asked for his data to be annihilated, we thought "What a douche.".

mekster 7y ago

> would cause no end of problems down the line That's just how badly the system is designed. Write a script to delete all relevant data and the user and it would be a simple op. > forget to put "where not is_deleted" Use views.