I use it in the exact same scenario, a Raspberry Pi (Zero W) with a camera with motion detection and notifications on movement, my implementation may be specific though.
Each of my Raspberry Pi cameras runs motion (https://motion-project.github.io/index.html), and recorded files are stored on a NFS share. Each camera has it's own directory within this share (or rather each camera has it's own share within a parent directory), and the server then runs a python script that monitors for changed/added files, and runs object detection on the newly created/changed files.
If a person is detected in the file, it then proceeds to create a "screenshot" of the frame with the most/largest bounding box, and sends a notification through Pushover.net including the screenshot with bounding box.
There implementation is not quite as simple as described here, i.e. i use a "notification service" listening on MQTT for sending pushover notifications, but the gist of it is described above.
Edit: I should probably clarify that my cameras are based on Raspberry Pi Zero W. They have enough power to run motion at 720p - at around 30fps. Not great, but good enough for most applications. I've since migrated most to Unifi Protect instead. A little higher hardware cost, a lot better quality :)
I think the next thing, which is already happening, is embedded “AI” processors on SOCs. Apple is doing it with their A12 processors, and it’s probably only a question of time before you can purchase “general purpose” ARM chips with “AI” capabilities.