For me it is worth it to host a password manager for me and my family, although I just did a one-click install on Digital Ocean (using Bitwarden, not Vaultwarden ... so far).
To me (and I'm pretty ignorant about this stuff), the biggest weakness of a password manager is that it's the one link in a chain that somebody needs to break to have all of my passwords. Hosting that at bitwarden.com or lastpass.com or whatever makes it an even sweeter target because those are known targets. At least self-hosting makes it more difficult for somebody else to find my password manager.
Moving to Vaultwarden has its complications but it would also be fun, and maybe a cool project for the kids. And like you say, if we screw up and they lose their instagram password they won't even remember the pain once they've all grown up and the world is a dystopian nightmare of climate catastrophes, wars, etc. They'll be busy looking for the fattest grubs to eat.
If the cost of your security policy is greater then the cost of a worst case compromise then you are probably over investing in security.
With that in mind, does your policy seem appropriate to a user securing their Facebook password? Or their homelab service accounts?
*Cost in this case being the combination of literal currency and subjective costs like time/emotional well-being/public perception.