Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Integrating Multi-Service Charter Management: Gmail, Calendar, and DynamoDB Synchronization for Crew Assignment Workflows

This post documents a multi-system integration effort to synchronize crew assignments across Gmail, Google Calendar, and a custom DynamoDB-backed crew management system. The workflow demonstrates practical patterns for coordinating data across disparate services using OAuth2 token scopes and automated calendar/database updates.

What Was Done

The core objective was to establish a single source of truth for charter crew assignments. When crew confirmation emails arrive in jadasailing@gmail.com, the system needed to:

  • Parse crew role assignments from incoming emails
  • Update Google Calendar events with crew metadata
  • Persist crew assignments in the ShipCaptainCrew DynamoDB table
  • Ensure consistency across the ops subdomain frontend

For the Jennifer Sanderson May 12, 2026 charter, we confirmed crew assignments (Captain: Gene O'Neal, 1st Mate: Angela Wong, 2nd Mate: Dan Rich) from Carole's confirmation email, then propagated this data through both the calendar system and the backend crew database.

Technical Architecture

Gmail to Calendar Integration

The unified OAuth2 token was verified to contain both gmail.readonly and calendar scopes, enabling sequential queries:

Gmail Search Query: from:carole@... subject:"Jennifer Sanderson" after:2026-04-12
Parse Response: Extract crew role assignments from email body
Calendar Update: PATCH /calendar/v3/calendars/primary/events/{eventId}
Update Payload: Add crew names to event description field

This approach avoids custom API endpoints by leveraging Google's native APIs. The description field was chosen as the update target because it's human-readable in calendar views while preserving the event's primary scheduling data.

DynamoDB Crew Persistence Layer

The ShipCaptainCrew system maintains a DynamoDB table with the following schema structure:

  • Partition Key: Charter event identifier (e.g., "jennifer-sanderson-2026-05-12")
  • Crew Role Attributes: Captain, FirstMate, SecondMate (stored as separate fields or nested objects)
  • Host/Hostess Field: Additional role for event hosts (e.g., Keely Hoyt)
  • Metadata: Timestamps, confirmation status, email source tracking

Updates to this table require the ShipCaptainCrew Lambda function's IAM execution role to have dynamodb:UpdateItem permissions on the specific table resource.

Frontend Integration Points

The ops subdomain (ops.queenofsandiego.com) serves as the primary UI for viewing crew assignments. The architecture spans:

  • Source Files: /Users/cb/Documents/repos/sites/ops/index.html (local development)
  • S3 Bucket: Hosting the compiled ops site (exact bucket name: ops-related resource in AWS account)
  • CloudFront Distribution: Edge caching and SSL termination for ops.queenofsandiego.com
  • ShipCaptainCrew API Gateway: Backend endpoint queried by frontend for crew data

The frontend makes cross-origin requests to the ShipCaptainCrew API Gateway. CORS headers were verified to permit requests from the ops domain origin.

Infrastructure and Deployment

S3 and CloudFront Workflow

After modifying the local ops site files, the deployment process involved:

# Build and upload to S3
aws s3 sync /Users/cb/Documents/repos/sites/ops s3://[ops-bucket-name]/ --delete

# Invalidate CloudFront cache to serve fresh content
aws cloudfront create-invalidation \
  --distribution-id [OPS_CLOUDFRONT_DIST_ID] \
  --paths "/*"

This two-step process ensures the CDN doesn't serve stale crew assignment data to users.

ShipCaptainCrew Lambda Environment

The crew management Lambda function sources its API Gateway URL from environment variables, enabling seamless routing from the ops frontend. Environment variables were checked to confirm the correct endpoint for DynamoDB queries:

Environment Variables Checked:
- SCC_API_GATEWAY_URL: https://[api-id].execute-api.[region].amazonaws.com/[stage]
- DYNAMODB_TABLE_NAME: ShipCaptainCrew (or similar convention)
- CORS_ALLOWED_ORIGINS: ops.queenofsandiego.com and related domains

CloudFront Origins Configuration

The ShipCaptainCrew CloudFront distribution was configured with an API Gateway origin. This layer provides:

  • Edge caching for crew data (cache TTL: verified in distribution settings)
  • GZIP compression for JSON responses
  • Security headers injection at the edge

Key Decisions and Rationale

Using Calendar Description for Crew Data

Decision: Store crew assignments in the Google Calendar event description rather than creating custom event fields.

Rationale: Calendar events have limited custom extensibility without proprietary integrations. The description field is:

  • Readable in all calendar clients (web, mobile, Outlook)
  • Indexable by Gmail search and Google Drive search
  • Non-destructive to other event data (attendees, times, location)
  • Parseable by the frontend without additional API calls

DynamoDB as Source of Truth for Backend Operations

Decision: Replicate crew data to DynamoDB rather than querying Calendar API on every request.

Rationale: Calendar API has rate limits and higher latency. DynamoDB provides:

  • Consistent sub-100ms read/write latency
  • Partition flexibility (crew lookup by charter ID, by date range, by crew member)
  • Offline-first capability for ops team tools
  • Audit trail via DynamoDB Streams (if enabled)

Unified OAuth2 Token Strategy

Decision: Use a single OAuth2 token with Gmail and Calendar scopes rather than separate service accounts.

Rationale: Reduces credential management complexity and ensures consistent permission auditing. Token scopes verified:

  • gmail.readonly: Search and read emails without modification
  • calendar: Full calendar read/write (could be narrowed to calendar.events)

Workflow Example: Jennifer Sanderson Charter

The May 12, 2026 charter exemplified the full workflow:

  1. Email Received: Carole's crew confirmation email to jadasailing@gmail.com listing Gene O'Neal (Captain), Angela Wong (1st Mate), Dan Rich (2nd Mate)
  2. Gmail Parse: Searched with filter: