First in-flight mission failure for nine years
SpaceX is, of course, looking deeply into what went wrong. But let's not forget that this is their first in-flight mission failure since 2015, on mission #19. This was mission #354. In the history of spaceflight, that's an unheard-of reliability rate for a launch platform.
It's also worth noting that, unlike the lower-stage booster*, the upper stage is not reused: a new one is built for every mission, so every flight is a shakedown flight. Rather like the SLS, in fact.
Clearly there was either a manufacturing error for this upper stage, or it was damaged during stacking, or there was a materials fault. Whichever it is, the authorities and SpaceX are absolutely correct to be going into analysis to determine the cause, and set it right. That's standard aerospace practice.
Would I happily fly in a Dragon capsule on a Falcon 9 launch platform? Definitely. Don't forget: the payloads were undamaged and were deployed; it's just the rocket motor that blew up.
Would I fly atop a Falcon Heavy? No. Not enough launches to establish a safety pattern yet.
In a Starship? No way on Earth! At least not until they've proven it as reliable as F9 is now.
No, I'm not a SpaceX simp. I'm a former satellite engineer who's been impressed by their reliability.
(* In fairness, some boosters are contractually single-use. Personally, I'd prefer to use one that's got flight heritage, but I'm not writing cheques on Government accounts.)