I wouldn't disagree that it's overly focused on, but I would disagree that it doesn't ruin the men who face it.
No spoilers on Bloodborne please, I'm still damned early in the game, and have yet to encounter anything beyond a room that kills me inexplicably, but I'm looking forward to digging into more of the story.
To the point there are a lot of Lovecraft's stories where, you're right, simple exposure isn't enough, but rather it's the slow accrual of knowledge that unmakes the man or ruins him in some way. Like 'Facts concerning the Late Arthur Jermyn and His Family'; no monster, just knowledge. All the guys who faced the 'Dunnwich Horror'; while not mad they clearly were going to enjoy evenings of heavy drink for the rest of their days. Or that guy who looked back over the glacier at the end of "At the Mountains of Madness"; I on't get the impression that he was going to be an exemplar of stability.
That said, I think people latch to much on the monsters and gods and madness of Lovecraft's stories and ignore the fact that nearly every one of his characters was a brilliant mind or sharp critical thinker. No one seems super excited to focus on that. Or what about his focus on family lines, and the horror that can come with discovering that your blood isnt quite as pure as you might have suspected? Sure the blood aspect is surface applicable in the game (at least where I'm at), but the lineage is conspicuously absent.
Sure a lot of the fear build in his blood line focused tales have less impact these days when the discovery that your great grandmother/father was black holds as much power as a fart in a windstorm (hurray for base level progress), but what sort of game could be made that focused on that element? How fun would a mash of the game Evil Genius and the show House of Cards be if it was playable on a multi-generational scale, and keep all the weird cosmic horror as more subtle elements on the periphery? I have no idea but I'd like someone to try!
As to your point regarding Twin Peaks, YES! The benality of evil and the horror that exists in every household's closet would be a fantastic thing to focus on. Fun fact, I live two streets down from the home used as the establishing shot for the Palmer's place and it would be a real treat to see something on the small city scale of Everett Washington get used as a setting for a game. It's a nice place, but there's a significant darkness to it's history with things like the dockside massacre, or the gang activity, or the weird, cruel, and often uncaring politics of the city council.
Anyhoo, enough rambling. I disagree with the main trust of your point even as I agree with it completely.