I'll try to keep this post up to date with nice mods I see. If you want me to include something, or think I should change the ordering, or whatever, let me know.
Basic Mods that Everyone Should Install
Dexterity Hotfix - Fixes the way bows and daggers calculate attack bonus and damage. Must have if you've a rogue in your party. Written by the Dragon Age gameplay engineer at Bioware.
Camp Storage Chest - Puts a chest in your camp site...
Respec Mod (Potions) - Adds a raven in your camp gives you potions that refund all your attribute/talent/skill/specialization points.
HD Textures - If you're barely running the game, you might have some issues with larger textures, but most people should at least give it a shot.
Tactics Mods
25 Tactics Slots - Self explanatory
Extra Tactics - Adds extra conditions for to the Tactics menu that were disabled by the devs for unknown reasons.
More Esoteric Mods
Open Lock Spells - So you don't have to drag a rogue along in your party.
Items Received - Tells you what item you just got
Enchant Any Weapon - Again, pretty self-explanatory; lets you enchant bows/staves
Winter Forge - Lets you craft any kind of item you want. Naturally you can bork the balance with this mod, so I'd advise against using it the first time through.