Background Jobs

In modern web applications, performing tasks asynchronously in the background is essential for maintaining security, responsiveness, and scalability. Whether it's performing complex computations, sending emails, or doing any sensitive operation like processing payments, background jobs allow applications to offload these tasks from the client to a server, ensuring a smooth user experience.

With ElectricSQL, state transfer is abstracted away by syncing the application's local database, but background jobs can still be managed through an event-sourcing pattern. The client can schedule a job as an entry to a local table, which the server will eventually pick up through a trigger on the backend database and perform the necessary processing, optionally writing results to the same and/or other tables that will be synced back to the client.

This recipe shows how to implement a simple background job system using ElectricSQL, and highlights the advantages of using an event-sourcing pattern such as offline resilience and having a complete audit log of all jobs submitted with no additional effort.

• Schema
• Data Access
• Usage

Schema

Adapt the schema and DDLX commands below to match your specific use-case.

-- Create a background jobs table.
CREATE TABLE IF NOT EXISTS background_jobs (
  id UUID PRIMARY KEY NOT NULL,
  -- Can have foreign key to the creator of the background job
  -- submitted_by UUID REFERENCES users(id)
  timestamp TIMESTAMPTZ NOT NULL,
  payload JSONB NOT NULL, -- job specification
  completed BOOLEAN NOT NULL,
  cancelled BOOLEAN NOT NULL,
  progress REAL NOT NULL,
  result JSONB
);

-- ⚡ Electrify the table
ALTER TABLE background_jobs ENABLE ELECTRIC;


/* Set up a trigger that will notify the appropriate service and perform
 * the work required to process the submitted job. The service should then
 * update the table accordingly with the progress and finally the result
 */

-- When a job is submitted, notify the appropriate service to process it
CREATE OR REPLACE FUNCTION process_job()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.completed = false AND NEW.cancelled = false THEN
    PERFORM pg_notify('process_background_job', row_to_json(NEW)::TEXT);
  END IF;
  RETURN NULL;
END
$$ LANGUAGE plpgsql;


-- Create a trigger to execute the function on INSERT into "background_jobs" table
CREATE TRIGGER "process_job_trigger"
AFTER INSERT ON background_jobs
FOR EACH ROW
EXECUTE FUNCTION process_job();

-- Enable the triggers on the tables
ALTER TABLE background_jobs ENABLE ALWAYS TRIGGER process_job_trigger;

Data Access

Adapt the headless components below and enhance them with additional features.

import { useCallback } from 'react'
import { useElectric } from '../electric/ElectricWrapper'
import { useLiveQuery } from 'electric-sql/react'
import { genUUID } from 'electric-sql/util'

export const useBackgroundJobs = ({ maxNumJobs }: { maxNumJobs?: number }) => {
  const { db } = useElectric()!

  const { results: jobs = [] } = useLiveQuery(
    db.background_jobs.liveMany({
      orderBy: { timestamp: 'desc' },
      take: maxNumJobs,
    }),
  )

  const onSubmitJob = useCallback(
    (payload: object = {}) =>
      db.background_jobs.create({
        data: {
          id: genUUID(),
          payload: payload,
          timestamp: new Date(),
          cancelled: false,
          completed: false,
          progress: 0,
        },
      }),
    [db.background_jobs],
  )

  const onCancelJob = useCallback(
    (jobId: string) =>
      db.background_jobs.update({
        data: { cancelled: true },
        where: { id: jobId },
      }),
    [db.background_jobs],
  )

  return {
    jobs,
    onSubmitJob,
    onCancelJob,
  }
}

Usage

Connect the schema and headless components with your UI library of choice to get a working component.

import { BackgroundJobsView } from './BackgroundJobsView'
import { useBackgroundJobs } from './use_background_jobs'

export const BackgroundJobs = ({ numJobsToShow = 10 }: { numJobsToShow?: number }) => {
  const { jobs, onSubmitJob, onCancelJob } = useBackgroundJobs({ maxNumJobs: numJobsToShow })
  return <BackgroundJobsView jobs={jobs} onSubmitJob={onSubmitJob} onCancelJob={onCancelJob} />
}