My app collects data from a centralized source where many different users can submit data about their organisation and their staff. Previously we used to just hard delete a users data when they were no longer relevant from the source of truth because it used to be reliable.
But a change to some software the clients use, messes with everything. They now DELETE all their data multiple times per month when they submit data. This is by mistake and due to a terrible design. Which means they lose the data for the users in our system and have to re-enter parts of it.
The software they use are stubborn and won't change the behaviour. We have tried educating the users about how to use it, but they don't learn. So now the last option is to soft delete the data for a certain time period.
Having looked at multiple Stack Overflow posts and blogs around the web, I don't really fancy any of the options, IE. add a column to the tables that need to be soft deleted. I started looking because that was my first instinct as well but don't really like it and the implications.
I was wondering if you could give me some feedback on a different idea. I have no experience with maintaining soft deletion and I don't know if my thought is terrible.
There is a user, their unique identifier is the same across multiple orgs. Per user affiliation with an org they have some userinformation like name, title etc. In our system they have one status row because it is the same in our app no matter what org they choose to connect as.
So if I follow the conventional way, of adding columns for soft deleting I would have to add one to each of the unique tables that contains user data, because their affiliation to a certain org might be deleted but as a user they still live on in our system from somewhere else.
But it seems like a hassle and a lot of change in the nitty gritty of my code to change things around to account for all these extra columns.
In my mind it would be simpler if I added a separate table containing the following:
And then whenever my app ask for data the api checks the new table; "is this person soft deleted from this org?" If true, they just block the request until they are restored if needed, or they will remain deleted until they are hard deleted within x hours of the soft deletion happening.
Instead of having to change many queries and logic all over the place.
The API uses EFCore as an ORM to connect to the database, in case that would help with any other smart fixes regarding its feature set. I have thought about creating custom savechanges logic, but couldn't come up with a good idea other than again adding a column to all the tables.
Please let me know if you need any more information.
J.D. Told me about row-level security which made me look around. It seems very useful, and it gave me some more insight into what I could search for.
So I came across global query filters for EFCore which seems promising. It allows the context to filter on all queries and when you actually need to ignore this global filter, you can simply do it on a query by query basis.
And it allows for dependency injection if you need to use something for the global filter that is based on the user that is connected. I created an answer based on this new information
It also turns out that what I really wanted was to deactivate the row until eventual activation or hard delete instead of soft delete. I didn't know the correct way to express myself.