I understand that this might be frustrating. Personally I see it viable only after lot's of warnings given to player via events that preceed the final one. Like, for example, there is a big meteor coming towards the Earth, and if player won't do anything about it, it will end with boom.
Truly random gameovers like sudden Commander's heart attack is kinda fun too but only once, and this is not stuff I'd make in my mod, as it's not gameplay wise.