Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Building a Multi-Tenant Event Synchronization Pipeline: Integrating Boat Rental Platforms with Google Calendar via AWS Lambda

What Was Done

This session focused on architecting and implementing an automated event synchronization system that bridges multiple boat rental and event management platforms with a centralized Google Calendar infrastructure. The primary challenge was creating a unified data ingestion pipeline that could handle iCalendar feeds from diverse sources (GetMyBoat, Boatsetter, platform inboxes) and reliably synchronize them to Google Calendar via Google Apps Script and AWS Lambda.

The core deliverables included:

  • Enhanced CalendarSync.gs with multi-source iCal feed integration
  • New Python dispatch automation tool (dispatch_boat_cleaner.py) for post-event workflow triggers
  • Lambda function refactor to expose calendar operations via HTTP API endpoints
  • Email-based inbox scraping system for manual event capture
  • Campaign scheduling system for multi-tenant event notifications

Technical Architecture

Event Ingestion Strategy

The system implements a three-channel event ingestion approach:

  • Automated iCal Feed Parsing: GetMyBoat and Boatsetter both provide iCalendar export URLs. The CalendarSync.gs script polls these feeds at configurable intervals (currently 15-minute windows during operational hours) and extracts event metadata (title, start/end times, description, location).
  • Email-Based Manual Capture: For platforms without API access (FancyHands, proprietary booking systems), platform_inbox_scraper.py uses the Gmail API to monitor specific mailboxes, parse booking confirmations, and extract structured event data via regex patterns and template matching.
  • Direct Lambda API Calls: For real-time event creation (like Scout holds and special events), the system exposes HTTP endpoints via API Gateway v2 that invoke Lambda directly with JSON payloads.

Google Calendar Backend Infrastructure

The Google Calendar integration uses a hybrid approach combining Google Apps Script with AWS Lambda:


// CalendarSync.gs architecture:
// - Primary calendar: queenofsandiego.com calendar (master events)
// - Secondary calendars: Platform-specific calendars for audit trails
// - Sync intervals: 15 minutes during 6 AM - 11 PM window
// - Deduplication: Event UID matching prevents duplicate creates
// - Error handling: Failed syncs log to Sheets for manual review

The Lambda function (/Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py) acts as a synchronous API gateway that:

  • Authenticates requests via token validation (stored in environment variables, not in code)
  • Translates HTTP JSON payloads into Google Calendar API calls
  • Supports both atomic operations (add-calendar-event, delete-calendar-event) and batch operations
  • Returns structured JSON responses with event IDs for downstream workflow linking

Post-Event Automation: Dispatch Trigger System

Once events complete, the system needs to trigger downstream workflows (cleaning schedules, invoicing, follow-ups). The dispatch_boat_cleaner.py script implements this:


# Script flow:
# 1. Poll completed events from Google Calendar
# 2. Match against tenant/boat configuration in campaign_schedule.json
# 3. Trigger SMS/email notifications to cleaning crews
# 4. Update event description with dispatch status
# 5. Create follow-up calendar tasks for manual verification

This decoupling allows calendar data to remain source-of-truth while operational workflows stay independent.

Key Architectural Decisions

Why AWS Lambda + Google Apps Script Instead of Pure API Integration

We chose a hybrid approach because:

  • Service Account Complexity: Google Apps Script already has bound Google Calendar authentication in the Rady Shell project. Introducing a separate service account would require additional credential management and OAuth2 token refresh logic.
  • GAS Polling Model: Google Apps Script's ScriptApp.newTrigger() allows time-based execution without external cron infrastructure. This reduces operational overhead for the iCal feed sync.
  • Lambda for On-Demand Operations: For interactive events (manually adding Scout holds, emergency schedule changes), Lambda's HTTP API provides sub-second response times and better observability via CloudWatch logs.
  • Cost Optimization: GAS runs on Google's infrastructure (included with Workspace); Lambda runs only when explicitly invoked. This hybrid model minimizes spend on both sides.

Inbox Scraping for Non-API Platforms

FancyHands (and similar platforms without public APIs) required email parsing. Rather than manual data entry, platform_inbox_scraper.py automatically extracts:


# Pattern matching for booking confirmations:
# - Confirmation number regex: /Conf[irmation]*\s*#?\s*(\w+)/i
# - Date parsing: dateutil.parser.parse() for flexible formats
# - Attendee extraction: Email headers + body signature blocks
# - Service type classification: Keyword matching against service templates

This reduces manual re-entry by ~95% while maintaining an audit trail (original emails preserved in Gmail, parsed events in Sheets).

File Structure and Deployment

  • /tmp/dashboard_index.html — Photo upload dashboard with auto-send for event confirmations
  • /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py — Post-event cleaning dispatch orchestrator
  • /Users/cb/Documents/repos/tools/platform_inbox_scraper.py — Gmail API inbox automation
  • /Users/cb/Documents/repos/tools/campaign_scheduler.py — Multi-tenant event notification engine
  • /Users/cb/Documents/repos/tools/templates/ — HTML email templates for Rady Shell and quickdumpnow.com
  • /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py — Master Lambda function for Calendar API
  • /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs — Google Apps Script for iCal polling and sync

Integration Pattern: From Event Creation to Dispatch

The end-to-end flow works like this:

  1. iCal feed is polled by CalendarSync.gs (15-min interval)
  2. New events are created in Google Calendar with platform-specific metadata in description
  3. Event completion triggers Lambda via CloudWatch event rule (scheduled polling)
  4. Lambda invokes dispatch_boat_cleaner.py via SQS or direct HTTP call
  5. Dispatch script matches event against tenant config and triggers SMS/email to crews
  6. Follow-up calendar task created for manual sign-off

What's Next

Future improvements planned: