The greatest mods for FO3: Mothership Zeta Crew: Not really lore friendly, but better than all FO3 DLCs! You get a crew for the alien space ship from the Mothership Zeta DLC and you get your own faction. Of course you will need the mothership zeta dlc for this mod... Fallout Wanderes Edition: A whole game overhaul. It changes the combat, adds tons of new items and weapons, it changes the damage penalty, you can activate a Fallout New Vegas -like Hardcore mode, adds many new RPG elements and much more! (Many configurable settings!) It adds no new story so it's also lore friendly.
the official trailer. Marts Mutants Mod: It adds many new enemies, increases enemy spawns and changes the respawntime of enemies. (All configurable!)