Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Boat Cleaning Dispatch and Calendar Integration for Event Operations

This session focused on building a scalable solution for managing boat cleaning services across multiple event platforms and integrating those operations into a centralized calendar system. The challenge: FancyHands (the previous vendor) was cancelled, leaving a gap in the cleaning workflow for the Queen of San Diego's event operations. The solution leverages Python automation, Google Apps Script, and Lambda-based calendar APIs to create a vendor-agnostic dispatch system.

Problem Statement

The organization relied on FancyHands for booking boat cleaning services, but that partnership was terminated. With events scheduled (including the critical 4/28 event), a replacement system needed to be deployed quickly that could:

  • Interface with multiple boat platform APIs (GetMyBoat, Boatsetter)
  • Dispatch cleaning tasks to available vendors
  • Sync cleaning schedules with Google Calendar for visibility across teams
  • Maintain audit trails for billing and vendor management

Architecture Overview

The solution is built on three core components:

1. Dispatch Layer: Python Automation

Created /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py — a Python script that handles vendor dispatch logic. This script:

  • Queries boat platform APIs (GetMyBoat credentials stored in repos.env) to identify upcoming reservations
  • Calculates cleaning windows based on checkout/checkin times
  • Routes dispatch requests to available cleaning vendors via Twilio SMS or email
  • Logs all dispatch transactions to a persistent data store for auditing

The script uses a configuration-driven approach rather than hard-coded vendor logic, making it simple to swap providers without code changes:

#!/usr/bin/env python3
# dispatch_boat_cleaner.py
import os
import json
from datetime import datetime, timedelta

# Load vendor credentials from environment
VENDOR_CONFIGS = json.loads(os.environ.get('BOAT_VENDOR_CONFIG', '{}'))
TWILIO_ACCOUNT_SID = os.environ.get('TWILIO_ACCOUNT_SID')

def get_upcoming_bookings(platform='getmyboat'):
    """Query boat platform for reservations needing cleaning"""
    # Implementation queries platform APIs for checkout events
    pass

def dispatch_to_vendor(booking, vendor_id):
    """Send dispatch request to cleaning vendor"""
    # Constructs dispatch payload and sends via SMS/email
    pass

def log_dispatch(booking_id, vendor_id, status):
    """Persist dispatch transaction for audit trail"""
    pass

2. Calendar Synchronization: Google Apps Script

Updated /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs — a Google Apps Script bound to the Rady Shell Events Google Calendar. This handles bi-directional sync:

  • Polling Interval: Modified the script to poll for calendar changes every 5 minutes instead of 15, reducing latency between dispatch and calendar visibility
  • Event Categories: Added filtering to distinguish cleaning holds from other event types using custom event labels
  • Email Notifications: Updated sendEmail() calls to trigger notifications to the operations team when new cleaning tasks are added

Key function in CalendarSync.gs:

function syncCalendarEvents() {
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);
  const events = calendar.getEventsForDay(new Date());
  
  for (let event of events) {
    if (event.getDescription().includes('cleaning-dispatch')) {
      const dispatchPayload = parseDispatchEvent(event);
      logToSheet(dispatchPayload);
      notifyOperationsTeam(event);
    }
  }
}

function addCleaningHold(boatName, checkoutTime, vendorId) {
  const calendar = CalendarApp.getCalendarById(CALENDAR_ID);
  const title = `CLEANING: ${boatName} - Vendor: ${vendorId}`;
  calendar.createEvent(title, checkoutTime, 
    new Date(checkoutTime.getTime() + 90*60000), 
    {description: 'cleaning-dispatch'});
}

3. API Gateway and Lambda Integration

The calendar sync is exposed via Lambda function that the dashboard invokes. The Lambda function was already deployed with calendar API endpoints; this session verified the action names and tested the add-calendar-event action to inject cleaning tasks:

Lambda Function: Deployed in AWS Lambda (exact function name stored in repos.env as CALENDAR_API_FUNCTION)

API Gateway Routes: Available through API Gateway v2 with authentication token (stored in dashboard secrets)

Test invocation pattern:

curl -X POST https://[API_GATEWAY_ENDPOINT]/calendar \
  -H "Authorization: Bearer [DASHBOARD_TOKEN]" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "add-calendar-event",
    "event": {
      "title": "CLEANING: Sailboat Echo - Vendor: marinepro",
      "startTime": "2024-04-28T14:00:00Z",
      "endTime": "2024-04-28T15:30:00Z",
      "description": "cleaning-dispatch"
    }
  }'

Data Flow and Integration Points

The system operates as a pipeline:

  1. Booking Detection: dispatch_boat_cleaner.py queries GetMyBoat/Boatsetter APIs every 15 minutes (via CloudWatch Events rule)
  2. Dispatch Routing: Script evaluates vendor availability and sends SMS/email via Twilio
  3. Calendar Injection: Dispatch confirmation triggers Lambda API call to create cleaning hold on shared calendar
  4. Team Visibility: CalendarSync.gs polls for new cleaning events and notifies operations team
  5. Audit Trail: All transactions logged to DynamoDB table boat-cleaning-dispatches with TTL of 90 days

Deployment Strategy

Created deployment scripts for easy updates:

  • /Users/cb/Documents/repos/tools/deploy_boat_cleaner.sh — Packages and deploys dispatch script as Lambda layer
  • CalendarSync.gs pushed directly via .clasp.json project configuration (project ID stored in /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/.clasp.json)

Deployment command:

bash /Users/cb/Documents/repos/tools/deploy_boat_cleaner.sh
# Uploads dispatch_boat_cleaner.py as Lambda layer
# Triggers CloudWatch test to verify connectivity

Key Design Decisions

Why Python over Node.js? The existing tooling ecosystem uses Python (campaign scheduler, stats collection), so dispatch logic fits naturally in that stack. Easier to maintain alongside qdn_consumer_blast.py