It allows you to debug any language that compiles to JavaScript, with the caveat that the language's compiler must output source-map compatible code (or the source must be run through some other program that somehow does it).

It's a pretty simple but extremely useful idea. To use your example, the CoffeeScript compiler knows exactly which bits of generated JavaScript correspond to exactly what bits of CoffeeScript code, so the compiler can output a mapping that says "these lines of JavaScript are the result of this line of CoffeeScript...so when you step through this CoffeeScript line, execute these X lines of JavaScript, if an error occurs in these X lines of JavaScript show there error as occurring on line Y of the CoffeeScript code.

I haven't done much front-end lately so I'm not sure what if any the CoffeeScript compiler has in terms of generating source maps, but if browsers are supporting it I imagine it won't be long if it's not here already.