Is SF on a downturn? Absolutely. The biggest change for me has been the mission, specifically right around the BART stations, those couple blocks have gotten out of hand.
Is it going to recover? Can't guarantee it, but the city has faced worse. It may not recover for a few years or a decade, but it will be back eventually. In the meantime, is it still worth visiting and living in? I think so, weather is great, nature and setting is great, still lots of weird and interesting things to do. Crime is up, and it is unfortunate that you can't leave anything visible in your car most places in the city, but it was like that before the pandemic also.
I think your observation of the suburbs doing well is astute. For my peers (mid-30s couples with dual incomes), COVID wasn't a time to leave the bay area, just a time to leave the SF rental market for the SF suburbs and buy a house. So many of my coworkers and former coworkers bought houses in 2020 and 2021 while interest rates were low. Almost no one bought in SF proper but lots of people within a 30min BART ride of the city and still go into the city on weekends or work hybrid model on weekdays. FIDI and SOMA east of 4th are still fine, albeit quieter.
I am not sure about the history of detroit but SF single family housing real-estate is doing just fine, there doesn't seem to be an exodus of people selling their houses if they actually have a house in SF. If anything high interest rates are actually keeping people from leaving SF. Also, generally the neighborhoods where there are single family homes (which is (un)fortunately most of the city), are pretty vibrant still. Except for maybe the mission.
It seems like the biggest exodus from SF was the younger rental market, single people in their 20s. The crowds feel a bit older than they used to, but then again that could just be my circles.
So generally I am a long term believer in the City. But it will probably get worse before it gets better.