on my phone (GrapheneOS / Android 14) u can accomplish this with:

1. Go to ‘App Info’ for the app (this can be done by long-pressing the app’s home screen icon > App Info)
2. Select ‘Notifications’
3. Select the specific notification kind (eg. ‘New Message’ or whatever)
4. Select ‘Sound’, which opens a radio-input of thw default system sounds, and an option to add a custom sound from a file

i would imagine most android builds, which are not very customized (such as samsung/oppo), would handle this similarly