Mods are a fun way to extend the lifespan of the game almost infinitely, but Bethesda is on the way to kill the experience with the Creation Club and other shenanigans.
I'm aware that people think that, but for many people like myself, there is no possibility of mods making a game infinitely entertaining. At some point games in general got boring for me. I have to take breaks and find unique games to keep my interest as it stands.
That's exactly it, it depends on the person more than the game. No game would be expected to be infinitely replayable, but there absolutely are people that will infinitely play a single game.
We are all built differently, my dad still plays to this day the settlers 4. Not even the history edition, but the one I installed (from CD!) for him a decade or more ago. Don't know how he does it.
There are some games that can approach that level of entertainment. Something like Minecraft may entertain some for tens of thousands of hours because of its sandbox nature, and Crusader Kings games can also eat years of one's life because of how different each game/story can be.
But a game with a pre-written story and an immutable world has a fairly short shelf-life for most people.