What are the best ways of finding such devices? Almost all the time when I look into some product it ends up being connected to some random cloud service with its own login.
* There are internet-connected controllers and local controllers so you'd also want a local controller. I've used an Aeotec Z-Stick for ZWave devices for around a decade, it plugs into USB, HomeAssistant accesses it directly, and the ZWave network itself is connections between the Z-Stick and the devices without the internet.
Source: I own one. :)
A shortcut however is checking out the homelab subreddit. People will post about the gear they are using in their stack.