I've had really great success with the TP-Link Tapo cameras. I have a number of their models, and they're all rock solid. Both in build, and image quality.

What drew me to them is that they have RTSP (and ONVIF for their PTZ models) built in, so you're not beholden to their app. I use them paired with Frigate, but they also all include an SD card slot for local recording.

They have on-camera detection for people, pets, cars, etc. No obligation to use their cloud services (I have them on an IoT VLAN with internet access disabled, and have never had an issue). If you're not using your own NVR then you'll probably have to keep them internet accessible for remote viewing.

Cameras have been in the Texas elements for a few years now and the streams look like they did brand new. I'd say my only complaint about them is that the mounts are oddly designed, but perfectly usable as is. 3D printing largely makes that a non-issue, though.