If Steam detects an out-of-date install or a modified ini, it overwrites it. That means
1) when a patch comes out you must either wait for mod patches or risk invalidating saved games and losing content
2) archive invalidation fails hard, so esp mods work, but any changes to bsa content fail
If you are just adding a cool sword to your game, steam is fine, but if you want to do any serious modding Steam will break your game. The nexus mod manager is good, but doesn't have anything to do with Steam or how bad it ruins mods. I have just started using mod organizer, which still integrates with NMM nexus links, so that's nice.
I can't speak for workshop. Civ5 is the only game where I tried the workshop. It worked Okay, but the in-game interface was not good, so I still prefer doing it myself, like with Civ4, but I can't blame Steam for that one. I have never successfully modded a Steam game (and had it still work after the next patch) and I have successfully modded TES3,4,5 and all modern Fallouts without it.