Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Event Calendar Synchronization Across Multiple Booking Platforms

What Was Done

This session focused on consolidating calendar management across multiple maritime event booking platforms into a single source of truth. The challenge: three separate platforms (GetMyBoat, Boatsetter, and internal Google Calendar) were operating independently, creating scheduling conflicts and manual synchronization overhead. The solution involved building a Google Apps Script-based synchronization layer that pulls booking data from external platforms and pushes events into a centralized Google Calendar, which then serves as the authoritative schedule.

Technical Architecture

The core infrastructure centers on Google Apps Script as the orchestration layer, deployed to the Queen of San Diego events project. The system operates on these principles:

  • Single Source of Truth: Google Calendar becomes the authoritative schedule for all events across platforms
  • Scheduled Polling: Apps Script triggers run on configurable intervals to fetch external platform data
  • Bidirectional Sync (Read-Only for Now): External platforms → Google Calendar, with future capability for reverse sync
  • Lambda API Abstraction: Calendar mutations go through a Lambda function to centralize access control and audit logging

Implementation Details

CalendarSync.gs File Structure

The primary synchronization logic lives in:

/Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs

This file handles:

  • OAuth credential management for GetMyBoat and Boatsetter APIs
  • Scheduled trigger setup using `ScriptApp.newTrigger()` to invoke sync every 6 hours
  • iCalendar feed parsing from booking platforms
  • Duplicate detection and conflict resolution logic
  • Email notifications to operations team when conflicts are detected

Why Google Apps Script?

We selected GAS over Lambda for the sync orchestration because:

  • Native Google Calendar API integration—no authentication overhead
  • Built-in scheduling through script triggers (no additional cron infrastructure needed)
  • Ability to run within the same Google Workspace organization, respecting existing IAM
  • Fast iteration and debugging through the Apps Script IDE
  • Reduced cold start overhead vs. Lambda for this use case

Calendar Event API Integration

Rather than have GAS write directly to Google Calendar, we abstracted write operations through an existing Lambda function:

/Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py

The Lambda function exposes an API Gateway v2 endpoint with action-based routing. The relevant actions for calendar operations are:

  • add-calendar-event – Creates a new event with conflict detection
  • update-calendar-event – Modifies existing event details
  • delete-calendar-event – Removes events with audit logging
  • list-calendar-events – Queries events within a date range

This abstraction allows:

  • Centralized permission checks (who can modify which calendars)
  • Audit trail logging to CloudWatch for compliance
  • Rate limiting to prevent platform throttling
  • Retry logic with exponential backoff for transient failures

Event Structure and Validation

Calendar events created via the API follow this JSON structure:

{
  "summary": "Sea Scout Wednesday Holds - [Boat Name]",
  "description": "Recurring weekly reservation hold for scout group activities",
  "startTime": "2024-04-24T18:00:00-07:00",
  "endTime": "2024-04-24T20:00:00-07:00",
  "recurrence": ["RRULE:FREQ=WEEKLY;UNTIL=20240630"],
  "attendees": ["operations@queenofsandiego.com"],
  "calendarId": "primary"
}

The Lambda function validates:

  • Time format compliance (RFC 3339)
  • No double-bookings within the specified calendar
  • Attendee email validity
  • Sufficient advance notice (configurable, default 24 hours)

Platform Integration Points

GetMyBoat Credentials

Stored securely in the GAS script properties (encrypted at rest by Google). The sync process authenticates to GetMyBoat's REST API using OAuth 2.0 client credentials, fetching available slots and confirmed bookings at the interval specified in the trigger configuration.

Boatsetter Integration

Boatsetter provides iCalendar (.ics) feed URLs for each vessel. CalendarSync.gs downloads and parses these feeds, extracting event metadata (title, start/end times, booking ID) and merging them into the master calendar with a "Boatsetter:" prefix for source tracking.

Conflict Resolution Strategy

When the same time slot appears in multiple platform feeds, the system:

  1. Identifies the booking with the highest revenue value
  2. Marks lower-priority bookings with a "TENTATIVE" status in Google Calendar
  3. Notifies the ops team immediately so they can manually resolve (phone call to client, usually)
  4. Logs the conflict to CloudWatch with full event details for post-incident review

Infrastructure and Deployment

Google Apps Script Project Mapping

Project metadata is stored in `.clasp.json` files throughout the repo:

find /Users/cb/Documents/repos -name ".clasp.json" -type f

The CalendarSync project is deployed via the Google Apps Script CLI (clasp). Changes are pushed to production with:

clasp push -f

Lambda Function Configuration

The Lambda runtime is configured with:

  • Memory: 512 MB (sufficient for Google Calendar API calls and JSON parsing)
  • Timeout: 30 seconds (typical calendar API response time is 200-500ms)
  • Environment variables: API endpoints, Calendar IDs, and notification email addresses
  • IAM role: Allows CloudWatch Logs writes and Secrets Manager reads for credential rotation

API Gateway v2 Routing

The endpoint is configured as a Lambda proxy integration with request validation. Incoming requests must include an Authorization header with a valid API token (hashed and stored in Secrets Manager).

Key Decisions and Rationale

Read-Only Initial Implementation

We started with one-way sync (platforms → Google Calendar) rather than bidirectional to reduce complexity and risk. This allows us to validate the data pipeline before attempting to write bookings back to external platforms, which would require more sophisticated conflict handling and API rate limit management.

6-Hour Polling Interval

Rather