The Job Scehduler

What is the Job Scheduler?

The Job Scheduler in CX Vizz is a robust feature designed to automate and manage the execution of various actions according to predefined schedules. This scheduler operates using regular expressions (RegEx) to define and trigger the timing of actions, providing users with a flexible and powerful way to control when tasks are executed.

Each schedule can consist of one or more action steps, which are executed sequentially. It is important to note that if any action step within the schedule fails, the entire schedule will halt immediately. This ensures that no subsequent actions are taken if a problem is encountered, allowing users to address the issue before continuing.

Whenever a schedule begins execution, detailed logs are generated. These logs are crucial for tracking the execution process, troubleshooting issues, and ensuring that all actions are performed as expected.

To prevent overlapping or conflicting executions, the scheduler is designed so that a new run will not start if the previous run is still in progress, even if the scheduled time for the new run has already passed. This ensures that each scheduled task is completed before a new one begins, maintaining the integrity and reliability of the system.

All schedules are stored in a dedicated database table, making them easily accessible for updates. Users can modify the schedule configurations directly in the database without the need to restart the CX Vizz application. This dynamic update capability allows for greater flexibility and responsiveness in adapting to changing business requirements.

Job Schedule Overview

A Job Schedule in the system is a single entry in the job_schedule table. This entry defines the overall schedule, including the current status and recurrence rules. Once a Job Schedule is created, specific actions can be associated with it. These actions are defined in a separate table and are linked to the job schedule by a common identifier.

Job Schedule and Actions Relationship

A Job Schedule can have one or more associated actions. These actions define the tasks that will be executed when the schedule is triggered. The relationship between Job Schedules and their actions can be visualized as follows:

erDiagram
    JOB_SCHEDULE {
        uniqueidentifier key PK
        nvarchar cron_expression
        nvarchar description
        datetime2 last_run_time
        datetime2 next_run_time
        nvarchar status
        nvarchar disabled_reason
    }

    JOB_SCHEDULE_ACTION {
        uniqueidentifier key PK
        nvarchar action_type
        nvarchar command
        bit is_disabled
        uniqueidentifier job_schedule_key FK
        int order
    }

    JOB_SCHEDULE ||--o{ JOB_SCHEDULE_ACTION: has

• JOB_SCHEDULE table has a primary key key.
• JOB_SCHEDULE_ACTION table also has a primary key key and a foreign key job_schedule_key that references the key in the JOB_SCHEDULE table.
• The relationship is a one-to-many relationship from JOB_SCHEDULE to JOB_SCHEDULE_ACTION, meaning one job schedule can have multiple actions.

Example INSERT statements

Not all fields are required when creating a new Job Schedule most are set to default values automatically, the following exmaple creates a Job Schedule that will run once a day at midnight.

INSERT INTO job_schedule ('BEE1A5E2-21F1-4AB0-8DF7-BB2D915C7F3B', cron_expression, description) VALUES ('0 0 * * *', 'Daily job run at midnight');

And the Actions can be added to the schedule:

INSERT INTO job_schedule_action ([key], command, job_schedule_key, [order]) VALUES ('DDB4A8C1-4321-4E11-87A9-FD4D915C8F1A', 'conversations', 'FBB7A5E1-45D5-4AC0-9EF8-FF2D915B7E8F', 0);

Notes

• The order field in the action table determeine the order in which each action is perfmored.
• The Keys are hardcoded in the examples but they can be generated by either using NEWGUID() in MS Sql Server or gen_random_uuid() for Postgres (v13+).