1 year ago

A few months back I wrote https://brandur.org/soft-deletion (referring to the traditional strategy of putting a deleted_at column in each table), an assertion that I still stand behind. I’ve spent the time migrating our code away from deleted_at, and we’re now at the point where it’s only left on a couple core tables where we want to retain deleted records for an exceptionally long time for debugging purposes.

CREATE TABLE deleted_record ( data jsonb NOT NULL, deleted_at timestamptz NOT NULL DEFAULT current_timestamp, object_id uuid NOT NULL, table_name varchar(200) NOT NULL, updated_at timestamptz NOT NULL DEFAULT current_timestamp ); Previously, I’d suggested manually writing deleted_record into each deletion query, but we’ve since found a much cleaner way to do it. Here’s a function which will generically insert a deleted record from any source table: CREATE FUNCTION deleted_record_insert() RETURNS trigger LANGUAGE plpgsql AS $$ 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; $$; Invoke it as an AFTER DELETE trigger on any table for which you want to retain soft deletion records: CREATE TRIGGER deleted_record_insert AFTER DELETE ON credit FOR EACH ROW EXECUTE FUNCTION deleted_record_insert(); CREATE TRIGGER deleted_record_insert AFTER DELETE ON discount FOR EACH ROW EXECUTE FUNCTION deleted_record_insert(); CREATE TRIGGER deleted_record_insert AFTER DELETE ON invoice FOR EACH ROW EXECUTE FUNCTION deleted_record_insert(); Speaking from 30,000 feet, programming is all about tradeoffs.

During this time we’ve undoubtedly saved ourselves from dozens of bugs and countless hours of debugging time as people accidentally omit deleted_at IS NULL from production and analytical queries.