Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Automating Crew Dispatch: OAuth Token Refresh, Google Workspace Integration, and DynamoDB Schema Mapping

Over the past development session, we rebuilt critical infrastructure for the JADA crew-dispatch system to handle OAuth token refresh failures, establish reliable Google Workspace API access, and implement a scalable crew-roster pipeline tied to DynamoDB. This post walks through the exact technical decisions, schema patterns, and deployment approach.

The Problem: Token Refresh Failures in Production

The reauth_google.py script on the EC2 host (running the unified token service) was failing silently when Google OAuth tokens expired. This broke downstream access to:

  • Gmail search (finding crew notifications and booking confirmations)
  • Google Sheets API (reading availability rosters and writing monthly reports)
  • Google Drive API (locating and uploading charter documents)

The root cause: the token refresh logic wasn't handling the service-account flow correctly when credentials were stored in ~/.config/gcloud/ with non-standard ownership or permissions.

Solution Architecture: Patched Reauth Script and Secrets Directory Hardening

We deployed a two-part fix to the EC2 instance running at 34.239.233.28:

Part 1: Reauth Script Patch

The original reauth_google.py was reading the service-account JSON from a hardcoded path and relying on implicit credential discovery. We patched it to:

  • Explicit path handling: Changed credential loading from implicit gcloud discovery to an explicit file read from ~/.config/gcloud/service-account.json
  • Scope enforcement: Ensured the gmail.send, drive.readonly, and sheets scopes are explicitly requested during the refresh handshake
  • Error logging: Added verbose output to stderr so token-refresh failures surface immediately in CloudWatch logs rather than silently degrading service
  • Atomic file writes: Changed the token-write path to use a temporary file + rename pattern to prevent partial writes if the process crashes mid-refresh

The patched script was syntax-checked locally, backed up the original, and deployed with remote verification using:

python3 -m py_compile reauth_google.py

This ensures no syntax errors before the script is invoked by cron or systemd.

Part 2: Secrets Directory Hardening

We locked down permissions on the secrets directory and unified token file:

chmod 700 ~/.config/gcloud/
chmod 600 ~/.config/gcloud/service-account.json
chmod 600 ~/.config/gcloud/unified-token.json

The rationale: Google's OAuth client libraries fail silently if they can't read credentials due to overly-permissive parent directories. By hardening to user-only access, we prevent accidental credential leakage and ensure the token-refresh logic can reliably stat() and read the files.

Google Workspace Integration: Sheets, Gmail, and Drive

With the token refresh working reliably, we built three parallel pipelines:

Monthly Revenue Report (Google Sheets + Drive)

Built a Python script that:

  • Queries the crew-dispatch DynamoDB table for all charters in a given month (using a GSI on charter_date)
  • Filters records where reportable_revenue > 0 (excluding internal training sails and test bookings)
  • Extracts guest names, tip amounts, and vessel info from the charter record schema
  • Generates an .xlsx file using openpyxl with:
    • A Template tab (frozen header row, named ranges for formulas)
    • A month-specific tab (e.g., May 2024) with transactional rows and summary totals
    • Proper number formatting (currency for revenue columns, date format for booking dates)
  • Uploads the workbook to Google Drive and shares it with the ops team

The script reads the prior month's report to match formatting and ensures consistent column ordering across all reports.

Statement Email Distribution

Automated the monthly crew statement by:

  • Searching Gmail for prior statement recipients (using Gmail search operators to find emails with specific subject patterns)
  • Building a recipients list from crew-roster metadata in DynamoDB
  • Attaching the single-tab .xlsx in "house format" (matching the style of prior statements)
  • Sending via Gmail API with CC to Sergio for audit trail

This eliminates manual email composition and ensures statements ship on a consistent schedule.

Crew Availability and Roster Sync

We read the crew-availability spreadsheet from Google Drive and mapped it to the DynamoDB crew-roster table:

  • Drive location: JADA Business → Crew folder → availability sheet (read-only via Drive API)
  • DynamoDB schema: crew_id (PK), name, email, phone, captain_certified, available_dates (string set)
  • Sync logic: Reads all tabs in the sheet (one tab per crew member or one per availability status), parses dates, and upserts records into DynamoDB

DynamoDB Schema Inspection and Crew Dispatch Querying

We sampled the crew-dispatch table to understand the charter record structure:

  • Partition key: charter_id (UUID)
  • Sort key: created_at (ISO 8601 timestamp)
  • Key attributes: charter_date, vessel_name, captain_assigned, crew_assigned (list of crew IDs), guest_count, reportable_revenue, tips
  • GSI on charter_date: Allows range queries to fetch all charters in a month without full table scans

We also read the charter-chats table to understand the crew-dispatch-to-messaging pipeline, confirming that chat records reference charter_id for message threading.

Magic Link and Crew Cascade System

For the Danika crew-page feature, we decoded the magic-link token structure:

  • Token generation: Base64-encoded segments containing a crew member's ID, timestamp, and HMAC signature
  • Lambda routing: The deployed app (unzipped from the distribution) uses the token to look up crew availability and generate a personalized crew-assignment page
  • Cascade rule: When a captain is assigned to a charter, the system fetches the crew roster, filters by availability and certification (e.g