Unfortunately no. There are apps that have custom notification sounds, or allow for the notification sound to be changed from the os default “tri-tone” sound. But that would seem to be a feature that an individual app developer chooses to add to their app (or not).
But even if the Blink app did enable custom alert sounds, that wouldn’t even completely solve the problem of middle of the night notifications, IMO. Unlike android, iOS does not allow for individual app notifications to bypass do not disturb. So even if I could change the Blink notification sound from the default to something more persistent/annoying to ensure it woke me up, I wouldn’t use it in the middle of the night anyway, because every other app that sends a notification would end up waking me up too.