Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Automating Event Calendar Synchronization and Boat Service Dispatch via Google Apps Script and Lambda

This session focused on integrating calendar management and service dispatch automation across multiple properties, replacing manual workflows with serverless functions and Google Apps Script. The work involved deploying a calendar sync system, creating a boat cleaning dispatch scheduler, and building email campaign infrastructure for event notifications.

Calendar Synchronization Architecture

The primary challenge was consolidating calendar events from multiple booking platforms (GetMyBoat, Boatsetter) into a unified Google Calendar while maintaining manual holds and event blocks. The solution involved updating /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs to pull iCalendar feeds from external booking platforms.

Why this approach: Google Apps Script provides direct Calendar API access without additional infrastructure costs. By syncing iCal feeds rather than building custom integrations for each platform, we reduce maintenance surface area and make the system extensible for future booking platforms.

Key implementation details:

  • CalendarSync.gs configured to fetch iCalendar URLs from platform credentials stored in repos.env
  • Polling interval set to sync every 4 hours (configurable via manifest)
  • Error handling logs failures to a dedicated error sheet within the GAS project without interrupting subsequent syncs
  • Calendar event creation uses the standard Google Calendar API with proper timezone handling for San Diego events
  • Duplicate prevention via event UID matching against existing calendar entries

The GAS project was deployed via Clasp (Google Apps Script CLI) by identifying the project ID from .clasp.json in the rady-shell-events directory. This allowed version control of the script through Git while maintaining the live deployment in Google's infrastructure.

Lambda-Based Calendar API for Dashboard Integration

While CalendarSync.gs handles inbound platform syncing, the dashboard needed a separate read/write API for event management. The existing Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py was extended with two new actions:

  • list-calendar-events — Returns calendar events within a date range, filtered by calendar name
  • add-calendar-event — Creates new calendar events with timezone support and conflict detection

The Lambda function authenticates to Google Calendar using a service account key stored in AWS Secrets Manager (referenced via environment variable GCal_CREDS_SECRET). This eliminates the need for individual OAuth flows and allows the dashboard to perform privileged calendar operations without exposing credentials to the frontend.

API Gateway configuration: The Lambda function is exposed via API Gateway v2 with a custom authorization token. Dashboard requests include this token in the Authorization header. The endpoint signature follows REST conventions:

POST /calendar
{
  "action": "add-calendar-event",
  "calendar": "calendar-name",
  "event": {
    "summary": "Event Title",
    "start": "2025-04-28T18:00:00",
    "end": "2025-04-28T22:00:00",
    "description": "Optional details"
  }
}

This approach allows the dashboard to add calendar holds (such as the 7 weekly Sea Scout Wednesday blocks) without modifying CalendarSync.gs or making direct Google Calendar API calls from the browser.

Boat Cleaning Dispatch Automation

A new dispatch system was created to automate boat cleaning scheduling. The tool consists of three components:

  • /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py — Python script that determines optimal cleaning windows based on calendar occupancy
  • /Users/cb/Documents/repos/tools/deploy_boat_cleaner.sh — Deployment wrapper for testing and scheduling via cron
  • CloudWatch Events rule triggering the Lambda wrapper daily at 6 AM Pacific

The dispatch logic queries the calendar API (using the same Lambda function) to identify gaps between bookings, then schedules cleanings with a buffer of 2 hours before the next event. If multiple gaps exist, it prioritizes longer windows to allow thorough cleaning.

Why this decision: Rather than building custom scheduling logic, we leverage the existing calendar as the source of truth. This ensures cleanings never overlap with guest events and adapts automatically when bookings change. The bot runs daily to accommodate last-minute cancellations or new bookings.

Email Campaign Infrastructure

To support event reminders and marketing communications, three campaign templates were created:

  • /Users/cb/Documents/repos/tools/templates/rady_shell_blast1.html — Event announcement template
  • /Users/cb/Documents/repos/tools/templates/rady_shell_blast2.html — Reminder template with 48-hour notice
  • /Users/cb/Documents/repos/tools/campaign_scheduler.py — Manages scheduling and dispatch logic

A companion configuration file (campaign_schedule.json) defines which template gets sent to which audience at which interval before events. This decouples template design from scheduling logic and allows non-engineers to adjust send times by editing JSON rather than Python code.

Email delivery uses AWS SES (Simple Email Service) with proper headers for unsubscribe links and DKIM signing via the domain's Route53 configuration. The deploy_campaign_scheduler.sh script stages the Python function as a CloudWatch Events rule.

Multi-Site Campaign Expansion

The campaign infrastructure was replicated for QuickDumpNow (a separate property) with site-specific customization:

  • /Users/cb/Documents/repos/sites/quickdumpnow.com/tools/qdn_consumer_blast.py — QDN-specific campaign dispatcher with custom unsubscribe handling
  • /Users/cb/Documents/repos/sites/quickdumpnow.com/marketing/templates/qdn_blast1.html — QDN brand templates
  • /Users/cb/Documents/repos/sites/quickdumpnow.com/unsubscribe/index.html — Unsubscribe landing page with list management
  • /Users/cb/Documents/repos/sites/quickdumpnow.com/tools/qdn_stats.py — Analytics tracking (opens, clicks, unsubscribes)

Each site maintains its own SES configuration and verified sender address, allowing independent reputation management and compliance tracking per property.

Integration Points and Future Extensibility

The architecture creates several extension points:

  • Additional booking platforms: New iCal feeds can be added to CalendarSync.gs without modifying Lambda or dashboard code
  • Custom business logic: The dispatch_boat_cleaner.py script can be extended with crew availability constraints, travel time calculations, or priority rules
  • Analytics: qdn_stats.py provides a template for tracking campaign performance; the same pattern applies to Rady Shell campaigns
  • Webhook integration: Booking platform webhooks can trigger immediate calendar syncs rather than waiting for the 4-hour polling interval

Key decision rationale: All systems use environment variables for configuration (stored in repos.env or AWS Secrets