My only concern is paid Bethesda Creation Club mods will be the only way to install mods without using DRM workarounds

This is your concern based on what exactly?

This is the second attempt by Bethesda to control and grift profit from the modding community of their games. Remember the disastrous roll out of paid steam workshop mods ? Over 40 free nexus mods were removed to be added as paid steam mods.

Creation club is going to splinter the modding community of the next Bethesda game regardless of whether it's exclusive to the platform.

I'm just hoping Bethesda isn't heavy handed about what content works with their next game.

It's not far-fetched to imagine Bethesda creating a shallow multiplayer component in order to ban players with "unauthorized content" not available from the Creation club for their next mostly single player Elder Scrolls game.