Build It: Automated SMS for Every Stage of a Service Job

This is where the stack comes together. In the Supabase walkthrough, we built the job tracking database. In the Twilio basics and advanced guides, we covered sending, receiving, delivery tracking, and opt-outs. Now we put it all together: a complete SMS notification system that fires automatically at every stage of a service job.

No polling. No manual sends. No technician calling the customer to say they’re on the way. The database changes, the SMS goes out — every time, in seconds.

Here’s what we’re building: seven SMS touchpoints tied to the job lifecycle.

Status Change	Recipient	Message
Job created	Customer	Booking confirmation with job details
Job assigned	Technician	Job details + accept link
Job assigned	Customer	Tech name and arrival window
Status → enroute	Customer	Tech is on the way with ETA
Status → onsite	Customer	Tech has arrived
Status → complete	Customer	Job complete confirmation
Status → complete	Customer	Payment link (follow-up)

By the end of this walkthrough, all seven are wired up and live.

---

Prerequisites

Before starting, you need:

• The job tracking database from the Supabase walkthrough — customers, jobs, and technicians tables with foreign keys, indexes, and the updated_at trigger
• Twilio account with a verified phone number, upgraded beyond trial (trial accounts can only send to verified numbers, which blocks real customer notifications)
• n8n running (cloud or self-hosted) with your Supabase credential already configured
• A2P 10DLC registration started — covered in the Advanced Twilio guide. You don’t need approval before testing, but start the registration now so it’s ready when you go live

If you haven’t done the Supabase walkthrough, do that first. This walkthrough assumes that database exists and the database webhooks pattern is familiar.

---

Step 1: Add the Message Log Table

Every SMS this system sends should be recorded. You need to know what was sent, to whom, when, and whether it delivered. Without this, you’re flying blind when a customer says they never received the payment link.

In your Supabase SQL Editor, run:

CREATE TABLE message_log (
  id               uuid        PRIMARY KEY DEFAULT gen_random_uuid(),
  job_id           uuid        REFERENCES jobs(id) ON DELETE SET NULL,
  customer_id      uuid        REFERENCES customers(id) ON DELETE SET NULL,
  technician_id    uuid        REFERENCES technicians(id) ON DELETE SET NULL,
  direction        text        NOT NULL CHECK (direction IN ('outbound', 'inbound')),
  message_type     text        NOT NULL,
  recipient_phone  text        NOT NULL,
  body             text        NOT NULL,
  twilio_sid       text,
  status           text        NOT NULL DEFAULT 'queued',
  sent_at          timestamptz NOT NULL DEFAULT now(),
  delivered_at     timestamptz,
  awaiting_reply   boolean     NOT NULL DEFAULT false
);

CREATE INDEX idx_message_log_job_id ON message_log(job_id);
CREATE INDEX idx_message_log_customer_id ON message_log(customer_id);
CREATE INDEX idx_message_log_twilio_sid ON message_log(twilio_sid);

The twilio_sid column is how you link a delivery status callback back to the right log row. When Twilio reports “message X was delivered,” you look it up by SID and update the status and delivered_at.

Also add an sms_opted_out column to your customers table if you don’t have one:

ALTER TABLE customers ADD COLUMN IF NOT EXISTS sms_opted_out boolean NOT NULL DEFAULT false;
ALTER TABLE customers ADD COLUMN IF NOT EXISTS opted_out_at timestamptz;

---

Step 2: Build the Reusable Phone Normalization Subworkflow

Every workflow in this system needs to normalize a phone number to E.164 format before passing it to Twilio. Rather than duplicating the Code node in every workflow, build it once as a sub-workflow you call from others.

In n8n, create a new workflow called “Normalize Phone Number.”

Add a Webhook trigger node (this makes it callable via n8n’s “Execute Workflow” node from other workflows):

• Respond immediately: on
• Path: normalize-phone

Add a Code node connected to the webhook:

const phone = $input.first().json.phone;

if (!phone) {
  return [{ json: { phone_e164: null, valid: false } }];
}

// Strip everything except digits
let digits = String(phone).replace(/\D/g, '');

// Handle US numbers
let e164;
if (digits.length === 10) {
  e164 = '+1' + digits;
} else if (digits.length === 11 && digits.startsWith('1')) {
  e164 = '+' + digits;
} else {
  return [{ json: { phone_e164: null, valid: false, raw: phone } }];
}

return [{ json: { phone_e164: e164, valid: true, raw: phone } }];

Activate this workflow. From any other workflow in this system, call it with an “Execute Workflow” node, pass the phone number in, and get back phone_e164 — ready for Twilio.

---

Step 3: Build the Opt-Out Check Pattern

Before every outbound SMS, check whether the customer has opted out. Do this in every workflow, not just some of them.

The pattern is two nodes at the start of any outbound SMS flow, after you have the customer ID:

Supabase node — check opt-out status:

• Operation: Get Many
• Table: customers
• Filter: id equals {{ $json.customer_id }}
• Return All: off (limit 1)

IF node — stop if opted out:

• Condition: {{ $json.sms_opted_out }} equals true
• True branch: Stop and Error node (or just do nothing — use a No Operation node)
• False branch: continue to the SMS send

This pattern protects you from TCPA liability and prevents wasted Twilio spend. Set it up in every workflow below. I’ll indicate where it goes rather than repeating the full description each time.

---

Step 4: Job Created → Customer Confirmation

The first SMS goes out the moment a new job is created. This uses an INSERT event on the jobs table rather than an UPDATE.

In Supabase — create the INSERT webhook:

Database → Webhooks → Create webhook:

• Name: job_created
• Table: jobs
• Events: INSERT only
• URL: your n8n webhook URL (production URL — we’ll set this up next)

In n8n — create the “Job Created Confirmation” workflow:

1. Webhook trigger — path: job-created, method: POST

2. Supabase node — query the full job + customer context:

SELECT
  j.id          AS job_id,
  j.address,
  j.scheduled_at,
  j.notes,
  c.id          AS customer_id,
  c.name        AS customer_name,
  c.phone       AS customer_phone,
  c.sms_opted_out
FROM jobs j
JOIN customers c ON j.customer_id = c.id
WHERE j.id = '{{ $json.body.record.id }}'
LIMIT 1;

3. IF node — opt-out check: stop if {{ $json.sms_opted_out }} is true

4. Code node — normalize phone:

let phone = $input.first().json.customer_phone;
let digits = String(phone).replace(/\D/g, '');
let e164 = digits.length === 10 ? '+1' + digits : digits.length === 11 ? '+' + digits : null;
return [{ json: { ...$input.first().json, phone_e164: e164 } }];

5. Set node — build the message body:

• message →

Hi {{ $json.customer_name }}, your service appointment has been confirmed at {{ $json.address }}. We'll send you a heads-up when your technician is assigned. Reply STOP to opt out.

6. Twilio node — send SMS:

• From: your Twilio number
• To: {{ $json.phone_e164 }}
• Message: {{ $json.message }}

7. Supabase node — log the message:

• Operation: Create
• Table: message_log
• Fields:
  • job_id → {{ $json.job_id }}
  • customer_id → {{ $json.customer_id }}
  • direction → outbound
  • message_type → job_confirmation
  • recipient_phone → {{ $json.phone_e164 }}
  • body → {{ $json.message }}
  • twilio_sid → {{ $('Twilio').item.json.sid }}
  • status → sent

Activate this workflow. Update the Supabase job_created webhook URL to the production n8n URL.

Test it: Insert a new job in Supabase with a real customer record (one with your verified phone number during trial). The confirmation SMS should arrive within a few seconds.

---

Step 5: Job Assigned → Technician Alert

When a dispatcher assigns a technician (technician_id is set on a job, status moves to assigned), two SMS go out: one to the technician with the job details, one to the customer with the tech’s name and arrival window.

In Supabase — update the job_status_changed webhook (from the Supabase walkthrough) to also fire on the INSERT event if you want, or create a separate approach. Since the assigned status is set via UPDATE (the dispatcher updates the job), the existing UPDATE webhook handles this.

In n8n — update the “Job Status Changed” workflow:

In the IF node where you check for enroute, add a second branch for assigned. Use a Switch node instead of a simple IF to handle multiple status values cleanly.

Replace the IF node with a Switch node:

• Mode: Rules
• Rule 1: {{ $json.body.record.status }} equals assigned
• Rule 2: {{ $json.body.record.status }} equals enroute
• Rule 3: {{ $json.body.record.status }} equals onsite
• Rule 4: {{ $json.body.record.status }} equals complete

Each rule connects to its own branch.

For the “assigned” branch:

Supabase query — get full context:

SELECT
  j.id            AS job_id,
  j.address,
  j.scheduled_at,
  j.notes,
  c.id            AS customer_id,
  c.name          AS customer_name,
  c.phone         AS customer_phone,
  c.sms_opted_out AS customer_opted_out,
  t.id            AS technician_id,
  t.name          AS technician_name,
  t.phone         AS technician_phone
FROM jobs j
JOIN customers c ON j.customer_id = c.id
JOIN technicians t ON j.technician_id = t.id
WHERE j.id = '{{ $json.body.record.id }}'
LIMIT 1;

SMS to technician (no opt-out check needed for internal staff):

Normalize technician phone, then Twilio node:

Hi {{ $json.technician_name }}, you've been assigned a new job.
Customer: {{ $json.customer_name }}
Address: {{ $json.address }}
Scheduled: {{ $json.scheduled_at }}
Notes: {{ $json.notes }}
Reply ACCEPT to confirm or call dispatch if you have questions.

Log to message_log with message_type: 'tech_assignment', technician_id, direction: 'outbound'.

SMS to customer (with opt-out check):

IF customer_opted_out is true → skip. Otherwise Twilio node:

Hi {{ $json.customer_name }}, {{ $json.technician_name }} has been assigned to your job at {{ $json.address }}. We'll let you know when they're on their way.

Log to message_log with message_type: 'tech_assigned_customer_notice', customer_id, direction: 'outbound'.

---

Step 6: Enroute → ETA Notification

This is the highest-value SMS in the system. Customers expect it. When they receive it, job complaints drop significantly.

In the Switch node’s “enroute” branch:

Supabase query — same join as above, same WHERE j.id = ....

Opt-out check for customer.

Normalize customer phone.

Twilio node:

Hi {{ $json.customer_name }}, {{ $json.technician_name }} is on the way to {{ $json.address }}. You'll receive another message when they arrive. If you need to reach them, call us at [your number].

Log with message_type: 'enroute_notification'.

---

Step 7: Onsite → Arrival Confirmation

Short and simple. The tech is there.

In the Switch node’s “onsite” branch:

Supabase query, opt-out check, normalize phone.

Twilio node:

Hi {{ $json.customer_name }}, {{ $json.technician_name }} has arrived at {{ $json.address }}. Thank you for choosing [your business name].

Log with message_type: 'onsite_arrival'.

---

Step 8: Complete → Completion + Payment Link

Two messages go out on job completion: the completion confirmation, then the payment link. Add a short delay between them so they don’t arrive as a wall of texts.

In the Switch node’s “complete” branch:

Supabase query, opt-out check, normalize phone.

First Twilio node — completion confirmation:

Hi {{ $json.customer_name }}, your service at {{ $json.address }} is complete. {{ $json.technician_name }} has wrapped up for the day. Thank you for your business.

Log with message_type: 'job_complete'.

Wait node — add a 60-second delay between the two messages.

Second Twilio node — payment link:

You’ll need the Stripe payment link for this customer’s invoice. If you’re using Stripe payment links, you either:

1. Use a fixed payment link (for a standard service fee), or
2. Generate a one-time payment link using the Stripe API in an HTTP Request node

For now, use a fixed Stripe payment link URL as a placeholder. In production, you’d generate a Stripe Payment Intent and get the hosted invoice URL from the Stripe API response.

Hi {{ $json.customer_name }}, your invoice is ready. Pay securely here: [your Stripe payment link]. Questions? Reply to this message or call [your number].

Log with message_type: 'payment_link', awaiting_reply: true.

---

Step 9: Wire Up the Delivery Status Callback

Without this, you’ll never know if the payment link SMS actually delivered.

In n8n, create a new workflow — “Twilio Status Callback”:

1. Webhook trigger — path: twilio-status, method: POST
2. Supabase node — update the message log:
   • Operation: Update
   • Table: message_log
   • Update Key: twilio_sid
   • Match value: {{ $json.body.MessageSid }}
   • Fields:
     • status → {{ $json.body.MessageStatus }}
     • delivered_at → add a condition: only set if status is delivered

For the delivered_at conditional, use a Set node before the Supabase update:

const status = $input.first().json.body.MessageStatus;
return [{
  json: {
    ...$input.first().json.body,
    delivered_at: status === 'delivered' ? new Date().toISOString() : null
  }
}];

Activate this workflow. Then in every Twilio node across all your workflows, add StatusCallback → your twilio-status webhook URL in the Twilio node’s additional fields.

Now every message in your message_log table will have an accurate delivery status within seconds of the carrier confirming delivery.

---

Step 10: Full Test Run

Set up a complete test run using your own phone number as the customer.

Seed fresh test data:

-- Insert yourself as a test customer
INSERT INTO customers (name, email, phone)
VALUES ('Test Customer', 'your@email.com', '+1YOURNUMBER')
RETURNING id;

-- Insert a test technician
INSERT INTO technicians (name, phone)
VALUES ('Test Tech', '+1YOUROTHERPHONE')
RETURNING id;

Run through the full lifecycle:

1. Create a job (triggers job confirmation SMS):

INSERT INTO jobs (customer_id, address, status)
VALUES ('customer-uuid', '123 Test St, Orlando FL', 'pending');

2. Assign a technician (triggers tech alert + customer notice):

UPDATE jobs SET status = 'assigned', technician_id = 'tech-uuid'
WHERE status = 'pending';

3. Mark enroute (triggers ETA SMS):

UPDATE jobs SET status = 'enroute' WHERE status = 'assigned';

4. Mark onsite (triggers arrival SMS):

UPDATE jobs SET status = 'onsite' WHERE status = 'enroute';

5. Mark complete (triggers completion + payment link):

UPDATE jobs SET status = 'complete' WHERE status = 'onsite';

After each step, your phone should receive the corresponding SMS within 2–5 seconds. In n8n, open the Executions tab on the “Job Status Changed” workflow — you should see one successful execution per UPDATE.

In Supabase, query your message_log table:

SELECT message_type, recipient_phone, body, status, sent_at, delivered_at
FROM message_log
ORDER BY sent_at DESC;

All seven messages should be there. Within a minute of delivery, status and delivered_at should be populated by the status callback workflow.

---

Common Issues and Fixes

SMS fires but no message received:

Check the Twilio console → Messaging → Logs → Messages. Find the message — look at its status. Error 21610 means the number is opted out. Error 21211 means bad phone format. Error 30007 means carrier filtering (A2P registration needed).

Switch node routing to wrong branch:

Click the Webhook node in the execution log and verify the path to the status field. It’s usually $json.body.record.status but could be $json.record.status depending on how the Webhook node is configured. Adjust your Switch node conditions to match the actual path.

Both “assigned” SMS messages fire but the customer one is missing:

The opt-out check IF node is probably routing to the wrong output. In n8n, the “false” branch is the pass-through (customer is NOT opted out, proceed with SMS). Confirm the branch connections — the Twilio node should be on the “false” (not opted out) branch.

Message log not updating with delivery status:

Confirm the StatusCallback URL in each Twilio node is set to the production webhook URL for your Twilio Status Callback workflow — not the test URL. Also confirm that workflow is Active, not just in test mode.

Supabase query returning empty on status change:

The database webhook fires almost simultaneously with the status update. In rare cases, the webhook payload arrives at n8n before the UPDATE has committed. Add a Wait node (1–2 seconds) between the Webhook trigger and the Supabase query to let the transaction complete. This is uncommon but worth knowing.

---

What to Build Next

You now have a fully automated job communication system. Here’s what to add next:

Inbound reply processing. Customers who reply to any of these messages should get a response. Build the inbound SMS handler from the Advanced Twilio guide. A customer replying “What time?” after the assigned SMS could get an automated response with the scheduled time from Supabase.

Technician ACCEPT/DECLINE flow. The tech assignment SMS asked the tech to reply ACCEPT. Build the inbound handler that processes that reply, confirms the assignment in Supabase, and notifies dispatch if the tech declines.

Failed delivery follow-up. When a message comes back undelivered, trigger a follow-up workflow — try calling the customer, or flag the record in Supabase for a dispatcher to call manually. No customer should fall through the cracks because of a bad phone number.

Stripe payment link generation. Replace the fixed payment link with a dynamic one. When the job completes, call the Stripe API from n8n to create a Payment Intent or an Invoice, get the hosted URL, and send that in the payment link SMS. Each customer gets a link tied to their specific job amount.

Weekly delivery report. Every Monday, query message_log for the past week — total sent, total delivered, undelivered count, delivery rate percentage. Email it to yourself. You’ll spot problems (carrier filtering, bad numbers) before they become patterns.

---

The communication layer is live. Every stage of a service job now communicates itself to the right person at the right time. Next: add email alongside SMS for customers who prefer it. What Is Resend? →