There will probably be some "drama", in the form of modders and people arguing where mods should be available, but I'm pretty sure that will pass and CurseForge and other sims CC sites can coexist.
It definitely has the potential to become a great way for people to find cc and mods and for mod and cc creators to keep them updated, it depends on the quality control.
It does remind me of Steam Workshop which works the same way, subscribing to mods is really easy there too, but you still need to be aware of what you are downloading and how it affects your game.
