The problem, at least in California, is that there is too much of an incentive for a long-time resident to not sell even if they want to move out permanently. Because they are paying extremely low property taxes that cannot be passed on to the next buyer, so even with high overhead, the efficient outcome is still for them to rent their place out instead of selling. So existing houses go on the market at a very low rate -- often only when the owners die.

Combine this with the number of Bay Area potential buyers who are relatively insensitive to interest rates due to being able to make very large down payments, and of course the lack of new SFH construction relative to SFH demand... I don't think Bay Area SFH prices are actually going to drop much.

I'm more optimistic about affordability of denser housing. I'm unsure how many Americans are actually happy with that, though (which is part of why it's relatively affordable...).