Core Entities
User: user_id, email, timezone, working_hours (JSON: {mon: “9:00-17:00”, …}). Calendar: calendar_id, user_id, name, color, is_primary, visibility (PRIVATE, PUBLIC, SHARED). Event: event_id, calendar_id, creator_id, title, description, start_time (UTC), end_time (UTC), location, status (CONFIRMED, TENTATIVE, CANCELLED), visibility, recurrence_rule (iCal RRULE string, nullable), recurrence_id (for instances of recurring events), original_start (for modified instances). Attendee: attendee_id, event_id, user_id, email, status (NEEDS_ACTION, ACCEPTED, DECLINED, TENTATIVE), is_organizer. CalendarShare: share_id, calendar_id, shared_with_user_id, permission (VIEW, EDIT).
Recurring Events
Recurring events are stored as a single master event with an RRULE (RFC 5545). Example: RRULE:FREQ=WEEKLY;BYDAY=MO,WE,FR;COUNT=10 (weekly on Mon/Wed/Fri for 10 occurrences). Expansion: generate individual occurrences on-the-fly for display. Do NOT store each occurrence as a separate database row — 10 years of daily events = 3,650 rows per recurring event. Expansion algorithm: given an RRULE and a query date range [start, end], expand only the occurrences falling in that range. Libraries: rrule.js (JavaScript), python-dateutil (Python). Exception handling: a user edits “this occurrence only” — create an exception row (EventException) linked to the master event with the original_start and the modified fields. When expanding recurrence: skip original_start values that have exceptions, and return the exception row instead. “Edit all following” creates a new master event from that date forward and truncates the original RRULE.
Conflict Detection
Detect overlapping events for a user in a calendar. Two events conflict if: event1.start event2.start. Database query for conflicts:
SELECT e.* FROM events e
JOIN attendees a ON e.event_id = a.event_id
WHERE a.user_id = :user_id
AND e.status != 'CANCELLED'
AND e.start_time :new_event_startIndex: (user_id, start_time, end_time) on the attendees-events join. For recurring events: expand the recurrence for the query range first, then check for conflicts. Soft conflict: show a warning but allow double-booking (meetings from different calendars, personal vs. work events). Hard conflict: block the booking (conference room booking — a room cannot be double-booked).
Free/Busy and Scheduling
The free/busy API: given a list of user_ids and a time range, return the busy slots for each user. Used by “Find a meeting time” features. Implementation: for each user, fetch all events in the range (including expanded recurring occurrences). Merge overlapping intervals. Return free slots as the inverse of busy intervals within working hours. Algorithm for merging overlapping intervals: sort by start time, iterate through — if current.start = requested duration with no busy intervals. For Google Calendar’s “Meet with” feature: this query runs server-side over all attendees’ calendars.
Timezone Handling
All events are stored in UTC. Display is converted to the user’s timezone at read time. Never store in local time (DST transitions cause bugs). Recurring event expansion: expand in the user’s local timezone — “every Monday at 9am” means 9am local time, which shifts in UTC when DST transitions. A recurring event defined as 9am EST (UTC-5) will have occurrences at UTC 14:00 in winter and UTC 13:00 in summer. The rrule library handles this correctly when initialized with the user’s timezone. All-day events: store as a date (not datetime) with no timezone. Display the same date in all timezones. Floating events: “no timezone” — always display at the stored time regardless of the viewer’s timezone (used for travel itineraries, sleep schedules).
Asked at: Atlassian Interview Guide
Asked at: LinkedIn Interview Guide
Asked at: Apple Interview Guide
Asked at: Airbnb Interview Guide