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.