I'm not sure where this whole mod debate started as I just came back after a couple of month of time away but seems to me that mods will always fall into these categories if left unchecked or allowed by the game devs. Whether or not you agree these are great or terrible is personal preference.
1. QoL changes - Things that make the game better or that players wanted but because devs took too long or show no signs of "caring" they perform the task themselves. These can be small like UI fixes to bug fixes. DSfix is a good example of this.
2. Vanity Mods - People re-skin, move-set swap, change animations to make the game better or worse. While not game breaking at the start it can evolve into it. Skyrim with Machoman Randy Savage is a good example of this.
3. Rule 34 - nuff said
4. Stand-alone Mod - Modders use in-game assets to create their own game, mode, or story, separate (or parallel) to the current lore that the devs intended. The og Counter Strike is a good example of this.
5. In-game Mod - Basically hacking. It can be anything from altering code to make your avatar look different from what the devs intended, to out-right hacking other people's information.
I feel kind of bad for game developers today since they created an abusive "drug-culture" out of gaming. They constantly focus on getting players "hooked" offering superficial products to keep that "high" going while slowly "cutting" the product but charging more. It's the literally what is happening to most games today, FFXIV included. First one is always free then you have to pay to keep going. They are a business and they have no obligation to creative freedom outside of its monetary worth.
Going back to CS where Valve hired the guys who made the mod to join them and develop the game. I only wish that the same would happen to people in FFXIV who do such mods. If they implement a UI fix that is inherently better than the current one and in less time then give that person a job because they clearly have some talent for it. While both the devs and modders are fueled by passion the former is restricted by deadlines, budgets and superiors while the latter is not that's why most amateur mods made by people who love the game will always be on par or better than the devs.