This can be addressed with PG views. You create a view where deleted_at IS NULL and now you query the view instead. Furthermore, if the view is “updatable” (which is the case when it only has filters), you can also do updates and deletes directly against it, and it all just works. So as long as your ORM/query builder can work with views, you are good to go.
I understand many devs want to avoid moving more logic to the database (I also personally avoid stored procedures and similar) but views are quite declarative and simpler to manage than indexes.
I haven't used row level security, but wouldn't it be easy to set the table up such that only a dedicated recovery role could select rows that have deleted_at set? Then the view wouldn't even be necessary.
This can be addressed with PG views. You create a view where deleted_at IS NULL and now you query the view instead. Furthermore, if the view is “updatable” (which is the case when it only has filters), you can also do updates and deletes directly against it, and it all just works. So as long as your ORM/query builder can work with views, you are good to go.
I understand many devs want to avoid moving more logic to the database (I also personally avoid stored procedures and similar) but views are quite declarative and simpler to manage than indexes.