If there's one thing that everyone can agree on when it comes to game mechanics, it's that if loot is present, then it has to be enticing enough for players to actually want to loot it. Of course, what this means in practice depends on a game-to-game basis, and the results aren't always clear cut. Obsidian's approach to loot in their latest RPG, Avowed, has demonstrated that even developers as experienced as they are can make mistakes.
Fortunately, the underlying basics are more or less the same across every RPG. The traditional approach to loot and gear progression is linear upgrades. In other words, one tier of gear will always be better than another tier. Eventually, you are funneled into a handful of equipment options which are objectively the best in the game.
Historically, most RPGs have linear gear progression because it's the simplest way to design gear and convey power levels to players. Skyrim demonstrates this methodology very well as it's immediately obvious that the Iron armor and weapons from the start of the game are nowhere near as powerful as Ebony or Daedric tier equipment.
The downside of linear loot systems is that they are predictable. You're generally going to need an auxiliary mechanism to present the illusion of choice and variety. Skyrim does this via enchanting, alchemy, smithing and crafting, and semi-random loot drops. Skyrim is also a relatively large game, so while one can assume Obsidian might've had plans for similar mechanics in Avowed, it's not out of the question that this would've led to feature creep and ultimately omitted.

The more modern approach can best be described as horizontal upgrades. This method generally has the same tier system as linear upgrades, but instead of culminating in a couple of sets of super powerful armor, it usually expands into a more diverse array of armor sets that augment certain play styles via unique effects.
Some games take this one step further by letting you change the appearance of your armor, adding yet another potential avenue for player retention. Monster Hunter Wilds is an example of how modern games should approach gear progression, featuring both a transmog system and multiple armor sets that have their own bonuses, giving you the chance to dress for fashion and function alike without compromise.
Horizontal gear mechanics are not without their own downsides. As far as players are concerned, this typically means more complexity, which is a non-issue if the game is built around having endgame content. For a linear story-driven experience like Avowed, this might be beyond what the developers intend for the game, especially if the target audience is leaning towards the casual end of the spectrum.
Avowed's Unique Approach to Leveling and Player Progression
To their credit, Obsidian clearly recognized what people tend to like about loot and gear progression. All the individual parts are there in Avowed, at least on paper. There's a transmog mechanic to change the appearance of your armor, you can find a decent number of unique items with unique effects, and there's decent weapon variety.
The issue is that it felt like Obsidian was trying to reinvent the wheel while awkwardly avoiding referencing what everyone likes about the wheel.

Take Avowed's transmog system for instance. In theory, it should be a simple process of selecting a piece of armor and then pressing a button to change its appearance to another set of armor that you've discovered. Yet for some reason, you have to keep both sets of armor in your inventory for this to work.
Similarly, Avowed has a decidedly linear method gear progression. There's nothing inherently wrong with this, except for the fact that there's only four quality tiers for normal weapons which means that there's very little visual variety. Yes, your superb sword + 3 is going to look way cooler than your common sword, nevermind that you're aesthetically limited to three European-style straight swords and a cutlass.
However, linear gear progression starts to present a practical problem if the game's damage scaling is tied to the player's gear quality. As far as can be told, all of Avowed's damage calculations ignore the player's level. Instead, incoming and outgoing damage is calculated purely based on the quality tier of your weapon and armor relative to the tier of the enemy.
If an enemy is a significantly higher tier, represented by the color of the health bar and any skulls next to their name, then they're going to take noticeably less damage. Similarly, if your armor is not a high enough tier, then you're going to get one-shot by everything on higher difficulties. Believe it or not, the the system was even more strict until Obsidian toned it down in an early patch.
Perhaps Obsidian realized that everyone will eventually level up to a minimum level, but not everyone will constantly keep their gear upgraded. Alternatively, early feedback might've rightfully indicated that most people didn't expect to lose a significant amount of damage and damage resistance simply by following the main quest and encountering their first higher tier enemies in the second playable area.
Avowed's Unique Problems with Unique Loot
Predictably, such a punishing damage model has cascading consequences. Players who are going in blind are likely going to be discouraged from switching weapons mid-playthrough because the damage loss might be too undesirable. The material cost would certainly hurt too as Avowed isn't exactly showering you in resources.

You would think that this would encourage people to find unique gear and stick with it to the end of the game, and you'd be right. The caveat is that a good number of Avowed's uniques have decidedly unspectacular effects. Annoyingly, there's no gear set bonuses either even though there's clearly gear sets.
This is especially noticeable in the first half of the story. No one's saying that you have to give new players the best armor in the game after their first kill, but surely Obsidian can do better than unique rings that slowly heal you when you're wet in a game where 99% of the time is spent on dry land. Other curious effects include boosts to parkour speed, health regen when walking, and the fact that Avowed's unique grimoires don't have any unique effect at all.
To put this into perspective, you can find an accessory that provides a 5% crit chance boost halfway through the game and that alone is more impressive than any other accessory that you could've looted thus far. Avowed's unique weapons are slightly better, though they mostly just provide a 10% bonus of a specific elemental damage type.
With how tame some of these unique effects are, you'd almost think that they were designed with PvP balancing in mind. Or worse, that they were designed in a vacuum since few (if any) unique effects directly interact with your skills outside of effects that activate on parry.

The obvious fix would've been through set bonuses. Take the aforementioned ring that only heals you when you're wet. Functionally useless on its own, but what if it was part of a set that when worn together grants you a chance to make everyone around you wet when dodging? Suddenly you have a more consistent health regen effect that makes it easier to freeze nearby enemies with the right spells.
Avowed's Future Lies with its Uniques
That Obsidian didn't go in such a direction with Avowed's gear progression is certainly an odd choice. If anything, Avowed's loot system looks increasingly peculiar the more you look at it. For a developer as experienced as Obsidian is, surely they would have realized that games have been trending towards horizontal upgrade paths for a while and adjusted accordingly.
There's no shortage of other games to draw inspiration from. The Persona series essentially lets you make custom endgame gear. The Borderlands games have a million examples of how unique gear doesn't need ridiculous stats to be relevant and memorable. From Dark Souls and Monster Hunter to Destiny and Final Fantasy XIV, you can even find players who will hunt down weapons and armor purely for the aesthetics.
Make no mistake, that's not to say that Avowed is a bad RPG. On the contrary, the RPG aspect of the game is above average at worst. The major hangup is in the loot and gear progression mechanics, which feel oddly basic. Good loot is the spice that keeps combat interesting, yet it seems Obsidian has only got vanilla in their kitchen.