Say it with me:
San Francisco is NOT the same as the Bay Area.
No, most of us wouldn't be able to afford a decent home in SF proper. But SF is just one city in a large metro area, with plenty of places you would and could want to own a home. Those of you who are 20-something: the day is coming, sooner than you'd imagine, when you're going to want to raise kids. And unless you've knocked the ball out of the park and are worth $20 million, you're not going to want to raise your kids in SF anyway.
if nobody who hasn't already made it can afford to live there what happens then?
You can afford it. It's an expensive area for sure, but there is much more to the Bay Area than "Silicon Valley" (whatever that means nowdays) and SF. I see this attitude a lot among recent "immigrants": this myopic view, apparently based on experience in other, very different metro areas, that there is One Big City and a few "suburbs." Things aren't like that here, and the reason you feel so frustrated is that you barely understand the place you live.
The "great" schools in the Bay Area are pretty much exclusively in places with $1m+ house prices
Wrong. For e.g., Pleasanton has (arguably) the best public schools in the Bay Area, and you don't need to pay $1 million for a house. But you've barely heard of it, because the only places your friends talk about are Redwood Shores, Mountain View, and SF, right? Expand your understanding.
San Francisco real-estate is absurdly corrupt.
So don't live there. It's just one city in the Bay Area. See above.
giving me the ability to pursue my own dream projects for more of the time.
SF and SV were built on that, and it's not possible there anymore unless you've got big investors.
Of course it's possible, and people do it all the time. More than ever, you do not need Big VC Cash to bootstrap a good startup. You could theoretically do it anywhere, but the tech-oriented population combined with the free-thinking culture will come in handy if/when your company grows.