Okay, somewhat big spoiler here - thus a spoiler in a spoiler. The end basically goes down like this:
Click Here To Toggle Spoiler Content
Orph is walking. Eurydice is behind him. Both sing (in their minds) of what happy lives they will live once they are back above. While Eurydice continues in her optimism, the appearance of the fates (basically the incarnation of the little voice in your head going "but what if?" shows the audience how "doubt comes in" with Orph. He starts questioning how a simple guy like him is supposed to do this - why Eury should be following him - who is he to lead her? or anybody? They raised the stakes a bit by Orph acting as a figure of inspiration to all of Hades' workers who told him that if he can do, they are convinced that they will be able to do it too. All this pressure is getting to him. He can basically see the light at the end of the tunnel when doubt overcomes him and he turns around to see Eurydice stop right behind him (on the drum revolve). He says something of the like of "...You are there...", she says yes, turns around (towards the audiences, crying), falls to her knees and the drum revolves sinks into the stage with Eurydice on it - Eurydice has been taken back to Hadestown to stay there forever. Orph sinks to his knees.
The story is over. Hermes comes in, saying the same words he used in the opening number "It's an old song [...] It’s a sad tale, it’s a tragedy - But we sing it anyway"
There is little ambiguity. The good guy just lost by his own fault, overcome by his doubt ("I tell you where the real road lies: between your ears, behind your eyes. That is the path to paradise and likewise the road to ruin" - as Hermes warns Orpheus before he starts the journey home). After all he went through there was no happy end for him and there was never meant to be one.
This leads to Orpheus being a relatively static character, while all the others around him have more of an arc (Eurydice realising that Hadestown isn't a nice place, that she made major mistakes, her realisation that Orpheus was what she wanted all along. Persephone and Hades rekindling their love, changing in the process - a process only started by Orpheus endless love for Eurydice.).