SMS Scheduling with Plivo, Node.js & Fastify: Complete Tutorial

graph LR
    User[API Client/User] -- HTTP Request --> API{Fastify API}
    API -- Write/Read --> DB[(SQLite Database)]
    Scheduler(Scheduler Plugin @fastify/schedule) -- Triggers --> Task(Reminder Check Task)
    Task -- Read Due Reminders --> DB
    Task -- Send SMS Request --> Plivo(Plivo SMS API)
    Plivo -- Delivers SMS --> Recipient[End User Phone]
    API -- Start/Manage --> Scheduler

mkdir fastify-plivo-scheduler
cd fastify-plivo-scheduler

npm init -y

npm install fastify @fastify/schedule toad-scheduler plivo better-sqlite3 fastify-plugin dotenv pino-pretty @sinclair/typebox

npm install --save-dev nodemon

// package.json
{
  // ... other configurations ...
  "type": "module", // Enable ES Modules
  "scripts": {
    "start": "node src/app.js",
    "dev": "nodemon src/app.js | pino-pretty"
  },
  // ... other configurations ...
}

fastify-plivo-scheduler/
├── .env             # Environment variables (DO NOT COMMIT)
├── .gitignore       # Specifies intentionally untracked files git should ignore
├── package.json
├── package-lock.json
└── src/
    ├── app.js         # Main application entry point
    ├── config/        # Configuration files (e.g., logger)
    │   └── logger.js
    ├── plugins/       # Fastify plugins (e.g., database, scheduler)
    │   ├── db.js
    │   └── scheduler.js
    ├── routes/        # API route definitions
    │   └── reminders.js
    ├── services/      # Business logic/external service interactions
    │   └── plivoService.js
    └── tasks/         # Scheduled tasks logic
        └── sendRemindersTask.js

# .gitignore
node_modules/
.env
*.log
database.db # Or your chosen SQLite filename

# .env

# Server Configuration
PORT=3000
HOST=0.0.0.0
NODE_ENV=development # change to 'production' for deployment
LOG_LEVEL=info # debug, info, warn, error

# Database Configuration
DATABASE_FILE=./database.db

# Plivo Configuration
PLIVO_AUTH_ID=YOUR_PLIVO_AUTH_ID
PLIVO_AUTH_TOKEN=YOUR_PLIVO_AUTH_TOKEN
PLIVO_SENDER_ID=YOUR_PLIVO_PHONE_NUMBER # Must be in E.164 format, e.g., +14155551234

// src/config/logger.js
import pino from 'pino';

const isProduction = process.env.NODE_ENV === 'production';

const loggerConfig = {
  level: process.env.LOG_LEVEL || 'info',
  ...(isProduction ? {} : { transport: { target: 'pino-pretty' } }), // Use pino-pretty only in dev
};

export default pino(loggerConfig);

// src/app.js
import Fastify from 'fastify';
import dotenv from 'dotenv';
import logger from './config/logger.js';
import dbPlugin from './plugins/db.js';
import schedulerPlugin from './plugins/scheduler.js';
import reminderRoutes from './routes/reminders.js';

// Load environment variables
dotenv.config();

// --- Helper Function to Load Environment Variables ---
function loadEnv(varName, required = true, defaultValue = null) {
    const value = process.env[varName];
    if (required && !value) {
        logger.fatal(`Missing required environment variable: ${varName}`);
        process.exit(1);
    }
    return value || defaultValue;
}

// --- Initialize Fastify ---
const fastify = Fastify({
    logger: logger, // Use our configured logger
});

async function buildApp() {
    // --- Register Plugins ---
    // Decorates fastify instance with 'db'
    await fastify.register(dbPlugin, {
        dbFile: loadEnv('DATABASE_FILE'),
    });
    fastify.log.info('Database plugin registered');

    // Decorates fastify instance with 'scheduler' and loads tasks
    await fastify.register(schedulerPlugin, {
      plivoAuthId: loadEnv('PLIVO_AUTH_ID'),
      plivoAuthToken: loadEnv('PLIVO_AUTH_TOKEN'),
      plivoSenderId: loadEnv('PLIVO_SENDER_ID'),
    });
    fastify.log.info('Scheduler plugin registered');

    // --- Register Routes ---
    await fastify.register(reminderRoutes, { prefix: '/api/v1' });
    fastify.log.info('Reminder routes registered under /api/v1');

    // --- Simple Health Check Route ---
    fastify.get('/health', async (request, reply) => {
        return { status: 'ok', timestamp: new Date().toISOString() };
    });

    return fastify;
}

// --- Start the Server ---
async function start() {
    try {
        const app = await buildApp();
        const port = parseInt(loadEnv('PORT', false, '3000'), 10);
        const host = loadEnv('HOST', false, '0.0.0.0');

        await app.listen({ port, host });
        app.log.info(`Server environment is ${process.env.NODE_ENV}`);
        // Ready signal is logged by Fastify on successful listen

    } catch (err) {
        logger.fatal({ err }, 'Failed to start server');
        process.exit(1);
    }
}

start();

// src/plugins/db.js
import fp from 'fastify-plugin';
import Database from 'better-sqlite3';

async function dbConnector(fastify, options) {
    const { dbFile } = options;
    if (!dbFile) {
        throw new Error('Database file path (dbFile) is required for dbPlugin');
    }

    try {
        // Connect to SQLite database. Creates the file if it doesn't exist.
        const db = new Database(dbFile, {
             // verbose: fastify.log.debug // Uncomment for detailed DB logging
        });
        fastify.log.info(`Connected to database: ${dbFile}`);

        // --- Define Schema and Create Table ---
        // Use TEXT for ISO 8601 date strings (UTC recommended)
        // status: pending, sending, sent, failed
        const createTableStmt = `
        CREATE TABLE IF NOT EXISTS reminders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            phoneNumber TEXT NOT NULL,
            message TEXT NOT NULL,
            reminderTime TEXT NOT NULL,
            status TEXT NOT NULL DEFAULT 'pending',
            createdAt TEXT DEFAULT CURRENT_TIMESTAMP,
            updatedAt TEXT DEFAULT CURRENT_TIMESTAMP,
            plivoMessageUuid TEXT NULL
        );
        `;
        db.exec(createTableStmt);
        fastify.log.info('Ensured "reminders" table exists.');

        // Add triggers for updatedAt (optional but good practice)
        const createTriggerStmt = `
        CREATE TRIGGER IF NOT EXISTS update_reminders_updatedAt
        AFTER UPDATE ON reminders FOR EACH ROW
        BEGIN
            UPDATE reminders SET updatedAt = CURRENT_TIMESTAMP WHERE id = OLD.id;
        END;
        `;
        db.exec(createTriggerStmt);
        fastify.log.info('Ensured "update_reminders_updatedAt" trigger exists.');

        // --- Decorate Fastify Instance ---
        // Make the 'db' instance available throughout the application
        fastify.decorate('db', db);

        // --- Cleanup Hook ---
        // Ensure the database connection is closed gracefully on server shutdown
        fastify.addHook('onClose', (instance, done) => {
            if (instance.db && instance.db.open) {
                instance.db.close();
                instance.log.info('Database connection closed.');
            }
            done();
        });

    } catch (err) {
        fastify.log.error({ err }, 'Failed to connect to or initialize the database');
        // Allow the error to propagate up to stop the server start
        throw err;
    }
}

// Export the plugin using fastify-plugin to avoid encapsulation issues
// and make the decorator ('db') available globally.
export default fp(dbConnector, {
    name: 'db-connector', // Optional name for the plugin
    fastify: '4-5.x'       // Specify Fastify version compatibility (v4 and v5)
});

// Example usage in a route handler (src/routes/reminders.js)
async function createReminderHandler(request, reply) {
  const { db } = request.server; // Access the decorated DB instance
  const { phoneNumber, message, reminderTime } = request.body;

  try {
    const stmt = db.prepare('INSERT INTO reminders (phoneNumber, message, reminderTime) VALUES (?, ?, ?)');
    const info = stmt.run(phoneNumber, message, reminderTime);
    // ... handle response ...
  } catch (err) {
    // ... handle error ...
  }
}

// src/services/plivoService.js
import { Client } from 'plivo'; // Use named import for ES Modules
import { setTimeout } from 'node:timers/promises'; // For async delay

class PlivoService {
    constructor(authId, authToken, senderId, logger) {
        if (!authId || !authToken || !senderId) {
            throw new Error('PlivoService requires authId, authToken, and senderId');
        }
        // Ensure logger is provided, default to console if not
        this.logger = logger || console;
        try {
            this.client = new Client(authId, authToken);
            this.senderId = senderId;
            this.logger.info('Plivo client initialized successfully.');
        } catch (error) {
            this.logger.error({ err: error }, 'Failed to initialize Plivo client');
            throw error; // Re-throw to prevent service usage with bad config
        }
    }

    /**
     * Sends an SMS message using the Plivo API with retry logic.
     * @param {string} to - The recipient phone number in E.164 format.
     * @param {string} text - The message content.
     * @returns {Promise<object|null&gt;} Plivo API response on success, null on failure after retries.
     */
    async sendSms(to, text) {
        this.logger.info({ to }, `Attempting to send SMS`);
        if (!this.client) {
             this.logger.error('Plivo client not initialized. Cannot send SMS.');
             return null;
        }

        const maxRetries = 2; // Retry up to 2 times (total 3 attempts)
        const initialDelay = 500; // Start with 500ms delay

        for (let attempt = 1; attempt &lt;= maxRetries + 1; attempt++) {
            try {
                const response = await this.client.messages.create(
                    this.senderId_ // Sender's phone number (from config)
                    to_           // Receiver's phone number
                    text          // The content of the message
                );
                this.logger.info({ response_ to_ attempt }_ 'SMS sent successfully via Plivo.');
                // Example response: { apiId: '...'_ message: 'message(s) queued'_ messageUuid: [ '...' ] }
                return response; // Success_ exit loop

            } catch (error) {
                this.logger.warn({
                     err: { message: error.message_ statusCode: error.statusCode }_
                     to_ attempt_ maxRetries
                }_ `Attempt ${attempt} failed sending SMS via Plivo.`);

                // Only retry on specific conditions (e.g._ server errors_ rate limits if desired)
                // Plivo error codes: https://www.plivo.com/docs/messaging/troubleshooting/error-codes
                // Generally retry on 5xx status codes from Plivo or network errors.
                // Avoid retrying on 4xx errors (like invalid number) as they won't succeed.
                const shouldRetry = (error.statusCode && error.statusCode &gt;= 500) ||
                                    (error.message && (error.message.includes('ETIMEDOUT') || error.message.includes('ECONNRESET'))); // Example network errors

                if (shouldRetry && attempt &lt;= maxRetries) {
                    const delay = initialDelay * Math.pow(2_ attempt - 1); // Exponential backoff
                    this.logger.info(`Retrying in ${delay}ms...`);
                    await setTimeout(delay); // Use async setTimeout
                } else {
                    // Don't retry or max retries reached_ log final error and exit
                     this.logger.error({
                          err: { message: error.message_ statusCode: error.statusCode_ feedback: error.feedback_ error: error.error }_
                          to
                     }_ 'Error sending SMS via Plivo (final attempt or non-retriable error).');
                    return null; // Indicate final failure
                }
            }
        }
        // This line should only be reached if the loop finishes without returning (which indicates a failure after retries)
        return null;
    }
}

export default PlivoService;

// src/plugins/scheduler.js
import fp from 'fastify-plugin';
import { fastifySchedule } from '@fastify/schedule';
import { SimpleIntervalJob } from 'toad-scheduler';
import PlivoService from '../services/plivoService.js'; // Import the service
import createSendRemindersTask from '../tasks/sendRemindersTask.js'; // Import the task factory

async function schedulerPlugin(fastify_ options) {
    const { plivoAuthId_ plivoAuthToken_ plivoSenderId } = options;

    // --- Instantiate Plivo Service ---
    // Pass credentials and the fastify logger to the service
    const plivoService = new PlivoService(plivoAuthId_ plivoAuthToken_ plivoSenderId_ fastify.log);

    // --- Register @fastify/schedule ---
    await fastify.register(fastifySchedule);
    fastify.log.info('@fastify/schedule registered.');

    // --- Create and Add Scheduled Task ---
    // Ensure the scheduler is ready before adding jobs
    fastify.ready().then(() => {
        fastify.log.info('Fastify instance is ready, setting up scheduled jobs.');

        // Check for due reminders every 30 seconds (adjust as needed)
        const intervalInSeconds = 30;

        // Create the task, injecting dependencies (db, plivoService, logger)
        const task = createSendRemindersTask(fastify.db, plivoService, fastify.log);

        // Create the job configuration
        const job = new SimpleIntervalJob(
            { seconds: intervalInSeconds, runImmediately: true }, // Run immediately on start, then every interval
            task, // The async task function to run
            { id: 'send-reminders-job', preventOverrun: true } // Unique ID, prevent task overlap
        );

        // Add the job to the scheduler
        try {
            fastify.scheduler.addSimpleIntervalJob(job);
            fastify.log.info(`Scheduled job "${job.id}" to run every ${intervalInSeconds} seconds.`);
        } catch (err) {
            fastify.log.error({ err, jobId: job.id }, 'Failed to add scheduled job');
            // Depending on severity, you might want to process.exit(1) here
        }
    }).catch(err => {
         fastify.log.error({ err }, 'Error during fastify.ready() in scheduler plugin');
    });

    // No decoration needed here, scheduler is accessed via fastify.scheduler
}

export default fp(schedulerPlugin, {
    name: 'scheduler-plugin',
    dependencies: ['db-connector'], // Ensure DB is ready before scheduler
    fastify: '4-5.x'
});

// src/tasks/sendRemindersTask.js
import { AsyncTask } from 'toad-scheduler';

// Factory function to create the task with injected dependencies
function createSendRemindersTask(db, plivoService, logger) {

    // The actual task logic, returned as an AsyncTask
    const taskLogic = async () => {
        logger.info('Running sendRemindersTask...');
        const now = new Date().toISOString(); // Get current time in UTC ISO 8601 format

        let remindersToSend = [];
        try {
            // --- Find Due Reminders ---
            // Select reminders that are 'pending' and whose time is now or in the past
            const stmt = db.prepare(`
                SELECT id, phoneNumber, message
                FROM reminders
                WHERE status = 'pending' AND reminderTime <= ?
                ORDER BY reminderTime ASC
                LIMIT 10 -- Process in batches to avoid overwhelming resources
            `);
            remindersToSend = stmt.all(now);
            logger.info(`Found ${remindersToSend.length} reminders due for sending.`);

        } catch (err) {
            logger.error({ err }_ 'Error fetching pending reminders from database.');
            return; // Exit task if DB query fails
        }

        if (remindersToSend.length === 0) {
            logger.info('No pending reminders to send at this time.');
            return; // Nothing to do
        }

        // --- Process Each Reminder ---
        for (const reminder of remindersToSend) {
            const { id_ phoneNumber_ message } = reminder;
            logger.info({ reminderId: id }_ 'Processing reminder.');

            try {
                // --- Mark as Sending (Optimistic Locking) ---
                // Prevents duplicate sends if the task runs again before completion
                const updateSendingStmt = db.prepare(`
                    UPDATE reminders SET status = 'sending'_ updatedAt = CURRENT_TIMESTAMP
                    WHERE id = ? AND status = 'pending' -- Ensure it's still pending
                `);
                const updateResult = updateSendingStmt.run(id);

                if (updateResult.changes === 0) {
                    // Another process might have picked it up already
                    logger.warn({ reminderId: id }_ 'Reminder was not in pending state when attempting to mark as sending. Skipping.');
                    continue; // Skip to the next reminder
                }

                logger.info({ reminderId: id }_ 'Marked reminder as "sending".');

                // --- Send SMS via Plivo Service ---
                const plivoResponse = await plivoService.sendSms(phoneNumber_ message);

                // --- Update Status Based on Plivo Response ---
                let finalStatus = 'failed'; // Default to failed
                let plivoUuid = null;

                if (plivoResponse && plivoResponse.messageUuid && plivoResponse.messageUuid.length > 0) {
                    finalStatus = 'sent';
                    plivoUuid = Array.isArray(plivoResponse.messageUuid) ? plivoResponse.messageUuid[0] : plivoResponse.messageUuid; // Plivo might return array
                    logger.info({ reminderId: id, plivoMessageUuid: plivoUuid }, 'Reminder successfully sent.');
                } else {
                    logger.error({ reminderId: id }, 'Failed to send reminder via Plivo (PlivoService indicated failure).');
                }

                const updateStatusStmt = db.prepare(`
                    UPDATE reminders
                    SET status = ?, plivoMessageUuid = ?, updatedAt = CURRENT_TIMESTAMP
                    WHERE id = ?
                `);
                updateStatusStmt.run(finalStatus, plivoUuid, id);
                logger.info({ reminderId: id, status: finalStatus }, 'Updated final reminder status in database.');

            } catch (err) {
                logger.error({ err, reminderId: id }, 'Unhandled error processing reminder. Attempting to mark as failed.');
                // Attempt to mark as failed even if sending failed partway through
                try {
                     // Use standard quotes for consistency if preferred, or backticks as originally shown
                     const failStmt = db.prepare(`UPDATE reminders SET status = 'failed', updatedAt = CURRENT_TIMESTAMP WHERE id = ? AND status != 'sent'`);
                     failStmt.run(id);
                } catch (dbErr) {
                     logger.error({ err: dbErr, reminderId: id }, 'Failed to mark reminder as failed after processing error.');
                }
            }
        } // End for loop

        logger.info('sendRemindersTask finished processing batch.');
    }; // End taskLogic

    // --- Error Handling Wrapper for the Task ---
    const errorHandler = (err) => {
        logger.error({ err }, 'Fatal error occurred within the scheduled AsyncTask (sendRemindersTask).');
        // Consider more robust error reporting here (e.g., Sentry, Better Stack)
    };

    // --- Return the AsyncTask ---
    // Wrap the logic in toad-scheduler's AsyncTask for proper handling
    return new AsyncTask('send-reminders-task', taskLogic, errorHandler);
}

export default createSendRemindersTask;

// src/routes/reminders.js
import { Static_ Type } from '@sinclair/typebox'; // For schema validation

// --- Request/Response Schemas using TypeBox ---

// E.164 regex (simplified version_ adjust for stricter needs)
const E164Format = /^\+[1-9]\d{1_14}$/;

const ReminderBaseSchema = Type.Object({
    phoneNumber: Type.RegExp(E164Format_ {
        description: 'Recipient phone number in E.164 format (e.g._ +14155551234)'_
        examples: ['+14155551234']_
    })_
    message: Type.String({
        minLength: 1_
        maxLength: 1600_ // Plivo limit for GSM 03.38 7-bit characters (https://support.plivo.com/hc/en-us/articles/360041298072)
        description: 'The content of the SMS message'_
    })_
    // ISO 8601 format (YYYY-MM-DDTHH:mm:ssZ or YYYY-MM-DDTHH:mm:ss+HH:mm)
    reminderTime: Type.String({
        format: 'date-time'_
        description: 'Scheduled time in ISO 8601 format (UTC recommended)'_
        examples: ['2025-12-31T23:59:59Z']_
    })_
});

const ReminderCreateSchema = ReminderBaseSchema;

const ReminderResponseSchema = Type.Intersect([
    Type.Object({
        id: Type.Integer({ description: 'Unique identifier for the reminder' })_
        status: Type.String({ description: 'Current status (pending_ sending_ sent_ failed)' })_
        createdAt: Type.String({ format: 'date-time'_ description: 'Timestamp when the reminder was created' })_
        updatedAt: Type.String({ format: 'date-time'_ description: 'Timestamp when the reminder was last updated' })_
        plivoMessageUuid: Type.Union([Type.String()_ Type.Null()]_ { description: 'Plivo message ID if sent_ otherwise null' })_
    })_
    ReminderBaseSchema_ // Include base fields
]);

// Type definition for TypeScript users (optional but good practice)
type ReminderCreate = Static<typeof ReminderCreateSchema&gt;;
// type ReminderResponse = Static<typeof ReminderResponseSchema&gt;; // If needed

const ParamsSchema = Type.Object({
    id: Type.Integer({ description: 'Reminder ID' }),
});
type ParamsType = Static<typeof ParamsSchema&gt;;

const ErrorSchema = Type.Object({
    statusCode: Type.Integer(),
    error: Type.String(),
    message: Type.String(),
});

// --- Route Definitions ---
export default async function reminderRoutes(fastify, options) {

    const { db } = fastify; // Access db instance decorated by the plugin

    // --- Create Reminder Endpoint ---
    fastify.post('/', {
        schema: {
            description: 'Schedule a new SMS reminder.',
            tags: ['Reminders'],
            summary: 'Create Reminder',
            body: ReminderCreateSchema,
            response: {
                201: Type.Object({
                    message: Type.String(),
                    reminderId: Type.Integer(),
                }),
                400: ErrorSchema, // Bad Request (validation failed)
                500: ErrorSchema, // Internal Server Error
            },
        },
    }, async (request, reply) =&gt; {
        const { phoneNumber, message, reminderTime } = request.body as ReminderCreate;
        const { log } = request; // Access request-specific logger

        // Basic validation: Ensure reminderTime is in the future
        if (new Date(reminderTime) &lt;= new Date()) {
            log.warn({ reminderTime }_ 'Attempt to schedule reminder in the past or present.');
            reply.code(400);
            return {
                statusCode: 400_
                error: 'Bad Request'_
                message: 'Reminder time must be in the future.'_
            };
        }

        try {
            const stmt = db.prepare(`
                INSERT INTO reminders (phoneNumber_ message_ reminderTime)
                VALUES (?_ ?_ ?)
            `);
            const info = stmt.run(phoneNumber_ message_ reminderTime);

            log.info({ reminderId: info.lastInsertRowid }_ 'Reminder created successfully.');
            reply.code(201); // Created
            return {
                message: 'Reminder scheduled successfully.'_
                reminderId: info.lastInsertRowid_
            };
        } catch (err) {
            log.error({ err }_ 'Error creating reminder in database.');
            reply.code(500);
            return {
                statusCode: 500_
                error: 'Internal Server Error'_
                message: 'Failed to schedule reminder.'_
            };
        }
    });

    // --- Get Reminder by ID Endpoint ---
    fastify.get('/:id'_ {
        schema: {
            description: 'Retrieve a specific reminder by its ID.'_
            tags: ['Reminders']_
            summary: 'Get Reminder'_
            params: ParamsSchema_
            response: {
                200: ReminderResponseSchema_
                404: ErrorSchema_ // Not Found
                500: ErrorSchema_
            }_
        }_
    }_ async (request_ reply) =&gt; {
        const { id } = request.params as ParamsType;
        const { log } = request;

        try {
            const stmt = db.prepare('SELECT * FROM reminders WHERE id = ?');
            const reminder = stmt.get(id);

            if (!reminder) {
                log.warn({ reminderId: id }, 'Reminder not found.');
                reply.code(404);
                return {
                    statusCode: 404,
                    error: 'Not Found',
                    message: `Reminder with ID ${id} not found.`,
                };
            }

            log.info({ reminderId: id }, 'Reminder retrieved successfully.');
            return reminder; // Automatically serialized by Fastify

        } catch (err) {
            log.error({ err, reminderId: id }, 'Error retrieving reminder from database.');
            reply.code(500);
            return {
                statusCode: 500,
                error: 'Internal Server Error',
                message: 'Failed to retrieve reminder.',
            };
        }
    });

    // --- Delete Reminder Endpoint ---
    fastify.delete('/:id', {
        schema: {
            description: 'Cancel/delete a pending reminder by its ID. Only pending reminders can be deleted.',
            tags: ['Reminders'],
            summary: 'Delete Reminder',
            params: ParamsSchema,
            response: {
                200: Type.Object({ message: Type.String() }),
                404: ErrorSchema, // Not Found or not pending
                500: ErrorSchema,
            },
        },
    }, async (request, reply) =&gt; {
        const { id } = request.params as ParamsType;
        const { log } = request;

        try {
            // Only allow deleting reminders that are still 'pending'
            const stmt = db.prepare(`
                DELETE FROM reminders
                WHERE id = ? AND status = 'pending'
            `);
            const info = stmt.run(id);

            if (info.changes === 0) {
                // Check if it exists at all to give a more specific error
                const checkStmt = db.prepare('SELECT status FROM reminders WHERE id = ?');
                const existing = checkStmt.get(id);
                if (!existing) {
                    log.warn({ reminderId: id }, 'Attempted to delete non-existent reminder.');
                    reply.code(404);
                    return { statusCode: 404, error: 'Not Found', message: `Reminder with ID ${id} not found.` };
                } else {
                    log.warn({ reminderId: id, status: existing.status }, 'Attempted to delete reminder not in pending state.');
                    reply.code(400); // Bad Request might be more appropriate than 404 here
                    return { statusCode: 400, error: 'Bad Request', message: `Reminder with ID ${id} cannot be deleted (status: ${existing.status}). Only pending reminders can be deleted.` };
                }
            }

            log.info({ reminderId: id }, 'Reminder deleted successfully.');
            return { message: `Reminder with ID ${id} deleted successfully.` };

        } catch (err) {
            log.error({ err, reminderId: id }, 'Error deleting reminder from database.');
            reply.code(500);
            return {
                statusCode: 500,
                error: 'Internal Server Error',
                message: 'Failed to delete reminder.',
            };
        }
    });

    // --- List Reminders Endpoint (Optional - Add Pagination/Filtering as needed) ---
    fastify.get('/', {
        schema: {
            description: 'List existing reminders (basic implementation).',
            tags: ['Reminders'],
            summary: 'List Reminders',
            // Add query parameters for pagination, filtering by status, etc. here
            response: {
                200: Type.Array(ReminderResponseSchema),
                500: ErrorSchema,
            },
        },
    }, async (request, reply) =&gt; {
        const { log } = request;
        try {
            // Basic list, consider adding LIMIT/OFFSET for pagination
            const stmt = db.prepare('SELECT * FROM reminders ORDER BY createdAt DESC LIMIT 50');
            const reminders = stmt.all();
            log.info(`Retrieved ${reminders.length} reminders.`);
            return reminders;
        } catch (err) {
            log.error({ err }, 'Error listing reminders from database.');
            reply.code(500);
            return {
                statusCode: 500,
                error: 'Internal Server Error',
                message: 'Failed to list reminders.',
            };
        }
    });
}