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

Master SMS delivery tracking with Plivo webhooks to monitor message lifecycle, handle failures, and improve deliverability. This guide shows you how to build a production-ready webhook server using Node.js v22 LTS, Express 5.1, and the Plivo Node.js SDK v4.74.0 with signature validation, database persistence, and comprehensive error handling.

Quick Reference: Plivo SMS Delivery Tracking

Message Status Flow: queued → sent → delivered / failed / undelivered

Key Components:

• Webhook endpoint to receive delivery callbacks
• Signature validation (V2 for SMS, V3 for Voice)
• Database persistence (SQLite example)
• Rate limiting and security headers
• Graceful shutdown handling

Callback URL Configuration: Per-message: message_create({ dst, src, text, url }) or Application-level: Plivo console

Required Technologies:

• Node.js v22 LTS (October 2024, supported until April 2027)
• Express 5.1.0 (requires Node.js 18+)
• Plivo Node.js SDK v4.74.0
• SQLite3 v5.1.7 for persistence

Security: HMAC SHA-256 signature validation with X-Plivo-Signature-V2 header (SMS) or X-Plivo-Signature-V3 (Voice)

What is SMS Delivery Status Tracking?

Tracking the delivery status of SMS messages is crucial for many applications, from ensuring critical alerts are received to managing marketing campaign effectiveness and enabling reliable two-way communication. Simply sending a message isn't enough; you need confirmation that it reached (or failed to reach) the intended recipient.

This guide provides a complete walkthrough for building a production-ready system using Node.js, Express, and Plivo to send SMS messages and reliably receive delivery status updates via webhooks (callbacks). We'll cover everything from initial setup and core implementation to security, error handling, database persistence, and deployment.

Project Goal: Build a Node.js application that can:

1. Send SMS messages using the Plivo API.
2. Expose a secure webhook endpoint to receive delivery status callbacks from Plivo.
3. Process and store these delivery statuses for later analysis or action.
4. Handle potential errors and ensure reliable operation.

Technologies Used:

• Node.js: Asynchronous JavaScript runtime for building the backend server. Recommended: Node.js v22 LTS (October 2024 release, supported until April 2027) or Node.js v24 (enters LTS October 2025).
• Express.js: Minimalist web framework for Node.js, used to create the API endpoint for callbacks. Current version: Express 5.1.0 (default on npm as of March 2025), requires Node.js 18+.
• Plivo Node.js SDK: Simplifies interaction with the Plivo REST API for sending messages and validating callbacks. Current version: v4.74.0 (as of January 2025).
• Plivo Communications Platform: Provides the SMS sending infrastructure and webhook callback mechanism.
• dotenv: Module to load environment variables from a .env file.
• ngrok (for local development): Exposes local servers to the public internet, enabling Plivo to send callbacks to your development machine.
• (Optional) Database: Such as PostgreSQL, MySQL, MongoDB, or even SQLite for storing message status information persistently. (This guide includes a basic SQLite example).

System Architecture:

1. Your application uses the Plivo Node.js SDK to make an API call to Plivo to send an SMS. Include a url parameter in this request, specifying where Plivo should send status updates.
2. Plivo accepts the request, assigns a unique Message UUID (Universal Unique Identifier), and attempts to deliver the SMS via the carrier network.
3. As the status of the message changes (e.g., queued, sent, delivered, failed, undelivered), Plivo sends an HTTP POST request containing the status details to the url you provided (your webhook endpoint).
4. Your Express application receives this POST request, validates it, parses the data, and takes appropriate action (e.g., logging, updating a database).

Prerequisites:

• Node.js v18 or later (v22 LTS recommended) and npm (or yarn) installed. Install Node.js
• A Plivo account. Sign up for Plivo
• ngrok installed for local development. Install ngrok
• Basic understanding of JavaScript, Node.js, Express, and REST APIs.

How Do You Set Up the Project?

Initialize your Node.js project and install the necessary dependencies.

1. Create Project Directory: Open your terminal and create a new directory for the project, then navigate into it.

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

2. Initialize Node.js Project: Create a package.json file to manage dependencies and project metadata.

npm init -y

3. Install Dependencies:

• express: Web framework (v5.1.0+ recommended, requires Node.js 18+).
• plivo: Plivo Node.js SDK (v4.74.0 as of January 2025).
• dotenv: Loads environment variables from .env file.
• Note on body-parser: Since Express 4.16+, body parsing is built into Express via express.json() and express.urlencoded(). You don't need the separate body-parser package for most applications. This guide uses the built-in Express methods.
• sqlite3 (Optional, for database persistence): Driver for SQLite.
• helmet: Basic security headers middleware (v8.1.0 as of January 2025).
• express-rate-limit: Middleware for rate limiting (v8.1.0 as of January 2025).

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

(or using yarn: yarn add express plivo dotenv sqlite3 helmet express-rate-limit)

Important: If you're using Express 4.16 or later, you don't need to install body-parser separately. Use express.json() and express.urlencoded() instead.

4. Install Development Dependency (Optional but recommended):

• nodemon: Automatically restarts the server on file changes during development.

npm install --save-dev nodemon

(or using yarn: yarn add --dev nodemon)

5. Create Project Structure: Create the following files and directories:

6. Configure .gitignore: Create a .gitignore file in the root directory and add these lines to prevent sensitive information and unnecessary files from being committed to version control:

# 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

7. Configure Environment Variables (.env): Create a file named .env in the project root. This file stores sensitive credentials and configuration. Never commit this file to version control.

Replace YOUR_PLIVO_AUTH_ID, YOUR_PLIVO_AUTH_TOKEN, and the placeholder phone number (+1##########) with your actual Plivo credentials and number. Also, update BASE_URL with your ngrok or public URL when running the application.

# 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

• PLIVO_AUTH_ID / PLIVO_AUTH_TOKEN: Find these on your Plivo Console dashboard. They're essential for authenticating API requests.
• PLIVO_NUMBER: An SMS-enabled phone number you've rented through the Plivo Console (Phone Numbers > Buy Numbers). This will be the sender ID for messages to US/Canada.
• PORT: The port your Express server will listen on.
• BASE_URL: The publicly accessible base URL for your server. Plivo needs this to send callbacks. Crucially, update this placeholder with your actual ngrok URL during development or your public domain in production.
• DATABASE_PATH: Location where the SQLite database file will be stored.

8. (Optional) Configure nodemon: Add scripts to your package.json to easily run the server with nodemon and set up the database.

{
  "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"
  }
}

Note: Package versions updated as of January 2025. Always check npm for the latest stable releases.

Now you can run npm run dev to start the server (automatically restarts when you save changes) and npm run db:setup to initialize the database schema.

How Do You Implement the Express Server?

Set up the basic Express server structure, load configuration, initialize the database connection, and configure middleware.

File: server.js (Initial Setup Part)

// 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

How Do You Send SMS Messages and Handle Callbacks?

Add the core API routes: one to trigger sending an SMS and another to receive the delivery status callbacks from Plivo.

File: server.js (Adding API Routes)

// 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)

Explanation:

1. /send-sms Route (POST):

   • Applies the sendSmsLimiter middleware (express.json() already applied globally).
   • Validates presence of to and text, checks BASE_URL configuration, and performs basic E.164 format check on to.
   • Constructs the callbackUrl.
   • Calls plivoClient.messages.create, passing the sender, recipient, text, and the critical url: callbackUrl option.
   • Logs the API response.
   • Extracts the messageUuid from the response.
   • Inserts a record into the messages database table with the message_uuid and an initial status of submitted. Handles potential DB errors gracefully (logs error but doesn't fail the user request).
   • Responds with 202 Accepted, including the messageUuid if available.
   • Includes error handling for the Plivo API call itself, returning appropriate status codes.

2. /plivo/callback Route (POST):

   • Protected by both callbackLimiter and validatePlivoSignature middleware.
   • Important: SMS callbacks use X-Plivo-Signature-V2 header (not V3). The validation middleware now handles both V2 (SMS) and V3 (Voice) signatures appropriately.
   • Logs the received callback data (req.body).
   • Extracts MessageUUID, Status, and ErrorCode.
   • Updates the corresponding message record in the database using the MessageUUID. Sets the new status, plivo_error_code, and updates last_updated_at. Handles cases where the UUID might not be found or DB errors occur.
   • Crucially, responds with res.status(200).send(...) quickly. This acknowledges receipt to Plivo, preventing retries. Any time-consuming logic based on the status should be handled asynchronously after sending this response.

How Should You Store Message Status Updates?

Persist delivery data to track history, analyze patterns, and comply with audit requirements. This example uses SQLite3 for local development. For production, migrate to PostgreSQL or MongoDB.

File: db_setup.js (Utility script)

This script explicitly creates the table and indices. Run this once (npm run db:setup) before starting the server for the first time or whenever the schema needs creation/update.

// 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.");
    }
});

Running the Setup:

npm run db:setup

Explanation:

• Schema: Defines the messages table with relevant columns for tracking SMS details and status. message_uuid is UNIQUE NOT NULL.
• Indices: Creates indices on message_uuid (critical for UPDATE performance in the callback handler), status, and last_updated_at to speed up common queries. CREATE INDEX IF NOT EXISTS ensures idempotency.
• Data Layer: The data access logic is currently embedded directly within the server.js route handlers using the sqlite3 driver. For larger applications, abstract this into separate data access modules or use an ORM (Object-Relational Mapper) like Sequelize or Prisma.
• Performance/Scale (SQLite): Suitable for development and moderate load. High-concurrency applications should consider PostgreSQL or MySQL in production.

How Do You Integrate with the Plivo Service?

We've used the SDK, but let's clarify the necessary Plivo Console configuration.

1. Obtain Credentials & Number:

• Log in to your Plivo Console.
• Find your Auth ID and Auth Token on the Dashboard. Copy these into your .env file (PLIVO_AUTH_ID, PLIVO_AUTH_TOKEN).
• Go to Phone Numbers -> Buy Numbers. Purchase an SMS-enabled number. Copy it (in E.164 format, e.g., +12025551234) into PLIVO_NUMBER in .env.
  • Trial Account Note: You can only send to numbers verified under Phone Numbers -> Sandbox Numbers.

2. Configure Callback URL Handling:

• Method Used: Per-Message URL (Implemented in server.js)

  • Our code provides the url: callbackUrl parameter in the client.messages.create call. This tells Plivo exactly where to send status updates for that specific message.
  • This is the recommended approach for flexibility and explicit control.
  • No Plivo Application setup is strictly required for callbacks when using this method.

• Alternative Method: Plivo Application Message URL (Global Default)

  • If you omit the url parameter when sending, Plivo uses the ""Message URL"" configured in a Plivo Application linked to the sender number (PLIVO_NUMBER).
  • Setup:
    1. Go to Messaging -> Applications in the Plivo Console.
    2. Create or edit an Application.
    3. Set the Message URL field to your publicly accessible callback endpoint (e.g., https://<your-ngrok-subdomain>.ngrok.io/plivo/callback or https://yourdomain.com/plivo/callback). Set method to POST.
    4. Go to Phone Numbers -> Your Numbers, find your PLIVO_NUMBER, and link it to this Application.
  • This sets a default but is less explicit than the per-message URL.

Recommendation: Stick with the Per-Message URL approach implemented in the code.

How Do You Handle Errors and Troubleshoot Issues?

Robust applications need solid error handling and observability.

Error Handling:

• API Call Errors (/send-sms): The try...catch block handles errors from plivoClient.messages.create (network, auth, invalid input, etc.). Logs the error server-side and returns an appropriate HTTP status (e.g., 500, 400) to the client initiating the send request.
• Callback Processing Errors (/plivo/callback):
  • Database errors during update are logged (console.error), but the route still returns 200 OK to Plivo. This prevents Plivo from retrying due to our internal processing failure. Critical failures (like inability to parse body, though Express's built-in parser handles most) could warrant a 4xx/5xx, but acknowledging receipt (200 OK) is generally preferred.
  • Signature validation failures correctly return 403 Forbidden.
• Input Validation (/send-sms): Checks for presence of to/text and basic to format. Returns 400 Bad Request on failure. Consider using libphonenumber-js for stricter phone number validation.
• Plivo Error Codes: The callback data includes ErrorCode on failed/undelivered status. Store this code and refer to Plivo SMS Error Code Documentation to understand failure reasons.

Sources: Plivo Node.js SDK v4.74.0 (npm, January 2025), Express.js 5.1.0 release notes (March 2025), Node.js v22 LTS announcement (October 2024), Plivo signature validation documentation (2024), helmet v8.1.0 and express-rate-limit v8.1.0 (npm, January 2025)

How Do You Test Webhooks Locally with ngrok?

Plivo sends callbacks to publicly accessible URLs. Use ngrok to expose your local server during development:

1. Install ngrok: Download from ngrok.com and follow installation instructions.

2. Start Your Server:

npm run dev

Your server runs on http://localhost:3000 by default.

3. Start ngrok: Open a new terminal and run:

ngrok http 3000

ngrok provides a public HTTPS URL (e.g., https://abc123.ngrok.io) that forwards to your local server.

4. Update BASE_URL: Copy the ngrok HTTPS URL and update your .env file:

BASE_URL=https://abc123.ngrok.io

5. Restart Your Server: Stop and restart npm run dev to load the new BASE_URL.

6. Send Test SMS:

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

Plivo sends callbacks to https://abc123.ngrok.io/plivo/callback, which ngrok forwards to your local server.

What Production Best Practices Should You Follow?

Deploy your webhook server with these production-ready patterns:

1. Use Environment-Specific Configuration:

• Separate .env files for development, staging, production
• Never commit .env files to version control
• Use secret management services (AWS Secrets Manager, HashiCorp Vault)

2. Implement Robust Logging:

• Use structured logging libraries (Winston, Pino)
• Log all webhook receipt and processing events
• Integrate with centralized logging (CloudWatch, Datadog, Loggly)
• Sanitize logs to prevent leaking sensitive data (phone numbers, message content)

3. Add Health Checks and Monitoring:

• Implement /health endpoint (already included)
• Add /readiness endpoint for Kubernetes/load balancer checks
• Monitor webhook latency, error rates, and database connection health
• Set up alerts for signature validation failures and high error rates

4. Scale Horizontally:

• Deploy behind load balancers (ALB, NGINX)
• Use stateless design (database for persistence, not memory)
• Implement connection pooling for database access
• Consider queue systems (Redis, SQS) for async processing

5. Secure Your Endpoints:

• Always use HTTPS in production (Let's Encrypt, AWS Certificate Manager)
• Implement IP whitelisting for Plivo webhook IPs if possible
• Use stronger rate limiting per user/account, not just per IP
• Regularly rotate auth tokens and credentials

6. Optimize Database:

• Migrate from SQLite to PostgreSQL or MongoDB for production
• Implement connection pooling (pg-pool, mongoose)
• Add database indexes on frequently queried columns
• Archive old message records to separate tables

7. Implement Graceful Degradation:

• Use circuit breakers for external service calls
• Implement retry logic with exponential backoff
• Queue failed webhook processing for retry
• Maintain fallback mechanisms for critical operations

Frequently Asked Questions

How do Plivo SMS delivery callbacks work?

Plivo sends HTTP POST requests to your webhook URL when message status changes (queued → sent → delivered/failed). Your server receives the callback, validates the signature using HMAC SHA-256 with your auth token, and processes the status update. Configure callbacks per-message via the url parameter in message_create() or set application-level defaults in the Plivo console.

What's the difference between X-Plivo-Signature-V2 and V3?

Plivo uses X-Plivo-Signature-V2 for SMS callbacks and X-Plivo-Signature-V3 for voice callbacks. SMS webhooks validate using validateSignature(url, body, signature, auth_token), while voice uses validateRequestSignatureV3(url, nonce, signature, auth_token) with an additional nonce header. Always check which header is present to use the correct validation method.

Why is my webhook signature validation failing?

Common causes: (1) Incorrect auth token in .env, (2) URL mismatch between ngrok/deployed URL and Plivo configuration, (3) Using wrong validation method (V2 vs V3), (4) Body parsing middleware not applied before validation, (5) Request body modified before validation. Verify your auth token, ensure the exact URL matches, and validate before any body transformation.

How do I retry failed SMS messages?

Check the Status field in callbacks. For failed or undelivered, inspect ErrorCode to determine if retry is appropriate. Don't retry 30000-30049 (invalid numbers) or 30050-30099 (carrier rejections). Retry network errors (30100-30199) with exponential backoff. Store failed messages in your database and implement a scheduled retry job with maximum attempt limits.

Can I use this code with Express 4.x?

Yes. Change package.json dependency to "express": "^4.21.2" (latest 4.x version). Express 4.16+ includes body parsing via express.json() and express.urlencoded(), so no changes needed to the middleware code. Express 5.1 requires Node.js 18+, while Express 4.x supports older Node.js versions down to v12 (check specific version requirements).

How do I handle duplicate callback requests?

Plivo may send duplicate callbacks during network issues. Implement idempotency by checking if the MessageUUID already exists in your database before processing. Use database constraints (UNIQUE on message_uuid) or application-level checks. Store callback timestamps to identify and skip duplicate recent requests within a time window (e.g., 5 minutes).

What database should I use for production?

This guide uses SQLite3 for simplicity. For production, migrate to PostgreSQL (structured, ACID-compliant, excellent query performance) or MongoDB (flexible schema, horizontal scaling). PostgreSQL is recommended for transactional systems requiring complex queries and joins. MongoDB works well for high-write-volume logging with flexible event schemas.

How do I scale webhook handling for high volume?

Implement these patterns: (1) Use queue systems (Redis, RabbitMQ, AWS SQS) to decouple webhook receipt from processing, (2) Scale horizontally with load balancers and multiple server instances, (3) Optimize database writes with batch inserts and connection pooling, (4) Cache frequently accessed data with Redis, (5) Use async processing for non-critical operations, (6) Monitor with APM tools (New Relic, Datadog) to identify bottlenecks.

Do I need separate endpoints for SMS and voice callbacks?

Not required but recommended for clarity. Use /api/webhooks/sms for SMS delivery status and /api/webhooks/voice for voice events. This makes debugging easier and allows different validation logic, rate limits, and processing workflows. Apply signature validation middleware per-route based on expected callback type (V2 for SMS, V3 for voice).

How long should I wait before marking a message as failed?

Plivo typically delivers status updates within seconds to minutes. If no delivered status arrives within 24-48 hours, consider the message failed. Implement a scheduled job that queries messages stuck in sent status beyond your threshold and marks them as failed. Check Plivo's delivery logs via the API or console for additional context before taking action.

What Should You Do Next?

You've built a production-ready SMS delivery tracking system with Plivo webhooks, Node.js, and Express. Next steps:

1. Deploy to Production: Move from ngrok to a production environment (AWS, Heroku, DigitalOcean) with a proper domain and SSL certificate
2. Upgrade Database: Migrate from SQLite to PostgreSQL or MongoDB for production durability and scalability
3. Add Monitoring: Integrate application performance monitoring (APM) tools to track webhook latency, failure rates, and error patterns
4. Implement Retry Logic: Build automated retry workflows for failed messages with exponential backoff and maximum attempt limits
5. Create Dashboards: Visualize delivery metrics, success rates, and error trends to optimize your SMS strategy
6. Review Security: Conduct a security audit of your webhook endpoint, validate all inputs, and implement additional rate limiting per user/account
7. Test Failure Scenarios: Simulate network failures, invalid signatures, malformed payloads, and high-volume spikes to verify error handling

Additional Resources:

• Plivo SMS API Documentation
• Plivo Node.js SDK GitHub
• Express.js 5.x Documentation
• Node.js v22 LTS Release Notes

Start tracking delivery status today to improve reliability, debug issues faster, and deliver better messaging experiences.