This guide provides a complete walkthrough for building a robust bulk SMS broadcasting system using Node.js, Express, and the Vonage Communication APIs. We'll cover everything from project setup and core sending logic to error handling, security, performance optimization, and deployment, enabling you to reliably send messages to large audiences.

The final system will feature a simple REST API endpoint that accepts a list of recipients and a message, efficiently handles sending via Vonage, manages rate limits, logs results, and incorporates best practices for production environments.

Project Overview and Goals

What We're Building:

A Node.js application using the Express framework that exposes a secure API endpoint (/broadcast) to:

1. Accept a list of phone numbers and a message payload.
2. Iterate through the list and send the message to each recipient using the Vonage SMS API.
3. Handle Vonage API rate limits gracefully (through client-side throttling or queuing).
4. Provide structured logging for successful sends and errors.
5. Offer a foundation for more advanced features like status tracking and scheduling.

Problem Solved:

This application addresses the need to send SMS messages programmatically to multiple recipients simultaneously (broadcast) without manually sending each one. It provides a scalable and manageable way to handle bulk SMS communications for notifications, alerts, marketing campaigns, or other use cases.

Technologies Used:

• Node.js: A JavaScript runtime built on Chrome's V8 engine, ideal for building scalable network applications.
• Express.js: A minimal and flexible Node.js web application framework, providing a robust set of features for web and mobile applications.
• Vonage SMS API: A powerful API for sending and receiving SMS messages globally. We'll use the @vonage/server-sdk for Node.js.
• dotenv: A zero-dependency module that loads environment variables from a .env file into process.env.
• pino: A very low overhead Node.js logger for structured logging.
• (Optional but Recommended) Prisma: A next-generation ORM for Node.js and TypeScript, useful for database interactions if storing broadcast history or recipient lists.
• (Optional but Recommended) p-limit: For client-side request throttling.
• (Optional but Recommended) express-rate-limit: Middleware for basic API endpoint rate limiting.
• (Optional but Recommended) Helmet: Middleware for setting security-related HTTP headers.

System Architecture:

+-----------------+      +---------------------+      +-----------------+      +-----------------+
|   API Client    |----->|  Node.js / Express  |----->|  Vonage SDK     |----->| Vonage SMS API  |
| (e.g., curl, UI)|      |  (Broadcast API)    |      | (@vonage/server)|      |                 |
+-----------------+      +---------------------+      +-----------------+      +-----------------+
       | Request              |  - Validate Input                    | Send SMS
       | (Recipients, Msg)    |  - Throttle/Queue Sends              | Request
       |                      |  - Call Vonage SDK                   |
       |                      |  - Log Results (Structured)          |
       |<---------------------|                                      |<-----------------+
       | Response             |<-------------------------------------| Response (Status)|
       | (Success/Failure)                                           +-----------------+

Prerequisites:

• Node.js (LTS version recommended) and npm installed.
• A Vonage API account. Sign up here if you don't have one.
• Your Vonage API Key and API Secret.
• A Vonage virtual phone number or configured Alphanumeric Sender ID (availability depends on the destination country).
• Basic familiarity with JavaScript_ Node.js_ and REST APIs.
• (Optional) curl or a tool like Postman for testing the API.

Expected Outcome:

A functional Node.js Express application running locally_ capable of accepting POST requests to /broadcast and sending SMS messages to the provided recipients via Vonage. The application will include structured logging_ awareness of rate limiting_ and basic security considerations.

1. Setting up the Project

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

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

   bash

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

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

   bash

   npm init -y
   

3. Install Dependencies: We need Express_ the Vonage SDK_ dotenv_ pino for logging_ and optionally pino-pretty for development. Install the latest versions.

   bash

   npm install express @vonage/server-sdk dotenv pino
   npm install --save-dev pino-pretty # For development logging only
   
   • express: The web framework.
   • @vonage/server-sdk: The official Vonage Server SDK for Node.js.
   • dotenv: Loads environment variables from a .env file.
   • pino: Fast_ structured JSON logger.
   • pino-pretty: (Dev dependency) Formats Pino logs for readability during development.

4. Enable ES Modules (Optional but Recommended): To use modern import/export syntax_ open your package.json file and add the following line:

   json

   // package.json
   {
     "name": "vonage-bulk-sms"_
     "version": "1.0.0"_
     "description": ""_
     "main": "index.js"_
     "type": "module"_ // <-- Add this line
     "scripts": {
       "start": "node index.js"_
       "dev": "node index.js | pino-pretty"_ // Example dev script using pino-pretty
       "test": "echo \"Error: no test specified\" && exit 1"
     }_
     "keywords": []_
     "author": ""_
     "license": "ISC"_
     "dependencies": {
       "@vonage/server-sdk": "^3.0.0"_ // Example version_ use actual installed version
       "dotenv": "^16.0.0"_           // Example version_ use actual installed version
       "express": "^4.0.0"_          // Example version_ use actual installed version
       "pino": "^8.0.0"              // Example version_ use actual installed version
     }_
     "devDependencies": {
       "pino-pretty": "^9.0.0"       // Example version_ use actual installed version
     }
   }
   

5. Create Core Files: Create the main application file_ logger configuration_ service files_ and environment file.

   bash

   touch index.js logger.js vonageClient.js smsService.js .env
   

6. Configure Environment Variables (.env): Open the .env file and add your Vonage credentials and sender ID. Never commit this file to version control.

   bash

   # .env
   
   # Vonage API Credentials
   # Found in your Vonage Dashboard: https://dashboard.nexmo.com/getting-started/api-accounts
   VONAGE_API_KEY=YOUR_API_KEY
   VONAGE_API_SECRET=YOUR_API_SECRET
   
   # Vonage Sender ID (Virtual Number or Alphanumeric Sender ID)
   # Purchase numbers: https://dashboard.nexmo.com/numbers/buy
   # Note: Alphanumeric Sender ID support varies by country and requires registration.
   # For US/Canada_ you generally MUST use a Vonage number registered for A2P 10DLC.
   VONAGE_SENDER_ID=YOUR_VONAGE_NUMBER_OR_SENDER_ID
   
   # Application Port
   PORT=3000
   
   # Logging Level (e.g._ 'info'_ 'debug'_ 'warn'_ 'error')
   LOG_LEVEL=info
   
   # (Optional) API Key for securing the broadcast endpoint (See Section 7)
   # INTERNAL_API_KEY=your-secret-api-key-here
   
   • VONAGE_API_KEY_ VONAGE_API_SECRET_ VONAGE_SENDER_ID: Your Vonage details. Crucially_ for sending to US numbers_ VONAGE_SENDER_ID typically needs to be a 10DLC-registered Vonage number. See Section 8.
   • PORT: Application port.
   • LOG_LEVEL: Controls logging verbosity.
   • INTERNAL_API_KEY: (Optional) Used for basic API security later.

7. Configure Logger (logger.js): Set up the shared Pino logger instance.

   javascript

   // logger.js
   import pino from 'pino';
   
   const logger = pino({
     level: process.env.LOG_LEVEL || 'info'_
     // Use pino-pretty for development_ structured JSON for production
     transport: process.env.NODE_ENV !== 'production'
       ? { target: 'pino-pretty'_ options: { colorize: true } }
       : undefined_ // In production_ log JSON to stdout
   });
   
   export default logger;
   

8. Create Basic Express Server (index.js): Set up the initial Express application structure using the logger.

   javascript

   // index.js
   import express from 'express';
   import 'dotenv/config'; // Load .env variables into process.env
   import logger from './logger.js'; // Import the configured logger
   
   const app = express();
   const port = process.env.PORT || 3000;
   
   // Middleware to parse JSON bodies
   app.use(express.json());
   // Middleware to parse URL-encoded bodies
   app.use(express.urlencoded({ extended: true }));
   
   // Simple health check endpoint
   app.get('/health'_ (req_ res) => {
     res.status(200).json({ status: 'OK', timestamp: new Date().toISOString() });
   });
   
   // Placeholder for our broadcast endpoint
   app.post('/broadcast', (req, res) => {
     // We will implement this in the next steps
     logger.warn('Broadcast endpoint hit but not implemented yet.');
     res.status(501).json({ message: 'Broadcast endpoint not implemented yet.' });
   });
   
   // Global error handler (using logger)
   app.use((err, req, res, next) => {
     logger.error({ err: err, stack: err.stack }, 'An unexpected error occurred');
     res.status(500).json({ message: 'Internal Server Error' });
   });
   
   
   app.listen(port, () => {
     logger.info(`Server running on http://localhost:${port}`);
     // Check if Vonage credentials are loaded (basic check)
     if (!process.env.VONAGE_API_KEY || !process.env.VONAGE_API_SECRET || !process.env.VONAGE_SENDER_ID) {
       logger.warn('Vonage API credentials or Sender ID are missing in .env file. SMS sending will fail.');
     }
   });
   
   export default app; // Export app for potential testing
   
   • We import and use our logger.
   • A dev script is added to package.json to pipe output through pino-pretty.

9. Run the Application: Start the server using the dev script for readable logs.

   bash

   npm run dev
   

   You should see Server running on http://localhost:3000. Test the health check: curl http://localhost:3000/health.

2. Implementing Core Functionality: Sending SMS

Now, let's implement the logic to send SMS messages using the Vonage SDK and our logger.

1. Initialize Vonage SDK (vonageClient.js):

   javascript

   // vonageClient.js
   import { Vonage } from '@vonage/server-sdk';
   import 'dotenv/config';
   import logger from './logger.js';
   
   // Basic validation for environment variables
   if (!process.env.VONAGE_API_KEY || !process.env.VONAGE_API_SECRET) {
     logger.fatal('Vonage API Key or Secret is not defined in .env file. Application cannot start.');
     process.exit(1); // Exit if essential credentials are missing
   }
   
   const vonage = new Vonage({
     apiKey: process.env.VONAGE_API_KEY,
     apiSecret: process.env.VONAGE_API_SECRET,
     // Optional: Specify custom user agent for easier debugging/tracking
     userAgent: ""vonage-bulk-sms-guide/1.0.0""
   });
   
   logger.info('Vonage SDK initialized.');
   
   export default vonage;
   
   • We import and use the logger for initialization messages and fatal errors.

2. Create the Sending Function (smsService.js):

   javascript

   // smsService.js
   import vonage from './vonageClient.js'; // Import the initialized client
   import logger from './logger.js'; // Import the logger
   
   const sender = process.env.VONAGE_SENDER_ID;
   
   /**
    * Sends a single SMS message using the Vonage API.
    * @param {string} recipient - The recipient's phone number (E.164 format recommended).
    * @param {string} message - The text message content.
    * @returns {Promise<object&gt;} - A promise that resolves with a structured result object.
    */
   export async function sendSingleSms(recipient, message) {
     if (!sender) {
       logger.error('VONAGE_SENDER_ID is not defined in .env file.');
       // Throwing an error here might stop a whole batch; returning failure is often better.
       return { success: false, recipient: recipient, error: 'Server configuration error: Missing Sender ID', status: 'config_error' };
     }
     if (!recipient || !message) {
       logger.warn({ recipient: recipient }, 'Attempted to send SMS with missing recipient or message.');
       return { success: false, recipient: recipient || 'unknown', error: 'Recipient and message parameters are required.', status: 'param_error' };
     }
   
     const logPayload = { recipient: recipient, sender: sender };
     logger.info(logPayload, `Attempting to send SMS to ${recipient}...`);
   
     try {
       const responseData = await vonage.sms.send({
         to: recipient,
         from: sender,
         text: message,
         // Optional: Specify 'type: unicode' if your message contains emojis or non-GSM characters
         // type: 'unicode'
       });
   
       // Check the response status for the first (and usually only) message part
       if (responseData.messages[0].status === '0') {
         logger.info({ ...logPayload, msgId: responseData.messages[0]['message-id'] }, `Message sent successfully to ${recipient}.`);
         return { success: true, recipient: recipient, data: responseData.messages[0] };
       } else {
         const errorText = responseData.messages[0]['error-text'];
         const statusCode = responseData.messages[0].status;
         logger.error({ ...logPayload, vonageStatus: statusCode, vonageError: errorText, msgId: responseData.messages[0]['message-id'] }, `Message failed to send to ${recipient}.`);
         // We still resolve, but indicate failure in the returned object
         return { success: false, recipient: recipient, error: errorText, status: statusCode };
       }
     } catch (error) {
       logger.error({ ...logPayload, error: error }, `Failed to send SMS to ${recipient} due to API/SDK error.`);
       // Propagate the error for higher-level handling, but include recipient info
       return { success: false, recipient: recipient, error: error.message || 'Unknown API error', status: 'api_error' };
     }
   }
   
   • Replaced all console.* with logger.* calls, providing context objects.
   • Handles missing sender by returning a failure object instead of throwing, which is often better in batch processes.
   • Includes more contextual info (like msgId, vonageStatus) in logs.

3. Building the API Layer

Now, let's implement the /broadcast endpoint using our service and Promise.allSettled.

1. Import Service and Implement Endpoint (index.js): Update index.js to handle the /broadcast route.

   javascript

   // index.js
   import express from 'express';
   import 'dotenv/config';
   import logger from './logger.js';
   import { sendSingleSms } from './smsService.js'; // Import the sending function
   // import { requireApiKey } from './apiKeyAuth.js'; // Uncomment when adding API key auth
   // import rateLimit from 'express-rate-limit'; // Uncomment when adding rate limiting
   
   const app = express();
   const port = process.env.PORT || 3000;
   
   app.use(express.json());
   app.use(express.urlencoded({ extended: true }));
   
   // --- Health Check ---
   app.get('/health', (req, res) => {
     res.status(200).json({ status: 'OK', timestamp: new Date().toISOString() });
   });
   
   // --- (Optional) API Endpoint Rate Limiter ---
   /* // Uncomment to enable
   const broadcastLimiter = rateLimit({
     windowMs: 15 * 60 * 1000, // 15 minutes
     max: 100, // Limit each IP to 100 requests per windowMs
     message: 'Too many broadcast requests created from this IP, please try again after 15 minutes',
     standardHeaders: true, legacyHeaders: false,
     handler: (req, res, next, options) => {
         logger.warn({ ip: req.ip }, `Rate limit exceeded for ${req.ip}`);
         res.status(options.statusCode).send(options.message);
     }
   });
   */
   
   // --- Broadcast Endpoint Implementation ---
   app.post('/broadcast',
     // broadcastLimiter, // Uncomment to apply rate limiting
     // requireApiKey, // Uncomment to apply API key authentication
     async (req, res, next) => {
     const { recipients, message } = req.body;
   
     // --- 1. Input Validation ---
     if (!Array.isArray(recipients) || recipients.length === 0) {
       logger.warn({ body: req.body }, 'Invalid broadcast request: recipients missing or not an array.');
       return res.status(400).json({ message: 'Invalid input: "recipients" must be a non-empty array.' });
     }
     if (typeof message !== 'string' || message.trim() === '') {
       logger.warn({ body: req.body }, 'Invalid broadcast request: message missing or empty.');
       return res.status(400).json({ message: 'Invalid input: "message" must be a non-empty string.' });
     }
     // Add more validation (e.g., phone number format, max recipients) here if needed
   
     const uniqueRecipients = [...new Set(recipients.map(r => String(r).trim()))]; // Deduplicate and trim
     const recipientCount = uniqueRecipients.length;
     logger.info({ recipientCount: recipientCount }, `Received broadcast request for ${recipientCount} unique recipients.`);
   
     try {
       // --- 2. Process Sends Concurrently (with awareness of limits) ---
       // NOTE: This basic Promise.allSettled approach sends all requests concurrently.
       // For production bulk sending (more than ~20-30 recipients), you MUST implement
       // client-side throttling (see Section 5 using p-limit) or preferably a
       // job queue system (see Section 9) to avoid hitting Vonage rate limits.
       const sendPromises = uniqueRecipients.map(recipient =>
         sendSingleSms(recipient, message)
       );
   
       // Wait for all send attempts to complete (either success or failure)
       const results = await Promise.allSettled(sendPromises);
   
       // --- 3. Aggregate Results ---
       let successfulSends = 0;
       let failedSends = 0;
       const detailedResults = results.map((result, index) => {
         const recipient = uniqueRecipients[index]; // Get recipient corresponding to this result
         if (result.status === 'fulfilled') {
            // Check the success flag returned by sendSingleSms
           if(result.value.success) {
             successfulSends++;
             return { recipient: recipient, status: 'Sent', messageId: result.value.data['message-id'] };
           } else {
             failedSends++;
             return { recipient: recipient, status: 'Failed', error: result.value.error, vonage_status: result.value.status };
           }
         } else {
           // Promise itself rejected (e.g., unexpected error within sendSingleSms before return)
           failedSends++;
           logger.error({ recipient: recipient, reason: result.reason }, 'Send promise rejected unexpectedly.');
           return { recipient: recipient, status: 'Failed', error: result.reason?.message || 'Promise rejected' };
         }
       });
   
       logger.info({ recipientCount, successfulSends, failedSends }, `Broadcast finished processing.`);
   
       // --- 4. Send Response ---
       res.status(200).json({
         message: `Broadcast processed for ${recipientCount} unique recipients.`,
         successful_sends: successfulSends,
         failed_sends: failedSends,
         details: detailedResults,
       });
   
     } catch (error) {
       // Pass unexpected errors (outside promise handling) to the global error handler
       logger.error({ error: error }, 'Unhandled error during broadcast processing.');
       next(error);
     }
   });
   // --- End Broadcast Endpoint ---
   
   
   // Global error handler
   app.use((err, req, res, next) => {
     // Log the error including stack trace
     logger.error({ err: { message: err.message, stack: err.stack }, url: req.originalUrl, method: req.method }, 'An unexpected error occurred handling a request.');
     // Avoid sending stack trace to client in production
     const responseMessage = process.env.NODE_ENV === 'production' ? 'Internal Server Error' : err.message;
     res.status(err.status || 500).json({ message: responseMessage });
   });
   
   
   app.listen(port, () => {
     logger.info(`Server running on http://localhost:${port}`);
     if (!process.env.VONAGE_API_KEY || !process.env.VONAGE_API_SECRET || !process.env.VONAGE_SENDER_ID) {
       logger.warn('Vonage API credentials or Sender ID are missing in .env file. SMS sending will fail.');
     }
      if (!process.env.INTERNAL_API_KEY && process.env.NODE_ENV !== 'production') {
        logger.warn('INTERNAL_API_KEY is not set. The /broadcast endpoint may be unsecured if API key middleware is enabled.');
      }
   });
   
   export default app; // Export for testing
   
   • Integrates sendSingleSms and logger.
   • Uses Promise.allSettled to handle concurrent sends.
   • Includes a clear NOTE warning about the need for throttling/queuing in production for this basic concurrent approach.
   • Aggregates results and provides a detailed response.
   • Includes placeholders for adding API key auth and rate limiting middleware later.
   • Improved global error handler logging.

2. Test the Endpoint: Restart your server (npm run dev). Use curl or Postman:

   bash

   # Replace YOUR_PHONE_NUMBER_1/2 with actual E.164 formatted numbers
   # Ensure these numbers are registered as test numbers in your Vonage dashboard if using a trial account
   curl -X POST http://localhost:3000/broadcast \
      -H "Content-Type: application/json" \
      -d '{
            "recipients": ["YOUR_PHONE_NUMBER_1", "YOUR_PHONE_NUMBER_2", "1555INVALID"],
            "message": "Hello from Vonage Bulk Sender! (Test v2)"
          }'
   

   Check your terminal for structured logs (formatted by pino-pretty) and the JSON response, which should detail success/failure for each recipient.

4. Integrating with Vonage (Setup Recap & Details)

This section consolidates the Vonage-specific setup steps.

1. Sign Up/Log In: Access the Vonage API Dashboard.
2. Get API Key and Secret: Find these in the ""API settings"" section and add them to your .env file (VONAGE_API_KEY, VONAGE_API_SECRET). Keep the secret secure.
3. Obtain a Sender ID (Vonage Number):
   • Buy an SMS-capable number in the ""Numbers"" section of the dashboard.
   • Add the number (E.164 format) to .env as VONAGE_SENDER_ID.
   • A2P 10DLC (USA): CRITICAL. If sending to US numbers using a US long code, you must register your Brand and Campaign via the Vonage dashboard (""Brands and Campaigns"") and link your number(s). Failure leads to blocking. See Section 8.
   • Alphanumeric Sender ID: Check Vonage docs for country support and registration requirements if needed. Often not allowed for A2P traffic to US/Canada.
4. Test Numbers (Trial Accounts): Verify recipient numbers in the dashboard if using a trial account.
5. Environment Variables Review: Ensure .env has VONAGE_API_KEY, VONAGE_API_SECRET, VONAGE_SENDER_ID, PORT, LOG_LEVEL.

5. Error Handling, Logging, and Retry Mechanisms

Production systems need robust handling.

1. Consistent Error Handling: Our sendSingleSms returns structured objects ({ success: boolean, ... }) even on failure, allowing the broadcast endpoint to aggregate results properly. The global error handler catches unexpected exceptions.

2. Structured Logging: We've integrated pino (see logger.js).

   • Logs are in JSON format (good for log aggregators).
   • pino-pretty used for readable development logs (npm run dev).
   • Log levels (info, warn, error, fatal) are used appropriately. Contextual data is included in log objects.

3. Rate Limiting & Throttling (CRUCIAL for Bulk):

   • Vonage Limits: Be aware of Vonage API rate limits (~30 req/sec default, check your account) and per-number throughput limits (e.g., 1 SMS/sec for standard US long codes).

   • Client-Side Throttling: The Promise.allSettled in Section 3 is insufficient for production bulk sending. You must throttle requests.

     • Recommended Library: p-limit: Control concurrency.
     bash

     npm install p-limit
     

     Example using p-limit in the /broadcast handler (Conceptual Integration):

     javascript

     // In index.js /broadcast handler
     import pLimit from 'p-limit';
     // ... other imports like logger, sendSingleSms ...
     
     // Define the concurrency limit.
     // START CONSERVATIVELY! e.g., 5-10.
     // Adjust based on Vonage limits (e.g., if 30 req/sec limit, maybe try 15-25),
     // testing, and observed error rates (429 errors).
     const limit = pLimit(10); // Limit to 10 concurrent sendSingleSms calls
     
     // ... inside app.post('/broadcast', async (req, res, next) => { ... try block
     
     logger.info({ concurrency: 10 }, 'Processing sends with concurrency limit.'); // Log the limit used
     
     // Wrap the sendSingleSms call with the limiter inside the map
     const sendPromises = uniqueRecipients.map(recipient =>
         limit(() => sendSingleSms(recipient, message))
     );
     
     // Use Promise.allSettled here. It works well with p-limit.
     // It waits for all limited tasks to complete, regardless of individual success/failure.
     const results = await Promise.allSettled(sendPromises);
     
     // Process results (aggregation logic remains the same as Section 3)
     // ... rest of endpoint
     
     • Job Queue (Best for High Volume): See Section 9. Decouples API response from sending, handles rate limiting in background workers.

4. Retry Mechanisms:

   • Selective Retries: Only retry on transient errors (network issues, maybe temporary Vonage 5xx errors). Avoid retrying permanent failures (invalid number status: 3, auth errors status: 4).
   • Use Libraries: async-retry can help implement retries with exponential backoff.
   • Queue-Based Retries: Job queues (Section 9) usually have robust built-in retry features.

6. Creating a Database Schema and Data Layer (Optional)

For tracking history and detailed status, use a database.

1. Technology Choice: PostgreSQL/MySQL with Prisma ORM recommended.

2. Schema Design (Example using Prisma):

   sql

   // prisma/schema.prisma
   
   datasource db {
     provider = ""postgresql"" // or ""mysql"", ""sqlite""
     url      = env(""DATABASE_URL"")
   }
   
   generator client {
     provider = ""prisma-client-js""
   }
   
   model Broadcast {
     id              String    @id @default(cuid())
     message         String
     createdAt       DateTime  @default(now())
     status          String    @default(""PENDING"") // PENDING, PROCESSING, COMPLETED, FAILED
     totalRecipients Int
     submittedCount  Int       @default(0) // Count submitted to Vonage
     successCount    Int       @default(0) // Count confirmed success by Vonage API
     failedCount     Int       @default(0) // Count confirmed failure by Vonage API or SDK error
     deliveredCount  Int       @default(0) // Count confirmed delivered via DLR
     dlrFailedCount  Int       @default(0) // Count confirmed failed/expired via DLR
     messages        Message[] // Relation to individual message statuses
   }
   
   model Message {
     id            String     @id @default(cuid())
     broadcast     Broadcast  @relation(fields: [broadcastId], references: [id], onDelete: Cascade)
     broadcastId   String     @db.VarChar(255)
     recipient     String     @db.VarChar(20) // Store in E.164
     status        String     // PENDING, SUBMITTED, FAILED_API, FAILED_DLR, DELIVERED, EXPIRED
     vonageMsgId   String?    @unique @db.VarChar(50) // Store the message ID from Vonage
     vonageStatus  String?    @db.VarChar(10) // Store Vonage status code (e.g., '0', '3', '4')
     errorCode     String?    // Store DLR error code if failed
     errorText     String?    // Store Vonage error text or DLR description
     submittedAt   DateTime   @default(now())
     dlrReceivedAt DateTime?  // Timestamp when DLR was processed
     updatedAt     DateTime   @updatedAt
   
     @@index([broadcastId])
     @@index([status])
     @@index([recipient])
   }
   
   • Detailed status tracking for broadcasts and individual messages. Includes fields for DLR updates.

3. Setup Prisma: Follow standard Prisma setup (npm install prisma @prisma/client --save-dev, npx prisma init, configure .env, add schema, npx prisma migrate dev, npx prisma generate).

4. Data Access Layer (Conceptual): Create services using Prisma Client.

   javascript

   // broadcastDbService.js (Conceptual - Requires Prisma setup)
   import { PrismaClient } from '@prisma/client';
   const prisma = new PrismaClient(); // Assumes Prisma client is generated
   
   export async function createBroadcastRecord(message, recipients) {
     const uniqueRecipients = [...new Set(recipients.map(r => String(r).trim()))];
     logger.info(`Creating broadcast record for ${uniqueRecipients.length} recipients.`);
     return prisma.broadcast.create({
       data: {
         message: message,
         totalRecipients: uniqueRecipients.length,
         status: 'PROCESSING', // Or 'QUEUED' if using a job queue
         messages: {
           create: uniqueRecipients.map(r => ({ recipient: r, status: 'PENDING' }))
         }
       },
       include: { messages: true } // Include messages for immediate processing
     });
   }
   
   export async function updateMessageStatusAfterSend(messageDbId, sendResult) {
     let updateData = {
        submittedAt: new Date(), // Mark submission time
        updatedAt: new Date()
     };
     if (sendResult.success) {
       updateData.status = 'SUBMITTED'; // Submitted to Vonage, awaiting DLR
       updateData.vonageMsgId = sendResult.data['message-id'];
       updateData.vonageStatus = sendResult.data.status; // Should be '0'
     } else {
       updateData.status = 'FAILED_API'; // Failed during API call or rejected by Vonage
       updateData.vonageStatus = sendResult.status; // Vonage status code (e.g., '3', '4') or custom error status
       updateData.errorText = sendResult.error;
     }
      logger.debug({ messageDbId, status: updateData.status, vonageMsgId: updateData.vonageMsgId }, 'Updating message status after send attempt.');
     return prisma.message.update({ where: { id: messageDbId }, data: updateData });
   }
   
   // Function to update broadcast counts based on message outcomes (call after batch finishes)
   export async function updateBroadcastSummary(broadcastId) {
       // Complex logic: Query message statuses for the broadcastId, calculate counts,
       // update Broadcast record (submittedCount, successCount, failedCount),
       // potentially update overall Broadcast status (e.g., to COMPLETED).
       // Consider using Prisma aggregate functions.
       logger.info({ broadcastId }, 'Updating broadcast summary counts.');
       // ... implementation omitted for brevity ...
   }
   
   // Function needed for DLR processing (Section 10)
   export async function updateMessageStatusFromDlr(vonageMsgI