Implement full Outlook link

[pataniaeli]

Currently, only SGA Spaces bookings appear on a user’s Outlook Calendar. Integrating main bookings should be investigated

[pataniaeli]

Investigation, as the issue asks. No code written. Numbers below are from the live database today.

How Spaces bookings reach Outlook

There is no calendar integration — no Graph API, no CalDAV, no OAuth. The confirmation email simply carries an .ics attachment:

• lib/emails/space-booking-confirmed.ts builds a VCALENDAR with METHOD:REQUEST, a full VTIMEZONE block for America/New_York, and UID:<space_booking_id>@chambers.northeasternsga.com.
• Resend attaches it as booking.ics with contentType: text/calendar; method=REQUEST.
• space-booking-cancelled.ts sends METHOD:CANCEL with the same UID and SEQUENCE:1, which is what makes Outlook remove the event rather than add a second one.

So "appearing on Outlook" is a side effect of emailing a well-formed invite. Extending it to main bookings means attaching .ics to the emails #79 just added, not building an integration.

What makes main bookings harder

Main bookings are easier in one respect — their times are already date + time columns, so DTSTART;TZID=America/New_York is a direct format rather than the wall-clock-labelled-Z dance Spaces needs. Everything else is harder.

1. Row ids are not stable, so they cannot be UIDs

This is the blocker, and it is not obvious. Every PATCH deletes and reinserts:

• administrator/bookings/weekly/route.ts — .from("weekly_room_occurrences").delete().eq("weekly_booking_id", …) then reinserts
• administrator/bookings/one-time/route.ts — same for one_time_room_bookings

So occurrence and session UUIDs change on every edit. A UID keyed on the row id would differ each time, and Outlook would add a second event instead of updating the first. Editing a 14-week series once would put 28 events in everyone's calendar.

UIDs therefore have to be derived deterministically from stable inputs — e.g. weekly-<weekly_booking_id>-<occurrence_date>, since weekly_booking_id survives the regeneration and the date is the natural key for a week.

2. Date alone is not unique

2 one-time bookings currently have two sessions on the same day. A <booking_id>-<date> UID would collapse them into one calendar event. The key needs an ordinal within the date, which then has to be ordered by something stable (start time).

3. There is no sequence counter to increment

SEQUENCE must strictly increase per UID for a client to treat a repeat REQUEST as an update. The booking tables carry created_at and nothing else — no updated_at, no revision number.

Workable without a migration: derive SEQUENCE from seconds since a fixed epoch at send time. Monotonic by construction, and well inside the 32-bit range implementations assume. Slightly unusual but valid.

4. Weekly series cannot be a simple RRULE

46 occurrences currently override their series on room, time or status. A single recurring VEVENT would need EXDATE plus a RECURRENCE-ID override per divergent week — the most complex corner of RFC 5545.

One VEVENT per occurrence avoids all of it. Series average 13.3 occurrences (max 14), so a typical invite is ~13 events in one file. Legal, and Outlook handles it, but it is a large attachment and every edit resends all of them.

5. REQUEST and CANCEL cannot share a file

METHOD is a property of the VCALENDAR, not the VEVENT. An edit that moves a booking off a date has to send two attachments — a REQUEST for the dates that remain and a CANCEL for the ones that went. The update routes already read the previous rows (for #79's change diff), so the before/after sets are available; they are just not currently used for this.

6. Cancellation has no email to attach to

administrator/bookings/cancel/route.ts sends no email at all. Marking a booking Cancelled — including via Auto-Cancel — notifies nobody and would leave the event sitting in calendars. Closing that gap means adding a member-facing cancellation email, which is a feature in its own right, not a rider on this one.

Recommendation

Feasible and worth doing, but it is a piece of work rather than a patch — roughly: a shared ICS builder, a UID scheme with the ordinal handling, sequence derivation, before/after diffing for cancellations in three routes, and a new cancellation email.

The failure mode to design against is a stale event: someone's Outlook saying Curry 333 at 6 PM after the booking moved to Curry 340 at 7 PM. A calendar entry people trust and act on is worse than no entry, which is why I would not ship creation-only invites as a first step.

Suggest v1.15.0, as its own issue with the UID scheme agreed up front.

One incidental note: space-booking-confirmed.ts and space-booking-cancelled.ts each carry their own copy of the VTIMEZONE block and escaping helpers. Whoever builds this should extract them into a shared module — and can verify the refactor by byte-comparing generated output before and after, since the Spaces integration currently works and should not be disturbed.