It's a set-and-forget solution, where you only have to reason about it locally to the schema. As they point out, `deleted_at` is viral, you have to incorporate it into every query that touches a table with that column type, otherwise you might get into weird behavior or possibly vulnerabilities/disclosures.
A set and forget solution at the ORM level is begging trouble IMHO. I don't like ORMs, but I really don't like the idea of my ORM being even more magical than it already was.
Id rather have the magic in an ORM than the database. Sometimes this desire to keep people as far as possible from understanding the database schema just makes everything much more complicated.
It's funny, that's exactly how I feel about ORMs! (That they're a way to create distance between the programmer and the database, when what you should have is understanding.)
Understanding the schema is important I agree - a great way to achieve that is to make the schema simpler to understand & to eliminate the need to for special case logic as much as possible.
In my view triggers like this are great, they're keeping the integrity rules you've established for your data ("though shalt not delete a record without creating a copy for observability and manual recovery by incident responders") out of your application code (where shifting requirements and much higher change rate put them in jeopardy) and enshrining them in the schema (where that can stay for decades, through rewrites etc.).
A set and forget solution at the ORM level is begging trouble IMHO. I don't like ORMs, but I really don't like the idea of my ORM being even more magical than it already was.