Neonchameleon
Legend
But the problem with providing the possibility of failure is that the timing of failure in a game serving the aesthetic doesn't always - and usually doesn't - well serve the timing required of narrative. One problem that you run into trying to recreate narrative in a game is that in narratives the protagonists can't fail unless it serves the story for them to do so. But in the game, characters just die off at random leaving plot threads dangling unfinished.
That depends on the story. In Fantasy Vietnam you absolutely can kill off characters to enhance the mood while leaving dangling plot threads unfinished. Look, for example, at Game of Thrones - or possibly the most tightly plotted TV of the 90s, Babylon 5. They wrote the intended protagonist out there.
But fundamentally you are making a spectacular mistake regarding narrative when you consider the only possible failure of the protagonists to be death of the protagonist. Unfortunately the D&D character sheet only really has options for death and equipment loss. I've cared more about whether my character became Prom Queen in Monsterhearts than whether my character died in D&D, and because it wasn't a matter of life or death the narrative for that character worked whether or not she succeeded at becoming Prom Queen.
Because one of the most certain facts regarding such well told narrative is that the author knows what is going to happen and writes in such a way that they make that vision true. If you look at authors with great narrative structure - Victor Hugo, JK Rawlings, Vernor Vinge, Lois Bujold - they often begin with the ending they've envisioned and plot their story backward from that point. But why should this process be an exciting experience for a collaborative group? It may be satisfying to write a good story, but it's not the same sort of experience and pleasure that comes from playing a good game.
One of the most certain facts regarding storytelling is there is more than one way to do it. Bujold also often begins "What's the worst thing I can do to this character and have them survive" and has it go forward while Rowling herself has admitted that actually sticking to that final chapter she'd pre-written was a mistake.
TV Tropes talks about the sliding scale of character vs plot - and the question of character driven vs plot driven stories is very much something sorted out on an author by author basis (or even in Bujold's a book by book basis; Memory is clearly character driven while A Civil Campaign clearly started with the dinner party and worked outwards from there). In the paragraph I quoted you seem to exalt plot driven stories over character driven ones while most such shared narrative games are character driven.
There are very few RPGs I'd regard as mechanically leading to plot driven stories; even Montsegur 1244 uses its narrative as a framework. The only ones that actively come to mind are My Life With Master (master mistreats minions -> minions get pissed off enough that one rebels -> fight to the death), Fiasco (the five act structure), and Grey Ranks (although even that's veering into Montsegur 1244 territory), and from memory Robin Laws' The Dying Earth in the worst ways .
But the whole point of simulating a story is that the players all want to be those heroic figures even if they aren't, and it's not clear to me how you can deliver on this while still giving them free will in the story.
What are hit points but plot armour?