There’s a couple issues with it. I mean, it’s simple for games where you’re not using a bunch of mods, but at some point it just becomes excessive. Not to mention that when a mod updates, the mod will automatically update breaking your game sometimes, or when you’re trying to play a game, a mod just doesn’t update causing it to break the game that way too. There’s just a lack of control that’s often necessary when modding.
For beatsaber (which doesn’t use steam workshop), the there’s no steam integration and its a pain to deal with.
For terraria (which uses steam workshop), the modloader is smart enough to know which mods don’t work with the current version of the game and disables them and steam lets you easily change which version of the game you have by using the “beta” options. Only time I’ve had issues with updates breaking things since 1.4 release was during beta-builds of 1.4 tmodloader (and those were generally easily fixed by going to the discord and finding the file to fix it). Since then, no needing to find files and paste them over the existing files, etc. No trying to install one mod at a time out of a dozen or two until you find which one breaks it and redoing the whole process over again. Pretty sure it also just uses the version of mods that support the game version you have, deals with dependencies automatically, etc… The modloader will also direct you to things like the non-steam pages for mods (sometimes forum posts, sometimes discord, etc).
I don’t think the steam integration is needed for such a seamless mod experience, but its certainly compatible with it. And terraria is an outlier because the game devs encourage mods and has a huge and dedicated community. For smaller games with devs that don’t like mods, simply trying to keeping things working may take so much work, so that time that making a good integrated user experience is probably difficult.
Let’s be real here though, Terraria is an unfair comparison considering it’s modloader is integrated into the game itself and holds significantly greater support than most other mods with Steam Workshop support. (Oh and that the modloader is basically a community made mod manager anyways and is akin to using the community mod managers for the games mentioned below)
Stellaris, Rimworld, Divinity: Original Sin 2, Total War: Warhammer 3, Binding of Isaac, Dwarf Fortress, Space Engineers, Cities: Skylines, all of these very popular games with massive modding support are still plagued by the issues I mentioned above. And you know what? It has the issues you mentioned as well. Did you subscribe to an outdated mod? Oh, well, good luck figuring out which one that is. Forgot to download a dependency? Crash. Did a mod update and Steam just didn’t update the mod? Figure out what mod that was and unsubscribe to it and subscribe to it again. Did a mod just update and Steam updated the mod, even though the update breaks save compatibility? Well, unless the mod author uploaded the older version of the mod, good luck trying to have fun.