I liked Planescape: Torment.
Even more than two decades on, I would still rate Planescape: Torment as the best video game story in general. Even though I think the ending is relatively weak compared to the rest of the game, it still more than deserves to be up there. It's one of the few times I think I needed to come back and play a game as an adult because its themes are mature (as in actually mature) enough that there's a lot you miss playing it as a child.
It is a very downer ending, although I actually think they pulled the punch a bit by showing what happens to the Nameless One after his business is resolved. It seems like they wanted to keep the character in play in some form so he could crop up in future D&D materials or stories, but in the context of this self-contained story I think it's a weakness to what should be a very moving resolution.
But I think, as a downer ending, it's totally earned, and the tone is baked into the whole feel of the game. I can't help but feel like a lot of people must have pushed back against the decision to base a game around a character who is a gross zombie man, but it really works in context. You look at the nameless one and immediately know this is not a guy who is having a great time.
Speaking of edgy 90s RPGs though.
Fallout: A Post-Nuclear Role Playing Game
Unlike Planescape: Torment, I don't actually think the original Fallout is as good as people remember in terms of writing and storytelling. It's good, and certainly better than a lot of modern games, but a lot of it hasn't aged well. However, I think a big part of the reason people remember Fallout as this great story driven game is that the ending really, really hits a home run. Everything from the walk down the corridor of revulsion to the end credits is brilliant.
The master, for example, absolutely shouldn't work. He's barely introduced before you meet him and you can meet him only once, his design is extremely 90s, his gimmick of having multiple voices threatens to veer into goofy or annoying, he talks about the "master race" in a very unsubtle way. But it's all completely nailed in the execution. He's barely present in the story, but he represents and serves as exposition for its most important theme of hope versus despair. His voice acting is standout even in a game with unusually good voice acting for its time. In a franchise full of interesting characters and in particular memorable, complex villains, he's still my favorite character despite having about 7 minutes of dialogue.
I also like how he's set up as a mirror to the other antagonist you have a conversation with at the end of the game, the Overseer. The overseer's betrayal is such a great twist ending and a nice piece of implied character development for the vault dweller despite the fact the vault dweller is basically a player avatar. It's also a classic reference to the coming of age themes of old fantasy. Sometimes the journey changes you so much that you can never really go home again. I think that's such a powerful and personal theme to end this story on after all the big picture stuff about the fate of humanity, and to me it's perfect. Fallout could very easily have gone down as a pretty generic, silly 90s RPG where the main appeal was combat and exploding people in gory ways, but the ending reaches for an emotional depth that makes it way more memorable.