I really like the "SimSurvivor" idea that was tossed around above, especially if there way a way to mix RTS and FPS modes, sort of like Shores of Hazeron.
In terms of the scarcity of ammo, I think System Shock 2 was the gold standard for achieving that balance of tension and playability. Unless you were on easy mode, you spent virtually the entire game low on ammo, but you usually managed to have at least one clip left for most weapons, unless you just sprayed bullets. The result was that it forced you take careful shots, and to avoid enemies where you could. It also forced you to control your "fight or flight" response. Nothing worse than turning a corner, running into a cyborg midwife and unloading several rounds into her, only to remember that you were loaded with frag ammo instead of armor-piercing, and now you've just wasted several shots. And now she's pissed...
FWIW, I thought the original Left 4 Dead was awesome. Haven't played the second one, but I thought L4D did a great job of building tension for a shooter. Not to mention the co-op dynamics like needing allies to save you from Smokers and Hunters, and the fact that you couldn't actually die as long as there was at least someone around to revive you quick enough. And the final level in the farmhouse in one of the single-player campaigns was pure Night of the Living Dead.