The biggest "narrative dissonance" in adventure games set in the modern day is the lack of retail stores.
Need a screwdriver? Better hope there's one lying under a brick in a back alley somewhere!
I know that simply buying every solution you need would make for a terribly silly game, and I can pretty easily ignore how little sense that makes while playing, sure. This isn't a complaint, exactly. Just something that went through my head earlier.
I remember not having any money to spend or a car to drive when I played adventure games, so I could relate :) And in 30 more years retail may be even more dead.
What are some good games that opt for a grounded approach? I agree that it is few and far between (whether that is good, bad, or indifferent is a topic for another space). A few that come to mind are Don't Escape: 4 Days to Survive (takes place in a survival situation) and Dreams in the Witch House, which incorporates shop/money mechanics and other survival management elements.
You wanna know which came ISN'T grounded? Nightlong: Union City Conspiracy. If you are in the mood for some hilariously baffling moon logic, outlandish set design and embarrassing period voice acting and CG, with a plot that makes slightly more sense than The Mystery of The Druids, give it a shot. It's basically "we have Blade Runner at home."
Guess how they travel in the cyberpunk future presented in this game: flying cars. But guess how they receive documents when out of the office? Public fax machine booths mounted on walls of train stations. That's right. FAX MACHINES. The future is now.
The game is not bad per se, it will definitely stay in your mind with its utter weirdness. Grimbeard did a detailed video on it if you are curious.
Stardew valley. The resources required to have a tool made by your blacksmith make sense and most people can’t simply walk down the road and buy a steel pickax. Especially not in a small rural town. But it’s still gamified sufficiently so it doesn’t just feel like work/arbitrary.
This actually could be a cool concept. Multiple ways to get the next key item, but some are significantly faster. Like, you could have whatever key item you need shipped, but that would take a day. Or you could go to the retail store and buy whatever, but then you have to spend money, stand in line etc. Or you can just find it in the back alley.
Then it's just a timed challenge to find the best (or most fun) route, but you always have a way to progress.
For example, would you take a shortcut and make items appear because you're fishing them in the pond (which makes pond a source of "outworldly" items), or rather come up with something more creative? The logistics of it can tell the story better than 1000 words on a wall made to push the narrative. Of course, at the same time, you don't want to make this a main focus to the player, unless it's an objective.