Soft Deletes with Ecto and PostgreSQL

I done the is_deleted flag with a deleted_at timestamp that gets cascaded down the table relations. Restoring is an admin job and the logic is to follow the table relations down again restoring linked objects with the same deleted_at timestamp.

I have this in a metadata database that controls a data warehouse. Accidentally knocking out a metadata record could nuke large parts of the warehouse so i have to have this and a locked flag that prevents records from being removed or even soft deleted once data is in the warehouse.

> It typically rots as you expand your model and add features. It's not clear what significance different tables have, and you you can't "restore" things with any reliability or safety. That may be because of relational/index conflicts, or because you don't have enough information about how it was originally deleted to actually "restore" something that is both correct and useful, or because there are actually 3 different kinds of restoration schemes/scopes but current problem calls for a fourth.

The long and short of soft deletes is that it breaks my data's correctness guarantees WRT referential integrity: i.e. I 'delete' a record while there are still references to it, and the RDBMS cannot even warn me, much less stop me!

If I wanted broken data, I'd use a NoSQL DB, or flat files even.

I'm all for naming things more precise, but functionally speaking at that point it's really just semantics, you still end up with some DB column acting as a flag, and from then on you need to take it into account in every single query that touches that table in the whole app. Since most modern ORMs know how to handle soft-delete internally, it's far easier to just stick to the defaults and use `deleted_at` if you really need a way to keep the records around. And you often need, for the referential integrity of the historical data.

I've used https://github.com/nearform/temporal_tables before for that, has been pretty solid. Still need to manually keep the schema in sync, though.

Would simple "create unique index on .... where deleted_at is null" work for you?

At my previous job we used prisma, and what a terrible system, the amount of problems I saw there. Some have been fixed since, but it shouldnt take 3 major versions of an ORM to use an actual upsert operation instead of doing

if select() then update() else insert() at the client side, which is all sorts of wrong. Or the 50ms of latency it introduced per query by starting a separate process that generated the queries, or the lack of schema flexibility, or the idiotic new join features that transforms rows into jsons in a destructive way.

It's just bad software

1. https://brandur.org/fragments/deleted-record-insert

Nothing should modify it anymore after that.

It's made "can we get an audit table / undo delete for XYZ?" feature requests really easy.

[1] https://github.com/jazzband/django-simple-history

You are right, the main downside of the approach in the article is that you have to remember to filter the deleted_at column on constraints and consider how foreign keys cascade.

About triggers, we do link at the bottom to an article that uses them: https://brandur.org/fragments/deleted-record-insert

Since I am exploring a sync engine, having a separate table for deletes means I’d need to run additional queries when catching up, which led me to explore the single table approach. Let’s see how it evolves.

You read my mind. Yes, this is possible. I haven’t done it in prod, but I made a PoC. As to table inheritance, yes, you can also do that, and yes, in PG anyway it automatically propagates schema changes.

There are quite a few gotchas [0], though, so think carefully before doing so.

The below assumes you have a user/customer table named customer, and another copy named inactive_customer. One other note: if you use an IDENTITY for the PK in the main table, you’ll want to drop that first in the inactive_customer table, so the ids match. Example below.

    ALTER TABLE inactive_customer ALTER COLUMN id DROP IDENTITY;

    CREATE FUNCTION move_inactive_customer()
    RETURNS TRIGGER
    LANGUAGE plpgsql
    AS $function$
    BEGIN
        INSERT INTO inactive_customer(<schema>) —-replace schema with yours
        VALUES (OLD.<col> for col in cols) —-replace this pseudo-loop with your schema
        ON CONFLICT (id) DO NOTHING —-modify for your table constraints
        RETURN OLD;
    END;
    $function$;

    CREATE TRIGGER redirect_customer
    BEFORE DELETE
    ON customer
    FOR EACH ROW
    EXECUTE PROCEDURE move_inactive_customer();

See my answer on stack overflow

https://stackoverflow.com/a/78694335/4188138

However, in my experience, it's more easily maintainable to use normal deletes, have a trigger to move it to a schemaless table (jsonb column type that contains all of the data at the time of delete), and have manual restore logic via a good UX.

This is adding more complexity but... views?

For example: rename the "foo" table to "all_foo" and "CREATE VIEW foo AS SELECT * FROM all_foo WHERE deleted_at IS NULL"

I think you could even create a trigger on the view so "DELETE FROM foo" gets replaced with an "UPDATE all_foo SET deleted_at = NOW()", but I've never tried that before.

this is normally known as an audit table.

The problem with all of these methods though is that data is persisted, but the strategy to actually recover it is left as an exercise for the reader. And in my experience, never implemented and data is never actually reviewed/recovered in practice. The scenarios where a "recycling bin" is actually needed usually ends up encoded as a user-facing business requirement that ends up having an actually well-defined, non-generic, and actually recoverable logic that has nothing to do with the generically applicable soft/hard/audit strategies.

This seems like an unaddressed use case on the part of the RDBMS to me, a feature that shouldn’t be reinvented by ORMs or each individual project.

Instead, you should be able to just do DELETE SOFT FROM … in your queries and have the DB make the rows unavailable in all queries.

Or maybe even set it up, so that regular DELETE is treated like a soft delete, so that suddenly all of the ORMs and users wouldn’t be able to drop data on accident.

Maybe allow overwriting it with DELETE /*+DELETE_HARD*/ FROM … if you so choose, as well as do SELECT /*+WITH_SOFT_DELETED*/ … if you don’t want to introduce any historical incompatibility.

If such features don’t exist at the DB or a DB extension level, then we’ll be doomed to deal with ORMs reimplementing it which will be pretty bad if you ever need more than one tech stack connected to it, alongside the issues of all of your queries needing additional conditions.