Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Boat Cleaning Dispatch and Calendar Synchronization: A Multi-Service Integration Pattern

This session focused on solving a critical operational gap: coordinating boat cleaning services across multiple booking platforms while maintaining calendar visibility for the organization. With FancyHands no longer viable, we implemented a dispatch automation system that pulls boat cleaning requests from multiple sources and routes them through a unified workflow.

The Problem: Multi-Platform Booking Chaos

The Queen of San Diego operates across multiple boat rental and service platforms (GetMyBoat, Boatsetter, and internal booking systems). Previously, cleaning requests were scattered across these platforms with no unified dispatch mechanism. When FancyHands fell through, we needed immediate automation to prevent operational breakdown.

Solution Architecture

We implemented a three-layer system:

  • Data Aggregation Layer: Scraping and API polling from booking platforms
  • Workflow Layer: Python-based task scheduling and event generation
  • Calendar Integration: Syncing dispatch events to Google Calendar via Google Apps Script

Technical Implementation

1. Dispatch Boat Cleaner Script

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

  • Polls GetMyBoat and Boatsetter APIs for completed bookings
  • Filters for bookings requiring cleaning post-checkout
  • Generates standardized dispatch tasks with time windows
  • Stores dispatch records in a local JSON state file for idempotency

The script reads platform credentials from repos.env and implements exponential backoff for API rate limiting. Each dispatch record includes booking ID, vessel name, checkout time, and assigned cleaner contact information.

2. Email-Based Notification Pipeline

Created /Users/cb/Documents/repos/tools/platform_inbox_scraper.py to monitor incoming booking notifications via Gmail API. This scraper:

  • Reads emails from designated booking platform inboxes
  • Parses structured booking data (vessel, guest, checkout time)
  • Generates dispatch records automatically
  • Sends acknowledgment emails to platform contacts

The Gmail API integration uses OAuth2 credentials stored in the deployment environment. We implemented message threading to avoid duplicate processing by tracking message_id in our state file.

3. Campaign Scheduler for Recurring Cleanings

Created /Users/cb/Documents/repos/tools/campaign_scheduler.py and corresponding campaign_schedule.json for managing recurring cleaning schedules (weekly deep cleans, seasonal maintenance, etc.). This allows:

  • Defining recurring dispatch patterns
  • Scheduling multiple cleaners for complex jobs
  • Setting escalation rules for no-shows
  • Generating calendar holds with 30-day advance notice

The scheduler processes JSON campaign definitions and creates calendar events via the Lambda API, ensuring no scheduling conflicts before committing dispatch assignments.

4. Google Apps Script Calendar Synchronization

The key integration point is /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs. This GAS file:

  • Runs on a 15-minute polling interval via time-based triggers
  • Queries the iCal feeds from GetMyBoat and Boatsetter (exported from platform settings)
  • Parses booking events and creates corresponding cleaning holds in Google Calendar
  • Maintains a sync log in Google Sheets to prevent duplicate imports

The script structure includes these key functions:

function syncBookingPlatforms() {
  // Fetch iCal feeds from GetMyBoat and Boatsetter
  // Parse event data and extract checkout times
  // Query existing calendar events to check for duplicates
  // Create "CLEANING HOLD" events 2-4 hours post-checkout
}

function dispatchToCleaners() {
  // Read cleaning holds from calendar
  // Match against available cleaner schedules
  // Send SMS/email notifications via SES
  // Log dispatch status back to calendar notes
}

We store the GetMyBoat and Boatsetter iCal URLs in a configuration spreadsheet that CalendarSync.gs reads at runtime, allowing configuration changes without code redeploys.

Deployment Infrastructure

Lambda Function: The existing calendar API Lambda function (referenced in the session) was extended to support the add-calendar-event action, accepting booking data and creating dispatch calendar blocks directly from the automation pipeline.

Deployment Scripts: Created deploy_boat_cleaner.py, deploy_inbox_scraper.sh, and deploy_campaign_scheduler.sh for pushing updates to their respective environments. These scripts:

  • Validate environment variable presence (credentials, API endpoints)
  • Run test imports against staging booking platforms
  • Create backup snapshots of current state files before deployment
  • Smoke test the dispatch workflow end-to-end

Email Templates and Notifications

Created HTML email templates in /Users/cb/Documents/repos/tools/templates/:

  • rady_shell_blast1.html – Dispatch confirmation email to cleaning team (includes vessel details, access codes, expected duration)
  • rady_shell_blast2.html – Completion checklist email (photo upload links, damage report form, feedback survey)

Emails are sent via SES with platform credentials, allowing tracking of delivery and opens. The templates use Jinja2 variable injection for dynamic booking details.

State Management and Idempotency

All three automation scripts maintain a JSON state file (e.g., dispatch_state.json) tracking:

  • Last-processed booking/email IDs
  • Dispatch task creation timestamps
  • Failed dispatch attempts with retry counts
  • Cleaner assignment history for load balancing

This prevents duplicate dispatch emails when scripts are re-run and enables audit trails for operational analysis.

Key Architectural Decisions

Why Gmail polling instead of webhook integration? Booking platforms offer limited webhook support; Gmail polling via API is stable, requires no third-party callbacks, and integrates with existing SES notification pipeline.

Why Google Apps Script instead of Lambda for calendar sync? GAS has native Google Calendar API access and built-in time-based triggers. This avoids Lambda cold-start latency for 15-minute polls and eliminates cross-account credential management.

Why JSON state files over a database? The operation runs on a small scale (5-10 bookings/week). Local state files are simpler to backup, easier to inspect/debug during troubleshooting, and don't require database infrastructure costs.

What's Next

Immediate action items:

  • Document the system for Carole, including how to add new cleaners and adjust scheduling rules
  • Monitor the