Design Google Calendar

3 collapsed lines
1
// Headers
2
Authorization: Bearer {access_token}
3
Content-Type: application/json
4

5
// Request body
6
{
7
  "summary": "Weekly Team Standup",
8
  "description": "Discuss blockers and priorities",
9
  "start": {
10
    "dateTime": "2024-01-15T09:00:00",
11
    "timeZone": "America/New_York"
12
  },
13
  "end": {
14
    "dateTime": "2024-01-15T09:30:00",
15
    "timeZone": "America/New_York"
16
  },
17
  "recurrence": ["RRULE:FREQ=WEEKLY;BYDAY=MO,WE,FR"],
18
  "attendees": [
19
    {"email": "alice@example.com"},
20
    {"email": "bob@example.com", "optional": true}
21
  ],
22
  "reminders": {
23
    "useDefault": false,
24
    "overrides": [
25
      {"method": "popup", "minutes": 10},
26
      {"method": "email", "minutes": 60}
27
    ]
28
  },
6 collapsed lines
29
  "conferenceData": {
30
    "createRequest": {"requestId": "unique-request-id"}
31
  },
32
  "visibility": "default",
33
  "transparency": "opaque"
34
}

5 collapsed lines
1
{
2
  "kind": "calendar#event",
3
  "etag": "\"3148476458000000\"",
4
  "id": "abc123xyz",
5
  "status": "confirmed",
6
  "htmlLink": "https://calendar.example.com/event?eid=abc123xyz",
7
  "created": "2024-01-10T15:30:00.000Z",
8
  "updated": "2024-01-10T15:30:00.000Z",
9
  "summary": "Weekly Team Standup",
10
  "description": "Discuss blockers and priorities",
11
  "creator": {
12
    "email": "organizer@example.com",
13
    "self": true
14
  },
15
  "organizer": {
16
    "email": "organizer@example.com",
17
    "self": true
18
  },
19
  "start": {
20
    "dateTime": "2024-01-15T09:00:00-05:00",
21
    "timeZone": "America/New_York"
22
  },
23
  "end": {
24
    "dateTime": "2024-01-15T09:30:00-05:00",
25
    "timeZone": "America/New_York"
26
  },
27
  "recurrence": ["RRULE:FREQ=WEEKLY;BYDAY=MO,WE,FR"],
28
  "iCalUID": "abc123xyz@calendar.example.com",
29
  "sequence": 0,
30
  "attendees": [
31
    { "email": "alice@example.com", "responseStatus": "needsAction" },
32
    { "email": "bob@example.com", "responseStatus": "needsAction", "optional": true }
33
  ],
34
  "reminders": {
15 collapsed lines
35
    "useDefault": false,
36
    "overrides": [
37
      { "method": "popup", "minutes": 10 },
38
      { "method": "email", "minutes": 60 }
39
    ]
40
  },
41
  "conferenceData": {
42
    "conferenceId": "meet123",
43
    "conferenceSolution": {
44
      "name": "Google Meet",
45
      "iconUri": "https://..."
46
    },
47
    "entryPoints": [{ "entryPointType": "video", "uri": "https://meet.example.com/meet123" }]
48
  }
49
}

1
{
2
  "start": {
3
    "dateTime": "2024-01-17T10:00:00",
4
    "timeZone": "America/New_York"
5
  },
6
  "end": {
7
    "dateTime": "2024-01-17T10:30:00",
8
    "timeZone": "America/New_York"
9
  }
10
}

1
{
2
  "timeMin": "2024-01-15T00:00:00Z",
3
  "timeMax": "2024-01-22T00:00:00Z",
4
  "items": [
5
    { "id": "alice@example.com" },
6
    { "id": "bob@example.com" },
7
    { "id": "conference-room-a@resource.example.com" }
8
  ]
9
}

3 collapsed lines
1
{
2
  "kind": "calendar#freeBusy",
3
  "timeMin": "2024-01-15T00:00:00Z",
4
  "timeMax": "2024-01-22T00:00:00Z",
5
  "calendars": {
6
    "alice@example.com": {
7
      "busy": [
8
        { "start": "2024-01-15T14:00:00Z", "end": "2024-01-15T15:00:00Z" },
9
        { "start": "2024-01-16T09:00:00Z", "end": "2024-01-16T10:00:00Z" }
10
      ]
11
    },
12
    "bob@example.com": {
13
      "busy": [{ "start": "2024-01-15T14:00:00Z", "end": "2024-01-15T14:30:00Z" }]
14
    },
15
    "conference-room-a@resource.example.com": {
16
      "busy": [{ "start": "2024-01-15T10:00:00Z", "end": "2024-01-15T11:00:00Z" }],
17
      "errors": []
18
    }
19
  },
20
  "groups": {}
21
}

5 collapsed lines
1
-- Users and calendars (simplified)
2
CREATE TABLE users (
3
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4
    email VARCHAR(255) UNIQUE NOT NULL,
5
    timezone VARCHAR(50) DEFAULT 'UTC',
6
    created_at TIMESTAMPTZ DEFAULT NOW()
7
);
8

9
CREATE TABLE calendars (
10
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
11
    owner_id UUID NOT NULL REFERENCES users(id),
12
    name VARCHAR(255) NOT NULL,
13
    timezone VARCHAR(50) NOT NULL,
14
    sync_token BIGINT DEFAULT 0,
15
    created_at TIMESTAMPTZ DEFAULT NOW()
16
);
17

18
-- Event master table (stores both single and recurring events)
19
CREATE TABLE events (
20
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
21
    calendar_id UUID NOT NULL REFERENCES calendars(id),
22
    ical_uid VARCHAR(255) NOT NULL,  -- RFC 5545 UID for iCal interop
23
    summary VARCHAR(500),
24
    description TEXT,
25
    location VARCHAR(500),
26

27
    -- Time fields stored in local time with timezone
28
    start_datetime TIMESTAMP NOT NULL,
29
    end_datetime TIMESTAMP NOT NULL,
30
    start_timezone VARCHAR(50) NOT NULL,
31
    end_timezone VARCHAR(50) NOT NULL,
32
    is_all_day BOOLEAN DEFAULT FALSE,
33

34
    -- Recurrence (NULL for single events)
35
    recurrence_rule TEXT,  -- RRULE string, e.g., "FREQ=WEEKLY;BYDAY=MO,WE,FR"
36
    recurrence_exceptions TEXT[],  -- EXDATE array
37
    recurrence_additions TEXT[],   -- RDATE array
38

39
    -- Metadata
40
    status VARCHAR(20) DEFAULT 'confirmed',  -- confirmed, tentative, cancelled
41
    visibility VARCHAR(20) DEFAULT 'default',  -- default, public, private
42
    transparency VARCHAR(20) DEFAULT 'opaque',  -- opaque, transparent
43
    sequence INTEGER DEFAULT 0,  -- Increment on updates (iCal SEQUENCE)
44

11 collapsed lines
45
    -- Organizer and creator
46
    organizer_email VARCHAR(255),
47
    creator_email VARCHAR(255),
48

49
    created_at TIMESTAMPTZ DEFAULT NOW(),
50
    updated_at TIMESTAMPTZ DEFAULT NOW(),
51
    deleted_at TIMESTAMPTZ,  -- Soft delete
52

53
    UNIQUE(calendar_id, ical_uid)
54
);
55

56
-- Indexes for common query patterns
57
CREATE INDEX idx_events_calendar_time ON events(calendar_id, start_datetime, end_datetime)
58
    WHERE deleted_at IS NULL;
59
CREATE INDEX idx_events_updated ON events(calendar_id, updated_at)
60
    WHERE deleted_at IS NULL;
61
CREATE INDEX idx_events_recurring ON events(calendar_id)
62
    WHERE recurrence_rule IS NOT NULL AND deleted_at IS NULL;

3 collapsed lines
1
-- Materialized instances for query performance
2
-- Regenerated nightly for rolling 90-day window
3
CREATE TABLE event_instances (
4
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
5
    event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
6
    calendar_id UUID NOT NULL REFERENCES calendars(id),
7

8
    -- Instance timing (UTC for efficient range queries)
9
    instance_start_utc TIMESTAMPTZ NOT NULL,
10
    instance_end_utc TIMESTAMPTZ NOT NULL,
11

12
    -- Original occurrence date (for exception matching)
13
    original_start_utc TIMESTAMPTZ NOT NULL,
14

15
    -- Instance-specific overrides (NULL = inherit from master)
16
    summary_override VARCHAR(500),
17
    description_override TEXT,
18
    location_override VARCHAR(500),
19
    start_override TIMESTAMP,
20
    end_override TIMESTAMP,
21
    timezone_override VARCHAR(50),
22

23
    -- Exception status
24
    status VARCHAR(20) NOT NULL DEFAULT 'confirmed',  -- confirmed, cancelled
25
    is_exception BOOLEAN DEFAULT FALSE,
26

27
    created_at TIMESTAMPTZ DEFAULT NOW()
28
);
29

6 collapsed lines
30
-- Primary query index: calendar + date range
31
CREATE INDEX idx_instances_calendar_range
32
    ON event_instances(calendar_id, instance_start_utc, instance_end_utc)
33
    WHERE status != 'cancelled';
34

35
-- Free/busy aggregation index
36
CREATE INDEX idx_instances_freebusy
37
    ON event_instances(calendar_id, instance_start_utc, instance_end_utc)
38
    WHERE status = 'confirmed';
39

40
-- Exception lookup (find if this occurrence has been modified)
41
CREATE INDEX idx_instances_exception
42
    ON event_instances(event_id, original_start_utc)
43
    WHERE is_exception = TRUE;

3 collapsed lines
1
-- Attendees for meetings
2
CREATE TABLE event_attendees (
3
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
4
    event_id UUID NOT NULL REFERENCES events(id) ON DELETE CASCADE,
5
    email VARCHAR(255) NOT NULL,
6
    display_name VARCHAR(255),
7

8
    -- Response status (RFC 5545 PARTSTAT)
9
    response_status VARCHAR(20) DEFAULT 'needsAction',
10
        -- needsAction, declined, tentative, accepted
11

12
    -- Role
13
    is_organizer BOOLEAN DEFAULT FALSE,
14
    is_optional BOOLEAN DEFAULT FALSE,
15
    is_resource BOOLEAN DEFAULT FALSE,  -- Conference room, equipment
16

17
    -- Response metadata
18
    response_comment TEXT,
19
    responded_at TIMESTAMPTZ,
20

21
    UNIQUE(event_id, email)
22
);
23

24
CREATE INDEX idx_attendees_email ON event_attendees(email, event_id);
1 collapsed line
25
CREATE INDEX idx_attendees_event ON event_attendees(event_id);

10 collapsed lines
1
// Using a library like rrule.js or python-dateutil for parsing
2
import { RRule, RRuleSet, rrulestr } from "rrule"
3

4
interface RecurrenceExpansionRequest {
5
  rruleString: string // e.g., "FREQ=WEEKLY;BYDAY=MO,WE,FR"
6
  dtstart: Date // Series start in local time
7
  timezone: string // IANA timezone
8
  rangeStart: Date // Query range start (UTC)
9
  rangeEnd: Date // Query range end (UTC)
10
  exdates?: Date[] // Excluded dates
11
  rdates?: Date[] // Additional dates
12
}
13

14
function expandRecurrence(req: RecurrenceExpansionRequest): Date[] {
15
  // Parse the RRULE with timezone awareness
16
  const rule = RRule.fromString(req.rruleString)
17

18
  const rruleSet = new RRuleSet()
19
  rruleSet.rrule(rule)
20

21
  // Add exclusions (EXDATE)
22
  for (const exdate of req.exdates ?? []) {
23
    rruleSet.exdate(exdate)
24
  }
25

26
  // Add additional dates (RDATE)
27
  for (const rdate of req.rdates ?? []) {
28
    rruleSet.rdate(rdate)
29
  }
30

31
  // Expand within range
32
  // CRITICAL: between() uses the RRULE's timezone for DST handling
33
  const instances = rruleSet.between(req.rangeStart, req.rangeEnd, true)
34

35
  return instances
36
}
37

38
// Example: Weekly standup at 9 AM, Mon/Wed/Fri
39
const instances = expandRecurrence({
40
  rruleString: "FREQ=WEEKLY;BYDAY=MO,WE,FR",
41
  dtstart: new Date("2024-01-15T09:00:00"),
42
  timezone: "America/New_York",
43
  rangeStart: new Date("2024-01-01T00:00:00Z"),
44
  rangeEnd: new Date("2024-03-31T23:59:59Z"),
3 collapsed lines
45
  exdates: [new Date("2024-01-17T09:00:00")], // Skip Jan 17
46
})
47
// Returns: [Jan 15, Jan 19, Jan 22, Jan 24, Jan 26, ...]

5 collapsed lines
1
// Handling non-existent times during spring forward
2
function adjustForDST(localTime: Date, timezone: string): Date {
3
  const { DateTime } = require("luxon")
4

5
  const dt = DateTime.fromJSDate(localTime, { zone: timezone })
6

7
  if (!dt.isValid && dt.invalidReason === "time zone offset transition") {
8
    // Time doesn't exist—shift forward to the next valid time
9
    return dt.plus({ hours: 1 }).toJSDate()
10
  }
11

12
  return localTime
13
}

8 collapsed lines
1
import { Redis } from "ioredis"
2

3
interface BusyInterval {
4
  start: number // Unix timestamp
5
  end: number
6
  eventId?: string // Only for the calendar owner
7
}
8

9
// Store busy intervals as sorted set members
10
// Key: freebusy:{calendarId}
11
// Score: start timestamp
12
// Member: JSON { start, end, eventId }
13

14
async function updateFreeBusy(redis: Redis, calendarId: string, instances: EventInstance[]): Promise<void> {
15
  const key = `freebusy:${calendarId}`
16
  const pipeline = redis.pipeline()
17

18
  // Clear existing entries in the affected range
19
  const rangeStart = Math.min(...instances.map((i) => i.startUtc.getTime() / 1000))
20
  const rangeEnd = Math.max(...instances.map((i) => i.endUtc.getTime() / 1000))
21
  pipeline.zremrangebyscore(key, rangeStart, rangeEnd)
22

23
  // Add new busy intervals
24
  for (const instance of instances) {
25
    if (instance.status === "confirmed" && instance.transparency === "opaque") {
26
      const interval: BusyInterval = {
27
        start: instance.startUtc.getTime() / 1000,
28
        end: instance.endUtc.getTime() / 1000,
29
        eventId: instance.eventId,
30
      }
31
      pipeline.zadd(key, interval.start, JSON.stringify(interval))
32
    }
33
  }
34

35
  // Set TTL to 7 days (refresh weekly)
36
  pipeline.expire(key, 7 * 24 * 60 * 60)
37

38
  await pipeline.exec()
39
}
11 collapsed lines
40

41
async function queryFreeBusy(
42
  redis: Redis,
43
  calendarId: string,
44
  rangeStart: Date,
45
  rangeEnd: Date,
46
): Promise<BusyInterval[]> {
47
  const key = `freebusy:${calendarId}`
48
  const start = rangeStart.getTime() / 1000
49
  const end = rangeEnd.getTime() / 1000
50

51
  // Get all intervals that START within the range
52
  const members = await redis.zrangebyscore(key, start, end)
53

54
  return members.map((m) => JSON.parse(m) as BusyInterval).filter((interval) => interval.end > start) // Exclude ended before range
55
}

5 collapsed lines
1
interface TimeSlot {
2
  start: Date
3
  end: Date
4
}
5

6
function findAvailableSlots(
7
  busyIntervalsByAttendee: Map<string, BusyInterval[]>,
8
  rangeStart: Date,
9
  rangeEnd: Date,
10
  duration: number, // minutes
11
  workingHours?: { start: number; end: number }, // e.g., { start: 9, end: 17 }
12
): TimeSlot[] {
13
  // Merge all busy intervals
14
  const allBusy: BusyInterval[] = []
15
  for (const intervals of busyIntervalsByAttendee.values()) {
16
    allBusy.push(...intervals)
17
  }
18

19
  // Sort by start time
20
  allBusy.sort((a, b) => a.start - b.start)
21

22
  // Merge overlapping intervals
23
  const merged: BusyInterval[] = []
24
  for (const interval of allBusy) {
25
    if (merged.length === 0 || merged[merged.length - 1].end < interval.start) {
26
      merged.push({ ...interval })
27
    } else {
28
      merged[merged.length - 1].end = Math.max(merged[merged.length - 1].end, interval.end)
29
    }
30
  }
31

32
  // Find gaps that fit the duration
33
  const durationSec = duration * 60
34
  const available: TimeSlot[] = []
35
  let cursor = rangeStart.getTime() / 1000
36

37
  for (const busy of merged) {
38
    if (busy.start - cursor >= durationSec) {
39
      available.push({
40
        start: new Date(cursor * 1000),
41
        end: new Date(busy.start * 1000),
42
      })
43
    }
44
    cursor = Math.max(cursor, busy.end)
45
  }
46

47
  // Check final gap
48
  const endSec = rangeEnd.getTime() / 1000
49
  if (endSec - cursor >= durationSec) {
11 collapsed lines
50
    available.push({
51
      start: new Date(cursor * 1000),
52
      end: rangeEnd,
53
    })
54
  }
55

56
  // Filter by working hours if specified
57
  if (workingHours) {
58
    return available.filter((slot) => {
59
      const startHour = slot.start.getHours()
60
      return startHour >= workingHours.start && startHour < workingHours.end
61
    })
62
  }
63

64
  return available
65
}

5 collapsed lines
1
-- Track changes for sync
2
CREATE TABLE calendar_changes (
3
    id BIGSERIAL PRIMARY KEY,
4
    calendar_id UUID NOT NULL REFERENCES calendars(id),
5
    event_id UUID NOT NULL,
6
    change_type VARCHAR(10) NOT NULL,  -- 'created', 'updated', 'deleted'
7
    changed_at TIMESTAMPTZ DEFAULT NOW(),
8
    sync_token BIGINT NOT NULL  -- Matches calendars.sync_token at time of change
9
);
10

11
CREATE INDEX idx_changes_sync ON calendar_changes(calendar_id, sync_token);
12

13
-- On event change, record it
14
CREATE OR REPLACE FUNCTION record_event_change()
15
RETURNS TRIGGER AS $$
16
BEGIN
17
  -- Increment calendar's sync token
18
  UPDATE calendars SET sync_token = sync_token + 1 WHERE id = NEW.calendar_id;
19

20
  -- Record the change
21
  INSERT INTO calendar_changes (calendar_id, event_id, change_type, sync_token)
22
  SELECT NEW.calendar_id, NEW.id, TG_OP, sync_token FROM calendars WHERE id = NEW.calendar_id;
23

24
  RETURN NEW;
25
END;
26
$$ LANGUAGE plpgsql;

1
Content-Type: multipart/alternative; boundary="boundary"
2

3
--boundary
4
Content-Type: text/plain
5

6
You've been invited to: Weekly Team Standup
7
When: Monday, January 15, 2024 9:00 AM - 9:30 AM (EST)
8

9
--boundary
10
Content-Type: text/calendar; method=REQUEST
11

12
BEGIN:VCALENDAR
13
VERSION:2.0
14
METHOD:REQUEST
15
BEGIN:VEVENT
16
UID:abc123xyz@calendar.example.com
17
DTSTART;TZID=America/New_York:20240115T090000
18
DTEND;TZID=America/New_York:20240115T093000
19
SUMMARY:Weekly Team Standup
20
ORGANIZER:mailto:organizer@example.com
21
ATTENDEE;PARTSTAT=NEEDS-ACTION:mailto:attendee@example.com
22
END:VEVENT
23
END:VCALENDAR
24

25
--boundary--

10 collapsed lines
1
// Load events in batches as user scrolls
2
interface CalendarViewState {
3
  visibleRange: { start: Date; end: Date }
4
  loadedRanges: Array<{ start: Date; end: Date }>
5
  events: Map<string, CalendarEvent>
6
}
7

8
function useCalendarEvents(calendarId: string) {
9
  const [state, setState] = useState<CalendarViewState>({
10
    visibleRange: getCurrentWeek(),
11
    loadedRanges: [],
12
    events: new Map(),
13
  })
14

15
  // Load events for visible range + buffer
16
  useEffect(() => {
17
    const rangeToLoad = expandRange(state.visibleRange, { days: 7 }) // ±1 week buffer
18

19
    if (!isRangeCovered(rangeToLoad, state.loadedRanges)) {
20
      fetchEvents(calendarId, rangeToLoad).then((newEvents) => {
21
        setState((prev) => ({
22
          ...prev,
23
          loadedRanges: mergeRanges([...prev.loadedRanges, rangeToLoad]),
24
          events: new Map([...prev.events, ...newEvents.map((e) => [e.id, e])]),
25
        }))
26
      })
27
    }
28
  }, [state.visibleRange, calendarId])
29

30
  return state.events
31
}

5 collapsed lines
1
// Real-time sync via WebSocket
2
const useCalendarSync = (calendarId: string) => {
3
  const queryClient = useQueryClient()
4

5
  useEffect(() => {
6
    const ws = new WebSocket(`wss://api.calendar.com/sync/${calendarId}`)
7

8
    ws.onmessage = (event) => {
9
      const change = JSON.parse(event.data)
10

11
      switch (change.type) {
12
        case "event.created":
13
        case "event.updated":
14
          queryClient.setQueryData(["events", calendarId], (old: CalendarEvent[]) => upsertEvent(old, change.event))
15
          break
16
        case "event.deleted":
17
          queryClient.setQueryData(["events", calendarId], (old: CalendarEvent[]) =>
18
            old.filter((e) => e.id !== change.eventId),
19
          )
20
          break
21
      }
22
    }
23

24
    return () => ws.close()
2 collapsed lines
25
  }, [calendarId, queryClient])
26
}

5 collapsed lines
1
// Convert and display event times
2
function formatEventTime(event: CalendarEvent, userTimezone: string): string {
3
  const { DateTime } = require("luxon")
4

5
  if (event.isAllDay) {
6
    // All-day events: show date only, no timezone conversion
7
    return DateTime.fromISO(event.start.date).toLocaleString(DateTime.DATE_MED)
8
  }
9

10
  // Timed events: convert to user's timezone
11
  const start = DateTime.fromISO(event.start.dateTime, { zone: event.start.timeZone })
12
  const userStart = start.setZone(userTimezone)
13

14
  // Show original timezone if different
15
  if (event.start.timeZone !== userTimezone) {
16
    return `${userStart.toLocaleString(DateTime.TIME_SIMPLE)} (${userStart.toFormat("ZZZZ")})`
17
  }
18

19
  return userStart.toLocaleString(DateTime.TIME_SIMPLE)
20
}

5 collapsed lines
1
// Drag event to new time slot
2
async function handleEventDrop(eventId: string, newStart: Date, newEnd: Date) {
3
  const previousEvent = queryClient.getQueryData(["event", eventId])
4

5
  // Optimistic update
6
  queryClient.setQueryData(["event", eventId], (old: CalendarEvent) => ({
7
    ...old,
8
    start: { dateTime: newStart.toISOString(), timeZone: old.start.timeZone },
9
    end: { dateTime: newEnd.toISOString(), timeZone: old.end.timeZone },
10
  }))
11

12
  try {
13
    await updateEvent(eventId, { start: newStart, end: newEnd })
14
  } catch (error) {
15
    // Rollback on failure
16
    queryClient.setQueryData(["event", eventId], previousEvent)
17
    toast.error("Failed to reschedule event")
18
  }
19
}
20

21
// For recurring event instance: prompt user for scope
22
function handleRecurringEventDrop(eventId: string, instanceDate: Date, newTime: Date) {
23
  showDialog({
24
    title: "Edit recurring event",
25
    options: [
26
      { label: "This event only", action: () => updateInstance(eventId, instanceDate, newTime) },
27
      { label: "This and future events", action: () => splitSeries(eventId, instanceDate, newTime) },
28
      { label: "All events", action: () => updateSeries(eventId, newTime) },
29
    ],
2 collapsed lines
30
  })
31
}