Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Managing Multi-Tenant Charter Operations: Automating Calendar Sync, Guest Pages, and Crew Dispatch

Running a charter booking operation involves coordinating multiple systems: calendar management, guest communications, crew scheduling, and waiver processing. This post details how we automated the weekend charter workflow for JADA (our charter operation) by synchronizing Google Calendar events with S3-hosted guest pages, DynamoDB crew records, and CloudFront distribution invalidations.

The Problem: Manual Coordination Across Disparate Systems

Before this session, charter operations required manual steps across several platforms:

  • Google Calendar held the source of truth for events, but crew assignments weren't always reflected
  • Guest pages lived in S3 (s3://sailjada-pages/guests/) but weren't automatically updated with crew or timing changes
  • DynamoDB tables (jada-events, jada-crew-dispatch) weren't synced with calendar updates
  • CloudFront caches weren't invalidated after page updates, causing stale content
  • Guest communication (emails and SMS) required manual lookup of contact info from multiple sources

A single calendar change meant updating four systems manually—and forgetting one risked guest confusion or crew no-shows.

Architecture: Layered Sync Between Google Calendar, S3, DynamoDB, and CloudFront

We built a workflow that treats Google Calendar as the source of truth and cascades updates down to guest-facing pages and crew dispatch:

Google Calendar (iCal + API)
    ↓
Lambda function (calendar event parser)
    ↓
DynamoDB (jada-events, jada-crew-dispatch)
    ↓
S3 guest pages (HTML templates rendered with event data)
    ↓
CloudFront (sailjada.com, invalidation on update)
    ↓
Guest/crew notification (email + SMS)

Each layer is independently testable, and failures at one layer don't cascade.

What We Did This Session

1. Recovered and Updated Calendar Events

We fetched JADA Internal calendar events using both iCal and the Google Calendar API. The iCal endpoint is more reliable for bulk reads (no auth expiration), while the API is used for writes:

curl -s "https://calendar.google.com/calendar/ical/JADA_INTERNAL_CALENDAR_ID/public/basic.ics" | grep -E "^(SUMMARY|DTSTART|DESCRIPTION|UID)"

We identified three weekend charters (May 30–31) and one June 14 event. The June 14 event had incorrect crew assignments (Darrell listed instead of Gene, with extra crew member Angelia). We patched this via the Google Calendar API, updating the event description field where crew names are stored as structured data.

2. Synchronized Guest Pages from S3 to HTML Templates

Guest pages live at s3://sailjada-pages/guests/ with naming convention {name}-{date}.html. For this session:

  • quinn-guest.html — Created new (May 30 charter)
  • jonathan-afternoon.html — Created new (May 31 charter)
  • danika-guest.html — Edited four times (May 30 charter, updated times from evening to 3–7 PM, added captain assignment)

Each page includes:

  • Boarding time — Parsed from calendar event description
  • Duration and end time — Calculated from event start/end
  • Captain and crew list — Pulled from DynamoDB jada-crew-dispatch table
  • Waiver link — Unique endpoint per guest (handled by Lambda at POST /waivers)
  • Photo upload code — Unique per charter, stored in DynamoDB

The page template uses a simple mustache-style substitution for dynamic content. We downloaded existing pages from S3, verified content accuracy (e.g., "arrive by 1:45 PM" for Danika), and re-uploaded after edits.

3. Updated DynamoDB Event Records

The jada-events table stores one record per charter with schema:

{
  "event_id": "XHQGMDH",  // Google Calendar event ID
  "event_name": "Quinn Sunset",
  "date": "2026-05-30",
  "start_time": "18:30",
  "end_time": "20:30",
  "duration_minutes": 120,
  "captain": "Darrell",
  "crew": ["Gene", "Sarah"],
  "guest_name": "Quinn",
  "guest_email": "quinn@example.com",
  "guest_phone": "+1-619-555-0001",
  "waiver_status": "pending",
  "photo_upload_code": "QNN001",
  "created_at": "2026-05-28T20:30:00Z"
}

For Quinn and Jonathan events (initially missing), we created records via DynamoDB PutItem. For Danika, we updated start_time and end_time (event was rescheduled), and toggled waiver_status to reflect minor participant requiring guardian signature.

4. Invalidated CloudFront Cache After S3 Updates

After uploading guest pages to S3, we invalidated the CloudFront distribution to ensure cached pages didn't serve stale content. CloudFront distribution ID: E2XYZABC123 (exact ID redacted for security).

aws cloudfront create-invalidation \
  --distribution-id E2XYZABC123 \
  --paths "/guests/quinn-guest.html" "/guests/jonathan-afternoon.html" "/guests/danika-guest.html"

Invalidation is crucial: without it, users would see the old "arrive by 5 PM" message for 15–60 minutes, depending on CloudFront's TTL. We set a TTL of 300 seconds (5 minutes) for guest pages to balance cache hits with freshness.

5. Routed Guest Contact Info via Multiple Sources

Guest contact information came from different sources depending on booking method:

  • Boatsetter bookings: Email forwarded to jada-bookings@sailjada.com, parsed for guest name and phone via Gmail API
  • Direct bookings: Stored in DynamoDB jada-guests table
  • Internal calendar descriptions: Captain and crew names embedded as structured text

For Jonathan's May 31 charter, contact info wasn't in the calendar event. We searched Gmail for the Boatsetter confirmation email (subject line matching the event ID XHQGMDH), extracted the phone number, and updated DynamoDB:

aws dynamodb update-item \
  --table-name jada-events \
  --key '{"event_id": {"S": "XHQGMDH"}}' \
  --update-expression "SET guest_phone = :