It depends. I have a vlan for work stuff thats connected to my office (site-to-site), and for some client and lab testing I have another VLAN that has a CF tunnel. For what I use it for (occasional tests and a demo of a concept for a client), its perfectly fine.
For my personal stuff, I don't need it/don't want to rely on it.
Pangolin on a VPS would be the most self-hosted alternative, but you're not really getting all the CF features there. Anubis is an example of a tool to protect from AI scraping, gatekeeper is an open source DDoS protection solution but meant for way more scale, DDoS Deflate is a shell script to help with the impact with dynamically blocking addresses, or HAProxy as a load balancer can be used as well.
I think for most folks though, getting DDoS'd would just mean stopping the public exposure for a while.
My issue with CF has nothing to do with its services, but about it being such a critical system to so many makes it a problem (much like people hosting too many things in AWS or Azure). As a service, CF works just fine.