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.