Design Google Calendar — recurring events with per-instance exceptions, IANA time zones that survive daylight saving, invitations that fan out to other people's calendars, and "find a time" across a room of attendees. One row can mean infinite meetings, and a single clock change can silently move a million stand-ups. The decision that separates a senior answer: store the rule, derive the instant. A full working-through — data flow, the schema, the local-time invariant, DST-correct expansion in Python, the free/busy bitmap, the change-stream pipeline, and the dashboard that proves it.
A calendar looks like the easiest app you will ever build: a list of events with a start and an end, sorted by time. It is, in fact, one of the quietest correctness traps in consumer software — and the question reveals fast whether someone has shipped against time, or only read about it.
"Design the data model behind Google Calendar — recurring events with per-instance exceptions, IANA time zones with daylight-saving correctness, invitations that accept/decline/tentative, and 'find a time' free/busy across many attendees. And it has to answer point-in-time: what did my Tuesday look like as Calendar showed it last Monday? How would you scope it?"
Two facts make this hard, and both are facts about data, not about the web tier. First, a recurring event is one row that denotes an unbounded number of occurrences — "every weekday, forever" is a single record and an infinite series. Second, the correct time of an event is a moving target: a 9:00 AM stand-up has to stay 9:00 AM after the clocks change, which means you cannot freeze it to a UTC instant and walk away. Layer invitations and "find a time across ten people" on top, and you have a read-heavy, latency-bound system where the bugs are measured in missed meetings. The weak answer draws a load balancer and three app servers. The strong answer names the forces that will deform the design first, because every later decision exists to survive one of them.
There are exactly four forces, and they are not the usual suspects. So before any boxes and arrows, the working frame for the whole session:
Scope is the first scored dimension, and most candidates skip it. In scope: event CRUD, recurrence with per-instance exceptions, invitations and RSVP, and free/busy. Out of scope, said explicitly: email-delivery internals, video conferencing, working-hours and appointment-slot features, and sharing ACLs — modeled-for, not designed in detail. One tension I state up front: this is read-heavy with a hard correctness requirement around time. A stale calendar view for a few seconds is fine, so availability beats strict consistency for reads — but RSVPs need read-your-writes, and free/busy must not double-book in obvious ways. Time gets stored three ways at once, which I will defend with the DDL.
Then the envelope math, volunteered rather than extracted. Google-class numbers:
| Quantity | Estimate | Consequence |
|---|---|---|
| Monthly / daily active users | ~500M / ~180M | Sets the read fan-out; everyone opens "this week" at once |
| Read : write ratio | ~10 : 1 | Make the common read nearly free; pay costs on the rare write |
| Occurrences per series row | 1 row → ∞ | The materialize-vs-expand call that shapes the whole architecture |
| Event writes / day | 50–80M | ~1k writes/s average, spiking to 5–10k/s, business-hours-spiky |
| Peak reads / s | 100k+ | Sync clients + calendar opens; a latency problem, not a volume one |
| Free/busy slot | 15 min · 96 bits/day | 12 bytes/calendar-day; ten people for two weeks < 2 KB |
| Storage over years | single-digit PB | Not storage-bound. Correctness and read latency are the game |
Notice the row unlike the others: a single physical row denotes an unbounded number of occurrences. That is not a detail — it dictates the storage layout, the write cost, and the entire free/busy design. The rest of this piece is a consequence of resolving it deliberately: store the rule, expand for the visible window only, and derive UTC per occurrence. Everything else follows the data.
One source of truth, four projections off one ordered log. The event store owns truth; attendee copies, the free/busy bitmap, the rendered-window cache, and client sync are all derived consumers of a single per-calendar change stream — fed by a transactional outbox so the stream can never miss a committed change or replay one that rolled back.
Three properties of this picture carry the interview. First, the write stores the rule, not the occurrences — editing a series is one row touched, not thousands rewritten, so writes stay O(1) no matter how long the series runs. Second, the event row and its changelog row commit in one transaction, so a CDC connector turns committed rows into exactly one ordered stream message — no dual-write window where the bitmaps and attendee copies silently disagree with truth. Third, everything below the dashed line is derived: copies, bitmaps, the rendered-window cache, the client sync feed. Drop the whole projection layer and you have lost only cache — the event store survives, and every projection rebuilds by replaying the log.
The store is truth; everything else degrades to stale, never to wrong. Because copies, bitmaps, and caches are versioned projections off one ordered log, every failure has the same shape: a consumer falls behind and a guest sees an invitation a few seconds late, or free/busy lags a write — both acceptable, because availability is advisory. A write degrades in exactly one direction: it either commits the rule plus its outbox row atomically, or it commits nothing. There is no half-fanned-out event and no orphaned bitmap, because the projections never hold authority — they hold a replayable copy of it.
The entity skeleton is unremarkable: users, calendars, events, and an attendee join table. Every interesting decision is inside events — where singles, recurring masters, and per-instance overrides all live in one table, disambiguated by which columns are populated, and time is stored three ways on purpose.
A user owns several calendars; the calendar is the unit of ownership and of sharing. The kind column lets rooms and resources reuse the exact same machinery as people, and the version counter — bumped on any write — becomes the cache key that makes invalidation a non-event.
The single most consequential choice: a row with an rrule and no parent is a master; a row with a parent_event_id and a recurrence_id is an override of one occurrence; a plain row is a one-off. One table keeps the read path to two queries and lets an override carry its own attendees for free — which is what lets someone decline a single moved instance while staying accepted on the series. And time is stored three ways: *_utc for range scans and one-off correctness, *_local as bare wall-clock, and tz_id as the IANA zone from which UTC for a recurrence is derived.
An offset like -08:00 is a fact about an instant, not a place. Store it and you have thrown away the rule that produced it, so you cannot re-derive the right wall-clock after a DST transition or a government zone change. The IANA zone id (America/Los_Angeles) is a pointer into the tz database, which does know the rules. So start_local + tz_id is the source of truth for anything that recurs, start_utc is a derived convenience for range scans and one-offs, and the two are kept consistent by the app, not by the user.
Attendees get their own table because RSVP state is per-person and, crucially, because an override row can have its own attendee set. External guests live by email and have a NULL attendee_id; the inbox index answers "what am I invited to, and have I replied?" in one seek.
That is the whole model the system rides on: two small tables, one carefully overloaded big one, and a join table that quietly carries the per-instance RSVP magic. The analytics warehouse mirrors this as a star — dim_events (SCD2, the recurrence master whose title and organizer drift over time), dim_calendars, dim_users, a dim_timezone that is the DST source of truth, and the occurrence, exception, invitation, and free/busy facts we interrogate in §07. The operational row shape and the dimensional grain are the same idea told twice.
Every correctness guarantee in this system collapses to one ordering: a recurrence is iterated in local wall-clock time, and the UTC instant is computed per occurrence afterward. Get that ordering backward — freeze each occurrence to UTC up front — and the next daylight-saving transition silently slides the whole series by an hour.
Consider the canonical case. A 9:00 AM Pacific stand-up is 17:00 UTC in winter and 16:00 UTC in summer, because America/Los_Angeles is UTC−8 under standard time and UTC−7 under daylight time. If the system had stored each occurrence as a UTC instant, the spring-forward boundary would leave half the series at 17:00Z and half at 16:00Z — and on the morning the clocks changed, everyone would show up an hour wrong. Storing local time and deriving UTC makes the right answer automatic: the wall-clock never moves, and the offset is recomputed from the zone database, which is the only thing that knows the transition dates.
A recurring event is one logical record but unbounded physical occurrences, and the model has to survive three kinds of edit. All three reduce to the same primitive — an exception row, a child pointing at the master via parent_event_id, tagged with the recurrence_id of the occurrence it is about.
A cancel one instance is an override row with status = cancelled for that recurrence_id — iCalendar's EXDATE, but as a row so it can carry metadata. A move or edit one instance is an override row with its own start and, because it is a full row, its own attendees and RSVPs. This and all following is a series split: set the old master's UNTIL to just before the split and create a new master from the split point. That third one is what people get wrong — they try to version a rule in place; the right move is two writes, history preserved, old exceptions still attached to the old master.
The exception lives in an append-only ledger keyed by recurrence_id: a cancel tombstones one occurrence, a move carries its own new start, an edit records which columns the user pinned. Recurrence health then becomes a GROUP BY series — and nothing ever UPDATEs the series in place.
There is a second invariant hiding inside the first, and it is what makes the system bounded: the per-query window cap and the max_instances guardrail. A recurrence yields occurrences in order, so expansion stops the instant it crosses the window's far edge — and a crafted FREQ=SECONDLY rule that tries to emit millions of instances dies at the cap instead of melting a worker. Local time keeps the books honest; the cap keeps a single read from being weaponized. Together they are the entire correctness story of the read path.
Three programs carry the read and write loops: the expansion that turns one master into concrete occurrences without ever freezing UTC, the two-query read path that overlays exceptions in memory, and the iCalendar ingestion that maps the outside world onto the same columns. Each is small; the judgment is in what it refuses to do.
This is the procedure that makes or breaks the design. The rule is walked in local time; each occurrence is converted to UTC only after it is generated. The guardrail is not decoration — it is the line that stands between a worker and a FREQ=MINUTELY abuse rule. And the merge of an override is sparse: it supplies its own time plus only the columns it explicitly set, so a later typo-fix on the series still reaches the moved Wednesday instance.
Notice the sparse-merge discipline. The naive instinct is a full snapshot — the override copies every field — which is simple and wrong, because the typo-fix never reaches the moved instance. The opposite extreme is a field-mask fan-out, which writes to every override on every master edit and can race or partially fail at occurrence 4,000 of 9,000. The sparse override makes propagation correct by construction: NULL means inherit, the merge is in-memory because we already loaded the master, and a series rename reaches the override for free.
"Give me this calendar for June" is two SQL statements. First, pull the singles and masters that could touch the window — the denormalized rrule_until_utc is exactly what keeps open-ended series index-filterable. Then pull every override for those masters and overlay them in the application after expansion.
Half of "invitations" is interop — an Outlook export, a subscribed holiday feed, a room-booking system, all of which speak iCalendar. The payoff of modeling recurrence on RFC 5545 in the first place is that ingestion is almost a straight column mapping: RRULE, EXDATE, and RECURRENCE-ID land on the columns we already have, and the uid makes re-ingesting the same feed a no-op.
The carve-out an interviewer listens for: the heavyweight case. When the master's rule or time changes, the occurrence grid moves out from under every recurrence_id, and there is no clean automatic answer to "where does the cancelled-Tuesday exception go in a Thursday series." So that one edit is treated as a rebuild — warn the user that instance-level changes will be discarded, then split or recreate the series. Everything else is a sparse merge; only rule/time changes earn the heavy hammer.
Two derived layers sit atop the event store: the free/busy index, which materializes the very thing we refused to materialize for events, and the change-stream pipeline, which keeps every projection in lockstep. Both exist for speed; both can be thrown away and rebuilt from the log.
For ten people over two weeks, computing free/busy live is genuinely fine — fan out to the ten calendars, expand each, project to busy intervals, merge per attendee, and intersect across attendees with a sweep line. So why not stop there? Three reasons: tail latency (one slow shard out of ten makes an interactive query slow, and people drag the duration slider, re-firing it), volume (schedulers, room booking, and AI agents hammer this far beyond the UI), and waste (recurrence expansion is deterministic, so re-expanding everyone on every call recomputes the same answer). The fix is a materialized free/busy index, separate from the source-of-truth event store.
Per calendar, per UTC day, store a bitmap at 15-minute granularity — 96 bits, 12 bytes. Two weeks for one user is 168 bytes; ten users is under 2 KB. The query collapses to: fetch the tiny per-day bitmaps, OR them together across attendees, and scan for a run of zero bits at least the meeting length long. What just came back through the side door is materialization of recurrences — the very thing rejected for the event store — and here it is right, because free/busy needs only coverage, not event identity, and the bitmap is fixed-size no matter how many events overlap a slot.
Only one thing makes those four projections consistent: the spine. Three different systems must react to one event write — attendee copies must fan out, free/busy bitmaps must rebuild, cache keys must rotate. Doing them inline makes "create event" latency hostage to three downstream systems, and a partial failure leaves the world inconsistent. The fix is a single ordered log per calendar fed by a transactional outbox: every mutation writes the event row and a changelog row in the same database transaction, so the stream can never miss a committed change or replay one that rolled back.
From there the log forks into independent consumers that share nothing but the stream. Fan-out replicates an invitation into each attendee's calendar partition, carrying the version so a stale copy is detectable and self-repairing — and above a threshold (a 50,000-person all-hands) it flips to reference-only, one source row resolved at read time the way feeds handle celebrity accounts. Free/busy runs the materializer above. Cache-bump rotates the calendar's version so rendered-window keys roll over and stale entries simply age out — no explicit invalidation. And client sync is the cheapest leverage of all: the changelog seq is the sync token, so "what changed since X?" turns most phone wake-ups into a tiny, usually empty delta. One spine, four jobs — and the deep cut: when IANA ships a new tz release, you diff which zones changed over which future ranges and enqueue a synthetic "touch" through the very same stream, so the materializer and cache-bump do the rest with code that already exists.
The occurrence, free/busy, invitation, and exception facts are where the system explains itself. Three queries an interviewer loves, each carrying a classic pattern on its back: the as-of read against materialized occurrences, gaps-and-islands for "find a time," and conditional aggregation over the append-only exception ledger.
The user's question, and the hot path of the whole product: my stand-up over the next month, cancellations dropped and moves honored. Because occurrences are materialized to a rolling horizon, this is a range scan on fct_event_occurrences — no RRULE re-expansion at read time, because the override row already carries its moved time and the cancelled occurrence already carries its tombstone flag.
The scheduler's question: the first 60-minute slot where Alice, Bren, and Chen are all free. The pattern is the canonical gaps-and-islands move — OR the per-attendee masks (here a SUM across calendars), keep only the all-free slots, and let a running count of busy slots label each free run; the first island long enough wins. A 60-minute meeting needs four consecutive zero slots.
The organizer's question and the workspace admin's, in one shape. Because invitations are per-occurrence and exceptions are an append-only ledger, the response mix is a GROUP BY response on one occurrence, and recurrence health is conditional aggregation over fct_event_exceptions — a series with cancels every week is a "zombie" the workspace nudges to delete.
A senior design ends with observability, because every clever degradation above is invisible without it. The calendar dashboard watches three things that fail differently: the read path that must feel instant, the pipeline that keeps projections honest, and the time-correctness machinery that no one notices until it is wrong.
Read the amber tiles together and the dashboard narrates the deep cut from §06. IANA shipped a zone change overnight; the bump job diffed the affected zones and ranges and enqueued synthetic touches, so 61k future occurrences are being re-derived and their bitmaps restamped — and because every projection is versioned and replayable, fan-out merely lagged rather than corrupted, stale copies are repairing themselves at 820/s, and not one user's render got slower. That is what a designed degradation looks like from the operator's chair: the time machinery churns, and the front of the house stays boring.
Strip the calendar details away and the question was probing five judgments, each of which generalizes far beyond scheduling: