Discounting mods I made, I'd have to say that P&N is probably still my favorite mod. It has a lot of little fixes and tweaks to stock items to improve balance, has QNP, pirates and nomads (of course!), was one of the first mods to implement crossover racial techs (Devnull was the other one way back when, Dark Nova is working on adding some), a bunch of minor, but cool, racial traits, balanced resource costs, and so on. It's a very enhanced stock, and was my inspiration to get into SE4 modding.
Note that SJ made an AI Patcher tool that can convert stock compatible AIs (including TDM!) to work in P&N, which can really give you a run for your money if you play as a pirate or nomad (and still challenging otherwise).
Devnull is another stock-enhancer, but strays much less farther from the tree than P&N does. It is primarily a "fixer" mod, but with some added features (space monsters!, some more missiles, other stuff I forget). Similar to TDM, it also has some killer AIs. Not all of the AIs are so highly tweaked though, if I recall correctly.
STM, though hindered by the restrictive Star Trek universe, is still a well thought out, fun mod. Each race has some special facilities and components to branch out from the core ST-flavored tech tree.
Proportions places great importance upon the homeworld, making it much more powerful than in stock. Colonies are small, weak and slow. They take a huge investment to get going, and never approach the HW (unless you are obsessive and play a few thousand turns
). There are lots of other features I forget these days. Definitely makes for a very different experience. AIC is a derivative, though with numerous added features of it's own, whose main goal is to provide killer AI in a similar universe.
I'll have to let someone else cover Carrier Battles, as I haven't played it much. One of the few things I do know is that Rollo recently made some reportedly stellar AI for it.
Well, I might as well say something about the other two:
FQM greatly expands map options and features. It is generally compatible with most mods, and can be combined with them if you so choose.
For Adamant, I'll just direct you to the
features page. It has some working AI, but nothing to write home about. The focus was definitely on multiplayer gaming.
I'm sure I've missed some important things about these mods, so feel free to add more!