Not only are SD cards not happy with losing power during writing, but they are very sensitive to "brownout" undervoltage conditions. In this case writes may appear to succeed but actually fail to commit, which is obviously problematic for the integrity of your data.
(if people were using a checksumming filesystem like ZFS, this of course would be immediately apparent when it was occurring!)
Samsung sells "high endurance" SD cards - I would strongly recommend these for RPi usage, and they are also very useful for dash cams since those are always continuously writing as well. Sandisk sells high-endurance cards as well but frankly Samsung is a cut above the rest of the SD card market - my SD card failures have essentially gone away since I stopped buying other brands. I think I have had one SD card failure since then and it wasn't related to write endurance, just didn't use it for a couple years and it was dead when I tried it again.
But yes, in general, power quality is a massive problem for RPis, and people don't really consider it because it's one of those "it works 99.9% of the time" situations. It's like a race condition that you only rarely ever hit, it looks correct and people will die on the hill of "it's worked perfectly fine for months now, the power can't be a problem" and then you hit a weak flash cell when the CPU is heavily loaded and the voltage is starting to droop and it happens to be a critical file rather than just some log or a chunk in an audiovisual file somewhere, and then you notice it.
I'd say >95% of all Pi failures come down to either power problems or SD card wearout. They're certainly not otherwise flawless, it's a janky cheapass SOC in general, but that is the overwhelming cause of Pi system failures.
If you can swing it, network booting from a fileserver is a much more reliable option in the long run. I haven't really experimented with it, and performance will probably be worse, but it gets the SD card out of the equation entirely, which mitigates both of these problems. You're not writing to flash, so brownout doesn't matter in terms of the potential for failed writes, and you aren't writing to a physical SD card so there's no wear.