This is based on opinion. Please don't list it on a work's trope example list.
Nightmare Fuel / Shadowrun Returns
Dead Man's Switch
The Reveal of Jessica Watts' true colors in the cemetery. In the space of only seconds, her cold, calm professional air shatters, revealing a hateful, raving, shrieking Knight Templar who was so obsessed with her mother that she had over a dozen innocent people murdered because their live saving organ transplants came from her mother, all because of her deranged Catholic beliefs. And that's before we learn of her Insect Spirit plot.
In the first mission in the Dragonfall expansion, the face that Monika makes when she has her seizure is pretty damn disturbing.
The guy that hired her got worse: his seizure was so damn bad that he contorted himself into breaking his back at an angle that is physically impossible.
The aforementioned seizure face makes a return at the end of the game if you free APEX without giving her control of the dragon. Your character will remark that he or she isn't afraid of APEX, where upon APEX will make the face and remark that they should be before cutting the transmission.
The same thing also returns if the player deletes APEX. It assumes the seizure-face whilst its avatar is disintegrating, presumably to make the player watch Monika die all over again.
In Dragonfall's alternate ending, if you switch sides and join up with the antagonist, you can kill off all the great dragons with some sort of wrym flu. While it might seem like a good idea at the time, it only takes a year until magic starts bleeding faster into the world, causing freak mana storms and rifts all over the place, and the coup de grâce comes in the form of extraplanar horrors that invade and drive metahumanity to the brink of extinction. Turns out, there's a very good reason to keep the dragons around...
In the Director's Cut version of Dragonfall, we get to go visit Glory's old residence. The 'Fireplace.' It's a satanic cultist compound where there's sadistic torture, human sacrifice, and terrifying indoctrination of children. And that's all BEFORE you get to the shrine, and see the 'Heart' of the Fireplace and its home.
Also from the Director's Cut, you can go terrorist hunting with Eiger and when you catch him and interrogate him, you find out who he's working for. Winternight. What's worse is that, unless you've played the pen and paper game, you and Eiger, have NO idea how BAD it is that they're present right next door to your home. For those uninformed, Winternight is a group of psychotic military-cultists trying to bring about Ragnarok, and who succeed in destroying the Matrix. Although this might be Nightmare Retardant if you decided to free APEX. The destruction of the wired Matrix is still a decade away after Dragonfall. Now, about surviving that long...
Kowloon Walled City and the dreams that are seeping out of it. Dreams filled with teeth, burned bodies, and horrible, clinging, endless feelings of despair and dread. Those are the pleasant ones.
The final Wham Line in the game. All the positive Qi had to go somewhere.
Take one magical artifact of immense power, add one rat shaman that's going toxic, and as a result you have The Rat King.
If you defeat it in combat, the story describes how the human host is devoured alive by the berserking rats.
Talking to Ermine Ka Fai eventually reveals that the high-level (human) corporate folk enjoy Hunting the Most Dangerous Game (metahumans). While it's non-lethal, they still take "trophies" (ear-tips, horns, tusks, etc.).
Ermine's husband Henry had loved his time at sea on a rescue tugboat. This changed when he knocked out his Captain in order to respond to a distress signal, which wound up being a Pirate trap. But it gets worse - said trap was the pirates having hacked the limbs off a shipload of civilians, including women and children, forcing Henry into a Sadistic Choice of staying to help while trying to fight off an overwhelming number of pirates, or fleeing and leaving those people to their fate. This handily explains why he's horrified that his son wants to become a pirate.