Yeah, I can relate to people saying they nearly got PTSD, I sure did get it. Paging apps use seriously offensive alarm sounds. I hated every sound they had in the options. It made me instantly sick. Fuck that!
The (in)offensiveness of the sound has little to do with it. For quite a few years after I left my job as an SRE at google, this gentle little tune called "morning flower"[1] which I used as my pager alarm sound would give me a little jolt of adrenaline. Even now, it's a bit startling, but it's old enough now that at least I do not hear it in the wild anymore.
The right answer is, in my experience, something that starts soft and intermittent, and then gradually ramps up in loudness and annoyance. In bed, it's enough to have a harpsichord gently playing, but if I'm watching TV or out at a restaurant I might need it to eventually escalate to a klaxon to get my attention.
My first experience with this was Groovy Blue: https://youtu.be/WCbyp_UuyoM?si=s0ZpfcXZKw7dP17i