Connecting Twilio to Your CRM for Automated SMS Follow-Ups

mkdir twilio-crm-integration
cd twilio-crm-integration
npm init -y
npm install express twilio dotenv pg node-cron libphonenumber-js axios body-parser
npm install --save-dev nodemon

mkdir src routes services db config
touch src/server.js routes/sms.js routes/webhooks.js
touch services/twilioService.js services/crmService.js
touch db/database.js db/schema.sql config/templates.js
touch .env .gitignore
```

Your structure should be:
```
twilio-crm-integration/
├── src/
│   └── server.js
├── routes/
│   ├── sms.js
│   └── webhooks.js
├── services/
│   ├── twilioService.js
│   └── crmService.js
├── db/
│   ├── database.js
│   └── schema.sql
├── config/
│   └── templates.js
├── .env
├── .gitignore
└── package.json

-- SMS messages log
CREATE TABLE IF NOT EXISTS sms_messages (
    id SERIAL PRIMARY KEY,
    message_sid VARCHAR(34) UNIQUE NOT NULL,
    from_number VARCHAR(20) NOT NULL,
    to_number VARCHAR(20) NOT NULL,
    body TEXT NOT NULL,
    direction VARCHAR(10) NOT NULL, -- 'inbound' or 'outbound'
    status VARCHAR(50), -- 'queued', 'sent', 'delivered', 'failed', 'received'
    error_code INTEGER,
    error_message TEXT,
    crm_contact_id VARCHAR(255),
    crm_type VARCHAR(50), -- 'hubspot', 'salesforce', 'pipedrive'
    campaign_id INTEGER,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- SMS campaigns
CREATE TABLE IF NOT EXISTS sms_campaigns (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    template_id VARCHAR(100),
    trigger_type VARCHAR(50), -- 'lead_created', 'deal_stage_changed', 'payment_failed'
    trigger_conditions JSONB,
    active BOOLEAN DEFAULT true,
    messages_sent INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- SMS conversations (threading)
CREATE TABLE IF NOT EXISTS sms_conversations (
    id SERIAL PRIMARY KEY,
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    crm_contact_id VARCHAR(255),
    crm_type VARCHAR(50),
    last_message_at TIMESTAMP,
    last_message_direction VARCHAR(10),
    message_count INTEGER DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Opt-out management
CREATE TABLE IF NOT EXISTS sms_opt_outs (
    id SERIAL PRIMARY KEY,
    phone_number VARCHAR(20) UNIQUE NOT NULL,
    opted_out_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    reason VARCHAR(255)
);

-- Indexes
CREATE INDEX idx_message_sid ON sms_messages(message_sid);
CREATE INDEX idx_crm_contact ON sms_messages(crm_contact_id);
CREATE INDEX idx_phone_number ON sms_conversations(phone_number);
CREATE INDEX idx_opt_out_phone ON sms_opt_outs(phone_number);

psql -U your_username -d your_database -f db/schema.sql

{
  "name": "twilio-crm-integration",
  "version": "1.0.0",
  "type": "module",
  "scripts": {
    "start": "node src/server.js",
    "dev": "nodemon src/server.js"
  },
  "dependencies": {
    "axios": "^1.6.2",
    "body-parser": "^1.20.2",
    "dotenv": "^16.3.1",
    "express": "^4.18.2",
    "libphonenumber-js": "^1.10.51",
    "node-cron": "^3.0.3",
    "pg": "^8.11.3",
    "twilio": "^4.19.0"
  },
  "devDependencies": {
    "nodemon": "^3.0.1"
  }
}

echo "node_modules/
.env
*.log" > .gitignore

# Twilio Configuration
TWILIO_ACCOUNT_SID=ACxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
TWILIO_AUTH_TOKEN=your_auth_token_here
TWILIO_PHONE_NUMBER=+15555551234
TWILIO_MESSAGING_SERVICE_SID=MGxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

# PostgreSQL Configuration
PG_HOST=localhost
PG_PORT=5432
PG_DATABASE=twilio_crm
PG_USER=your_username
PG_PASSWORD=your_password

# CRM Configuration (HubSpot example)
HUBSPOT_API_KEY=your_hubspot_api_key
CRM_TYPE=hubspot

# Server Configuration
PORT=3000
NODE_ENV=development
BASE_URL=https://your-domain.com

# SMS Configuration
SMS_RATE_LIMIT_PER_SECOND=10
ENABLE_OPT_OUT_CHECK=true

import pg from 'pg';
import dotenv from 'dotenv';

dotenv.config();

const { Pool } = pg;

const pool = new Pool({
  host: process.env.PG_HOST,
  port: process.env.PG_PORT,
  database: process.env.PG_DATABASE,
  user: process.env.PG_USER,
  password: process.env.PG_PASSWORD,
  max: 20,
});

// Test connection
export async function testConnection() {
  try {
    const client = await pool.connect();
    console.log('✓ PostgreSQL connected');
    client.release();
    return true;
  } catch (error) {
    console.error('✗ PostgreSQL connection failed:', error.message);
    return false;
  }
}

// Log SMS message
export async function logSMSMessage(messageData) {
  const query = `
    INSERT INTO sms_messages (
      message_sid, from_number, to_number, body, direction,
      status, error_code, error_message, crm_contact_id, crm_type
    ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
    ON CONFLICT (message_sid)
    DO UPDATE SET
      status = EXCLUDED.status,
      error_code = EXCLUDED.error_code,
      error_message = EXCLUDED.error_message,
      updated_at = CURRENT_TIMESTAMP
    RETURNING *
  `;

  const values = [
    messageData.messageSid,
    messageData.from,
    messageData.to,
    messageData.body,
    messageData.direction,
    messageData.status || 'queued',
    messageData.errorCode || null,
    messageData.errorMessage || null,
    messageData.crmContactId || null,
    messageData.crmType || null,
  ];

  const result = await pool.query(query, values);
  return result.rows[0];
}

// Update message status
export async function updateMessageStatus(messageSid, status, errorCode = null, errorMessage = null) {
  const query = `
    UPDATE sms_messages
    SET status = $1, error_code = $2, error_message = $3, updated_at = CURRENT_TIMESTAMP
    WHERE message_sid = $4
    RETURNING *
  `;

  const result = await pool.query(query, [status, errorCode, errorMessage, messageSid]);
  return result.rows[0];
}

// Create or update conversation
export async function upsertConversation(phoneNumber, crmContactId, direction) {
  const query = `
    INSERT INTO sms_conversations (phone_number, crm_contact_id, last_message_at, last_message_direction, message_count)
    VALUES ($1, $2, CURRENT_TIMESTAMP, $3, 1)
    ON CONFLICT (phone_number)
    DO UPDATE SET
      last_message_at = CURRENT_TIMESTAMP,
      last_message_direction = EXCLUDED.last_message_direction,
      message_count = sms_conversations.message_count + 1
    RETURNING *
  `;

  const result = await pool.query(query, [phoneNumber, crmContactId, direction]);
  return result.rows[0];
}

// Check if phone number is opted out
export async function isOptedOut(phoneNumber) {
  const query = 'SELECT * FROM sms_opt_outs WHERE phone_number = $1';
  const result = await pool.query(query, [phoneNumber]);
  return result.rows.length > 0;
}

// Add opt-out
export async function addOptOut(phoneNumber, reason = 'User requested') {
  const query = `
    INSERT INTO sms_opt_outs (phone_number, reason)
    VALUES ($1, $2)
    ON CONFLICT (phone_number) DO NOTHING
    RETURNING *
  `;

  const result = await pool.query(query, [phoneNumber, reason]);
  return result.rows[0];
}

// Get conversation history
export async function getConversationHistory(phoneNumber, limit = 50) {
  const query = `
    SELECT * FROM sms_messages
    WHERE from_number = $1 OR to_number = $1
    ORDER BY created_at DESC
    LIMIT $2
  `;

  const result = await pool.query(query, [phoneNumber, limit]);
  return result.rows;
}

// Get messages by CRM contact
export async function getMessagesByContact(crmContactId) {
  const query = `
    SELECT * FROM sms_messages
    WHERE crm_contact_id = $1
    ORDER BY created_at DESC
  `;

  const result = await pool.query(query, [crmContactId]);
  return result.rows;
}

export default pool;

// SMS message templates with variable substitution
export const messageTemplates = {
  // New lead follow-up
  new_lead: {
    name: 'New Lead Follow-Up',
    body: `Hi {firstName}, thanks for your interest in {companyName}! I'm {repName}, your account executive. When's a good time for a quick call this week?`,
    variables: ['firstName', 'companyName', 'repName'],
  },

  // Deal stage changed
  proposal_sent: {
    name: 'Proposal Sent Follow-Up',
    body: `Hi {firstName}, I just sent over the proposal we discussed. Did you get a chance to review it? Any questions I can answer?`,
    variables: ['firstName'],
  },

  // Payment failed
  payment_failed: {
    name: 'Payment Failed Alert',
    body: `Hi {firstName}, your recent payment didn't go through. Please update your payment method at {paymentLink} to avoid service interruption.`,
    variables: ['firstName', 'paymentLink'],
  },

  // Meeting reminder
  meeting_reminder: {
    name: 'Meeting Reminder',
    body: `Hi {firstName}, reminder: we have a call scheduled for {meetingTime}. Looking forward to speaking! Join here: {meetingLink}`,
    variables: ['firstName', 'meetingTime', 'meetingLink'],
  },

  // Generic follow-up
  generic_followup: {
    name: 'Generic Follow-Up',
    body: `Hi {firstName}, just checking in! Do you have any questions about {topic}? I'm here to help.`,
    variables: ['firstName', 'topic'],
  },
};

// Replace variables in template
export function renderTemplate(templateId, variables) {
  const template = messageTemplates[templateId];

  if (!template) {
    throw new Error(`Template ${templateId} not found`);
  }

  let body = template.body;

  // Replace all variables
  for (const [key, value] of Object.entries(variables)) {
    const placeholder = `{${key}}`;
    body = body.replace(new RegExp(placeholder, 'g'), value || '');
  }

  // Check for unreplaced variables
  const unreplaced = body.match(/\{(\w+)\}/g);
  if (unreplaced) {
    console.warn(`Unreplaced variables in template: ${unreplaced.join(', ')}`);
  }

  return body;
}

export default { messageTemplates, renderTemplate };

import twilio from 'twilio';
import { parsePhoneNumber } from 'libphonenumber-js';
import dotenv from 'dotenv';
import { logSMSMessage, isOptedOut, addOptOut } from '../db/database.js';

dotenv.config();

const accountSid = process.env.TWILIO_ACCOUNT_SID;
const authToken = process.env.TWILIO_AUTH_TOKEN;
const twilioPhoneNumber = process.env.TWILIO_PHONE_NUMBER;
const messagingServiceSid = process.env.TWILIO_MESSAGING_SERVICE_SID;

// Initialize Twilio client
const client = twilio(accountSid, authToken);

// Format and validate phone number
export function formatPhoneNumber(phoneNumber, defaultCountry = 'US') {
  try {
    const parsed = parsePhoneNumber(phoneNumber, defaultCountry);

    if (!parsed || !parsed.isValid()) {
      throw new Error('Invalid phone number');
    }

    return parsed.format('E.164'); // Returns format: +15555551234
  } catch (error) {
    console.error('Phone number formatting error:', error.message);
    throw new Error(`Invalid phone number: ${phoneNumber}`);
  }
}

// Send SMS message
export async function sendSMS(to, body, options = {}) {
  try {
    // Format phone number
    const formattedTo = formatPhoneNumber(to);

    // Check opt-out status
    if (process.env.ENABLE_OPT_OUT_CHECK === 'true') {
      const optedOut = await isOptedOut(formattedTo);
      if (optedOut) {
        console.warn(`⚠️  Cannot send SMS to ${formattedTo}: Opted out`);
        throw new Error('OPTED_OUT');
      }
    }

    // Prepare message data
    const messageData = {
      to: formattedTo,
      body: body.trim(),
    };

    // Use Messaging Service or From number
    if (messagingServiceSid) {
      messageData.messagingServiceSid = messagingServiceSid;
    } else {
      messageData.from = twilioPhoneNumber;
    }

    // Add status callback if provided
    if (options.statusCallback) {
      messageData.statusCallback = options.statusCallback;
    }

    // Send message via Twilio
    const message = await client.messages.create(messageData);

    console.log(`✓ SMS sent: ${message.sid} to ${formattedTo}`);

    // Log to database
    await logSMSMessage({
      messageSid: message.sid,
      from: message.from,
      to: message.to,
      body: message.body,
      direction: 'outbound',
      status: message.status,
      crmContactId: options.crmContactId,
      crmType: options.crmType,
    });

    return {
      success: true,
      messageSid: message.sid,
      status: message.status,
      to: message.to,
    };
  } catch (error) {
    console.error('Error sending SMS:', error.message);

    // Log failed message
    if (error.code !== 'OPTED_OUT') {
      await logSMSMessage({
        messageSid: `failed-${Date.now()}`,
        from: twilioPhoneNumber,
        to: to,
        body: body,
        direction: 'outbound',
        status: 'failed',
        errorCode: error.code,
        errorMessage: error.message,
        crmContactId: options.crmContactId,
        crmType: options.crmType,
      });
    }

    return {
      success: false,
      error: error.message,
      code: error.code,
    };
  }
}

// Send bulk SMS (with rate limiting)
export async function sendBulkSMS(recipients, body, options = {}) {
  const results = {
    sent: 0,
    failed: 0,
    optedOut: 0,
    errors: [],
  };

  const rateLimit = parseInt(process.env.SMS_RATE_LIMIT_PER_SECOND) || 10;
  const delayMs = 1000 / rateLimit;

  for (const recipient of recipients) {
    try {
      const result = await sendSMS(recipient.phoneNumber, body, {
        crmContactId: recipient.contactId,
        crmType: options.crmType,
        statusCallback: options.statusCallback,
      });

      if (result.success) {
        results.sent++;
      } else if (result.code === 'OPTED_OUT') {
        results.optedOut++;
      } else {
        results.failed++;
        results.errors.push({
          phoneNumber: recipient.phoneNumber,
          error: result.error,
        });
      }

      // Rate limiting delay
      await new Promise(resolve => setTimeout(resolve, delayMs));
    } catch (error) {
      results.failed++;
      results.errors.push({
        phoneNumber: recipient.phoneNumber,
        error: error.message,
      });
    }
  }

  console.log(`Bulk SMS complete: ${results.sent} sent, ${results.failed} failed, ${results.optedOut} opted out`);

  return results;
}

// Handle opt-out keywords (STOP, UNSUBSCRIBE, etc.)
export async function handleOptOutKeywords(body, fromNumber) {
  const optOutKeywords = ['STOP', 'STOPALL', 'UNSUBSCRIBE', 'CANCEL', 'END', 'QUIT'];
  const upperBody = body.trim().toUpperCase();

  if (optOutKeywords.includes(upperBody)) {
    await addOptOut(fromNumber, 'User sent STOP keyword');
    console.log(`✓ Added opt-out for ${fromNumber}`);

    // Send confirmation
    await client.messages.create({
      to: fromNumber,
      from: twilioPhoneNumber,
      body: 'You have been unsubscribed from SMS messages. Reply START to resubscribe.',
    });

    return true;
  }

  return false;
}

// Verify webhook signature (security)
export function verifyTwilioSignature(req) {
  const signature = req.headers['x-twilio-signature'];
  const url = `${process.env.BASE_URL}${req.originalUrl}`;

  return twilio.validateRequest(authToken, signature, url, req.body);
}

export default {
  sendSMS,
  sendBulkSMS,
  formatPhoneNumber,
  handleOptOutKeywords,
  verifyTwilioSignature,
};

import axios from 'axios';
import dotenv from 'dotenv';

dotenv.config();

const HUBSPOT_API_KEY = process.env.HUBSPOT_API_KEY;
const CRM_TYPE = process.env.CRM_TYPE || 'hubspot';

// Get contact by ID
export async function getContact(contactId) {
  if (CRM_TYPE === 'hubspot') {
    return getHubSpotContact(contactId);
  }
  // Add other CRM types here
  throw new Error(`CRM type ${CRM_TYPE} not supported`);
}

// HubSpot: Get contact
async function getHubSpotContact(contactId) {
  try {
    const response = await axios.get(
      `https://api.hubapi.com/crm/v3/objects/contacts/${contactId}`,
      {
        params: {
          hapikey: HUBSPOT_API_KEY,
          properties: 'firstname,lastname,phone,email',
        },
      }
    );

    return {
      id: response.data.id,
      firstName: response.data.properties.firstname,
      lastName: response.data.properties.lastname,
      phone: response.data.properties.phone,
      email: response.data.properties.email,
    };
  } catch (error) {
    console.error('Error fetching HubSpot contact:', error.message);
    throw error;
  }
}

// Create activity/engagement in CRM
export async function logSMSActivity(contactId, messageBody, direction) {
  if (CRM_TYPE === 'hubspot') {
    return logHubSpotEngagement(contactId, messageBody, direction);
  }
  throw new Error(`CRM type ${CRM_TYPE} not supported`);
}

// HubSpot: Log SMS as engagement
async function logHubSpotEngagement(contactId, messageBody, direction) {
  try {
    const engagement = {
      engagement: {
        active: true,
        type: 'NOTE',
        timestamp: Date.now(),
      },
      associations: {
        contactIds: [contactId],
      },
      metadata: {
        body: `SMS ${direction === 'inbound' ? 'Received' : 'Sent'}: ${messageBody}`,
      },
    };

    const response = await axios.post(
      'https://api.hubapi.com/engagements/v1/engagements',
      engagement,
      {
        params: { hapikey: HUBSPOT_API_KEY },
      }
    );

    return response.data;
  } catch (error) {
    console.error('Error logging HubSpot engagement:', error.message);
    throw error;
  }
}

// Get contact by phone number
export async function getContactByPhone(phoneNumber) {
  if (CRM_TYPE === 'hubspot') {
    return getHubSpotContactByPhone(phoneNumber);
  }
  throw new Error(`CRM type ${CRM_TYPE} not supported`);
}

// HubSpot: Search contact by phone
async function getHubSpotContactByPhone(phoneNumber) {
  try {
    const response = await axios.post(
      'https://api.hubapi.com/crm/v3/objects/contacts/search',
      {
        filterGroups: [
          {
            filters: [
              {
                propertyName: 'phone',
                operator: 'EQ',
                value: phoneNumber,
              },
            ],
          },
        ],
        properties: ['firstname', 'lastname', 'phone', 'email'],
      },
      {
        params: { hapikey: HUBSPOT_API_KEY },
      }
    );

    if (response.data.results.length === 0) {
      return null;
    }

    const contact = response.data.results[0];
    return {
      id: contact.id,
      firstName: contact.properties.firstname,
      lastName: contact.properties.lastname,
      phone: contact.properties.phone,
      email: contact.properties.email,
    };
  } catch (error) {
    console.error('Error searching HubSpot contact:', error.message);
    return null;
  }
}

export default {
  getContact,
  getContactByPhone,
  logSMSActivity,
};

import express from 'express';
import { sendSMS, sendBulkSMS } from '../services/twilioService.js';
import { getContact } from '../services/crmService.js';
import { renderTemplate } from '../config/templates.js';
import { getMessagesByContact } from '../db/database.js';

const router = express.Router();

// POST /sms/send - Send single SMS
router.post('/send', async (req, res) => {
  try {
    const { to, body, contactId, templateId, variables } = req.body;

    if (!to || (!body && !templateId)) {
      return res.status(400).json({ error: 'to and body/templateId required' });
    }

    // Render template if provided
    let messageBody = body;
    if (templateId) {
      messageBody = renderTemplate(templateId, variables || {});
    }

    // Send SMS
    const result = await sendSMS(to, messageBody, {
      crmContactId: contactId,
      crmType: process.env.CRM_TYPE,
      statusCallback: `${process.env.BASE_URL}/webhooks/sms/status`,
    });

    res.json(result);
  } catch (error) {
    console.error('Send SMS error:', error);
    res.status(500).json({ error: error.message });
  }
});

// POST /sms/send-to-contact - Send SMS to CRM contact
router.post('/send-to-contact', async (req, res) => {
  try {
    const { contactId, body, templateId, variables } = req.body;

    if (!contactId || (!body && !templateId)) {
      return res.status(400).json({ error: 'contactId and body/templateId required' });
    }

    // Get contact from CRM
    const contact = await getContact(contactId);

    if (!contact || !contact.phone) {
      return res.status(404).json({ error: 'Contact not found or no phone number' });
    }

    // Render template with contact data
    let messageBody = body;
    if (templateId) {
      const templateVars = {
        firstName: contact.firstName,
        lastName: contact.lastName,
        ...variables,
      };
      messageBody = renderTemplate(templateId, templateVars);
    }

    // Send SMS
    const result = await sendSMS(contact.phone, messageBody, {
      crmContactId: contactId,
      crmType: process.env.CRM_TYPE,
    });

    res.json({
      ...result,
      contact: {
        id: contact.id,
        name: `${contact.firstName} ${contact.lastName}`,
        phone: contact.phone,
      },
    });
  } catch (error) {
    console.error('Send to contact error:', error);
    res.status(500).json({ error: error.message });
  }
});

// POST /sms/bulk - Send bulk SMS
router.post('/bulk', async (req, res) => {
  try {
    const { recipients, body, templateId, variables } = req.body;

    if (!recipients || !Array.isArray(recipients)) {
      return res.status(400).json({ error: 'recipients array required' });
    }

    // Render template if provided
    let messageBody = body;
    if (templateId) {
      messageBody = renderTemplate(templateId, variables || {});
    }

    // Send bulk
    const results = await sendBulkSMS(recipients, messageBody, {
      crmType: process.env.CRM_TYPE,
    });

    res.json(results);
  } catch (error) {
    console.error('Bulk SMS error:', error);
    res.status(500).json({ error: error.message });
  }
});

// GET /sms/conversation/:phone - Get conversation history
router.get('/conversation/:phone', async (req, res) => {
  try {
    const { phone } = req.params;

    const history = await getConversationHistory(phone);

    res.json({
      success: true,
      phone,
      messageCount: history.length,
      messages: history,
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

// GET /sms/contact/:contactId - Get messages for contact
router.get('/contact/:contactId', async (req, res) => {
  try {
    const { contactId } = req.params;

    const messages = await getMessagesByContact(contactId);

    res.json({
      success: true,
      contactId,
      messageCount: messages.length,
      messages,
    });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
});

export default router;

import express from 'express';
import {
  verifyTwilioSignature,
  handleOptOutKeywords,
  sendSMS,
} from '../services/twilioService.js';
import {
  logSMSMessage,
  updateMessageStatus,
  upsertConversation,
} from '../db/database.js';
import { getContactByPhone, logSMSActivity } from '../services/crmService.js';

const router = express.Router();

// POST /webhooks/sms/inbound - Handle incoming SMS
router.post('/inbound', async (req, res) => {
  try {
    // Verify webhook signature
    if (process.env.NODE_ENV === 'production') {
      const isValid = verifyTwilioSignature(req);
      if (!isValid) {
        console.error('Invalid Twilio signature');
        return res.status(403).send('Forbidden');
      }
    }

    const { MessageSid, From, To, Body, NumMedia } = req.body;

    console.log(`\n📱 Incoming SMS from ${From}: ${Body}`);

    // Handle opt-out keywords
    const isOptOut = await handleOptOutKeywords(Body, From);
    if (isOptOut) {
      return res.status(200).send('OK');
    }

    // Find contact in CRM by phone number
    const contact = await getContactByPhone(From);

    // Log message
    await logSMSMessage({
      messageSid: MessageSid,
      from: From,
      to: To,
      body: Body,
      direction: 'inbound',
      status: 'received',
      crmContactId: contact?.id,
      crmType: contact ? process.env.CRM_TYPE : null,
    });

    // Update conversation
    await upsertConversation(From, contact?.id, 'inbound');

    // Log activity in CRM
    if (contact) {
      await logSMSActivity(contact.id, Body, 'inbound');
      console.log(`✓ Logged SMS in CRM for contact ${contact.id}`);
    } else {
      console.warn(`⚠️  No CRM contact found for ${From}`);
    }

    // Auto-reply logic (customize based on your needs)
    if (Body.toLowerCase().includes('help')) {
      await sendSMS(From, 'Thanks for reaching out! A team member will respond shortly. For urgent matters, call us at (555) 123-4567.');
    }

    // Respond to Twilio webhook
    res.status(200).send('OK');
  } catch (error) {
    console.error('Inbound webhook error:', error);
    res.status(500).send('Error');
  }
});

// POST /webhooks/sms/status - Handle delivery status updates
router.post('/status', async (req, res) => {
  try {
    const { MessageSid, MessageStatus, ErrorCode, ErrorMessage } = req.body;

    console.log(`📊 Status update: ${MessageSid} → ${MessageStatus}`);

    // Update message status in database
    await updateMessageStatus(MessageSid, MessageStatus, ErrorCode, ErrorMessage);

    res.status(200).send('OK');
  } catch (error) {
    console.error('Status webhook error:', error);
    res.status(500).send('Error');
  }
});

// POST /webhooks/crm - Handle CRM events (e.g., new lead created)
router.post('/crm', async (req, res) => {
  try {
    const { event, contactId, dealId, properties } = req.body;

    console.log(`\n🔔 CRM webhook: ${event} for contact ${contactId}`);

    // Handle different CRM events
    switch (event) {
      case 'contact.created':
        // Send welcome SMS to new lead
        const contact = await getContact(contactId);
        if (contact && contact.phone) {
          await sendSMS(contact.phone, renderTemplate('new_lead', {
            firstName: contact.firstName,
            companyName: 'Your Company',
            repName: 'Sales Team',
          }), {
            crmContactId: contactId,
            crmType: process.env.CRM_TYPE,
          });
        }
        break;

      case 'deal.stage_changed':
        // Send SMS when deal moves to proposal stage
        if (properties.stage === 'proposal_sent') {
          const dealContact = await getContact(contactId);
          if (dealContact && dealContact.phone) {
            await sendSMS(dealContact.phone, renderTemplate('proposal_sent', {
              firstName: dealContact.firstName,
            }), {
              crmContactId: contactId,
            });
          }
        }
        break;

      default:
        console.log(`Unhandled event: ${event}`);
    }

    res.status(200).json({ success: true });
  } catch (error) {
    console.error('CRM webhook error:', error);
    res.status(500).json({ error: error.message });
  }
});

export default router;

import express from 'express';
import bodyParser from 'body-parser';
import dotenv from 'dotenv';
import { testConnection } from '../db/database.js';
import smsRoutes from '../routes/sms.js';
import webhookRoutes from '../routes/webhooks.js';

dotenv.config();

const app = express();
const PORT = process.env.PORT || 3000;

// Middleware
app.use(bodyParser.urlencoded({ extended: false }));
app.use(bodyParser.json());

// Health check
app.get('/health', (req, res) => {
  res.json({ status: 'healthy' });
});

// Routes
app.use('/api/sms', smsRoutes);
app.use('/webhooks', webhookRoutes);

// Start server
async function startServer() {
  try {
    await testConnection();

    app.listen(PORT, () => {
      console.log(`\n🚀 Twilio-CRM Integration Server running on http://localhost:${PORT}`);
      console.log(`\nAPI Endpoints:`);
      console.log(`  POST /api/sms/send - Send SMS`);
      console.log(`  POST /api/sms/send-to-contact - Send to CRM contact`);
      console.log(`  POST /api/sms/bulk - Send bulk SMS`);
      console.log(`\nWebhook Endpoints:`);
      console.log(`  POST /webhooks/sms/inbound - Incoming SMS`);
      console.log(`  POST /webhooks/sms/status - Delivery status`);
      console.log(`  POST /webhooks/crm - CRM events\n`);
    });
  } catch (error) {
    console.error('Failed to start:', error);
    process.exit(1);
  }
}

startServer();

npm run dev

curl -X POST http://localhost:3000/api/sms/send \
  -H "Content-Type: application/json" \
  -d '{
    "to": "+15555555555",
    "body": "Test message from Twilio-CRM integration",
    "contactId": "12345"
  }'

{
  "success": true,
  "messageSid": "SM1234567890abcdef",
  "status": "queued",
  "to": "+15555555555"
}

curl -X POST http://localhost:3000/api/sms/send \
  -H "Content-Type: application/json" \
  -d '{
    "to": "+15555555555",
    "templateId": "new_lead",
    "variables": {
      "firstName": "John",
      "companyName": "Acme Corp",
      "repName": "Sarah"
    }
  }'

psql -U your_username -d twilio_crm

SELECT message_sid, from_number, to_number, body, direction, status
FROM sms_messages
ORDER BY created_at DESC
LIMIT 5;

import { parsePhoneNumber } from 'libphonenumber-js';

function formatPhoneNumber(phone, defaultCountry = 'US') {
  try {
    const parsed = parsePhoneNumber(phone, defaultCountry);
    
    if (!parsed.isValid()) {
      throw new Error('Invalid phone number');
    }
    
    return parsed.format('E.164');
  } catch (error) {
    throw new Error(`Cannot format phone: ${phone}`);
  }
}

// ❌ Wrong formats
sendSMS('555-1234', 'message'); // Missing country code
sendSMS('(555) 123-4567', 'message'); // Not E.164

// ✅ Correct
sendSMS('+15551234567', 'message');
sendSMS(formatPhoneNumber('(555) 123-4567'), 'message');

SELECT * FROM sms_opt_outs WHERE phone_number = '+15551234567';

DELETE FROM sms_opt_outs WHERE phone_number = '+15551234567';

async function handleResubscribe(body, fromNumber) {
  const upperBody = body.trim().toUpperCase();
  
  if (upperBody === 'START' || upperBody === 'UNSTOP') {
    // Remove from opt-out list
    await pool.query('DELETE FROM sms_opt_outs WHERE phone_number = $1', [fromNumber]);
    
    // Send confirmation
    await sendSMS(fromNumber, 'You have been resubscribed to SMS messages. Reply STOP to unsubscribe.');
    
    return true;
  }
  
  return false;
}

import twilio from 'twilio';

function verifyTwilioSignature(req) {
  const signature = req.headers['x-twilio-signature'];
  
  // IMPORTANT: URL must match exactly what Twilio sees
  const url = `${process.env.BASE_URL}${req.originalUrl}`;
  
  // Body must be exactly as received
  const params = req.body;
  
  return twilio.validateRequest(
    process.env.TWILIO_AUTH_TOKEN,
    signature,
    url,
    params
  );
}

// In webhook route
router.post('/inbound', (req, res) => {
  if (process.env.NODE_ENV === 'production') {
    if (!verifyTwilioSignature(req)) {
      return res.status(403).send('Forbidden');
    }
  }
  
  // Process webhook...
});

   # ❌ Wrong
   BASE_URL=http://localhost:3000
   
   # ✅ Correct (with ngrok or production domain)
   BASE_URL=https://abc123.ngrok.io

   // Use urlencoded for Twilio webhooks
   app.use(bodyParser.urlencoded({ extended: false }));
```

3. **Trailing slash difference:**
```
   Twilio sends to: https://domain.com/webhooks/sms/inbound
   Your URL has:     https://domain.com/webhooks/sms/inbound/
   
   These don't match! Remove trailing slash.

  if (process.env.NODE_ENV === 'production' && !verifyTwilioSignature(req)) {
    return res.status(403).send('Forbidden');
  }

  // Always check opt-out before sending
  const optedOut = await isOptedOut(phoneNumber);
  if (optedOut) throw new Error('OPTED_OUT');
  
  // Handle STOP keyword immediately
  if (body.toUpperCase() === 'STOP') await addOptOut(fromNumber);

  import rateLimit from 'express-rate-limit';
  
  const smsLimiter = rateLimit({
    windowMs: 60 * 1000, // 1 minute
    max: 100, // Max 100 SMS per minute
  });
  
  app.use('/api/sms/', smsLimiter);

  function sanitizeMessage(body) {
    return body
      .trim()
      .slice(0, 1600) // SMS limit
      .replace(/[<>]/g, ''); // Remove HTML chars
  }

  if (process.env.NODE_ENV === 'production' && !req.secure) {
    return res.redirect('https://' + req.headers.host + req.url);
  }

  // ❌ Don't fetch all fields
  const contact = await getContact(contactId);
  
  // ✅ Fetch only what's needed
  const contact = await getContact(contactId, ['phone', 'firstName']);

  await logSMSMessage({
    messageSid,
    from,
    to,
    body,
    direction,
    status,
    crmContactId,
    timestamp: new Date(),
  });

  const SPAM_KEYWORDS = ['click here', 'verify account', 'urgent'];
  
  if (SPAM_KEYWORDS.some(keyword => body.toLowerCase().includes(keyword))) {
    console.warn('Potential spam detected');
    // Handle accordingly
  }

  if (result.status === 'failed') {
    // Send alert to ops team
    await sendAlert(`SMS failed to ${to}: ${result.error}`);
  }