Online Appointment Scheduling System Development

Online Appointment System Development

Online appointment — not just "choose date and time" form. It's specialist schedule management, buffers between appointments, booking rules, notifications, and cancellations. Underestimating this complexity leads to double bookings, empty slots, and unhappy customers.

Data Model

CREATE TABLE staff (
    id          BIGSERIAL PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    timezone    VARCHAR(64) DEFAULT 'Europe/Moscow'
);

-- Specialist working schedule (template)
CREATE TABLE working_hours (
    id          BIGSERIAL PRIMARY KEY,
    staff_id    BIGINT REFERENCES staff(id),
    day_of_week SMALLINT NOT NULL, -- 0=Sun, 1=Mon ... 6=Sat
    start_time  TIME NOT NULL,
    end_time    TIME NOT NULL
);

-- Exceptions: days off and special hours
CREATE TABLE schedule_overrides (
    id          BIGSERIAL PRIMARY KEY,
    staff_id    BIGINT REFERENCES staff(id),
    date        DATE NOT NULL,
    is_day_off  BOOLEAN DEFAULT FALSE,
    start_time  TIME,
    end_time    TIME
);

CREATE TABLE services (
    id            BIGSERIAL PRIMARY KEY,
    name          VARCHAR(255) NOT NULL,
    duration_min  INT NOT NULL,          -- service duration in minutes
    buffer_after  INT DEFAULT 0,          -- buffer after visit
    capacity      INT DEFAULT 1           -- simultaneous clients
);

CREATE TABLE appointments (
    id           BIGSERIAL PRIMARY KEY,
    staff_id     BIGINT REFERENCES staff(id),
    service_id   BIGINT REFERENCES services(id),
    client_id    BIGINT REFERENCES clients(id),
    starts_at    TIMESTAMPTZ NOT NULL,
    ends_at      TIMESTAMPTZ NOT NULL,
    status       VARCHAR(32) DEFAULT 'pending', -- pending/confirmed/cancelled/completed
    notes        TEXT,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX ON appointments(staff_id, starts_at);

Free Slot Generation Algorithm

Key system logic — generating available time windows. Algorithm:

1. Take specialist working hours for date
2. Subtract already booked intervals (confirmed + pending appointments)
3. Split remaining time into slots with step equal to service duration + buffer
4. Filter slots starting in past or violating minimum advance time

class SlotGenerator {
    public function getAvailableSlots(
        Staff $staff,
        Service $service,
        Carbon $date
    ): array {
        $tz = new \DateTimeZone($staff->timezone);

        // Check override (day off or special hours)
        $override = ScheduleOverride::where('staff_id', $staff->id)
            ->where('date', $date->toDateString())
            ->first();

        if ($override?->is_day_off) {
            return [];
        }

        // Working window for date
        $dayOfWeek  = $date->dayOfWeek;
        $workHours  = $override ?? WorkingHours::where('staff_id', $staff->id)
            ->where('day_of_week', $dayOfWeek)
            ->first();

        if (!$workHours) {
            return [];
        }

        $windowStart = Carbon::parse($date->toDateString() . ' ' . $workHours->start_time, $tz);
        $windowEnd   = Carbon::parse($date->toDateString() . ' ' . $workHours->end_time, $tz);

        // Booked intervals
        $busy = Appointment::where('staff_id', $staff->id)
            ->whereIn('status', ['pending', 'confirmed'])
            ->whereBetween('starts_at', [$windowStart, $windowEnd])
            ->orderBy('starts_at')
            ->get(['starts_at', 'ends_at'])
            ->map(fn($a) => [
                'start' => Carbon::parse($a->starts_at),
                'end'   => Carbon::parse($a->ends_at),
            ])
            ->toArray();

        $slotDuration = $service->duration_min + $service->buffer_after;
        $minAdvance   = now()->addMinutes(30); // can't book less than 30 min advance
        $slots        = [];
        $cursor       = clone $windowStart;

        while ($cursor->copy()->addMinutes($service->duration_min)->lte($windowEnd)) {
            $slotEnd = $cursor->copy()->addMinutes($service->duration_min);

            $occupied = collect($busy)->first(fn($b) =>
                $cursor->lt($b['end']) && $slotEnd->gt($b['start'])
            );

            if (!$occupied && $cursor->gt($minAdvance)) {
                $slots[] = $cursor->toIso8601String();
            }

            $cursor->addMinutes($slotDuration);
        }

        return $slots;
    }
}

Booking API

GET  /api/booking/slots?staff_id=3&service_id=1&date=2025-04-10
POST /api/booking/appointments
GET  /api/booking/appointments/{id}
POST /api/booking/appointments/{id}/cancel

Example /api/booking/slots response:

{
  "date": "2025-04-10",
  "staff": { "id": 3, "name": "Anna Petrov" },
  "service": { "id": 1, "name": "Consultation", "duration_min": 60 },
  "slots": [
    "2025-04-10T09:00:00+03:00",
    "2025-04-10T10:00:00+03:00",
    "2025-04-10T12:00:00+03:00",
    "2025-04-10T15:00:00+03:00"
  ]
}

Race Condition Protection

When two clients simultaneously select one slot — need locking. Optimistic check with transaction:

public function bookAppointment(BookingRequest $data): Appointment {
    return DB::transaction(function() use ($data) {
        // Pessimistic lock: SELECT FOR UPDATE
        $conflict = Appointment::where('staff_id', $data->staff_id)
            ->whereIn('status', ['pending', 'confirmed'])
            ->where('starts_at', '<', $data->ends_at)
            ->where('ends_at', '>', $data->starts_at)
            ->lockForUpdate()
            ->first();

        if ($conflict) {
            throw new SlotUnavailableException('Slot already taken');
        }

        return Appointment::create([
            'staff_id'   => $data->staff_id,
            'service_id' => $data->service_id,
            'client_id'  => $data->client_id,
            'starts_at'  => $data->starts_at,
            'ends_at'    => $data->ends_at,
            'status'     => 'pending',
        ]);
    });
}

Notifications

Chain of notifications for appointment:

// Right after creation
class AppointmentBooked implements ShouldQueue {
    public function handle(AppointmentCreated $event): void {
        $appt = $event->appointment;
        // SMS to client
        SmsService::send($appt->client->phone, "Appointment confirmed for " . $appt->starts_at->format('d.m at H:i'));
        // Email to specialist
        Mail::to($appt->staff->email)->send(new NewAppointmentMail($appt));
    }
}

// Reminder 24h before (cron or scheduled job)
Appointment::confirmed()
    ->whereBetween('starts_at', [now()->addDay()->startOfDay(), now()->addDay()->endOfDay()])
    ->each(fn($a) => SmsService::send($a->client->phone, "Reminder: appointment tomorrow at " . $a->starts_at->format('H:i')));

Embedded Widget

For embedding on third-party sites, widget implemented as autonomous JS script:

<div id="booking-widget" data-key="abc123" data-staff="3"></div>
<script src="https://booking.example.com/widget.js" async></script>

// widget.js — shadow DOM for style isolation
class BookingWidget extends HTMLElement {
    connectedCallback() {
        const shadow = this.attachShadow({ mode: 'open' });
        const apiKey  = this.dataset.key;
        const staffId = this.dataset.staff;

        // Mount React app inside shadow root
        const root = document.createElement('div');
        shadow.appendChild(root);
        ReactDOM.createRoot(root).render(
            <BookingApp apiKey={apiKey} staffId={staffId} />
        );
    }
}
customElements.define('booking-widget', BookingWidget);

Implementation Timeline

Basic system for one specialist and one service with web interface and SMS notifications: 1–1.5 weeks. Support multiple specialists, services, group bookings, and embedded widget: 2.5–3 weeks. Google Calendar integration, booking payment, client cabinet with history: plus 1–2 weeks.