Most cameras on that list are low cost, typically with 4-5MP sensors. They don't compete on the high end in terms of image quality but you will have an open source firmware stack with root access over SSH.
Models from Eufy, Cinnado, Jooan, TP-Link, WUUK, Galayou are relatively easy to source on Amazon or Aliexpress.
This is basically how I run Frigate at home today, with only the NVR able to reach the camera IPs on my no web access “internet of nothing” VLAN.
Any product that would fall under the good quality segment is primarily targeted at the commercial market, and nobody there is looking for open software.
It's not quite clear to me what the firmware is actually able to do, though. Apparently its motion detection is very basic, though, so you'd need to use e.g. Frigate for that.