Well, the simple answer is that any mods that require skse or other third party software can *not* be brought to console. This rules out a lot of mods right off the bat. Retextures and mesh replacers are examples of mods that can probably be brought with minimal effort. I think the best way to get an idea of what type of mods will be available for Skyrim on consoles, is to check the Fallout 4 console modding scene. New quests can work depending on how they are designed. New armors and weapons too.
Oh wow, I hadn't thought about skse. That sure does rule out a lot.