Send SMS Hook

1
create table job_queue (
2
  job_id uuid primary key default gen_random_uuid(),
3
  job_data jsonb not null,
4
  created_at timestamp default now(),
5
  status text default 'pending',
6
  priority int default 0,
7
  retry_count int default 0,
8
  max_retries int default 2,
9
  scheduled_at timestamp default now()
10
);

1
create or replace function send_sms(event jsonb) returns void as $$
2
declare
3
    job_data jsonb;
4
    scheduled_time timestamp;
5
    priority int;
6
begin
7
    -- extract phone and otp from the event json
8
    job_data := jsonb_build_object(
9
        'phone', event->'user'->>'phone',
10
        'otp', event->'sms'->>'otp'
11
    );
12
13
    -- calculate the nearest 5-minute window for scheduled_time
14
    scheduled_time := date_trunc('minute', now()) + interval '5 minute' * floor(extract('epoch' from (now() - date_trunc('minute', now())) / 60) / 5);
15
16
    -- assign priority dynamically (example logic: higher priority for earlier scheduled time)
17
    priority := extract('epoch' from (scheduled_time - now()))::int;
18
19
    -- insert the job into the job_queue table
20
    insert into job_queue (job_data, priority, scheduled_at, max_retries)
21
    values (job_data, priority, scheduled_time, 2);
22
end;
23
$$ language plpgsql;
24
25
grant all
26
  on table public.job_queue
27
  to supabase_auth_admin;
28
29
revoke all
30
  on table public.job_queue
31
  from authenticated, anon;

1
create or replace function dequeue_and_run_jobs() returns void as $$
2
declare
3
    job record;
4
begin
5
    for job in
6
        select * from job_queue
7
        where status = 'pending'
8
          and scheduled_at <= now()
9
        order by priority desc, created_at
10
        for update skip locked
11
    loop
12
        begin
13
            -- add job processing logic here.
14
            -- for demonstration, we'll just update the job status to 'completed'.
15
            update job_queue
16
            set status = 'completed'
17
            where job_id = job.job_id;
18
19
        exception when others then
20
            -- handle job failure and retry logic
21
            if job.retry_count < job.max_retries then
22
                update job_queue
23
                set retry_count = retry_count + 1,
24
                    scheduled_at = now() + interval '1 minute'  -- delay retry by 1 minute
25
                where job_id = job.job_id;
26
            else
27
                update job_queue
28
                set status = 'failed'
29
                where job_id = job.job_id;
30
            end if;
31
        end;
32
    end loop;
33
end;
34
$$ language plpgsql;
35
36
grant execute
37
  on function public.dequeue_and_run_jobs
38
  to supabase_auth_admin;
39
40
revoke execute
41
  on function public.dequeue_and_run_jobs
42
  from authenticated, anon;

1
select
2
  cron.schedule(
3
    '* * * * *', -- this cron expression means every minute.
4
    'select dequeue_and_run_jobs();'
5
  );

1
import { Webhook } from 'https://esm.sh/standardwebhooks@1.0.0'
2
import { readAll } from 'https://deno.land/std/io/read_all.ts'
3
import { Twilio } from 'https://cdn.skypack.dev/twilio'
4
import * as base64 from 'https://denopkg.com/chiefbiiko/base64/mod.ts'
5
6
const accountSid: string | undefined = Deno.env.get('TWILIO_ACCOUNT_SID')
7
const authToken: string | undefined = Deno.env.get('TWILIO_AUTH_TOKEN')
8
const fromNumber: string = Deno.env.get('TWILIO_PHONE_NUMBER')
9
10
const sendTextMessage = async (
11
  messageBody: string,
12
  accountSid: string | undefined,
13
  authToken: string | undefined,
14
  fromNumber: string,
15
  toNumber: string
16
): Promise<any> => {
17
  if (!accountSid || !authToken) {
18
    console.log('Your Twilio account credentials are missing. Please add them.')
19
    return
20
  }
21
  const url: string = `https://api.twilio.com/2010-04-01/Accounts/${accountSid}/Messages.json`
22
23
  const encodedCredentials: string = base64.fromUint8Array(
24
    new TextEncoder().encode(`${accountSid}:${authToken}`)
25
  )
26
27
  const body: URLSearchParams = new URLSearchParams({
28
    To: `+${toNumber}`,
29
    From: fromNumber,
30
    // Uncomment when testing with a fixed number
31
    Body: messageBody,
32
  })
33
34
  const response = await fetch(url, {
35
    method: 'POST',
36
    headers: {
37
      'Content-Type': 'application/x-www-form-urlencoded',
38
      Authorization: `Basic ${encodedCredentials}`,
39
    },
40
    body,
41
  })
42
43
  return response.json()
44
}
45
46
Deno.serve(async (req) => {
47
  const payload = await req.text()
48
  const base64_secret = Deno.env.get('SEND_SMS_HOOK_SECRET').replace('v1,whsec_', '')
49
  const headers = Object.fromEntries(req.headers)
50
  const wh = new Webhook(base64_secret)
51
  try {
52
    const { user, sms } = wh.verify(payload, headers)
53
    const messageBody = `Your OTP is: ${sms.otp}`
54
    const response = await sendTextMessage(
55
      messageBody,
56
      accountSid,
57
      authToken,
58
      fromNumber,
59
      user.phone
60
    )
61
    if (response.status !== 'queued') {
62
      return new Response(
63
        JSON.stringify({
64
          error: {
65
            http_code: response.code,
66
            message: `Failed to send SMS: ${response.message}. More info: ${response.more_info}`,
67
          },
68
        }),
69
        {
70
          status: response.status,
71
          headers: {
72
            'Content-Type': 'application/json',
73
          },
74
        }
75
      )
76
    }
77
    return new Response(
78
      JSON.stringify({}),
79
      {
80
        status: 200,
81
        headers: {
82
          'Content-Type': 'application/json',
83
        },
84
      }
85
    )
86
  } catch (error) {
87
    return new Response(
88
      JSON.stringify({
89
        error: {
90
          http_code: 500,
91
          message: `Failed to send sms: ${JSON.stringify(error)}`,
92
        }
93
      }),
94
      {
95
        status: 500,
96
        headers: {
97
          'Content-Type': 'application/json',
98
        },
99
      }
100
    )
101
  }
102
})

1
import { Webhook } from "https://esm.sh/standardwebhooks@1.0.0";
2
import { readAll } from "https://deno.land/std/io/read_all.ts";
3
import * as base64 from "https://denopkg.com/chiefbiiko/base64/mod.ts";
4
5
const accountSid: string | undefined = Deno.env.get("TWILIO_ACCOUNT_SID");
6
const authToken: string | undefined = Deno.env.get("TWILIO_AUTH_TOKEN");
7
const fromNumber: string = Deno.env.get("TWILIO_WHATSAPP_NUMBER");
8
const smsFromNumber: string = Deno.env.get("TWILIO_SMS_NUMBER");
9
10
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'];
11
12
const sendMessage = async (
13
    messageBody: string,
14
    accountSid: string | undefined,
15
    authToken: string | undefined,
16
    fromNumber: string,
17
    toNumber: string,
18
    useWhatsApp: boolean,
19
): Promise < any > => {
20
    if (!accountSid || !authToken) {
21
        console.log("Your Twilio account credentials are missing. Please add them.");
22
        return;
23
    }
24
    const url: string = `https://api.twilio.com/2010-04-01/Accounts/${accountSid}/Messages.json`;
25
26
    const encodedCredentials: string = base64.fromUint8Array(
27
        new TextEncoder().encode(`${accountSid}:${authToken}`),
28
    );
29
30
    const body: URLSearchParams = new URLSearchParams({
31
        To: useWhatsApp ? `whatsapp:${toNumber}` : toNumber,
32
        From: useWhatsApp ? `whatsapp:${fromNumber}` : smsFromNumber,
33
        Body: messageBody,
34
    });
35
36
    const response = await fetch(url, {
37
        method: "POST",
38
        headers: {
39
            "Content-Type": "application/x-www-form-urlencoded",
40
            "Authorization": `Basic ${encodedCredentials}`,
41
        },
42
        body,
43
    });
44
45
    return response.json();
46
};
47
48
Deno.serve(async (req) => {
49
    const payload = await req.text();
50
    const base64_secret = Deno.env.get("SEND_SMS_HOOK_SECRET").replace('v1,whsec_', '');
51
    const headers = Object.fromEntries(req.headers);
52
    const wh = new Webhook(base64_secret);
53
    try {
54
        const {
55
            user,
56
            sms
57
        } = wh.verify(payload, headers);
58
        const messageBody = `Your OTP is: ${sms.otp}`;
59
        const userPhoneNumber = user.phone;
60
        const countryCode = userPhoneNumber.substring(1, userPhoneNumber.indexOf(userPhoneNumber.match(/\d/)!));
61
62
        const useWhatsApp = latinAmericanCountryCodes.includes(countryCode);
63
64
        const response = await sendMessage(
65
            messageBody,
66
            accountSid,
67
            authToken,
68
            fromNumber,
69
            userPhoneNumber,
70
            useWhatsApp,
71
        );
72
73
        if (response.status !== "queued") {
74
            return new Response(
75
                JSON.stringify({
76
                    error: `Failed to send message, Error Code: ${response.code} ${response.message} ${response.more_info}`,
77
                }), {
78
                    status: response.status,
79
                    headers: {
80
                        "Content-Type": "application/json",
81
                    },
82
                },
83
            );
84
        }
85
        return new Response(
86
            JSON.stringify({
87
                message: "Message sent successfully."
88
            }), {
89
                headers: {
90
                    "Content-Type": "application/json",
91
                },
92
            },
93
        );
94
    } catch (error) {
95
        return new Response(
96
            JSON.stringify({
97
                error: `Failed to process the request: ${error}`
98
            }), {
99
                status: 500,
100
                headers: {
101
                    "Content-Type": "application/json",
102
                },
103
            },
104
        );
105
    }
106
});