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.
Gameplay Mods
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. (Daggers are fixed in the 1.02 retail patch, but archery is still borked)
Nightmare Plus - Fixes enemy AI, tweaks spells, adds a lyrium addiction system, delivered in a module system so you can enable/disable changes as you wish. Recommended for second/multiple playthroughs for a bit of an extra challenge.
Open Lock Spells - So you don't have to drag a rogue along in your party.
Convenience/UI Mods
Respec Mod (Potions) - Adds a raven in your camp gives you potions that refund all your attribute/talent/skill/specialization points.
Camp Storage Chest - Puts a chest in your camp site...
Detailed Tooltips - Turns stuff like "Instantly restores moderate health" to "Instantly restores (50+SP)*3 health."
Annoyance Remover - Changes the visual effects for the persistent auras to one-/two-second things.
Items Received - Tells you what item you just got
Tactics System Mods
25 Tactics Slots - Self explanatory
Advanced Tactics - Adds extra conditions for to the Tactics menu that were disabled by the devs for unknown reasons.
Graphics Mods
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.
Item Mods
DLC Items - Adds doppelgangers for the various sundry items you get for preordering at <insert_game_store_name>
Enchant Any Weapon - Again, pretty self-explanatory; lets you enchant bows/staves. This can throw off item balance, so use it wisely....or not.
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.
