Build a Production-Ready SMS Scheduling & Reminder App with Next.js and Vonage

This guide details how to build a robust application using Next.js and Vonage that allows users to subscribe to receive SMS reminders at a frequency they choose. We'll cover everything from project setup and core logic to database integration, security, deployment, and monitoring, ensuring you have a production-ready solution.

We aim to create a system where users can sign up via a web interface, providing their phone number and desired reminder frequency. A backend process will then reliably send SMS reminders via the Vonage Messages API according to each user's schedule, handling time zones correctly. This solves the common need for scheduled notifications in applications like appointment reminders, subscription renewals, or habit tracking.

Project Overview and Goals

What We'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. We'll use the Pages Router for API routes in this guide.
Node.js: The runtime environment for our Next.js backend and scheduled tasks.
Vonage Messages API: A powerful API for sending and receiving messages across various channels, including SMS. We'll use its Node SDK.
PostgreSQL: A reliable open-source relational database.
Prisma: A modern Node.js and TypeScript ORM that simplifies database access, migrations, and type safety.
date-fns & date-fns-tz: Libraries for robust date and time zone manipulation.
Vercel: A platform for deploying frontend and serverless applications, offering seamless integration with Next.js and features like Cron Jobs.

System Architecture:

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

Prerequisites:

Node.js (v18 or later recommended) 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.
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 Project

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

Create Next.js App: Open your terminal and run the following command. We'll use the Pages Router (--no-app) for API routes in this guide for clarity in the examples.
```
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).
Install Dependencies:
```
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.
- @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.
- 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.
Initialize Prisma:
```
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!).
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. It's best practice to quote all string values, especially multi-line ones or those containing special characters.
```
# .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.
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)
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. 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: A secret key to prevent unauthorized triggering of our cron job API endpoint. Generate a strong random string.
Project Structure: Your src directory will contain:
- pages/: Frontend pages and API routes.
  - index.tsx: Our 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

We'll define our database schema using Prisma and create the necessary database table.

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

// 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
}

We store phoneNumber (uniquely), frequencyMinutes, and the user's timezone.
nextReminderAt is crucial: we 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.

Run Database Migration: Apply the schema changes to your database:
```
npx prisma migrate dev --name init
```
This creates the Subscription table in your PostgreSQL database.
Generate Prisma Client: Ensure the Prisma client is up-to-date with your schema:
```
npx prisma generate
```

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

// 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
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)

Let's set up the Vonage SDK client.

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

// 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 =&gt; {
  if (process.env.VONAGE_PRIVATE_KEY) {
    // Replace escaped newlines if reading directly from .env
    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.');
}

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&gt; =&gt; {
  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 {
    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.

4. Building the API Layer (Subscription Endpoint)

Now, let's create the API endpoint for users to subscribe.

Create Subscription API Route:

// 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'; // We'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>&gt;['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 our 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: You could 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',
    });
  }
}

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

// src/utils/timezones.ts
import { listTimeZones } from 'date-fns-tz';

// Cache the list for performance. Using a Set allows for quick lookups.
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();
}

// 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.
 *
 * @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 =&gt; {
    try {
        // Important: Ensure the baseTime is correctly interpreted.
        // If baseTime is the *last scheduled UTC time*, we need to convert it to the user's zone, add minutes, then convert back to UTC.
        // If baseTime is 'now' for initial calculation, we 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} -&gt; BaseUserTZ=${baseTimeInUserTz} -&gt; NextUserTZ=${nextTimeInUserTz} -&gt; 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
    }
};

Testing the API Endpoint: You can test this endpoint using curl or Postman once your development server is running (npm run dev):

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):

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

Expected Validation Error Response (400 Bad Request):

{
    ""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 part handles sending the reminders. We'll create an API route containing the job logic, designed to be triggered externally by a scheduler like Vercel Cron Jobs.

Create Cron Job API Route:

// 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
    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.
                // For now, we'll proceed without updating the time for this sub.
                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): Just 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)
export default async function handler(
  req: NextApiRequest,
  res: NextApiResponse<ResponseData&gt;
) {
  // 1. Security Check: Verify the CRON_SECRET
  // Recommended: Check Authorization header (Bearer token)
  const authHeader = req.headers.authorization;
  const triggerSecret = authHeader?.startsWith('Bearer ') ? authHeader.split(' ')[1] : null;

  // Check Bearer token first
  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' });
}