Building an Appointment Reminder System with Node.js, Express, and Sinch SMS

+-------------+       +------------------------+       +-----------------+       +-----------------+
| End User    | ----> | Browser                | ----> | Node.js/Express | ----> | PostgreSQL DB   |
| (Admin)     |       | (Input Form)           |       | App             |       | (Prisma)        |
+-------------+       +------------------------+       | - Routes        |       +-----------------+
                                                       | - Logic         |
                                                       | - Sinch Client  |
                                                       +--------+--------+
                                                                |
                                                                v
+-------------+                                          +---------------+
| Recipient   | <--------------------------------------- | Sinch SMS API |
| (Patient)   |       (Receives SMS Reminder)            +---------------+
+-------------+

mkdir sinch-appointment-scheduler
cd sinch-appointment-scheduler

npm init -y

# On Linux/macOS
mkdir public public/css views prisma
touch .env .gitignore app.js routes.js public/css/style.css views/appointment_form.ejs views/success.ejs views/error.ejs prisma/schema.prisma

# On Windows (Command Prompt)
mkdir public public\css views prisma
echo. > .env
echo. > .gitignore
echo. > app.js
echo. > routes.js
echo. > public\css\style.css
echo. > views\appointment_form.ejs
echo. > views\success.ejs
echo. > views\error.ejs
echo. > prisma\schema.prisma

# On Windows (PowerShell)
mkdir public, public\css, views, prisma
New-Item .env, .gitignore, app.js, routes.js, public\css\style.css, views\appointment_form.ejs, views\success.ejs, views\error.ejs, prisma\schema.prisma -ItemType File

# .gitignore

# Dependencies
node_modules/

# Environment variables
.env

# Prisma
prisma/migrations/*/*.sql
prisma/dev.db
prisma/dev.db-journal

# OS generated files
.DS_Store
Thumbs.db

npm install express @sinch/sdk-core dotenv ejs luxon @prisma/client connect-flash express-session helmet express-rate-limit
npm install --save-dev prisma nodemon

npx prisma init --datasource-provider postgresql

# .env

# Sinch API Credentials
# Get these from your Sinch Customer Dashboard -> Access Keys
SINCH_PROJECT_ID='YOUR_project_id'
SINCH_KEY_ID='YOUR_key_id'
SINCH_KEY_SECRET='YOUR_key_secret'

# Sinch SMS Configuration
# A phone number purchased or verified on your Sinch account
SINCH_FROM_NUMBER='+1xxxxxxxxxx' # Use E.164 format
# The region your SMS service plan is configured for ('us' or 'eu').
# This is crucial for routing and billing, even if not directly passed to the SDK constructor.
SINCH_SMS_REGION='us'

# Application Configuration
SESSION_SECRET='a-very-strong-random-secret-key' # Change this to a long random string for production
PORT=3000

# Database Connection URL (Prisma)
# Example for PostgreSQL: postgresql://USER:PASSWORD@HOST:PORT/DATABASE?schema=public
# Example for local SQLite (simpler for quick testing, create prisma/dev.db): file:./prisma/dev.db
DATABASE_URL="postgresql://your_db_user:your_db_password@localhost:5432/appointment_reminders?schema=public"

# Optional: Default Country Code for phone numbers if not provided with E.164
# Primarily useful if your app *only* serves one region and users might omit the code.
# The code below prioritizes E.164 format.
# DEFAULT_COUNTRY_CODE='+1' # Example for US/Canada

// package.json (add or modify the "scripts" section)
{
  // ... other properties
  "scripts": {
    "start": "node app.js",
    "dev": "nodemon app.js",
    "prisma:migrate:dev": "npx prisma migrate dev", // Renamed for clarity
    "prisma:migrate:deploy": "npx prisma migrate deploy", // For production deployments
    "prisma:generate": "npx prisma generate",
    "test": "echo \"Error: no test specified - Setup tests using Jest/Mocha/Supertest\" && exit 1" // Placeholder
  },
  // ... other properties
}

// prisma/schema.prisma

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

datasource db {
  provider = ""postgresql"" // Or your chosen provider
  url      = env(""DATABASE_URL"")
}

model Appointment {
  id              Int      @id @default(autoincrement())
  patientName     String
  doctorName      String
  patientPhone    String   // Store in E.164 format (e.g., +12223334444)
  appointmentTime DateTime // Store in UTC
  reminderSentAt  DateTime? // Timestamp when the reminder was successfully scheduled/sent via Sinch
  // Optional: Add a status field for more complex retry/failure handling
  // status          String   @default(""pending_schedule"") // e.g., pending_schedule, scheduled, schedule_failed
  createdAt       DateTime @default(now())
  updatedAt       DateTime @updatedAt

  @@index([appointmentTime]) // Index for querying appointments by time
}

npm run prisma:migrate:dev

npm run prisma:generate

// app.js
require('dotenv').config(); // Load .env variables early
const express = require('express');
const path = require('path');
const session = require('express-session');
const flash = require('connect-flash');
const helmet = require('helmet');
const rateLimit = require('express-rate-limit');
const routes = require('./routes'); // Import our routes

const app = express();
const PORT = process.env.PORT || 3000;

// --- Security Middleware ---
app.use(helmet()); // Set various security HTTP headers

// Basic rate limiting to prevent abuse
const limiter = rateLimit({
  windowMs: 15 * 60 * 1000, // 15 minutes
  max: 100, // Limit each IP to 100 requests per windowMs
  message: 'Too many requests from this IP, please try again after 15 minutes'
});
app.use(limiter); // Apply to all requests

// --- Standard Middleware ---

// Serve static files (CSS, JS, images) from the 'public' directory
app.use(express.static(path.join(__dirname, 'public')));

// Parse URL-encoded request bodies (form submissions)
app.use(express.urlencoded({ extended: false }));

// Parse JSON request bodies (optional, if you expect JSON input)
// app.use(express.json());

// Session middleware configuration
// Required for connect-flash
if (!process.env.SESSION_SECRET) {
    console.error("FATAL ERROR: SESSION_SECRET is not set in the environment variables.");
    process.exit(1);
}
app.use(session({
  secret: process.env.SESSION_SECRET,
  resave: false,
  saveUninitialized: false,
  cookie: {
    // Use secure cookies in production (requires HTTPS)
    secure: process.env.NODE_ENV === 'production',
    httpOnly: true, // Prevent client-side JS access
    maxAge: 60 * 60 * 1000 // Example: 1 hour expiry
  }
}));

// Flash messages middleware
// Must be after session middleware
app.use(flash());

// Make flash messages available in templates
app.use((req, res, next) => {
  res.locals.success_msg = req.flash('success_msg');
  res.locals.error_msg = req.flash('error_msg');
  next();
});

// --- View Engine Setup ---
app.set('view engine', 'ejs');
app.set('views', path.join(__dirname, 'views')); // Tell Express where to find view files

// --- Routes ---
// Mount the routes defined in routes.js
app.use('/', routes);

// --- Error Handling ---
// Catch 404 and forward to error handler
app.use((req, res, next) => {
  const error = new Error('Not Found');
  error.status = 404;
  next(error);
});

// Generic error handler
app.use((err, req, res, next) => {
  // Log the full error stack in development, or just the message in production for security
  console.error(process.env.NODE_ENV === 'development' ? err.stack : err.message);

  res.status(err.status || 500);
  res.render('error', { // Render the error page
      message: err.message,
      // Provide error object with stack trace only in development
      error: process.env.NODE_ENV === 'development' ? err : {}
  });
});


// --- Start Server (only if run directly) ---
// This structure allows importing 'app' for testing without starting the server
if (require.main === module) {
  app.listen(PORT, () => {
    console.log(`Server started on port ${PORT}. Access at http://localhost:${PORT}`);
  });
}

// Export the app instance for testing or potential programmatic use
module.exports = app;

// routes.js
const express = require('express');
const { PrismaClient } = require('@prisma/client');
const { DateTime } = require('luxon');
const { SinchClient } = require('@sinch/sdk-core');

const router = express.Router();
const prisma = new PrismaClient();

// --- Check for Sinch Configuration ---
// Ensure critical environment variables are loaded
const requiredEnvVars = ['SINCH_PROJECT_ID', 'SINCH_KEY_ID', 'SINCH_KEY_SECRET', 'SINCH_FROM_NUMBER', 'SINCH_SMS_REGION'];
const missingEnvVars = requiredEnvVars.filter(varName => !process.env[varName]);

if (missingEnvVars.length > 0) {
    console.error(`FATAL ERROR: Missing required Sinch environment variables: ${missingEnvVars.join(', ')}`);
    process.exit(1); // Exit if critical config is missing
}

// --- Initialize Sinch Client ---
const sinchClient = new SinchClient({
    projectId: process.env.SINCH_PROJECT_ID,
    keyId: process.env.SINCH_KEY_ID,
    keySecret: process.env.SINCH_KEY_SECRET,
    // Note: SMS region ('us' or 'eu') is crucial for your account's service plan/routing,
    // but typically not needed in the SinchClient constructor for v1+ SMS API calls.
});

// --- Helper Function to Send Scheduled SMS ---
async function scheduleReminder(appointment) {
    // Calculate reminder time (e.g., 2 hours before appointment)
    // Ensure appointmentTime is a Luxon DateTime object in UTC
    const appointmentDtUtc = DateTime.fromJSDate(appointment.appointmentTime, { zone: 'utc' });
    const reminderDtUtc = appointmentDtUtc.minus({ hours: 2 });

    // Format for Sinch API (ISO 8601 format, Z denotes UTC)
    const sendAtIso = reminderDtUtc.toISO();

    // Construct the message body
    // Display appointment time in server's local time for the message - consider user timezone if needed
    const localAppointmentTimeStr = appointmentDtUtc.setZone('local').toLocaleString(DateTime.DATETIME_SHORT);
    const messageBody = `Reminder: Your appointment with ${appointment.doctorName} is scheduled for ${localAppointmentTimeStr}.`;

    // Recipient number should already be in E.164 format from validation/storage
    const recipientNumber = appointment.patientPhone;

    console.log(`Scheduling SMS to ${recipientNumber} for ${sendAtIso}. Message: "${messageBody}"`);

    try {
        const response = await sinchClient.sms.batches.send({
            sendSMSRequestBody: {
                to: [recipientNumber],
                from: process.env.SINCH_FROM_NUMBER,
                body: messageBody,
                send_at: sendAtIso, // The crucial scheduling parameter
                // Optional: delivery_report: 'summary' or 'full'
            }
        });

        console.log('Sinch API Send Response:', JSON.stringify(response));

        // Update the appointment record in the database after successful scheduling
        await prisma.appointment.update({
            where: { id: appointment.id },
            data: { reminderSentAt: new Date() } // Record scheduling time
            // Optional: Update status field if using one: data: { reminderSentAt: new Date(), status: 'scheduled' }
        });

        return { success: true, batchId: response.id };

    } catch (error) {
        let errorMessage = error.message;
        if (error.response && error.response.data) {
            // Extract more specific Sinch error details if available
            errorMessage = `Sinch API Error: ${JSON.stringify(error.response.data)}`;
        }
        console.error('Error scheduling SMS via Sinch:', errorMessage);
        // Consider more specific error handling based on Sinch error codes if needed
        return { success: false, error: errorMessage };
    }
}


// --- Route Definitions ---

// GET / : Display the appointment scheduling form
router.get('/', (req, res) => {
    res.render('appointment_form', {
        formData: {} // Pass empty object initially
    });
});

// POST /schedule : Handle form submission, validate, save, and schedule reminder
router.post('/schedule', async (req, res) => {
    const { patientName, doctorName, patientPhone, appointmentDate, appointmentTime } = req.body;

    // --- Basic Input Validation ---
    if (!patientName || !doctorName || !patientPhone || !appointmentDate || !appointmentTime) {
        req.flash('error_msg', 'Please fill in all fields.');
        // Re-render form with errors and previous data
        return res.render('appointment_form', { formData: req.body });
    }

    // --- Date/Time Processing with Luxon ---
    let appointmentDt;
    try {
        // Combine date and time.
        // WARNING: Assumes input is in the server's local time zone.
        // See Section 7 for robust timezone handling (recommend storing UTC).
        appointmentDt = DateTime.fromISO(`${appointmentDate}T${appointmentTime}`, { zone: 'local' });

        if (!appointmentDt.isValid) {
            throw new Error(`Invalid date/time format: ${appointmentDt.invalidReason}`);
        }
    } catch (err) {
        console.error("Date/Time Parsing Error:", err);
        req.flash('error_msg', 'Invalid date or time format provided.');
        return res.render('appointment_form', { formData: req.body });
    }

    const appointmentDtUtc = appointmentDt.toUTC(); // Convert to UTC for storage and scheduling
    const reminderDtUtc = appointmentDtUtc.minus({ hours: 2 }); // Calculate reminder time
    const nowUtc = DateTime.now().toUTC();

    // --- Validation: Ensure reminder time is in the future ---
    // Allow a small buffer (e.g., 5 minutes) to avoid race conditions with API call
    if (reminderDtUtc < nowUtc.plus({ minutes: 5 })) {
        req.flash('error_msg', 'Appointment must be far enough in the future to schedule a reminder (at least ~2 hours + 5 mins from now).');
        return res.render('appointment_form', { formData: req.body });
    }

    // --- Phone Number Formatting (Robust E.164 target) ---
    const trimmedPhone = patientPhone.trim();
    let formattedPhone;
    if (trimmedPhone.startsWith('+')) {
        // Input claims to be E.164. Keep the leading +, remove other non-digits.
        formattedPhone = '+' + trimmedPhone.substring(1).replace(/\D/g, '');
    } else {
        // Assume local format, remove all non-digits, prepend default country code.
        const digits = trimmedPhone.replace(/\D/g, '');
        // Use default country code from .env or fallback (e.g., +1 for North America)
        const defaultCode = process.env.DEFAULT_COUNTRY_CODE || '+1';
        formattedPhone = defaultCode + digits;
        console.log(`Applied default country code to ${patientPhone} -> ${formattedPhone}`);
    }

    // Basic validation for E.164-like format (starts with +, at least 7 digits)
    // Enhance with libphonenumber-js for production robustness (See Section 7)
    if (!/^\+\d{7,}$/.test(formattedPhone)) {
        req.flash('error_msg', `Invalid phone number format provided: ${patientPhone}. Please use E.164 format (e.g., +15551234567) or a valid local number.`);
        return res.render('appointment_form', { formData: req.body });
    }

    // --- Save Appointment to Database ---
    let newAppointment;
    try {
        newAppointment = await prisma.appointment.create({
            data: {
                patientName,
                doctorName,
                patientPhone: formattedPhone, // Store formatted E.164 number
                appointmentTime: appointmentDtUtc.toJSDate(), // Store as native Date (in UTC)
                // Optional: status: 'pending_schedule' // If using status field
            }
        });
        console.log('Appointment saved to DB:', newAppointment.id);

    } catch (dbError) {
        console.error('Database Error Saving Appointment:', dbError);
        req.flash('error_msg', 'Failed to save appointment due to a database error. Please try again.');
        return res.render('appointment_form', { formData: req.body });
    }

    // --- Schedule the Reminder via Sinch ---
    const scheduleResult = await scheduleReminder(newAppointment);

    if (scheduleResult.success) {
        req.flash('success_msg', `Appointment scheduled successfully! Reminder SMS queued (Batch ID: ${scheduleResult.batchId}).`);
        res.redirect('/success'); // Redirect to a success page
    } else {
        // Scheduling failed after saving to DB.
        // The DB record exists but no reminder is scheduled.
        // Implications: Requires manual follow-up, a cleanup job, or a retry mechanism (see Section 5).
        // Adding a 'status' field to the Appointment model helps manage this.
        console.error(`Failed to schedule reminder for appointment ID: ${newAppointment.id}. Error: ${scheduleResult.error}`);
        // Optional: Update DB status to 'schedule_failed' here
        // await prisma.appointment.update({ where: { id: newAppointment.id }, data: { status: 'schedule_failed' } });

        req.flash('error_msg', `Appointment saved (ID: ${newAppointment.id}), but failed to schedule the SMS reminder via Sinch. Error: ${scheduleResult.error}. Please contact support or try rescheduling manually.`);
        // Redirect back to the form, retaining user input
        res.render('appointment_form', { formData: req.body });
    }
});

// GET /success : Display a success confirmation page
router.get('/success', (req, res) => {
    // Check if a success message exists from a previous redirect
    // If accessed directly without a flash message, redirect home.
    const successMsg = req.flash('success_msg'); // Retrieve and clear the message
    if (!successMsg || successMsg.length === 0) {
       return res.redirect('/');
    }
    // Pass the message to the view
    res.render('success', { success_msg: successMsg });
});

// GET /health : Basic health check endpoint
router.get('/health', (req, res) => {
    // Could add checks for DB connection, Sinch connectivity etc. later
    res.status(200).json({ status: 'OK', timestamp: new Date().toISOString() });
});


module.exports = router;

/* public/css/style.css */
body {
    font-family: system-ui, -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, Oxygen, Ubuntu, Cantarell, 'Open Sans', 'Helvetica Neue', sans-serif;
    line-height: 1.6;
    padding: 20px;
    max-width: 600px;
    margin: 40px auto;
    background-color: #f8f9fa;
    color: #212529;
}

h1, h2 {
    color: #343a40;
    margin-bottom: 1rem;
}

.container {
    background: #ffffff;
    padding: 30px;
    border-radius: 8px;
    box-shadow: 0 4px 8px rgba(0, 0, 0, 0.05);
}

.form-group {
    margin-bottom: 1.25rem;
}

label {
    display: block;
    margin-bottom: 0.5rem;
    font-weight: 600;
}

input[type=""text""],
input[type=""tel""],
input[type=""date""],
input[type=""time""],
button {
    width: 100%;
    padding: 0.75rem 1rem;
    border: 1px solid #ced4da;
    border-radius: 4px;
    box-sizing: border-box; /* Include padding and border */
    font-size: 1rem;
    line-height: 1.5;
}

input:focus {
    border-color: #80bdff;
    outline: 0;
    box-shadow: 0 0 0 0.2rem rgba(0, 123, 255, 0.25);
}

button {
    background-color: #007bff;
    color: white;
    border: none;
    cursor: pointer;
    font-weight: 600;
    transition: background-color 0.2s ease-in-out;
}

button:hover {
    background-color: #0056b3;
}

.alert {
    padding: 1rem 1.25rem;
    margin-bottom: 1.5rem;
    border: 1px solid transparent;
    border-radius: 4px;
    font-size: 0.95rem;
}

.alert-success {
    color: #0f5132;
    background-color: #d1e7dd;
    border-color: #badbcc;
}

.alert-danger {
    color: #842029;
    background-color: #f8d7da;
    border-color: #f5c2c7;
}

small {
    display: block;
    margin-top: 0.25rem;
    font-size: 0.875em;
    color: #6c757d;
}

pre {
    background-color: #e9ecef;
    padding: 1rem;
    border-radius: 4px;
    overflow-x: auto;
}

<!-- views/appointment_form.ejs -->
<!DOCTYPE html>
<html lang=""en"">
<head>
    <meta charset=""UTF-8"">
    <meta name=""viewport"" content=""width=device-width, initial-scale=1.0"">
    <title>Schedule Appointment Reminder</title>
    <link rel=""stylesheet"" href=""/css/style.css"">
</head>
<body>
    <div class=""container"">
        <h1>Schedule Appointment Reminder</h1>

        <% if (locals.error_msg && error_msg.length > 0) { %>
            <div class=""alert alert-danger""><%= error_msg %></div>
        <% } %>

        <form action=""/schedule"" method=""POST"">
            <div class=""form-group"">
                <label for=""patientName"">Patient Name:</label>
                <input type=""text"" id=""patientName"" name=""patientName"" value=""<%= locals.formData && formData.patientName ? formData.patientName : '' %>"" required>
            </div>
            <div class=""form-group"">
                <label for=""doctorName"">Doctor/Service Provider Name:</label>
                <input type=""text"" id=""doctorName"" name=""doctorName"" value=""<%= locals.formData && formData.doctorName ? formData.doctorName : '' %>"" required>
            </div>
            <div class=""form-group"">
                <label for=""patientPhone"">Patient Phone Number:</label>
                <input type=""tel"" id=""patientPhone"" name=""patientPhone"" placeholder=""e.g., +15551234567 or local number"" value=""<%= locals.formData && formData.patientPhone ? formData.patientPhone : '' %>"" required>
                <small>Use E.164 format (+CountryCodeNumber) or local number if default code is set.</small>
            </div>
            <div class=""form-group"">
                <label for=""appointmentDate"">Appointment Date:</label>
                <input type=""date"" id=""appointmentDate"" name=""appointmentDate"" value=""<%= locals.formData && formData.appointmentDate ? formData.appointmentDate : '' %>"" required>
            </div>
            <div class=""form-group"">
                <label for=""appointmentTime"">Appointment Time:</label>
                <input type=""time"" id=""appointmentTime"" name=""appointmentTime"" value=""<%= locals.formData && formData.appointmentTime ? formData.appointmentTime : '' %>"" required>
                <small>Time is assumed to be in the server's local timezone.</small>
            </div>
            <button type=""submit"">Schedule Reminder</button>
        </form>
    </div>
</body>
</html>

<!-- views/success.ejs -->
<!DOCTYPE html>
<html lang=""en"">
<head>
    <meta charset=""UTF-8"">
    <meta name=""viewport"" content=""width=device-width, initial-scale=1.0"">
    <title>Appointment Scheduled</title>
    <link rel=""stylesheet"" href=""/css/style.css"">
</head>
<body>
    <div class=""container"">
        <h1>Success!</h1>

        <% if (locals.success_msg && success_msg.length > 0) { %>
            <div class=""alert alert-success""><%= success_msg %></div>
        <% } else { %>
            <p>Appointment scheduled successfully.</p> <!-- Fallback message -->
        <% } %>

        <p><a href=""/"">Schedule another appointment</a></p>
    </div>
</body>
</html>

<!-- views/error.ejs -->
<!DOCTYPE html>
<html lang=""en"">
<head>
    <meta charset=""UTF-8"">
    <meta name=""viewport"" content=""width=device-width, initial-scale=1.0"">
    <title>Error</title>
    <link rel=""stylesheet"" href=""/css/style.css"">
</head>
<body>
    <div class=""container"">
        <h1>An Error Occurred</h1>
        <div class=""alert alert-danger"">
            <%= message %>
        </div>

        <% if (locals.error && error.stack) { %>
            <h2>Stack Trace (Development Mode)</h2>
            <pre><code><%= error.stack %></code></pre>
        <% } %>

        <p><a href=""/"">Return to Home</a></p>
    </div>
</body>
</html>

npm run prisma:migrate:dev

npm run dev

npx prisma studio