This list is in beta atm, I'll update it during the day. I plan to take inventory of all the mods which are worth it, both Multiplayer and Single player. I was working for a French website specialized in mods, now defunct (pmods.net), that's why I know kinda lot.
Map Packs
Community Bonus Packs 1&2 are two excellent map packs. Gameplay and/or graphics at their best. Not sure if it's included in ECE edition, though. But if it's not, you must get it.
Unreal Community Map Packs are 4 great map packs.
Single Player mods
Damnation: Hell Breaks Loose: It's steampunk time! A great single player campaign with 3rd/1st person view. Highly recommended!
The developers are currently doing a second project using this universe on the UT3 engine... edited by Codemasters. If somebody is interested...
Out of Hell : a survival-horror zombie themed. We can see the huge influence of silent hill on this one, what can I say... it's a demo and can be buggy, but it's damn worth it. Highly recommended!
: A puzzle adventure game using a lot of physics. You are a ball and you move through the levels via a hub, earning stars and unlocking others. Great atmosphere... the developers were planing to do a UT3 version but they lacked motivation, and the official website is down. Highly recommended as well. [url=http://www.moddb.com/mods/meta-ball/images]Some screens are here!
Hollow Moon: A visual and audio experience, states the website... it truly is. Horror at its utmost. Grab it here.
Ashura: Dark Reign: Currently in tech demo state, this mod plan on giving UT2004 the feel on a sonic game... just try it, it's damn good!
Multiplayer mods
Killing Floor : A scary experience. An awesome experience. Fight against hordes of zombies and weird monsters in co-op... truly outstanding ambiance, very polished and really far away from Left 4 Dead. Recommended, played a lot with friends.
XMP - Multiplayer conversion of UT2004 (and also Unreal 2), which makes MP game more BattleField 1942-like.
Tactical Ops: Crossfire : A terrorist / counter terrorist mod. Very professional and lots of gamers around... they are already developping a sequel for UT3 and released a stand alone game on the first Unreal Engine. Recommended.
Spawn of Deflebub : An damn original mod where you have to shoot the others at third person view, with different kind of bullets in a little arena where you can build walls. A 8bit feeling makes it quite retro, intentionally. I had a lot of fun playing with friends! Highly recommended! :)
Deathball : A... sport mod. Really fun, you have a ball in the middle of the map and you have to send it to the adverse goal. No weapons, just the ball grabber/launcher, and only the one carrying the ball can die. Very teamwork oriented,
highly recommended.
Alien Swarm : An overhead co-op mod where you have to kick some Alien asses. For fans of Alien Shooter. Also features single player campaign!
LinUT: winter is coming... Developed by some friends, this is a penguins themed mod: you have to grab ice and construct your igloo! But the other team is here to do the same, the fastest wins. Let's throw some snowballs!
BTW: some parts may be in French.
USkaarj: RTS? In my UT2004? Yep sir. Go test it, it's worth a look :)
The Soulkeeper : A fantasy driver mod, with several classes, magic spells and swords, leveling and everything. I had a really fun time playing it with buddies as well. Their website isn't available yet (they're creating an independent game), that's why I'm linking the ModDB page.
Air Buccaneers: hot air ballons, pirates... very original, atmospheric, strategic and quite fun.
Troopers: Dawn of Destiny: Star Wars epic battles, on air and ground!
UnWheel : As its name suggests, it's a car racing mod. Very polished, it looks really professional, by the way.
Chaos UT2: Evolution: A shitload of weapons and items added, insanity guaranteed.
Unreal 4 Ever 2004: The sequel of the wonderful Unreal4Ever for UT99. Shitload of weapons as well.
Gunreal : A huge weapons modifications. Fucking insane.
Mutators
Node Siege: a gametype which creates a mix of Assault/Domination on Deathmatch maps. As the node is placed randomly, the games are sometimes unbalanced.
Ralackk told about UT2004RPG, I highly recommend it as well. Some servers still host it with permanent stats and playing it with friends on big ass maps is a damn pleasure (Grendelkeep :D!)

Here's my addition... my favorite gametype for Ut2k4:
Node Siege

Not mods but worth mentioning, the two CBP's, which are awesome !

To whomever gets the CBP2, and Node Siege:
Play a Node Siege game in the DM-Summit map. TONS of fun. ;-)

Great mods list!
For anyone who may be interested, I created a few maps for vanilla UT2004, and I also created a map for the Alien Swarm mod called AO-BugCity. You can find them all on my website.
Also keep your eyes peeled for the Out of Hell mod. It's been in the works for years, but I think the author may be getting close to a release. *crosses fingers*

Yep, I was going to put it in the Single player mods, later in the night along with Damnation and some other interesting mods yet not enough known... I'll do it later that night, I got tons of other things to do atm :(

There, added a bunch of Single and multi player mods.
Don't forget to add some, my memory has some flaws since I don't work in the mod community anymore :<

O my! This might be the big chance!
I present to you, the greatest dead mod ever. Also, it's the best Star Wars mod ever.
Troopers: Dawn of Destiny
I still don't know how it didn't take off. :/

Just a little heads up for people wanting ChaosUT2. Last time I played there was only 1 server up. Ever. And it was melee weapons only. by now that server is probably down too.

"Ballistic Weapons" is a great "mutator".
It features 30+ weapons and excessive amounts of blood and gore.
Www.Runestorm.Com .

If you are still adding items to the list, UT2004 RPG adds a stupid amount of replay to any mode. Its a mutator and what it does is gives you experiance for kills that you can then spend on new skills or to make yourself do more damage, move faster etc.
http://unrealtournament2004.filefront.com/file/UT2004_RPG_v12;27988#Download

Note that Chaos for UT2004 (as well as for UT) has a King of the Hill (team or solo) mode, and well implemented for fans of KotH.

Let me add the excellent Unreal Tournament 2 Conversion. It borrows heavily from UT99, with a little Quake 3 Arena thrown in for good measure. Basically, it's what UT99 might have been, if it had been made 5 years later (which is not the same as UT2004).

Been playing single player of Air Buccaneers - excellent mod. Any interest in a server? I can host.

XMP - Multiplayer conversion of UT2004 (and also Unreal 2), which makes MP game more BattleField 1942-like.