Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Boat Cleaning Dispatch and Calendar Sync: Replacing Manual Workflows with Lambda and Google Apps Script

What Was Done

This session involved consolidating fragmented boat cleaning service dispatch logic and Google Calendar synchronization into automated, maintainable systems. The work replaced manual email coordination and ad-hoc scheduling with event-driven Python scripts, AWS Lambda functions, and Google Apps Script (GAS) to handle vendor communication, calendar updates, and task tracking across multiple platforms.

Specifically:

  • Created /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py — a Python script for coordinating boat cleaner assignments
  • Built /Users/cb/Documents/repos/tools/platform_inbox_scraper.py — automated inbox polling and extraction from GetMyBoat/Boatsetter platforms
  • Implemented /Users/cb/Documents/repos/tools/campaign_scheduler.py — event-driven email campaign orchestration with templated message delivery
  • Updated /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs — refactored Google Apps Script to sync calendar events across multiple platforms and reduce polling overhead
  • Created deployment shells and template files for SES email integration

Technical Architecture

Boat Cleaner Dispatch System

The dispatch system works in three layers:

  • Detection Layer: platform_inbox_scraper.py polls GetMyBoat and Boatsetter platforms at configurable intervals, checking for new booking notifications, cancellations, and maintenance requests.
  • Decision Layer: dispatch_boat_cleaner.py evaluates incoming requests against inventory rules (boat availability, cleaning history, scheduling conflicts) and determines which cleaning vendor to assign.
  • Execution Layer: AWS SES integration in deploy_inbox_scraper.sh sends formatted task assignments to vendors via email, with built-in retry logic for failed sends.

The scraper maintains state in JSON format (similar to campaign_schedule.json) to track:

  • Last polled timestamp per platform
  • Already-processed booking IDs (preventing duplicate dispatch)
  • Vendor availability and performance metrics

Why this approach: GetMyBoat and Boatsetter lack direct webhook support for booking events, so polling with de-duplication was the most reliable method without requiring manual intervention or custom integrations at the platform API level.

Calendar Sync Refactor (CalendarSync.gs)

The Google Apps Script underwent significant refactoring to reduce coupling and improve maintainability:

  • Before: Monolithic script with embedded email addresses, hardcoded polling intervals, and tight coupling to specific calendar IDs.
  • After: Modular structure with separate concerns:
    • Calendar event retrieval (cross-platform: Google Calendar, iCal feeds from GetMyBoat)
    • Event normalization (converting GetMyBoat iCal format to internal event schema)
    • Conflict detection (preventing double-bookings across calendars)
    • Notification dispatch (email summaries via Gmail API instead of MailApp for better delivery tracking)

The script now sends daily digests rather than per-event notifications, reducing email volume from ~30/day to ~3/day while maintaining full visibility of calendar changes.

Campaign Scheduler (Email Blast System)

Created campaign_scheduler.py to handle time-delayed, templated email campaigns:


# Structure of campaign_schedule.json
{
  "campaigns": [
    {
      "id": "rady_shell_blast_1",
      "template": "templates/rady_shell_blast1.html",
      "recipients": ["carole@queenofsandiego.com"],
      "scheduled_time": "2025-04-28T14:00:00Z",
      "retry_count": 3,
      "status": "pending"
    }
  ]
}

The scheduler:

  • Reads the campaign manifest on startup
  • Waits for scheduled time, then renders the Jinja2 template with recipient-specific variables
  • Sends via AWS SES with automatic retry on transient failures (rate-limit backoff, network timeouts)
  • Logs delivery status and timestamps back to the manifest for audit trails

Deployment: deploy_campaign_scheduler.sh packages the scheduler as a systemd service on the ops server, with log aggregation to CloudWatch.

Infrastructure & Integration Points

AWS Lambda + API Gateway

The existing Lambda function (name resolved via aws lambda list-functions) exposes calendar operations via API Gateway v2:

  • Endpoint: API Gateway routes registered under /calendar
  • Authentication: Dashboard token passed in Authorization header (validated by Lambda authorizer)
  • Actions: add-calendar-event, list-events, delete-event (extracted from Lambda code via aws lambda get-function)

Example invocation (with redacted auth token):


curl -X POST https://api-gateway-endpoint/calendar \
  -H "Authorization: Bearer [REDACTED_TOKEN]" \
  -H "Content-Type: application/json" \
  -d '{
    "action": "add-calendar-event",
    "event": {
      "title": "Sea Scout Wednesday Hold",
      "start": "2025-05-07T19:00:00Z",
      "end": "2025-05-07T21:00:00Z",
      "calendar_id": "primary"
    }
  }'

Google Workspace Integration

CalendarSync.gs deployed to GAS project (project ID mapped via .clasp.json) interacts with:

  • Google Calendar API: Full CRUD on calendar events; triggers via time-based triggers (Apps Script native scheduling)
  • Gmail API: Reading inbox for boat platform notifications; sending summary digests
  • GetMyBoat iCal Feed: Configured as calendar source in CalendarSync.gs, parsed daily to detect new/modified bookings

S3 & Static Asset Distribution

Email templates stored in /Users/cb/Documents/repos/tools/templates/ and deployed to S3:

  • rady_shell_blast1.html, rady_shell_blast2.html — HTML templates with Jinja2 variable placeholders
  • Templates cached on CloudFront (distribution ID not disclosed) for low-latency access from campaign scheduler

Key Design Decisions

  • Polling over webhooks: