So about that one time when the obvious solution was so far from the party's mind that the DM had to break out the Clue-by-four...

There WAS that one time in a Starfinder campaign when every single member of the party tanked an important Knowledge check so hard the DM just threw up his hands and made a guide book fall out of a rift in space right in front of us.

Sounds to me like you should set up the idea that there's a Q like entity that is observing and amused by your party.

And this is why you shouldn't be rolling for anything that is neccessary for the plot to progress. Or at the very least rolling should be to determine degree of success rather then whether or not the party learns important information/gets past the big obstacle.

Always plan out how would you keep the game going if the entire party rolls a nat one.

"But a natural one from an entire party is statistically insignifica-" <slaps> NO. ALWAYS.

There's an official pathfinder campaign that requires a DC 40? knowledge check to be rolled at some point to even have an idea to know where to go next. It was the sort of DC that required someone with maximum ranks, a good stat, and a high roll.
If it was tied to seeking out information in a mysterious tome the party found, they might know to keep researching it but the module just calls for the roll without any opportunity to prepare, and has no recovery options.
A good GM can work with that, but a good module would not require making something up for one of the most probable outcomes of a random roll.

The Strange Aeons module?
Because I'm DMing that for a group of friends (we're starting the final module next session) and there have been a few weird situations like that.
It's been a strange experience running that module as a first time DM.

I forget which one, but one of the convention AD&D modules from back in the day had a situation where nobody in the area knew the location of the main event dungeon *except* a wereboar with a bad attitude and a stone shield that was not obviously magical that *intentionally* tried to pick a fight with the party for petty reasons. My party took offense to him nearly instantly and killed him without remorse, only to have the DM flip through the module a few times and throw up his hands before saying the Mod was done, there was literally no way to find the next segment. After a few minutes of talking it out, we just reconned it to him being beaten to within an inch of his life and went on from there.

Another solution would be to find a map on his body.