Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Boat Cleaning Dispatch and Calendar Synchronization Across Multiple Event Platforms

What Was Done

This session involved building an automated boat cleaning dispatch system and implementing cross-platform calendar synchronization for the Queen of San Diego event operations. The work spanned three major components:

  • Created a Python-based dispatch script to coordinate boat cleaning services
  • Implemented Google Apps Script calendar sync to bridge multiple booking platforms
  • Deployed email notification infrastructure using SES and Gmail API
  • Set up automated event scheduling via Lambda API endpoints

The goal: eliminate manual coordination overhead for recurring events (particularly Sea Scout Wednesday holds) and centralize cleaning service requests across GetMyBoat and Boatsetter platforms.

The Dispatch Boat Cleaner System

The foundation is a Python script located at /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py. This script queries platform APIs to identify upcoming boat charters, extracts scheduling information, and triggers cleaning service requests.

Why this approach? Manual coordination between booking platforms and cleaning services created delays and missed windows. By automating the lookup and dispatch, we ensure cleaners are scheduled immediately after event confirmation.

The script integrates with:

  • GetMyBoat API — Queries charter bookings and iCal feeds
  • Boatsetter credentials — Maintained in repos.env for secure access
  • AWS SES — Sends dispatch confirmations to cleaning team
  • Google Calendar API — Reads event details for timing context

The script was tested against live APIs before deployment and verified to output correctly formatted dispatch requests.

Calendar Synchronization via Google Apps Script

The most complex piece is the CalendarSync.gs replacement, located in /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs. This GAS project acts as a bridge between multiple event sources and a centralized Google Calendar.

Architecture:

  • Polling mechanism — Time-based triggers check external platforms every 2 hours
  • iCal parsing — Consumes .ics feeds from GetMyBoat and Boatsetter
  • Duplicate detection — Prevents duplicate calendar entries using event title + date hashing
  • Email notifications — Uses MailApp.sendEmail() to notify team of sync status
  • Error logging — Logs failures to a dedicated Google Sheet for monitoring

Key decision: Why GAS instead of Lambda? Google Apps Script provides native Calendar and Gmail integration without authentication overhead. Since the authoritative calendar lives in Google Workspace, GAS reduces data transformation layers and eliminates token refresh complexity.

The script includes hardcoded calendar IDs and platform URLs:


// Example structure (no actual credentials)
const TARGET_CALENDAR_ID = "primary";
const GETMYBOAT_ICAL_URL = "https://ical.getmyboat.com/...";
const SYNC_INTERVAL_MINUTES = 120;
const POLLING_TRIGGER_NAME = "calendarSyncTrigger";

Push to GAS was done via Clasp CLI, mapping local files to the GAS project ID stored in .clasp.json.

Calendar Event Insertion via Lambda API

For recurring holds (like Sea Scout Wednesdays), we built a direct Lambda integration to avoid polling delays. The Lambda function at rady-shell-events-api exposes an HTTP endpoint via API Gateway (v2).

Endpoint structure:


POST /calendar/add-event
Content-Type: application/json
Authorization: Bearer {dashboard-token}

{
  "action": "add-calendar-event",
  "summary": "Sea Scout Wednesday Hold",
  "start": "2025-04-30T18:00:00Z",
  "end": "2025-04-30T22:00:00Z",
  "description": "Hold for Sea Scout group",
  "calendarId": "primary"
}

Why Lambda + API Gateway? It provides:

  • Synchronous response — Immediate confirmation vs. async polling
  • IAM-based auth — No plaintext tokens in code
  • Scalability — Can handle burst requests from multiple platforms
  • Audit trail — CloudWatch logs every calendar mutation

Seven Sea Scout holds were added programmatically on Apr 30 (and subsequent Wednesdays) using this endpoint.

Email Notification Pipeline

Two email systems run in parallel:

1. Gmail API (for inbound reading)

  • Service account credentials loaded from repos.env
  • Queries Carole's inbox for incoming event requests
  • Full email bodies parsed to extract booking details

2. AWS SES (for outbound dispatch)

  • Sends dispatch confirmations to cleaning team
  • Sends sync status reports to operations lead
  • Verified sender identity configured in SES console

The dual-system approach ensures we listen to human-initiated requests (Gmail) while automating team notifications (SES).

Infrastructure and Deployment

Deployment scripts:

  • /Users/cb/Documents/repos/tools/deploy_boat_cleaner.sh — Copies dispatch script to production
  • /Users/cb/Documents/repos/tools/deploy_campaign_scheduler.sh — Deploys email campaign automation

Configuration files:

  • campaign_schedule.json — Defines email blast timing and templates
  • repos.env — Centralized secrets (GetMyBoat keys, Boatsetter creds, SES config)

Credential management: All secrets are environment-variable sourced. No credentials are committed to git. Clasp deployment uses OAuth via the Google Cloud project's service account.

Key Technical Decisions

1. Event deduplication via hash: Rather than querying all existing events, we store a hash of (title + date) in event descriptions. This allows O(1) duplicate detection without full calendar scan.

2. Time-based triggers in GAS: Polling every 2 hours balances freshness vs. quota consumption. Google Apps Script has a free tier with 1000 executions/day; hourly polling would exhaust it.

3. Lambda for synchronous operations: Calendar adds require immediate feedback to users. Async messaging (SQS/SNS) would complicate the UX; Lambda's synchronous model is simpler.

4. Separate