Implement SMS Delivery Status Callbacks with Node.js, Express, and Infobip

This guide provides a step-by-step walkthrough for building a Node.js application using the Express framework to send SMS messages via the Infobip API and receive real-time delivery status updates through webhooks (callbacks).

You will learn how to set up a project, send messages, configure Infobip webhooks, create an endpoint to receive status updates, handle potential errors, and deploy the application. This enables you to track the delivery status of every message sent, crucial for applications requiring reliable communication and reporting.

Project Goals:

• Send SMS messages using the Infobip API from a Node.js application.
• Receive delivery status reports (DLRs) from Infobip via webhooks.
• Store basic message status information.
• Provide a robust foundation for production use.

Technologies Used:

• Node.js: A JavaScript runtime environment for server-side development.
• Express: A minimal and flexible Node.js web application framework (version 4.16.0+ includes built-in JSON parsing).
• Infobip API: Used for sending SMS messages and configuring delivery report webhooks.
• axios: A promise-based HTTP client for making requests to the Infobip API.
• dotenv: To manage environment variables securely.

System Architecture:

(System Architecture Diagram Placeholder: Ideally, replace this text with an actual diagram image showing the flow: User/Service -> Node.js App -> Infobip API (for sending SMS), and then Infobip Platform -> Node.js App (for delivery report callback).)

Prerequisites:

• A free or paid Infobip account (https://www.infobip.com/).
• Node.js and npm (or yarn) installed on your system.
• Basic understanding of JavaScript, Node.js, Express, and REST APIs.
• A publicly accessible URL for receiving webhook callbacks (we'll use ngrok for local development).

Final Outcome:

By the end of this guide, you will have a running Node.js Express application capable of:

1. Accepting API requests to send SMS messages.
2. Calling the Infobip API to dispatch those messages.
3. Receiving POST requests from Infobip on a dedicated endpoint (/delivery-report) containing the delivery status of sent messages.
4. Logging the received delivery status.

Setting up the Project

Let's start by creating our project directory, initializing Node.js, and installing necessary dependencies.

Create Project Directory

Open your terminal or command prompt and create a new directory for the project.

mkdir infobip-delivery-callbacks
cd infobip-delivery-callbacks

Initialize Node.js Project

Initialize the project using npm. You can accept the defaults or customize them.

npm init -y

This creates a package.json file.

Install Dependencies

We need express for the web server, axios to make HTTP requests to Infobip, and dotenv to manage environment variables. Note that modern Express versions (4.16.0+) include built-in middleware for parsing JSON and URL-encoded bodies, so body-parser is often no longer needed as a separate dependency.

npm install express axios dotenv

(Optional) Install Nodemon for Development: nodemon automatically restarts the server upon file changes, speeding up development.

npm install --save-dev nodemon

If you installed nodemon, update the scripts section in your package.json:

// package.json
{
  // ... other fields
  "scripts": {
    "start": "node server.js",
    "dev": "nodemon server.js",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  // ... rest of the file
}

Project Structure

Create the basic files and folders.

touch server.js .env .env.example .gitignore

Your initial structure should look like this:

Configure Environment Variables

We need to store sensitive information like API keys securely.

• .env: This file will hold your actual secrets (API Key, Base URL). Do not commit this file to version control.
• .env.example: A template showing required variables. Commit this file.
• .gitignore: Ensures .env and node_modules are not tracked by Git.

Add the following to your .gitignore:

# .gitignore
node_modules
.env
npm-debug.log

Add the following variable names to .env.example:

# .env.example
INFOBIP_API_KEY=YOUR_INFOBIP_API_KEY
INFOBIP_BASE_URL=YOUR_INFOBIP_BASE_URL
PORT=3000 # Optional: default port for the server

Now, populate your actual .env file with your credentials. See the "Obtaining API Credentials" section for detailed instructions on obtaining your Infobip API Key and Base URL.

# .env (DO NOT COMMIT)
INFOBIP_API_KEY=xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
INFOBIP_BASE_URL=yyyyyy.api.infobip.com
PORT=3000

Purpose of Configuration:

• .env / dotenv: Keeps sensitive credentials out of the codebase, adhering to security best practices. Different environments (development, production) can have different .env files.
• nodemon: Improves developer experience by automatically restarting the server on code changes.

Implementing Core Functionality

Now, let's write the code for our Express server, including sending SMS and preparing the endpoint for delivery reports.

Basic Express Server Setup

Open server.js and set up a minimal Express application.

// server.js
require('dotenv').config(); // Load environment variables from .env file
const express = require('express');
const axios = require('axios');

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

// Middleware to parse JSON request bodies (built-in with Express 4.16.0+)
app.use(express.json());
// If you needed to parse URL-encoded data as well:
// app.use(express.urlencoded({ extended: true }));

// In-memory storage for message statuses (replace with a database in production)
const messageStore = {};

// --- Routes will go here ---

// Start the server
// Note: For automated testing, it's better to export 'app' and call 'listen'
// in a separate file or conditional block, as discussed in testing considerations.
app.listen(port, () => {
  console.log(`Server running on http://localhost:${port}`);
});

// Export the app instance ONLY if needed for testing frameworks
// module.exports = app;

Implementing SMS Sending

We'll adapt the logic from the Infobip Node.js blog post to create an API endpoint /send-sms.

Add the following code inside server.js where // --- Routes will go here --- is indicated:

// server.js (continued)

// === Infobip Helper Functions ===
const buildUrl = (baseUrl) => {
  // Ensure baseUrl doesn't end with a slash, and append the path
  const cleanBaseUrl = baseUrl.endsWith('/') ? baseUrl.slice(0, -1) : baseUrl;
  return `https://${cleanBaseUrl}/sms/2/text/advanced`;
};

const buildHeaders = (apiKey) => {
  if (!apiKey) {
    throw new Error('INFOBIP_API_KEY is missing.');
  }
  return {
    'Authorization': `App ${apiKey}`,
    'Content-Type': 'application/json',
    'Accept': 'application/json',
  };
};

const buildRequestBody = (destinationNumber, messageText, messageId) => {
  // Basic validation
  if (!destinationNumber || !messageText) {
    throw new Error('Destination number and message text are required.');
  }

  // Use custom messageId if provided, otherwise Infobip generates one
  const message = {
    destinations: [{ to: destinationNumber }],
    text: messageText,
  };
  if (messageId) {
    message.messageId = messageId; // Optional: Link your internal ID
  }

  return {
    messages: [message],
    // IMPORTANT: Add tracking for delivery reports
    tracking: {
        track: ""SMS"", // Specifies the channel being tracked (e.g., ""SMS"").
        type: ""ONE_TIME_PIN"" // An arbitrary classification useful for segmenting reports later (e.g., ""TRANSACTIONAL"", ""MARKETING"", ""OTP""). Adjust as needed or omit.
        // processKey: ""YOUR_PROCESS_KEY"" // Optional: further categorize reports
    }
  };
};

// === API Endpoint to Send SMS ===
app.post('/send-sms', async (req, res) => {
  const { to, text } = req.body; // Expect 'to' (phone number) and 'text' in request body

  if (!to || !text) {
    return res.status(400).json({ error: 'Missing required fields: to, text' });
  }

  const apiKey = process.env.INFOBIP_API_KEY;
  const baseUrl = process.env.INFOBIP_BASE_URL;

  if (!apiKey || !baseUrl) {
    console.error('Infobip API Key or Base URL missing in environment variables.');
    return res.status(500).json({ error: 'Server configuration error.' });
  }

  try {
    const url = buildUrl(baseUrl);
    const headers = buildHeaders(apiKey);
    const requestBody = buildRequestBody(to, text); // Let Infobip generate messageId initially

    console.log(`Sending SMS to: ${to}`);
    console.log('Request Body:', JSON.stringify(requestBody, null, 2)); // Log the request body

    const infobipResponse = await axios.post(url, requestBody, { headers });

    console.log('Infobip API Response:', JSON.stringify(infobipResponse.data, null, 2));

    // Store initial status (optional, demonstrates tracking)
    if (infobipResponse.data && infobipResponse.data.messages && infobipResponse.data.messages.length > 0) {
        const sentMessage = infobipResponse.data.messages[0];
        messageStore[sentMessage.messageId] = {
            to: sentMessage.to,
            status: sentMessage.status.name,
            group: sentMessage.status.groupName,
            timestamp: new Date().toISOString()
        };
        console.log(`Message ID ${sentMessage.messageId} stored with status ${sentMessage.status.name}`);
    }

    // Return the successful response from Infobip
    res.status(infobipResponse.status).json(infobipResponse.data);

  } catch (error) {
    console.error('Error sending SMS via Infobip:');
    if (error.response) {
      // Infobip API returned an error
      console.error('Status:', error.response.status);
      console.error('Data:', JSON.stringify(error.response.data, null, 2));
      res.status(error.response.status).json({
        error: 'Failed to send SMS via Infobip.',
        details: error.response.data
      });
    } else if (error.request) {
      // Request was made but no response received
      console.error('Request Error:', error.request);
      res.status(500).json({ error: 'No response received from Infobip API.' });
    } else {
      // Other errors (e.g., setup issue, validation)
      console.error('Error:', error.message);
      res.status(500).json({ error: 'Internal server error.', message: error.message });
    }
  }
});

// --- Delivery Report Endpoint will go here ---

Why this approach?

• Separation of Concerns: Helper functions (buildUrl, buildHeaders, buildRequestBody) keep the main route handler clean.
• Asynchronous Handling: Using async/await makes handling the promise returned by axios.post straightforward.
• Error Handling: The try...catch block specifically checks for error.response (errors from Infobip), error.request (network issues), and other general errors.
• Tracking Parameter: Adding the tracking object in the request body tells Infobip we want delivery reports for this message. The specific type or processKey can help categorize reports later.
• Built-in Middleware: Uses express.json() instead of the external body-parser package for simplicity and modern practice.

Implementing the Delivery Report Endpoint

This endpoint (/delivery-report) will receive POST requests from Infobip containing status updates.

Add the following code inside server.js where // --- Delivery Report Endpoint will go here --- is indicated:

// server.js (continued)

// === API Endpoint to Receive Delivery Reports ===
app.post('/delivery-report', (req, res) => {
  console.log('Received Delivery Report:');
  console.log(JSON.stringify(req.body, null, 2)); // Log the entire report

  // Infobip sends reports in an array called 'results'
  const reports = req.body.results;

  if (!reports || !Array.isArray(reports)) {
    console.warn('Received invalid delivery report format.');
    // Still acknowledge receipt to Infobip
    return res.status(200).send('Report received but format unexpected.');
  }

  try {
    reports.forEach(report => {
      const { messageId, status, error, doneAt, sentAt, price } = report;
      const statusName = status ? status.name : 'UNKNOWN';
      const groupName = status ? status.groupName : 'UNKNOWN';
      const errorName = error ? error.name : 'NONE';

      console.log(`---\nMessage ID: ${messageId}`);
      console.log(`Status: ${statusName} (${groupName})`);
      console.log(`Error: ${errorName}`);
      console.log(`Sent At: ${sentAt}`);
      console.log(`Done At: ${doneAt}`);
      console.log(`Price: ${price ? price.pricePerMessage + ' ' + price.currency : 'N/A'}`);

      // Update our simple in-memory store (replace with DB update)
      if (messageStore[messageId]) {
          messageStore[messageId].status = statusName;
          messageStore[messageId].group = groupName;
          messageStore[messageId].error = errorName;
          messageStore[messageId].finalTimestamp = doneAt || new Date().toISOString();
          console.log(`Updated status for Message ID ${messageId} to ${statusName}`);
      } else {
          console.warn(`Received report for unknown Message ID: ${messageId}`);
          // Optionally, store the report anyway if needed
      }
      console.log(`---`);
    });
  } catch (processingError) {
      console.error('Error processing delivery report:', processingError);
      // Log the error, but still acknowledge receipt to Infobip below.
  }

  // **IMPORTANT**: Always respond with a 2xx status code (e.g., 200 OK)
  // to acknowledge receipt to Infobip. Failure to do so will cause Infobip
  // to retry sending the report according to their retry schedule (e.g.,
  // 1min + (1min * retryNumber * retryNumber)), potentially flooding your endpoint.
  // Even if internal processing fails, acknowledging receipt is crucial.
  res.status(200).send('Delivery report received successfully.');
});

// Optional: Add a route to view the message store (for debugging)
app.get('/message-status', (req, res) => {
    res.status(200).json(messageStore);
});

Why this approach?

• Dedicated Endpoint: Clearly separates the logic for receiving reports.
• Logging: Logs the entire received payload for debugging, then extracts key fields.
• Data Structure: Parses the expected results array from Infobip's payload.
• Acknowledgement: Crucially sends a 200 OK response back to Infobip. This is highlighted due to its importance in preventing unnecessary retries from Infobip.
• Robust Processing: Includes a try...catch around the report processing loop to handle potential errors without crashing the server or preventing the 200 OK response.
• Status Update: Demonstrates updating the status in our simple messageStore. In a real application, this would involve database operations.

Building a Complete API Layer

Our API layer currently consists of:

• POST /send-sms: Triggers sending an SMS.

  • Request Body (JSON):
    jsonCopy

    {
      "to": "+12345678900",
      "text": "Hello from the Infobip Guide!"
    }

  • Success Response (JSON - Example):
    jsonCopy

    {
      "bulkId": "some-bulk-id",
      "messages": [
        {
          "to": "+12345678900",
          "status": {
            "groupId": 1,
            "groupName": "PENDING",
            "id": 26,
            "name": "PENDING_ACCEPTED",
            "description": "Message accepted, pending delivery."
          },
          "messageId": "unique-infobip-message-id-12345"
        }
      ]
    }

  • Error Response (JSON - Example 400 Bad Request):
    jsonCopy

    {
      "error": "Missing required fields: to, text"
    }

  • Error Response (JSON - Example 500 Infobip Error):
    jsonCopy

    {
      "error": "Failed to send SMS via Infobip.",
      "details": {
        "requestError": {
          "serviceException": {
            "messageId": "INVALID_DESTINATION_ADDRESS",
            "text": "Invalid destination address format."
          }
        }
      }
    }

• POST /delivery-report: Receives delivery status updates from Infobip.

  • Request Body (JSON - Example from Infobip):
    jsonCopy

    {
      "results": [
        {
          "bulkId": "some-bulk-id",
          "messageId": "unique-infobip-message-id-12345",
          "to": "+12345678900",
          "sentAt": "2023-10-26T10:00:05.123+0000",
          "doneAt": "2023-10-26T10:00:07.456+0000",
          "smsCount": 1,
          "price": {
            "pricePerMessage": 0.005,
            "currency": "USD"
          },
          "status": {
            "groupId": 3,
            "groupName": "DELIVERED",
            "id": 5,
            "name": "DELIVERED_TO_HANDSET",
            "description": "Message delivered to handset"
          },
          "error": {
            "groupId": 0,
            "groupName": "OK",
            "id": 0,
            "name": "NO_ERROR",
            "description": "No Error",
            "permanent": false
          }
          // ... other fields like mccMnc, callbackData etc. might be present
        }
      ]
    }

  • Success Response (Text): Delivery report received successfully.

• GET /message-status: (Optional debugging endpoint) View stored statuses.

  • Success Response (JSON):
    jsonCopy

    {
      "unique-infobip-message-id-12345": {
        "to": "+12345678900",
        "status": "DELIVERED_TO_HANDSET",
        "group": "DELIVERED",
        "timestamp": "2023-10-26T10:00:05.150Z",
        "error": "NO_ERROR",
        "finalTimestamp": "2023-10-26T10:00:07.456+0000"
      }
    }

Testing with curl:

1. Send SMS: (Make sure your server is running: npm run dev or npm start)

   bashCopy

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

   Note: Replace +12345678900 with your test number (must be verified on Infobip free trial).

2. Check Status (Optional Debugging):

   bashCopy

   curl http://localhost:3000/message-status

3. Simulate Infobip Callback (Advanced): You would typically rely on Infobip sending the callback, but you could simulate it:

   bashCopy

   curl -X POST http://localhost:3000/delivery-report \
   -H "Content-Type: application/json" \
   -d '{
     "results": [
       {
         "messageId": "unique-infobip-message-id-12345",
         "to": "+12345678900",
         "status": { "groupId": 3, "groupName": "DELIVERED", "id": 5, "name": "DELIVERED_TO_HANDSET", "description": "Simulated delivery" },
         "error": { "groupId": 0, "groupName": "OK", "id": 0, "name": "NO_ERROR", "description": "No Error" },
         "doneAt": "2023-10-26T11:00:00.000+0000",
         "sentAt": "2023-10-26T10:59:58.000+0000"
       }
     ]
   }'

   Note: Use a messageId from a previous /send-sms response. After simulation, check /message-status again or watch the server logs.

Authentication/Authorization:

This basic example lacks robust API authentication. For production:

• /send-sms: Protect this endpoint with API keys, JWT tokens, or other mechanisms suitable for your application's security model. Ensure only authorized clients can trigger messages.
• /delivery-report: This endpoint needs to be public for Infobip to reach it. Security relies on:
  • Obscurity: The URL itself isn't widely known.
  • (Strongly Recommended) Signature Validation: Consult the official Infobip documentation to determine if they currently offer webhook signature validation for SMS delivery reports. If they do, implement it. This typically involves obtaining a secret key from the Infobip portal and using cryptographic libraries (like Node.js crypto) to verify a signature sent in the request header (e.g., X-Infobip-Signature). This is the most reliable method to ensure the request genuinely originated from Infobip and prevent malicious POSTs to your callback URL.
  • IP Whitelisting: If Infobip provides a stable list of IP addresses they use for sending webhooks, you could potentially whitelist these IPs at your firewall/infrastructure level. However, IPs can change, making signature validation generally preferable.

Integrating with Infobip

Obtaining API Credentials

1. Log in to your Infobip account (portal.infobip.com).
2. API Key:
   • Navigate to the ""Developers"" section (often in the main menu or sidebar).
   • Go to ""API Keys"".
   • Create a new API key or use an existing one. Give it a descriptive name (e.g., ""Node Delivery Callback App"").
   • Copy the generated API key immediately and securely store it. You usually cannot view it again.
   • Paste this key into your .env file as INFOBIP_API_KEY.
3. Base URL:
   • On the same API Keys page, or sometimes in your account settings/profile, you will find your unique Base URL. It typically looks like xxxxx.api.infobip.com.
   • Copy this URL (just the domain part, without https://).
   • Paste it into your .env file as INFOBIP_BASE_URL.

Configuring the Delivery Report Webhook

This tells Infobip where to send the delivery status updates.

1. In the Infobip portal, find the section for Webhooks or API Settings. This might be under ""Developers,"" ""Channels,"" or specific product settings like ""SMS.""
2. Look for ""Delivery Reports,"" ""Status Updates,"" or a similar option for SMS.
3. You will need to provide the URL for your /delivery-report endpoint.
   • Local Development: Use a tunneling service like ngrok.
     • Install ngrok (ngrok.com).
     • Run your Node.js server (npm run dev or npm start). Let's assume it's on port 3000.
     • In a new terminal window, run: ngrok http 3000
     • ngrok will provide a public HTTPS URL (e.g., https://<random-id>.ngrok-free.app or similar).
     • Copy this HTTPS URL and append your endpoint path: https://<random-id>.ngrok-free.app/delivery-report
   • Production Deployment: Use the public URL of your deployed application (e.g., https://your-app-domain.com/delivery-report).
4. Paste the complete, publicly accessible URL into the appropriate field in the Infobip webhook configuration section.
5. Save the configuration. Infobip might send a test request to verify the URL is reachable.

Environment Variables Recap

• INFOBIP_API_KEY: Your secret key for authenticating API requests to Infobip. Found in Infobip portal > Developers > API Keys.
• INFOBIP_BASE_URL: Your unique domain for accessing the Infobip API. Found near your API Key in the portal. Format: xxxxx.api.infobip.com.
• PORT: The local port your Express server listens on (e.g., 3000). Used by server.js.

Fallback Mechanisms: Infobip handles retries for webhook delivery if your endpoint is temporarily unavailable (as discussed under the 'Implementing the Delivery Report Endpoint' section). For sending SMS, implement retries with exponential backoff in your client-side logic calling /send-sms if the initial request fails due to network issues or temporary Infobip unavailability (5xx errors).

Error Handling, Logging, and Retry Mechanisms

Error Handling Strategy

• /send-sms Endpoint:
  • Validate incoming request bodies (presence of to, text). Return 400 Bad Request for invalid input.
  • Wrap Infobip API calls in try...catch.
  • Distinguish between Infobip API errors (error.response), network errors (error.request), and other server errors.
  • Return appropriate HTTP status codes (4xx for client errors, 5xx for server/Infobip errors) and informative JSON error messages.
  • Log detailed errors server-side for debugging (including Infobip's error payload).
• /delivery-report Endpoint:
  • Validate the basic structure of the incoming report (req.body.results array).
  • Log received reports thoroughly.
  • Wrap processing logic in try...catch to prevent crashes if a report has an unexpected format or processing fails.
  • Crucially: Always return a 200 OK status to Infobip even if internal processing fails, unless the request format itself is completely unrecognizable. This acknowledges receipt and stops Infobip's retries. Log internal processing errors for later investigation.

Logging

• Current: Using console.log, console.warn, console.error. Suitable for development and simple cases.
• Production Recommendation: Use a dedicated logging library like Winston or Pino.
  • Benefits: Structured logging (JSON format), different log levels (debug, info, warn, error), configurable outputs (console, file, external services), timestamps.
  • Example Setup (Conceptual with Winston):
    bashCopy

    npm install winston

    javascriptCopy

    // logger.js
    const winston = require('winston');
    
    const logger = winston.createLogger({
      level: process.env.LOG_LEVEL || 'info', // Control level via env var
      format: winston.format.combine(
        winston.format.timestamp(),
        winston.format.errors({ stack: true }), // Log stack traces
        winston.format.json() // Log as JSON
      ),
      defaultMeta: { service: 'infobip-callback-service' }, // Add service context
      transports: [
        // - Write all logs with level `error` and below to `error.log`
        new winston.transports.File({ filename: 'error.log', level: 'error' }),
        // - Write all logs with level `info` and below to `combined.log`
        new winston.transports.File({ filename: 'combined.log' }),
      ],
    });
    
    // If we're not in production OR specifically want console logs, add console transport
    if (process.env.NODE_ENV !== 'production') {
      logger.add(new winston.transports.Console({
        format: winston.format.combine(
            winston.format.colorize(),
            winston.format.simple()
        ),
      }));
    }
    
    module.exports = logger;

    Replace console.log etc. with logger.info, logger.error, etc. in server.js (after importing the logger).

Retry Mechanisms

• Sending SMS (/send-sms): Retries should be implemented by the client calling your /send-sms endpoint. If the client receives a 5xx error (indicating a server or temporary Infobip issue), it can retry using exponential backoff (e.g., wait 1s, then 2s, then 4s...). Avoid retrying on 4xx errors.
• Receiving Delivery Reports (/delivery-report): Infobip handles retries automatically if your endpoint doesn't respond with a 2xx status code. Your primary responsibility is to ensure your endpoint is reliable and acknowledges receipt promptly with a 200 OK.

Testing Error Scenarios:

• Send requests to /send-sms with missing to or text fields (expect 400).
• Temporarily use an invalid INFOBIP_API_KEY in .env and send an SMS (expect 401/500 with Infobip auth error).
• Send an SMS to an invalid phone number format (expect 4xx/500 with Infobip validation error).
• Temporarily stop your server after sending an SMS and observe Infobip's retry attempts in the Infobip portal logs (if available) or by restarting your server later and checking logs for delayed reports.
• Send a malformed JSON payload to /delivery-report using curl (expect 200 response but a warning in server logs).

Creating a Database Schema and Data Layer (Conceptual)

The current in-memory messageStore is unsuitable for production as it loses data on server restart and doesn't scale.

Conceptual Schema (e.g., SQL):

CREATE TABLE sms_messages (
    infobip_message_id VARCHAR(255) PRIMARY KEY, -- Infobip's unique ID
    application_message_id VARCHAR(255) NULL UNIQUE, -- Optional: Your internal reference ID
    recipient_number VARCHAR(20) NOT NULL,
    message_text TEXT NULL, -- Store if needed for context
    status_group VARCHAR(50) NULL, -- e.g., PENDING, DELIVERED, FAILED, UNDELIVERABLE
    status_name VARCHAR(100) NOT NULL, -- e.g., PENDING_ACCEPTED, DELIVERED_TO_HANDSET
    status_description TEXT NULL,
    error_group VARCHAR(50) NULL,
    error_name VARCHAR(100) NULL,
    error_description TEXT NULL,
    is_permanent_error BOOLEAN DEFAULT FALSE,
    segments_count INT NULL,
    price_per_message DECIMAL(10, 5) NULL,
    price_currency VARCHAR(3) NULL,
    sent_at TIMESTAMPTZ NULL, -- Timestamp from Infobip report
    done_at TIMESTAMPTZ NULL, -- Timestamp from Infobip report
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL, -- When record created in *our* DB
    last_updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP NOT NULL -- Updated on status change
);

-- Optional Index for faster lookups
CREATE INDEX idx_sms_recipient_timestamp ON sms_messages (recipient_number, created_at DESC);
CREATE INDEX idx_sms_status_group ON sms_messages (status_group);
CREATE INDEX idx_sms_created_at ON sms_messages (created_at DESC);
-- Index for the optional application ID if used frequently for lookups
-- CREATE UNIQUE INDEX idx_sms_app_message_id ON sms_messages (application_message_id);

-- Trigger to update last_updated_at automatically (Example for PostgreSQL)
CREATE OR REPLACE FUNCTION update_last_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
   NEW.last_updated_at = NOW();
   RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_sms_messages_last_updated
BEFORE UPDATE ON sms_messages
FOR EACH ROW
EXECUTE FUNCTION update_last_updated_at_column();

Data Access Layer (Conceptual using an ORM like Prisma or Sequelize):

1. Setup: Install ORM (npm install @prisma/client, npm install prisma --save-dev or npm install sequelize pg pg-hstore). Initialize it (npx prisma init, npx sequelize init).
2. Schema Definition: Define the model matching the table above in the ORM's schema definition language (e.g., schema.prisma, Sequelize models).
3. Migrations: Generate and apply database migrations to create the table (npx prisma migrate dev, npx sequelize db:migrate).
4. Implementation:
   • Replace the messageStore object with an instance of your ORM client (e.g., const { PrismaClient } = require('@prisma/client'); const prisma = new PrismaClient();).
   • In /send-sms: After successfully getting a response from Infobip, use the ORM's create method to insert a new record into sms_messages, storing the messageId, to, initial status, etc.
     javascriptCopy

     // Example using Prisma inside /send-sms success block
     const sentMessage = infobipResponse.data.messages[0];
     try {
       await prisma.sms_message.create({ // Adjust model name if needed
         data: {
           infobip_message_id: sentMessage.messageId,
           recipient_number: sentMessage.to,
           status_name: sentMessage.status.name,
           status_group: sentMessage.status.groupName,
           status_description: sentMessage.status.description,
           // Add application_message_id if you track it
         }
       });
       console.log(`Stored initial record for Message ID ${sentMessage.messageId}`);
     } catch (dbError) {
       console.error(`Database error storing initial record for ${sentMessage.messageId}:`, dbError);
       // Decide how to handle DB errors - log, alert, maybe retry?
     }

   • In /delivery-report: For each report, use the ORM's update method (with a where clause on infobip_message_id) to update the status fields (status_group, status_name, error_name, done_at, etc.) and last_updated_at. Handle cases where the messageId might not exist in your database (e.g., log a warning).
     javascriptCopy

     // Example using Prisma inside /delivery-report forEach loop
     try {
       const updatedMessage = await prisma.sms_message.update({ // Adjust model name if needed
         where: { infobip_message_id: messageId },
         data: {
           status_name: statusName,
           status_group: groupName,
           status_description: status?.description,
           error_name: errorName,
           error_group: error?.groupName,
           error_description: error?.description,
           is_permanent_error: error?.permanent,
           done_at: doneAt ? new Date(doneAt) : null, // Ensure correct type conversion
           price_per_message: price?.pricePerMessage,
           price_currency: price?.currency,
           segments_count: report.smsCount,
           // last_updated_at is handled by DB trigger or ORM hook
         }
       });
       console.log(`Updated DB status for Message ID ${messageId} to ${statusName}`);
     } catch (dbError) {
       if (dbError.code === 'P2025') { // Example Prisma code for 'Record not found'
         console.warn(`DB record not found for Message ID: ${messageId}. Storing report info might be needed.`);
         // Optionally create a new record here if needed, or store in a separate 'unmatched_reports' table
       } else {
         console.error(`Database error updating status for ${messageId}:`, dbError);
         // Log error, potentially alert. Still send 200 OK to Infobip.
       }
     }

This provides a persistent and scalable way to track message statuses. Remember to add proper database connection management, error handling for database operations, and potentially connection pooling for production environments.