Published fragment: Easy, alternative soft deletion: deleted_record_insert.

We’ve switched away from traditional soft deletion using a deleted_at column which is wound into every query like deleted_at IS NULL to one that uses a schemaless deleted_record table that’s still useful for debugging, but doesn’t need to be threaded in throughout production code, and razes a whole class of bugs involving forgotten deleted_at IS NULL predicates or foreign key problems (see Soft deletion probably isn’t worth it).

I recommend the use of a generic insertion function:

CREATE FUNCTION deleted_record_insert() RETURNS trigger
    LANGUAGE plpgsql
AS $$
    BEGIN
        EXECUTE 'INSERT INTO deleted_record
          (data, object_id, table_name)
        VALUES
          ($1, $2, $3)'
        USING to_jsonb(OLD.*), OLD.id, TG_TABLE_NAME;

        RETURN OLD;
    END;
$$;

Which can then be used as an AFTER DELETE trigger on any table:

CREATE TRIGGER deleted_record_insert AFTER DELETE ON invoice
    FOR EACH ROW EXECUTE FUNCTION deleted_record_insert();

Our results have been shockingly good. No bugs to speak of, no reduction in operational visibility, and less friction in writing new code and analytics.