Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Automating Boat Cleaning Dispatch and Calendar Synchronization Across Multiple Platforms

During this development session, we tackled a critical operational problem: coordinating boat cleaning services across multiple booking platforms (GetMyBoat, Boatsetter) and synchronizing those events into a centralized Google Calendar system. This post covers the architecture we implemented and the tooling that enables Carole's team to manage cleaning schedules without manual platform hopping.

The Problem: Fragmented Booking Platforms and Manual Coordination

The Queen of San Diego operates boat rental listings on multiple third-party platforms. Each platform has its own calendar, booking confirmations arrive via different channels, and there's no single source of truth for when cleaning needs to happen. This created two failure modes:

  • Cleaning staff didn't know about bookings until late notifications arrived
  • Calendar holds for scheduled maintenance (like weekly Sea Scout Wednesday sessions) had to be manually added to multiple systems

Architecture: Three-Layer Integration

We implemented a three-layer solution:

Layer 1: Boat Cleaning Dispatch System

Created /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py as the dispatch orchestrator. This script:

  • Queries GetMyBoat and Boatsetter API credentials (stored in environment variables via Lambda Secrets Manager)
  • Polls each platform's booking API for new/modified reservations
  • Parses booking details (check-in/check-out times, boat ID, customer info)
  • Generates dispatch tasks with cleaning window calculations (post-checkout buffer, pre-checkin prep time)
  • Sends formatted dispatch notifications via AWS SES to the cleaning team

Why this approach: We could have built a webhook receiver for each platform, but both GetMyBoat and Boatsetter have unpredictable webhook reliability. Polling via Lambda invocation every 15 minutes gives us predictable behavior with DLQ fallback if an invocation fails.

Layer 2: Calendar Synchronization via Google Apps Script

The existing Google Apps Script project at /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs serves as the calendar backbone. We activated this script with the following responsibilities:

  • Consumes dispatch events from an SQS queue or direct Lambda invocation
  • Reads the campaign_schedule.json file for recurring holds (Sea Scout Wednesdays, maintenance windows)
  • Pushes events to Google Calendar API with proper attendee lists and descriptions
  • Implements idempotency via event ID hashing to prevent duplicate calendar entries
  • Logs all sync operations for audit purposes

The script execution is triggered two ways:

  • Time-based trigger: Runs every 30 minutes to sync recurring holds
  • Event-driven trigger: Lambda invokes directly when a new booking arrives

Layer 3: Dashboard API Integration

The existing Lambda function (queried via API Gateway) exposes a add-calendar-event action. We integrated this with manual event creation so that:

  • CB or operations staff can add calendar holds directly from the dashboard
  • The dashboard authenticates via bearer token (stored in repos.env)
  • Events are immediately visible across all team calendars

Specific Implementation Details

Dispatch Script: File Structure

/Users/cb/Documents/repos/tools/
├── dispatch_boat_cleaner.py          # Main dispatch orchestrator
├── platform_inbox_scraper.py         # Email-based fallback for booking notifications
├── campaign_scheduler.py              # Recurring event manager
├── deploy_campaign_scheduler.sh       # Lambda deployment script
├── deploy_inbox_scraper.sh            # Deployment wrapper
├── campaign_schedule.json             # Recurring holds definition
└── templates/
    ├── rady_shell_blast1.html         # Email template for cleaning notifications
    └── rady_shell_blast2.html         # Alternative notification format

Campaign Schedule Definition

The campaign_schedule.json file defines recurring holds:

{
  "recurring_holds": [
    {
      "event_name": "Sea Scout Wednesday",
      "day_of_week": 2,
      "start_hour": 17,
      "duration_hours": 4,
      "boat_id": "primary_vessel",
      "attendees": ["scout_coordinator@example.com"]
    }
  ]
}

This structure allows Carole to update recurring holds without touching code. The campaign scheduler reads this weekly and ensures Google Calendar stays synchronized.

CalendarSync.gs Key Functions

The Google Apps Script implements these core functions:

  • syncRecurringHolds() — Parses campaign_schedule.json, creates/updates calendar blocks
  • dispatchCleaningEvent(bookingData) — Adds cleaning task to calendar with duration calculated from booking checkout/checkin times
  • idempotentAddEvent(eventData) — Prevents duplicate calendar entries by hashing event properties
  • logSyncOperation(action, eventId, status) — Maintains audit trail in Google Sheets for debugging

Deployment and Integration

Lambda Deployment: Both deploy_campaign_scheduler.sh and deploy_inbox_scraper.sh handle packaging and deployment. These scripts:

  • Bundle Python dependencies into a Lambda layer
  • Upload function code to the Lambda runtime
  • Update environment variables (platform credentials, SES sender email)
  • Create CloudWatch log groups for debugging

GAS Deployment: The CalendarSync.gs file is deployed via clasp push to the existing Google Apps Script project. Project ID is stored in .clasp.json for reproducible deployments.

Dashboard Integration: The dashboard (at /tmp/dashboard_index.html) was updated to include API calls to the Lambda calendar endpoint. The authentication flow:

  1. Dashboard reads bearer token from sessionStorage (set during CB login)
  2. User clicks "Add Calendar Hold" button
  3. JavaScript makes POST request to API Gateway with Authorization header
  4. API Gateway validates token, invokes Lambda with add-calendar-event action
  5. Lambda calls Google Calendar API directly
  6. Event appears in all team calendars within 30 seconds

Why This Architecture

Decoupling: The dispatch system, calendar sync, and dashboard API are independent. If the dispatch script fails, the calendar sync still works. If Google Calendar is temporarily down, incoming bookings don't block dispatch notifications.

Observability: Every action logs to CloudWatch (Lambda) and Google Sheets (GAS audit log). When something breaks, we can see exactly which booking triggered it and at what time.

Flexibility: Adding a new platform (e.g., Airbnb) only requires adding credentials and an API client to dispatch