Design a shared calendar

A daily meeting for 5 years is 1,825 rows, and 'repeat forever' has no end. Store one series record with a start time, a duration and a recurrence rule in the style of the iCalendar standard (RFC 5545), such as 'every week on Tuesday', 'every 2 weeks' or 'monthly on the last Friday', with an optional end date or count.

Expand only what is visible. To draw a week, take each series that overlaps the window, generate its occurrences between the start and end of the window, and drop the rest. A weekly series gives one occurrence in a 7-day window. The expansion code is a shared library, used by both the Calendar service and the Free/busy service, so they always agree.

Index series by their span. A series row in the index holds the first start and the last end, or a far-future date when it never ends, with its rule, zone and time-changing overrides. A window query then finds every single event and every series that might have an occurrence in the window.

Exceptions. When someone moves or cancels one occurrence, store an override keyed by that occurrence's original start time. It is either 'cancelled' or a replacement with a new time or title. Expansion generates the occurrence, then applies any override with the same key. The original start is the key because a moved occurrence may land on another day.

This and all following. Editing from one occurrence onward is a split. End the old series just before that occurrence, and create a new series that starts there with the changed rule or time. Link the two with a shared group ID so that 'edit all' still finds both. Overrides dated after the split move to the new series. Guests get one update about the new part, not a rewrite of the past.

A guest's reply to one occurrence, such as 'decline this one', is an override too, kept in that guest's response row.

An instant is a point on the universal timeline, stored as UTC, such as 'the flight departs at 14:05 UTC'. A wall time is what a clock in a place shows, such as 'every Tuesday at 9:00 in New York'. Meetings are wall times: people expect 9:00 to stay 9:00 when the clocks change.

So for an event tied to a place, store the local date and time and an IANA zone name, such as America/New_York. Do not store a fixed offset such as -05:00, because the offset changes twice a year and governments change the rules. A one-off event that really is an instant is stored as UTC.

A worked example. The meeting is Tuesdays at 9:00 in America/New_York. In winter New York is 5 hours behind UTC, so 9:00 is 14:00 UTC. After the spring change it is 4 hours behind, so the same 9:00 is 13:00 UTC. The rule is stored once, as local time plus zone, and each occurrence is turned into UTC only when it is expanded. A person in London sees 14:00 in winter. The United States changes its clocks on the second Sunday of March and the United Kingdom on the last Sunday, so for a few weeks London sees 13:00, and then 14:00 again once the UK changes too. That shift is correct, and it follows from the rule. Nobody stored a wrong time.

Odd local times. When clocks spring forward, 02:30 does not exist that night. When they fall back, 01:30 happens twice. Pick a rule and write it down: a missing time moves forward by the length of the gap, and a repeated time means the first one.

Keep the zone database current. Use the IANA time zone database through a library, never your own tables, and update it on a schedule. The index holds UTC bounds so it can be searched by range. When a country changes its rules, those bounds are wrong for future events, so a tz update starts a job that recomputes them from the stored local times.

At the display edge, convert to the viewer's zone and group events by the viewer's local day, because 23:30 in one zone can be the next day in another.

Each guest has a response row: accepted, declined, maybe or no reply, plus their own overrides. A guest changes only their own row, so replies never clash with the organizer's edits. A reply also writes an outbox row, and a worker copies it into that guest's index row. Each edit by the organizer raises the event's version number, so guests and synced apps can tell what is new. A change of time resets replies to 'no reply'.

Fan-out. Each guest's view needs the event in their own user-keyed index, or their week view would have to search every event on every request. The write saves the event, the guest rows and an outbox row in one transaction. A relay publishes the outbox row to the invite queue, and workers update each guest's index row, at least once. The update is safe to repeat: the row is keyed by user and event, and the version number stops an old update from overwriting a new one. That is about 2,300 index updates a second at peak from edits, and as many again from replies. If one write fails, the worker retries from the queue, and that guest sees the old version for a few seconds.

Free and busy. The Free/busy service takes the people and a window. For each person it reads the index rows in the window, expands any series, applies the overrides, and keeps only start, end and a busy flag. A row the person has declined, or marked as free, does not count. The rule and the reply are in the row, so the Event store is not read, and the answer holds times only.

Then it merges. Ten people with about 30 busy blocks each in a week is 300 intervals. Sort them by start, sweep through, and join any that overlap or touch. The gaps between the merged blocks that fall inside working hours are the times when everyone is free. Rank them, say earliest first or away from lunch, and return the top few. Sorting 300 items takes microseconds. The real cost is the 10 index reads, which run in parallel.

Check permissions first. A person who hides their calendar can still be included under a rule such as 'show busy only'. For someone outside the organisation, return no data at all and say so.