Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Boat Cleaning Dispatch and Calendar Synchronization Across Multiple Platforms

This session focused on consolidating fragmented service management across three distinct booking platforms (GetMyBoat, Boatsetter, and Google Calendar) into a unified dispatch workflow. The core challenge: eliminating manual data entry between platforms while maintaining audit trails and preventing double-booking.

The Problem: Three Platforms, One Schedule

The existing system relied on manual calendar management across multiple platforms:

  • Google Calendar held the canonical schedule (boat maintenance holds, Sea Scout events)
  • GetMyBoat and Boatsetter hosted separate booking calendars
  • No synchronization between systems—changes in one didn't propagate to others
  • Boat cleaning requests came through email, requiring manual task creation and dispatch

This fragmentation created operational friction: Carole (operations lead) had to manually check multiple calendars, email cleaning coordinators individually, and track responses in email threads rather than a centralized system.

Solution Architecture: Event-Driven Calendar Sync

File Structure Created:

/Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs
/Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py
/Users/cb/Documents/repos/tools/platform_inbox_scraper.py
/Users/cb/Documents/repos/tools/deploy_inbox_scraper.sh

The solution leverages three core components:

1. Google Apps Script Calendar Synchronizer (CalendarSync.gs)

Purpose: Acts as the authoritative calendar source, syncing hold events (boat maintenance windows, Sea Scout Wednesday activities) to booking platforms via iCal feeds.

Key Implementation Details:

  • Deployed to Google Apps Script project (mapped via .clasp.json configuration)
  • Listens for calendar changes via Apps Script triggers
  • Generates iCal feed that GetMyBoat and Boatsetter consume as "unavailable time"
  • Polling interval set to hourly checks (configurable via environment variables)
  • Email notifications on sync failure sent to operations team

Architecture Decision: Using iCal feeds rather than direct API calls eliminates the need to manage platform-specific credentials in the Apps Script environment. Most booking platforms support iCal import natively, reducing implementation surface area.

2. Dispatch Boat Cleaner Script (dispatch_boat_cleaner.py)

Purpose: Converts incoming cleaning requests into structured dispatch tasks and calendar holds.

Workflow:

Email Request → Parse cleaning date/location
              → Create Google Calendar hold (blocks booking platforms via iCal sync)
              → Generate dispatch notification with task ID
              → Update dashboard with status card
              → Send confirmation to Carole

Key Features:

  • Reads from Gmail API (requires OAuth scope: gmail.readonly, gmail.send)
  • Parses unstructured email text using pattern matching for dates and locations
  • Calls Lambda calendar API endpoint to add holds: /calendar-api/add-event
  • Stores task state in dashboard JSON cards for operations visibility
  • Prevents duplicate dispatch by checking existing calendar events within 24-hour window

Example Invocation Pattern:

# Deployed as scheduled Lambda (CloudWatch Events trigger, every 30 minutes)
python dispatch_boat_cleaner.py --dry-run  # Test mode
python dispatch_boat_cleaner.py --send     # Production execution

3. Inbox Scraper & Deployment (platform_inbox_scraper.py, deploy_inbox_scraper.sh)

Purpose: Extracts booking requests from email inboxes across multiple platforms, normalizes format, and feeds into the dispatch system.

Deployment Strategy:

  • deploy_inbox_scraper.sh wraps Python script in Lambda layer for environment isolation
  • Stores secrets (Gmail credentials, platform API keys) in AWS Secrets Manager
  • Writes normalized request logs to S3: s3://boat-operations-logs/inbox-scrapes/
  • Triggers downstream dispatch workflow via SNS topic: arn:aws:sns:region:account:boat-cleaning-requests

Infrastructure Changes

AWS Lambda Functions Updated:

  • Existing calendar API Lambda extended with add-calendar-event action handler
  • Action name format: {resource}-{operation} for extensibility
  • Added CloudWatch log group: /aws/lambda/dashboard-calendar-api with 30-day retention

API Gateway Routes Added:

POST /calendar-api/add-event
POST /calendar-api/list-events
POST /calendar-api/update-event
POST /calendar-api/delete-event

All routes require authentication via dashboard token (passed as Authorization: Bearer {token} header).

Google Calendar Setup:

  • Created dedicated calendar: "Boat Operations - Holds" (used for iCal export to platforms)
  • Separate calendar: "Internal Schedule" (Carole's working calendar, not synced to platforms)
  • Service account used by Lambda has calendar.events.create, calendar.events.list permissions

Key Design Decisions & Rationale

Decision 1: iCal Feeds Over Direct API Integration

Why: GetMyBoat and Boatsetter's APIs require separate authentication and have rate limits. iCal feeds are platform-agnostic and supported universally. One source of truth (Google Calendar) feeds both platforms without duplicating logic.

Decision 2: Email Scraping as Initial Data Source

Why: Booking platforms don't expose webhooks for new reservations. Email remains the most reliable trigger mechanism. Parsing happens server-side (not in Apps Script) to avoid quota limitations.

Decision 3: Dashboard Cards for Operational Status

Why: Carole needs visibility into pending tasks without context-switching between tools. Dashboard becomes the single pane for all active operations (calendar holds, dispatch status, confirmations sent).

Integration Points & Data Flow

GetMyBoat/Boatsetter (booking platforms)
          ↓
    [iCal Feed] ← CalendarSync.gs imports boat holds
          ↓
    Google Calendar (canonical schedule)
          ↑
          ├── dispatch_boat_cleaner.py (adds holds via Lambda API)
          ├── Manual entries (Carole for non-cleaning events)
          └── Email scraper (extracts request metadata)
          ↓
    Dashboard (operations view)
          ↓
    Carole (approves, coordinates response)

Deployment