There is also conda/mamba/pixi/etc. (anything in the conda-forge ecosystem) that can be used without root. Then there are Guix and nix, which (mostly) require to be set up by someone with root privileges, but which then allow unprivileged users to install packages for themselves. I think I have even used emerge rootless-ly at some point a few years ago.
The only thing I have not solved is password-protected sudo on the target host. I deploy using a dedicated user, which has passwordless sudo set up to work. Seems like a necessary evil.
IMO there is no point in doing that over just using root, maybe unless you have multiple administrators and do it for audit purposes.
Anyway, what you can do is have a dedicated deployment key that is only allowed to execute a subset of commands (via the command= option in authorized_keys). I've used it to only allow starting the nixos-upgrade.service (and some other not necessarily required things), which then pulls updates from a predefined location.