Personally, I've had good luck with Reolink cameras. I block them from the Internet at the router, just in case, but they do seem to respect your choice if you disable the cloud/mobile app feature.

The cameras will upload jpegs and mpegs to a local FTP server based on configurable triggers, which include 'AI' detection of animal/vehicle/human, all running on-camera.

I wrote a simple script to put all the daily uploaded jpegs on a HTML webpage (each linked to the video) for review. Home Assistant also has an integration that can do streaming and grabs the detection triggers as well.

Most people I know in a similar situation went with generic ip cameras paired with a synology nas for an inexpensive option.