Frigate can definitely run on a Pi. It can also integrate with Home Assistant which can run on a Pi. As for turning itself off when you're home, I don't know.
I use Eufy doorbells, locks, and cameras. I realize they are a Chinese company, but that is almost unavoidable until the US gets back to actually manufacturing tech.
The reason I like Eufy is the local storage and no required subscription fees.
I use eufy as well. The only issue they recently had was that thumbnails were stored in their cloud if you chose to enable them on notifications. If you just select the text option, none of the footage is stored online and everything is local.
You don't need a doorbell camera, get a regular camera instead.
You need an app which will receive events from a doorbell via a server to receive data to interface with the app. Both of those things need to be owned and operated by someone to be approved for use on the app store. That's why a custom open source of doorbell as you envision it doesn't exist and never will.