rembSend SMS Hook
Use your own SMS service to send authentication messages.
The Send SMS Hook replaces Supabase's built-in SMS sending. You can use this hook to:
- Use a regional SMS Provider
- Use alternate messaging channels such as WhatsApp
- Fall back to another provider if your primary one fails
- Adjust the message body to include platform specific fields such as the
AppHash
Inputs
Field
Type
Description
user
The user attempting to sign in.
sms
object
Metadata specific to the SMS sending process. Includes the OTP.
1{2 "user": {3 "id": "6481a5c1-3d37-4a56-9f6a-bee08c554965",4 "aud": "authenticated",5 "role": "authenticated",6 "email": "",7 "phone": "+1333363128",8 "phone_confirmed_at": "2024-05-13T11:52:48.157306Z",9 "confirmation_sent_at": "2024-05-14T12:31:52.824573Z",10 "confirmed_at": "2024-05-13T11:52:48.157306Z",11 "phone_change_sent_at": "2024-05-13T11:47:02.183064Z",12 "last_sign_in_at": "2024-05-13T11:52:48.162518Z",13 "app_metadata": {14 "provider": "phone",15 "providers": ["phone"]16 },17 "user_metadata": {},18 "identities": [19 {20 "identity_id": "3be5e552-65aa-41d9-9db9-2a502f845459",21 "id": "6481a5c1-3d37-4a56-9f6a-bee08c554965",22 "user_id": "6481a5c1-3d37-4a56-9f6a-bee08c554965",23 "identity_data": {24 "email_verified": false,25 "phone": "+1612341244428",26 "phone_verified": true,27 "sub": "6481a5c1-3d37-4a56-9f6a-bee08c554965"28 },29 "provider": "phone",30 "last_sign_in_at": "2024-05-13T11:52:48.155562Z",31 "created_at": "2024-05-13T11:52:48.155599Z",32 "updated_at": "2024-05-13T11:52:48.159391Z"33 }34 ],35 "created_at": "2024-05-13T11:45:33.7738Z",36 "updated_at": "2024-05-14T12:31:52.82475Z",37 "is_anonymous": false38 },39 "sms": {40 "otp": "561166"41 }42}
Outputs
- No outputs are required. An empty response with a status code of 200 is taken as a successful response.
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
1create 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);
Create the hook:
1create or replace function send_sms(event jsonb) returns void as $$2declare3 job_data jsonb;4 scheduled_time timestamp;5 priority int;6begin7 -- extract phone and otp from the event json8 job_data := jsonb_build_object(9 'phone', event->'user'->>'phone',10 'otp', event->'sms'->>'otp'11 );1213 -- calculate the nearest 5-minute window for scheduled_time14 scheduled_time := date_trunc('minute', now()) + interval '5 minute' * floor(extract('epoch' from (now() - date_trunc('minute', now())) / 60) / 5);1516 -- assign priority dynamically (example logic: higher priority for earlier scheduled time)17 priority := extract('epoch' from (scheduled_time - now()))::int;1819 -- insert the job into the job_queue table20 insert into job_queue (job_data, priority, scheduled_at, max_retries)21 values (job_data, priority, scheduled_time, 2);22end;23$$ language plpgsql;2425grant all26 on table public.job_queue27 to supabase_auth_admin;2829revoke all30 on table public.job_queue31 from authenticated, anon;
Create a function to periodically run and dequeue all jobs
1create or replace function dequeue_and_run_jobs() returns void as $$2declare3 job record;4begin5 for job in6 select * from job_queue7 where status = 'pending'8 and scheduled_at <= now()9 order by priority desc, created_at10 for update skip locked11 loop12 begin13 -- add job processing logic here.14 -- for demonstration, we'll just update the job status to 'completed'.15 update job_queue16 set status = 'completed'17 where job_id = job.job_id;1819 exception when others then20 -- handle job failure and retry logic21 if job.retry_count < job.max_retries then22 update job_queue23 set retry_count = retry_count + 1,24 scheduled_at = now() + interval '1 minute' -- delay retry by 1 minute25 where job_id = job.job_id;26 else27 update job_queue28 set status = 'failed'29 where job_id = job.job_id;30 end if;31 end;32 end loop;33end;34$$ language plpgsql;3536grant execute37 on function public.dequeue_and_run_jobs38 to supabase_auth_admin;3940revoke execute41 on function public.dequeue_and_run_jobs42 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
1select2 cron.schedule(3 '* * * * *', -- this cron expression means every minute.4 'select dequeue_and_run_jobs();'5 );