How 2 Soft Delete

ALTER TABLE users
ADD COLUMN archived_at TIMESTAMPTZ DEFAULT NULL;

CREATE OR REPLACE FUNCTION fn_shadow_archive()
RETURNS TRIGGER AS $$
DECLARE
v_cause_table TEXT;
v_cause_id_text TEXT;
BEGIN
v_cause_table := current_setting('app.current_deleter_table', true);
v_cause_id_text := current_setting('app.current_deleter_id', true);

IF (v_cause_table IS NULL OR v_cause_table = '') THEN
v_cause_table := TG_TABLE_NAME;
v_cause_id_text := OLD.id::text;

PERFORM set_config('app.current_deleter_table', v_cause_table, true);
PERFORM set_config('app.current_deleter_id', v_cause_id_text, true);
END IF;

INSERT INTO archives (
table_name,
record_id,
data,
caused_by_table,
caused_by_id
)
VALUES (
TG_TABLE_NAME,
jsonb_build_object('id', OLD.id),
to_jsonb(OLD),
v_cause_table,
jsonb_build_object('id', v_cause_id_text)
);

RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_archive_users BEFORE DELETE ON users FOR EACH ROW EXECUTE FUNCTION fn_shadow_archive();
CREATE TRIGGER trg_archive_establishments BEFORE DELETE ON establishments FOR EACH ROW EXECUTE FUNCTION fn_shadow_archive();
CREATE TRIGGER trg_archive_schedulings BEFORE DELETE ON schedulings FOR EACH ROW EXECUTE FUNCTION fn_shadow_archive();