A lot of the major quests in Skyrim were just badly written. My opinion of that below, but are there any mods that offer alternatives to or rewrite major quests without just avoiding them completely?
Spoilers and rants ahead.
1. The main quest forces you to side with Delphine. She says that the fate of the world and legacy of the Blades lies with you being Dragonborn, but doesn't trust you enough to believe you outright or even with reliable testimony (Irileth, Balgruuf, Arngeir). She then risks everything by making you fight a dragon. You barely managed to kill Mirmulnir, and that was with the help of 6 others. Sahloknir was weak, but your character had no way of knowing that. If either you or Delphine died then, there goes the Blades and Skyrim, all because Delphine didn't trust you and didn't use her supposed connections to send a scouting party to Kynesgrove instead of just you two.
After that, she has you break into the Thalmor Embassy on a guess. Keep in mind that although you did learn about a possible Thalmor conspiracy behind the civil war, this was all by chance. You got some information from the quest, but basically committed a criminal act (and international incident, if you were also in the Imperial Legion) and probably killed several innocent guards because of Delphine's ignorant bias. Also keep in mind that neither she nor Malborn provided you with or were aware of an escape route. You're lucky that cave exit was there.
Lastly, despite helping her rebuild the Blades, even with the fate of the world in the balance, she still can't get past her prejudices. She orders you to kill Paarthurnax and refuses to help you until you do. On Alduin's Wall, there's a scene where the Blades pledge their loyalty to the Dragonborn, and yet here we have the last of the Blades forsaking that legacy. I'd even go so far as to say that they were never loyal to you, and were just using you all along.
There are mods out there that let you make Delphine and Esbern see reason, but on the other hand, they're basically hypocritical ungrateful jerks who probably don't deserve to be Blades anymore, much less reside at Sky Haven Temple, so you could also use mods to kill them. Realistically, there should've been other sources of information about Alduin. The Greybeards, The College of Winterhold, Farengar Secret-Fire, and even just discovering Sky Haven Temple on your own, maybe getting in a fight in the bloodseal room and accidentally opening it.
2. The Thieves Guild questline has several things wrong with it as well. First, you have Karliah, who spends 25 years holding onto the very journal that can be used to prove her innocence, and what does she do? Instead of trying to decode the journal or otherwise use it to prove her innocence, she spends 25 years working against the Thieves Guild in a long and complicated plan to lure Mercer to a specific spot to kill him with a specific poisoned arrow. 25 years is enough time to decode the journal without a stranger's help. Or to make a better plan than conspiring against the very guild you're trying to get back into. Or making a better plan to kill someone. Even making more than one poison arrow didn't cross her mind. The poison took her a year to perfect, soo... maybe spend another year on it? Already going for the long-term plan, so why not?
But why save the Thieves Guild at all? They kinda deserve their bad luck if they're too stupid to somehow not notice Mercer stealing from the treasury room, since the only entrance is a big, obvious door that's pretty much right in the middle of where the guildmembers spend all of their time. If I were in a group of thieves, I wouldn't depend entirely on one lock to keep people out. Or one person to control entry into the room. Guard shifts; look into it.
Also, why do I have to be a member of a specific thieves guild to join the Nightingales, especially when the only member of the guild who's in it is a traitor? I find it hard to believe that Nocturnal wouldn't get the word out through her other shrines to other guilds or thieves to stop Mercer.
3. In the College of Winterhold questline, instead of keeping the Eye of Magnus at Saarthal - which the College seems to control access to - and keeping it a secret from the obvious Thalmor spy Ancano, Tolfdir decides that the mysterious artifact the ancient Nords were guarding beyond death (and which may have been the true reason behind the Night of Tears) is perfectly safe to keep in a populated area. Also, the whole town probably saw it. How exactly did they manage to keep a giant floating orb a secret from everyone in Winterhold when they brought it to the College?
This is also partly your fault, since you didn't tell anyone about the warning of impending doom the Psijic order gave you, or what the Augur of Dunlain told you about the Staff of Magnus. Granted, both sources hint that Ancano's betrayal is inevitable, but that's very hard to believe when at any point you could've told Savos everything and probably avoided the whole disaster.
But you were for some reason rendered mute except for vague, unhelpful dialogue, so there wasn't a happy ending. After Ancano *surprisingly* betrays the College, the rest of the College staff - who are shocked at the notion that someone would misuse a powerful artifact that's just sitting in the main hall - are entirely unhelpful with resolving the issue. I can understand them not helping you when you went to Mzulft, since they thought it was a small errand (since you couldn't tell them otherwise) and maybe they wanted to stay behind to study and guard the Eye, (which they could've done anyway had they left it at Saarthal) but after Ancano's betrayal, they all just wander around asking, "What will we do now?" and so on. Staying behind to keep Winterhold safe from the Eye? Again, if they cared, they wouldn't have brought it there to begin with. They could've spared some mages. Even the apprentices would've been helpful. Or even Jarl Korir of Winterhold or Jarl Igrod of Morthal (the Hold in where Labyrinthian is) lending some guards, given the circumstances. The only part that makes sense is that they make you the Arch-Mage at the end. Not because you're a powerful mage, but because you did all the work.
If these complaints seem trivial to you, then I assume you're not using a single mod, because that's what they're for.