A marketing crm application I've build involves a feature where the application tracks and scheduled actions for a specific contact. The user schedule these up front and application execute them on time.
When they are created the scheduling instructions are provided as follows.
1. Execute this action on contact creation.
2. Execute this action after 2 days of creation.
3. Execute this action after 3 days of creation.
These actions contains, email sending, reminders to self etc.
The way I've placed it now is, in background the application loop through and execute these actions. Once an action is executed a record is saved in database to keep track. The next time it runs it can skip the already executed steps.
1. Get all the possible steps.
2. Query and loop through each contact.
3. On each contact, query the action tracking table and see which steps has been executed.
4. After excluding executed steps, loop through possible steps and check if any step can be executed (checking with creation date)
5. Execute the steps and record in tracking table.
This flow has allowed flexibility on action management. Because it query actions and it's related instructions on each execution user can go ahead and update the actions any time and have them reflected almost instantly.
However I have to run this flow on every 5 minutes, and right now my application has total of 100,000+ contacts and it's getting harder to maintain.
Is there a better way to do this or should I just optimize the current flow?