While it's phenomenal mods going to X1 and likely PS4 soon after is a major victory for BGS and console gamers, fans should have realistic expectations. Many of the most well-known and popular mods on PC will never work on consoles for varying reasons.
1. Microsoft or BGS will regulate what mods are available on X1.
2. HD texture packs won't happen because consoles aren't an open platform with the capacity to be upgraded.
3. ENB, ReShade, SweetFX, NMM, SKSE, or other 3rd party programs will not work on consoles and would never be allowed.
4. Massive player-created expansions are unlikely to be permitted due to sheer size.
5. RAM unlocks or any other sort of performance boosting tools would not be allowed.
This isn't even mentioning the amout of work that would have to go into bug-testing player-created content and making sure mods work as they are intended. It's also worth mentioning that mods can and will crash your game, especially if you use a lot of mods. Microsoft or BGS will also likely put restrictions on how much you can actually modify the experience.
In reality, the best console gamers will be able to hope for are relatively minor mods that are purely cosmetic or fluff. This could be as basic as adding new armor or weapons to the game. Perhaps custom companions will be allowed. The simple fact is the more complex and ambitious the mod, the more likely it is to be censored or not considered.
Moral of the story? Mods coming to consoles are nice, but I wouldn't expect it to revolutionize the experience on consoles. The best mods will always only be available on PC because it is an open platform with no one regulating it. Be happy, but be cautious about what your expectations are for modding on consoles.