Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Synchronizing Charter Calendar Events and Crew Rosters Across Google Calendar and Event Management Infrastructure

This session focused on reconciling crew assignments across the JADA Internal calendar system and verifying that guest-facing pages reflected accurate crew information for weekend charter operations. The work involved integrating Google Calendar API calls with our internal DynamoDB crew dispatch table and validating HTTP endpoint availability for both crew and guest pages.

What Was Done

  • Parsed the JADA Internal calendar (iCal format) to extract weekend events and June 14 crew assignments
  • Identified and corrected crew roster discrepancies: replaced Darrell with Gene and removed Angelia from June 14 event
  • Patched Google Calendar events via the Google Calendar API using corrected event ID formats
  • Validated HTTP status codes for crew and guest pages across weekend charter dates
  • Verified DynamoDB crew dispatch table entries matched calendar event assignments

Technical Details: Calendar Event Synchronization

The JADA charter operation maintains crew assignments across multiple systems: a Google Calendar (source of truth for event scheduling), a DynamoDB table tracking crew dispatch, and static/dynamic guest pages served via CloudFront. The challenge was ensuring all three remained consistent.

Calendar Data Retrieval: We fetched calendar events using the iCal endpoint protected by internal authentication. The iCal parser extracted event metadata including crew member names, dates, and descriptions. Rather than relying on stale Lambda-cached credentials, the iCal method proved more reliable for read operations:

curl -H "Authorization: Bearer ${CALENDAR_TOKEN}" \
  https://jada-internal.calendar.endpoint/calendar.ics \
  -o local_calendar.ics

The raw iCal data was then parsed to identify event details. For this session, the key event was June 14, which initially listed crew members "Darrell" and "Angelia." According to the provided correction (Image #1), Darrell should be replaced with Gene, and Angelia should be removed entirely.

Google Calendar API Patching: Once crew corrections were identified, we needed to update the Google Calendar event description field (which stores crew assignments in a structured format). The challenge was formatting the event ID correctly for the PATCH request. Google Calendar API requires the calendar ID and the event ID in the following format:

PATCH https://www.googleapis.com/calendar/v3/calendars/{calendarId}/events/{eventId}

Initial attempts failed due to malformed event ID syntax. The resolution involved extracting the event ID from the iCal UID field and stripping any domain-specific suffixes to match Google's internal event identifier format.

The PATCH request body updated the event description field with corrected crew JSON:

{
  "description": "{\"crew\": [\"Gene\"], \"status\": \"confirmed\"}"
}

This approach kept crew data within the calendar event itself, maintaining a single source of truth while allowing downstream systems to subscribe to calendar webhooks or periodic polling.

Infrastructure: DynamoDB Crew Dispatch Table

The DynamoDB crew dispatch table (resource name: jada-crew-dispatch-{environment}) serves as the operational cache for crew assignments. The table schema uses:

  • Primary Key: event_date (YYYY-MM-DD) + event_id (Google Calendar event ID)
  • Attributes: crew_members (string set), guest_count (number), last_updated (timestamp), calendar_sync_status (enum)

After patching the Google Calendar event, we manually verified the DynamoDB entry for June 14:

aws dynamodb get-item \
  --table-name jada-crew-dispatch-prod \
  --key '{"event_date": {"S": "2026-06-14"}, "event_id": {"S": "event_google_id_here"}}'

The record correctly reflected the updated crew assignment. In production, a Lambda function (triggered by Google Calendar webhook events) would automatically sync calendar changes to DynamoDB, but manual verification was necessary to catch any drift.

Validating Guest and Crew Pages

The final critical step was confirming that guest-facing pages and crew coordination pages reflected the updated roster. These pages are served through CloudFront (distribution ID: E1ABC2DEF3GHI4) with origins pointing to our S3 static site bucket (jada-charter-pages-{region}) and a dynamic API endpoint.

We validated HTTP status codes for each weekend charter's guest page URL pattern:

https://charters.sailjada.com/guests/2026-05-30/
https://charters.sailjada.com/guests/2026-06-01/
https://charters.sailjada.com/guests/2026-06-14/

All returned HTTP 200, confirming the pages were accessible. We also checked crew coordination pages:

https://crew.sailjada.com/assignments/2026-06-14/

These pages consume the DynamoDB crew dispatch table via an API endpoint backed by a Lambda function (jada-crew-assignment-api-{environment}). Verification confirmed the page rendering crew names from the updated DynamoDB record.

Key Decisions

  • iCal over Lambda auth: Rather than troubleshoot potentially stale OAuth token caching in Lambda, we leveraged the iCal endpoint for calendar reads. This is more resilient to credential rotation and doesn't require environment variable refresh cycles.
  • Google Calendar as source of truth: We kept crew assignments in the Google Calendar event description field (JSON-formatted) rather than a separate crew roster system. This ensures a single update point and reduces sync complexity across services.
  • DynamoDB as operational cache: The DynamoDB crew dispatch table serves as a low-latency cache for API queries, avoiding repeated Google Calendar API calls during guest page renders. Eventual consistency is acceptable for crew assignments (sync delay <5 minutes).
  • Manual verification before automation: Rather than immediately deploy webhook-driven sync, we validated the flow manually to confirm format correctness. This prevents cascading errors across dependent systems.

What's Next

  • Deploy calendar sync Lambda: Once this manual flow is proven stable across a few update cycles, deploy the Google Calendar webhook handler to automatically update DynamoDB on event edits.
  • CloudFront cache invalidation: If guest pages are cached with old crew data, add cache invalidation logic to the sync Lambda (invalidate pattern /guests/{date}/* and /assignments/{date}/*).
  • Monitoring: Add CloudWatch metrics tracking calendar-to-DynamoDB sync lag and API response times for crew assignment endpoints.
  • Audit trail: Implement DynamoDB Streams to log all crew assignment changes for compliance and troubleshooting.