How to Build a SMS Notification System with Lovable

1Create an SMS notification system schema in Supabase.
2
3Tables:
4
51. sms_templates:
6   id uuid primary key default gen_random_uuid()
7   name text not null unique
8   body_template text not null — supports {{variable}} placeholders, max 160 chars per segment
9   variables text[] — list of expected variable names for this template
10   category text — e.g. 'transactional', 'marketing', 'alert'
11   is_active boolean default true
12   created_at timestamptz default now()
13   updated_at timestamptz default now()
14
152. notification_queue:
16   id uuid primary key default gen_random_uuid()
17   to_phone text not null — E.164 format: +1XXXXXXXXXX
18   template_id uuid references sms_templates(id)
19   variables jsonb default '{}' — the values to substitute into the template
20   body text — overrides template if provided (for one-off messages)
21   status text default 'pending' — 'pending', 'sending', 'sent', 'delivered', 'failed', 'undelivered', 'cancelled'
22   priority integer default 5 — 1 = highest, 10 = lowest
23   send_at timestamptz default now() — allows future scheduling
24   sent_at timestamptz
25   delivered_at timestamptz
26   twilio_message_sid text
27   twilio_error_code text
28   twilio_error_message text
29   created_by uuid references auth.users(id)
30   created_at timestamptz default now()
31   updated_at timestamptz default now()
32
333. (Optional) phone_opt_outs:
34   id uuid primary key default gen_random_uuid()
35   phone text not null unique
36   opted_out_at timestamptz default now()
37   reason text — 'user_request', 'stop_reply', 'undeliverable'
38
39RLS:
40- sms_templates: authenticated users SELECT; authenticated INSERT/UPDATE for template managers
41- notification_queue: authenticated users can INSERT their own messages (created_by = auth.uid()); SELECT their own messages; service role for status updates
42- phone_opt_outs: service role only for INSERT; authenticated SELECT
43
44Indexes:
45- notification_queue(status, send_at) — the queue processing query
46- notification_queue(twilio_message_sid) — for webhook lookups
47- notification_queue(to_phone, created_at) — for per-number history
48- phone_opt_outs(phone)

1Create a Supabase Edge Function at supabase/functions/send-sms/index.ts.
2
3The function accepts POST with body: { queue_id: string }
4
5Logic:
61. Fetch the notification_queue row by queue_id with template JOIN
72. If status !== 'pending': return { skipped: true } (idempotent)
83. Check phone_opt_outs for to_phone. If found, update status='cancelled' and return.
94. Render the message body:
10   - If row.body is set, use it directly
11   - Else fetch the template's body_template and replace {{variable}} with row.variables[variable]
12   - Use: body = template.replace(/\{\{(\w+)\}\}/g, (_, key) => variables[key] ?? '')
135. Update notification_queue.status = 'sending'
146. Call Twilio Messages API:
15   const accountSid = Deno.env.get('TWILIO_ACCOUNT_SID')
16   const authToken = Deno.env.get('TWILIO_AUTH_TOKEN')
17   const from = Deno.env.get('TWILIO_FROM_NUMBER')
18   const auth = btoa(accountSid + ':' + authToken)
19
20   const response = await fetch(
21     `https://api.twilio.com/2010-04-01/Accounts/${accountSid}/Messages.json`,
22     {
23       method: 'POST',
24       headers: {
25         Authorization: 'Basic ' + auth,
26         'Content-Type': 'application/x-www-form-urlencoded',
27       },
28       body: new URLSearchParams({ To: to_phone, From: from, Body: renderedBody }).toString(),
29     }
30   )
317. Parse response. On success (status 201): update notification_queue with status='sent', twilio_message_sid=data.sid, sent_at=now()
328. On error: update status='failed', twilio_error_code=data.code, twilio_error_message=data.message
339. Return { success: boolean, sid?: string, error?: string }
34
35Return CORS headers on all responses.

1// SQL to run in Supabase Dashboard → SQL Editor
2
3CREATE OR REPLACE FUNCTION process_sms_queue()
4RETURNS void
5LANGUAGE plpgsql
6SECURITY DEFINER
7AS $$
8DECLARE
9  msg RECORD;
10  edge_url text := current_setting('app.supabase_url') || '/functions/v1/send-sms';
11  service_key text := current_setting('app.service_role_key');
12BEGIN
13  FOR msg IN
14    SELECT id
15    FROM notification_queue
16    WHERE status = 'pending'
17      AND send_at <= now()
18      AND NOT EXISTS (
19        SELECT 1 FROM phone_opt_outs WHERE phone = notification_queue.to_phone
20      )
21    ORDER BY priority ASC, send_at ASC
22    LIMIT 50
23    FOR UPDATE SKIP LOCKED
24  LOOP
25    PERFORM net.http_post(
26      url := edge_url,
27      headers := jsonb_build_object(
28        'Content-Type', 'application/json',
29        'Authorization', 'Bearer ' || service_key
30      ),
31      body := jsonb_build_object('queue_id', msg.id)
32    );
33  END LOOP;
34END;
35$$;
36
37-- Register cron job: runs every minute
38SELECT cron.schedule(
39  'process-sms-queue',
40  '* * * * *',
41  'SELECT process_sms_queue()'
42);

1Create a Supabase Edge Function at supabase/functions/twilio-status-webhook/index.ts.
2
3Twilio calls this URL via POST with URL-encoded form data when message status changes.
4
5Logic:
61. Parse the form body: const formData = await req.formData()
7   - MessageSid: formData.get('MessageSid')
8   - MessageStatus: formData.get('MessageStatus') — 'sent', 'delivered', 'failed', 'undelivered'
9   - ErrorCode: formData.get('ErrorCode') (present on failures)
10   - ErrorMessage: formData.get('ErrorMessage')
11
122. Validate the request is from Twilio using Twilio's signature validation:
13   - X-Twilio-Signature header must match HMAC-SHA1 of the full URL + sorted params with TWILIO_AUTH_TOKEN
14   - If invalid, return 403
15
163. Find the notification_queue row by twilio_message_sid = MessageSid
17
184. Update based on MessageStatus:
19   - 'delivered': status='delivered', delivered_at=now()
20   - 'failed' or 'undelivered': status=MessageStatus, twilio_error_code=ErrorCode, twilio_error_message=ErrorMessage
21   - If 'undelivered' and ErrorCode is 21610 (STOP reply): insert into phone_opt_outs
22
235. Return TwiML 200 response: new Response('<?xml version="1.0"?><Response/>', { headers: { 'Content-Type': 'text/xml' } })
24
25Note: Always return a 200 TwiML response — Twilio retries webhooks that return non-2xx.
26
27Register this Edge Function URL in Twilio Console → Phone Numbers → Active Numbers → 'A MESSAGE COMES IN' → Webhook → HTTP POST.

1Build an SMS notification dashboard with two pages.
2
31. src/pages/SmsHistory.tsx — send history:
4   - Summary Cards at top: Total Sent (last 30 days), Delivery Rate, Failed Count
5   - DataTable showing notification_queue rows with columns:
6     To (masked: first 3 digits + *** + last 2), Template Name, Status Badge, Sent At, Delivered At, Message Preview (first 50 chars)
7   - Status Badge colors: pending=gray, sending=blue, sent=indigo, delivered=green, failed=red, cancelled=yellow
8   - Filter Select: All Status, Pending, Delivered, Failed
9   - Search Input: filter by phone number (last 4 digits)
10   - 'Resend' action for failed messages: copies the queue row with status='pending' and send_at=now()
11
122. src/pages/SmsTemplates.tsx — template manager:
13   - Left panel: list of templates with name and category Badge
14   - Right panel: template editor
15     - Name Input
16     - Category Select: transactional, marketing, alert
17     - Body Textarea: monospace font
18     - Live character count with color: green (<=160), yellow (161-320), red (>320 = multiple SMS segments)
19     - Variables list: auto-detected from {{variable}} in body, shown as Badges
20     - Preview section: Input fields for each detected variable, rendered preview below
21   - Save and Delete buttons
22
233. Send SMS Dialog (reusable component used from other parts of the app):
24   - Template Select
25   - Phone number Input (with E.164 format hint)
26   - Auto-generated variable Inputs based on selected template's variables array
27   - Schedule toggle: 'Send Now' vs 'Schedule for Later' (shows DateTimePicker if later)
28   - Priority Select: Normal (5), High (2), Urgent (1)
29   - On submit: INSERT into notification_queue