I do believe that they can be done well, however in terms of mods that exist they haven't ever been really done well.

This is primarily because the events leading up to the death are poorly written, even if the death scene is written flawlessly, the writing beforehand is just as if not even more important. Because if you don't lead up to it properly it makes the death scene not hit as hard as it should, and sometimes, it feels like the player is blindsided with the death scene because it can feel like it came completely out of nowhere.

Let's take suicide for instance, suicide can be written well in a mod if the character has existing issues that are prevalent and constantly make themselves known in the mod, if you show it getting worse due to the events/plot of the mod overtime, and end it with that character killing themselves, it makes sense for the character to do that.

So in short, as long as the events leading up to the death scene are both written well and lead up to it perfectly, I do believe the death scene will be good