I'm not actively developing the
STAC video extension (although I'm open to new discussions/PRs) and it almost certainly won't meet your needs without some work, in a few areas.
The main point of the extension was to track embedded metadata (temporal geometry) within videos, although the current implementation (sidecar files) isn't great. Around the same time I made the video extension, OGC came out with the
moving features spec, which would consolidate some of the video extension sidecar files, and may be of interest to you, despite your cameras not moving (or maybe they do!). I believe there would also need to be work done on the STAC API side, particularly to enable frame-level search. Furthermore, I'm not sure if frame-level metadata is feasible from a performance perspective - there can easily be thousands of geometries in a single video.
Also, I can't claim to be a video expert (this was a hobby project for me), so there are likely numerous ways the extension could be improved to support more formats and use cases.