Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Boat Cleaning Dispatch and Calendar Integration: Building a Multi-Platform Scheduling System

This session focused on solving a critical operational gap: replacing manual FancyHands booking with an automated dispatch system while integrating boat cleaning schedules across multiple calendar platforms. The work involved building Python-based dispatch automation, Google Apps Script calendar synchronization, and establishing a reliable handoff system between booking platforms.

The Problem Statement

FancyHands cancellation left us without a reliable way to schedule boat cleaning services. Manual booking was error-prone and didn't integrate with existing calendar systems. We needed a system that could:

  • Accept cleaning requests from multiple sources (platform inbox, manual entry)
  • Dispatch tasks to available cleaning crew
  • Sync confirmed appointments to Google Calendar and external booking platforms
  • Provide visibility across all stakeholders

Technical Architecture: Three-Layer Dispatch System

Layer 1: Inbox Scraper and Request Aggregation

Created /Users/cb/Documents/repos/tools/platform_inbox_scraper.py to pull booking requests from multiple platforms. This script:

  • Authenticates with platform APIs using credentials stored in environment variables
  • Parses incoming requests for date, time, vessel, and cleaning scope
  • Writes normalized requests to campaign_schedule.json for downstream processing
  • Runs on a scheduled interval via deploy_inbox_scraper.sh

Why this approach: Centralizing inbox scraping prevents duplicate processing and creates a single source of truth for all pending requests. Using JSON as the intermediate format allows loose coupling between scraping and dispatch logic.

Layer 2: Dispatch Engine

Built /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py as the core dispatch logic:

#!/usr/bin/env python3
# Core functions:
# - read_pending_requests(schedule_file)
# - assign_crew_by_availability(request, crew_roster)
# - validate_schedule_conflicts(assigned_date, vessel)
# - generate_dispatch_notification(request, crew_assignment)
# - log_dispatch_event(request_id, crew_id, timestamp)

This script:

  • Reads pending requests from the JSON queue
  • Checks crew availability against existing calendar holds
  • Prevents double-booking by querying Google Calendar via Lambda API
  • Generates dispatch notifications sent to crew via SES
  • Updates dispatch status back to the request record

Key decision: Rather than maintaining a separate crew database, we query the authoritative Google Calendar. This eliminates sync issues and keeps the single source of truth for availability.

Layer 3: Calendar Synchronization via Google Apps Script

Modified /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs to handle bidirectional calendar syncing:

// Key functions in CalendarSync.gs:
function syncExternalBookingToCalendar(platformBooking) {
  // Extract event details from booking platform
  // Create Google Calendar event with crew assignment
  // Add event ID mapping for two-way sync
}

function pushCalendarChangesToPlatforms(calendarEvent) {
  // When calendar updated, propagate to GetMyBoat, Boatsetter
  // Maintains consistency across all booking surfaces
}

function handleDispatchNotification(crewEmail, dispatchData) {
  // Triggered by dispatch system
  // Sends crew assignment + calendar link
  // Logs response for availability tracking
}

This script runs as a timed trigger (every 15 minutes) and event-based trigger (when calendar changes). It syncs with:

  • Google Calendar (primary scheduling surface)
  • GetMyBoat platform API (for boat owner visibility)
  • Boatsetter integration (backup booking platform)

Infrastructure and Deployment

Lambda-Based Calendar API

The existing Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py already provided calendar endpoints. We extended it with new dispatch actions:

  • action: "add-calendar-event" — Creates blocked-time holds for crew scheduling
  • action: "list-calendar-events" — Queries availability (used by dispatch engine)
  • action: "sync-external-booking" — Pushes confirmed bookings to external platforms

The Lambda function is invoked via API Gateway v2, secured with authentication tokens stored in environment variables. Dispatch system calls the calendar API before assigning crew to verify no conflicts.

Scheduled Automation

Deployment scripts handle scheduling:

  • deploy_inbox_scraper.sh — CloudWatch Events rule triggering scraper every 30 minutes
  • deploy_campaign_scheduler.sh — EventBridge rule running dispatch engine every hour
  • CalendarSync.gs — Apps Script time-based trigger (15-min intervals) and onChange triggers

Why hourly dispatch instead of real-time: Batching reduces API calls and prevents race conditions. If two requests arrive simultaneously, they're processed together with the latest availability data.

Data Flow and Integration Points

Platform Inbox
     ↓
[platform_inbox_scraper.py] → campaign_schedule.json
     ↓
[dispatch_boat_cleaner.py] → queries Google Calendar via Lambda
     ↓
Crew assigned → Lambda API creates calendar hold → CalendarSync.gs watches event
     ↓
[CalendarSync.gs] → pushes to GetMyBoat/Boatsetter APIs
     ↓
Dispatch notification → crew receives email + calendar link

Key Decisions and Rationale

1. Google Calendar as the authoritative source: Rather than maintaining separate databases for crew availability, crew assignments, and bookings, we treat Google Calendar as the single source of truth. All systems query it via Lambda API, eliminating sync divergence.

2. JSON-based request queue: Using campaign_schedule.json instead of a database allows the system to run entirely with S3, SES, and Lambda — no database overhead. Requests are atomic JSON objects that can be reprocessed if dispatch fails.

3. Apps Script as the integration layer: Google Apps Script sits at the boundary between our internal systems and external platforms. It handles API authentication, error retry logic, and maintains historical event-to-booking mappings without requiring a separate integration server.

4. Lambda for calendar queries: Using the existing Lambda function for all calendar operations centralizes authentication and provides a typed API interface that's easier to test than direct GCal API calls from multiple scripts.

What's Next

Immediate tasks:

  • Load crew availability baseline into Google Calendar (blocking existing time commitments)
  • Test end-to-end flow with a staging booking request
  • Set up CloudWatch alarms for dispatch failures (SNS notification to ops)
  • Create dashboard card for