Mark Rippetoe, while kind of crazy and not a cyclist, has done a pretty good synopsis of this in his book Practical Programming. A key difference when it comes to time frames for any sort of significant supercompensation is training experience.

A novice, say, someone who has only been riding for a couple of months, can likely see benefits from every single session. So a simple, ride, then recover, then ride again as soon as you're recovered is all that is really needed.

An intermediate, say someone that's been riding (and, more specifically actively seeking to improve) for anywhere from 6 months to a couple of years may have to take a couple of hard rides, with incomplete recovery, in order to drive meaningful supercompensation. So, for example, doing a hard interval ride on Friday, then a long ride on Saturday and then another fast/hard ride on Monday, then rest Tuesday - Thursday.

Someone who is advanced, which might be someone who has been training hard and racing for more than a couple of years will have to do even more to drive meaningful supercompensation. This is where you would have something like a hard training block lasting several weeks (with the type(s) of rides being largely determined by what physical attribute(s) you're looking to improve) where fatigue steadily increases, then following that with a week or two of easier riding in order for the fatigue to dissipate.

So, as you say, anyone beyond a beginner level will likely have to train while fatigued at times in order to make meaningful gains.