I'd definitely say that the Excelsior and the Akira classes are the primary workhorses of the Starfleet designs. I think the Miranda class WAS the workhorse, but given how often we saw them specifically getting blown apart during the war, I'd wager that the Akira class had been designed with the idea of taking over the work of the Miranda class, and that Starfleet was in the process of phasing out and retiring the Miranda class when the Dominion War broke out, and a great deal of the Mirandas we saw in combat were likely already near the end of their functional life or even hauled out of mothballs and surplus yards and pressed back in to service, just for the sake of having one more ship to throw at the Dominion.
Nebula classes are probably the scientific workhorses, with the scientific elements able to be swapped out and turned into tactical armaments - granted, I may just be unable to divorce my thinking from the Star Trek Armada, where they specifically got four special weapons, but particularly when you factor in the variations of the sensor pod over the saucer, I feel like the design was meant to be variable and modular, and so a lot of them had weapons systems swapped in for the various science equipment.
I would agree that, as effectively being Starfleet's in-between of the Excelsior and Galaxy class, the Ambassador class probably SHOULD have been represented more within the show, enough to be a workhorse itself, but if there were issues with the model to the point that it just wasn't in any condition to actually USE on DS9 outside of the one we see during the scene of Wolf 359, that means that, by the time they made the switchover to full CGI, they had other models to use there, and hadn't been using any within the rest of the series, so for it to suddenly explode into being seen constantly in combat scenes would seem weird - the Akira class was introduced in First Contact, so its lack of appearance prior to the war's outbreak is passed off as the ship just entering service. Suddenly seeing the Ambassador class make a lot of appearances after five years of not appearing would be odd. IRL limitations sometimes just have to overwhelm anything in universe.
I think the Ambassador class represents a technological dead end. I think the a way to think of it is in generational terms, in 60-80 year spans. The Ambassador class landed outside of that generational span.
We know that in 2161 Federation retired Earth's Warp 5 ships, and was soon launching Warp 7 ships. It is likely the Warp 6-6.5 ships of Andoria, Vulcan and Tellar stayed in service, but Warp 7 would be needed to both patrol and expand this new, young generation that had maybe 10 systems to its name.
The Constitution class appeared in the 2240s and did Warp 8. So its likely that from 2161 to around 2040 (about 80 years), Warp 7 was "average" and Warp 8 was in what the newest ships could do. The way I see it, for the Federation to grow in the 23rd century, it would likely need Warp 8 engines and associated technologies. And sure enough, the Constitution class did revolutionary travel and exploration, and it's likely that predecessor ships (Discovery era) were retrofitted to High Warp 7 or Warp 8 in the decades after the 2240s.
But those Connies, and their better, modular derivatives, the Mirandas, topped out at around Warp 8 for the next 40 years until the Excelsior class showed up in the mid 2280s. We know Transwarp failed, but it wasn't the only technology on that ship that was an advance over the Constitution era. Rather, the Excelsior is a analogy of the Galaxy class - a whole host of technologies. And I think it was the ship that made the 24th century as we know it possible by going Warp 9, and allowing the "large" Federation of that era to both form and function. The Constitution cruisers were rapidly retired. The Mirandas kept being built because of their unbeatable modularity, and upgrades allowed them to go Warp 8+ and do work horse duty. But without a doubt, the Excelsiors were the roadmap for the first 50 year sof 24th century Starfleet, just as the Connies were 60 years earlier, and the Warp 7 ships were 80 years before that.
Which brings us to the Ambassador class of the 2340s. The way I think of it, the Federation wanted to to build another leap forward, one of similar scope as the Excelsior, Connies and Warp 7s, and I think it found that the technologies weren't quite there yet. The tech hadn't reached their ambitions for the program. The Ambassador class was more matured Excelsior technology rather the breakthrough it was hoped for. So they only built a handful, and began the plan the Galaxy-class that would show up in 2257 with the USS Galaxy. But they did something very smart - they prototyped Galaxy class technologies in a host of other ships, from the USS Pegasus the New Orleans class, Freedom class, Cheyenne class, Challenger class and several others. These shared common designs (often times scaled down) and implementations of galaxy class technologies, but not the whole suite. It was a limited run of ships to act as a half-step to the Galaxy-class, to field test the technologies and mature them, so the Galaxy class did not fail to reach its promise like the Ambassador class did. This explains why we never saw very many of those ships - there weren't many (because there wasn't supposed to be), they existed just to prove Galaxy-class tech, and most of them got chewed up at Wolf 359.
If we say the Excelsiors started to enter service rapidly in the 2290s and 2300s, then that lands the galaxy class 60 years after the Excelsior's debut, which itself fully entered service in the front line about 60 years after the Connies. It lays out nicely. Trying to do it in 40, in the Ambassador, simply failed.
Side note, I think the Intrepids were the "pre-Dominion War" intended workhorse. The Galaxy and Nebula classes are massive, front line explorers. The Intrepids are smaller cruisers. They represented galaxy class technology matured and downscaled and paired with technology that didn't make it into the Galaxy class. And I think it's worth pointing out that before it got pulled to the Delta Quadrant, Janeway was "just another Starfleet Captain". She had a fine, if unexceptional career. She could have been thrown onto any old Excelsior or Miranda. Picard was considered one Starfleet's top Captains before he assumed command of the Enterprise, which is why he got the Flagship, and evidently people had to move heaven and earth to get posted there. But Voyager was your average 150 man Starfleet cruiser, just a new advanced design.
I'm thinking is that this is what happened: the Galaxy class was meant for beyond the edge of Federation space, while the Intrepid class was meant for many of the duties of the late-model Excelsior class (probably not shuttling people around though, definitely exploration). But I'm thinking the existence of the Dominion becoming known changed all that. Starfleet evidently froze production, because we never knew of more than three Intrepid class ships (Voyager, Intrepid, Bellpheron). But we know galaxy reactivated the "Federation Battle Fleet" plans from the Post-system J-25 / Pre-Wolf 359 era, in response to the Dominion. This saw the introduction of the Defiant-class, the Steamrunner-class, the Norway-class, the Akira-class, the Saber-class, Quantum Torpedoes, pulse phasers, ablative armor, an entirely new approach to hull plating, and more advanced shields... all things the Intrepid class didn't get. And Starfleet built a lot of these ships, and as we saw in the Battle of Sector 001, they could do exactly what they were designed to do - defeat the Borg (with a little help from Picard's insider information). And we saw even more in the Dominion War. But not Intrepids.Only Intrepid we saw was used as Admiral Ross' flagship at Romulus (the Bellpheron).
Given the "Battle Fleet" nature of 'Battle of Sector 001" ships, its unlikely that any of them, including the Akira, represent a true replacement workhorse. And by the late 2370s, the Intrepid class would represent an old design because it lacked those "Battle Fleet" technologies, so resuming production of that would be a non-starter. If anything, the modularity of the Excelsior and Mirandas likely saved them again and kept them in their Workhorse role, in contrast to the highly integrated Intrepid and Battle of Sector 001 ships.
There is a real world analogue for this, for what it's worth. The Arleigh Burke-class Destroyer was introduced in the 1980s. It's currently in it's 5th major variant (there's been Flight I, Flight II, Flight IIA, Flight II Technology Insertion, and now Flight III). They share the name and they share the look, but inside, Flight IIIs about about 20% in common with Flight Is, and the ship was 40% redesigned internally between Flight IIA and Flight III. Flight III will be built for the rest of the 2020s, and likely into the early 2030s, meaning when production finally ceases, it'll be one (heavily modified) class for 55 years, and those Flight IIIs will serve for another 30-40 years each. And there's a funny thing about the Arleigh Burkes....they're larger than most ships previously called Cruisers, and with the impending retirement this decade of the older Ticonderoga-class cruiser (which represents an older technology base than the Arleigh Burkes), there will be exactly one class of large surface ship for the US Navy. It will be joined in a few years by a smaller "frigate", about half its size, the "Constellation class".
In essence, the Arleigh Burke class is the Excelsior. It out competed larger ships, and short-run replacements, and is a design that has proven so versatile that despite looking externally very similar, the guts of it have changed several times, still making it the most capable destroyer in the world. In fact the only reason the US Navy is seriously starting to look at a clean-sheet successor is because - like for Starfleet moving on from the Excelsior - the US Navy needs a larger hull for a specific purpose. Namely, they destroyers to produce far more energy for directed energy weapons, a hybrid electric propulsion drive and sensors, and the Arleigh Burke doesn't have the hull space to support that. It's kind of like with the Galaxy-class... it kind of had to be that big to support an engine that went that fast at the time. In fact we can even say the Zumwalt class, of which the US navy built 3 and represents a "first pass" on many of their ambitions with last decade's tech, represents the Ambassador class. The Navy in 2000 knew what it wanted, but trying to do it with 2005 and 2010 tech proved uneconomical and disappointing, so they cut production at 3 (and they'll likely have unremarkable careers as technology test beds and retire early to save money). And now after 2030, they're going to try again with a new design with more mature technology that they hope to make dozens of ships with - a true Arleigh Burke successor. To me, I can look at that and see the Galaxy-class, 15-20 years after the limited Ambassador class didn't provide the leap-forward that was hoped for either.
