Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Integrating Charter Crew Data Across Multiple Systems: Gmail, Calendar, and DynamoDB

During a recent development session, we faced a common operational challenge: crew assignment information existed in email, needed to be surfaced in calendar events, and ultimately had to be synchronized with our ShipCaptainCrew (SCC) backend system. This post details how we orchestrated data flow across three distinct systems to maintain a single source of truth for charter crew assignments.

The Problem

A charter booking confirmation existed in our Gmail inbox with crew role assignments, but this critical information wasn't reflected in our calendar system or backend database. When operations staff needed to view crew assignments, they had to manually cross-reference emails, which introduced data inconsistency risks and operational friction.

Architecture Overview

Our tech stack comprises:

  • Gmail + Google Calendar API: Source of crew confirmation emails and calendar event storage
  • ShipCaptainCrew (SCC) Backend: DynamoDB table storing authoritative crew-charter relationships
  • Frontend: Ops subdomain served from S3 via CloudFront, consuming SCC API Gateway

The integration workflow required connecting these systems in sequence: extracting crew data from Gmail → updating Google Calendar → persisting to DynamoDB via Lambda → exposing through API Gateway → rendering on frontend.

Technical Implementation

Step 1: Gmail Data Extraction with Scoped OAuth

We verified OAuth token scopes to ensure the unified token included both gmail.readonly and calendar permissions. This allowed a single authentication mechanism to access both Gmail and Calendar APIs.

The Gmail search command filtered for messages from a specific sender (Carole's crew confirmation emails) within the last 30 days:

Search criteria: from:crew-confirmation@jadasailing.com 
                after:2024-04-XX 
                subject:crew assignments

This query returned structured email data containing crew role assignments. From the confirmation emails, we extracted three critical fields:

  • Captain: Gene O'Neal
  • 1st Mate: Angela Wong
  • 2nd Mate: Dan Rich

Why this approach: Gmail's native search API is more reliable than parsing raw email content, and filtering by date ensures we're working with recent confirmations rather than stale historical data.

Step 2: Google Calendar Event Synchronization

We located the specific calendar event (Jennifer Sanderson charter on May 12) and updated its description field with structured crew assignment data. The update operation used the Google Calendar API with the event ID and calendar identifier.

The event object was modified to include crew information in a parseable format within the event description:

{
  "eventId": "jennifer_sanderson_may12_charter",
  "summary": "Queen of San Diego - Jennifer Sanderson Charter",
  "description": "Crew: Captain Gene O'Neal, 1st Mate Angela Wong, 2nd Mate Dan Rich",
  "updated": true
}

Design decision: Rather than creating separate custom fields (which would require custom event properties not supported by Google Calendar), we embedded crew data in the event description. This maintains backwards compatibility while making crew assignments human-readable for calendar viewers.

Step 3: DynamoDB Persistence via ShipCaptainCrew System

The ShipCaptainCrew system maintains a DynamoDB table (exact table name withheld for security) with the following structure:

Table: ShipCaptainCrew
Primary Key: charterID + crewMemberID
Attributes:
  - charterID (String): Identifier for the specific charter
  - crewMemberID (String): Unique crew identifier
  - role (String): Captain | FirstMate | SecondMate
  - name (String): Crew member full name
  - assignmentDate (ISO8601): When assignment was made

We created three separate item entries for the Jennifer Sanderson charter—one for each crew member—ensuring the DynamoDB table became the system of record. This enabled downstream systems to query crew assignments without parsing emails or calendar events.

Architectural rationale: DynamoDB provides strong consistency (with single-region setup) and scales horizontally. By making it the authoritative data store, we avoid sync conflicts and enable real-time crew assignment queries through the Lambda-backed API Gateway.

Step 4: Frontend Integration via API Gateway

The ops subdomain frontend at /Users/cb/Documents/repos/sites/ops/index.html needed to display crew assignments. We verified the SCC API Gateway endpoint and confirmed CORS headers were properly configured to allow requests from ops.queenofsandiego.com.

The frontend makes calls to the SCC API Gateway endpoint with the charter ID as a query parameter:

GET /scc/api/charters/{charterID}/crew
Host: api.shipCaptainCrew.internal
Accept: application/json

The Lambda function behind the API reads from the DynamoDB table and returns crew assignments in JSON format, which the frontend renders in the charter details view.

Infrastructure & Deployment

S3 & CloudFront Configuration:

  • Ops page source: Stored locally, deployed to S3 bucket (specific bucket name withheld)
  • CloudFront distribution: Configured with S3 origin, invalidation strategy used after content updates
  • Cache invalidation: Performed on deployment to ensure fresh crew data propagates immediately

We deployed the updated index.html file four times during this session as we refined the crew data rendering logic. Each deployment triggered CloudFront cache invalidation to push changes to edge locations.

Lambda Environment Configuration:

The SCC Lambda functions read their DynamoDB table name and API Gateway endpoint from environment variables, avoiding hardcoded configuration. This enables environment parity between staging and production.

Key Design Decisions

  • Multi-system synchronization: Rather than creating a new system, we synchronized existing systems (Gmail → Calendar → DynamoDB) to maintain architectural simplicity.
  • DynamoDB as source of truth: By making DynamoDB the authoritative store, we decoupled the frontend from email/calendar dependencies.
  • Scoped OAuth tokens: Using a single unified token with multiple API scopes reduced authentication complexity and credential management overhead.
  • CORS-enabled API Gateway: Enabling CORS on the SCC API eliminated frontend fetch errors while maintaining security boundaries.

What's Next

Future improvements should include:

  • Crew assignment automation: Trigger Lambda functions automatically when confirmation emails arrive, eliminating manual synchronization.
  • Event-driven architecture: Implement EventBridge to listen for calendar event changes and auto-update DynamoDB.
  • Crew member versioning: Add timestamps and version fields to DynamoDB to track crew assignment history.
  • Frontend caching: Implement Redux or similar state management to cache crew data locally and reduce API calls.

This integration pattern demonstrates how to bridge disparate systems while maintaining data consistency and operational visibility. By leveraging each system's strengths (Gmail for unstructured crew communications, Calendar for team visibility, DynamoDB for queryable state), we created a robust foundation for crew management at scale.