TL;DR for the novellette below: Overall, I view Discovery as a flawed success more than a noble failure.
The show's casting, visual effects, and production design was always tops. I absolutely adore Saru and Culber. Discovery is a damn cool looking ship. The script's the thing that frequently caught the chagrin of the king, however. The staff turmoil over the first three years of the show sounds to be something that could rival Chaos on the Bridge for the insanity of it, and unfortunately Disco wore that on its sleeve.
Season 1 I feel was the most successful. It got Trek back on screens in a big way. Sure, the Klingon redesign was weird, the Spore Drive kinda bizarre, but it feels a strong and confident first installment. Breaking up the season into three distinct story arcs (Klingon War/Mirror Universe/Klingon War II) actually worked to the benefit of the storytelling. Unfortunately, the ultra-serialized plot meant that we only really got to know Burnham, Saru, Lorca, and Mirror Georgiou... a problem that would dog the show throughout.
Season 2 started incredibly promising! Pike was great. The show's tone bloomed away from the ultra-serious drama from season 1. The setup of the science vs. faith storyline was intriguing. But then... Berg and Harberts fuck up, get shitcanned, the show goes on a lengthy production pause, and Kurtzman rewrites a significant part of the story of the season - even including the in-person addition of Spock. The season at it stands feels two halves that don't make a whole.
The Red Angel thing becomes uninteresting. We get the whole Control storyline with universe-ending ramifications - a plot device I'm super tired of seeing in television these days. Airiam's death remains an utter low point for the show. The worst case of writers forcing us to give a shit about a background bridge bunny in time to kill her off and then making the other characters act overwrought to bash the audience over the head with "you're supposed to be sad!" I'm not entirely convinced that Discovery going into the future was "always the intent" in all these retrospective interviews, as disjointed as the whole thing became.
Season 3 is a mixed bag. I enjoyed seeing a new century, freed from the bounds of canon. Everyone in the galaxy now are a group of assholes because the future got grimdark? I was on board with that concept. The Emerald Chain concept was good. The new Orion makeup looked horrendous on Janet Kidder as Osyraa. Loved the introduction of Adira and Gray to the series. Representation was always Discovery's strongest suit.
The Burn, however... while I found the idea of an impactful cataclysm upending life in the galaxy fascinating, the root cause had me smacking my head into a wall. All dilithium blew up... because of a child's emotions? In retrospect, that's probably the most Michelle Paradise idea, considering how each successive season of the show increasingly felt like it was being written by a therapist. It was the start of Discovery being overly fixated on trauma as a tool for character arcs.
Season 4 is an even more mixed bag, and the lowest point of the series. The idea of Species 10-C is intriguing. The visualizing of it and the totally alien concept of their language was the hardest sci-fi ideas the show has ever worked with. However, the show lags HARD during the middle. I never gave a shit about Tarka, and his backstory came too little, too late. Book's storyline again ties into Paradise's obsession with post-traumatic growth. This also was the start of the background bridge bunnies giving us details about themselves in clunky bits of expositional dialogue because the writers couldn't think of a natural way to incorporate them.
Season 5 righted the ship quite a bit. I think the reduction to a 10-episode order forced the writing staff to get on with it and not drag shit out longer than necessary. However, we still have many instances where the plot stops cold for the characters to talk about their feelings. At this point, Disco's gonna Disco. I really liked L'ak this season and wished he could've stayed around. Moll... I didn't. She played the same note in every scene she was in and I barely felt any sort of nuance. As antagonists go, she was one of the least memorable the franchise has ever come up with.
When we got to the finale, I quite enjoyed how it all came together. How the Progenitor's tech was dealt with worked for me. I loved the idea that it wasn't even the Progenitor's to begin with; they only found it. We got Burnham and Book walking off into the sunset - a perfect ending I thought.
Oh, shit wait - you mean there's more?! That epilogue... I still don't care for it. It made a satisfying series finale worse for two reasons: 1) Burnham's flashback on the Bridge is such a patting-ourselves-on-the-back gag that falls a bit flat given how little we really know about most of the characters. 2) There is no goddamn reason the show needed to tie back into Calypso. They managed to do connect-the-dots worse than Picard season 3.
I really didn't mean to go on this much about Discovery, but the fact I care enough to write this much means that the show means something to me. We don't get any of the other Paramount+ series - Picard, Lower Decks, SNW, Prodigy, SFA - without Discovery. For that, I shall be eternally grateful the show exists, however flawed it is.