Blogmark

This is a simple (as in ‘not complex’) pattern for saving any deleted records that you want to persist in a shared, schemaless table. Triggers fire after delete for any table you want to save and that deleted data is inserted as a jsonb entry.

I suggest an alternative to deletedat called deletedrecord, a separate schemaless table that gets a full dump of deleted data, but which doesn’t interfere with mainline code (no need to include a deleted_at IS NULL predicate in every live query, no foreign key problems), and without the expectation that it’ll be used to undelete data.

This article is great, but it leaves me unsatisfied in that I don’t feel it touches on the “why” at all. Why and for what purpose are we saving the deleted data? Is it just for debugging purposes? Do we ever restore it or otherwise reference it?

Brandur also suggests that a simple deleted_at column won’t really work for undeleting data. Why not? This seems like the best method for that use case assuming you don’t mind all the implications for app code, queries, and indexes.

Brandur covers the reasons that undoing soft deletes isn’t as easy as it seems in Soft Deletion Probably Isn’t Worth It:

The biggest reason for this is that almost always, data deletion also has non-data side effects. Calls may have been made to foreign systems to archive records there, objects may have been removed in blob stores, or servers spun down. The process can’t simply be reversed by setting NULL on deleted_at – equivalent undos need to exist for all those other operations too, and they rarely do.

I came across the primary article (in the show notes) after listening to the Soft Deletes episode of Postgres.fm.