I'm running Debian 12 (since the soft freeze I think) on my newly built AMD 7700X. I wouldn't exactly call that "ancient hardware".
Zero issue. No headaches for me. FWIW I'm running Debian since Debian 1.1 (1.1, not 11).
Alternatively, one can use OCI containers (e.g. Docker) for something even more recent, or software unavailable on Debian altogether. That way, you can have a very stable base server OS, with isolated bits of something more recent, feature packed, albeit sometimes a bit more unstable.
Those aren't the only options, of course, but they are pretty viable, depending on your use case. Personally, I also didn't see any issues with the ~10 year EOL of some of the RPM distros, as long as you care mostly about security updates/stability and not recent features.
I've had an order of magnitude more breakage on regular Ubuntu updates than on Debian testing. That being said, I ditched Ubuntu probably a decade ago and it might go out better now.
Anything of importance has deb package/official deb repo (Chrome, Brave, VS Code, Slack, Signal, Dropbox, etc), so you receive latest versions. There is flatpak as well. Newer kernel and some libraries could be found in backports repo.
I use Debian with 0 flatpaks/snaps/Appimages, only Debian repos and few official third-party ones.
I think Flatpak would be current best choice for these tasks if you are running a stable distro. But I think it would be good to have a distro where core OS and user application is completely separated, and can be updated independently. It would be nice to have latest application without fearing the next grub/DE/kernel/driver/... update might break your current setup.
There is nuance here. Technically, this is how it currently is, since the "core OS" is the kernel, and you can absolutely update that separately from everything else.
But lots of what people tend to think of as the "OS" are just applications (in Linux, anyway), so if that's the point of view, then it gets trickier. You can still update that stuff independently, but you have to decide what parts count as "core OS" to you and you'll need to update each of them independently.
True "user applications" are updateable independently.
Use the Source, Luke.
If there's something too shiny that I really need on my Debian, I compile it from source.
For example Emacs 29, with tree-sitter and whatnots, is just too shiny. So I compile it from source.
[EDIT] And, yeah, I know you can probably get a similar effect with Nix, but, ah, no. I tried it in a VM a couple years back, and it's a hard no. And I was a Gentoo user for years, so I'm not necessarily averse to some pain... but no.
On the off chance that I need some library, I may use a backports mirror. But I am not needing any at the moment and don't recall the last time I had to use backports. Must have been at least a couple of years since...
I also used to run Debian on my server machines but switched to NixOS - although I'm now seriously considering switching back to Debian since it has far less headaches and just works.
About the rest of the software, it's not very important which version I have. Actually, staying for years on an older Gnome shell won't force me to spend time to adapt to or disable new weirdnesses. Libre Office from n years ago would be OK. Evince, the same. Nautilus, give me the one from 2008.
Also, things just work. There's no application that I need that needs to be absolutely using the latest version. The only apps I can think of that require this auto-update themselves, like Steam and Bitwarden (for which I use an AppImage).
I'm not running anything fancy locally so perhaps it's an unusual case, and I will admit that I can't print from my desktop for reasons I've never bothered to look into, so the experience isn't 100%; but I prefer it to running Windows, which is becoming more and more like an OS-as-a-service-with-ads by the day.
Personally, there's only been one time in the last decade or so that not using the latest version of something has caused any real headache for me, and that was a manageable situation.
You want Testing. I don't think people really use Unstable unless they work on Debian itself.