Welcome to the forums and the game.
Right now the best mods are the TDM and the FQM.
But if you look at the forum load page, you will see a sicky at the top of this forum, (Space Empires IV, about Mods. There is also a page at Malfador about the mods.
The one that I use most often that is not a nitch mod, ie like the
http://astmod.com is the Fyron Quadrant Mod Delux, and Proportions.
The TDM mod has great AI, and should be considered for any serious single player games. But the AI is transferable to any mod. You just have to do it.
The best thing about SEIV is the fact that YOU can easily mod just about any aspect of the game that you want.