Twilio SMS Integration

The ticket system sends SMS alerts via Twilio for critical-urgency tickets. The integration includes deduplication, rate limiting, opt-out management, and delivery status tracking.

---

Overview

Component	Details
Service	Twilio Programmable SMS
Key File	include/class.twilio-sms.php
Triggers	AI urgency set to CRITICAL
Recipients	Assigned staff + PMs
Cleanup Cron	cron-twilio-cleanup.php

---

SMS Alert Flow

Gemini AI sets urgency = CRITICAL
  → Check ost_ticket_sms_alert (dedup — one SMS per ticket)
  → If not already sent:
    → Check ost_twilio_daily_counter (rate limit)
    → Check ost_twilio_sms_optout (opt-out list)
    → Send SMS via Twilio API
    → Record in ost_ticket_sms_alert (UNIQUE on ticket_id)
    → Increment daily counter
    → Twilio fires status_callback webhook
    → Delivery status stored in ost_twilio_delivery_status

---

Deduplication

Each ticket can only trigger one SMS alert, enforced by a UNIQUE constraint:

-- ost_ticket_sms_alert
CREATE TABLE ost_ticket_sms_alert (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ticket_id INT NOT NULL,
    staff_id INT NOT NULL,
    phone_number VARCHAR(20),
    message_sid VARCHAR(64),
    sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_ticket (ticket_id)
);

If a ticket's urgency is re-evaluated and set to CRITICAL again, no duplicate SMS is sent.

---

Rate Limiting

A daily counter prevents excessive SMS sending:

-- ost_twilio_daily_counter
CREATE TABLE ost_twilio_daily_counter (
    id INT AUTO_INCREMENT PRIMARY KEY,
    date DATE NOT NULL,
    count INT DEFAULT 0,
    UNIQUE KEY unique_date (date)
);

The daily limit is configurable in admin settings. When the limit is reached, SMS alerts are queued but not sent until the next day.

Counter Reset

The cron-twilio-cleanup.php cron (midnight daily) resets counters and generates a failure report for any undelivered messages.

---

Opt-Out Management

Staff can opt out of SMS alerts:

-- ost_twilio_sms_optout
CREATE TABLE ost_twilio_sms_optout (
    id INT AUTO_INCREMENT PRIMARY KEY,
    phone_number VARCHAR(20) NOT NULL,
    staff_id INT,
    opted_out_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY unique_phone (phone_number)
);

Opt-outs are managed via Admin → Twilio → Opt-Outs (scp/twilio-optouts.php).

---

Delivery Status Webhooks

Twilio sends delivery status updates to a webhook endpoint:

Method	Endpoint	Description
POST	/scp/ajax.php/twilio/status_callback	Receives delivery status from Twilio

Status events are stored in ost_twilio_delivery_status:

Status	Meaning
queued	Message accepted by Twilio
sent	Message sent to carrier
delivered	Message confirmed delivered
failed	Delivery failed
undelivered	Could not be delivered

---

Configuration

Configured via Admin → Settings → Twilio SMS:

Setting	Purpose
Account SID	Twilio account identifier
Auth Token	Twilio authentication token
From Number	Twilio phone number (sender)
Daily Limit	Maximum SMS per day
Test Mode	When enabled, logs SMS but doesn't send
Status Callback URL	Webhook URL for delivery status

---

Message Format

🚨 CRITICAL TICKET #108749
Subject: Checkout page 500 error
Client: Acme Corp
Urgency: CRITICAL (95%)
Est. Hours: 4.5

View: https://tickets.rhinogroup.com/scp/tickets.php?number=108749

---

Troubleshooting

Issue	Solution
SMS not sending	Check daily counter hasn't hit limit; verify Test Mode is off
Duplicate SMS	Should be impossible (UNIQUE constraint); check for race condition
"Undelivered" status	Check recipient number is valid; review Twilio error codes
Opt-out not respected	Verify number format matches (include country code)
Webhook not receiving	Verify callback URL is accessible from internet; check Apache config

---

Cron: Cleanup (cron-twilio-cleanup.php)

Runs at midnight daily:

1. Resets daily SMS counter for the new day
2. Checks for any failed or undelivered messages from prior day
3. Generates failure report email to admins
4. Cleans up delivery status records older than 30 days

---

Key Files

File	Purpose
include/class.twilio-sms.php	Twilio API client, SMS sending, dedup, rate limiting
cron-twilio-cleanup.php	Daily cleanup cron (counter reset, failure report)
scp/twilio-optouts.php	Admin UI for managing SMS opt-outs
deploy/deploy-sms-tracking.sql	Migration: creates all Twilio tables