I have a smart lock from Schlage that has all the features you just described, but also ZWave and so can be controlled over HomeAssisstant. We can both tell our friend a code to get in, but I can revoke a code remotely if I ever need to.

I also never get locked out. Usually I key myself in with the keypad, and there's a normal key slot too. It's also powered by a 9V -- but will also indicate to HomeAssistant what the battery level is

The key isn't that connectivity is bad, the key is that it should degrade gracefully when the more advanced features fail. Wireless is down? Can't unlock with my phone, but I can still use the keypad. Battery is dead? I can still use my physical key.