Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Integrating Personnel Management with Crew Scheduling: Unifying Gmail, Calendar, and DynamoDB for Charter Operations

This post walks through a real-world integration challenge: synchronizing crew assignments from email confirmation workflows into both Google Calendar and a DynamoDB-backed crew management system, while adding a time-tracking interface to the crew-facing dashboard.

The Problem Statement

The Queen of San Diego charter operation was managing crew assignments across multiple disconnected systems:

  • Gmail inbox containing crew confirmation emails with captain/mate assignments
  • Google Calendar events for individual charters lacking crew role details
  • ShipCaptainCrew (SCC) DynamoDB backend without charter crew metadata
  • No time-tracking capability on the crew-facing web application

For the Jennifer Sanderson charter on 2026-05-12, crew assignments existed only in email confirmations from the operations team. This required manual propagation to calendar events and the backend system.

Technical Architecture Overview

The system comprises several interconnected services:

  • Gmail API: Primary source of truth for crew confirmations
  • Google Calendar API: Scheduling system needing enrichment with crew role data
  • DynamoDB (ShipCaptainCrew table): Backend crew and charter data store
  • Lambda (SCC backend): API handlers at `/tmp/scc_lambda/lambda_function.py`
  • S3 + CloudFront: Static assets for ops.queenofsandiego.com and crew dashboard

Phase 1: Email Mining and Crew Role Extraction

The initial workflow involved querying Gmail for Jennifer Sanderson references:

Gmail API Search:
  Query: "Jennifer Sanderson" in:inbox newer_than:30d
  Scope requirements: gmail.readonly, calendar.events

From Carole's crew confirmation email, we extracted:

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

The OAuth token required both gmail.readonly and calendar.events scopes to be present in the unified token configuration. This was verified by checking the token structure against the OAuth 2.0 scopes before attempting API calls.

Phase 2: Google Calendar Event Enhancement

The calendar event for 2026-05-12 was located and updated with crew assignments. Rather than modifying the event title (which might break booking logic), we appended crew information to the event description:

Calendar Event Update Pattern:
  Event ID: [Jennifer Sanderson charter event ID]
  Description field append: "Captain: Gene O'Neal | 1st Mate: Angela Wong | 2nd Mate: Dan Rich | Host: Keely Hoyt"
  Update method: calendar.events().update() with calendarId='primary'

The decision to use the description field rather than custom event properties was driven by Google Calendar's limitations on custom metadata—the description field is always available, searchable, and doesn't require special parsing of extended properties.

Phase 3: DynamoDB Crew Manifest Synchronization

The ShipCaptainCrew DynamoDB table structure required creation of crew role entries. The table uses event date + crew name as composite keys:

DynamoDB Put Item Pattern:
  Table: ShipCaptainCrew
  Item structure:
    {
      "eventDate": "2026-05-12",
      "crewMember": "Gene O'Neal",
      "role": "Captain",
      "charterName": "Jennifer Sanderson",
      "timestamp": [Unix timestamp]
    }

Separate items were created for each crew member (Gene O'Neal, Angela Wong, Dan Rich, Keely Hoyt) with their respective roles. This denormalization allows the Lambda function to quickly query crew by date without complex queries across multiple table structures.

Phase 4: Time-Tracking Frontend Enhancement

The crew-facing page required a time-tracking interface. Angela Wong's actual hours (3:45 PM to 9:00 PM) needed to be logged with quarter-hour granularity.

Frontend Location: `/tmp/scc_index.html` (source for ops.queenofsandiego.com crew page)

Changes Made:

  • Added time-picker UI component with 15-minute increment selectors
  • Connected to new Lambda endpoint: POST /set-hours
  • Populated crew member dropdown from DynamoDB crew list
  • Implemented client-side timestamp calculation (3:45 PM = 15:45 in 24-hour format)

Unix Timestamp Calculations: All crew hours were converted to Unix timestamps for consistent storage:

Example: 2026-05-12, 3:45 PM Pacific Time
  Local: 2026-05-12T15:45:00
  Timezone offset: UTC-7 (PDT)
  Unix timestamp: [calculated value]

All crew members' hours were logged with quarter-hour increments to the DynamoDB hours_log field:

DynamoDB hours_log structure:
  {
    "2026-05-12": [
      {"crewMember": "Angela Wong", "start": [unix_ts], "end": [unix_ts]},
      {"crewMember": "Gene O'Neal", "start": [unix_ts], "end": [unix_ts]},
      ...
    ]
  }

Phase 5: Lambda Backend Enhancement for Manual Entry

File: `/tmp/scc_lambda/lambda_function.py`

Added a new endpoint handler to support manual hour entry:

New Lambda Route:
  Path: /set-hours
  Method: POST
  Auth: Required (checks sessionRole variable)
  Payload: {"eventDate": "YYYY-MM-DD", "crewMember": "name", "startTime": unix_ts, "endTime": unix_ts}

The handler validates admin authorization by checking the stored sessionRole variable (only crew members with administrative privileges can manually log hours for others). The endpoint then performs a conditional DynamoDB update to append to the hours_log array.

Admin Auth Pattern: The Lambda function checks whether the calling user's role includes administrative privileges before allowing manual hour modifications. This prevents crew members from retroactively altering each other's timecards.

Deployment and Infrastructure Changes

S3 Bucket Updates:

  • Uploaded updated crew dashboard: s3://[ops-bucket]/index.html
  • Invalidated CloudFront distribution to clear cache

Lambda Deployment:

  • Packaged updated lambda_function.py with new /set-hours endpoint