Yes and mostly no. Mods are nice, are they necessary to enjoy a detailed, thought out story? No. Not at all.

Most mods can help quality of life but let's be real. The greatest mods ever made, still lack the production value, quality voice acting, and stability needed for a decent to wonderful experience. I love mods and use them in all my PC games(when available) but I've never played a mod better than one of the many first party Sony games.

It makes sense if you want to have fun, but it doesn't make sense if you want to experience a narrative the was well crafted over 5+ years and a team of a 100+ people. Of course the AAA studio can still fail to deliver but honestly, even after a decade+ of mods I've yet to play sonething from a modder that's the calibur of a AAA studio.

There are mods that are better games and I've played mods that I would pay 60 bucks for, but majority of the time, in 20 years, the payed employees and big budget games still hit hard.

Counter strike is the best mod almost ever made, but it's still not better than Half life and I wouldn't give up the experience I felt with half life to play counter strike.

I know CS isn't a mod that relates to single player experiences but it's my best example of yes, mods are necessary, but today, no one has a made a mod that I can agree is better than the original. The biggest Skyrim mods in the world are fun, and great to play, but still lack perfection and attention to detail (for obvious reasons, not for lack of talent but because they will never have the team and budget to pull off what they want). That being said, I'm still waiting on Skywind