As many commenters pointed out, the ability to revert deletions is a highly desired feature. I cannot remember how many times our customers have deleted records by mistake and while deleted_at solution makes a revert trivial (update .. set deleted_at = null where undo_condition = true) it's much more complicated with the proposed condition. Why?
In any living system db schema is something that continuously evolves. New foreign keys, new columns, dropped foreign keys, dropped columns, new indexes. If soft deleted data remains in the tables it evolves with the rest of the system, but that's not the case if it all went into a json blob. Are you sure you want to remember all changes made to your data while trying to restore it?
The problem with queries is there, but I'm wondering about the scale of the problem. From my experience, tables do not go from hard deleted to soft deleted often, hence it's more a matter of habit to check the structure of a table you see for the first time and take deleted_at into account in case it's there.
As the author says, it's all about trade-offs, I would use an audit log for debug purposes (deleted_at does not answer who did it, adding deleted_by to every table adds a risk of split brain - what if deleted_at is null, but deleted_by is not?) and deleted_by to enable quick reverts to accommodate mistakes users and developers do.
A more problematic pattern for me is to trace the changes for different columns. Let's say you tag your uses as being a superhero. The moment you introduce this prop, analysts will immediately ask you who, why and how many times has changed this field and what was the value of the field at the time X. One can say that it's not necessary for all the fields, but I do observe that I have much less trouble in the development if I accommodate for it from the start rather then add hacks to support it later
In any living system db schema is something that continuously evolves. New foreign keys, new columns, dropped foreign keys, dropped columns, new indexes. If soft deleted data remains in the tables it evolves with the rest of the system, but that's not the case if it all went into a json blob. Are you sure you want to remember all changes made to your data while trying to restore it?
The problem with queries is there, but I'm wondering about the scale of the problem. From my experience, tables do not go from hard deleted to soft deleted often, hence it's more a matter of habit to check the structure of a table you see for the first time and take deleted_at into account in case it's there.
As the author says, it's all about trade-offs, I would use an audit log for debug purposes (deleted_at does not answer who did it, adding deleted_by to every table adds a risk of split brain - what if deleted_at is null, but deleted_by is not?) and deleted_by to enable quick reverts to accommodate mistakes users and developers do.
A more problematic pattern for me is to trace the changes for different columns. Let's say you tag your uses as being a superhero. The moment you introduce this prop, analysts will immediately ask you who, why and how many times has changed this field and what was the value of the field at the time X. One can say that it's not necessary for all the fields, but I do observe that I have much less trouble in the development if I accommodate for it from the start rather then add hacks to support it later