System design for doctor appointment booking system

Designing a doctor appointment booking system involves creating a platform that allows patients to schedule appointments with doctors, manage their bookings, and handle administrative tasks efficiently. Below is a detailed system design using Prisma for database modeling, Express.js for API routes, and a recommended tech stack:

Tech Stack

• Backend: Node.js with Express.js
• Database: PostgreSQL for relational data (users, doctors, appointments), Redis for caching
• ORM: Prisma for database interactions
• Caching: Redis for caching frequently accessed data (session management, search results)
• Authentication: JWT (JSON Web Tokens) for user authentication and authorization
• Messaging: RabbitMQ for handling asynchronous tasks (appointment reminders, notifications)
• Payment Gateway: Optional, depending on system requirements
• Monitoring: Prometheus and Grafana for monitoring system metrics

System Components

1. Client Applications (Web and Mobile):

   • Interfaces for patients to search for doctors, view availability, book appointments, and manage their bookings.

2. Express.js Backend

   • Prisma Models:

     // schema.prisma
     
     datasource db {
       provider = "postgresql"
       url      = env("DATABASE_URL")
     }
     
     generator client {
       provider = "prisma-client-js"
     }
     
     model User {
       id        Int      @id @default(autoincrement())
       username  String   @unique
       email     String   @unique
       password  String
       appointments Appointment[]
       createdAt DateTime @default(now())
     }
     
     model Doctor {
       id          Int           @id @default(autoincrement())
       name        String
       specialization String
       appointments Appointment[]
     }
     
     model Appointment {
       id           Int      @id @default(autoincrement())
       userId       Int
       user         User     @relation(fields: [userId], references: [id])
       doctorId     Int
       doctor       Doctor   @relation(fields: [doctorId], references: [id])
       appointmentDateTime DateTime
       status       String   // pending, confirmed, cancelled
       createdAt    DateTime @default(now())
     }
     

   • Express API Routes:

     // server.js
     
     const express = require('express');
     const { PrismaClient } = require('@prisma/client');
     const prisma = new PrismaClient();
     const jwt = require('jsonwebtoken');
     const bcrypt = require('bcrypt');
     
     const app = express();
     app.use(express.json());
     
     // Endpoint for user registration
     app.post('/register', async (req, res) => {
       const { username, email, password } = req.body;
       try {
         const hashedPassword = await bcrypt.hash(password, 10);
         const user = await prisma.user.create({
           data: {
             username,
             email,
             password: hashedPassword,
           },
         });
         res.json(user);
       } catch (error) {
         console.error(error);
         res.status(500).json({ error: 'Failed to register user' });
       }
     });
     
     // Endpoint for user login and JWT generation
     app.post('/login', async (req, res) => {
       const { username, password } = req.body;
       try {
         const user = await prisma.user.findUnique({ where: { username } });
         if (!user) {
           return res.status(404).json({ error: 'User not found' });
         }
         const passwordMatch = await bcrypt.compare(password, user.password);
         if (!passwordMatch) {
           return res.status(401).json({ error: 'Invalid password' });
         }
         const token = jwt.sign({ userId: user.id }, 'secret', { expiresIn: '1h' });
         res.json({ token });
       } catch (error) {
         console.error(error);
         res.status(500).json({ error: 'Login failed' });
       }
     });
     
     // Endpoint for searching doctors by specialization
     app.get('/doctors', async (req, res) => {
       const { specialization } = req.query;
       try {
         const doctors = await prisma.doctor.findMany({
           where: {
             specialization: { contains: specialization || '' },
           },
         });
         res.json(doctors);
       } catch (error) {
         console.error(error);
         res.status(500).json({ error: 'Failed to fetch doctors' });
       }
     });
     
     // Endpoint for booking an appointment
     app.post('/appointments', async (req, res) => {
       const { userId, doctorId, appointmentDateTime } = req.body;
       try {
         const appointment = await prisma.appointment.create({
           data: {
             userId: parseInt(userId),
             doctorId: parseInt(doctorId),
             appointmentDateTime,
             status: 'pending',
           },
         });
         res.json(appointment);
       } catch (error) {
         console.error(error);
         res.status(500).json({ error: 'Failed to book appointment' });
       }
     });
     
     const PORT = process.env.PORT || 3000;
     app.listen(PORT, () => {
       console.log(`Server is running on http://localhost:${PORT}`);
     });
     

3. Database Layer

   • PostgreSQL: Stores user data, doctor details, appointment information, and transactional data.
   • Redis: Used for caching doctor search results, session management, and improving application performance.

4. Authentication and Authorization

   • JWT: Token-based authentication for securing API endpoints and managing user sessions.

5. Messaging

   • RabbitMQ: Used for asynchronous tasks such as sending appointment reminders and notifications to users and doctors.

6. Monitoring and Analytics

   • Prometheus and Grafana: Monitor system metrics, track performance, and troubleshoot issues proactively.

Scalability and Fault Tolerance

• Horizontal Scaling: Deploy multiple instances of microservices and use load balancing to handle high traffic and ensure availability.
• Database Sharding: Partition databases to distribute load and scale horizontally as the number of users and appointments increase.
• Redundancy and Backup: Store backups in Amazon S3 and deploy services across multiple availability zones (AZs) for fault tolerance and disaster recovery.

Security

• Data Encryption: Use SSL/TLS for secure data transmission and implement encryption at rest for sensitive information stored in databases.
• Authorization: Implement role-based access control (RBAC) to restrict access to sensitive APIs and operations.