Track SMS Delivery Status with Plivo, Node.js & Express: Complete Guide (2025)

+-------------------+       +-----------------+       +---------------------+
| Your Node.js App  | ----> |   Plivo API     | ----> | Mobile Carrier Network| ----> User's Phone
| (Express Server)  |       | (Send SMS)      |       |                     |
+-------------------+       +-----------------+       +---------------------+
       ^                          |
       |  (HTTP POST Callback)    | (Delivery Status Update)
       |                          V
+-------------------+       +-----------------+
| Webhook Endpoint  | <---- |   Plivo         |
| (/plivo/callback) |       | (Webhook Service)|
+-------------------+       +-----------------+

mkdir plivo-sms-callbacks
cd plivo-sms-callbacks

npm init -y

npm install express plivo dotenv sqlite3 helmet express-rate-limit

npm install --save-dev nodemon

plivo-sms-callbacks/
├── .env                # Environment variables (Auth ID, Token, etc.)
├── .gitignore          # Files/folders to ignore in Git
├── server.js           # Main application file (Express server)
├── package.json        # Project manifest
├── package-lock.json   # Dependency lock file (or yarn.lock)
├── db_setup.js         # (Optional) Script to set up the database schema
└── node_modules/       # Installed dependencies (managed by npm/yarn)

# Dependencies
node_modules/

# Environment Variables
.env

# Logs
*.log

# Operating system files
.DS_Store
Thumbs.db

# Build files
dist/
build/

# Ngrok binary (if downloaded locally)
ngrok
ngrok.exe

# Database file (if using SQLite)
*.db

# Plivo Credentials (Get from Plivo Console: https://console.plivo.com/dashboard/)
PLIVO_AUTH_ID=YOUR_PLIVO_AUTH_ID
PLIVO_AUTH_TOKEN=YOUR_PLIVO_AUTH_TOKEN

# Plivo Number (Must be SMS-enabled and owned by you)
PLIVO_NUMBER=+1########## # Use E.164 format (e.g., +12025551234)

# Application Configuration
PORT=3000
# BASE_URL needs to be set to your publicly accessible URL.
# Use your ngrok HTTPS URL during local development.
# For production, set this to your server's public domain/IP (HTTPS recommended).
# BASE_URL=YOUR_NGROK_OR_PUBLIC_URL # e.g., https://yourapp.ngrok.io or https://yourdomain.com
DATABASE_PATH=./messages.db # Path for SQLite database file

{
  "name": "plivo-sms-callbacks",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "start": "node server.js",
    "dev": "nodemon server.js",
    "test": "echo \"Error: no test specified\" && exit 1",
    "db:setup": "node db_setup.js"
  },
  "keywords": [],
  "author": "",
  "license": "ISC",
  "dependencies": {
    "dotenv": "^16.4.7",
    "express": "^5.1.0",
    "express-rate-limit": "^8.1.0",
    "helmet": "^8.1.0",
    "plivo": "^4.74.0",
    "sqlite3": "^5.1.7"
  },
  "devDependencies": {
    "nodemon": "^3.1.9"
  }
}

// server.js
require('dotenv').config(); // Load environment variables from .env file
const express = require('express');
const plivo = require('plivo');
const sqlite3 = require('sqlite3').verbose(); // Use verbose for more detailed logs
const helmet = require('helmet');
const rateLimit = require('express-rate-limit');

// --- Configuration ---
const PORT = process.env.PORT || 3000;
const PLIVO_AUTH_ID = process.env.PLIVO_AUTH_ID;
const PLIVO_AUTH_TOKEN = process.env.PLIVO_AUTH_TOKEN;
const PLIVO_NUMBER = process.env.PLIVO_NUMBER;
const BASE_URL = process.env.BASE_URL; // Crucial for callback URL construction
const DATABASE_PATH = process.env.DATABASE_PATH || './messages.db';

// Validate essential configuration
if (!PLIVO_AUTH_ID || !PLIVO_AUTH_TOKEN || !PLIVO_NUMBER) {
    console.error("Plivo Auth ID, Auth Token, or Number missing in .env file. Exiting.");
    process.exit(1);
}
if (!BASE_URL) {
    console.warn("Warning: BASE_URL is not set in .env file. Callbacks will likely fail unless set dynamically.");
    // In a real app, you might exit here too, or have a default fallback if appropriate.
    // process.exit(1);
}

// --- Database Setup ---
// Connect to SQLite database (or create it if it doesn't exist)
const db = new sqlite3.Database(DATABASE_PATH, (err) => {
    if (err) {
        console.error("Error opening database:", err.message);
        process.exit(1); // Exit if DB connection fails on startup
    } else {
        console.log("Connected to the SQLite database.");
        // Ensure the messages table exists (idempotent) – moved setup to db_setup.js
        // You might keep a check here or rely on the setup script.
        db.get("SELECT name FROM sqlite_master WHERE type='table' AND name='messages'", (err, row) => {
           if (err) {
                console.error("Error checking for messages table:", err.message);
           } else if (!row) {
                console.warn("Warning: 'messages' table not found. Run 'npm run db:setup' first.");
           } else {
                console.log("'messages' table found.");
           }
        });
    }
});

// --- Plivo Client Initialization ---
const plivoClient = new plivo.Client(PLIVO_AUTH_ID, PLIVO_AUTH_TOKEN);

// --- Express App Setup ---
const app = express();

// --- Security Middleware ---
// 1. Helmet: Sets various HTTP headers for security
app.use(helmet());

// 2. Body Parser: Use built-in Express methods (available since Express 4.16+)
//    Parse URL-encoded bodies (used by Plivo SMS callbacks)
app.use(express.urlencoded({ extended: true }));
// Parse JSON bodies (for routes expecting JSON, like /send-sms)
app.use(express.json());

// 3. Rate Limiting
const sendSmsLimiter = rateLimit({
    windowMs: 15 * 60 * 1000, // 15 minutes
    max: 100, // Limit each IP to 100 send requests per windowMs
    message: 'Too many SMS send requests from this IP. Try again after 15 minutes.',
    standardHeaders: true,
    legacyHeaders: false,
});

const callbackLimiter = rateLimit({
    windowMs: 1 * 60 * 1000, // 1 minute
    max: 500, // Allow more callbacks per minute
    message: 'Too many callback requests from this IP.',
    standardHeaders: true,
    legacyHeaders: false,
    // keyGenerator: (req, res) => req.header('X-Forwarded-For') || req.ip // Consider proxy headers
});

<!-- DEPTH: Rate limit values (100, 500) provided without justification - why these numbers? (Priority: Medium) -->
<!-- GAP: Missing guidance on how to calculate appropriate rate limits for different use cases (Type: Substantive) -->

// 4. Plivo Signature Validation Middleware (Applied per-route later)
// Note: SMS callbacks use X-Plivo-Signature-V2, not V3 (V3 is for voice)
const validatePlivoSignature = (req, res, next) => {
    const signature = req.header('X-Plivo-Signature-V2') || req.header('X-Plivo-Signature-V3');
    const nonce = req.header('X-Plivo-Signature-V3-Nonce');

    // --- Important: SMS callbacks use V2, Voice callbacks use V3 ---
    // For SMS, nonce is not typically provided (V2 validation)
    // For Voice, nonce is required (V3 validation)

    // --- Potential Issue with URL Construction ---
    // The simple BASE_URL + req.originalUrl might not correctly handle all edge cases,
    // especially if Plivo includes query parameters in the signature calculation but
    // they are not present in req.originalUrl in some proxy/framework setups.
    // A more robust method might involve inspecting req.protocol, req.get('host'),
    // and potentially reconstructing the URL more carefully if validation fails unexpectedly.
    // For most standard setups, the below should work.
    // Example Robust Construction (Conceptual):
    // const protocol = req.protocol;
    // const host = req.get('host'); // Includes port if non-standard
    // const fullUrl = `${protocol}://${host}${req.originalUrl}`;
    const url = BASE_URL + req.originalUrl; // Using the simpler method for this example

    const method = req.method; // Should be POST for callbacks

    // Log details for debugging (consider reducing verbosity in production)
    console.log(`Validating signature for URL: ${url}`);
    console.log(`Signature: ${signature}, Nonce: ${nonce || 'N/A'}`);

    if (!signature) {
         console.warn("Missing Plivo signature headers.");
         return res.status(400).send("Missing Plivo signature headers");
    }

    try {
        let valid = false;

        // Try V3 validation first (for voice callbacks or if V3 header is present)
        if (nonce && req.header('X-Plivo-Signature-V3')) {
            valid = plivo.validateRequestSignatureV3(url, nonce, signature, PLIVO_AUTH_TOKEN);
            console.log("Using V3 signature validation (Voice)");
        }
        // Fall back to V2 validation (for SMS callbacks)
        else {
            valid = plivo.validateSignature(url, req.body, signature, PLIVO_AUTH_TOKEN);
            console.log("Using V2 signature validation (SMS)");
        }

        if (valid) {
            console.log("Plivo signature validation successful.");
            next(); // Signature is valid, proceed to the route handler
        } else {
            console.warn("Plivo signature validation FAILED.");
            res.status(403).send("Invalid signature"); // Forbidden
        }
    } catch (error) {
        console.error("Error during Plivo signature validation:", error);
        res.status(500).send("Error validating signature");
    }
};

<!-- EXPAND: Add troubleshooting flowchart for signature validation failures (Type: Enhancement) -->

// --- Health Check Route ---
app.get('/health', (req, res) => {
    db.get("SELECT 1", (err) => {
        if (err) {
            console.error("Health check DB query failed:", err.message);
            res.status(503).json({ status: 'error', database: 'unhealthy' });
        } else {
            res.status(200).json({ status: 'ok', database: 'healthy' });
        }
    });
});

// (API Routes will be added in the next section)

// --- Start Server ---
// Capture the server instance for graceful shutdown
const server = app.listen(PORT, () => {
    console.log(`Server running on port ${PORT}`);
    console.log(`Plivo Number: ${PLIVO_NUMBER}`);
    console.log(`Node.js version: ${process.version}`);
    if (BASE_URL) {
        console.log(`Configured Base URL for callbacks: ${BASE_URL}`);
        console.log("Ensure this URL is publicly accessible via HTTPS (e.g., via ngrok or production deployment).");
    } else {
        console.error("FATAL: BASE_URL is not defined! Plivo callbacks require a public HTTPS URL.");
    }
    console.log(`Database Path: ${DATABASE_PATH}`);
});


// --- Graceful Shutdown Logic ---
const shutdown = (signal) => {
    console.info(`${signal} signal received. Closing application…`);

    // 1. Stop accepting new connections
    server.close((err) => {
        if (err) {
            console.error("Error closing HTTP server:", err);
        } else {
            console.log("HTTP server closed.");
        }

        // 2. Close database connection
        db.close((dbErr) => {
            if (dbErr) {
                console.error("Error closing database connection:", dbErr.message);
            } else {
                console.log("Database connection closed.");
            }

            // 3. Exit process
            console.log("Exiting process.");
            process.exit(err || dbErr ? 1 : 0); // Exit with error code if server/db closing failed
        });
    });

    // Force exit if server doesn't close gracefully within a timeout
    setTimeout(() => {
        console.error("Could not close connections gracefully in time. Forcing shutdown.");
        process.exit(1);
    }, 10000); // 10-second timeout
};

// Listen for termination signals
process.on('SIGTERM', () => shutdown('SIGTERM')); // Sent by process managers (like PM2, Kubernetes)
process.on('SIGINT', () => shutdown('SIGINT'));  // Sent on Ctrl+C

// Add these routes within server.js, after middleware setup and before app.listen

// --- API Routes ---

// 1. Endpoint to Send SMS
// POST /send-sms
// Body: { "to": "+1recipientNumber", "text": "Your message content" }
// Apply rate limiter to this route (express.json() already applied globally)
app.post('/send-sms', sendSmsLimiter, async (req, res) => {
    const { to, text } = req.body;

    if (!to || !text) {
        return res.status(400).json({ error: 'Missing "to" or "text" in request body.' });
    }
    if (!BASE_URL) {
         return res.status(500).json({ error: 'Server configuration error: BASE_URL not set.' });
    }

    // Basic 'to' number validation (enhance with libphonenumber-js for stricter validation)
    if (!/^\+[1-9]\d{1,14}$/.test(to)) {
         return res.status(400).json({ error: 'Invalid "to" phone number format. Use E.164 format (e.g., +12025551234).' });
    }

<!-- GAP: Phone number validation is basic - missing guidance on international format edge cases (Type: Substantive) -->
<!-- EXPAND: Add table of common phone number validation pitfalls by region (Type: Enhancement) -->

    // Construct the full callback URL dynamically (must be HTTPS for production)
    const callbackUrl = `${BASE_URL}/plivo/callback`;
    console.log(`Attempting to send SMS to: ${to}, Callback URL: ${callbackUrl}`);

    try {
        const response = await plivoClient.messages.create(
            PLIVO_NUMBER, // Sender ID (Your Plivo Number)
            to,          // Recipient Number
            text,        // Message Content
            { url: callbackUrl } // The crucial callback URL!
        );

        console.log("Plivo Send API Response:", JSON.stringify(response, null, 2));

        // Store initial message attempt in DB (status 'submitted')
        // Plivo returns messageUuid as an array
        const messageUuid = response.messageUuid && response.messageUuid.length > 0 ? response.messageUuid[0] : null;

        if (messageUuid) {
            db.run(`INSERT INTO messages (message_uuid, sender, recipient, message_text, status)
                    VALUES (?, ?, ?, ?, ?)`,
                   [messageUuid, PLIVO_NUMBER, to, text, 'submitted'], // Initial status
                   function(err) { // Use function() to access 'this.lastID' if needed
                        if (err) {
                           // Log DB error but don't fail the API response to the client
                           console.error(`DB Error inserting initial message record for UUID ${messageUuid}:`, err.message);
                        } else {
                           console.log(`Initial message record inserted, ID: ${this.lastID}, UUID: ${messageUuid}`);
                        }
                   });
        } else {
            console.warn("No MessageUUID received from Plivo Send API response. Cannot track status via DB.");
            // Consider how to handle this – the API call may have failed partially
            // The response object might contain error details.
        }

        // Respond 202 Accepted: Request received, processing initiated
        res.status(202).json({
            message: "SMS submission accepted by Plivo.",
            message_uuid: messageUuid, // Include UUID in response if available
            api_response: response // Include full Plivo response for client reference
        });

    } catch (error) {
        console.error("Error sending SMS via Plivo API:", error);
        // Provide a more generic error message to the client
        res.status(error.statusCode || 500).json({
             error: 'Failed to send SMS via Plivo.',
             details: error.message // Or use more generic message in production
        });
    }
});

<!-- DEPTH: Error handling logs full error but lacks guidance on WHEN to expose details vs hide them (Priority: Medium) -->

// 2. Endpoint to Receive Plivo Callbacks
// POST /plivo/callback
// This route MUST match the 'url' parameter sent in the /send-sms request
// Apply rate limiter and signature validation middleware ONLY to this route
app.post('/plivo/callback', callbackLimiter, validatePlivoSignature, (req, res) => {
    const callbackData = req.body;
    // Log cautiously in production – may contain sensitive info (MSISDNs)
    console.log("Received Plivo Callback Data:", JSON.stringify(callbackData, null, 2));

    const messageUuid = callbackData.MessageUUID;
    const status = callbackData.Status;
    const errorCode = callbackData.ErrorCode || null; // May not be present if successful

    if (!messageUuid) {
        console.warn("Callback received without MessageUUID. Ignoring.");
        // Respond 200 OK to Plivo even if we can't process, to prevent unnecessary retries
        return res.status(200).send('Callback received (ignored – missing MessageUUID).');
    }

<!-- DEPTH: Callback data structure not documented - what other fields are available? (Priority: High) -->
<!-- GAP: Missing complete callback payload example with all possible fields (Type: Critical) -->

    // Update the status in the database
    // Use CURRENT_TIMESTAMP for SQLite to record update time
    db.run(`UPDATE messages
            SET status = ?,
                plivo_error_code = ?,
                last_updated_at = CURRENT_TIMESTAMP
            WHERE message_uuid = ?`,
           [status, errorCode, messageUuid],
           function(err) { // Use function() to access 'this.changes'
                if (err) {
                    console.error(`DB Error updating status for UUID ${messageUuid}:`, err.message);
                    // Log the error, but still respond 200 OK to Plivo below
                    // Consider adding to an error queue for later investigation if critical
                } else if (this.changes === 0) {
                    // This means no row was found with that UUID
                    // This could happen if the initial DB insert failed or if the UUID is wrong
                    console.warn(`No message found in DB with UUID ${messageUuid} to update status to '${status}'.`);
                    // Optional: Insert a new record here if desirable, though usually indicates an issue
                } else {
                    console.log(`Successfully updated status for UUID ${messageUuid} to '${status}'`);
                }
           });

    // IMPORTANT: Respond quickly to Plivo with a 200 OK
    // Plivo expects a fast confirmation (< 15s typically)
    // Perform lengthy processing asynchronously (e.g., push to a queue) if needed
    res.status(200).send('Callback received and acknowledged.');
});

// (Make sure app.listen and graceful shutdown logic are *after* these routes)

// db_setup.js
require('dotenv').config();
const sqlite3 = require('sqlite3').verbose();

const DATABASE_PATH = process.env.DATABASE_PATH || './messages.db';

const db = new sqlite3.Database(DATABASE_PATH, (err) => {
    if (err) {
        console.error("Error opening database for setup:", err.message);
        process.exit(1); // Exit if cannot open DB for setup
    } else {
        console.log(`Connected to the SQLite database '${DATABASE_PATH}' for setup.`);
    }
});

// Use serialize to run commands sequentially
db.serialize(() => {
    console.log("Running database schema setup...");

    // Create the main messages table if it doesn't exist
    db.run(`
    CREATE TABLE IF NOT EXISTS messages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        message_uuid TEXT UNIQUE NOT NULL, -- Plivo's unique ID for the message
        sender TEXT,                       -- Sender number/ID used
        recipient TEXT NOT NULL,           -- Destination number
        message_text TEXT,                 -- Content of the message
        status TEXT NOT NULL,              -- Delivery status (e.g., submitted, queued, sent, delivered, failed, undelivered)
        plivo_error_code INTEGER,          -- Plivo's error code if status is failed/undelivered
        submitted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, -- When the app tried to send it
        last_updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP -- When the last callback was processed
    );`, (err) => {
        if (err) {
            console.error("Error creating 'messages' table:", err.message);
        } else {
            console.log("'messages' table checked/created successfully.");
        }
    });

    // Add an index on message_uuid for faster lookups during updates (essential)
    db.run(`CREATE UNIQUE INDEX IF NOT EXISTS idx_message_uuid ON messages (message_uuid);`, (err) => {
        if (err) {
            console.error("Error creating index on 'message_uuid':", err.message);
        } else {
            console.log("Index 'idx_message_uuid' checked/created successfully.");
        }
    });

    // Optional: Add an index on status for querying messages by status
    db.run(`CREATE INDEX IF NOT EXISTS idx_status ON messages (status);`, (err) => {
        if (err) {
            console.error("Error creating index on 'status':", err.message);
        } else {
            console.log("Index 'idx_status' checked/created successfully.");
        }
    });

    // Optional: Add an index on last_updated_at for time-based queries
    db.run(`CREATE INDEX IF NOT EXISTS idx_last_updated ON messages (last_updated_at);`, (err) => {
        if (err) {
            console.error("Error creating index on 'last_updated_at':", err.message);
        } else {
            console.log("Index 'idx_last_updated' checked/created successfully.");
        }
    });

}); // End of db.serialize

// Close the database connection after setup commands are queued/run
db.close((err) => {
    if (err) {
        console.error("Error closing database connection after setup:", err.message);
    } else {
        console.log("Database setup complete. Connection closed.");
    }
});

npm run db:setup

npm run dev

ngrok http 3000

BASE_URL=https://abc123.ngrok.io

curl -X POST http://localhost:3000/send-sms \
  -H "Content-Type: application/json" \
  -d '{"to": "+1RECIPIENT", "text": "Test message"}'