Steam restricts some Oblivion mods (that require OBSE), but works with all of the Mount and Blade mods and Fallout 3 mods I've tried. The issue with steam is that the retail .exe files are encrypted with their DRM so mods that depend on changing it don't work. The vast majority of mods that involve just adding files to directory structures work just fine. As for patches, steam generally has the latest patch for each title but you do want to let steam install them automatically because again there could be a DRM issue if you did it yourself. This varies based on the title so if you have concerns, google your target game along with "steam patch" or "steam mod".