I've run into some tragedy (and been a tragic character) at one of my tables.
My character is a devout cleric to his god, but circumstances have resulted in him being tricked or forced into advancing the devil BBEG's agenda multiple times. For example, the party was captured and forced to assist in experiments, and a simple experiment where I blessed the results ended up being the final input into crafting an artifact with both devil and celestial power, creating a superweapon. And then when our party recovered the artifact to dispose of it on the frustrated orders of my irate God at a key holy site, the devil adulterated the artifact to banish my god when we went through the purification ritual.
But the biggest tragedy was the most simple. When we arrived at an abandoned temple of my god to cleanse the artifact, we were attacked on sight by hobgoblins behind a makeshift barricade. Our party did the generic "fight the baddies thing", only to wipe out the group with a single fireball. My heart sank when I realized that they were carrying the insignia of my god carved crudely into their armor.
It turns out that they weren't actually antagonist warriors but civilian new adherents to my god who were drawn to that place of power and were trying to preserve and protect it. The leader of the hobgoblins wailed with sorrow and rage when he found out that these defenders - led by his brother - were killed at their posts. My character was gutted - he reveres protecting the innocent as a sacred duty.
I would say that you need a few things for good tragedy:
1.a. A player willing to make choices that would be in-character but viewed as a trap or a mistake by genre-saavy players.
1.b. This player should be willing to have their character experience tragedy and change in ways that they didn't anticipate; don't spring this on somebody who wants a happy story where their character thrives.
2. A goal or objective that this character holds dear, which could be anything from protecting a loved one to adhering to an oath.
3. Some sort of flaw or reason to lose what is in #2. This could be an external force like devilish trickery or an internal "flaw" like putting love ahead of duty or being too zealous in a faith and losing the spirit of the cause to the letter of holy guidance.
So maybe your paladin breaks his oath when the party must resurrect an ancient mage to cure a plague. Only for the mage to become a slaughtering BBEG, causing the paladin to become an oathbreaker and have nothing to show for it.
Or maybe the royal wizard saves and falls in love with a roguish visiting gentleman, only for him to be the assassin sent to kill the king. And a decision to let him into the palace to visit her quarters was the opportunity he needed to gut the royal family.
Or maybe your cleric of good loses the support of her god after refusing to stop the BBEG to save civilians, having to make a devil's pact as a warlock to continue the quest. And then wrestle with the terms of that contract and the juxtaposition with their earlier life.
Ideally this downfall will happen slowly but surely, as the character makes a number of realistic choices that result in terrible consequences and a change to the status quo.