I ended up returning all of the expensive stuff and only keeping a bunch of really cheap Amcrest PoE cameras.
Here is one such example: https://www.microcenter.com/product/634071/amcrest-5mp-ultra...
I've been very impressed with Amcrest cameras.
They support being configured without an outside internet connection.
They support dual streams.
They have all kinds of tuning settings, and come with sane defaults.
They support H.265 encoding, so you get good quality at small file (and bandwidth) sizes.
They also have MicroSD slots, and some cloud stuff that I don't use.
They work great with Fridate and Google Coral with is what I use them with.
Highly, highly satisfied and recommend.
Each camera is only $26 and works great. In fact I prefer their ecosystem to the super-bloated Ring and Nest ones that I used to have.
They have sound-activated recording and some basic AI features (package dropped off, I see a pet, your wife is home, etc.) but not specific sound detection.
I also run Skrypted[2] container on my Synology that concurrently makes me able to use HomeKit Secure Video.
I've been using this setup for years. I kinda miss the ease of the cloud, but love the configurability and modularity. What you save in leaving the cloud you will spend in personal time maintaining your setup.
1. https://blueirissoftware.com/ . Blue Iris has a huge learning curve, but the following video will fast track you into a viable setup without that curve: https://www.youtube.com/watch?v=jqc1ukrkMmI
2. https://www.scrypted.app/