Nice writeup and a fun read! Never thought I would encounter a fellow NixOS and FoundryVTT user in the wild, but I realize the Venn diagram of these kinds of users do have more overlap than I thought.
With regards to your point about Foundry needing more power than a cheap VPS: I have it working fine on an Oracle cloud free tier VPS (unfortunately not the ARM-cores). That being said, it does want a little more power.
I am not running it with NixOS though. I am renting a temporary space, so I do not own or want to do too much locally right now, and Oracle OCI was only sort of working with NixOS. I did manage to install it with nixos-infect, but think I messed up the SSH with my reverse proxy and had no way to fall back to a previous version, which begs the question how would you?
You linked to "NixOS friendly hosters", do those give you access to boot options to recover from such a case? Since I did not have that option I determined the risk of failure too great for setting up NixOS on that particular VPS provider.
I also note that you use the nix-foundryvtt module and was wondering how your experience with it was. Does your sops define your login to the website such that it fetches the package automatically or do you have to manually install them?