It’s one of the great things about Deep Rock Galactic, in so many ways there’s very little story other than deep lore, and the Dwarves aren’t saving the world, they’re getting drunk and doing their dayjobs.
It’s not an RPG, but I think Owlboy handled it expertly.
Each level, Owlboy is out to handle some dangerous issue that is happening. By the end of the level, he succeeds.
The thing is, in the background, other things are happening. Almost every time you “succeed” the story moves forward to tell you, “oh, while you were doing that, THIS was happening that made all you just did basically pointless and we’re all even more screwed than before you started this level.”
So, it keenly points out the enemies aren’t waiting around, in fact, they’re doing dastardly things while you’re busy trying to save the day, so much so that your character continues to feel like a failure despite many successes. I think it’s a great way to present and write a story, to show that your character isn’t the only one in the wider world that things are happening to and can’t handle all problems at once. Things happen outside of their control and outside of their vision, just like in our real lives.
Witcher 3 did it really well by having the “end of the world” stuff being centered around Ciri, and Geralt is just kind of along for the ride and isn’t really entirely sure what is happening, and his impact on whether she succeeds is rooted in whether he supports her as a father figure. Most of the game is Geralt not really having any idea that this end of the world stuff is happening to Ciri at all, until near the end.
On the other hand, it would be nice to have an RPG with lower stakes. Like I’ve always imagined a modern-day RPG where you have to do things like scrounge through your couch for change to buy a soda. The mundane RPG.
Honestly, toughest part of Baldur’s Gate 3 is recognizing how much there is to do despite the fact that you literally have specific characters haranguing you to move the story forward (I’m looking at you Frog Wife, we’ll get to your fucking Creche when I’m ready!), which makes you feel like maybe there’s a time limit. First playthrough I missed massive amounts of the game because I felt rushed by the characters in my party.
On the other hand, maybe there does need to be a time limit so the urgency is real? In the original Fallout, on release, you had something like 100 in-game days before The Master found Vault 13 and it was game-over. They later removed this because it was seen as too difficult… but I actually dislike that it got removed. Maybe change how long the player is given, but still, give them something to press that urgency as an actual, real, urgent thing.
To your edit, similarly the final sequence of Nier Automata is so deeply rooted in not being alone, and encouraging one another, and being there for one another. A chance to break the cycle.