The top destination for most PC gamers into mods is ModDB. If you dig the site, you may be interested to know they're now celebrating their 10th anniversary with a retrospective look at their accomplishments and history. In short, it's come a long way, and survived a lot (including three death threats). As they put it:
"Since  we've survived all manner of doomsday scenarios such as the 2006 "PC gaming is dying" catastrophe, when "closed-off non-modding friendly consoles" were set to take over. We've survived the great DLC avalanche of 2010 when previously mod-strong games like Call of Duty and Diablo announced you'll have to buy updates from them. Despite this modding is as strong as it has ever been, with millions of modders continuing to share their creations on ModDB."
The site currently boasts over 17,000 mods (1/3 of which are publicly available to try out), one million members, and its most popular mod ever Garrys Mod 9.