A job scheduler in two tables - recurring tasks inside a Uniface app

The customer app from the series Building a customer app in Uniface 10 had accumulated several operations that someone has to start regularly: the subscription billing run, the dunning run, closing solved tickets after two weeks, the backup, capturing price changes. Each of them worked; none of them ran on its own.

The last stage added a small scheduler. It does not replace the Windows Task Scheduler - something still has to start it - but it moves the knowledge of what is due and what happened into the database, where the app can show it.

Tested with Rocket Uniface 10.4.03 Community Edition and SQLite 3.50.2, October 2026.

Two tables

CREATE TABLE JOB_DEF (
  JOB_CODE      VARCHAR(20) NOT NULL PRIMARY KEY,
  JOB_NAME      VARCHAR(60) NOT NULL,
  JOB_TYPE      VARCHAR(20) NOT NULL,   -- SUBSCRIPTIONS, DUNNING, TICKET_CLOSE, BACKUP, PRICE_CAPTURE
  INTERVAL_DAYS INTEGER NOT NULL,
  NEXT_RUN      DATE NOT NULL,
  IS_ACTIVE     INTEGER NOT NULL,
  CHANGED_AT    DATETIME NOT NULL,
  CHANGED_BY    VARCHAR(30)
);

CREATE TABLE JOB_RUN (
  RUN_ID      INTEGER NOT NULL PRIMARY KEY,
  JOB_CODE    VARCHAR(20) NOT NULL,
  STARTED_AT  DATETIME NOT NULL,
  FINISHED_AT DATETIME,
  STATUS      VARCHAR(10) NOT NULL,     -- RUNNING, OK, ERROR
  MESSAGE     VARCHAR(500),
  RUN_BY      VARCHAR(30)
);
CREATE INDEX IX_JOB_RUN_JOB ON JOB_RUN (JOB_CODE, STARTED_AT);

The schedule is deliberately simple: an interval in days and the date of the next run. No cron expressions, no "first Monday of the month". Everything in this app runs daily, weekly or monthly-ish, and a day interval covers that well enough. A job type is a fixed list; it maps to code, not to a configurable command line.

Defining a job

SAVE_JOB creates or updates a job. Everything is validated before it is stored:

• the code has 1 to 20 characters from A-Z, 0-9 and _,
• the name 1 to 60 characters,
• the type is one of the five known types,
• the interval is 1 to 365 days,
• active is 0 or 1,
• the next run is a valid date (empty means today).

The type check uses a little trick for "is this word in the list":

    if ($scan(",SUBSCRIPTIONS,DUNNING,TICKET_CLOSE,BACKUP,PRICE_CAPTURE,", ",%%(pType),") = 0 | pType = "")
        pError = "Unknown job type '%%(pType)'. Allowed: SUBSCRIPTIONS, DUNNING, TICKET_CLOSE, BACKUP, PRICE_CAPTURE."
        return -1
    endif

The commas on both sides prevent partial matches - CLOSE alone is not found, because ,CLOSE, doesn't occur in the list.

Running what is due

RUN_DUE takes a date (normally today), selects all active jobs whose next run is on or before it, and runs them one after another:

    sql/data "SELECT JOB_CODE, JOB_TYPE, INTERVAL_DAYS, NEXT_RUN FROM JOB_DEF WHERE IS_ACTIVE = 1 AND NEXT_RUN <= '%%(vDay)' ORDER BY NEXT_RUN, JOB_CODE", "CUSTOMERS"
    ...
    forlist vRow in vJobs
        ...
        call NEXT_ID("JOB_RUN", "RUN_ID", vRunId)
        sql "INSERT INTO JOB_RUN (RUN_ID, JOB_CODE, STARTED_AT, STATUS, RUN_BY) VALUES (%%(vRunId), '%%(vCode)', datetime('now', 'localtime'), 'RUNNING', %%(vUserLit))", "CUSTOMERS"
        call RUN_ONE(vType, vDay, vMsg, vErr)
        vRc = $status
        if (vRc < 0)
            vStatus = "ERROR"
            vMsg = vErr
            pErrors = pErrors + 1
        else
            vStatus = "OK"
        endif
        ...
        sql "UPDATE JOB_RUN SET FINISHED_AT = datetime('now', 'localtime'), STATUS = '%%(vStatus)', MESSAGE = %%(vMsgLit) WHERE RUN_ID = %%(vRunId)", "CUSTOMERS"

Three decisions are in these lines:

The log row is written before the job runs. If the job crashes the process, the log shows a run with status RUNNING and no end time. That is much more useful than no row at all.

One failing job does not stop the others. The error goes into the log and into an error counter, and the loop continues. The dunning run should not be skipped because the backup folder was full.

Every job type calls an existing operation. The scheduler contains no business logic of its own:

    if (pType = "SUBSCRIPTIONS")
        call GET_TEXT("SELECT strftime('%d.%m.%Y', '%%(pDay)')", vDate)
        activate "BILLING_SVC".BILLING_RUN(vDate, 0, vRunId, vData, vCount, pError)
        ...
        pMessage = "%%(vCount) invoice drafts in billing run %%(vRunId)"
    elseif (pType = "DUNNING")
        activate "DUNNING_SVC".DUNNING_RUN(0, 0, vData, vCount, pError)
        ...
    elseif (pType = "TICKET_CLOSE")
        call SETTING_NUM("TICKET_CLOSE_DAYS", "14", vDays)
        activate "TICKET_SVC".AUTO_CLOSE(vDays, 0, vData, vCount, pError)
        ...
    elseif (pType = "BACKUP")
        activate "BACKUP2_SVC".SAFE_BACKUP(vFile, vMethod, vCheck, pError)
        ...
    elseif (pType = "PRICE_CAPTURE")
        activate "PRICEHIST_SVC".CAPTURE(vCount, pError)
        ...

The billing run gets the job date in the display format. Its date parameter accepts ISO dates as well, so the conversion is only defensive. The message is short and human-readable: "1 tickets closed", "3 invoice drafts in billing run 12".

Moving the next run

After a run, NEXT_RUN moves forward by the interval. The interesting case is a job that hasn't run for a while - the PC was off for a week, and a daily job is seven days overdue. There are two options:

1. run it seven times to catch up, or
2. run it once and move the next run past today.

For every job in this app, option 2 is right. Each of the called operations already processes everything that is due in one call - the billing run all due periods, the ticket closer all old solved tickets, the dunning run all overdue invoices with its own minimum interval between reminder levels. Replaying them would add nothing but log rows. So the next run moves by as many intervals as needed to land after the run date:

        call COUNT_SQL("SELECT CAST((julianday('%%(vDay)') - julianday('%%(vNext)')) / %%(vInterval) AS INTEGER) + 1 FROM (SELECT 1 AS X)", vSteps)
        vDays = vSteps * vInterval
        call GET_TEXT("SELECT date('%%(vNext)', '+%%(vDays) days')", vNewNext)
        sql "UPDATE JOB_DEF SET NEXT_RUN = '%%(vNewNext)' WHERE JOB_CODE = '%%(vCode)'", "CUSTOMERS"

Example: a weekly job with next run 10 days ago. 10 / 7 is 1.43, truncated to 1, plus 1 is 2 steps, so the next run is the old date plus 14 days - four days from now. The rhythm stays on the original weekday instead of drifting to "seven days after whenever it last ran". A daily job due today moves to tomorrow.

The next run also moves after an error. A job that fails every time would otherwise run again on every call of RUN_DUE; this way the error shows up once per interval in the log, and a system check reports jobs whose last run failed.

The job that can't be tested like the others

All test services in this app run inside one transaction that is rolled back at the end, so they never leave data behind. The backup job breaks that: before the backup copies the database file, it commits all pending changes so the copy is complete.

public operation SAFE_BACKUP
    ...
    commit

Running it inside a test would commit everything the test had written so far. So the test creates a BACKUP job, checks that it is stored and listed - and creates it inactive, so RUN_DUE doesn't pick it up.

The scheduler test covers the rest:

• four jobs: one overdue and active, one due today, one inactive, one in the future,
• invalid code, unknown type and interval 0 are rejected,
• saving an existing code updates it instead of creating a second job,
• RUN_DUE runs exactly the two due active jobs: a solved ticket from 30 days ago is closed, a price snapshot is written,
• the next runs are today + 4 days (weekly, 10 days overdue) and tomorrow (daily, due today),
• the log has one OK row per job with a readable message.

What still starts the scheduler

The scheduler decides what runs; it doesn't run by itself. Something has to call RUN_DUE. In this app that part doesn't exist yet - the operation is there and tested, but no form or startup command calls it. The obvious options are a small batch start of the app that calls RUN_DUE and exits, triggered by the Windows Task Scheduler, or a button in the admin menu. The job log would be the same either way.

Takeaways

Keep schedules simple: an interval in days and a next-run date cover most business jobs.

Write the log row before the job runs, and finish it afterwards.

Let one failing job not stop the others.

Skip missed runs instead of replaying them, unless the job really needs every single run - and move the date even after an error.

Know which jobs commit, and keep them out of rolled-back test runs.