As much as I would love it if modern builders tried making things look nice again, in practice I think this would just result in glass cube copypasta and beige foam
While we think SF would look great if it were like in the Star Trek movies, that would pretty much destroy a large part of what makes San Francisco San Francisco.
People come to SF to visit the attractions at the high density parts e.g fisherman's wharf, union square, chinatown, ghirardelli square, coit tower, etc.
Look at a standard tourist map of SF: https://www.sftodo.com/sanfrancisco/wp-content/uploads/2018/...
No one comes to SF to visit the vast tracts of suburbia in the sunset: https://goo.gl/maps/p91HMQTcXBQ2
No one comes to outer Richmond, Sunset, or Parkside for the "charming old houses". It's mostly single-family sprawl.
The funny part is there are 6-10 story apartment buildings in Sunset that are illegal to build under current code. Doesn't seem to be "ruining" the neighborhood.
The vast bulk of SF is probably not of any particular interest, though. Most European cities will have areas of historical interest where buildings are protected, sometimes incredibly strictly (here's a 250 year old 8000sqft house which would require 1.5million renovation to make it habitable, due to strict controls on acceptable renovation methods: https://www.independent.ie/life/home-garden/homes/georgian-p...). But that doesn't mean that you can't densify anywhere. Areas of historical importance are normally a small fraction of a city.
There are very few tourists in the vast majority of the Richmond and Sunset, except near the water and Golden Gate Park (around the museums). Also, there are very few tourists south of Bernal Heights Park.
If all those low density areas in SF were converted to high density, SF could grow a lot and housing issues could be plaited temporally (years?) but after a while it be the same thing, where there's more demand than supply.
Personally, I think having single-family housing inside a city is a waste of space but a city full of 30 story buildings usually is not very pretty. I like the Eixample, were streets are wide and buildings just the right size so there is plenty of sunlight, airflow and all that. Obviously if I were an owner of a single-family home in SF I would be against of converting my quiet, low-density neighborhood into all high-rise.
Tricky problem to solve.