The Day Soft Deletes Caused Chaos

As a rule, I would not say a botched implementation is grounds to discard an approach. If you committed a change by mistake, acknowledge that as the root cause first and foremost.

That said, if certain approaches reliably lead to increased likelihood of botched implementations, then that could be weighed when making relevant architectural decisions; “soft deletion” (not liking the term, personally) may be one of such approaches.

On which note, a good way of implementing soft deletion without implementing soft deletion is audit trail, externally to the system.

Something was created and then deleted? Both events are captured in the trail. Pile on a stringified JSON dump of the change (where it’s going it won’t require a schema) and you have all you need with low effort.

An important invoice was deleted by a paying customer? CS will get pinged, and if it is indeed a problem—well, you have the trail, so reinstating is not automatic but is possible within the retention period.

(Speaking of, remember to observe the retention period. In general, it can actually be architecturally simpler if you maintain an audit trail separately: if that system deals with just trail records, you do not need to worry about accidentally expiring current data and can apply the same purge logic to everything within. Pro tip: there can be different retention policies for different kinds of data; may be worth capturing that.)

Client accidentally batch-wiped 50000 objects? Get a junior to write a script that walks the trail and returns the requisite SQL.

As a side benefit, having audit trail is a must for all sorts of compliance and reporting scenarios, so it addresses multiple issues in one go.

> The application ORM layer usually automatically filters out "deleted" records, but this convenience can lead to oversight when constructing complex SQL queries manually.

Fine. But what does that have to do with the root cause,

> I was migrating from soft deleting seat claim’s to a new collection explicitly meant for storing the deleted rows. [... I] removed the Paranoia library on the model that had abstracted away the soft deletion logic [...]

Reading to the end, I think I've figured it out:

> If you don't want to manually archive data all over your codebase, you can build an audit trail at the database layer.

> For PostgreSQL, there are a few different strategies to implement this described in our Ultimate Guide to PostgreSQL Data Change Tracking. This can also be automated by services like bemi.io, which plugs into a database and provides immutable records of data changes automatically.

I don't hate submarines but I don't like this one.

This sounds like you made a change that you didn't understand, and the conclusion is that this particular change must be evil, and never to be used. Got it.

Hard deletes also can cause chaos and issues. I've got a data warehouse with much more complicated ETL because the source system does quiet hard deletes... Which really throws a wrench in reporting on the state of the business.

It seems to me the issue in this article wasn't caused by soft deletes, it was caused by a botched migration from soft deletes to the history table method the author is recommending. Blaming the issue on soft deletes completely misses the actual root cause.

Before I start reading, I'm going to hazard a guess that this was caused by a uniqueness constraint on a nullable column.

After reading: ah, I was wrong. I feel like this was caused by a very specific library doing a very specific thing that the developer wasn't aware of; it has nothing to do with soft deletes as a concept at all.