Production-Ready Guide: Node.js Express SMS Delivery Status & Callbacks with Plivo - code-examples -

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: To 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.
  • Express.js: Minimalist web framework for Node.js, used to create the API endpoint for callbacks.
  • Plivo Node.js SDK: Simplifies interaction with the Plivo REST API for sending messages and validating callbacks.
  • 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 will include a basic SQLite example).

System Architecture:

+-------------------+       +-----------------+       +---------------------+
| 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)|
+-------------------+       +-----------------+
  1. Your application uses the Plivo Node.js SDK to make an API call to Plivo to send an SMS. You 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:

1. Setting up the Project

Let's initialize our 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: This creates a package.json file to manage dependencies and project metadata.

npm init -y

3. Install Dependencies:

  • express: Web framework.
  • plivo: Plivo Node.js SDK.
  • dotenv: Loads environment variables from .env file.
  • body-parser: Middleware to parse incoming request bodies (needed for Plivo callbacks).
  • sqlite3 (Optional_ for database persistence): Driver for SQLite.
  • helmet: Basic security headers middleware.
  • express-rate-limit: Middleware for rate limiting.
npm install express plivo dotenv body-parser sqlite3 helmet express-rate-limit

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

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:

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)

6. Configure .gitignore: Create a .gitignore file in the root directory and add the following 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 will store sensitive credentials and configuration. Never commit this file to version control.

Remember to 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 are 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"": {
    ""body-parser"": ""^1.20.2"",
    ""dotenv"": ""^16.3.1"",
    ""express"": ""^4.18.2"",
    ""express-rate-limit"": ""^7.1.5"",
    ""helmet"": ""^7.1.0"",
    ""plivo"": ""^4.35.0"",
    ""sqlite3"": ""^5.1.6""
  },
  ""devDependencies"": {
    ""nodemon"": ""^3.0.2""
  }
}

Now you can run npm run dev to start the server, which will automatically restart when you save changes, and npm run db:setup to initialize the database schema.

2. Implementing the Express Server

First, let's set up the basic Express server structure, load configuration, initialize the database connection, and set up middleware.

File: server.js (Initial Setup Part)

// server.js
require('dotenv').config(); // Load environment variables from .env file
const express = require('express');
const bodyParser = require('body-parser');
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: Parse URL-encoded bodies (used by Plivo callbacks)
//    Must come before routes that need the parsed body.
app.use(bodyParser.urlencoded({ extended: true }));
// Use express.json() for routes expecting JSON bodies (like our /send-sms)
// Can be applied globally or per-route. Applying per-route below for clarity.

// 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 created from this IP, please 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 received from this IP.',
    standardHeaders: true,
    legacyHeaders: false,
    // keyGenerator: (req, res) => req.header('X-Forwarded-For') || req.ip // Consider proxy headers
});

// 4. Plivo Signature Validation Middleware (Applied per-route later)
const validatePlivoSignature = (req, res, next) => {
    const signature = req.header('X-Plivo-Signature-V3');
    const nonce = req.header('X-Plivo-Signature-V3-Nonce');

    // --- 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(`Nonce: ${nonce}, Signature: ${signature}`);

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

    try {
        // Use your Plivo Auth Token here
        const valid = plivo.validateRequestSignatureV3(url, nonce, signature, PLIVO_AUTH_TOKEN);

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

// --- 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}`);
    if (BASE_URL) {
        console.log(`Configured Base URL for callbacks: ${BASE_URL}`);
        console.log(""Ensure this URL is publicly accessible (e.g., via ngrok or production deployment)."");
    } else {
        console.error(""FATAL: BASE_URL is not defined! Plivo callbacks require a public 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

3. Sending SMS and Handling Callbacks

Now, let's 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 and JSON body parser to this route
app.post('/send-sms', sendSmsLimiter, express.json(), 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 (can be enhanced with libphonenumber-js)
    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).' });
    }

    // Construct the full callback URL dynamically
    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' or similar)
        // Plivo usually 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 - maybe the API call 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 more generic message in production
        });
    }
});

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

    // 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 and express.json() middleware.
    • 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 like 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.
    • Logs the received callback data (req.body).
    • Extracts MessageUUID_ Status_ and ErrorCode.
    • Updates the corresponding message record in the database using the MessageUUID. It 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_ it 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.

4. Database Schema and Data Layer

We need a way to store the message status. Here's the setup script for our SQLite database.

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.

5. Integrating with 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&gt;.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.

6. Error Handling, Logging, and Retry Mechanisms

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 body-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.

Logging:

  • Uses console.log, console.warn, console.error.
  • Production Logging: Replace console.* with a structured logging library like Winston or Pino for features like:
    • Log levels (info, warn, error, debug).
    • Structured formats (JSON).
    • Log rotation and transport to files or logging services (like Datadog, Loggly, ELK stack).