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.

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

>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?

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.

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?

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.

Most ORMs have built-in support for this. For example Laravel's Eloquent automatically filters out `deleted_at` records

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

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.

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.

> 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.

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.".