I'm comparing to large-sensor cameras on large 4k screens, of course.
What is "usable" or not varies dramatically depending on how large you display it.
Here's a JPEG from a single shot 12MP RAW made with Halide (can't shoot 48MP single shot RAW for some reason): https://drive.google.com/file/d/1oqQB_UbdBaaoM3vC2IG_9jzP2AG...
Here's a JPEG from a ProRAW 48MP made with the camera app: https://drive.google.com/file/d/1oqQB_UbdBaaoM3vC2IG_9jzP2AG...
Here's a JPEG from a ProRAW 48MP made with Halide: https://drive.google.com/file/d/13GW_CIIvOSFEsKcON28Y34NgQAA...
All images are shot on an iPhone 14 Pro Max and processed in Lightroom (60 on the sharpening slider, no noise reduction). There are certainly many differences between the images, but it's not as if the single shot RAW is a total mess. In fact, in this case the single shot RAW is cleaner and better looking than the ProRAW output of Apple's camera app for the most part. It even has more textural detail in a couple of areas where Apple's processing has smoodged things. This could partly be because I was able to select ISO 57 using Halide, whereas the camera app chose to use ISO 100. (All images were shot on a tripod, so there was no real need to use a higher ISO.) As you can see, Halide's ProRAW doesn't smoodge quite as much. I generally prefer the Halide ProRAW to the single shot RAW, even though it looks a little more processed when pixel peeping.
There is clearly more noise in the single shot RAW (as you'd expect). However, bear in mind that the JPEG above shows the result of doing fairly heavy sharpening and no noise reduction whatsoever. With more balanced processing the noise is much less noticeable. Here's an example: https://drive.google.com/file/d/1PQKAcE-Cr-M6Uz-rcXTDt3OM0Ej...
The noise levels are perfectly acceptable. If you're finicky about noise (a pointless obsession, IMO), then of course you won't use single shot RAWs from a cellphone camera. For any ordinary photographic purpose the level of noise from a modern phone sensor is perfectly fine – even without any fancy processing or multishot blending.