Video Consultation Scheduling on Website

Video Consultation Scheduling System Development

Scheduling system is not just a form. Must display real specialist availability, prevent double booking, account for time zones, manage cancellations and rescheduling, sync with Google Calendar or Outlook.

Availability Structure

CREATE TABLE availability_schedules (
  id UUID PRIMARY KEY,
  specialist_id UUID REFERENCES specialists(id),
  day_of_week SMALLINT NOT NULL,
  start_time TIME NOT NULL,
  end_time TIME NOT NULL,
  is_active BOOLEAN DEFAULT true
);

CREATE TABLE availability_overrides (
  id UUID PRIMARY KEY,
  specialist_id UUID REFERENCES specialists(id),
  date DATE NOT NULL,
  type VARCHAR(50),
  start_time TIME,
  end_time TIME
);

CREATE TABLE bookings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  specialist_id UUID REFERENCES specialists(id),
  client_id UUID REFERENCES users(id),
  starts_at TIMESTAMPTZ NOT NULL,
  ends_at TIMESTAMPTZ NOT NULL,
  status VARCHAR(50) DEFAULT 'confirmed',
  cancel_reason TEXT,
  google_event_id VARCHAR(255),
  created_at TIMESTAMPTZ DEFAULT now()
);

Available Slots Algorithm

async function getAvailableSlots(
  specialistId: string,
  date: string,
  durationMinutes: number,
  userTimezone: string
): Promise<Array<{ start: string; end: string }>> {
  const schedule = await db.query(
    `SELECT start_time, end_time FROM availability_schedules
     WHERE specialist_id = $1 AND day_of_week = $2 AND is_active = true`,
    [specialistId, getISODayOfWeek(new Date(date))]
  );

  if (!schedule.rows.length) return [];

  const override = await db.query(
    `SELECT * FROM availability_overrides
     WHERE specialist_id = $1 AND date = $2`,
    [specialistId, date]
  );

  if (override.rows[0]?.type === 'blocked') return [];

  const workStart = override.rows[0]?.start_time ?? schedule.rows[0].start_time;
  const workEnd = override.rows[0]?.end_time ?? schedule.rows[0].end_time;

  const booked = await db.query(
    `SELECT starts_at, ends_at FROM bookings
     WHERE specialist_id = $1 AND DATE(starts_at AT TIME ZONE $3) = $2 AND status = 'confirmed'`,
    [specialistId, date, userTimezone]
  );

  const slots: Array<{ start: string; end: string }> = [];
  let current = parseTime(date, workStart, userTimezone);
  const end = parseTime(date, workEnd, userTimezone);

  while (current < end) {
    const slotEnd = addMinutes(current, durationMinutes);
    if (slotEnd > end) break;

    const isBusy = booked.rows.some(b =>
      current < new Date(b.ends_at) && slotEnd > new Date(b.starts_at)
    );

    if (!isBusy) {
      slots.push({
        start: current.toISOString(),
        end: slotEnd.toISOString(),
      });
    }

    current = addMinutes(current, durationMinutes);
  }

  return slots;
}

Booking API with Double-Booking Protection

app.post('/api/bookings', authenticate, async (req, res) => {
  const { specialistId, startsAt, durationMinutes } = req.body;
  const endsAt = addMinutes(new Date(startsAt), durationMinutes);

  try {
    const booking = await db.transaction(async (trx) => {
      const conflict = await trx.query(
        `SELECT id FROM bookings
         WHERE specialist_id = $1 AND status = 'confirmed'
         AND tstzrange(starts_at, ends_at) && tstzrange($2::timestamptz, $3::timestamptz)
         FOR UPDATE NOWAIT`,
        [specialistId, startsAt, endsAt.toISOString()]
      );

      if (conflict.rows.length > 0) {
        throw Object.assign(new Error('Slot taken'), { code: 'CONFLICT' });
      }

      const [booking] = await trx.query(
        `INSERT INTO bookings (specialist_id, client_id, starts_at, ends_at)
         VALUES ($1, $2, $3, $4) RETURNING *`,
        [specialistId, req.user.id, startsAt, endsAt.toISOString()]
      );

      return booking;
    });

    await syncToGoogleCalendar(booking);
    await sendBookingConfirmation(booking, req.user);

    res.json(booking);
  } catch (err: any) {
    if (err.code === 'CONFLICT') {
      return res.status(409).json({ error: 'Slot unavailable' });
    }
    throw err;
  }
});

Google Calendar Sync

import { google } from 'googleapis';

async function syncToGoogleCalendar(booking: Booking) {
  const specialist = await db.specialists.findById(booking.specialist_id);
  if (!specialist.google_calendar_token) return;

  const oauth2Client = new google.auth.OAuth2(...);
  oauth2Client.setCredentials(specialist.google_calendar_token);

  const calendar = google.calendar({ version: 'v3', auth: oauth2Client });
  const client = await db.users.findById(booking.client_id);

  const event = await calendar.events.insert({
    calendarId: 'primary',
    requestBody: {
      summary: `Consultation with ${client.name}`,
      start: { dateTime: booking.starts_at.toISOString() },
      end: { dateTime: booking.ends_at.toISOString() },
      attendees: [{ email: client.email }],
      conferenceData: { createRequest: { requestId: booking.id } },
    },
    conferenceDataVersion: 1,
  });

  await db.bookings.update(booking.id, { google_event_id: event.data.id });
}

Timeline

Complete scheduling with calendar availability, double-booking protection, reminders, Google Calendar sync—1.5–2 weeks.