For about 30 years cities were relatively undesirable places to live in for the average person. The cities that young people have flocked to in the last decade or so are designed and developed more like large scale open shopping malls than the cities of the 70s and 80s.
The closest comparison to what's happening now was "White flight", a largely racist reaction to the civil rights movement involving increased protests and riots in cities. White flight changed many cities in the 50/60s, in particular Cleveland, Oakland and Detroit. These cities only started to see any kind of revival in the tail end of the last economic boom. For decades cities struggled with violent crime, racial tensions, poor infrastructure, etc.
What we're seeing is a much larger scale than that and will likely change cities for decades to come. In the shopping mall era of cities you live in cities because you are close to work and close to restaurants, bars and other things to do. And cities feel remarkably safe. Nearly every tech person who "loves cities!" today would feel very uncomfortable in SF or NYC of the 1970s.
Virtually every one I know in a major metro is considering leaving because: jobs no longer require being there, pandemic has destroyed much of the food and small business scene, and cities are becoming visibly less safe. The people are moving to other areas and they are very often looking to purchase a home rather than rent (since what you can get for the same mortgage as your current rent is typically an order of magnitude more space). This means they won't be coming back soon. This will cause a feedback loop that will continue to force employers to go remote, reduce the number of restaurants and leave cities more vacant and more dangerous.
It means the city will also change very rapidly and be much less attractive, even at a lower price.
But to your point about the Castro: it's precisely because "average" people didn't want to live in SF that marginalized groups found a home there. The Castro in SF, the Village in N
YC were places that Engineers didn't want to work and live, so artists and outcasts moved in.
My fear is that this collapse is going to be too extreme and too fast to allow for the creation of this interesting space of possibility that NYC and SF of the 1970-80s was. The collapse of commercial and residential real estate in these areas is going to have consequences that I think are very hard to predict right now.