As a shameless Dragon Age: Origins addict, when I finally got the PC version I couldn't wait to start modding the crap out of it. Which lead to much madness, I'll admit. Here's a list of my favourites. Some clash, but unfortunately I forget what clashes with what.
Add Origins Companions to Awakening - This mod will put your Origins companions (With the obvious exceptions of Morrigan, Wynne, Sten and Oghren) in Vigil's Keep. You can't add them to your party, but you can catch up and continue romances if you so wish.
Alistair Dialogue Patch - This alters various bits of dialogue. Most are romance-related, for example "Something you need, my dear?" has been replaced with "My love?".
DahliaLynn's Alistair Epilogue - Gives the player a wedding scene at the end of the game if the player chose to marry Alistair and rule beside him. Does NOT play if the player chose to be a Chancellor.
Dalish Mage Origin - This allows you to choose the Dalish origin if you picked the Mage class. It will require Non-Circle Mage Dialogue to prevent characters contradicting your origins later in the game.
Armour of the Devout - A beautiful armour set for Leliana, based on her Chantry robes.
Armour of the Elven Warden - Similar to the Ancient Elven Armour set, but with less garish colours and the Grey Warden insignia on the breast plate.
Extended Dog Talents - Additional abilities for your faithful Mabari.
Grey Warden Runic Armour - Weapons and armour for your Grey Wardens.
Improved Romance Scenes - Alistair - Adds some cute animations of Alistair and the Warden hugging, cuddling and so on.
Improved Romance Scenes - Zevran - The equivalent of the above for Zevran, except much more adult given how worldy Zevran is in comparison to Alistair.
Karma's Origins Companions - Allows you to recruit and romance characters from your Origin story eg Gorim, Tamlen, Jowan etc. Note that this mod does require a lot of additional mods, and as a result clashes with additional merchants etc in your camp.
Lock Bash - Lets you force open locks on doors/chests regardless of your class or lockpicking skill. The mod won't give access to doors and chests that require keys to be opened.
More Options At The Pearl - A very amusing (and adult!) mod that adds a character to The Pearl whorehouse in Denerim. Ever wondered how it would go if Sten and Morrigan hooked up? Want to see a member of your party pole dance for The Pearl's patrons? There are a number of hilarious scenes you can witness through this mod.
Morrigan Restoration Patch - Restores and fixes a number of scenes within the game, including fully-voiced scenes and party banter that didn't make the final cut of the game.
Dark Ritual Deleted Scene - A scene based on the Revelation comic, it triggers after the conversation with Riordan if Alistair is in love with the Warden and Morrigan has been made friendly.
Alistair Adore Items - Adds a ring (restricted to the Warden) and a locket (restricted to Alistair) when the relationship reaches Adore.
Mages Pack - Adds four different mage robe. Three resemble noble clothing, and one resembles Enchanter's robes.
Mages of Ferelden - Alters the appearance of the standard male mage robe to the style of First Enchanter Irving.
Improved Atmosphere - Makes everything a bit more lively. Party members won't just stand around camp, but will instead pace slowly, sit down etc. NPCs in towns will wander around and actually look alive.
Shangrila - A beautiful version of the female Noble dress.
Skip Ostagar - Lets you skip Ostagar, the Korcari Wilds and the Tower of Ishal. Recommended if you've played often enough that the Ostagar portion of the game feels a bit tedious, what with it being pretty much identical no matter your Origin and character personality.
Skip The Fade - Reduces the Sloth section of the Broken Circle quest to your initial dream with Duncan, your companions' dreams and the confrontation with Sloth.
Song of the Rose - A beautiful set containing weapons, armour and accessories all restricted to Leliana.
Manolax's Staff Collection for Mages - A set of four different staves. If you dislike the style the mod is at least worth adding for you to sell them early on, as each staff is worth quite a bit of gold, allowing you to start the game wealthy.
Camp Storage Chest - Adds a chest to your camp. Use it to store items you want to keep but lack inventory room for, as well as high level weapons and armour you can't use yet.
The Phoenix Armour - A set of weapons and armours for Rogues, but fitting for Warriors as well! Female only.
Universal Dye Kit - Allows you to customise the colour of your armour and weapons. Also includes a portable enchanter!
Vishala's Penance - Adds the merchant Vishala to camp.
Wings of Velvet - Beautiful Mage robes.
Zevran ASAP - Initiates the first meeting with Zevran immediately after Lothering, allowing you to recruit him sooner.