The one's I have used would still allow the screen to sleep but they would flash the entire screen when you had a notification. You could customize how often for it to flash and how long. The NoLed app says it just flashes a dot on the screen. Not sure how that would work but sounds cool.
Yeah each notification can have its own little square dot in a user defined color or alternatively it can just display the small app notification icons. The few default notifications like phone, sms, gmail have their own icon that can also be set to different colors. All of the dots/icons will pop around the screen randomly every second or so.