Developer Guide: Building a Production-Ready Bulk SMS System with Node.js, Express, and Vonage

This guide provides a step-by-step walkthrough for building a robust system capable of sending bulk SMS messages using Node.js, Express, and the Vonage Messages API. We'll cover everything from project setup and core sending logic to handling rate limits, security, error management, and deployment considerations.

This system addresses the need to reliably send the same message to a large list of recipients while navigating the complexities of API rate limits, carrier regulations (like A2P 10DLC in the US), and ensuring message delivery status tracking.

Goal: To create a Node.js/Express application with an API endpoint that accepts a list of recipients and a message, then efficiently sends SMS messages via Vonage, managing concurrency and potential errors.

Technologies Used:

• Node.js: JavaScript runtime environment.
• Express: Minimalist web framework for Node.js, used to build the API layer.
• Vonage Messages API: Vonage's unified API for sending messages across various channels, including SMS. We'll use the @vonage/server-sdk and @vonage/messages.
• dotenv: Module to load environment variables from a .env file into process.env.
• (Optional) Prisma & PostgreSQL: For managing recipient lists and logging message status in a database.
• (Optional) p-queue: A promise queue library for concurrency control, crucial for managing Vonage API rate limits.

System Architecture:

+-----------------+      +---------------------+      +-----------------+      +-----------------+
|   Client App    |----->| Node.js/Express API |----->| Vonage Messages |----->|   SMS Network   |
| (e.g., Postman, |      | (Your Application)  |      |       API       |      | (Recipient Phones)|
|  Frontend App)  |      +---------------------+      +-----------------+      +-----------------+
|                 |               |                                |
+-----------------+               | (Optional DB Interaction)      | (Status Webhooks)
                                  V                                V
                         +--------------------+         +---------------------+
                         | Database (e.g.,    |         |   Webhook Handler   |
                         | PostgreSQL w/      |         | (Part of your API)  |
                         | Prisma)            |         +---------------------+
                         +--------------------+

Final Outcome: A functional Node.js application that can:

1. Accept bulk SMS sending requests via an API endpoint.
2. Send messages sequentially or concurrently (with rate limiting) using the Vonage Messages API.
3. Securely manage Vonage API credentials using JWT (Application ID and Private Key).
4. (Optionally) Integrate with a database for recipient management and message logging.
5. Provide basic logging and error handling.

Prerequisites:

• Node.js and npm (or yarn) installed.
• A Vonage API account (Sign up here).
• A Vonage Application ID and Private Key file (generated via the Vonage Dashboard).
• A Vonage virtual phone number capable of sending SMS.
• (Optional but recommended for local development involving webhooks) ngrok installed (Download here).
• (Optional) Vonage CLI installed globally: npm install -g @vonage/cli.
• (Optional) Docker and Docker Compose installed if using the database example with PostgreSQL.

1. Project Setup

Let's initialize our Node.js project and install the necessary dependencies.

1. Create Project Directory:

   bash

   mkdir vonage-bulk-sms
   cd vonage-bulk-sms
   

2. Initialize npm:

   bash

   npm init -y
   

   This creates a package.json file.

3. Install Core Dependencies:

   bash

   npm install express @vonage/server-sdk @vonage/messages dotenv
   
   • express: Web framework.
   • @vonage/server-sdk: Official Vonage SDK for Node.js (core client).
   • @vonage/messages: Part of the SDK for constructing message objects.
   • dotenv: Loads environment variables from .env.

4. Install Development/Optional Dependencies:

   bash

   npm install --save-dev nodemon # Optional: for auto-restarting the server during development
   npm install p-queue # Optional: for advanced concurrency control
   # Optional: For database integration with Prisma
   npm install @prisma/client
   npm install --save-dev prisma
   

5. Create Project Structure:

   • vonage-bulk-sms/
     • node_modules/
     • src/
       • controllers/
         • smsController.js
       • routes/
         • smsRoutes.js
       • services/
         • vonageService.js
       • utils/
         • logger.js
         • prismaClient.js (Optional: if using Prisma)
       • server.js
     • prisma/ (Optional: for database schema)
       • schema.prisma
     • .env
     • .env.example
     • .gitignore
     • private.key (Your downloaded Vonage private key)
     • package.json

   Create these directories and empty files.

6. Configure .gitignore: Create a .gitignore file in the root directory:

   node_modules/
   .env
   dist/
   npm-debug.log*
   yarn-debug.log*
   yarn-error.log*
   private.key # Ensure your private key is not committed
   

7. Setup Environment Variables (.env.example): Create a .env.example file. Never commit the actual .env file.

   # Vonage API Credentials (JWT Authentication)
   VONAGE_APPLICATION_ID=YOUR_VONAGE_APP_ID
   VONAGE_PRIVATE_KEY_PATH=./private.key # Path relative to project root
   
   # Vonage Number
   VONAGE_NUMBER=YOUR_VONAGE_VIRTUAL_NUMBER # E.g., 14155550100
   
   # Application Settings
   PORT=3000
   LOG_LEVEL=info
   
   # Optional: Database URL (if using Prisma)
   # DATABASE_URL="postgresql://user:password@host:port/database?schema=public"
   
   # Optional: Concurrency Settings (if using p-queue)
   VONAGE_CONCURRENCY=5 # Adjust based on your Vonage account limits (often starts low)
   VONAGE_INTERVAL_MS=1000 # Interval between bursts (e.g., 1000ms = 1 second)
   VONAGE_INTERVAL_CAP=5   # Max requests per interval (matches concurrency here)
   
   • Create a copy named .env and fill in your actual credentials later. Place your downloaded private.key file in the project root.

8. Setup Basic Express Server (src/server.js):

   javascript

   // src/server.js
   require('dotenv').config();
   const express = require('express');
   const smsRoutes = require('./routes/smsRoutes');
   const logger = require('./utils/logger');
   
   const app = express();
   const PORT = process.env.PORT || 3000;
   
   // Middleware
   app.use(express.json()); // Parse JSON bodies
   app.use(express.urlencoded({ extended: true })); // Parse URL-encoded bodies
   
   // Basic Logging Middleware
   app.use((req, res, next) => {
       logger.info(`${req.method} ${req.url}`);
       next();
   });
   
   // Routes
   app.use('/api/sms', smsRoutes);
   
   // Basic Health Check
   app.get('/health', (req, res) => {
       res.status(200).json({ status: 'UP', timestamp: new Date().toISOString() });
   });
   
   // Global Error Handler (Very Basic)
   app.use((err, req, res, next) => {
       logger.error('Unhandled error:', err);
       res.status(500).json({ error: 'Internal Server Error' });
   });
   
   app.listen(PORT, () => {
       logger.info(`Server running on port ${PORT}`);
       logger.info(`Access health check at http://localhost:${PORT}/health`);
   });
   
   module.exports = app; // Export for potential testing
   

9. Setup Basic Logger (src/utils/logger.js):

   javascript

   // src/utils/logger.js
   const logLevel = process.env.LOG_LEVEL || 'info';
   
   const logger = {
       info: (...args) => {
           if (logLevel === 'info' || logLevel === 'debug') {
               console.log('[INFO]', ...args);
           }
       },
       warn: (...args) => {
           if (logLevel === 'info' || logLevel === 'debug' || logLevel === 'warn') {
               console.warn('[WARN]', ...args);
           }
       },
       error: (...args) => {
           console.error('[ERROR]', ...args);
       },
       debug: (...args) => {
           if (logLevel === 'debug') {
               console.debug('[DEBUG]', ...args);
           }
       },
   };
   
   module.exports = logger;
   

10. Add start and dev scripts to package.json:

    json

    // package.json (add/modify scripts section)
    "scripts": {
      "start": "node src/server.js",
      "dev": "nodemon src/server.js",
      "test": "echo \"Error: no test specified\" && exit 1"
      // Add prisma scripts here if using database, e.g.,
      // "prisma:migrate:dev": "prisma migrate dev",
      // "prisma:generate": "prisma generate"
    },
    

2. Vonage Configuration and Integration

Before writing the core sending logic, we need to configure Vonage correctly.

1. Log in to Vonage: Go to your Vonage API Dashboard.

2. Create a Vonage Application: Using the Messages API requires a Vonage Application. This application acts as a container for configuration, including webhook URLs and security credentials (public/private key pair for JWT authentication).

   • Navigate to ""Applications"" in the dashboard menu.
   • Click ""Create a new application"".
   • Give it a name (e.g., ""Node Bulk SMS App"").
   • Click ""Generate public and private key"". A private.key file will be downloaded. Save this file in your project's root directory. Ensure the path in your .env file (VONAGE_PRIVATE_KEY_PATH) matches its location (e.g., ./private.key). Also add private.key to your .gitignore.
   • Enable the ""Messages"" capability.
   • Status URL: This is crucial for tracking message delivery status (Delivery Receipts - DLRs).
     • If developing locally, run ngrok http YOUR_PORT (e.g., ngrok http 3000).
     • Copy the https:// Forwarding URL provided by ngrok.
     • Enter the Status URL as YOUR_NGROK_HTTPS_URL/api/sms/status (we'll create this endpoint later).
     • In production, use your server's public URL.
   • Inbound URL: If you also need to receive SMS replies, enter a URL like YOUR_NGROK_HTTPS_URL/api/sms/inbound. For sending only, this can often be left blank or set the same as the status URL, but ideally, you should handle it even if just with a 200 OK.
   • Click ""Generate new application"".
   • Note the Application ID provided. Add this to your .env file (VONAGE_APPLICATION_ID).

3. Link Your Vonage Number:

   • Go back to the Application details page (Applications -> Your App Name).
   • Scroll down to ""Link virtual numbers"".
   • Select the Vonage virtual number you want to send SMS from. Add this number (in E.164 format, e.g., 14155550100) to your .env file (VONAGE_NUMBER).
   • Click ""Link"".

4. Set Default SMS API (Important):

   • Navigate to ""Settings"" in the dashboard menu.
   • Under ""API settings"" -> ""SMS settings"", ensure ""Messages API"" is selected as the default API for sending SMS messages. Vonage has older APIs, and using the wrong one will cause unexpected behavior or webhook format mismatches.
   • Click ""Save changes"".

5. Initialize Vonage Client (src/services/vonageService.js):

   javascript

   // src/services/vonageService.js
   require('dotenv').config(); // Ensure env vars are loaded
   const { Vonage } = require('@vonage/server-sdk');
   const { SMS } = require('@vonage/messages'); // Import SMS class for message construction
   const logger = require('../utils/logger');
   const fs = require('fs'); // Needed to read the private key
   const { default: PQueue } = require('p-queue'); // Use default import syntax
   
   // Ensure required environment variables are present
   if (!process.env.VONAGE_APPLICATION_ID || !process.env.VONAGE_PRIVATE_KEY_PATH || !process.env.VONAGE_NUMBER) {
       logger.error('Missing required Vonage environment variables (VONAGE_APPLICATION_ID, VONAGE_PRIVATE_KEY_PATH, VONAGE_NUMBER)');
       process.exit(1); // Exit if critical config is missing
   }
   
   let privateKey;
   try {
       privateKey = fs.readFileSync(process.env.VONAGE_PRIVATE_KEY_PATH);
   } catch (err) {
       logger.error(`Error reading private key from ${process.env.VONAGE_PRIVATE_KEY_PATH}:`, err);
       process.exit(1);
   }
   
   const vonageClient = new Vonage({
       applicationId: process.env.VONAGE_APPLICATION_ID,
       privateKey: privateKey
   });
   
   // --- Core Sending Function ---
   const sendSingleSms = async (message) => {
       try {
           // The message object should already be created using 'new SMS(...)' before calling this
           const response = await vonageClient.messages.send(message);
           logger.info(`Message sent successfully to ${message.to}. Message UUID: ${response.message_uuid}`);
           return { success: true, message_uuid: response.message_uuid, recipient: message.to };
       } catch (error) {
           // Log detailed error information from Vonage if available
           const errorDetails = error?.response?.data || error.message;
           logger.error(`Error sending message to ${message.to}:`, JSON.stringify(errorDetails));
           return { success: false, error: errorDetails, recipient: message.to };
       }
   };
   
   // --- Simple Sequential Sending (Basic Approach) ---
   const sendBulkSmsSequential = async (recipients, messageText) => {
       const results = [];
       for (const recipient of recipients) {
           // Construct the message object for each recipient
           const message = new SMS({
               to: recipient,
               from: process.env.VONAGE_NUMBER,
               text: messageText,
               channel: 'sms',
               message_type: 'text'
           });
           const result = await sendSingleSms(message);
           results.push(result);
           // Simple delay to respect basic rate limits (e.g., 1 SMS/sec for Long Codes)
           // Adjust delay based on your number type and regulations (e.g., 10DLC)
           await new Promise(resolve => setTimeout(resolve, 1100));
       }
       return results;
   };
   
   // --- Concurrent Sending with Rate Limiting (Advanced Approach using p-queue) ---
   
   // Configure queue based on .env settings or defaults
   const concurrency = parseInt(process.env.VONAGE_CONCURRENCY || '5', 10);
   const intervalMs = parseInt(process.env.VONAGE_INTERVAL_MS || '1000', 10);
   const intervalCap = parseInt(process.env.VONAGE_INTERVAL_CAP || '5', 10);
   
   const queue = new PQueue({
       concurrency: concurrency,
       interval: intervalMs,
       intervalCap: intervalCap,
       // autoStart: true // default
   });
   
   logger.info(`Initialized Vonage send queue: Concurrency=${concurrency}, Interval=${intervalMs}ms, Cap=${intervalCap}`);
   
   const sendBulkSmsConcurrent = async (recipients, messageText) => {
       const sendPromises = recipients.map(recipient => {
           // Add the task function to the queue
           return queue.add(async () => {
               // Construct the message object inside the queued task
               const message = new SMS({
                   to: recipient,
                   from: process.env.VONAGE_NUMBER,
                   text: messageText,
                   channel: 'sms',
                   message_type: 'text'
               });
               return sendSingleSms(message); // Execute the sending logic
           });
       });
   
       // Wait for all tasks scheduled in the queue to complete
       const results = await Promise.all(sendPromises);
       return results;
   };
   
   // --- Webhook Handlers ---
   const handleStatusWebhook = (payload) => {
       logger.info('Received Status Webhook:', JSON.stringify(payload, null, 2));
       // Key fields: message_uuid, status (delivered, expired, failed, rejected, accepted, buffered), timestamp, error, to, from
       if (payload.status === 'delivered') {
           logger.info(`Message ${payload.message_uuid} successfully delivered to ${payload.to}`);
       } else if (payload.status === 'failed' || payload.status === 'rejected') {
           const reason = payload.error?.reason || payload.error?.code || payload.error?.type || 'Unknown error';
           logger.error(`Message ${payload.message_uuid} to ${payload.to} failed/rejected. Status: ${payload.status}, Reason: ${reason}`);
           // See Vonage DLR error codes for details:
           // https://developer.vonage.com/en/messaging/sms/guides/delivery-receipts#dlr-error-codes
       } else {
           logger.info(`Message ${payload.message_uuid} to ${payload.to} has status: ${payload.status}`);
       }
       // TODO: Add logic to update database record based on payload.message_uuid (see Optional Section 4)
   };
   
   const handleInboundWebhook = (payload) => {
       logger.info('Received Inbound SMS:', JSON.stringify(payload, null, 2));
       // Key fields: msisdn (sender), to (your Vonage number), text, messageId, message-timestamp, type (usually 'text')
       // TODO: Process the inbound message (e.g., store in DB, trigger reply)
   };
   
   module.exports = {
       sendSingleSms,
       sendBulkSmsSequential, // Export basic version
       sendBulkSmsConcurrent, // Export advanced version
       handleStatusWebhook,
       handleInboundWebhook,
       // vonageClient // Optionally export client if needed elsewhere
   };
   
   • This service initializes the Vonage client using Application ID and Private Key (JWT) from .env.
   • It provides sendSingleSms for sending one message.
   • sendBulkSmsSequential: Simple loop, inefficient for large volumes.
   • sendBulkSmsConcurrent: Uses p-queue for rate-limited concurrency - recommended for bulk.
   • Includes placeholder functions for handling status and inbound webhooks.

3. Building the API Layer

Now, let's create the Express routes and controller to handle incoming bulk SMS requests and webhook callbacks.

1. Create SMS Controller (src/controllers/smsController.js):

   javascript

   // src/controllers/smsController.js
   const vonageService = require('../services/vonageService');
   const logger = require('../utils/logger');
   // Optional: Import Prisma client if using database
   // const prisma = require('../utils/prismaClient');
   
   const sendBulkHandler = async (req, res, next) => {
       const { recipients, message } = req.body;
   
       // Basic Validation
       if (!recipients || !Array.isArray(recipients) || recipients.length === 0) {
           return res.status(400).json({ error: 'Invalid or empty "recipients" array is required.' });
       }
       if (!message || typeof message !== 'string' || message.trim() === '') {
           return res.status(400).json({ error: 'Invalid or empty "message" string is required.' });
       }
       // TODO: Add more robust phone number validation (e.g., check E.164 format) for each recipient
   
       try {
           logger.info(`Received bulk send request for ${recipients.length} recipients.`);
   
           // Choose the sending strategy: concurrent is generally preferred for bulk
           const results = await vonageService.sendBulkSmsConcurrent(recipients, message);
           // const results = await vonageService.sendBulkSmsSequential(recipients, message); // Alternative
   
           const summary = {
               total_requested: recipients.length,
               total_sent_successfully: results.filter(r => r.success).length,
               total_failed: results.filter(r => !r.success).length,
               // Optionally include detailed results (can be large, consider omitting in production response)
               // details: results
           };
   
           logger.info(`Bulk send attempt completed. Success: ${summary.total_sent_successfully}, Failed: ${summary.total_failed}`);
   
           // Log failures with details
           results.filter(r => !r.success).forEach(failure => {
               logger.error(`Failed sending to ${failure.recipient}: ${JSON.stringify(failure.error)}`);
           });
   
           // Respond with 202 Accepted as the process is asynchronous (delivery happens later)
           res.status(202).json({ message: 'Bulk send request processed.', summary });
   
       } catch (error) {
           logger.error('Error in sendBulkHandler:', error);
           // Pass error to the global error handler
           next(error);
       }
   };
   
   const statusWebhookHandler = (req, res) => {
       const payload = req.body;
       try {
           vonageService.handleStatusWebhook(payload);
           // Respond quickly to Vonage to acknowledge receipt
           res.status(200).send('OK');
       } catch (error) {
           logger.error('Error processing status webhook:', error);
           // Still try to send OK, but log the error server-side
           res.status(500).send('Error processing webhook');
       }
   };
   
   const inboundWebhookHandler = (req, res) => {
       const payload = req.body;
       try {
           vonageService.handleInboundWebhook(payload);
            // Respond quickly to Vonage
            res.status(200).send('OK');
       } catch (error) {
           logger.error('Error processing inbound webhook:', error);
            res.status(500).send('Error processing webhook');
       }
   };
   
   module.exports = {
       sendBulkHandler,
       statusWebhookHandler,
       inboundWebhookHandler,
   };
   
   • Handles the /bulk request, validates input, calls the appropriate vonageService function, logs results, and returns a summary.
   • Includes handlers for status and inbound webhooks. Crucially, these must respond with 200 OK quickly, otherwise Vonage will retry.

2. Create SMS Routes (src/routes/smsRoutes.js):

   javascript

   // src/routes/smsRoutes.js
   const express = require('express');
   const smsController = require('../controllers/smsController');
   // Optional: Add rate limiting middleware for the API endpoint
   const rateLimit = require('express-rate-limit');
   
   const router = express.Router();
   
   // Rate Limiter for the bulk send endpoint (adjust limits as needed)
   // Apply this only if the API is exposed to potentially untrusted clients
   const bulkSendLimiter = rateLimit({
       windowMs: 15 * 60 * 1000, // 15 minutes
       max: 100, // Limit each IP to 100 requests per windowMs
       message: 'Too many bulk send requests created from this IP, please try again after 15 minutes',
       standardHeaders: true, // Return rate limit info in the `RateLimit-*` headers
       legacyHeaders: false, // Disable the `X-RateLimit-*` headers
   });
   
   // Route for sending bulk messages
   // Apply rate limiting middleware specifically to this sensitive endpoint if needed
   router.post('/bulk', bulkSendLimiter, smsController.sendBulkHandler);
   
   // Routes for Vonage Webhooks (Status/DLR and Inbound)
   // These should generally NOT be rate-limited as they come from Vonage IPs.
   // Ensure body parsing middleware (express.json, express.urlencoded) is applied *before* these routes in server.js.
   router.post('/status', smsController.statusWebhookHandler);
   router.get('/status', (req, res) => res.status(200).send('Status webhook GET endpoint reached. Use POST.')); // Handle potential GET probe
   
   router.post('/inbound', smsController.inboundWebhookHandler);
   router.get('/inbound', (req, res) => res.status(200).send('Inbound webhook GET endpoint reached. Use POST.')); // Handle potential GET probe
   
   module.exports = router;
   
   • Defines the API endpoints:
     • POST /api/sms/bulk: Accepts the bulk send request. Includes optional express-rate-limit.
     • POST /api/sms/status: Receives delivery receipts from Vonage.
     • POST /api/sms/inbound: Receives incoming SMS messages from Vonage.
     • Includes simple GET handlers for webhook URLs as Vonage sometimes probes with GET.

3. Testing the /bulk endpoint:

   • Ensure your .env file is populated with your Vonage Application ID, Private Key Path, and Vonage Number.
   • Place the private.key file in the project root.
   • Start the server: npm run dev
   • Use curl or Postman:

   Curl Example:

   bash

   curl -X POST http://localhost:3000/api/sms/bulk \
   -H "Content-Type: application/json" \
   -d '{
     "recipients": ["YOUR_TEST_PHONE_NUMBER_1", "YOUR_TEST_PHONE_NUMBER_2"],
     "message": "Hello from Node.js Bulk Sender! Test message."
   }'
   # Replace YOUR_TEST_PHONE_NUMBER_* with actual E.164 formatted numbers (e.g., 14155550101)
   

   Expected Response (JSON):

   json

   {
       "message": "Bulk send request processed.",
       "summary": {
           "total_requested": 2,
           "total_sent_successfully": 2, // Or based on actual outcome
           "total_failed": 0 // Or based on actual outcome
       }
   }
   
   • Check your terminal logs for detailed output from the logger, including message UUIDs for successful sends and error details for failures.
   • Check your test phone(s) for the SMS message.
   • If using ngrok, check the ngrok console (http://localhost:4040) for incoming POST requests to /api/sms/status (these are the delivery reports). Check your server logs to see how handleStatusWebhook processed them.

4. (Optional) Creating a Database Schema and Data Layer with Prisma

Managing large recipient lists and tracking message status is easier with a database. Let's integrate PostgreSQL using Prisma.

1. Install PostgreSQL: Use Docker (recommended) or install natively.

   • Docker: Create a docker-compose.yml:
     yaml

     # docker-compose.yml
     version: '3.8'
     services:
       db:
         image: postgres:14 # Use a specific version
         restart: always
         environment:
           POSTGRES_USER: your_db_user
           POSTGRES_PASSWORD: your_db_password
           POSTGRES_DB: vonage_bulk_sms
         ports:
           - ""5432:5432""
         volumes:
           - postgres_data:/var/lib/postgresql/data
     
     volumes:
       postgres_data:
     
     • Run docker-compose up -d.
     • Update your .env DATABASE_URL accordingly: DATABASE_URL=""postgresql://your_db_user:your_db_password@localhost:5432/vonage_bulk_sms?schema=public""

2. Initialize Prisma:

   bash

   npx prisma init --datasource-provider postgresql
   

   This creates prisma/schema.prisma and updates .env with a placeholder DATABASE_URL. Ensure your actual DATABASE_URL is correct in .env.

3. Define Schema (prisma/schema.prisma):

   sql

   // prisma/schema.prisma
   generator client {
     provider = ""prisma-client-js""
   }
   
   datasource db {
     provider = ""postgresql""
     url      = env(""DATABASE_URL"")
   }
   
   // Model for storing recipient information
   model Recipient {
     id          Int      @id @default(autoincrement())
     phoneNumber String   @unique @map(""phone_number"") // E.164 format
     firstName   String?  @map(""first_name"")
     lastName    String?  @map(""last_name"")
     list        String   // Categorize recipients (e.g., 'newsletter', 'promotions')
     subscribed  Boolean  @default(true)
     createdAt   DateTime @default(now()) @map(""created_at"")
     updatedAt   DateTime @updatedAt @map(""updated_at"")
   
     // Relation to message logs for this recipient
     messageLogs MessageLog[]
   
     @@map(""recipients"")
   }
   
   // Model for logging individual message send attempts and status updates
   model MessageLog {
     id           Int       @id @default(autoincrement())
     recipientId  Int       @map(""recipient_id"") // Foreign key to Recipient
     messageUuid  String?   @unique @map(""message_uuid"") // Vonage message ID (nullable for initial failures)
     status       String    // e.g., submitted, sent, delivered, failed, rejected
     errorCode    String?   @map(""error_code"") // Store error code/reason from Vonage DLR
     errorMessage String?   @map(""error_message"") @db.Text // Store detailed error message if available
     submittedAt  DateTime  @default(now()) @map(""submitted_at"") // When we tried to send
     statusAt     DateTime? @map(""status_at"") // When the final status was received via webhook
   
     // Relation back to the recipient
     recipient Recipient @relation(fields: [recipientId], references: [id], onDelete: Cascade)
   
     @@index([recipientId])
     @@index([status])
     @@index([messageUuid]) // Index for quick lookup by Vonage UUID
     @@map(""message_logs"")
   }
   
   // Optional: Model for Bulk Send Campaigns
   model BulkCampaign {
       id          Int      @id @default(autoincrement())
       name        String
       messageText String   @map(""message_text"") @db.Text
       listTarget  String   @map(""list_target"") // Which recipient list to use
       status      String   // e.g., pending, processing, completed, failed
       startedAt   DateTime? @map(""started_at"")
       completedAt DateTime? @map(""completed_at"")
       createdAt   DateTime @default(now()) @map(""created_at"")
       // Optional: Add relation to MessageLog if needed (can get complex)
   
       @@map(""bulk_campaigns"")
   }
   

4. Apply Schema Migrations:

   bash

   # Create the first migration file and apply it to the database
   npx prisma migrate dev --name init
   
   # Generate Prisma Client based on the schema
   npx prisma generate
   

   This creates the tables in your database and generates the Prisma Client library in node_modules/@prisma/client.

5. Integrate Prisma Client:

   • Create a Prisma client instance (src/utils/prismaClient.js):
     javascript

     // src/utils/prismaClient.js
     const { PrismaClient } = require('@prisma/client');
     const prisma = new PrismaClient();
     module.exports = prisma;
     
   • Modify smsController.js to use Prisma:
     javascript

     // src/controllers/smsController.js (modified example with Prisma)
     const vonageService = require('../services/vonageService');
     const logger = require('../utils/logger');
     const prisma = require('../utils/prismaClient'); // Import Prisma client
     
     // Modified handler to fetch recipients from DB and log results
     const sendBulkHandler = async (req, res, next) => {
         // Example: Get list target from request body instead of raw numbers
         const { listTarget, message } = req.body;
     
         if (!listTarget) return res.status(400).json({ error: '""listTarget"" is required.' });
         if (!message || typeof message !== 'string' || message.trim() === '') {
             return res.status(400).json({ error: 'Invalid or empty ""message"" string is required.' });
         }
     
         try {
             // 1. Fetch subscribed recipients from the specified list
             const recipientsFromDb = await prisma.recipient.findMany({
                 where: {
                     list: listTarget,
                     subscribed: true,
                 },
                 select: {
                     id: true, // Need ID to link logs
                     phoneNumber: true,
                 }
             });
     
             if (recipientsFromDb.length === 0) {
                 logger.warn(`No subscribed recipients found for list: ${listTarget}`);
                 return res.status(404).json({ message: `No subscribed recipients found for list: ${listTarget}` });
             }
     
             const recipientNumbers = recipientsFromDb.map(r => r.phoneNumber);
             logger.info(`Fetched ${recipientNumbers.length} recipients from list ""${listTarget}"". Starting send.`);
     
             // TODO: Implement logic to create MessageLog entries *before* sending,
             //       then update them based on send results and webhooks.
             //       This example focuses on fetching; full logging requires more steps.
     
             // 2. Send messages using the fetched numbers
             const results = await vonageService.sendBulkSmsConcurrent(recipientNumbers, message);
     
             // 3. Process results and potentially update DB (simplified example)
             const summary = {
                 total_requested: recipientNumbers.length,
                 total_sent_successfully: results.filter(r => r.success).length,
                 total_failed: results.filter(r => !r.success).length,
             };
     
             logger.info(`Bulk send attempt for list ""${listTarget}"" completed. Success: ${summary.total_sent_successfully}, Failed: ${summary.total_failed}`);
     
             results.filter(r => !r.success).forEach(failure => {
                 logger.error(`Failed sending to ${failure.recipient}: ${JSON.stringify(failure.error)}`);
                 // TODO: Update corresponding MessageLog entry in DB to 'failed' status
             });
     
             results.filter(r => r.success).forEach(success => {
                 // TODO: Update corresponding MessageLog entry in DB with message_uuid and 'submitted' status
                 logger.info(`Submitted message to ${success.recipient}, UUID: ${success.message_uuid}`);
             });
     
             res.status(202).json({ message: `Bulk send request for list ""${listTarget}"" processed.`, summary });
     
         } catch (error) {
             logger.error(`Error in sendBulkHandler for list ${listTarget}:`, error);
             next(error);
         }
     };
     
     // Modify webhook handlers to update DB based on message_uuid
     const statusWebhookHandler = async (req, res) => {
         const payload = req.body;
         try {
             vonageService.handleStatusWebhook(payload); // Log the webhook first
     
             // Update database record
             if (payload.message_uuid) {
                 await prisma.messageLog.updateMany({ // Use updateMany as UUID should be unique
                     where: { messageUuid: payload.message_uuid },
                     data: {
                         status: payload.status,
                         statusAt: new Date(payload.timestamp), // Convert timestamp string to Date
                         errorCode: payload.error?.code || payload.error?.type,
                         errorMessage: payload.error?.reason,
                     },
                 });
                 logger.info(`Updated DB status for message ${payload.message_uuid} to ${payload.status}`);
             } else {
                 logger.warn('Status webhook received without message_uuid:', payload);
             }
     
             res.status(200).send('OK');
         } catch (error) {
             logger.error('Error processing status webhook and updating DB:', error);
             res.status(500).send('Error processing webhook');
         }
     };
     
     // Modify inbound handler if storing inbound messages
     const inboundWebhookHandler = async (req, res) => {
          const payload = req.body;
          try {
              vonageService.handleInboundWebhook(payload); // Log the webhook
     
              // TODO: Add logic to store inbound message in DB if needed
              // Example: Find recipient by sender number, create an inbound log entry
     
              res.status(200).send('OK');
          } catch (error) {
              logger.error('Error processing inbound webhook:', error);
              res.status(500).send('Error processing webhook');
          }
     };
     
     module.exports = {
         sendBulkHandler,
         statusWebhookHandler,
         inboundWebhookHandler,
     };