I still often think of Descent: FreeSpace, the 1997 space dogfighting game from Volition. It had such a masterful way with pulling the player into the middle of the story and keeping the player focused on the moment-to-moment emotional beats.
Unlike a lot of 3D space games of the era, I think it's still the easiest to approach and get drawn into because the scripted missions focus you into encounters and moves the plot along without giving the player "choices." You're a fighter pilot, you're told what's happening on a need-to-know basis. Whatever interstellar politics are happening aren't your purview.
(Also it just oozed that 90's industrial sci-fi aesthetic in art and music)
Just absolutely love this series and sad it got trapped in IP rights limbo.