This guide provides a comprehensive, step-by-step tutorial for building a production-ready Node.js application using the Express framework to send SMS messages, receive inbound SMS, and track message delivery statuses via webhooks using the Vonage Messages API.

We'll cover everything from project setup and core functionality implementation to security, error handling, and deployment considerations. By the end, you'll have a robust system capable of reliably handling SMS communication flows.

Project overview and goals

This project aims to create a Node.js service that can:

1. Send SMS messages: Programmatically send SMS messages to specified phone numbers using the Vonage Messages API.
2. Receive inbound SMS messages: Handle incoming SMS messages sent to a dedicated Vonage virtual number via a webhook.
3. Track SMS delivery status: Receive real-time delivery receipts (DLRs) for outbound messages via a status webhook.

Problem Solved: This application addresses the need for real-time, two-way SMS communication with status tracking, crucial for applications like appointment reminders, order confirmations, two-factor authentication (2FA), and customer support notifications where delivery confirmation is essential.

Technologies Used:

• Node.js: A JavaScript runtime environment chosen for its asynchronous, event-driven nature, well-suited for handling concurrent webhook requests and I/O operations efficiently. Popular within the JavaScript ecosystem.
• Express.js: A minimal and flexible Node.js web application framework used to quickly set up the web server and API endpoints required for Vonage webhooks.
• Vonage Messages API: A unified API for sending and receiving messages across various channels (SMS, MMS, WhatsApp, etc.). We focus on SMS for its ubiquity and reliability. It provides robust features including delivery receipts.
• @vonage/server-sdk: The official Vonage Node.js SDK simplifies interaction with the Vonage APIs.
• ngrok: A utility to expose local development servers to the internet, essential for testing Vonage webhooks during development.
• dotenv: A module to load environment variables from a .env file into process.env, keeping sensitive credentials out of source code.

System Architecture:

+-----------------+      +---------------------+      +--------------------+      +-----------------+
| User / System   |----->| Your Node.js App    |----->| Vonage Messages API|----->| Carrier Network |
| (Triggers Send) |      | (Express Server)    |      | (sends SMS)        |      | (delivers SMS)  |
+-----------------+      | - Sends SMS         |      +--------------------+      +-------+---------+
                       | - /send API         |             |    ^                      |
                       | - /webhooks/inbound |<------------+    | (Inbound SMS)        | (Recipient)
                       | - /webhooks/status  |<------------+    | (Status Update)      |
                       +---------------------+                 +--------------------+
                             |     ^
                             |     | (Exposed via ngrok during dev)
                             v     |
                       +-----------------+
                       |  Developer Logs |
                       +-----------------+

Prerequisites:

• Node.js and npm: Installed on your system (LTS version recommended). Download Node.js
• Vonage API Account: A free account is sufficient to start. Sign up for Vonage
  • You'll need your API Key and API Secret from the Vonage API Dashboard.
• Vonage Virtual Number: Purchase an SMS-capable number from the Vonage dashboard (Numbers > Buy Numbers).
• ngrok: Installed and authenticated (a free account is fine). Download ngrok
• Vonage CLI (Optional but Recommended): Install via npm: npm install -g @vonage/cli. Useful for managing applications and numbers.

1. Setting up the project

Let's create the project structure, install dependencies, and configure the environment.

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

mkdir vonage-sms-app
cd vonage-sms-app

2. Initialize Node.js Project: Initialize the project using npm, accepting the defaults.

npm init -y

This creates a package.json file.

3. Install Dependencies: Install Express for the web server, the Vonage SDK for API interaction, and dotenv for environment variable management.

npm install express @vonage/server-sdk dotenv --save

• express: Web framework for handling HTTP requests and routes.
• @vonage/server-sdk: Simplifies calls to the Vonage APIs.
• dotenv: Loads environment variables from a .env file.

4. Create Project Structure: Create the necessary files and folders.

touch server.js .env .gitignore private.key # Create empty files

• server.js: Will contain our main Express application logic.
• .env: Stores sensitive configuration like API keys (DO NOT commit this file).
• .gitignore: Specifies intentionally untracked files that Git should ignore (like .env and node_modules).
• private.key: Will store the private key downloaded from your Vonage Application (DO NOT commit this file).

5. Configure .gitignore: Add the following lines to your .gitignore file to prevent committing sensitive information and unnecessary files:

# Dependencies
node_modules/

# Environment Variables
.env

# Vonage Private Key
private.key

# Operating System Files
.DS_Store
Thumbs.db

6. Configure .env: Open the .env file and add placeholders for your Vonage credentials and application details. We'll fill these in later.

# Vonage API Credentials (Get from Vonage Dashboard Settings)
VONAGE_API_KEY=YOUR_API_KEY
VONAGE_API_SECRET=YOUR_API_SECRET

# Vonage Application Details (Get after creating Vonage Application)
VONAGE_APPLICATION_ID=YOUR_APPLICATION_ID
VONAGE_PRIVATE_KEY_PATH=./private.key # Path relative to project root

# Vonage Number (The virtual number you rented)
VONAGE_NUMBER=YOUR_VONAGE_VIRTUAL_NUMBER

# Recipient Number (For testing sending SMS)
TO_NUMBER=RECIPIENT_PHONE_NUMBER # Use E.164 format, e.g., 14155552671

# Server Port
PORT=3000

Explanation of Configuration Choices:

• .env File: Using a .env file and dotenv is a standard practice for managing configuration and secrets in Node.js applications, keeping sensitive data separate from the codebase.
• .gitignore: Essential for preventing accidental exposure of API keys, private keys, and environment files in version control systems like Git.
• private.key: The Vonage Messages API often uses JWTs generated with a public/private key pair for authentication via Applications. Storing the key securely is paramount.

2. Vonage account and application setup

Before writing code, we need to configure our Vonage account and create a Vonage Application to handle Messages API interactions and webhooks.

1. Get API Key and Secret: Log in to your Vonage API Dashboard. Your API Key and Secret are displayed at the top. Copy these values into your .env file for VONAGE_API_KEY and VONAGE_API_SECRET.

2. Set Default SMS API: Navigate to your Account Settings in the Vonage dashboard. Scroll down to ""API settings"" > ""SMS settings"". Ensure ""Default SMS Setting"" is set to Messages API. This ensures webhooks use the Messages API format. Click ""Save changes"".

3. Create a Vonage Application: Vonage Applications act as containers for your communication configurations, including webhook URLs and authentication keys.

• Navigate to ""Applications"" in the dashboard menu (https://dashboard.nexmo.com/applications).
• Click ""Create a new application"".
• Give your application a descriptive name (e.g., ""NodeJS SMS Callbacks App"").
• Click ""Generate public and private key"". This will automatically download the private.key file. Save this file in the root directory of your project (the same location as your server.js). Ensure the VONAGE_PRIVATE_KEY_PATH in your .env file correctly points to it (./private.key). The public key is automatically stored by Vonage.
• Enable the Messages capability.
• You will see fields for Inbound URL and Status URL. We need a public URL for these, which we'll get from ngrok in the next step. For now, you can enter temporary placeholders like http://example.com/webhooks/inbound and http://example.com/webhooks/status. We will update these shortly. Ensure the method is set to POST.
• Click ""Generate new application"".
• On the next screen, you'll see your Application ID. Copy this ID and paste it into your .env file for VONAGE_APPLICATION_ID.

4. Link Your Vonage Number: Scroll down on the Application details page to the ""Link virtual numbers"" section. Find the Vonage virtual number you rented and click the ""Link"" button next to it. This directs incoming messages and status updates for this number to the webhooks defined in this application. Copy this number (in E.164 format, e.g., 18885551212) into your .env file for VONAGE_NUMBER.

5. Start ngrok: Now, let's get the public URL for our webhooks. Open a new terminal window (keep the first one for running the server later). Navigate to your project directory (optional but good practice) and run ngrok, telling it to expose the port your Express server will run on (defined as PORT in .env, default is 3000).

ngrok http 3000

ngrok will display output similar to this:

ngrok by @inconshreveable                                     (Ctrl+C to quit)

Session Status                online
Account                       Your Name (Plan: Free)
Version                       x.x.x
Region                        United States (us-cal-1)
Web Interface                 http://127.0.0.1:4040
Forwarding                    http://<random-string&gt;.ngrok.io -&gt; http://localhost:3000
Forwarding                    https://<random-string&gt;.ngrok.io -&gt; http://localhost:3000

Connections                   ttl     opn     rt1     rt5     p50     p90
                              0       0       0.00    0.00    0.00    0.00

Copy the https://<random-string&gt;.ngrok.io URL (use the HTTPS version). This is your public base URL.

6. Update Webhook URLs in Vonage Application: Go back to your Vonage Application settings in the dashboard (https://dashboard.nexmo.com/applications, find your app, and click ""Edit"").

• Update the Inbound URL to: YOUR_NGROK_HTTPS_URL/webhooks/inbound (e.g., https://<random-string&gt;.ngrok.io/webhooks/inbound)
• Update the Status URL to: YOUR_NGROK_HTTPS_URL/webhooks/status (e.g., https://<random-string&gt;.ngrok.io/webhooks/status)
• Ensure the HTTP method for both is POST.
• Click ""Save changes"".

Now, Vonage knows where to send incoming messages and status updates.

3. Implementing core functionality (Express server and Vonage SDK)

Let's write the Node.js code in server.js to initialize the server, configure Vonage, send SMS, and handle the webhooks.

// server.js

// 1. Import necessary modules
require('dotenv').config(); // Load environment variables from .env file
const express = require('express');
const { Vonage } = require('@vonage/server-sdk');
const { FileSystemCredentials } = require('@vonage/server-sdk/dist/credentials/fileSystemCredentials'); // For private key auth

// 2. Initialize Express app
const app = express();
const port = process.env.PORT || 3000;

// 3. Middleware for parsing JSON and URL-encoded request bodies
// Vonage webhooks typically send data as JSON
app.use(express.json());
app.use(express.urlencoded({ extended: true }));

// 4. Initialize Vonage SDK
// Using Application ID and Private Key for authentication with Messages API
const vonageCredentials = new FileSystemCredentials(
    process.env.VONAGE_APPLICATION_ID,
    process.env.VONAGE_PRIVATE_KEY_PATH
);
const vonage = new Vonage(vonageCredentials);


// --- API Endpoint to Send SMS ---
// Example: POST /send-sms with JSON body {""to"": ""..."", ""text"": ""...""}
app.post('/send-sms', async (req, res) => {
    const { to, text } = req.body;
    const from = process.env.VONAGE_NUMBER;

    // Basic validation
    if (!to || !text) {
        console.error('Missing ""to"" or ""text"" in request body');
        return res.status(400).json({ error: 'Missing ""to"" or ""text"" in request body' });
    }
    if (!from) {
        console.error('VONAGE_NUMBER is not set in .env');
        return res.status(500).json({ error: 'Server configuration error: Missing sender number.' });
    }

    console.log(`Attempting to send SMS from ${from} to ${to}: ""${text}""`);

    try {
        const resp = await vonage.messages.send({
            message_type: 'text',
            text: text,
            to: to,
            from: from,
            channel: 'sms'
        });

        console.log('SMS submitted successfully:', resp);
        res.status(200).json({ message: 'SMS sent successfully!', message_uuid: resp.message_uuid });

    } catch (err) {
        console.error('Error sending SMS:', err.response ? err.response.data : err.message);
        // Provide more context if available from Vonage error response
        const errorDetails = err.response ? err.response.data : { message: err.message };
        res.status(err.response?.status || 500).json({ error: 'Failed to send SMS', details: errorDetails });
    }
});


// --- Webhook Endpoint for Inbound SMS ---
app.post('/webhooks/inbound', (req, res) => {
    console.log('--- Inbound SMS Received ---');
    console.log('Request Body:', JSON.stringify(req.body, null, 2));

    // TODO: Add logic here to process the inbound message
    // e.g., save to database, trigger automated reply, forward to another system
    // Example: Log key information
    const { from, text, message_uuid, timestamp } = req.body;
    console.log(`From: ${from?.number || 'Unknown'}, Text: ${text}, UUID: ${message_uuid}, Timestamp: ${timestamp}`);


    // Vonage requires a 2xx response to acknowledge receipt of the webhook
    res.status(200).send('OK');
    // Using .send('OK') or .end() is fine. Avoid sending back large bodies.
});


// --- Webhook Endpoint for Delivery Status Updates ---
app.post('/webhooks/status', (req, res) => {
    console.log('--- Delivery Status Update Received ---');
    console.log('Request Body:', JSON.stringify(req.body, null, 2));

    // TODO: Add logic here to update message status in your database
    // based on message_uuid and status
    // Example: Log key information
    const { message_uuid, status, timestamp, error } = req.body;
    console.log(`UUID: ${message_uuid}, Status: ${status}, Timestamp: ${timestamp}`);
    if (error) {
        console.error(`Error Code: ${error.code}, Reason: ${error.reason}`);
    }

    // Acknowledge receipt
    res.status(200).send('OK');
});


// 5. Basic Root Route (Optional)
app.get('/', (req, res) => {
    res.send(`SMS Application is running. Use POST /send-sms to send messages. Webhooks configured at /webhooks/inbound and /webhooks/status.`);
});


// 6. Start the server
app.listen(port, () => {
    console.log(`Server listening at http://localhost:${port}`);
    console.log('Make sure ngrok is running and pointing to this port.');
    console.log(`Webhook URLs should be configured in Vonage Application:`);
    console.log(`Inbound: YOUR_NGROK_URL/webhooks/inbound`);
    console.log(`Status:  YOUR_NGROK_URL/webhooks/status`);
});

Code Explanation:

1. Imports: Load dotenv first to ensure environment variables are available. Import express and necessary components from @vonage/server-sdk.
2. Express Init: Standard Express application setup.
3. Middleware: express.json() and express.urlencoded() are crucial for parsing incoming webhook request bodies, which Vonage sends as JSON.
4. Vonage SDK Init: We initialize Vonage using FileSystemCredentials, providing the Application ID and the path to the private.key file. This method is required for authenticating Messages API calls associated with an Application.
5. /send-sms Endpoint:
   • Defines a POST route to trigger sending an SMS.
   • Extracts to and text from the JSON request body.
   • Performs basic validation.
   • Uses vonage.messages.send() within an async function.
   • Specifies message_type: 'text', channel: 'sms', along with to, from, and text.
   • Uses try...catch for error handling, logging errors and returning appropriate HTTP status codes (400 for bad input, 500 or Vonage's status for API errors).
   • Returns the message_uuid on success, which is useful for tracking.
6. /webhooks/inbound Endpoint:
   • Defines a POST route matching the Inbound URL configured in Vonage.
   • Logs the entire request body (req.body) received from Vonage. This contains details about the incoming SMS (sender number, message text, timestamp, etc.).
   • Crucially, sends a 200 OK response. Vonage expects this acknowledgment; otherwise, it will retry sending the webhook, leading to duplicate processing.
   • Includes placeholders (TODO) for adding your application-specific logic (database saving, auto-replies).
7. /webhooks/status Endpoint:
   • Defines a POST route matching the Status URL configured in Vonage.
   • Logs the request body, which contains the message_uuid of the original outbound message and its delivery status (e.g., delivered, failed, rejected, accepted), along with timestamps and potential error details.
   • Also sends a 200 OK response to acknowledge receipt.
   • Includes placeholders (TODO) for updating your application's message records.
8. Root Route: A simple GET route for basic verification that the server is running.
9. Server Start: Starts the Express server listening on the configured port, providing helpful console messages.

4. Running and testing the application

Let's bring it all together and test the flow.

1. Ensure ngrok is Running: Verify that your ngrok http 3000 command is still running in its terminal window and that the Forwarding URL matches the one configured in your Vonage Application's webhook settings. If it stopped, restart it and update the URLs in Vonage if the random subdomain changed (unless you have a paid ngrok plan with a static domain).

2. Fill .env File: Make sure you have filled in all the values in your .env file: VONAGE_API_KEY, VONAGE_API_SECRET, VONAGE_APPLICATION_ID, VONAGE_NUMBER, and a valid TO_NUMBER for testing. Remember to use the E.164 format for phone numbers (e.g., 14155552671).

3. Start the Node.js Server: In your primary terminal window (the one where you created the files and installed dependencies), run:

node server.js

You should see output like:

Server listening at http://localhost:3000
Make sure ngrok is running and pointing to this port.
Webhook URLs should be configured in Vonage Application:
Inbound: https://<random-string&gt;.ngrok.io/webhooks/inbound
Status:  https://<random-string&gt;.ngrok.io/webhooks/status

4. Test Sending an SMS: Open a third terminal window or use a tool like Postman or curl to send a POST request to your /send-sms endpoint.

Using curl:

curl -X POST \
  http://localhost:3000/send-sms \
  -H 'Content-Type: application/json' \
  -d '{
    ""to"": ""YOUR_TEST_RECIPIENT_NUMBER"",
    ""text"": ""Hello from Vonage Node.js Application!""
  }'

Replace YOUR_TEST_RECIPIENT_NUMBER with the number you set in TO_NUMBER or any other valid number.

• Check Server Logs: In the terminal running server.js, you should see:
  • Attempting to send SMS from <VONAGE_NUMBER&gt; to <TO_NUMBER&gt;: ""Hello from Vonage Node.js Application!""
  • SMS submitted successfully: { message_uuid: '...' }
• Check curl Output: You should receive a JSON response like: {""message"":""SMS sent successfully!"",""message_uuid"":""...""}.
• Check Your Phone: You should receive the SMS message on the recipient phone.

5. Test Delivery Status Webhook: Wait a few seconds after the message is delivered to your phone.

• Check Server Logs: You should see the status webhook being received:
  • --- Delivery Status Update Received ---
  • Request Body: { ... ""status"": ""delivered"", ""message_uuid"": ""..."", ... } (or potentially accepted first, then delivered). The exact status flow can vary slightly.
  • UUID: ..., Status: delivered, Timestamp: ...

6. Test Inbound SMS Webhook: Using the phone that received the test message (or any phone), send an SMS message to your Vonage virtual number (the one specified in VONAGE_NUMBER).

• Check Server Logs: You should see the inbound webhook being received:
  • --- Inbound SMS Received ---
  • Request Body: { ""from"": { ""type"": ""sms"", ""number"": ""<SENDER_NUMBER&gt;"" }, ""text"": ""<YOUR_MESSAGE_TEXT&gt;"", ... }
  • From: <SENDER_NUMBER&gt;, Text: <YOUR_MESSAGE_TEXT&gt;, UUID: ..., Timestamp: ...

If all these steps work and you see the corresponding logs, your core functionality is correctly implemented!

5. Implementing error handling, logging, and retry mechanisms

While our basic example includes try...catch and console.log/console.error, production applications need more robust strategies. This involves replacing the basic console logging with a structured logger like Winston for better analysis and handling errors gracefully.

Error Handling Strategy:

• API Errors: Catch errors from the vonage.messages.send() call. Inspect err.response.data or err.message for details from the Vonage API. Return appropriate HTTP status codes (e.g., 400 for invalid input to Vonage, 401 for auth issues, 500/503 for Vonage server issues). Log these errors using a structured logger.
• Webhook Errors: Wrap the logic inside webhook handlers (/webhooks/inbound, /webhooks/status) in try...catch blocks. Log any errors encountered during processing (e.g., database write failure) using the logger. Crucially, still return a 200 OK response to Vonage unless the request itself is malformed (which is unlikely for valid Vonage webhooks). Acknowledging the webhook prevents Vonage retries for processing errors that Vonage can't fix. Handle the processing failure asynchronously (e.g., add to a retry queue).
• Validation Errors: Validate request bodies (/send-sms) and webhook payloads (check for expected fields) early and return 400 Bad Request if invalid. Log validation failures.

Logging:

Using a dedicated logging library like Winston or Pino is highly recommended over console.log/console.error for production applications.

• Replace console.*: The primary goal is to replace all instances of console.log and console.error in your server.js (specifically within the route handlers like /send-sms, /webhooks/inbound, /webhooks/status) with calls to a logger instance (e.g., logger.info, logger.error).
• Configure Log Levels: Use levels like debug, info, warn, error. Log informational messages (like successful sends, received webhooks) at info, potential issues at warn, and definite errors at error. Use debug for verbose tracing during development.
• Structured Logging: Log messages as JSON objects. This makes parsing and analysis much easier in log management systems (e.g., Datadog, Splunk, ELK stack). Include relevant context like message_uuid, request_id, endpoint, etc.
• Log Formatting: Include timestamps, log levels, and consistent message formats.

Example using Winston (Basic Setup):

1. Install Winston: npm install winston
2. Configure Winston near the top of server.js:

// server.js (add near the top, before route definitions)
const winston = require('winston');

const logger = winston.createLogger({
  level: process.env.LOG_LEVEL || 'info', // Default to 'info', use 'debug' for dev
  format: winston.format.combine(
    winston.format.timestamp(),
    winston.format.errors({ stack: true }), // Log stack traces for errors
    winston.format.json() // Log as JSON
  ),
  defaultMeta: { service: 'vonage-sms-service' }, // Add common metadata
  transports: [
    // For production, configure transports to write to files or log aggregation services.
    // Example file transports (commented out):
    // new winston.transports.File({ filename: 'error.log', level: 'error' }),
    // new winston.transports.File({ filename: 'combined.log' }),

    // For development, log to the console with a more readable format:
    new winston.transports.Console({
      format: winston.format.combine(
        winston.format.colorize(),
        winston.format.simple()
      )
    })
  ],
});

// Now, replace all console.log and console.error calls throughout
// your route handlers (/send-sms, /webhooks/inbound, /webhooks/status)
// with logger calls, including relevant context.

// Example replacement in /send-sms success:
// Instead of: console.log('SMS submitted successfully:', resp);
// Use: logger.info('SMS submitted successfully', { message_uuid: resp.message_uuid, to, from });

// Example replacement in /send-sms error handler:
// Instead of: console.error('Error sending SMS:', err.response ? err.response.data : err.message);
// Use: logger.error('Error sending SMS', {
//         errorMessage: err.message,
//         vonageResponse: err.response ? err.response.data : null,
//         to: to,
//         from: from,
//         stack: err.stack // Include stack trace if available
//      });

// Example replacement in /webhooks/inbound:
// Instead of: console.log(`From: ${from?.number || 'Unknown'}, Text: ${text}, ...`);
// Use: logger.info('Inbound SMS Received', {
//         from: req.body.from?.number,
//         text: req.body.text,
//         message_uuid: req.body.message_uuid
//      });

// Example replacement in /webhooks/status error log:
// Instead of: console.error(`Error Code: ${error.code}, Reason: ${error.reason}`);
// Use: logger.warn('Delivery status update contained error', { // Use warn or error based on severity
//         message_uuid: req.body.message_uuid,
//         status: req.body.status,
//         errorCode: error.code,
//         errorReason: error.reason
//      });

Integrating the Logger:

Go back through the server.js code provided in Section 3 and systematically replace every console.log(...) with logger.info(...) and every console.error(...) with logger.error(...). Ensure you pass relevant contextual information as the second argument (metadata object) to the logger methods, as shown in the examples above. This provides much richer and more useful logs for debugging and monitoring.

Retry Mechanisms (Webhook Processing):

Vonage automatically retries sending webhooks if it doesn't receive a 200 OK response within a certain timeout (usually a few seconds). However, this only handles network issues or your server being temporarily down. It doesn't handle errors within your webhook processing logic (like a database connection failure).

For robust processing:

1. Acknowledge Quickly: Always send res.status(200).send('OK') immediately after receiving and basic validation of the webhook.
2. Asynchronous Processing: Perform the actual work (database writes, triggering other actions) asynchronously after sending the 200 OK.
3. Use a Queue: For critical webhook processing, push the payload onto a reliable queue (e.g., Redis queue, RabbitMQ, AWS SQS).
4. Worker Process: Have separate worker processes consume jobs from the queue.
5. Implement Retries with Backoff: If a worker fails to process a job (e.g., database temporarily unavailable), implement a retry strategy with exponential backoff (e.g., retry after 1s, 2s, 4s, 8s...). Libraries like async-retry or queue-specific features can help.
6. Dead-Letter Queue: After several failed retries, move the job to a ""dead-letter queue"" for manual inspection.

This ensures your webhook endpoints remain responsive to Vonage while handling transient processing failures gracefully. For simpler applications, logging the error and potentially alerting might suffice, accepting that some webhook events might be lost if processing fails persistently.

6. Creating a database schema and data layer (Conceptual)

Storing message details and status is often necessary. Here’s a conceptual schema and considerations.

Database Choice: PostgreSQL, MySQL, MongoDB, or others depending on needs. Relational databases (Postgres, MySQL) are often suitable for structured message data.

Conceptual Schema (Relational Example):

CREATE TABLE sms_messages (
    id SERIAL PRIMARY KEY,                 -- Auto-incrementing ID
    message_uuid VARCHAR(255) UNIQUE,      -- Vonage message UUID (use this for correlation)
    direction VARCHAR(10) NOT NULL,        -- 'outbound' or 'inbound'
    sender_number VARCHAR(20),             -- E.164 format
    recipient_number VARCHAR(20),          -- E.164 format
    message_body TEXT,
    status VARCHAR(50) DEFAULT 'submitted', -- e.g., submitted, accepted, delivered, failed, read (for RCS/WhatsApp), rejected
    vonage_status_code VARCHAR(10) NULL,   -- e.g., Vonage error code if status is 'failed'
    vonage_status_reason TEXT NULL,        -- e.g., Vonage error reason if status is 'failed'
    submitted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status_updated_at TIMESTAMP WITH TIME ZONE,
    received_at TIMESTAMP WITH TIME ZONE   -- For inbound messages
    -- Add foreign keys to users, orders, etc. as needed
);

-- Index for efficient status updates and lookups
CREATE INDEX idx_sms_messages_message_uuid ON sms_messages(message_uuid);
CREATE INDEX idx_sms_messages_status ON sms_messages(status);
CREATE INDEX idx_sms_messages_direction ON sms_messages(direction);

Data Layer Implementation:

• ORM/Query Builder: Use libraries like Sequelize, TypeORM, Prisma (for relational DBs), or Mongoose (for MongoDB) to interact with the database. This abstracts SQL/database commands and helps prevent SQL injection.
• Outbound Flow:
  1. Before calling vonage.messages.send(), create a record in sms_messages with direction = 'outbound', status = 'submitted', recipient_number, message_body, etc. Store the intended message_uuid if you generate one client-side, or leave it NULL.
  2. After a successful vonage.messages.send() call, update the record with the message_uuid returned by Vonage.
• Status Webhook (/webhooks/status):
  1. Receive the webhook.
  2. Extract message_uuid, status, timestamp, and error details.
  3. Find the corresponding record in sms_messages using message_uuid.
  4. Update the status, status_updated_at, vonage_status_code, and vonage_status_reason. Handle potential race conditions if multiple status updates arrive for the same UUID (e.g., check timestamps).
• Inbound Webhook (/webhooks/inbound):
  1. Receive the webhook.
  2. Extract from.number, text, message_uuid, timestamp.
  3. Create a new record in sms_messages with direction = 'inbound', status = 'received', sender_number = from.number, message_body = text, received_at = timestamp, and the message_uuid.

Migrations: Use database migration tools (like Sequelize CLI, TypeORM CLI, Prisma Migrate) to manage schema changes version control.

Performance: Index columns frequently used in WHERE clauses (message_uuid, status, direction, timestamps).