So I just watched The Cabin in the Woods, and thought it was pretty good. This must be some kind of a spiritual successor to Thirteen Ghosts (I'll look that up later).
Well... for starters I would try my best to think about how to use a person's senses to create a terrifying experience. Some can be manipulated directly like: sight, sound, and possibly touch (using a rumble feature). While the player's other senses, taste and smell, would need to be affected indirectly through creative game making techniques. For instance: having the character in the game reacting to a bad smell or taste in a negative way (or vice versa). Eternal Darkness and Amnesia (I'm sure there are other games as well) added in another "sense" insanity that really made the game seem scarier.
I think catering to a specific culture's superstitions and folklore would also play a big role in the design of a terrifying game. I don't necessarily think that a good horror game would hold up "universally"; it needs to consider what the culture of the intended audience would deem terrifying. For example: the inclusion of a chupacabra in a game may be scary as shit for some people, while others might think that a bedroom fan losing its power in the night would be far more terrifying.
Perhaps some things are universally alarming, such as: sharp teeth, blood curdling screams, foul smells, darkness, isolation, torture, etc (of course the list goes on and on). But being able to recognize these elements and including them into a game would be one of my goals... if I were to make a horror game. Take for instance the Exorcist (I thought this was a scary ass movie), if you understand the concept of the Devil the movie is pretty damn scary; but if you don't, then not so much.
Of course there is the protagonist, and I agree with you, in that you need to be able to relate to him/her or it's all for nothing. That's why I would go the route of a creating a "blank slate" protagonist that basically leaves the player to fill in the shoes of this character. I choose this style because a lot of my favorite games have basically "blank slate" protagonists.
As far as the NPC cannon fodder goes, in order to get the player emotionally invested in their outcome, I agree that sticking to cliché horror movie tropes is not the way to go. Some games overcome this by making the NPC weak and powerless forcing the protagonist to become the savior. This works really well in the Horror genre because, the protagonist may be able to easily get away from whatever is chasing him, but what about the NPC relying on her/him.
I need to cut this short, but man you really got me thinking about how to implement a good horror game now. I haven't really given it much thought before, so sorry if this comes off as extremely rushed and scatter brained. I guess it's more of a brainstorming/response post.
One other thing I wanted to touch on real quick concerning The Cabin in the Woods and story telling in general is: staying true to the lore you create/use. The Cabin in the Woods fell apart at the point when the jock slammed into the electric force field and died. If they could just kill the characters in any old fashion, why would they rely on monsters to do the job. Perhaps I missed something here.