MessageBird SMS Delivery Status and Callbacks with Next.js and Supabase

npx create-next-app@latest messagebird-sms-tracker
cd messagebird-sms-tracker

npm install messagebird @supabase/supabase-js dotenv

# .env.local

# MessageBird API Credentials
MESSAGEBIRD_ACCESS_KEY=your_messagebird_access_key_here

# Supabase Configuration
NEXT_PUBLIC_SUPABASE_URL=https://xxxxxxxxxxxxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=your_supabase_anon_key_here

# Webhook Configuration
WEBHOOK_BASE_URL=https://your-ngrok-url.ngrok.io  # or production domain

# Message Configuration
MESSAGEBIRD_ORIGINATOR=YourBrand  # or phone number
RECIPIENT_NUMBER=+1234567890      # Test recipient (E.164 format)

-- Create messages table for SMS tracking
CREATE TABLE messages (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  message_id VARCHAR(255) UNIQUE NOT NULL,
  reference VARCHAR(255),
  originator VARCHAR(50),
  recipient VARCHAR(50),
  body TEXT,
  status VARCHAR(50) DEFAULT 'pending',
  status_reason VARCHAR(255),
  status_error_code INTEGER,
  status_datetime TIMESTAMP,
  mccmnc VARCHAR(10),
  ported BOOLEAN,
  message_length INTEGER,
  message_part_count INTEGER,
  price_amount DECIMAL(10,4),
  price_currency VARCHAR(3),
  created_at TIMESTAMP DEFAULT NOW(),
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Create indexes for fast lookups
CREATE INDEX idx_message_id ON messages(message_id);
CREATE INDEX idx_reference ON messages(reference);
CREATE INDEX idx_status ON messages(status);
CREATE INDEX idx_created_at ON messages(created_at DESC);

-- Enable Row Level Security (optional, recommended for production)
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Create policy to allow all operations (adjust for production security needs)
CREATE POLICY "Allow all operations" ON messages FOR ALL USING (true);

// lib/supabase.js
import { createClient } from '@supabase/supabase-js';

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY;

if (!supabaseUrl || !supabaseAnonKey) {
  throw new Error('Missing Supabase environment variables');
}

export const supabase = createClient(supabaseUrl, supabaseAnonKey);

// lib/messagebird.js
const messagebird = require('messagebird');

const accessKey = process.env.MESSAGEBIRD_ACCESS_KEY;

if (!accessKey) {
  throw new Error('Missing MESSAGEBIRD_ACCESS_KEY environment variable');
}

// Initialize MessageBird client
const client = messagebird(accessKey);

module.exports = client;

// lib/sendSMS.js
const messagebirdClient = require('./messagebird');
const { supabase } = require('./supabase');

/**
 * Send SMS via MessageBird with delivery report tracking
 * @param {Object} params
 * @param {string} params.recipient - Phone number in E.164 format (e.g., +1234567890)
 * @param {string} params.body - Message content
 * @param {string} params.originator - Sender ID or phone number
 * @param {string} params.reference - Optional client reference for tracking
 * @param {string} params.reportUrl - Webhook URL for delivery reports
 * @returns {Promise<Object&gt;} MessageBird message object
 */
async function sendSMS({ recipient, body, originator, reference, reportUrl }) {
  try {
    // Validate required fields
    if (!recipient || !body || !originator) {
      throw new Error('Missing required fields: recipient, body, originator');
    }

    // Generate reference if not provided
    const messageReference = reference || `msg_${Date.now()}_${Math.random().toString(36).substr(2, 9)}`;

    // Send SMS via MessageBird
    const message = await new Promise((resolve, reject) =&gt; {
      messagebirdClient.messages.create(
        {
          originator,
          recipients: [recipient],
          body,
          reference: messageReference,
          reportUrl: reportUrl || `${process.env.WEBHOOK_BASE_URL}/api/webhooks/status`,
        },
        (err, response) =&gt; {
          if (err) {
            reject(err);
          } else {
            resolve(response);
          }
        }
      );
    });

    console.log('SMS sent successfully:', {
      messageId: message.id,
      reference: messageReference,
      recipient,
    });

    // Store initial message record in Supabase
    const { data, error } = await supabase
      .from('messages')
      .insert([
        {
          message_id: message.id,
          reference: messageReference,
          originator,
          recipient,
          body,
          status: message.recipients?.items?.[0]?.status || 'sent',
          status_reason: message.recipients?.items?.[0]?.statusReason,
          message_length: message.recipients?.items?.[0]?.messageLength,
          message_part_count: message.recipients?.items?.[0]?.messagePartCount,
          created_at: new Date().toISOString(),
        },
      ])
      .select();

    if (error) {
      console.error('Error storing message in database:', error);
      // Don't fail SMS send if database insert fails
    } else {
      console.log('Message stored in database:', data);
    }

    return message;
  } catch (error) {
    console.error('Error sending SMS:', error);
    throw error;
  }
}

module.exports = { sendSMS };

// pages/api/webhooks/status.js
import { supabase } from '../../../lib/supabase';

/**
 * MessageBird Status Report Webhook Handler
 * Receives HTTP GET requests from MessageBird with delivery status updates
 *
 * Query Parameters (from MessageBird):
 * - id: Message ID
 * - reference: Client reference
 * - recipient: Recipient phone number
 * - status: scheduled|sent|buffered|delivered|expired|delivery_failed
 * - statusReason: Detailed status description
 * - statusErrorCode: Error code (optional)
 * - statusDatetime: RFC3339 timestamp
 * - mccmnc: Mobile operator code
 * - ported: Whether number was ported (0 or 1)
 * - messageLength: Character count
 * - messagePartCount: Number of SMS segments
 * - datacoding: plain or unicode
 * - price[amount]: Cost (optional)
 * - price[currency]: Currency code (optional)
 */
export default async function handler(req, res) {
  // MessageBird sends status reports via GET requests
  if (req.method !== 'GET') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

  try {
    // Extract parameters from query string
    const {
      id,
      reference,
      recipient,
      status,
      statusReason,
      statusErrorCode,
      statusDatetime,
      mccmnc,
      ported,
      messageLength,
      messagePartCount,
      datacoding,
    } = req.query;

    // Extract price (MessageBird sends as price[amount] and price[currency])
    const priceAmount = req.query['price[amount]'];
    const priceCurrency = req.query['price[currency]'];

    console.log('Received delivery status webhook:', {
      id,
      reference,
      recipient,
      status,
      statusReason,
      statusErrorCode,
      statusDatetime,
    });

    // Validate required fields
    if (!id || !status) {
      console.error('Missing required fields in webhook');
      return res.status(400).json({ error: 'Missing required fields' });
    }

    // Update message status in Supabase
    const { data, error } = await supabase
      .from('messages')
      .update({
        status,
        status_reason: statusReason,
        status_error_code: statusErrorCode ? parseInt(statusErrorCode, 10) : null,
        status_datetime: statusDatetime,
        mccmnc,
        ported: ported === '1',
        message_length: messageLength ? parseInt(messageLength, 10) : null,
        message_part_count: messagePartCount ? parseInt(messagePartCount, 10) : null,
        price_amount: priceAmount ? parseFloat(priceAmount) : null,
        price_currency: priceCurrency,
        updated_at: new Date().toISOString(),
      })
      .eq('message_id', id)
      .select();

    if (error) {
      console.error('Error updating message in database:', error);
      // Still return 200 OK to prevent MessageBird retries
      return res.status(200).send('OK');
    }

    console.log('Message status updated in database:', data);

    // IMPORTANT: MessageBird requires 200 OK response
    // Failure to respond with 200 will trigger retries (up to 10 times)
    return res.status(200).send('OK');
  } catch (error) {
    console.error('Error processing webhook:', error);
    // Still return 200 OK to prevent infinite retries
    return res.status(200).send('OK');
  }
}

// pages/api/send-sms.js
const { sendSMS } = require('../../lib/sendSMS');

export default async function handler(req, res) {
  if (req.method !== 'POST') {
    return res.status(405).json({ error: 'Method not allowed' });
  }

  try {
    const { recipient, body, originator, reference } = req.body;

    // Validate required fields
    if (!recipient || !body) {
      return res.status(400).json({ error: 'Missing required fields: recipient, body' });
    }

    // Use default originator from env if not provided
    const senderOriginator = originator || process.env.MESSAGEBIRD_ORIGINATOR;

    if (!senderOriginator) {
      return res.status(400).json({ error: 'Missing originator (sender ID)' });
    }

    // Send SMS
    const message = await sendSMS({
      recipient,
      body,
      originator: senderOriginator,
      reference,
      reportUrl: `${process.env.WEBHOOK_BASE_URL}/api/webhooks/status`,
    });

    return res.status(200).json({
      success: true,
      messageId: message.id,
      reference: message.reference,
      status: message.recipients?.items?.[0]?.status,
    });
  } catch (error) {
    console.error('Error in send-sms API:', error);
    return res.status(500).json({
      error: 'Failed to send SMS',
      details: error.message,
    });
  }
}

# Start Next.js dev server
npm run dev

# In another terminal, start ngrok
ngrok http 3000

WEBHOOK_BASE_URL=https://abc123.ngrok.io

curl -X POST http://localhost:3000/api/send-sms \
  -H "Content-Type: application/json" \
  -d '{
    "recipient": "+1234567890",
    "body": "Test message from MessageBird + Next.js",
    "originator": "YourBrand"
  }'

// Check if status update already processed
const { data: existing } = await supabase
  .from('messages')
  .select('status, status_datetime')
  .eq('message_id', id)
  .single();

if (existing && existing.status_datetime >= statusDatetime) {
  console.log('Status already up to date, skipping');
  return res.status(200).send('OK');
}

-- Allow inserts only from authenticated users
CREATE POLICY "Allow insert for authenticated" ON messages
  FOR INSERT WITH CHECK (auth.role() = 'authenticated');

-- Allow updates only for specific columns
CREATE POLICY "Allow status updates" ON messages
  FOR UPDATE USING (true)
  WITH CHECK (true);

# Install Vercel CLI
npm install -g vercel

# Deploy
vercel

# Set environment variables in Vercel dashboard
# Settings > Environment Variables

// Get delivery rate by status
const { data: stats } = await supabase
  .from('messages')
  .select('status, count(*)')
  .group('status');

// Get messages by date range
const { data: recentMessages } = await supabase
  .from('messages')
  .select('*')
  .gte('created_at', '2025-01-01')
  .order('created_at', { ascending: false });

// Calculate delivery rate
const { data: delivered } = await supabase
  .from('messages')
  .select('count(*)')
  .eq('status', 'delivered')
  .single();

const { data: total } = await supabase
  .from('messages')
  .select('count(*)')
  .single();

const deliveryRate = (delivered.count / total.count) * 100;

npm install @sentry/nextjs
npx @sentry/wizard@latest -i nextjs