The one I had in mind as the exception to the rule when I posted that was maybe Doom Eternal. I really, really liked that game. But the more I think about it.... Doom isn't really there for the story. John Romero, though no longer at id, is famously quoted as saying that video game stories are akin to the plot of a porno - it's expected to be there, but it isn't expected to be good and it isn't expected to get in the way of the game itself. New Doom takes itself seriously enough to have lore - pretty good lore, in fact, in my opinion! But I wouldn't call it... inspired. It is, truly, just the necessary set dressing required in order to orient oneself to the ripping and the tearing. They put a little more effort into it than old doom did but the overall result pretty much plays out exactly like you expect it to. There's no big twists or turns. There are bad guys, Doomguy arrives to delete the bad guys, and gets a lead on the next bad guys. Rinse repeat until out of funding. And it's fine, it works. Doom is a game more about the moment to moment gameplay anyway.
So, not Doom. What then? Dusk maybe? Dusk was a fucking good game. Doesn't hold a candle to Effect & Cause or The Fold Weapon though, in my opinion. Dusk didn't have characters I could get invested in.
Bioshock is a good contender, but Titanfall is a better game overall imo and therefore takes the W.
The last big ones that come to mind to me personally are Half-Life, like you've said, which is an extremely strong contender for this title, and then Fallout New Vegas (yes I am one of those people but believe me when I say it's a really fucking good game and you should try it)
But even given those challengers TF|2 can sit very comfortably within the top 5 ranking of 'objectively' best single player FPS campaigns. It is an absolute banger of a game and I'm eternally disappointed that the series basically got absorbed into Apex Legends. I'd sell my left nut for a Titanfall 3. In the meantime I'm making do with Armored Core.