Amcrest AD110 doorbell connected over wifi to a recycled HP mini-PC (can be had cheaply on Amazon) running Frigate (https://frigate.video/) with a Google Coral accelerator installed in one of the internal slots. The Amcrest doorbell does have a cloud video connection if you want, but I firewalled it off from the internet and use the local RTSP streaming over to Frigate. Frigate talks to my local Home Assistant install too for automation. Also built a little buzzer with an ESP8266 and a simple piezo buzzer that runs ESPHome, which Home Assistant can use to play little sounds for stuff like person at the front door, lightning strikes in the area, etc... whatever you want to. The doorbell also has a local API to expose notifications when the button is pressed, which you can link to HA with amcrest2mqtt.
The wifi connection for the doorbell isn't ideal for security purposes, but it's good enough and a lot easier than getting CAT5 to my doorbell. It is more or less 100% reliable with my home wifi setup, though again I'm not really counting on it for any sort of reliable home security.
OP is absolutely right tho that this setup requires work to put together and maintain. Configuring Frigate can be especially confusing, but there are other local options like BlueIris (paid, and Windows only which kills it for me but it is very capable). I've been in this game for many years so it's all easy to me, but getting started is definitely a learning curve. Very fun though, if it's your sort of thing. The DIY home automation community is in a bit of a golden age right now, IMO.
The Ubiquity doorbell also does local person and package detection in addition to standard motion detection. Indeed, it has a second camera aimed down specifically to detect packages: https://ui.com/us/en/camera-security/special-devices
You also need a Protect NVR to record video, manage the camera and provide notifications - the Cloud Key Plus works perfectly for up to a dozen or so cameras (uses standard laptop hard drive) - but they have a variety of devices that can run Protect from the UDM Pro (also will serve as a router/firewall) to larger dedicated Protect NVRs with four or seven disks depending on how much storage you want.
It can be a bit pricy for just a doorbell camera, but if you add a couple of other cameras then the value gets significantly better. Cloud is optional other than being required for initial configuration and is used for notifications, remote access and remote administration.
Would you maybe share with us how you built it/which parts you used?