Custom Alerts System - Need a hint

Hello everyone.

For my current application, I am trying to develop an "Alerts System".

The main function of the app is to allow a user to manage "jobs". Jobs can be created and modified with ease, each job carries a ton of information, customer id, date, hour rate, duration, address, state, payment state, payment method, etc. So basically we have the table "jobs" with all these fields and some more.

When a job has been changed to "Executed" state, it means the job has been finished and is awaiting payment by the customer, if the payment is already due and the "payment state" has not yet been filled, then an alert should be generated so the manager can quickly take care of it.

I have been brainstorming on how to achieve this, this alert system has to query the DB often to check for "Executed" jobs with "payment = 0". Right now there are more than 50k jobs in my DB, and to run this query all the time is unthinkable...

I can make it so the query runs when the manager decides to check for Alerts manually, but that defeats the purpose of what I am trying to achieve.

This alert system would also be useful for more things:

• customers with no telephone number information or other sensitive data
• jobs that have had their final date pass and still haven't been changed to "executed"
• customers who haven't had any jobs done in the last year so the manager can either run a campaign or take some other action
  

I wonder if such a thing is achievable nowadays without compromising the application's speed too much or taxing the server resources too much.

Any suggestions would be very appreciated.

Ibdont think you'd have much of a problem running the query as long as your databases is setup correctly with indexies. Are you running shared hosting or vps/DS?