Send SMS Hook

Your company uses a worker to manage all messaging related jobs. For performance reasons, the messaging system sends messages in intervals via a job queue. Instead of sending a message immediately, messages are queued and sent in periodic intervals via pg_cron.

Create a table to store jobs

create table job_queue (
  job_id uuid primary key default gen_random_uuid(),
  job_data jsonb not null,
  created_at timestamp default now(),
  status text default 'pending',
  priority int default 0,
  retry_count int default 0,
  max_retries int default 2,
  scheduled_at timestamp default now()
);

Create the hook:

create or replace function send_sms(event jsonb) returns void as $$
declare
    job_data jsonb;
    scheduled_time timestamp;
    priority int;
begin
    -- extract phone and otp from the event json
    job_data := jsonb_build_object(
        'phone', event->'user'->>'phone',
        'otp', event->'sms'->>'otp'
    );

    -- calculate the nearest 5-minute window for scheduled_time
    scheduled_time := date_trunc('minute', now()) + interval '5 minute' * floor(extract('epoch' from (now() - date_trunc('minute', now())) / 60) / 5);

    -- assign priority dynamically (example logic: higher priority for earlier scheduled time)
    priority := extract('epoch' from (scheduled_time - now()))::int;

    -- insert the job into the job_queue table
    insert into job_queue (job_data, priority, scheduled_at, max_retries)
    values (job_data, priority, scheduled_time, 2);
end;
$$ language plpgsql;

grant all
  on table public.job_queue
  to supabase_auth_admin;

revoke all
  on table public.job_queue
  from authenticated, anon;

Create a function to periodically run and dequeue all jobs

create or replace function dequeue_and_run_jobs() returns void as $$
declare
    job record;
begin
    for job in
        select * from job_queue
        where status = 'pending'
          and scheduled_at <= now()
        order by priority desc, created_at
        for update skip locked
    loop
        begin
            -- add job processing logic here.
            -- for demonstration, we'll just update the job status to 'completed'.
            update job_queue
            set status = 'completed'
            where job_id = job.job_id;

        exception when others then
            -- handle job failure and retry logic
            if job.retry_count < job.max_retries then
                update job_queue
                set retry_count = retry_count + 1,
                    scheduled_at = now() + interval '1 minute'  -- delay retry by 1 minute
                where job_id = job.job_id;
            else
                update job_queue
                set status = 'failed'
                where job_id = job.job_id;
            end if;
        end;
    end loop;
end;
$$ language plpgsql;

grant execute
  on function public.dequeue_and_run_jobs
  to supabase_auth_admin;

revoke execute
  on function public.dequeue_and_run_jobs
  from authenticated, anon;

Configure pg_cron to run the job on an interval. You can use a tool like crontab.guru to check that your job is running on an appropriate schedule. Ensure that pg_cron is enabled under Database > Extensions

select
  cron.schedule(
    '* * * * *', -- this cron expression means every minute.
    'select dequeue_and_run_jobs();'
  );

Your company would like to use an alternate message provider. Some examples of alternate message providers include Msg91 for India and Africa's Talking. The example uses Twilio as it is widely available and does not require a regional number.

import { Webhook } from 'https://esm.sh/standardwebhooks@1.0.0'
import { readAll } from 'https://deno.land/std/io/read_all.ts'
import { Twilio } from 'https://cdn.skypack.dev/twilio'
import * as base64 from 'https://denopkg.com/chiefbiiko/base64/mod.ts'

const accountSid: string | undefined = Deno.env.get('TWILIO_ACCOUNT_SID')
const authToken: string | undefined = Deno.env.get('TWILIO_AUTH_TOKEN')
const fromNumber: string = Deno.env.get('TWILIO_PHONE_NUMBER')

const sendTextMessage = async (
  messageBody: string,
  accountSid: string | undefined,
  authToken: string | undefined,
  fromNumber: string,
  toNumber: string
): Promise<any> => {
  if (!accountSid || !authToken) {
    console.log('Your Twilio account credentials are missing. Please add them.')
    return
  }
  const url: string = `https://api.twilio.com/2010-04-01/Accounts/${accountSid}/Messages.json`

  const encodedCredentials: string = base64.fromUint8Array(
    new TextEncoder().encode(`${accountSid}:${authToken}`)
  )

  const body: URLSearchParams = new URLSearchParams({
    To: `+${toNumber}`,
    From: fromNumber,
    // Uncomment when testing with a fixed number
    Body: messageBody,
  })

  const response = await fetch(url, {
    method: 'POST',
    headers: {
      'Content-Type': 'application/x-www-form-urlencoded',
      Authorization: `Basic ${encodedCredentials}`,
    },
    body,
  })

  return response.json()
}

Deno.serve(async (req) => {
  const payload = await req.text()
  const base64_secret = Deno.env.get('SEND_SMS_HOOK_SECRET').replace('v1,whsec_', '')
  const headers = Object.fromEntries(req.headers)
  const wh = new Webhook(base64_secret)
  try {
    const { user, sms } = wh.verify(payload, headers)
    const messageBody = `Your OTP is: ${sms.otp}`
    const response = await sendTextMessage(
      messageBody,
      accountSid,
      authToken,
      fromNumber,
      user.phone
    )
    if (response.status !== 'queued') {
      return new Response(
        JSON.stringify({
          error: {
            http_code: response.code,
            message: `Failed to send SMS: ${response.message}. More info: ${response.more_info}`,
          },
        }),
        {
          status: response.status,
          headers: {
            'Content-Type': 'application/json',
          },
        }
      )
    }
    return new Response(
      JSON.stringify({}),
      {
        status: 200,
        headers: {
          'Content-Type': 'application/json',
        },
      }
    )
  } catch (error) {
    return new Response(
      JSON.stringify({
        error: {
          http_code: 500,
          message: `Failed to send sms: ${JSON.stringify(error)}`,
        }
      }),
      {
        status: 500,
        headers: {
          'Content-Type': 'application/json',
        },
      }
    )
  }
})

Your company is expanding into Latin America and would like to use WhatsApp for higher deliverability. Write a hook to send WhatsApp messages to requests from the continent and SMS messages to all other numbers.

import { Webhook } from "https://esm.sh/standardwebhooks@1.0.0";
import { readAll } from "https://deno.land/std/io/read_all.ts";
import * as base64 from "https://denopkg.com/chiefbiiko/base64/mod.ts";

const accountSid: string | undefined = Deno.env.get("TWILIO_ACCOUNT_SID");
const authToken: string | undefined = Deno.env.get("TWILIO_AUTH_TOKEN");
const fromNumber: string = Deno.env.get("TWILIO_WHATSAPP_NUMBER");
const smsFromNumber: string = Deno.env.get("TWILIO_SMS_NUMBER");

const latinAmericanCountryCodes = ['54', '55', '56', '57', '58', '501', '502', '503', '504', '505', '506', '507', '508', '509', '51', '52', '53', '591', '592', '593', '594', '595', '596', '597', '598', '599'];

const sendMessage = async (
    messageBody: string,
    accountSid: string | undefined,
    authToken: string | undefined,
    fromNumber: string,
    toNumber: string,
    useWhatsApp: boolean,
): Promise < any > => {
    if (!accountSid || !authToken) {
        console.log("Your Twilio account credentials are missing. Please add them.");
        return;
    }
    const url: string = `https://api.twilio.com/2010-04-01/Accounts/${accountSid}/Messages.json`;

    const encodedCredentials: string = base64.fromUint8Array(
        new TextEncoder().encode(`${accountSid}:${authToken}`),
    );

    const body: URLSearchParams = new URLSearchParams({
        To: useWhatsApp ? `whatsapp:${toNumber}` : toNumber,
        From: useWhatsApp ? `whatsapp:${fromNumber}` : smsFromNumber,
        Body: messageBody,
    });

    const response = await fetch(url, {
        method: "POST",
        headers: {
            "Content-Type": "application/x-www-form-urlencoded",
            "Authorization": `Basic ${encodedCredentials}`,
        },
        body,
    });

    return response.json();
};

Deno.serve(async (req) => {
    const payload = await req.text();
    const base64_secret = Deno.env.get("SEND_SMS_HOOK_SECRET").replace('v1,whsec_', '');
    const headers = Object.fromEntries(req.headers);
    const wh = new Webhook(base64_secret);
    try {
        const {
            user,
            sms
        } = wh.verify(payload, headers);
        const messageBody = `Your OTP is: ${sms.otp}`;
        const userPhoneNumber = user.phone;
        const countryCode = userPhoneNumber.substring(1, userPhoneNumber.indexOf(userPhoneNumber.match(/\d/)!));

        const useWhatsApp = latinAmericanCountryCodes.includes(countryCode);

        const response = await sendMessage(
            messageBody,
            accountSid,
            authToken,
            fromNumber,
            userPhoneNumber,
            useWhatsApp,
        );

        if (response.status !== "queued") {
            return new Response(
                JSON.stringify({
                    error: `Failed to send message, Error Code: ${response.code} ${response.message} ${response.more_info}`,
                }), {
                    status: response.status,
                    headers: {
                        "Content-Type": "application/json",
                    },
                },
            );
        }
        return new Response(
            JSON.stringify({
                message: "Message sent successfully."
            }), {
                headers: {
                    "Content-Type": "application/json",
                },
            },
        );
    } catch (error) {
        return new Response(
            JSON.stringify({
                error: `Failed to process the request: ${error}`
            }), {
                status: 500,
                headers: {
                    "Content-Type": "application/json",
                },
            },
        );
    }
});