A couple of things here. I've never ruled (in any edition) that the target of a successful Charm spell understands that they were under the effects of a mental compulsion for the duration of the spell. Only in scenarios of extreme outliers (such as if the player tries to get the NPC to do something specifically antagonistic toward its own nature...or if the NPC is a well accomplished spellcaster) would I consider this ruling.
My understanding is that the rulebooks agree with that approach. I can't find it right quick in my old books but I know the 3.x books find it this way on PHB p177:
"Succeeding on a Saving Throw: A creature that successfully saves against a spell that has no obvious physical effects feels a hostile force or a tingle, but cannot deduce the exact nature of the attack. For example, if you secretly cast charm person on a creature and its saving throw succeeds, it knows that someone used magic against it, but it can’t tell what you were trying to do. Likewise, if a creature’s saving throw succeeds against a targeted spell, such as charm person, you sense that the spell has failed. You do not sense when creatures succeed on saves against effect and area spells"
It provides ruling guidance for successful saves only and is silent on failed saves;
First off, similar wording also exists in Pathfinder, without the specific example. As you note, it is silent on failed saves. In such cases, the spell does have an effect on the target - a magical compulsion. The question now simply becomes whether the target knows it was the victim of a spell once its effects have ended. I'm not sure any spell states the answer one way or the other. Some seem more obvious than others (Hold Person, for example). One possible dividing line would be compulsions (Dominate Person, Hold Person) versus less the forceful charms (Charm Person; Suggestion).
I note that a DC 25 Sense Motive check enables identification that a person is influenced by an enchantment, (reduced to 15 for a Dominated person). Perhaps the same rule should apply to the victim, post-save. I don't see any better skill, off the cuff, for such a self-assessment, assuming the presumption that the knowledge one was a victim of a spell is not automatic at the cessation of the spell.
The target feels a hostile force or tingle on a failure but cannot deduce the effect (unless, also implicit, they are an accomplished spellcaster with the accompanying acumen). No tingle on duration running out and puzzling over or inability to deduce the affect...and certainly nothing declaring stock awareness of the effect and SoP deduction of a magical compulsion.
The sucessful save means they know they were the target of a spell. After watching the caster speak with a strong voice while making measured and precise hand motions, then feeling that little tingle, I'd expect most characters, especially those with any familiarity with magic, to be less than pleased with the caster. I don't think the means of deducing effect is in any wayimplicit - a Spellcraft check is the RAW means of detecting what spell was cast from observing its casting.
As always, choosing interpretations more favourable to the spellcaster where there is some doubt enhances the power of the spellcaster.
Grabbed my Fate Stargazer right quick and it basically handles "Black Magic" Power of Domination charm the same way (there are gradations in the Trappings and Stunts but this is Charm specifically):
"A subtle maneuver which isn't immediately obvious, this places a temporary aspect on the target. It is resisted by Resolve or an appropriate power skill. On a failure, the target may not know the character tried to charm them."
Hypnotize more aggressively asserts "on a failure, the target knows the character tried to Hypnotize them."
I note that this non-D&D rule indicates it isn't
immediately obvious, and allows a possibility the target does know about the attempt to charm them on a failure. So the question becomes when does it become obvious (liking that Sense Motive more and more), and how one determines whether a failed attempt was or was not detected.
4e D&D just mechanizes the various iterations of the power with the Arcane keyword, gives you a bonus to Diplomacy or lets you use Arcana instead of Diplomacy and says something akin to "You weave magic into your words, defusing a dangerous situation through the fine art of diplomacy." No duration and nothing about deducing a hostile invasion of your autonomy by a magical compulsion and becoming hostile. More apropos is the Ritual version of the spell (Call to Friendship). It has duration and its effect and duration depends on your Diplomacy check. On memory and behavior post-Ritual, it says; "Once you complete the ritual, make a Diplomacy check to determine the effect it has on the target. Once the ritual’s duration expires, the target’s attitude returns to normal. The ritual does not affect the target’s memory in any way." Post-ritual target's attitude reverts to normal and memory is unaffected.
I'm not a 4e expert, off the top. This, however, seems much more subtle, enhancing diplomacy rather than a Charm effect.
For what its worth, I just asked a few folks who are not gamers what they intuitively felt the situation would be post-charm. The consensus was that the target might be conflicted/puzzled if, at the end of the duration, they were doing something or in a place that they never would have been in otherwise (eg all of a sudden they are in a tavern that they outwardly hated or vowed to never go to). If this were the case, depending on their intelligence or understanding of spellcasting, they maybe should get some kind of check to surmise the truth of things. Beyond that though, no reflexive deduction or hostility. I don't recall if that was my intuitive response to the spell when I first started playing but it wouldn't surprise me if it was.
Asking anyone "how would magic work" seems unlikely to generate a consensus at all. Of course, one other aspect not discussed is whether a target would blame a very persuasive Diplomacy check on a magical efect, even when it was not magical. "I would never have willingly done that - I must have been ensorcelled". Too late to Detect whether there was a magical effect, as the spell has expired, so tough to prove or disprove.
That must be why I didn't assert this.
For instance, if the player succeeds in persuading the NPC to do XYZ, then the NPC can't change his/her mind even if the GM thinks that that would be "better for the story", or would be likely given the NPC's personality (the NPC's personality should have been a factor in the prior action resolution, and so already taken full account of).
Your statement sounded to me like the social skill must resolve a conflict once and for all ("the NPC can't change his/her mind") for such rules to equate to the narrative control of magic. I must have interpreted that statement more strongly than you intended it.
EDIT: If the only way that players of fighters and rogues have to effectively change the narrative space is to have their PCs kill things, then they will take that approach. This has been a recurring issue in D&D.
This is not limited to non-spellcasters. If all spared prisoners eventually return to threaten the party again, they stop taking prisoners. When every (most? some?) NPC they befriend betrays them, they start treating them all as enemy combatants. Once, a villager turned out to be a lycanthrope, so now every NPC we meet has to submit to a "silver manacles" test before we will extend any trust to him. And we pour Holy Water on him. And touch cold iron to his flesh. Hey, what do you mean the villagers aren't friendly to us?
When every attempt at mundane diplomacy (or every one that has any real meaning to the game) is overridden by the GM based on NPC personality and role playing, the players quickly learn not to bother with mundane diplomacy. If all prisoners are silent from fear of their ultimate master, the players learn not to bother taking prisoners, much less questioning them.
And, as a second "of course", this does not apply to boring old plus items (+1 to hit, damage, AC). IIRC, earlier in the thread it was mentioned that combat performance pressure might force the fighter into picking "being better at fighting" feats over non-combat/narrative control ones. The same applies to magic items. The fighter might feel forced to choose to upgrade his sword from +4 to +5 over getting that Passwall Ring, either by his own expectations, or by peer pressure.
So, to get the fighter to *actually pick* narrative control abilities, whether it is new class abilities, or old magic item abilities, how do we relieve that pressure? By limiting free selection, forcing certain build options? (In fact, we have DMs out there who, I assume unintentionally, exclude the fighter from "narrative control" style items, simply by limiting available treasure...)
Once again, the fighter who devotes all available resources to enhancing his combat abilities now complains that he doesn't have non-combat abilities. So trade off some of your combat enhancers for some non-combat enhancers. The game allows a highly focused character - the players choose how narrowly to focus. If the fighter took a low DEX, specialized in a two handed melee weapon with numerous feats, and spent all his wealth upgrading that weapon, would we feel sorry or him when he complains that he doesn't have a great AC and isn't very useful in ranged combat? I suspect most of us would point out that every choice he made leads to him being a one trick pony. So why do we feel sorry for his being ineffective out of combat when he dedicates no resources whatsoever to effectiveness anywhere but in combat?
That's probably a main question we should be asking here - is the goal to provide
options for these martial characters to have more influence outside combat, is it to
add abilities that provide that influence while not requiring any reduction in their combat abilities, or is it to
force these out of combat abilities on them. Because I suspect if we gave them a series of abilities designed to enhance out of combat influence, a portion would want the option of trading off these non-combat abilities for more combat abilities. Then, a portion of them would still complain they are ineffectual outside combat because "the game forces them" to devote all their resources to combat skills.
Maybe the question is how the GM goes about making it clear to players that the game will include non-combat challenges, so being an expert in "just combat" will mean you are bored/frustrated by out of combat challenges, just like a character with tons of skills and non-combat abilities will provide little assistance in combat. Unfortunatey, what some see as "encouraging a balance", others perceive as "punishing the player" for not designing to the GM's desires or "removing player choice" from character design.