​

Doctor Schedule & True Slot Availability

​

Problem

• Which time slots are actually within clinic hours
• Whether a slot is already taken by another patient
• Whether a specific doctor works that day/time

​

What We’re Building

1. doctor_schedules table — per-doctor weekly availability
2. GET /available-slots API — returns genuinely open slots for a given date + doctor
3. Patient booking UI — slot grid replacing the TimePicker
4. Settings: Doctor Schedule — weekly picker per doctor in Settings > Staff

​

Schema: doctor_schedules

CREATE TABLE app.doctor_schedules (
  id          text PRIMARY KEY DEFAULT gen_random_uuid(),
  clinic_id   text NOT NULL REFERENCES app.clinics(id) ON DELETE CASCADE,
  doctor_id   text NOT NULL REFERENCES app.users(id) ON DELETE CASCADE,
  day_of_week text NOT NULL CHECK (day_of_week IN ('monday','tuesday','wednesday','thursday','friday','saturday','sunday')),
  start_time  time NOT NULL,
  end_time    time NOT NULL,
  is_off      boolean NOT NULL DEFAULT false,
  created_at  timestamp DEFAULT now() NOT NULL,
  updated_at  timestamp DEFAULT now() NOT NULL,
  UNIQUE(clinic_id, doctor_id, day_of_week)
);

​

API: Available Slots

​

Endpoint

​

Query Params

​

Logic

1. Parse dayOfWeek from date
2. Fetch clinic operating hours for that day → if isClosed, return { slots: [], clinicClosed: true }
3. Fetch doctor_schedules for that doctor+day → if is_off, return { slots: [], doctorOff: true }
4. If no doctor schedule row exists, fall back to clinic hours (doctor has no schedule configured)
5. Effective window = intersection of clinic hours and doctor hours
6. Generate 30-minute slots within effective window
7. Fetch existing confirmed/pending appointments for that doctor+date
8. Remove booked slots
9. Remove past slots if date is today (compare to PKT now)
10. Return { slots: ["09:00","09:30",...], clinicPhone: "...", clinicClosed: false }

​

Response

{
  "slots": ["09:00", "09:30", "10:00"],
  "clinicPhone": "+92 300 1234567",
  "clinicClosed": false,
  "doctorOff": false
}

​

Auth

​

Patient Booking UI Changes

​

SlotPicker Component (ui/src/components/ui/slot-picker.tsx)

• Fetches /available-slots?date=&doctorId= when date or doctor changes
• Shows loading skeleton while fetching
• Renders slots as a grid of buttons (4 per row, 30-min increments)
• Grey/disabled: past slots, booked slots
• If clinicClosed === true or slots.length === 0: show “Clinic is closed on this day. Call us: [phone]”
• Clinic phone sourced from the clinic object already loaded in portal

​

Doctor Selection

• If clinic has multiple active doctors: show a “Doctor” dropdown (optional — patient can leave it “Any”)
• “Any” = fetch slots for all doctors and show union (slot is available if any doctor has it free)

​

Settings: Doctor Schedule

• Weekly grid: Mon–Sun rows, each row has: Toggle (working/off) + time range (start–end)
• Default: inherits clinic hours if no schedule set
• Save calls PUT /api/v1/protected/doctor-schedules/:doctorId

​

Routes

• GET /api/v1/protected/doctor-schedules — fetch all schedules for clinic
• PUT /api/v1/protected/doctor-schedules/:doctorId — upsert full weekly schedule for a doctor
• Body: { schedule: [{ dayOfWeek, startTime, endTime, isOff }] }

​

Permissions

​

Error Handling

• No rows in doctor_schedules for a doctor → fall back to clinic hours (graceful, no error)
• ensureAppointmentsSchema creates the table on first hit (like all other schema-ensure calls)
• Invalid date → 400
• doctorId not in clinic → 403

​

Out of Scope

• Break times / lunch slots (can be added later)
• Multi-location schedules
• Recurring exceptions (holidays)