SD cards are indeed a really bad deal when it comes to reliability, especially if like me you tend to slam raspis everywhere almost as a reflex: before you know it, you end up with a large-ish fleet of the things in your house.
But: Raspis these days work 100% fine with SSD's, and while a small SSD is not yet as cheap as an SD card, it's not far off.
I have entirely stopped using SD cards for my Raspis for quite a long while now.
I also had good experience setting up PIs with read-only root devices. All data needs to be sent off-device (or at least onto external storage), but it wasn’t too tricky and should avoid the usual SD-card issues.
But: Raspis these days work 100% fine with SSD's, and while a small SSD is not yet as cheap as an SD card, it's not far off.
I have entirely stopped using SD cards for my Raspis for quite a long while now.