Steam stops updating
Collectively, the community will quickly find out if mods need fixing.
It always depends on what mods you use and their requirements for other mods.
New modders may not be aware of what makes Steam an awful Platform, whether you use Steam Workshop for mods or not.
Experienced Modders may not be aware of the changes to Steam's update settings (Skip to Heading, Recent Steam Change).
Supported mods are usually changed within days, but some mods get abandoned, will never be updated and just stop working.
Mods here on Nexus and otther sites can come as loose files and folders or packed in a .bsa/.ba2 file.Sadly I doubt Bethesda would have the vision to do this. This should make it easier for modders and Bethesda to find the cause of bugs in each patch.The system of limiting updates by setting the time for auto updates for when you are asleep and PC is off, is working.Which is a decent enough reason to be wary of any complex mod attempts until then.I agree with the OP though, Steam system is a good way to break a working modded game one is happy with.
The absolute worst way to update a modded game, is to do it automatically like Steam and the Workshop.