Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Integrating Charter Crew Data Across Calendar, DynamoDB, and Frontend Systems

This post documents a technical workflow for synchronizing crew assignment data from email confirmations through multiple backend systems and surfacing that information on the operations frontend. The challenge involved coordinating updates across Gmail, Google Calendar, DynamoDB, S3-hosted frontends, and CloudFront distributions.

What Was Done

We processed a confirmed charter event (Jennifer Sanderson, May 12, 2026) by:

  • Extracting crew assignments from Gmail confirmation emails in jadasailing@gmail.com
  • Updating the calendar event with captain, first mate, and second mate information
  • Syncing crew data to the ShipCaptainCrew DynamoDB table
  • Adding host/hostess assignments to both backend systems
  • Redeploying the ops frontend to reflect these changes

Technical Details: Data Extraction and Validation

The workflow began by querying Gmail using the unified OAuth token (which includes Gmail and Calendar scopes). We searched for messages matching "Jennifer Sanderson" within the last 30 days in jadasailing@gmail.com:

Gmail Query: "Jennifer Sanderson" newer_than:30d
Key emails reviewed:
- Booking report containing initial charter details
- May confirmed charters summary
- Key crew confirmation email from Carole (Captain, 1st Mate, 2nd Mate assignments)

Why this approach: Email remains the source of truth for crew confirmations at this organization. Rather than building a separate crew management UI, we validated that the Gmail-to-Calendar-to-Database pipeline could handle the administrative workflow people already use.

Crew Roles Confirmed:

  • Captain: Gene O'Neal
  • 1st Mate: Angela Wong
  • 2nd Mate: Dan Rich ("Danny")
  • Host/Hostess: Keely Hoyt

Infrastructure: Multi-System Synchronization

Google Calendar Event Updates

The calendar event for Jennifer Sanderson on May 12, 2026 was located and updated with crew assignments appended to the event description. This serves as a human-readable reference in Google Calendar while the database maintains structured data.

Updated field: Calendar event description
Action: Append crew role information
Reason: Maintains human-readable audit trail alongside automated systems

DynamoDB: ShipCaptainCrew Table

The ShipCaptainCrew DynamoDB table serves as the single source of truth for programmatic access to crew assignments. We verified the table structure and added/updated the Jennifer Sanderson charter record with:

  • Charter identifier (linked to calendar event)
  • Structured crew role assignments (captain, 1st_mate, 2nd_mate)
  • Host assignment for Keely Hoyt
DynamoDB Table: ShipCaptainCrew
Primary Key: charter_id (May 12 event identifier)
Attributes:
- charter_name: "Jennifer Sanderson Charter"
- date: "2026-05-12"
- captain: "Gene O'Neal"
- first_mate: "Angela Wong"
- second_mate: "Dan Rich"
- host: "Keely Hoyt"

Why DynamoDB: The ShipCaptainCrew system uses a Lambda-backed API Gateway for real-time crew lookups. DynamoDB provides the fast, structured queries needed for frontend rendering without the overhead of a relational database for this use case.

Frontend Deployment Pipeline

The ops frontend (ops.queenofsandiego.com) needed to surface the updated crew information. The deployment flow involved:

  1. Locate source files: /Users/cb/Documents/repos/sites/ops/index.html
  2. Verify S3 bucket configuration for ops domain
  3. Check CloudFront distribution settings (origin pointing to S3 ops bucket)
  4. Deploy updated content to S3
  5. Invalidate CloudFront cache for immediate propagation
S3 Bucket: ops.queenofsandiego.com (static site hosting)
CloudFront Distribution: ops subdomain distribution
Local source: /Users/cb/Documents/repos/sites/ops/
Deployment command example (no credentials shown):
  aws s3 sync ./index.html s3://ops.queenofsandiego.com/
  aws cloudfront create-invalidation --distribution-id [DIST_ID] --paths "/*"

Architecture decision: We use S3 + CloudFront rather than dynamic rendering because the ops page updates infrequently and benefits from edge caching. The frontend queries the ShipCaptainCrew API Gateway endpoint to fetch crew data dynamically, decoupling page deployment from API updates.

API Integration: CORS and Token Scopes

The frontend communicates with ShipCaptainCrew via API Gateway. We verified:

  • CORS headers permit requests from ops.queenofsandiego.com domain
  • OAuth token includes necessary scopes for both Gmail and Calendar operations
  • Lambda environment variables correctly point to the DynamoDB table and other resources
Verified scopes on unified token:
- Gmail API: read messages, list threads
- Google Calendar API: update events, read calendar

API Gateway CORS configuration:
- Allowed origin: https://ops.queenofsandiego.com
- Allowed methods: GET, POST, OPTIONS
- Allowed headers: Content-Type, Authorization

Key Decisions

Multi-system synchronization: Rather than treating Gmail, Calendar, and DynamoDB as separate systems, we created a validated pipeline where each layer serves a distinct purpose—Gmail for administrative confirmation, Calendar for human-readable scheduling, DynamoDB for programmatic crew lookups, and the frontend for customer-facing display.

Unified OAuth token: Requesting both Gmail and Calendar scopes on a single token simplified authentication complexity while maintaining security through token expiration and revocation policies.

Static frontend + dynamic API: Keeping the frontend static (S3 + CloudFront) while pulling crew data from a Lambda-backed API allows us to update crew assignments without redeploying the frontend, reducing deployment friction.

What's Next

Future improvements to this workflow could include:

  • Automated email parsing to extract crew assignments without manual validation
  • Webhook-based synchronization from Gmail to DynamoDB (via SNS/SQS)
  • Crew conflict detection (preventing double-booking across charters)
  • Customer-facing crew display on the public booking page

This integration demonstrates how legacy email-based workflows can coexist with modern cloud-native systems when the pipeline is designed with clear separation of concerns and validated data handoffs at each stage.