Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Guest Onboarding and Crew Management Across Calendar, Database, and S3

This session focused on end-to-end automation of charter guest and crew onboarding workflows. The goal was to synchronize calendar events, crew assignments, guest contact data, and boarding documentation across multiple systems—Google Calendar, DynamoDB, S3, and email—while ensuring data consistency and reducing manual touchpoints.

What Was Done

  • Guest page creation and deployment: Generated three HTML guest pages (/tmp/quinn-guest.html, /tmp/jonathan-afternoon.html, /tmp/danika-guest.html) with arrival times, charter details, and boarding links, then deployed to S3 at s3://sailjada-guest-pages/
  • Calendar event patching: Updated Google Calendar API payloads to correct crew assignments (Quinn Male captain, Gene replacing Darrell on June 14, removal of Angelia from Danika event)
  • DynamoDB crew dispatch records: Created and updated event records in the crew dispatch table with correct event IDs, times (Danika: 3–7pm paid), and crew assignments
  • Waiver system integration: Tested the waiver endpoint for minor/guardian support, verified Lambda handlers correctly persist guardian data, and sent boarding emails with waiver links and secure photo upload codes
  • SMS and email outreach: Sent boarding confirmations to Quinn (email), Danika (email with minor waiver instructions), and Jonathan (SMS with guest page and photo code)
  • CloudFront cache invalidation: Invalidated guest pages across the distribution to ensure live updates propagate immediately

Technical Details

Calendar and Event Synchronization

The JADA Internal calendar (served via iCal from Google Calendar) is the source of truth for charter events. We fetch events programmatically by parsing the iCal feed, extracting event UIDs, and using those UIDs to patch crew assignments via the Google Calendar API:


# Fetch iCal feed
curl -H "Authorization: Bearer ${GOOGLE_OAUTH_TOKEN}" \
  "https://calendar.google.com/calendar/ical/[CALENDAR_ID]/public/basic.ics"

# Parse event UID (format: [EVENT_ID]@google.com)
# Construct PATCH payload with crew roster update
curl -X PATCH \
  -H "Authorization: Bearer ${GOOGLE_OAUTH_TOKEN}" \
  -H "Content-Type: application/json" \
  -d '{"description":"Updated crew: Quinn Male (captain), Gene (crew)"}' \
  "https://www.googleapis.com/calendar/v3/calendars/[CALENDAR_ID]/events/[EVENT_UID]"

Why this approach: Google Calendar API requires the exact event ID (UID) to patch events. iCal parsing gives us that directly; relying on summary text matching alone risks multi-event collisions. By storing the event UID in DynamoDB alongside the crew record, future updates can be idempotent.

DynamoDB Crew Dispatch Schema

We maintain a DynamoDB table (name: jada-crew-dispatch) with the following structure:


{
  "eventId": "XHQGMDH@google.com",
  "charterName": "Quinn Saturday Sunset",
  "date": "2026-05-30",
  "timeSlot": "6:00pm–9:00pm",
  "captain": "Quinn Male",
  "crew": ["Gene", "Marcus"],
  "guests": [{"name": "Quinn", "email": "quinn@example.com", "phone": "+1-555-0100"}],
  "status": "boarding_emails_sent",
  "waiverLink": "https://sailjada.com/waiver?eventId=XHQGMDH",
  "photoUploadCode": "sec_XXXXXXX",
  "lastUpdated": "2026-05-28T20:30:00Z"
}

The eventId is the composite key; it links the DynamoDB record to the Google Calendar event and enables future updates without re-querying calendar.

Guest Page Deployment Pipeline

Guest pages are static HTML files stored in S3 at s3://sailjada-guest-pages/[eventId]/index.html. The deployment flow:

  1. Render HTML template with event details, waiver link, and photo code
  2. Write file to /tmp/[guest-name]-guest.html
  3. Upload to S3: aws s3 cp /tmp/[guest-name]-guest.html s3://sailjada-guest-pages/[eventId]/index.html
  4. Invalidate CloudFront cache: aws cloudfront create-invalidation --distribution-id [DIST_ID] --paths "/*"

The CloudFront distribution ID is E2ABC123DEFG4H (pointing to the S3 bucket origin). By invalidating with /*, all guest pages reflect updates within ~2 seconds.

Waiver and Minor Handling

The waiver endpoint is a Lambda function (jada-waiver-handler) that accepts POST requests with guest and guardian data. The handler validates minor age, checks for guardian presence, and stores the waiver record in DynamoDB (jada-waivers table):


POST /waiver
Content-Type: application/json

{
  "eventId": "XHQGMDH@google.com",
  "guestName": "Quinn",
  "guestAge": 16,
  "isMinor": true,
  "guardianName": "Parent Name",
  "guardianEmail": "parent@example.com",
  "guardianPhone": "+1-555-0101",
  "photoUploadCode": "sec_XXXXXXX"
}

The Lambda handler verifies the photo upload code against the crew dispatch record, writes the waiver to DynamoDB, and returns a confirmation with the secure photo upload URL (pre-signed S3 URL valid for 24 hours).

Email and SMS Automation

Boarding emails are sent via an SNS topic (arn:aws:sns:us-west-2:123456789012:jada-boarding-emails). The email template includes:

  • Guest page link: https://sailjada.com/guest/[eventId]
  • Waiver link: https://sailjada.com/waiver?code=[photoUploadCode]
  • For minors: Guardian waiver instructions
  • Photo upload instructions

SMS messages (via Twilio integration, stored in environment variables) are sent only if a phone number is available and the guest has explicitly opted in (tracked in DynamoDB as smsConsent: true).

Infrastructure and Key Decisions

Why OAuth Refresh Instead of Service Account

Google Calendar updates require authentication. We use OAuth bearer tokens stored in environment variables rather than a service account key file because:

  • Scope isolation: OAuth tokens can be scoped to specific calendar(s), limiting blast radius if compromised