Mods

Why not just play No More Room in Hell?

Mods... for what games?

I'd download Rinarin! ;)

black mesa source ;)
the stanley parable
day hard
etc etc.
see at moddb

Zy-El. Best mod for Diablo 2 so far, i hope there will be a similar mod for Torchlight II

GoldSrc - Sven Co-op and Natural Selection.

Black Mesa (DO IT NOW) it's a master piece.
is a total remake of HL1 in the most advanced source engine, and you just need SDK 2007 to play it. (also is going to be in steam in a few weeks/months)
also Missing information, it's a mod for HL2, that kinda rebuilds the "HL2 beta" that was liked before the release of HL2, the erly concept of the game (totally different from what is now HL2), aslo BOREALIS!

try Desura, you would have a LOT of mods there, and you can link your steam account with it, so every single mod is going to be launched from you steam client...

Single Player Source Mods

Black Mesa

Underhell

Grey

No more room in hell

Multiplayer Source Mods

Hidden

GoldenEye Source

Zombie Panic Source

Pirates, Vikings and Knights

There are TONS of Source mods out there but these eight should keep you busy for a while.

Also, I've found a really easy way to find and install mods without any hassle, use Desura, I don't think I've heard of a Source Mod that isn't listed there and Desura installs them in the correct folders (i.e. Steam/Steamapps/Sourcemods) with just the click of a button.