> You cannot even select your own notification sound for Messages on MacOS anymore
I don’t see a UI for it, but when I drop a sound in ~/Library/Sounds (tested with .aif an .m4r; .aiff likely will work, too, looking at ~/System/Library/Sounds) it shows up in the “Sound Effects/Alert sound” pop-up for me.
Yes, that's the alert sound. I have 100 sounds in there and can use them for the System alert sound. However, when you receive a message in "Messages" that plays the sound chosen in Messages app's Settings window -> Message received sound. That one only shows the builtin sounds that are on the sealed, signed, tamper-proof volume.
I don’t see a UI for it, but when I drop a sound in ~/Library/Sounds (tested with .aif an .m4r; .aiff likely will work, too, looking at ~/System/Library/Sounds) it shows up in the “Sound Effects/Alert sound” pop-up for me.