Yeah, I've been digging moddb from A to Z, yet you won't find too many great mods there, eventually try "dear esther" mod for hl2.
Problem is games modding is pretty prehistoric style, you have to bust your ass like hell just to create a simple model animation, for example. For example if I wanna make a character scratch his nose, I will have to code the hell out of me for just a simple crappy animation. Where's the freedom of imagination taking place? Down the road of too much unworthing work.
I was thinking today if I would create a wolf3d mod on source engine, what would I bring new in terms of interactivity, storyline, player animations and all that stuff. In half an hour of putting my brainz to work (lol) I had so many ideas already, that if I would spend time coding it, it would take me one year, already. I'd say the proportion between how you think a mod and the effective way you have the possibility to make the mod real, regarding the study you have to go for and the time you must spend, is unfair for any decent mind.
I wonder when will there be modern modding and editing tools, to allow people put their imagination in a game mod, without too much effort on the technical aspect of this job.