Auth

Send Email Hook

Use a custom email provider to send authentication messages

The Send Email Hook runs before an email is sent and allows for flexibility around email sending. You can use this hook to configure a back-up email provider or add internationalization to your emails.

Inputs

FieldTypeDescription
userUserThe user attempting to sign in.
emailobjectMetadata specific to the email sending process. Includes the OTP and token_hash.

_49
{
_49
"user": {
_49
"id": "8484b834-f29e-4af2-bf42-80644d154f76",
_49
"aud": "authenticated",
_49
"role": "authenticated",
_49
"email": "john@soupbase.io",
_49
"phone": "",
_49
"app_metadata": {
_49
"provider": "email",
_49
"providers": ["email"]
_49
},
_49
"user_metadata": {
_49
"email": "john@soupbase.io",
_49
"email_verified": false,
_49
"phone_verified": false,
_49
"sub": "8484b834-f29e-4af2-bf42-80644d154f76"
_49
},
_49
"identities": [
_49
{
_49
"identity_id": "bc26d70b-517d-4826-bce4-413a5ff257e7",
_49
"id": "8484b834-f29e-4af2-bf42-80644d154f76",
_49
"user_id": "8484b834-f29e-4af2-bf42-80644d154f76",
_49
"identity_data": {
_49
"email": "john@soupbase.io",
_49
"email_verified": false,
_49
"phone_verified": false,
_49
"sub": "8484b834-f29e-4af2-bf42-80644d154f76"
_49
},
_49
"provider": "email",
_49
"last_sign_in_at": "2024-05-14T12:56:33.824231484Z",
_49
"created_at": "2024-05-14T12:56:33.824261Z",
_49
"updated_at": "2024-05-14T12:56:33.824261Z",
_49
"email": "john@soupbase.io"
_49
}
_49
],
_49
"created_at": "2024-05-14T12:56:33.821567Z",
_49
"updated_at": "2024-05-14T12:56:33.825595Z",
_49
"is_anonymous": false
_49
},
_49
"email_data": {
_49
"token": "305805",
_49
"token_hash": "7d5b7b1964cf5d388340a7f04f1dbb5eeb6c7b52ef8270e1737a58d0",
_49
"redirect_to": "http://localhost:3000/",
_49
"email_action_type": "signup",
_49
"site_url": "http://localhost:9999",
_49
"token_new": "",
_49
"token_hash_new": ""
_49
}
_49
}

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 emails related jobs. For performance reasons, the messaging system sends emails in batches 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


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

Create the hook


_40
create or replace function send_email(event jsonb) returns void as $$
_40
declare
_40
job_data jsonb;
_40
scheduled_time timestamp;
_40
priority int;
_40
begin
_40
-- Extract email details from the event JSON
_40
job_data := jsonb_build_object(
_40
'email_action_type', event->'email_data'->>'email_action_type',
_40
'token_hash', event->'email_data'->>'token_hash',
_40
'token', event->'email_data'->>'token',
_40
'email', event->'user'->>'email'
_40
);
_40
_40
-- Calculate the nearest 5-minute window for scheduled_time
_40
scheduled_time := date_trunc('minute', now()) + interval '5 minute' * floor(extract('epoch' from (now() - date_trunc('minute', now())) / 60) / 5);
_40
_40
-- Assign priority dynamically (example logic: higher priority for earlier scheduled time)
_40
priority := extract('epoch' from (scheduled_time - now()))::int;
_40
_40
insert into job_queue (job_data, priority, scheduled_at, max_retries)
_40
values (job_data, priority, scheduled_time, 2);
_40
end;
_40
$$ language plpgsql;
_40
_40
grant execute
_40
on function public.send_email
_40
to supabase_auth_admin;
_40
_40
revoke execute
_40
on function public.send_email
_40
from authenticated, anon;
_40
_40
grant all
_40
on table public.job_queue
_40
to supabase_auth_admin;
_40
_40
revoke all
_40
on table public.job_queue
_40
from authenticated, anon;

Create a function to periodically run and dequeue all jobs


_42
create or replace function dequeue_and_run_jobs() returns void as $$
_42
declare
_42
job record;
_42
begin
_42
for job in
_42
select * from job_queue
_42
where status = 'pending'
_42
and scheduled_at <= now()
_42
order by priority desc, created_at
_42
for update skip locked
_42
loop
_42
begin
_42
-- add job processing logic here.
_42
-- for demonstration, we'll just update the job status to 'completed'.
_42
update job_queue
_42
set status = 'completed'
_42
where job_id = job.job_id;
_42
_42
exception when others then
_42
-- handle job failure and retry logic
_42
if job.retry_count < job.max_retries then
_42
update job_queue
_42
set retry_count = retry_count + 1,
_42
scheduled_at = now() + interval '1 minute' -- delay retry by 1 minute
_42
where job_id = job.job_id;
_42
else
_42
update job_queue
_42
set status = 'failed'
_42
where job_id = job.job_id;
_42
end if;
_42
end;
_42
end loop;
_42
end;
_42
$$ language plpgsql;
_42
_42
grant execute
_42
on function public.dequeue_and_run_jobs
_42
to supabase_auth_admin;
_42
_42
revoke execute
_42
on function public.dequeue_and_run_jobs
_42
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


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