If a package is not in the repo? Sorry, you have to compile from source. Want a newer version? Compile from source and hope that the build environment dependencies are in the repo. Want an older version for some reason? Break out docker or KVM so you don't break your system.
None of this is fundamental to the model, that much is true, but in practice it is how all Linux distributions using a package manager/repo model without things like Snap, AppImage, and Flatpak work.
Here's the best part though: Even with Flatpaks and AppImage you can still use a repo! In fact Fedora Silverblue, which uses an immutable base system and installs everything through Flatpak and Toolbox, uses a Fedora controlled Flatpak repo by default.
[0] http://saimei.ftp.acc.umu.se/pub/debian-meetings/2014/debcon...
For decades, Linux package managers have been the killer app for Linux. They made installing and updating every single one of your applications trivial. You didn't google for sketchy download sites and unsigned exe's. You didn't have to fight the system to cleanly uninstall things. Even release upgrades were the smoothest thing ever. In 25 years, I've never had a Debian release upgrade go wrong.
Anyone bitching about package managers as user hostile is a flat out idiot.