Calendar View Logic

Overview

The calendar system provides month and week views of appointments with flexible filtering for specialists, patients, and admins.

API Endpoints

GET /v1/calendar

POST /v1/calendar

Both endpoints support the same parameters. POST is preferred for complex filter combinations.

Request Parameters:

{
  "view": "month",                                    // "month" | "week"
  "start_date": "2025-01-01",                        // ISO date
  "end_date": "2025-01-31",                          // ISO date
  "specialist_id": 2,                                // Optional: filter by specialist
  "status": ["upcoming", "confirmed", "done"]        // Optional: filter by status
}

---

Month View

Returns aggregate appointment counts per day.

Response:

{
  "data": {
    "view": "month",
    "start_date": "2025-01-01",
    "end_date": "2025-01-31",
    "days": [
      { "date": "2025-01-15", "count": 3 },
      { "date": "2025-01-16", "count": 1 },
      { "date": "2025-01-20", "count": 5 }
    ]
  }
}

SQL Query (simplified):

SELECT
    DATE(started_at) as date,
    COUNT(*) as count
FROM appointments
WHERE organization_id = ?
  AND started_at >= ?
  AND started_at < ?
  AND (? IS NULL OR specialist_id = ?)
  AND (? IS NULL OR status = ANY(?))
  AND deleted_at IS NULL
GROUP BY DATE(started_at)
ORDER BY date;

Use case: Calendar overview for quickly seeing busy days.

---

Week View

Returns full appointment details grouped by day.

Response:

{
  "data": {
    "view": "week",
    "start_date": "2025-01-20",
    "end_date": "2025-01-26",
    "days": [
      {
        "date": "2025-01-20",
        "appointments": [
          {
            "id": 102,
            "uid": "550e8400-e29b-41d4-a716-446655440000",
            "title": "Initial Consultation",
            "started_at": "2025-01-20T10:00:00Z",
            "ended_at": "2025-01-20T10:30:00Z",
            "status": "upcoming",
            "specialist": {
              "id": 2,
              "name": "Dr. Smith",
              "avatar_url": "https://s3.../avatar.jpg"
            },
            "person": {
              "id": 81,
              "name": "John Doe",
              "username": "[email protected]"
            }
          },
          {
            "id": 103,
            "title": "Follow-up",
            "started_at": "2025-01-20T14:00:00Z",
            "ended_at": "2025-01-20T14:30:00Z",
            "status": "confirmed",
            "specialist": { "id": 2, "name": "Dr. Smith" },
            "person": { "id": 82, "name": "Jane Smith", "username": "[email protected]" }
          }
        ]
      },
      {
        "date": "2025-01-21",
        "appointments": []
      }
    ]
  }
}

SQL Query (simplified):

SELECT
    a.id,
    a.uid,
    a.title,
    a.started_at,
    a.ended_at,
    a.status,
    s.id as specialist_id,
    s.name as specialist_name,
    s.avatar_url,
    pp.id as patient_person_id,
    pp.name as patient_name,
    u.username as patient_username
FROM appointments a
LEFT JOIN specialists s ON a.specialist_id = s.id
LEFT JOIN patient_persons pp ON a.patient_person_id = pp.id
LEFT JOIN users u ON pp.user_id = u.id
WHERE a.organization_id = ?
  AND a.started_at >= ?
  AND a.started_at < ?
  AND (? IS NULL OR a.specialist_id = ?)
  AND (? IS NULL OR a.status = ANY(?))
  AND a.deleted_at IS NULL
ORDER BY a.started_at;

Use case: Detailed day-by-day schedule view for specialists and admins.

---

Filtering

By Specialist

Query param: specialist_id=2

• Returns only appointments assigned to the specified specialist
• Used by specialist's personal calendar view
• Admin can filter any specialist

RLS: Patients cannot filter by specialist (they only see their own appointments).

---

By Status

Query param: status=upcoming,confirmed

Accepts comma-separated list or array:

{
  "status": ["upcoming", "confirmed", "inprogress"]
}

Common filter combinations:

Filter	Use Case
upcoming,confirmed,inprogress	Active appointments
done	Completed appointments (history)
cancelled,noshow	Cancelled/missed appointments
upcoming,confirmed	Upcoming scheduled appointments

---

By Date Range

Query params: start_date, end_date

• Always inclusive: started_at >= start_date AND started_at < end_date
• Frontend typically calculates:
  • Month view: First day of month → first day of next month
  • Week view: Monday → Monday (next week)

Example:

{
  "view": "week",
  "start_date": "2025-01-20",    // Monday
  "end_date": "2025-01-27"       // Next Monday (exclusive)
}

---

RLS Scoping

Calendar queries automatically respect Row-Level Security:

Role	Scope
Patient	Only own appointments (via patient_person_id IN (SELECT current_user_patient_person_ids()) — covers managed dependents too)
Specialist	Own assigned appointments (via specialist_id IN (SELECT id FROM specialists WHERE user_id = ...))
Admin/Support	All appointments in organization
Superadmin	All appointments across all organizations

No explicit filtering needed in query — RLS policies apply automatically.

---

Timezone Handling

All times are UTC in database and API responses.

Frontend converts to user's local timezone for display:

// Example: Frontend converts UTC to local time
const appointment = {
  started_at: "2025-01-20T10:00:00Z"  // UTC from API
};

// Display in user's local timezone
const localTime = new Date(appointment.started_at).toLocaleString('ro-RO', {
  timeZone: 'Europe/Bucharest',
  hour: '2-digit',
  minute: '2-digit'
});
// Result: "12:00" (for Bucharest, UTC+2)

Specialist timezone (scheduling_timezone on specialists table) is used only for:

• Availability calculation (timeslot generation)
• Displaying specialist's working hours

Appointments table has no timezone column — all TIMESTAMPTZ values are UTC.

---

Performance Optimization

Composite Index

The idx_appointments_calendar index optimizes calendar queries:

CREATE INDEX idx_appointments_calendar
ON appointments(organization_id, specialist_id, started_at, status);

Why this index?

1. organization_id — RLS filter (always present)
2. specialist_id — Common filter for specialist views
3. started_at — Date range filter
4. status — Status filter

Query planner can use this index for:

• WHERE organization_id = ? AND specialist_id = ? AND started_at >= ? AND started_at < ?
• WHERE organization_id = ? AND started_at >= ? AND started_at < ? AND status IN (...)

Partial Index for Active Appointments

CREATE INDEX idx_appointments_active
ON appointments(organization_id)
WHERE deleted_at IS NULL;

Filters out soft-deleted appointments automatically.

---

Implementation Example (Go)

// internal/handlers/calendar.go

type CalendarRequest struct {
    View        string    `json:"view"`         // "month" | "week"
    StartDate   time.Time `json:"start_date"`
    EndDate     time.Time `json:"end_date"`
    SpecialistID *int64   `json:"specialist_id,omitempty"`
    Status      []string  `json:"status,omitempty"`
}

type MonthViewResponse struct {
    View      string      `json:"view"`
    StartDate time.Time   `json:"start_date"`
    EndDate   time.Time   `json:"end_date"`
    Days      []DayCount  `json:"days"`
}

type DayCount struct {
    Date  string `json:"date"`  // ISO date: "2025-01-20"
    Count int    `json:"count"`
}

type WeekViewResponse struct {
    View      string        `json:"view"`
    StartDate time.Time     `json:"start_date"`
    EndDate   time.Time     `json:"end_date"`
    Days      []DayWithAppts `json:"days"`
}

type DayWithAppts struct {
    Date         string        `json:"date"`
    Appointments []Appointment `json:"appointments"`
}

func (h *CalendarHandler) GetCalendar(c *gin.Context) {
    var req CalendarRequest
    if err := c.ShouldBindJSON(&req); err != nil {
        c.JSON(400, gin.H{"error": err.Error()})
        return
    }

    // Validate view
    if req.View != "month" && req.View != "week" {
        c.JSON(400, gin.H{"error": "view must be 'month' or 'week'"})
        return
    }

    // RLS is automatic via session variables
    ctx := c.Request.Context()

    if req.View == "month" {
        days, err := h.service.GetMonthView(ctx, req)
        if err != nil {
            c.JSON(500, gin.H{"error": err.Error()})
            return
        }
        c.JSON(200, MonthViewResponse{
            View:      "month",
            StartDate: req.StartDate,
            EndDate:   req.EndDate,
            Days:      days,
        })
    } else {
        days, err := h.service.GetWeekView(ctx, req)
        if err != nil {
            c.JSON(500, gin.H{"error": err.Error()})
            return
        }
        c.JSON(200, WeekViewResponse{
            View:      "week",
            StartDate: req.StartDate,
            EndDate:   req.EndDate,
            Days:      days,
        })
    }
}

---

Frontend Integration

Month View Example

// Calendar month view component
async function loadMonthView(year: number, month: number) {
  const startDate = new Date(year, month, 1);
  const endDate = new Date(year, month + 1, 1);

  const response = await fetch('/v1/calendar', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      view: 'month',
      start_date: startDate.toISOString().split('T')[0],
      end_date: endDate.toISOString().split('T')[0],
      status: ['upcoming', 'confirmed', 'inprogress']
    })
  });

  const data = await response.json();

  // Map counts to calendar grid
  const countsByDate = new Map(
    data.days.map(d => [d.date, d.count])
  );

  // Render calendar with counts
  renderCalendarGrid(year, month, countsByDate);
}

Week View Example

// Calendar week view component
async function loadWeekView(weekStart: Date) {
  const weekEnd = new Date(weekStart);
  weekEnd.setDate(weekEnd.getDate() + 7);

  const response = await fetch('/v1/calendar', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify({
      view: 'week',
      start_date: weekStart.toISOString().split('T')[0],
      end_date: weekEnd.toISOString().split('T')[0],
      specialist_id: selectedSpecialistId,
      status: ['upcoming', 'confirmed', 'inprogress', 'done']
    })
  });

  const data = await response.json();

  // Render week grid with appointment cards
  renderWeekGrid(data.days);
}

---

Specialist vs Patient Views

Specialist View

Use case: Specialist sees their own schedule

Query:

{
  "view": "week",
  "start_date": "2025-01-20",
  "end_date": "2025-01-27",
  "specialist_id": 2,
  "status": ["upcoming", "confirmed", "inprogress"]
}

Features:

• Shows all appointments assigned to specialist
• Displays patient names and contact info
• Shows appointment status and forms progress

---

Patient View

Use case: Patient sees their own appointments

Query:

{
  "view": "month",
  "start_date": "2025-01-01",
  "end_date": "2025-02-01",
  "status": ["upcoming", "confirmed", "inprogress", "done"]
}

RLS automatically filters: Only appointments where patient_person_id IN (SELECT current_user_patient_person_ids()) — includes appointments for managed dependents (e.g. a parent viewing a child's appointments)

Features:

• Shows only patient's own appointments
• Displays specialist name and specialty
• Shows appointment status and forms to complete

---

Admin View

Use case: Admin sees all appointments in organization

Query:

{
  "view": "week",
  "start_date": "2025-01-20",
  "end_date": "2025-01-27"
  // No specialist_id filter — see all
}

Features:

• Multi-specialist calendar grid
• Filter by specialist dropdown
• Overlapping appointment warnings
• Bulk status management

---

Empty Days Handling

Week view always returns 7 days, even if some have no appointments:

{
  "days": [
    { "date": "2025-01-20", "appointments": [...]},
    { "date": "2025-01-21", "appointments": []},     // Empty day
    { "date": "2025-01-22", "appointments": [...]},
    { "date": "2025-01-23", "appointments": []},     // Empty day
    { "date": "2025-01-24", "appointments": [...]},
    { "date": "2025-01-25", "appointments": []},     // Empty day
    { "date": "2025-01-26", "appointments": []}      // Empty day
  ]
}

Month view only returns days with appointments:

{
  "days": [
    { "date": "2025-01-15", "count": 3 },
    { "date": "2025-01-20", "count": 5 }
    // Days with 0 appointments are omitted
  ]
}

Frontend fills missing days with 0 when rendering calendar grid.

---

Related Documentation

• API Endpoints - Full API contracts
• Lifecycle - Appointment status definitions
• Schema - Database indexes and RLS policies