It's called Underhell, and it's calling itself a mod—though it's closer to a full, stand-alone game. Specifically, an episodic survival horror-adventure game.
Underhell is a Source mod, meaning anyone with a copy of the Source SDK Base 2007—obtained through Steam as a free download—can try it. The mod features large, non-linear levels, an original soundtrack, and a full voice cast, all serving to tell the story of Jake Hawkfield, a SWAT operative investigating her wife's recent death. Here's a small, twelve-minute slice of gameplay, released by the developer earlier this year, which shows how all that looks in motion:
If you're interested, you can grab the first chapter of the mod from either Mod DB (where the mod is currently #1 on their Top 100 list), or you can visit the official site for the torrent download. There's also a Greenlight entry you can vote on, if you so desire.
Underhell [Official Site]
Underhell [Mod DB]
Questions? Comments? Contact the author of this post at andras-AT-kotaku-DOT-com.