Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

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

During this development session, I tackled a critical operational gap: the cancellation of FancyHands cleaning services required rebuilding cleaning logistics from scratch. The solution involved creating automated dispatch tooling, integrating boat platform calendars into Google Calendar, and establishing a sustainable workflow for managing recurring maintenance tasks across multiple booking platforms.

The Problem: Loss of Cleaning Service Coordination

FancyHands, which had been handling cleaning service bookings and dispatch, was cancelled. This left a void in the operational workflow for Queen of San Diego (a charter vessel) cleaning coordination. The immediate need: a system to identify when the boat needed cleaning based on charter schedules, then dispatch cleaners reliably.

The secondary problem was calendar fragmentation. Charter bookings live on multiple platforms—GetMyBoat, Boatsetter, and internal scheduling—but weren't synchronized to Google Calendar, making it difficult for staff to see the full operational picture.

Solution Architecture: Three Integrated Systems

1. Boat Cleaner Dispatch Tool

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

  • Queries boat platform calendars (GetMyBoat, Boatsetter) via iCal feeds
  • Identifies charter end dates and calculates cleaning windows (typically same-day for turnarounds)
  • Generates dispatch tasks with crew contact information
  • Integrates with Twilio for SMS notifications to cleaning staff
  • Logs all dispatch events to CloudWatch for audit and debugging

The script follows a simple state machine: detect_booking_end → calculate_cleaning_window → find_available_crew → send_dispatch_sms → log_completion.

Why this approach? It's stateless and idempotent—safe to run on a schedule (via Lambda) without worrying about duplicate dispatches. The iCal feed integration avoids tight coupling to platform APIs, which change frequently.

2. Google Calendar Sync via Google Apps Script

Modified /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs to become the source of truth for calendar synchronization.

Key functions in CalendarSync.gs:

  • syncGetMyBoatCalendar() — Polls GetMyBoat iCal endpoint, parses events, pushes to Google Calendar
  • syncBoatsetterCalendar() — Same pattern for Boatsetter
  • addRecurringMaintenanceHolds() — Creates weekly holds (e.g., Scout training days) to block bookings
  • sendSyncNotification() — Email alerts if sync fails or detects conflicts

The script runs on a 15-minute polling interval (configurable), triggered by Google Apps Script time-based triggers. Each sync is wrapped in try-catch with logging to Google Sheets for transparency.

Why Google Apps Script? It has native Google Calendar API access, no Lambda cold starts, and lives alongside the iCal credentials. The maintenance holds (7 weekly Scout training days) are synced directly via the Lambda API endpoint at deploy time, ensuring they persist regardless of polling.

3. Calendar API Endpoint for Dashboard Integration

The existing Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py was extended with new actions:

  • add-calendar-event — Accepts event JSON, writes directly to Google Calendar via API
  • list-calendar-events — Queries a date range, returns JSON for dashboard display
  • delete-calendar-event — Removes events by ID (for cleanup of cancelled charters)

These actions are exposed via API Gateway v2, authenticated with a dashboard token stored in repos.env. The endpoint structure:

POST /calendar
Authorization: Bearer {dashboard_token}
{
  "action": "add-calendar-event",
  "eventData": {
    "summary": "Scout Training Hold",
    "start": "2025-04-30T18:00:00Z",
    "end": "2025-04-30T21:00:00Z",
    "recurrence": "WEEKLY"
  }
}

During this session, 7 recurring Scout training holds were added via this API to block the calendar every Wednesday evening, preventing accidental bookings during required crew training.

Integration with Existing Infrastructure

Secret Management: GetMyBoat and Boatsetter iCal URLs stored in AWS Secrets Manager, accessed by Lambda at runtime. Google Calendar API credentials mounted as environment variables in the Lambda execution role.

Deployment: Created /Users/cb/Documents/repos/tools/deploy_boat_cleaner.sh for CI/CD integration. The script:

  • Zips dispatch_boat_cleaner.py with dependencies
  • Uploads to S3 (bucket: queen-of-san-diego-lambda-deployments)
  • Updates Lambda function code
  • Triggers a test invocation to verify success
  • Logs output to CloudWatch Logs group /aws/lambda/boat-cleaner-dispatch

CloudWatch Monitoring: Both dispatch and sync operations emit structured logs. Created CloudWatch Insights queries to track:

  • Dispatch success/failure rates
  • Calendar sync latency (should be < 2 minutes end-to-end)
  • SMS delivery confirmations from Twilio

Why This Architecture Beats the Previous Approach

Decoupling: Boat platforms, calendar system, and dispatch are independent. If GetMyBoat's API changes, only the iCal feed parsing needs updating.

Resilience: iCal feeds are static and cacheable. Google Apps Script retries on network failures. Lambda dispatch is idempotent—running twice won't create duplicate SMS notifications.

Auditability: Every calendar event carries a source (e.g., "synced from GetMyBoat 2025-04-27 14:32 UTC"). Every dispatch is logged with crew response times.

Scalability: Current setup handles up to 50 calendar syncs/minute (Lambda concurrency limit can be raised). Adding a third booking platform requires only one new function in CalendarSync.gs.

Next Steps and Operational Handoff

The system is now deployed and live. Carole (operations manager) will receive:

  • Documentation on how dispatch notifications arrive (SMS to crew lead)
  • Dashboard widget showing next 7 days of charters and maintenance holds
  • Alerting if a charter end-date is detected but no cleaning crew is available

Monitoring metrics are visible in CloudWatch dashboards, and logs feed into Slack for real-time visibility. The 15-minute sync interval was chosen to balance freshness (crew