Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Event Calendar Synchronization and Cleaning Service Dispatch: Infrastructure Refactor for Multi-Site Operations

What Was Done

This session tackled a critical infrastructure gap: replacing deprecated Google Apps Script (GAS) automation with Lambda-based calendar synchronization and implementing a dispatch system for recurring cleaning services across multiple event venues. The work involved:

  • Migrating calendar event management from Google Apps Script to AWS Lambda with API Gateway endpoints
  • Implementing a boat platform credential mapping system for multi-vendor integration (GetMyBoat, Boatsetter)
  • Building a Python-based dispatch scheduler for cleaning service coordination
  • Establishing calendar hold management for recurring sea scout activities

Technical Architecture Changes

Calendar Synchronization Infrastructure

The primary component is located at /Users/cb/Documents/repos/sites/queenofsandiego.com/rady-shell-events/apps-script-replacement/CalendarSync.gs. This Google Apps Script handles bi-directional calendar synchronization with the following workflow:

  • Event Polling: Executes on a time-based trigger to periodically check for new events in the source calendar
  • Calendar ID Mapping: Maintains references to multiple Google Calendar IDs for different event types (birthday parties, afternoon sessions, scout holds)
  • Notification System: Sends email notifications via Gmail API to stakeholders when calendar changes occur

The script was updated to correct polling intervals and email delivery mechanisms. Rather than relying on the deprecated sendEmail() method in certain contexts, we now route through explicitly configured Gmail API calls with proper error handling.

Lambda-Based Calendar API

The Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py exposes calendar operations via API Gateway. The endpoint structure supports:

  • add-calendar-event — Creates events with title, start time, end time, and optional description
  • list-calendar-events — Retrieves events within a date range for dashboard display
  • update-calendar-event — Modifies existing event details

Authentication is managed through API Gateway with environment variable-based token validation. The Lambda function reads Google Calendar API credentials from repos.env and uses service account authentication to bypass OAuth flows in automated contexts.

Cleaning Service Dispatch System

A new Python module at /Users/cb/Documents/repos/tools/dispatch_boat_cleaner.py manages recurring cleaning task creation. The system:

  • Reads a JSON schedule from /Users/cb/Documents/repos/tools/campaign_schedule.json
  • Generates dispatch commands for boat platform APIs (GetMyBoat, Boatsetter)
  • Logs task status to CloudWatch for monitoring and audit trails
  • Integrates with the dashboard via the Lambda API to create "hold" calendar events that block off cleaning windows

The dispatch script runs as a scheduled Lambda function with a cron-based trigger. This replaces manual email-based task creation with deterministic, auditable automation.

Multi-Site Campaign Infrastructure

Two parallel marketing automation systems were established:

Queen of San Diego Campaign (Rady Shell Events)

  • Inbox Scraper: /Users/cb/Documents/repos/tools/platform_inbox_scraper.py polls event booking platforms for incoming inquiries
  • Email Templates: Located in /Users/cb/Documents/repos/tools/templates/ (rady_shell_blast1.html, rady_shell_blast2.html)
  • Campaign Scheduler: /Users/cb/Documents/repos/tools/campaign_scheduler.py orchestrates template rendering and SES delivery
  • Deployment: Shell scripts (deploy_inbox_scraper.sh, deploy_campaign_scheduler.sh) handle CloudFormation stack updates and Lambda code refreshes

QuickDumpNow Consumer Outreach

  • Blast System: /Users/cb/Documents/repos/sites/quickdumpnow.com/tools/qdn_consumer_blast.py sends bulk marketing emails with template variants
  • Analytics: qdn_stats.py tracks open rates, click-through rates, and unsubscribe events by parsing SES bounce/complaint notifications
  • Compliance: Unsubscribe landing page at /Users/cb/Documents/repos/sites/quickdumpnow.com/unsubscribe/index.html integrates with DynamoDB for preference management

Key Technical Decisions

Lambda vs. Google Apps Script

We chose to move calendar operations to Lambda because:

  • GAS quota limits were being exceeded during peak event seasons
  • Lambda offers better integration with AWS infrastructure (CloudWatch, IAM, VPC)
  • Python provides stronger typing and testing frameworks than GAS's JavaScript variant
  • API Gateway allows external systems (dashboard, third-party integrations) to trigger calendar updates without GAS authentication overhead

Email Delivery Routing

SES (Simple Email Service) was standardized for transactional emails across all systems rather than mixing Gmail API and SES. This provides:

  • Unified bounce/complaint tracking
  • Better IP reputation management with warm-up schedules
  • Template version control via CloudFormation
  • Cost predictability with per-message pricing

Credential Management Pattern

Platform credentials (GetMyBoat, Boatsetter API keys) are stored in a structured format within repos.env rather than scattered across individual script files. The dispatch system reads these at runtime via environment variable substitution, enabling:

  • Credential rotation without code changes
  • Easy audit logging of which systems accessed which platforms
  • Staging/production credential switching via deployment environment

Operational Integration Points

The dashboard at /tmp/dashboard_index.html now displays:

  • Calendar holds created by the dispatch system (visual blocking)
  • Inbox scraper results with unread inquiry counts
  • Campaign scheduler status (queued, sent, bounced)
  • Real-time Lambda invocation metrics from CloudWatch

Team members can trigger calendar updates directly via the dashboard's API interface, which validates requests against the token stored in repos.env before forwarding to Lambda.

What's Next

Immediate follow-ups:

  • Implement dead-letter queue (DLQ) handling for failed calendar API requests to prevent silent failures
  • Add CloudWatch alarms for inbox scraper timeout events (threshold: >5