I am more familiar with the LA market than SF, but they are legal in LA and a few are in the process of being built[1]. The developer for those is a non-profit though which leads back into your first point. We build at the top of the market because that is the most profitable. I believe there needs to be government incentives or regulation to spur construction across the full spectrum of housing
Right now we allow neighbors, particularly wealthy and powerful neighborhoods with the most amenities and the most demand for housing, to stop new housing. This is the core problem.
Housing markets have never delivered for all of society, but right now by constraining them heavily, we are in the worst possible situation. I personally support a robust system of public housing, for all income levels that uses cross subsidy to expand the public housing system and provide for all income levels.
However, being more politically realistic, even allowing young professionals to pay laborers to build new housing, rather than forcing them to pay landlords and drive out lower income people, would be a big improvement over our current system. But even this small change seems politically intractable, unless we move zoning and permitting responsibility to the state level rather than city/county level. This is how Japan has made housing much more affordable, by not local power brokers and land owners act as rentiers.
That and new SROs have been banned; they might be profitable but they are illegal to build in most jurisdictions. My town recently made them legal again, but the anti-growth crowd has paradoxically started opposing them in full force because they are "unaffordable" despite being far more affordable than other options.
Also, SRO construction could make your rent cheaper. Pressure on supply comes from both directions, and as 2-bedroom apartments that house 8 people start to have fewer people due to SROs, is helps everyone. Or due to any affordable housing, honestly. I'm of the opinion that all supply is good, from deeply subsidized deed-restricted below market rate units, to luxury that is used only as a second or third house. (Those luxury units pay big impact fees and lots of property taxes, and prevent the luxury unit tenant from taking a more affordable unit).