+-----------+       +-----------------+      +-----------------+      +---------+      +---------------+      +-----------+
|   Client  | ----> | Express API     | ---> | Database (PG)   | <--- | Scheduler | ---> | Infobip API   | ---> | End User  |
| (e.g. UI, |       | (Node.js)       |      | (Reminders)     |      | (node-cron) |      | (via SDK)     |      | (via SMS) |
|   script) | <---- |                 |      +-----------------+      +-----------+      +---------------+      +-----------+
+-----------+       +-----------------+
  (Schedule          (Stores/Retrieves       (Checks for due
   Reminder)          Reminder Data)           reminders &
                                               triggers sending)

mkdir node-sms-reminders
cd node-sms-reminders

npm init -y

npm install express dotenv @infobip-api/sdk node-cron @prisma/client pg express-validator winston express-rate-limit

npm install -D prisma nodemon

{
  "name": "node-sms-reminders",
  "version": "1.0.0",
  "description": "SMS Reminder Application",
  "main": "src/server.js",
  "type": "module",
  "scripts": {
    "start": "node src/server.js",
    "dev": "nodemon src/server.js",
    "prisma:migrate": "prisma migrate dev",
    "prisma:generate": "prisma generate",
    "prisma:studio": "prisma studio",
    "test": "echo \"Error: no test specified\" && exit 1"
  },
  "dependencies": {
    "@infobip-api/sdk": "^2.0.0",
    "@prisma/client": "^5.0.0",
    "dotenv": "^16.0.0",
    "express": "^4.18.0",
    "express-rate-limit": "^7.0.0",
    "express-validator": "^7.0.0",
    "node-cron": "^3.0.0",
    "pg": "^8.10.0",
    "winston": "^3.9.0"
  },
  "devDependencies": {
    "nodemon": "^3.0.0",
    "prisma": "^5.0.0"
  }
}

npx prisma init --datasource-provider postgresql

# .env

# Database Connection
# Example: postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public
DATABASE_URL="postgresql://postgres:yourpassword@localhost:5432/sms_reminders?schema=public"

# Infobip API Credentials
# Get these from your Infobip account dashboard
INFOBIP_BASE_URL="YOUR_INFOBIP_BASE_URL" # e.g., xyzabc.api.infobip.com
INFOBIP_API_KEY="YOUR_INFOBIP_API_KEY"

# Application Settings
PORT=3000
NODE_ENV=development # Set to 'production' in deployment

# Security
# Generate a strong random string for API key authentication
# LEAVE EMPTY ONLY FOR LOCAL DEVELOPMENT WHERE AUTH IS INTENTIONALLY SKIPPED (NOT RECOMMENDED FOR PROD)
API_SECRET_KEY="YOUR_STRONG_SECRET_API_KEY_FOR_INTERNAL_AUTH"

# Scheduler Settings (Cron pattern: second minute hour day-of-month month day-of-week)
# Check every minute: '* * * * *'
# Check every 5 minutes: '*/5 * * * *'
SCHEDULER_CRON_PATTERN="* * * * *"

# Logging Level (optional, defaults to 'info')
# LOG_LEVEL=debug

node-sms-reminders/
├── prisma/
│   ├── schema.prisma
│   └── migrations/
├── src/
│   ├── config/
│   │   ├── index.js         # Load and export environment variables
│   │   ├── logger.js        # Winston logger configuration
│   │   ├── infobip.js       # Infobip client initialization
│   │   └── prisma.js        # Prisma client instance
│   ├── controllers/
│   │   └── reminderController.js # Handles API request logic
│   ├── middleware/
│   │   ├── authMiddleware.js     # API key authentication
│   │   ├── errorMiddleware.js    # Global error handler
│   │   └── validationMiddleware.js # Request validation rules
│   ├── routes/
│   │   └── reminderRoutes.js     # Defines API routes for reminders
│   ├── services/
│   │   ├── reminderService.js    # Business logic for reminders (DB interaction)
│   │   ├── schedulerService.js   # Logic for the cron scheduler
│   │   └── smsService.js         # Logic for sending SMS via Infobip
│   ├── utils/
│   │   └── timeUtils.js        # Time zone handling utilities (optional but recommended)
│   └── server.js          # Main application entry point (Express setup)
├── .env
├── .gitignore
├── package.json
└── package-lock.json
# Optional: jest.config.js, .eslintrc.json, etc.

mkdir -p src/config src/controllers src/middleware src/routes src/services src/utils

# .gitignore
node_modules
.env
dist
coverage
*.log

// prisma/schema.prisma

generator client {
  provider = ""prisma-client-js""
}

datasource db {
  provider = ""postgresql""
  url      = env(""DATABASE_URL"")
}

model Reminder {
  id        String   @id @default(cuid()) // Unique identifier
  recipient String   // Phone number to send the SMS to (E.164 format recommended)
  message   String   // The content of the reminder message
  sendAt    DateTime // The scheduled time for sending (Store in UTC!)
  status    String   @default(""PENDING"") // PENDING, SENT, FAILED (Consider adding: RETRYING, DELIVERED, UNDELIVERABLE with webhooks)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
  // Optional: Add retry count for better error handling
  // retryCount Int @default(0)
  // Optional: Store failure reason
  // failureReason String?

  // Index for efficient querying by the scheduler
  @@index([sendAt, status])
}

npx prisma migrate dev --name initial-reminder-schema

// src/config/prisma.js
import { PrismaClient } from '@prisma/client';

const prisma = new PrismaClient({
  // Optional: Log queries during development
  // log: process.env.NODE_ENV === 'development' ? ['query', 'info', 'warn', 'error'] : ['warn', 'error'],
});

export default prisma;

// src/services/reminderService.js
import prisma from '../config/prisma.js';
import logger from '../config/logger.js';

/**
 * Creates a new reminder in the database.
 * @param {string} recipient - Phone number (E.164 format recommended).
 * @param {string} message - SMS content.
 * @param {Date} sendAt - Scheduled time (UTC).
 * @returns {Promise<object>} The created reminder object.
 */
export const createReminder = async (recipient, message, sendAt) => {
  try {
    const reminder = await prisma.reminder.create({
      data: {
        recipient,
        message,
        sendAt, // Ensure this is a Date object in UTC
        status: 'PENDING',
      },
    });
    logger.info(`Reminder created with ID: ${reminder.id}`);
    return reminder;
  } catch (error) {
    logger.error(`Error creating reminder: ${error.message}`, { error });
    // Re-throw a more specific error or handle as needed
    throw new Error('Failed to create reminder in database.');
  }
};

/**
 * Finds pending reminders that are due to be sent.
 * @param {Date} now - The current time (UTC).
 * @returns {Promise<Array<object>>} An array of due reminders.
 */
export const findDueReminders = async (now = new Date()) => {
  try {
    // Find reminders where sendAt is less than or equal to now AND status is PENDING
    const dueReminders = await prisma.reminder.findMany({
      where: {
        sendAt: {
          lte: now, // Less than or equal to the current time
        },
        status: 'PENDING',
      },
      orderBy: {
        sendAt: 'asc', // Process older reminders first
      },
      take: 100, // Limit batch size to avoid overwhelming the system/API
    });
    // No need to log if zero, scheduler will handle that
    // logger.info(`Found ${dueReminders.length} due reminders.`);
    return dueReminders;
  } catch (error) {
    logger.error(`Error finding due reminders: ${error.message}`, { error });
    throw new Error('Failed to query due reminders.');
  }
};

/**
 * Updates the status of a reminder.
 * @param {string} reminderId - The ID of the reminder.
 * @param {'PENDING' | 'SENT' | 'FAILED'} status - The new status.
 * @param {string} [reason] - Optional reason for failure.
 * @returns {Promise<object|null>} The updated reminder object or null if update failed.
 */
export const updateReminderStatus = async (reminderId, status, reason = null) => {
  try {
    const dataToUpdate = {
      status: status,
      // Potentially add a 'failureReason' field to the schema if needed
      // failureReason: reason
    };
    // Consider adding retryCount update here if implementing retries

    const updatedReminder = await prisma.reminder.update({
      where: { id: reminderId },
      data: dataToUpdate,
    });
    logger.info(`Updated status for reminder ${reminderId} to ${status}`);
    return updatedReminder;
  } catch (error) {
    // Prisma's P2025 error code means record to update not found
    if (error.code === 'P2025') {
        logger.warn(`Attempted to update status for non-existent reminder ID: ${reminderId}`);
        return null; // Indicate not found
    }
    logger.error(`Error updating status for reminder ${reminderId}: ${error.message}`, { status, error });
    // Don't throw here generally, as the primary action (sending) might have succeeded/failed already.
    // Logged the error, scheduler should handle it. Return null to indicate update failure.
    return null;
  }
};

/**
 * Finds a reminder by its ID.
 * @param {string} reminderId - The ID of the reminder.
 * @returns {Promise<object|null>} The reminder object or null if not found.
 */
export const findReminderById = async (reminderId) => {
  try {
    const reminder = await prisma.reminder.findUnique({
      where: { id: reminderId },
    });
    return reminder;
  } catch (error) {
    logger.error(`Error finding reminder by ID ${reminderId}: ${error.message}`, { error });
    throw new Error('Failed to query reminder by ID.');
  }
};

/**
 * Deletes a reminder by its ID.
 * Only allow deletion if it's still PENDING.
 * @param {string} reminderId - The ID of the reminder.
 * @returns {Promise<object>} The deleted reminder object.
 * @throws {Error} If reminder not found (statusCode 404) or not pending (statusCode 400).
 */
export const deletePendingReminder = async (reminderId) => {
  try {
    // Use a transaction to ensure atomicity (check status and delete)
    const result = await prisma.$transaction(async (tx) => {
      const reminder = await tx.reminder.findUnique({
        where: { id: reminderId },
      });

      if (!reminder) {
        logger.warn(`Attempted to delete non-existent reminder: ${reminderId}`);
        // Throw specific error to be caught by controller
        const notFoundError = new Error('Reminder not found.');
        notFoundError.statusCode = 404;
        throw notFoundError;
      }

      if (reminder.status !== 'PENDING') {
        logger.warn(`Attempted to delete reminder ${reminderId} with status ${reminder.status}`);
        // Throw specific error to be caught by controller
        const badRequestError = new Error('Cannot delete reminder that is not in PENDING status.');
        badRequestError.statusCode = 400;
        throw badRequestError;
      }

      const deletedReminder = await tx.reminder.delete({
        where: { id: reminderId },
      });
      logger.info(`Deleted reminder ${reminderId}`);
      return deletedReminder;
    });
    return result;
  } catch (error) {
     // If it's one of our custom errors with statusCode, re-throw it
    if (error.statusCode === 400 || error.statusCode === 404) {
        throw error;
    }
    // Otherwise, log and throw a generic error
    logger.error(`Error deleting reminder ${reminderId}: ${error.message}`, { error });
    throw new Error('Failed to delete reminder.');
  }
};

/**
 * Lists reminders with pagination.
 * @param {number} page - Page number (1-based).
 * @param {number} limit - Items per page.
 * @returns {Promise<{reminders: Array<object>, total: number, pages: number, currentPage: number}>}
 */
export const listReminders = async (page = 1, limit = 20) => {
  try {
    const skip = (page - 1) * limit;
    const [reminders, total] = await prisma.$transaction([
      prisma.reminder.findMany({
        skip: skip,
        take: limit,
        orderBy: {
          createdAt: 'desc', // Show newest first
        },
      }),
      prisma.reminder.count(),
    ]);

    return {
      reminders,
      total,
      pages: Math.ceil(total / limit),
      currentPage: page,
    };
  } catch (error) {
    logger.error(`Error listing reminders: ${error.message}`, { page, limit, error });
    throw new Error('Failed to list reminders.');
  }
};

// src/config/index.js
import dotenv from 'dotenv';

dotenv.config(); // Load .env file contents into process.env

const config = {
  nodeEnv: process.env.NODE_ENV || 'development',
  port: process.env.PORT || 3000,
  databaseUrl: process.env.DATABASE_URL,
  infobip: {
    baseUrl: process.env.INFOBIP_BASE_URL,
    apiKey: process.env.INFOBIP_API_KEY,
  },
  scheduler: {
    cronPattern: process.env.SCHEDULER_CRON_PATTERN || '* * * * *', // Default to every minute
  },
  auth: {
    apiKey: process.env.API_SECRET_KEY,
  },
  logLevel: process.env.LOG_LEVEL || 'info',
};

// Basic validation
if (!config.databaseUrl) {
  console.error('FATAL ERROR: Missing environment variable: DATABASE_URL');
  process.exit(1);
}
if (!config.infobip.baseUrl || !config.infobip.apiKey) {
  console.error('FATAL ERROR: Missing Infobip environment variables: INFOBIP_BASE_URL, INFOBIP_API_KEY');
  process.exit(1);
}
// API Key check moved to auth middleware for clarity, only warn on startup if missing
if (!config.auth.apiKey && config.nodeEnv === 'production') {
  console.warn('SECURITY WARNING: API_SECRET_KEY is not set in production environment. API authentication is disabled!');
} else if (!config.auth.apiKey) {
    console.warn('INFO: API_SECRET_KEY is not set. API authentication will be skipped (intended for local development only).');
}


export default config;

// src/config/infobip.js
import { Infobip, AuthType } from '@infobip-api/sdk';
import config from './index.js';
import logger from './logger.js';

let infobipClient;

try {
  if (!config.infobip.baseUrl || !config.infobip.apiKey) {
      throw new Error('Infobip Base URL or API Key is missing in configuration.');
  }
  infobipClient = new Infobip({
    baseUrl: config.infobip.baseUrl,
    apiKey: config.infobip.apiKey,
    authType: AuthType.ApiKey,
  });
  logger.info('Infobip client initialized successfully.');
} catch (error) {
  logger.error('FATAL ERROR: Failed to initialize Infobip client:', { message: error.message });
  // Exit if Infobip client fails to init, as SMS sending is core functionality
  process.exit(1);
}

export default infobipClient;

// src/services/smsService.js
import infobipClient from '../config/infobip.js';
import logger from '../config/logger.js';

/**
 * Sends an SMS message using the Infobip API.
 * @param {string} recipient - The phone number to send to (E.164 format).
 * @param {string} messageText - The content of the SMS.
 * @param {string} [sender='InfoSMS'] - The sender ID (alpha-numeric). Check Infobip docs for regulations.
 * @returns {Promise<{success: boolean, messageId: string|null, error: string|null, status: object|null}>} Result object.
 */
export const sendSms = async (recipient, messageText, sender = 'InfoSMS') => {
  logger.info(`Attempting to send SMS to ${recipient}`);
  try {
    // Construct the payload according to Infobip SDK requirements
    const payload = {
      messages: [
        {
          destinations: [{ to: recipient }],
          from: sender,
          text: messageText,
          // Add other options like delivery time window, notifyUrl etc. if needed
          // notifyUrl: 'YOUR_DELIVERY_REPORT_WEBHOOK_URL', // Add this for delivery reports
          // See: https://www.infobip.com/docs/api/channels/sms/sms-messaging/outbound-sms/send-sms-message
        },
      ],
      // Optional: Add tracking for grouping messages
      // tracking: { track: 'MY_APP_REMINDERS' }
    };

    const response = await infobipClient.channels.sms.send(payload);

    // Process the response - structure might vary slightly based on SDK version
    // Check the first message result as we're sending one message at a time here
    const messageResult = response?.data?.messages?.[0];

    // Group ID 1 (PENDING) or 3 (DELIVERED_TO_HANDSET - unlikely for initial send response) indicate acceptance by Infobip
    // See: https://www.infobip.com/docs/essentials/response-status-and-error-codes#status-object-and-status-ids
    if (messageResult && (messageResult.status?.groupId === 1 || messageResult.status?.groupId === 3)) {
      logger.info(`SMS submitted successfully to Infobip for ${recipient}. Message ID: ${messageResult.messageId}, Status: ${messageResult.status.name} (${messageResult.status.description})`);
      return {
        success: true,
        messageId: messageResult.messageId,
        status: messageResult.status,
        error: null,
      };
    } else {
      // Handle cases where the API accepted the request but reported an issue with the message itself
      const statusDescription = messageResult?.status?.description || 'Unknown status';
      const errorCode = messageResult?.status?.id || 'N/A';
      const groupName = messageResult?.status?.groupName || 'UNKNOWN_GROUP';
      logger.error(`Infobip API reported issue sending SMS to ${recipient}: ${statusDescription} (Code: ${errorCode}, Group: ${groupName})`, { responseData: response?.data });
      return {
        success: false,
        messageId: messageResult?.messageId || null,
        status: messageResult?.status || null,
        error: `Infobip API Error: ${statusDescription} (Code: ${errorCode}, Group: ${groupName})`,
      };
    }
  } catch (error) {
    // Handle network errors or SDK specific exceptions
    const statusCode = error?.response?.status;
    let apiErrorData = error?.response?.data;
    let errorMessage = error.message; // Default to generic error message

    // Try to extract a more specific error message from Infobip's response structure
    if (apiErrorData?.requestError?.serviceException?.text) {
      errorMessage = apiErrorData.requestError.serviceException.text;
    } else if (apiErrorData?.messages?.[0]?.status?.description) {
       errorMessage = apiErrorData.messages[0].status.description;
    } else if (typeof apiErrorData === 'string' && apiErrorData.length > 0) {
        errorMessage = apiErrorData; // Sometimes the error data is just a string
    }

    logger.error(`Failed to send SMS via Infobip to ${recipient}: ${errorMessage}`, {
      statusCode: statusCode,
      apiError: apiErrorData, // Log detailed API error if available
      originalError: error.message,
    });

    return {
      success: false,
      messageId: null,
      status: null,
      error: `Infobip SDK/API Error: ${errorMessage}${statusCode ? ` (Status Code: ${statusCode})` : ''}`,
    };
  }
};

// src/services/schedulerService.js
import cron from 'node-cron';
import config from '../config/index.js';
import logger from '../config/logger.js';
import { findDueReminders, updateReminderStatus } from './reminderService.js';
import { sendSms } from './smsService.js';

let cronJob = null;
let isRunning = false; // Simple lock to prevent overlapping runs

const processDueReminders = async () => {
  if (isRunning) {
    logger.warn('Scheduler job processing is already in progress. Skipping this cycle.');
    return;
  }
  isRunning = true;
  logger.info('Scheduler job started: Checking for due reminders...');

  try {
    const now = new Date(); // Use UTC time
    const dueReminders = await findDueReminders(now);

    if (dueReminders.length === 0) {
      logger.info('No due reminders found.');
      // No 'finally' block needed here, will reach the one below
    } else {
        logger.info(`Processing ${dueReminders.length} due reminders...`);

        // Process reminders sequentially or in parallel (with limits)
        // Sequential processing is simpler and safer for API rate limits initially
        for (const reminder of dueReminders) {
          logger.info(`Processing reminder ID: ${reminder.id} for ${reminder.recipient}`);
          try {
            // 1. Attempt to send SMS
            const smsResult = await sendSms(reminder.recipient, reminder.message);

            // 2. Update status based on SMS result
            if (smsResult.success) {
              await updateReminderStatus(reminder.id, 'SENT');
              logger.info(`Successfully sent reminder ${reminder.id} and updated status to SENT.`);
            } else {
              // SMS sending failed according to Infobip or SDK
              // Note: This basic implementation marks as FAILED on the first attempt.
              // A production system should implement retry logic (e.g., increment retry count, check error type) here.
              await updateReminderStatus(reminder.id, 'FAILED', smsResult.error); // Pass error reason if schema supports it
              logger.error(`Failed to send reminder ${reminder.id}: ${smsResult.error}. Status updated to FAILED.`);
            }
          } catch (processError) {
            // Catch errors during the processing of a single reminder (e.g., DB update failure *after* potential send)
            logger.error(`Error processing reminder ${reminder.id}: ${processError.message}`, { error: processError });
            // Attempt to mark as FAILED if possible, but avoid crashing the whole scheduler run
             try {
               await updateReminderStatus(reminder.id, 'FAILED', processError.message);
             } catch (updateErr) {
               logger.error(`Failed to update status to FAILED for reminder ${reminder.id} after processing error: ${updateErr.message}`);
             }
          }
        } // end for loop
    } // end else block

  } catch (error) {
    // Catch errors related to finding reminders (e.g., DB connection issue)
    logger.error(`Scheduler job failed during reminder lookup: ${error.message}`, { error });
  } finally {
    isRunning = false; // Ensure the lock is always released
    logger.info('Scheduler job finished.');
  }
};

export const startScheduler = () => {
  if (cronJob) {
    logger.warn('Scheduler already started.');
    return;
  }

  const cronPattern = config.scheduler.cronPattern;
  if (!cron.validate(cronPattern)) {
     logger.error(`Invalid cron pattern: ""${cronPattern}"". Scheduler not started.`);
     // Optionally throw an error or prevent app startup
     return;
  }

  logger.info(`Starting scheduler with pattern: ${cronPattern}`);
  // Schedule the task
  cronJob = cron.schedule(cronPattern, processDueReminders, {
    scheduled: true,
    timezone: ""Etc/UTC"" // IMPORTANT: Run cron based on UTC
  });

  // Optional: Run immediately on start for testing or catching up missed jobs
  // logger.info('Running scheduler job immediately on startup...');
  // processDueReminders();
};

export const stopScheduler = () => {
  if (cronJob) {
    logger.info('Stopping scheduler...');
    cronJob.stop();
    cronJob = null;
    logger.info('Scheduler stopped.');
  } else {
    logger.warn('Scheduler is not running or already stopped.');
  }
};

// Graceful shutdown handling integrated into server.js

// src/config/logger.js
import winston from 'winston';
import config from './index.js'; // Import config to get log level

const { combine, timestamp, json, printf, colorize, errors } = winston.format;

// Custom format for console logging with colors and stack traces
const consoleFormat = printf(({ level, message, timestamp, stack, ...metadata }) => {
  let msg = `${timestamp} [${level}]: ${message}`;
  // Only include metadata if it's not empty and not the stack trace
  const meta = Object.keys(metadata).length > 0 ? JSON.stringify(metadata, null, 2) : '';
  if (meta) {
    msg += ` ${meta}`;
  }
  // Add stack trace if available
  if (stack) {
    msg += `\n${stack}`;
  }
  return msg;
});

const logger = winston.createLogger({
  level: config.logLevel || 'info', // Use level from config or default to 'info'
  format: combine(
    errors({ stack: true }), // Log stack traces
    timestamp({ format: 'YYYY-MM-DD HH:mm:ss' }),
    json() // Default format for files or other transports
  ),
  transports: [
    // Console Transport (conditionally formatted based on NODE_ENV)
    new winston.transports.Console({
      format: combine(
        config.nodeEnv === 'development' ? colorize() : winston.format.uncolorize(), // Colorize only in development
        consoleFormat
      ),
      handleExceptions: true, // Log unhandled exceptions
    }),
    // Optional: File transport for production logs
    // new winston.transports.File({
    //   filename: 'logs/error.log',
    //   level: 'error',
    //   handleExceptions: true,
    //   maxsize: 5242880, // 5MB
    //   maxFiles: 5,
    // }),
    // new winston.transports.File({
    //   filename: 'logs/combined.log',
    //   handleExceptions: true,
    //   maxsize: 5242880, // 5MB
    //   maxFiles: 5,
    // }),
  ],
  exitOnError: false, // Do not exit on handled exceptions
});

// Create a stream object with a 'write' function that will be used by morgan
logger.stream = {
  write: (message) => {
    // Use the 'info' level so the output will be picked up by both transports
    logger.info(message.trim());
  },
};

export default logger;