Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Boat Cleaning Dispatch and Calendar Sync: Replacing Legacy Google Apps Script with Lambda-Backed APIs

What Was Done

This session consolidated boat cleaning service management and calendar synchronization into a unified, programmatic system. The legacy approach—manual Google Apps Script (GAS) polling and email-based task coordination—was replaced with:

  • A Python-based dispatch system (/Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py) that manages cleaning requests and vendor communication
  • A Lambda-backed inbox scraper (platform_inbox_scraper.py) that monitors email for booking confirmations and status updates
  • Updated CalendarSync.gs in the Apps Script replacement project that syncs boat events to Google Calendar via Lambda APIs instead of polling external calendars
  • Deployment automation via shell scripts to reduce manual intervention

The key insight: instead of GAS polling external boat platforms (GetMyBoat, Boatsetter) every N minutes and building complex email parsing logic in Apps Script, we centralize platform monitoring in Python Lambda functions and use the existing dashboard Lambda API gateway to push calendar events atomically.

Technical Architecture

Dispatch Script Design

The dispatch cleaner script reads boat cleaning requests from multiple sources:

  • Email ingest: Monitors platform inbox (Gmail) via Gmail API for booking confirmations from GetMyBoat and Boatsetter
  • State management: Tracks which requests have been assigned, completed, or are pending (stored in DynamoDB or local JSON state file)
  • Vendor routing: Maps booking requests to available cleaning contractors based on availability, location, and boat type
  • Status communication: Sends SMS/email updates back to Carole with cleaning ETA and vendor contact info

/Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py is idempotent—safe to run on a schedule (via cron or EventBridge) without duplicate dispatches.

Inbox Scraper as Supplement

The inbox scraper (platform_inbox_scraper.py) runs independently to:

  • Extract structured data (booking ID, guest name, boat, dates, cleaning notes) from platform emails
  • Detect status changes (booking confirmed, guest cancelled, rescheduled)
  • Trigger downstream workflows (e.g., "booking confirmed → dispatch cleaner 24 hours before arrival")
  • Archive processed emails to avoid re-processing

This is deployed as a Lambda function via deploy_inbox_scraper.sh, running on a 15-minute schedule through EventBridge.

Calendar Sync Refactor

The original CalendarSync.gs at /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs was updated to:

  • Stop polling external iCal feeds: GetMyBoat and Boatsetter iCal subscriptions are brittle and create calendar noise
  • Call the dashboard Lambda API instead: Use the authenticated /calendar/add-event endpoint (action: add-calendar-event) to push boat events directly
  • Reduce email dependencies: Instead of waiting for email notifications and manually parsing them, the dispatch script directly calls the Lambda API with structured event data
  • Batch operations: The updated script can add multiple cleaning holds in a single execution cycle rather than one-by-one

Example API call pattern (no actual token shown):

POST https://[API_GATEWAY_ENDPOINT]/calendar/add-event
Authorization: Bearer [dashboard_token]
Content-Type: application/json

{
  "action": "add-calendar-event",
  "title": "Boat Cleaning - [Boat Name]",
  "start": "2025-04-28T10:00:00Z",
  "end": "2025-04-28T12:00:00Z",
  "description": "Cleaning for guest arrival. Vendor: [Name]",
  "calendarId": "primary"
}

Infrastructure Changes

Lambda Functions

  • Existing dashboard Lambda: Expanded with boat event handling. Action routing verifies the add-calendar-event action name matches the Lambda code
  • New inbox scraper Lambda: Deployed via CloudFormation/SAM with environment variables pointing to Gmail API credentials (stored in SecretsManager), DynamoDB state table, and SNS topic for notifications
  • Event trigger: CloudWatch Events rule fires every 15 minutes to invoke the scraper; another rule fires the dispatch script every 30 minutes

S3 and State Management

  • Boat platform credentials (GetMyBoat API keys, Boatsetter auth tokens) stored in AWS Secrets Manager, not in repos.env
  • Dispatch state (processed bookings, vendor assignments) persisted in DynamoDB table boat-cleaning-state with TTL for old records
  • Email archives (processed platform emails) stored in S3 bucket platform-email-archives for audit and recovery

Key Decisions and Rationale

Why Lambda Over Cron?

Lambda + EventBridge provides:

  • Resilience: Built-in retries, DLQ support, CloudWatch alarms if a function fails
  • Cost: Pay per invocation, not per minute a server is running
  • Observability: CloudWatch Logs automatically captured; no need to SSH into boxes to debug

Why Pull Email Instead of Push?

GetMyBoat and Boatsetter send webhook notifications for bookings, but:

  • Webhooks require public endpoints; pulling email is more firewall-friendly
  • Email is the system of record; if a webhook is missed, email is the fallback
  • Parsing email is forgiving of platform API changes—platform emails rarely break format

Why Not Keep Google Apps Script for Calendar Sync?

GAS polling is inefficient and unmaintainable:

  • Multiple GAS projects create coordination complexity (which .clasp.json file maps to which project?)
  • GAS has no built-in state management; polling often means duplicate calendar entries or missed events
  • Email-based triggers in GAS (OnChange) are unreliable for external platform integrations
  • Lambda is version-controlled, testable, and integrates seamlessly with the existing dashboard API

Deployment and Testing

deploy_inbox_scraper.sh handles:

  • Building the Python zip package with dependencies (boto3, google-auth, requests)