I think you are ignoring the racial angle here. San Francisco became trendy because it was a major urban center, a walkable city, with less than a 6% black population, and that population was relegated to the Bayview area, which of course did not become popular with tech workers as a residential destination. You have a similar situation for Seattle. And Austin. Or Portland. But why is there is no big tech gentrification happening in South Chicago? Or DC? Or Houston? Or Gary, Indiana? Or St. Louis? Show me one urban core area that has more than, say, a 20% African American population that is popular as a target for tech gentrification. I'll wait.
The fact is, there just aren't that many cities that fit this bill, and those that do experience rapidly rising real estate prices. As with many issues, race is a taboo factor operating behind the scenes in determining which neighborhoods are targets of gentrification and which are not. When you create a lot of low income housing, people viewed as more undesirable move in and the high income earners go elsewhere. Then you rinse and repeat. But of course the problem is not that there's a shortage of housing or even a shortage of affordable housing. Median value of owner occupied units is $245K, which is 3.5x the median household income[1]. That's perfectly affordable. But the median home value in a major urban city with less than a 10% African American population is a different story. So there's a shortage of housing in our diverse society that has the "right" kind of demographics. As we become more diverse, the number of locations that are predominantly white or asian will get more and more scarce -- and more and more expensive.
You see a similar situation in Sweden, which accepted a large number of migrants and put them on the outskirts of major cities, and of course the house prices of units in the urban cores that were free of migrants skyrocketed. People are paying to not live in areas with large migrant populations. So all of a sudden housing is becoming 'unaffordable' in Sweden. But the unspoken part is that housing with few migrants is become unaffordable, not all housing. Again, this is a social problem, it's not a problem of a shortage of lumber or space.
So building more low income housing doesn't have the effects you believe, in terms of increasing the amount of housing in highly desirable areas. It will have the opposite effect, by removing one area that has the desired demographics, it makes the remaining islands even more desirable and thus even more expensive. At least this is my reading of history. It was very clear, after the race riots of 2020, how home prices in predominantly white/asian enclaves began to rapidly increase, at which point those who wanted to live in these enclaves began complaining about a "housing crisis" because they could not bring themselves to complain about a racial crisis.
But hey, I could be wrong -- housing in St. Louis or Detroit is still affordable. I'll wait to see tech workers scramble to move there, rather than, say Salt Lake City.
[1] https://www.census.gov/quickfacts/fact/table/US/VET605221