assume I have a "good" mod, lots of people download it, which as far as I understand is a "subscription" in steam workshop terms, somebody breaks into my account and uploads a malicious update, now everybody will download the malicious update instead: unless there was a way to tie a code audit to a specific module that you download it seems like this would still be risky
That's a risk you take running any program that isn't sandboxed. I don't see how mods through Steam are any different. Until any popular mod is found to be malicious I don't think it's worth getting paranoid about.
mods that cannot initiate network connections or the local filesystem are not that much of a concern, but running an arbitrary C# program as your local user to me is a significantly different use-case, I think the steam workshop should integrate a checksum approval process where a user can decide for each individual update if they want to install or not.
I personally have bought skylines and find it great, but I am really wary about downloading mods for it as things stand now unfortunately.
Additionally, there are so many users testing these mods that I doubt any serious malware could be put on Steam without being caught by the community.