There are many horror films where the protagonists are competent. Not all horror has to have three gormless teenagers wandering the woods for the first time ever.
The issue comes though that the very thing that D&D PCs are hyper-competent at is the one thing that helps you in a horror story: fighting monsters. A soldier might be competent at fighting other soldiers, but their bullets are useless against the supernatural horrors. A eccentric scholar might be versed in vampire hunting lore, but he's too old and feeble to face a vampire's physical might. A investigator might be able to solve crimes, but facing a killer who isn't human puts him on heels. Your typical D&D character is one magic sword away from killing anything in the MM, can max out lore abilities AND throw fire and lighting at the vampire, and is pretty good at recognizing that supernatural abilities like invisibility and flight exist because their wizard buddy does it every combat. The vampire isn't as scary when he's been turned by the cleric and is now fleeing like a scared child.
D&D doesn't do horror UNLESS you start neutering your PCs, which is how most of Ravenloft handled the issues. Turning was weakened, allies couldn't be trusted, divination was unreliable, teleportation all but unusable. It worked fine in 2e because most PCs didn't have many abilities to neuter in the first place (compare your average 2e class to its 5e counterpart as far as class features) and I recall the long lists of altered wizard, priest, psionics, and magic items. Ravenloft did horror by changing D&D, because D&D as written was poor for horror. Its a problem that only expanded in later editions and PCs got exponentially more powerful and competent at their chosen roles, so much so that trying to account for all a 5e PCs abilities and counter-them sufficiently to create horror feels almost like a fool's errand.
Perhaps instead of seeing HP and AC as a barrier to PC death, try looking at HP and AC as a way of tracking the physical trauma that the PC endures.
I mean, isn't that what they do in regular D&D? It doesn't stop the fact that baring outright burst damage, most 5th level PCs can survive encounters with typical supernatural monsters long enough to run or get lucky.
I agree that vulnerability is important but there are several methods of creating this...
- Isolation
- Removal of safe places
- Inability to effectively hurt or stop your foes
- An enemy that seems to know your tactics and weaknesses and effectively hits them.
- An enemy that hits you when you are not expecting it, or when you feel safe.
- An enemy that hits you when it’s least convenient.
- An enemy that defies And confounds you expectations.
- An inability to predict or use logic to anticipate outcomes.
- The doomsday clock that takes control of pace away from PCs.
All good advice, but not exactly unknown in regular D&D though. I mean, the Caves of Chaos near the Keep on the Borderlands features several of these elements as well!
Horror rpg adventuring is about the setup and the buy in from players willing to be scared. Sure the party wizard could have taken rope trick and undermined all this. However, the session zero made it clear for the players to bring along characters that were flawed and willing to be scared. That was the deal. They stuck to it, I stuck to it the campaign worked. It was without doubt my most satisfying and enjoyable campaign to DM. No characters died, that wasn’t what made it work.
Your getting closer to the point here: its hard to write horror for D&D because horror is personal and requires certain buy-in from the players. If one of your players had objected or if one of them said "you know what? I'm genre savvy enough to know using rope-trick until dawn is far safer than exploring a haunted woods at night" then the scenario falls flat. You need players WILLING to be vulnerable (hence why I said it was an RP issue) or rules that neuter the PCs to BE vulnerable (such as Ravenloft prohibiting extradimensional spaces)
Another leaf out the Walking Dead book was to play in a world without zombie films. Essentially for The Walking Dead to work the characters can’t have been brought up on Zombie Slasher films, so there is a conceit that this doesn’t exist. The player characters will be built on the conceit that they had never heard of vampires, werewolves or zombies. They just hadn’t come across those things in their careers.
Genredumb is the probably the laziest way to work around this. I used to say RPGs let you bypass the obvious blind-spot scripting of most traditional media; to borrow the classic example, you've watched slasher flicks and screamed at the doe-eyed co-ed "don't go in the basement!" an RPG lets you actually NOT go in the basement! RPGs have to account for the fact the audience may in-fact know the tropes and take reasonable precautions. The more you try to hamstring that with "don't say the z word" blindspots, the more your rob the characters of their agency. Sure, a little playing dumb is needed for all RPGs (I mean, who would rationally risk life and limb to go into dungeons in the first place?) but I think its unreasonable to assume in a world where elves, orcs and dragons exist that PCs don't know about vampires or werewolves!