I rarely ever mod my games outside of fan patches to fix bugs but I'm looking for games that have some quality mods.

Project Reality for Battlefield 2 comes to mind. That's a quality mod that's now it's own standalone game.