Re: the Borg
First to be clear. There are no underlying mechanics. Its a tv show.
We are talking about getting results similar to the show in an RPG - so we should then talk about the game-representations we can use for things.
And I just can't interpret the narrative that way at all.
Well, if you can't think in new ways and directions, that'll get in your way here. My answer becomes, "if you think about your game differently, you can get these results."
The Borg basically stop adapting by the time Voyager is dealing with them.
Well, perhaps we have different ideas of what Borg adaptability really is....
The Borg (with the exception of the Queen) have no individual or original thought, no imagination*. They create nothing new, and are thus incapable of R&D. They adapt by assimilating new technologies and biologies. They are adaptable in the way that a spellcaster with a large and varied spellbook is. If you hit them with a weapon, they run through their list of tricks, and apply one that will work. But, if you hit them with something truly novel, that isn't in their book, they are stuck. So, standard phasers, or other beam weapons? Psha! That's easy, because energy beam weapons are a dime a dozen, and they've seen hundreds of variants already.
But, for example, with Species 8472, the issue is that they are resistant to Borg nanoprobes. Sp.8472 are from a different dimension the Borg have never seen, and have a biology unlike anything in normal space. They can swap in all their different nanoprobe tech, but if none of those work, they are stuck. The crew of the Voyager, however, are full of individuality and imagination and the ability to pull new concepts out of their butts.
Regarding opposition. In D&D, yes, there is (or can be) the treadmill. But again, Trek doesn't really fit that model. The planet-of-the-week really doesn't fit any curve of advancement.
My point is that, from the outside, if your advancement isn't accompanied by highly visible pyrotechnics, you may not notice advancements in the resulting narrative. If the character can hit targets better, the GM provides targets that are harder to hit, and in the end your overall hit percentage doesn't rise. In your first session, you hit 25% of the time. In your 100th session, you hit 25%. From the outside, it looks like the character has not advanced! But, the reality is that your 1st session character would get eaten alive by the forces seen in the 100th.
Which is to say, the end narrative does not fully specify what the mechanics of the game look like. Just because you don't see advancement on the TV show, doesn't mean it doesn't happen in the game.
Regarding buy-in: yes. I'm willing to say that you have a handle on what I'm getting at. My experience with players in multiple systems doesn't match yours. In a traditional combat-centric game (D&D (multiple editions), Boot Hill, Savage Worlds, GURPS, etc.) I always see the players reverting to murderhobo methods, especially if they come to feel untouchable by law enforcement (as setting appropriate).
Okay, I'm pretty sure that's got little to do with the mechanics, and has much to do with what your players want, and what behaviors you reward.
In part, I think this because "it only takes one" of the PCs to start shooting/torturing/whatever.
You see, that's your players, but not mine. If someone tries to torture a prisoner in my group, this will result in an hour-long discussion of the ethics, and if the torturer doesn't back down, that's probably a party-breaking crisis. That's just not who they want to be.
In another part, traditional games tend to lack clear non-combat resolution systems and thus there is a lot of "when all you have is a hammer, everything is a nail" going on. IME, players choose concrete resolution over talking, whenever possible.
With respect, again, that's your players. I used to run classic Deadlands, which is more combat-centric** than its descendant, Savage Worlds. And my players shot a lot of things. But, all the things they shot were bad, and they didn't go around torturing people. They could have - this was the lawless Wild West, and they eventually got to the point where they'd be an even match or better for the game's Wyatt Earp and other lawmen. But the players
wanted to be heroes. They didn't shoot things and take the gold. Of their own volition they made their home in Dodge, KS, and became its protectors.
But that choice has NOTHING to do with the mechanics used.
And that's where I do think system matters, in the absence of player "buy in".
I strongly disagree. While a game can support a playstyle choice, the game will not make that choice for you. If you don't want to play a good guy, no game will produce the result of you being a hero, because your choices will be non-heroic ones. If you don't have buy-in, the mechanics are irrelevant, and probably annoying.
*This is actually much like some old-school versions of the fae.
** The largest chapter in the classic Deadlands players rules is titled, "Blowin' Things All to Hell".