Implement a task scheduler

[loicknuchel]

Gospeak should be able to execute scheduled tasks:

• once at a fixed time (ex: publish a tweet on 24th April at 12:00)
• periodically (ex: send a newsletter every day at 10:00)

They should be defined by the user (speaker, group orga or admin) depending on the task.

Use cases:

• send an email with a tech video every day to user who subscribed
• send a tweet with @gospeak_io one week before a CFP closes
• send a tweet with group account one day before an event starts (using a template)
• send an email to event attendees one day before it starts

Technical solution 1

• Create a tasks table containing planned tasks (execution time, task, arguments and status).
• Every minute a batch will look for scheduled tasks to execute
• Every hour a batch will look for periodical actions and add incoming scheduled tasks

CREATE TABLE tasks
(
    id           CHAR(36)      NOT NULL PRIMARY KEY, -- uuid of the task
    status       VARCHAR(10)   NOT NULL,             -- enum: scheduled, executing, success, error
    task         VARCHAR(120)  NOT NULL,             -- enum, task payload ref
    arguments    VARCHAR(1024) NOT NULL,             -- Seq[String], task arguments
    result       VARCHAR(1024),                      -- text returned by the task, to display in UI
    error        VARCHAR(1024),                      -- error text when the task fail
    created_at   TIMESTAMP     NOT NULL,             -- when the task is created
    scheduled_at TIMESTAMP     NOT NULL,             -- when the task should be executed
    launched_at  TIMESTAMP,                          -- when the task is launched (should be just after scheduled_at most of the time)
    finished_at  TIMESTAMP                           -- when the task finished (fill result and error in the same time of needed)
);

Technical solution 2

Any other idea ?