Build automated SMS appointment reminders using Next.js and Vonage Messages API that reliably deliver scheduled notifications to users at their chosen frequency. This comprehensive guide covers project setup, database design with Prisma and PostgreSQL, timezone handling, security best practices, and production deployment with Vercel Cron Jobs.

Learn how to create a system where users subscribe via a web interface, provide their phone number and reminder frequency, and receive automated SMS reminders via the Vonage Messages API according to their schedule. This tutorial addresses real-world notification needs including appointment reminders, subscription renewals, and habit tracking applications.

Project Overview and Goals

What You'll Build:

• A Next.js application with a simple frontend for users to subscribe
• A Next.js API route (using Pages Router) to handle subscription requests
• A PostgreSQL database managed with Prisma ORM to store subscription details
• A scheduled task (logic within an API route, triggered externally) to check for due reminders
• Integration with the Vonage Messages API to send SMS reminders
• Robust error handling, logging, and security considerations
• Deployment instructions for Vercel

Technologies Used:

• Next.js: A React framework providing server-side rendering, static site generation, and API routes – ideal for building full-stack applications. Use the Pages Router for API routes in this guide. (Compatible with Next.js 14-15; while the App Router with Route Handlers is now recommended for new projects as of Next.js 14+, Pages Router remains fully supported.)
• Node.js: The runtime environment for your Next.js backend and scheduled tasks. (v18 or later recommended, aligning with current LTS releases.)
• Vonage Messages API: A powerful API for sending and receiving messages across various channels, including SMS. Use the Node SDK (@vonage/server-sdk v3.24.1 or @vonage/messages v1.20.3, current as of late 2024/early 2025).
• PostgreSQL: A reliable open-source relational database.
• Prisma: A modern Node.js and TypeScript ORM that simplifies database access, migrations, and type safety. (Prisma v5.x+ includes driver adapter support and improved TypeScript type-safety.)
• date-fns & date-fns-tz: Libraries for robust date and time zone manipulation. (date-fns-tz v3.x provides timezone support using the Intl API, which is natively supported in modern Node.js and browsers.)
• Vercel: A platform for deploying frontend and serverless applications, offering seamless integration with Next.js and features like Cron Jobs. (Vercel Cron Jobs require vercel.json configuration and are available on Hobby plans with 2 job limit, Pro plans with 40 job limit.)

Deployment Alternatives:

System Architecture:

+-----------------+      +---------------------+      +-----------------+
| User (Browser)  | ---> | Next.js Frontend    | ---> | Next.js API     |
|                 |      | (pages/index.tsx)   |      | (/api/subscribe)|
+-----------------+      +---------------------+      +--------+--------+
                                                             |
                                                             | (Write)
                                                             v
+-----------------+      +---------------------+      +--------+--------+
| External Cron   | ---> | Next.js API         | <--- | PostgreSQL DB   |
| Trigger (Vercel)|      | (/api/cron)         |      | (Prisma Client) |
|                 |      | (Scheduled Job Logic)| ---> +--------+--------+
+-----------------+      +---------+-----------+            |
                                   |                        | (Read)
                                   v                        ^
                          +--------+--------+               |
                          | Vonage Messages |---------------+
                          | API             |
                          +-----------------+

Prerequisites:

• Node.js (v18 or later recommended, matching current LTS) and npm/yarn
• A Vonage API account (Sign up here). You'll need your API Key, API Secret, and a Vonage Application ID with the "Messages" capability enabled and a generated private key. (Vonage uses JWT authentication with private keys for production environments as a security best practice.)
• A Vonage phone number capable of sending SMS messages, which must be linked to your specific Vonage Application within the Vonage dashboard
• Access to a PostgreSQL database (local or cloud-hosted, e.g., Vercel Postgres, Supabase, Neon)
• A tool like ngrok only if you intend to test incoming Vonage webhooks (like delivery receipts or inbound SMS), which this guide doesn't focus on. It's not required for the core outbound scheduling functionality described here.
• Basic understanding of React, Node.js, APIs, and databases

1. Setting up the Next.js Project

Initialize your Next.js project and install the necessary dependencies for SMS scheduling.

1. Create Next.js App: Open your terminal and run the following command. Use the Pages Router (--no-app) for API routes in this guide for clarity in the examples.

   bash

   npx create-next-app@latest vonage-scheduler --typescript --eslint --tailwind --src-dir --no-app --import-alias "@/*"
   cd vonage-scheduler
   

   (Adjust Tailwind/ESLint preferences if needed. This guide assumes a src/ directory).

2. Install Dependencies:

   bash

   npm install @vonage/server-sdk @prisma/client date-fns date-fns-tz dotenv zod
   npm install --save-dev prisma typescript @types/node @types/react @types/react-dom
   
   • @vonage/server-sdk: The official Vonage SDK for Node.js (v3.24.1 as of late 2024). Alternatively, use @vonage/messages (v1.20.3) for standalone Messages API access.
   • @prisma/client: The Prisma client library to interact with your database.
   • prisma: The Prisma CLI for migrations and generation (dev dependency).
   • date-fns, date-fns-tz: For reliable date/time and time zone handling. date-fns-tz v3.x uses the Intl API for timezone support.
   • dotenv: To load environment variables from a .env file during development.
   • zod: For robust input validation.
   • typescript and @types/*: For TypeScript support and type definitions.

3. Initialize Prisma:

   bash

   npx prisma init --datasource-provider postgresql
   

   This creates:

   • A prisma directory with a schema.prisma file.
   • A .env file (add this to .gitignore if not already present!).

4. Configure .env: Open the .env file created by Prisma and add your database connection URL and Vonage credentials. Never commit this file to version control. Create a .env.example file to track needed variables. Quote all string values, especially multi-line ones or those containing special characters.

   bash

   # .env
   
   # Database Connection (Replace with your actual connection string)
   DATABASE_URL="postgresql://user:password@host:port/database?sslmode=require"
   
   # Vonage Credentials
   VONAGE_API_KEY="YOUR_VONAGE_API_KEY"
   VONAGE_API_SECRET="YOUR_VONAGE_API_SECRET"
   VONAGE_APPLICATION_ID="YOUR_VONAGE_APPLICATION_ID"
   
   # Store the content of your private.key file here, enclosed in double quotes.
   # Ensure correct formatting with literal \n for newlines if copying directly.
   # Alternatively, provide a path (VONAGE_PRIVATE_KEY_PATH) and read the file in code.
   # IMPORTANT: Vonage uses JWT authentication with private keys for production security.
   # The private key should not contain return characters; newlines must be escaped as \n.
   VONAGE_PRIVATE_KEY="-----BEGIN PRIVATE KEY-----\nYOUR_PRIVATE_KEY_CONTENT_LINE_1\nYOUR_PRIVATE_KEY_CONTENT_LINE_2\n-----END PRIVATE KEY-----"
   # Or uncomment and use path:
   # VONAGE_PRIVATE_KEY_PATH="./private.key"
   
   VONAGE_SMS_FROM_NUMBER="YOUR_VONAGE_PHONE_NUMBER" # e.g., 14155550100
   
   # Used by the cron job trigger (can be anything, just needs to match)
   # SECURITY: Vercel automatically includes this secret as a bearer token in cron job requests.
   # Generate using: openssl rand -base64 32
   CRON_SECRET="YOUR_SUPER_SECRET_CRON_TRIGGER_KEY"
   
   • DATABASE_URL: Get this from your PostgreSQL provider.
   • Vonage Credentials: Find these on your Vonage API Dashboard:
     • API Key & Secret: Top of the dashboard.
     • Application ID & Private Key: Go to "Applications" → "Create a new application". Give it a name (e.g., "NextJS Scheduler"), enable the "Messages" capability. Click "Generate public and private key" – the private.key file will download. Save this file securely. Each time a new key is generated, the old key is no longer valid. Copy its content exactly into VONAGE_PRIVATE_KEY (ensuring newlines are represented as \n within the quotes) or provide the path to the file in VONAGE_PRIVATE_KEY_PATH. Note the Application ID shown.
     • Link your Vonage Number: In the Application settings within the Vonage dashboard, link the Vonage phone number you intend to send SMS from (VONAGE_SMS_FROM_NUMBER) to this specific application.
   • VONAGE_SMS_FROM_NUMBER: The Vonage number linked above.
   • CRON_SECRET: Generate a strong random string using openssl rand -base64 32 or a password generator. Vercel Cron Jobs automatically include this as a bearer token in the Authorization header for security.

5. Project Structure: Your src directory will contain:

   • pages/: Frontend pages and API routes.
     • index.tsx: Your main subscription form UI.
     • api/: Backend API endpoints.
       • subscribe.ts: Handles new subscriptions.
       • cron.ts: Contains the logic for sending scheduled reminders.
       • health.ts: (Optional) Basic health check endpoint.
   • lib/: Utility functions/modules.
     • prisma.ts: Prisma client instance.
     • vonageClient.ts: Vonage SDK client instance.
   • utils/: Helper functions.
     • timezones.ts: Timezone validation helpers.
     • scheduler.ts: Logic for calculating reminder times.
   • styles/: Global styles.

2. Creating a Database Schema and Data Layer

Define your database schema using Prisma and create the necessary database table for storing SMS reminder subscriptions.

1. Define Schema: Open prisma/schema.prisma and define the Subscription model:

   sql

   // prisma/schema.prisma
   
   generator client {
     provider = "prisma-client-js"
   }
   
   datasource db {
     provider = "postgresql"
     url      = env("DATABASE_URL")
   }
   
   model Subscription {
     id        String   @id @default(cuid())
     phoneNumber String   @unique // Ensure phone numbers are unique
     frequencyMinutes Int      // How often to remind (in minutes)
     timezone  String   // User's IANA timezone (e.g., "America/New_York")
     nextReminderAt DateTime // When the next reminder should be sent (UTC)
     isActive  Boolean  @default(true) // To easily disable subscriptions
   
     createdAt DateTime @default(now())
     updatedAt DateTime @updatedAt
   
     @@index([nextReminderAt, isActive]) // Index for efficient cron job querying
   }
   
   • Store phoneNumber (uniquely), frequencyMinutes, and the user's timezone.
   • nextReminderAt is crucial: store the exact UTC timestamp for the next reminder.
   • isActive allows disabling reminders without deleting data.
   • An index on nextReminderAt and isActive makes querying for due reminders efficient.
   • Note: Prisma v5.4.0+ supports driver adapters as the primary database communication method, improving performance and flexibility.

Scaling Considerations for Indexes:

For applications with millions of subscriptions, consider these additional indexes:

2. Run Database Migration: Apply the schema changes to your database:

   bash

   npx prisma migrate dev --name init
   

   This creates the Subscription table in your PostgreSQL database.

3. Generate Prisma Client: Ensure the Prisma client is up-to-date with your schema:

   bash

   npx prisma generate
   

4. Create Prisma Client Instance: Create a reusable Prisma client instance.

   typescript

   // src/lib/prisma.ts
   import { PrismaClient } from '@prisma/client';
   
   declare global {
     // eslint-disable-next-line no-var
     var prisma: PrismaClient | undefined;
   }
   
   // Initialize PrismaClient, enabling query logging in development
   // Prisma v5.x+ provides enhanced type-safety and performance optimization
   export const prisma =
     global.prisma ||
     new PrismaClient({
       log: process.env.NODE_ENV === 'development' ? ['query', 'info', 'warn', 'error'] : ['error'],
     });
   
   // Prevent multiple instances of Prisma Client in development
   if (process.env.NODE_ENV !== 'production') {
     global.prisma = prisma;
   }
   
   export default prisma;
   

   This pattern prevents creating multiple PrismaClient instances during development hot-reloading.

3. Integrating with Necessary Third-Party Services (Vonage)

Set up the Vonage SDK client for sending automated SMS reminders.

1. Create Vonage Client Instance: Create a file to initialize and export the Vonage client.

   typescript

   // src/lib/vonageClient.ts
   import { Vonage } from '@vonage/server-sdk';
   import { Auth } from '@vonage/auth';
   import { SMS } from '@vonage/messages'; // Import SMS class specifically
   import fs from 'fs';
   import path from 'path';
   
   // Ensure environment variables are loaded (may be redundant in standard Next.js API route context,
   // but ensures it works if this module is imported or run elsewhere, e.g., standalone scripts)
   import 'dotenv/config';
   
   const getPrivateKey = (): string => {
     if (process.env.VONAGE_PRIVATE_KEY) {
       // Replace escaped newlines if reading directly from .env
       // IMPORTANT: Private key must not contain return characters; use \n for newlines
       return process.env.VONAGE_PRIVATE_KEY.replace(/\\n/g, '\n');
     } else if (process.env.VONAGE_PRIVATE_KEY_PATH) {
       try {
         const keyPath = path.resolve(process.cwd(), process.env.VONAGE_PRIVATE_KEY_PATH);
         return fs.readFileSync(keyPath, 'utf-8');
       } catch (error) {
         console.error('Error reading Vonage private key file:', error);
         throw new Error(`Could not read Vonage private key file at path: ${process.env.VONAGE_PRIVATE_KEY_PATH}`);
       }
     } else {
       throw new Error('VONAGE_PRIVATE_KEY or VONAGE_PRIVATE_KEY_PATH must be set in environment variables.');
     }
   };
   
   if (!process.env.VONAGE_API_KEY || !process.env.VONAGE_API_SECRET || !process.env.VONAGE_APPLICATION_ID) {
     console.error('Missing Vonage API Key, Secret, or Application ID in environment variables.');
     throw new Error('Missing required Vonage API credentials.');
   }
   
   // Vonage SDK v3.x uses JWT authentication with private keys for production security
   const credentials = new Auth({
     apiKey: process.env.VONAGE_API_KEY,
     apiSecret: process.env.VONAGE_API_SECRET,
     applicationId: process.env.VONAGE_APPLICATION_ID,
     privateKey: getPrivateKey(),
   });
   
   const vonage = new Vonage(credentials);
   
   export { vonage, SMS }; // Export both the main client and the SMS class
   
   export const sendSms = async (to: string, text: string): Promise<any> => {
     if (!process.env.VONAGE_SMS_FROM_NUMBER) {
       console.error('VONAGE_SMS_FROM_NUMBER is not set in environment variables.');
       throw new Error('Vonage SMS sender number is not configured.');
     }
   
     console.log(`Attempting to send SMS via Vonage to ${to}`);
     try {
       // Vonage Messages API uses the new SDK v3.x structure with SMS class
       const response = await vonage.messages.send(
         new SMS({
           to: to,
           from: process.env.VONAGE_SMS_FROM_NUMBER,
           text: text,
           // client_ref: `reminder_${to}_${Date.now()}` // Optional: Add a client reference
         })
       );
       console.log(`Vonage message sent successfully to ${to}. Message UUID: ${response.messageUuid}`);
       return response;
     } catch (error: any) {
       // Log detailed error information if available
       const errorDetails = error?.response?.data || error.message || error;
       console.error(`Error sending Vonage SMS to ${to}:`, JSON.stringify(errorDetails, null, 2));
       // Rethrow or handle specific errors (e.g., invalid number format, insufficient funds)
       throw new Error(`Failed to send SMS: ${error.message || 'Unknown Vonage API error'}`);
     }
   };
   
   • This initializes the Vonage client using credentials from environment variables.
   • It includes logic to read the private key either directly from VONAGE_PRIVATE_KEY (handling escaped newlines) or from a file path specified by VONAGE_PRIVATE_KEY_PATH.
   • The sendSms helper function simplifies sending SMS messages, including improved logging.
   • Vonage SDK v3.x (specifically v3.24.1 as of late 2024) uses TypeScript and Promises for asynchronous operations.

Vonage Error Handling:

Common Vonage API errors and how to handle them:

4. Building the API Layer (Subscription Endpoint)

Create the API endpoint for users to subscribe to automated SMS reminders.

1. Create Subscription API Route:

   typescript

   // src/pages/api/subscribe.ts
   import type { NextApiRequest, NextApiResponse } from 'next';
   import prisma from '@/lib/prisma';
   import { sendSms } from '@/lib/vonageClient';
   import { z } from 'zod';
   import { isSupportedTimeZone } from '@/utils/timezones'; // You'll create this util
   import { calculateNextReminderTime } from '@/utils/scheduler'; // And this one
   
   // Input validation schema using Zod
   const SubscriptionSchema = z.object({
     phoneNumber: z.string().trim().regex(/^\+?[1-9]\d{1,14}$/, { message: "Invalid phone number format (E.164 expected, e.g., +15551234567)" }),
     frequencyMinutes: z.coerce.number().int().min(1, { message: "Frequency must be at least 1 minute" }).max(10080, { message: "Frequency cannot exceed 1 week (10080 minutes)" }), // Added max
     timezone: z.string().refine(isSupportedTimeZone, { message: "Invalid or unsupported timezone" }),
   });
   
   // Define a more specific type for the response data
   type ResponseData = {
       message: string;
       subscriptionId?: string;
       error?: string;
       details?: z.ZodFormattedError<z.infer<typeof SubscriptionSchema>>['fieldErrors'];
   };
   
   
   export default async function handler(
     req: NextApiRequest,
     res: NextApiResponse<ResponseData&gt;
   ) {
     if (req.method !== 'POST') {
       res.setHeader('Allow', ['POST']);
       return res.status(405).json({ message: `Method ${req.method} Not Allowed` });
     }
   
     // 1. Validate Input
     const parseResult = SubscriptionSchema.safeParse(req.body);
     if (!parseResult.success) {
       console.warn('Subscription validation failed:', parseResult.error.flatten());
       return res.status(400).json({
         message: 'Invalid input data.',
         error: 'Validation Error',
         details: parseResult.error.format().fieldErrors, // Use format() for better structure
       });
     }
   
     const { phoneNumber, frequencyMinutes, timezone } = parseResult.data;
   
     try {
       // 2. Calculate initial next reminder time (using a helper)
       const nextReminderAt = calculateNextReminderTime(new Date(), frequencyMinutes, timezone);
       if (!nextReminderAt) {
           // This indicates an issue in calculation or timezone data
           console.error(`Could not calculate next reminder time for tz: ${timezone}, freq: ${frequencyMinutes}`);
           throw new Error("Internal error: Could not calculate the initial reminder time.");
       }
   
       // 3. Save to Database (using Prisma upsert)
       // Upsert automatically handles:
       // - Creating a new subscription if the phone number doesn't exist.
       // - Updating the existing subscription if the phone number already exists.
       const subscription = await prisma.subscription.upsert({
         where: { phoneNumber: phoneNumber },
         update: {
           frequencyMinutes: frequencyMinutes,
           timezone: timezone,
           nextReminderAt: nextReminderAt, // Set the new calculated time
           isActive: true, // Ensure user is active if they re-subscribe
           updatedAt: new Date(), // Explicitly set update time
         },
         create: {
           phoneNumber: phoneNumber,
           frequencyMinutes: frequencyMinutes,
           timezone: timezone,
           nextReminderAt: nextReminderAt,
           isActive: true,
         },
       });
   
       console.log(`Subscription created/updated for ${phoneNumber}: ${subscription.id}`);
   
       // 4. Send Welcome SMS (Optional but recommended)
       try {
         await sendSms(
           phoneNumber,
           `Welcome! You're subscribed to reminders every ${frequencyMinutes} minutes. Your timezone is set to ${timezone}.`
         );
       } catch (smsError) {
         // Log the SMS error but don't fail the whole request, as the subscription *was* saved.
         console.error(`Failed to send welcome SMS to ${phoneNumber} (Sub ID: ${subscription.id}), but subscription was saved:`, smsError);
         // Optionally: Add a flag to the user record indicating the welcome message failed.
       }
   
       // 5. Respond Successfully
       return res.status(201).json({ // 201 Created (or 200 OK if update is more common)
         message: 'Subscription successful!',
         subscriptionId: subscription.id,
       });
   
     } catch (error: any) {
       console.error('Error processing subscription request:', error);
   
       // Handle potential database connection errors or other unexpected issues
       return res.status(500).json({
         message: 'Failed to process subscription due to an internal error.',
         error: error.message || 'Internal Server Error',
       });
     }
   }
   

2. Create Utility Functions: Create helpers for time zone validation and calculating the next reminder time.

   typescript

   // src/utils/timezones.ts
   import { listTimeZones } from 'date-fns-tz';
   
   // Cache the list for performance. Using a Set allows for quick lookups.
   // date-fns-tz v3.x uses the Intl API for timezone support (natively available in Node.js v18+)
   let supportedTimezones: Set<string&gt; | null = null;
   
   function getTimezoneSet(): Set<string&gt; {
       if (!supportedTimezones) {
           try {
               supportedTimezones = new Set(listTimeZones());
           } catch (e) {
               console.error("Failed to list timezones using date-fns-tz. Intl API might be required.", e);
               // Fallback or rethrow depending on requirements
               supportedTimezones = new Set(['UTC', 'America/New_York', 'Europe/London', 'Asia/Tokyo']); // Basic fallback
           }
       }
       return supportedTimezones;
   }
   
   export const isSupportedTimeZone = (tz: string): boolean =&gt; {
       if (!tz) return false;
       return getTimezoneSet().has(tz);
   };
   
   export const getTimeZoneList = (): string[] =&gt; {
       // Return sorted array for predictable dropdown order
       return Array.from(getTimezoneSet()).sort();
   }
   
   typescript

   // src/utils/scheduler.ts
   import { addMinutes } from 'date-fns';
   import { utcToZonedTime, zonedTimeToUtc } from 'date-fns-tz';
   
   /**
    * Calculates the next reminder time in UTC.
    * For the *initial* subscription, it calculates based on the current time in the user's zone.
    * For *subsequent* reminders, it should calculate based on the *last scheduled* time.
    *
    * date-fns-tz provides timezone conversion using the Intl API (Node.js v18+ native support)
    *
    * @param baseTime The time to calculate from. For initial calc, use new Date(). For subsequent, use the last `nextReminderAt`.
    * @param frequencyMinutes The reminder frequency.
    * @param timezone The user's IANA timezone string (must be valid).
    * @returns The next reminder Date object in UTC, or null if calculation fails.
    */
   export const calculateNextReminderTime = (
       baseTime: Date,
       frequencyMinutes: number,
       timezone: string
   ): Date | null => {
       try {
           // Important: Ensure the baseTime is correctly interpreted.
           // If baseTime is the *last scheduled UTC time*, convert it to the user's zone, add minutes, then convert back to UTC.
           // If baseTime is 'now' for initial calculation, get 'now' in the user's zone, add minutes, convert to UTC.
   
           // 1. Convert the base time (which should ideally be UTC) into the user's local time zone.
           const baseTimeInUserTz = utcToZonedTime(baseTime, timezone);
   
           // 2. Add the frequency to this time (calculation happens correctly in the context of the user's zone).
           const nextTimeInUserTz = addMinutes(baseTimeInUserTz, frequencyMinutes);
   
           // 3. Convert this future time *back* to UTC for storage and comparison.
           const nextTimeUtc = zonedTimeToUtc(nextTimeInUserTz, timezone);
   
           // Logging for debugging
           // console.log(`Calculating next reminder: BaseUTC=${baseTime.toISOString()}, Freq=${frequencyMinutes}, TZ=${timezone} → BaseUserTZ=${baseTimeInUserTz} → NextUserTZ=${nextTimeInUserTz} → NextUTC=${nextTimeUtc.toISOString()}`);
   
           return nextTimeUtc;
       } catch (error) {
           console.error(`Error calculating next reminder time for base ${baseTime}, freq ${frequencyMinutes}, tz ${timezone}:`, error);
           return null; // Return null to indicate failure
       }
   };
   

Timezone Conversion Flow:

User Time (Local) → UTC (Storage) → User Time (Display/Calculation)
    ↓                    ↓                      ↓
Input/Display      Database Store         Next Calculation
America/NY 2pm → 2025-01-15T19:00Z → America/NY 3pm (next hour)
                                    ↓
                           2025-01-15T20:00Z (stored)

DST Handling: The date-fns-tz library automatically handles Daylight Saving Time transitions. When DST occurs:

• Spring forward: A 1-hour reminder might skip if scheduled during the missing hour (2am → 3am)
• Fall back: A reminder scheduled during the repeated hour runs once at the first occurrence

3. Testing the API Endpoint: Test this endpoint using curl or Postman once your development server is running (npm run dev):

   bash

   curl -X POST http://localhost:3000/api/subscribe \
     -H 'Content-Type: application/json' \
     -d '{
           "phoneNumber": "+12015550123",
           "frequencyMinutes": 60,
           "timezone": "America/New_York"
         }'
   

   Expected Success Response (201 Created):

   json

   {
     "message": "Subscription successful!",
     "subscriptionId": "clsomecuidstring123"
   }
   

   Expected Validation Error Response (400 Bad Request):

   json

   {
       "message": "Invalid input data.",
       "error": "Validation Error",
       "details": {
           "phoneNumber": [
               "Invalid phone number format (E.164 expected, e.g., +15551234567)"
           ]
       }
   }
   

5. Implementing Core Functionality (Scheduled Job)

This section handles sending the reminders. Create an API route containing the job logic, designed to be triggered externally by a scheduler like Vercel Cron Jobs.

1. Create Cron Job API Route:

   typescript

   // src/pages/api/cron.ts
   import type { NextApiRequest, NextApiResponse } from 'next';
   import prisma from '@/lib/prisma';
   import { sendSms } from '@/lib/vonageClient';
   import { calculateNextReminderTime } from '@/utils/scheduler';
   
   type JobStats = {
       processed: number;
       sent: number;
       failed: number;
       errors: string[];
   };
   
   type ResponseData = {
     message: string;
     stats?: JobStats;
     error?: string;
   };
   
   // This is the core logic that runs when the /api/cron endpoint is triggered
   async function runReminderJob(): Promise<JobStats&gt; {
       const stats: JobStats = { processed: 0, sent: 0, failed: 0, errors: [] };
       console.log(`[${new Date().toISOString()}] Reminder Job triggered.`);
   
       const now = new Date(); // Current time in UTC
   
       // 1. Find active subscriptions due for a reminder
       // Process in batches to avoid overwhelming resources or hitting timeouts
       // Batch size of 100 chosen to stay within typical serverless timeout (10s default, 300s max on Pro)
       // At ~100ms per SMS send, 100 messages = ~10s max execution time
       const batchSize = 100; // Adjust based on typical function execution time and limits
       let subscriptionsToSend;
       try {
            subscriptionsToSend = await prisma.subscription.findMany({
               where: {
                   isActive: true,
                   nextReminderAt: {
                       lte: now, // Find reminders scheduled for now or in the past
                   },
               },
               take: batchSize,
               orderBy: {
                   nextReminderAt: 'asc', // Process oldest first
               },
           });
       } catch (dbError: any) {
            console.error("Cron job failed to query database:", dbError);
            stats.errors.push(`Database query failed: ${dbError.message}`);
            return stats; // Cannot proceed without data
       }
   
   
       stats.processed = subscriptionsToSend.length;
       console.log(`Found ${stats.processed} subscriptions due for reminders.`);
   
       if (stats.processed === 0) {
           console.log("No reminders due at this time.");
           return stats;
       }
   
       // 2. Process each due subscription concurrently (with limits)
       const processingPromises = subscriptionsToSend.map(async (sub) =&gt; {
           try {
               // a. Send SMS reminder
               await sendSms(
                   sub.phoneNumber,
                   `Friendly reminder! Time for your scheduled check-in.` // Customize message as needed
               );
               stats.sent++;
   
               // b. Calculate the *next* reminder time based on the *last scheduled time*
               // This prevents drift if the job runs slightly late.
               const nextReminderAt = calculateNextReminderTime(
                   sub.nextReminderAt, // Base calculation on the time it *should* have run
                   sub.frequencyMinutes,
                   sub.timezone
               );
   
               if (!nextReminderAt) {
                   // Log error, but maybe don't disable immediately? Could be transient timezone issue.
                   console.error(`Could not recalculate next reminder time for sub ${sub.id}. Leaving current nextReminderAt.`);
                   // Optionally: Mark for review or attempt calculation again later.
                   throw new Error(`Could not recalculate next reminder time for sub ${sub.id}`);
                }
   
   
               // c. Update the subscription with the new nextReminderAt
               await prisma.subscription.update({
                   where: { id: sub.id },
                   data: {
                       nextReminderAt: nextReminderAt,
                       // Optionally reset error counters here if implementing retry logic
                   },
               });
   
               console.log(`Reminder sent and next time updated for sub ${sub.id} (${sub.phoneNumber}). Next at: ${nextReminderAt.toISOString()}`);
   
           } catch (error: any) {
               stats.failed++;
               const errorMessage = `Failed processing sub ${sub.id} (${sub.phoneNumber}): ${error.message}`;
               console.error(errorMessage);
               stats.errors.push(errorMessage);
   
               // Basic Error Handling/Retry Strategy:
               // Log the error (done above).
               // Option 1 (Simple): Let the next run try again (if nextReminderAt wasn't updated).
               // Option 2 (Basic Retry Delay): Update nextReminderAt to slightly in the future to avoid immediate retries.
               // Option 3 (More Robust): Increment a failure counter. After N failures, set isActive = false.
               // Option 4 (Exponential Backoff): Calculate next retry time exponentially.
   
               // Implementing Option 2 (Basic Retry Delay):
               // Calculate a short delay from 'now' to avoid hammering a failing number.
                const retryTime = new Date(Date.now() + 5 * 60 * 1000); // e.g., 5 minutes from now
                try {
                   await prisma.subscription.update({
                       where: { id: sub.id },
                       data: {
                           nextReminderAt: retryTime,
                           // Optionally add/update 'lastErrorTimestamp' or 'retryCount' fields
                       },
                   });
                   console.warn(`Sub ${sub.id} failed. Scheduled retry attempt around ${retryTime.toISOString()}`);
                } catch (updateError: any) {
                    console.error(`Failed to update sub ${sub.id} after error: ${updateError.message}`);
                    // If updating fails, the original nextReminderAt might still cause it to be picked up next run.
                }
                // Note: For production, consider more sophisticated error handling like exponential backoff
                // or moving failed jobs to a dead-letter queue after several attempts.
           }
       });
   
       // Wait for all processing to complete
       await Promise.allSettled(processingPromises);
   
       console.log(`Reminder Job finished. Processed: ${stats.processed}, Sent: ${stats.sent}, Failed: ${stats.failed}`);
       if (stats.errors.length &gt; 0) {
           console.error("Errors encountered during job run:", stats.errors);
       }
       return stats;
   }
   
   
   // API Route Handler – Designed to be triggered externally (e.g., Vercel Cron)
   // SECURITY: Vercel automatically includes CRON_SECRET as a bearer token in the Authorization header
   export default async function handler(
     req: NextApiRequest,
     res: NextApiResponse<ResponseData&gt;
   ) {
     // 1. Security Check: Verify the CRON_SECRET
     // Recommended: Check Authorization header (Bearer token)
     // Vercel Cron Jobs automatically send the CRON_SECRET as a bearer token
     const authHeader = req.headers.authorization;
     const triggerSecret = authHeader?.startsWith('Bearer ') ? authHeader.split(' ')[1] : null;
   
     // Check Bearer token first (Vercel Cron automatic method)
     if (triggerSecret === process.env.CRON_SECRET) {
         // Authorized via Bearer token
         console.log("Cron job authorized via Bearer token.");
         const stats = await runReminderJob();
         return res.status(200).json({ message: 'Cron job executed.', stats });
     }
   
     // Fallback: Check query parameter (less secure, but common for simple cron triggers)
     if (req.query.secret === process.env.CRON_SECRET) {
         // Authorized via query parameter
         console.log("Cron job authorized via query parameter.");
         const stats = await runReminderJob();
         return res.status(200).json({ message: 'Cron job executed.', stats });
     }
   
     // If neither matched, return Unauthorized
     console.warn("Unauthorized attempt to trigger cron job.");
     return res.status(401).json({ message: 'Unauthorized' });
   }
   

Retry Logic Decision Flow:

Send SMS → Success? ─Yes→ Calculate Next Time → Update DB → Done
              │
              No
              ↓
         Log Error
              ↓
    Retry Count < 3? ─Yes→ Schedule Retry (+5min) → Update DB
              │
              No
              ↓
    Set isActive = false → Notify Admin → Done

Related Resources

Learn more about SMS messaging and API integrations:

• Phone Number Lookup API - Validate phone numbers before sending SMS reminders
• E.164 Phone Number Format Guide - Understanding international phone number formatting for SMS
• Twilio vs Vonage SMS Comparison - Compare SMS API providers for your project