Cron Jobs

In this section we will learn how to configure Supaplate, Supabase Queues and Supabase Cron to schedule and process scheduled tasks.

A scheduled task could be sending a newsletter every week, or sending an email to a user if they abandon their cart in your e-commerce application.

To give you a starting point, Supaplate comes with a ./app/features/cron/api/mailer.ts file that sends a welcome email to a user when they sign up using Supabase Queues and Supabase Cron.

The difference between Queues and Cron Jobs

Supabase Queues and Supabase Cron are two different features, but they are closely related.

In the context of backend development, queues are a data structure that allows you to store tasks that need to be processed asynchronously, that means, tasks that don't need to be processed immediately.

Supabase Queues is a feature that allows you to have a Queue inside of your PostgresQL database.

Supabase Cron is a feature that allows you to schedule tasks to run at specified intervals, you can choose to run the task every minute, hour, day, week, month or year.

A task, in the context of Supabase Cron, can be hitting a URL, running a function or executing a SQL query.

How Queues and Cron Jobs work together

In the case of Supaplate, we will use Supabase Queues to create a 'mailer' queue, there we will put all the emails that need to be sent.

Then we will use Supabase Cron to create a cron job that will run every minute (or the interval we want) and hit an API route that will take the emails from the queue and send them.

+------------------------+            +--------------------------+
|                        |            |                          |
|     App / Supaplate    |  ─────▶    |    Supabase Queue:       |
|                        |   Enqueue  |      'mailer'            |
+------------------------+            |  (emails to be sent)     |
                                      |                          |
                                      +--------------------------+
 
 
+-------------------------------+
|                               |
|  Supabase Cron Job (every 1m) |
|    ↳ Calls API route          |
|                               |
+-------------------------------+
               │
               ▼
+------------------------------+
|                              |
|  API Route: /api/cron/mailer |
|                              |
+------------------------------+
               │
               ▼
+-------------------------------+
|                               |
|   ↳ Fetch from 'mailer' queue |
|    ↳ Send email               |
|                               |
+-------------------------------+
 

Configuring the 'mailer' queue

Enable Queues and Cron Jobs

To enable Queues and Cron Jobs, head over to the Queue integration page and click on the Enable button.

Then go to the Cron integration page and click on the Enable button if it is not already enabled.

Create the 'mailer' queue

Head over to the Queue integration page and click on the Create Queue button.

Name the queue mailer, select 'Basic Queue', check the 'Enable Row Level Security' checkbox and click on the Create Queue button.

Create the 'mailer' cron job

Before creating the cron job, we need to expose the API route to the internet.

You can either write down the URL of your website (if you have deployed it) or you can use a quick tunnel to expose your local server to the internet.

Once you have the URL, head over to the Cron integration page and click on the Create Job button.

Follow the steps below to create the cron job:

1. Name the job mailer
2. Enter */30 * * * * * in the 'Schedule' field (that means every 30 seconds)
3. Select 'HTTP Request' in the 'Type'
4. Select 'POST' in the 'Method' field
5. Enter the URL in the 'URL' field
6. Click on 'Add a new header'
7. The header name should be 'Authorization' and the value should be a random string of characters. This value should also be saved in the .env file as CRON_SECRET
8. Click on the Create Job button

Adding tasks to the 'mailer' queue

Since Supabase Queues is powered by a PostgresQL extension, we need to use SQL to add tasks to the queue.

We will create a tigger and a function to add a task to the mailer queue when a user signs up.

-- ./sql/functions/welcome_email.sql
CREATE OR REPLACE FUNCTION welcome_email()
RETURNS TRIGGER
LANGUAGE PLPGSQL
SECURITY DEFINER
SET SEARCH_PATH = ''
AS $$
BEGIN
    PERFORM pgmq.send(
            queue_name => 'mailer'::text,
            msg => (json_build_object(
                'template', 'welcome'::text,
                'to', new.raw_user_meta_data ->> 'email',
                'data', row_to_json(new.*)
            ))::jsonb
        );
    RETURN NEW;
END;
$$;

CREATE TRIGGER welcome_email
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION welcome_email();

{
"template":"welcome",
"to":"john.doe@example.com",
"data": { "name":"John Doe" }
}

Consuming tasks from the 'mailer' queue

By this point the cron job will be hitting the /api/cron/mailer route every 30 seconds.

The ./app/features/cron/api/mailer.ts file exports a 'action' function that looks like this:

// ./app/features/cron/api/mailer.ts

// Import the WelcomeEmail component
import WelcomeEmail from 'transactional-emails/emails/welcome'

export async function action({ request }: Route.LoaderArgs) {
  // Check if the request method is POST
  // and the Authorization header is the same as the CRON_SECRET
  if (
    request.method !== 'POST' ||
    request.headers.get('Authorization') !== process.env.CRON_SECRET
  ) {
    return data(null, { status: 401 })
  }

  // Get the message from the 'mailer' queue with the adminClient
  const { data: message, error } = await adminClient
    .schema('pgmq_public')
    .rpc('pop', {
      queue_name: 'mailer',
    })

  /*
    Extract the message data (to, data, template) we added before when we did:

    pgmq.send(
        queue_name => 'mailer'::text,
        msg => (json_build_object(
            'template', 'welcome'::text,
            'to', new.raw_user_meta_data ->> 'email',
            'data', row_to_json(new.*)
        ))::jsonb
    );

    */
  const {
    message: { to, data: emailData, template },
  } = message

  // If the template is 'welcome', send the email
  if (template === 'welcome') {
    const { error } = await resendClient.emails.send({
      from: 'Supaplate <hello@supaplate.com>',
      to: [to],
      subject: 'Welcome to Supaplate!',
      react: WelcomeEmail({ profile: JSON.stringify(emailData, null, 2) }),
    })
  }
  // Here you can add more logic to handle other templates
  // ...

  // Return a 200 status code
  return data(null, { status: 200 })
}

Notes

• To get messages from the mailer queue, we use the pop rpc function.
• The pop function returns a message and removes it from the queue.
• To pop a message from the queue we use the adminClient.

Conclusion

This is a pretty solid set up to send emails in the background or any other task that needs to be run in the background, you can extend this to send other types of emails, or create other queues and cron jobs to handle other types of tasks (deleting ghost users, calculating statistics, etc).

The flow will be the same:

1. Create a queue
2. Create a cron job that will hit a route every X interval.
3. Create a route that will take the tasks from the queue and process them.
4. Create a function that will add the tasks to the queue when the event happens.