Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Multi-Service Crew Dispatch: OAuth Token Refresh, DynamoDB Roster Management, and Magic-Link Integration

Over the past development session, we tackled a critical infrastructure challenge for the Ship Captain Crew dispatch system: enabling programmatic access across Gmail, Google Sheets, and Google Drive APIs while maintaining secure, auditable credential rotation. This post details the technical decisions, architecture patterns, and specific implementation changes that now allow the backend to send crew notifications, manage rosters, and generate revenue reports without manual intervention.

The Problem: Expired OAuth Tokens in Production

The JADA operations team relies on automated crew-call generation and monthly revenue reporting. Both workflows depend on Google APIs (Gmail for send, Sheets for reporting, Drive for storage). The original deployment used a static OAuth token that had expired, blocking all downstream functionality. Manual token refresh required developer intervention—unacceptable for a production dispatch system.

Solution Architecture: Automated Token Refresh with Patched reauth_google.py

We identified /home/ubuntu/repos/reauth_google.py on the EC2 instance as the credential refresh handler. This script uses the Google OAuth2 flow to obtain a fresh access token when the stored token expires. The issue was a hardcoded path reference that no longer matched the actual secrets directory structure.

The Core Fix

The patched script now correctly locates the secrets directory at /home/ubuntu/repos/.secrets/, reads the stored refresh token, exchanges it for a new access token via the Google OAuth2 endpoint, and persists the updated token back to the filesystem. This allows any Python process importing the token—whether gmail_contacts.py, build_sheet.py, or send_statement.py—to automatically use a fresh credential.


# Pseudocode: reauth_google.py pattern
import json
from pathlib import Path
from google.oauth2.service_account import Credentials

SECRETS_DIR = Path("/home/ubuntu/repos/.secrets/")
TOKEN_FILE = SECRETS_DIR / "unified_token.json"

def refresh_token():
    with open(TOKEN_FILE, "r") as f:
        token_data = json.load(f)
    
    # Use refresh_token to get new access_token from Google OAuth2 endpoint
    new_token = google_oauth2_refresh(token_data["refresh_token"])
    
    with open(TOKEN_FILE, "w") as f:
        json.dump(new_token, f)
    
    return new_token

Deployment involved SSH to the box, backing up the original script, syntax-checking the patched version, and deploying it alongside a corrected iteration that fixed an encoding issue in the first patch attempt.

DynamoDB Roster Management: Adding Captain Abdul

The crew-dispatch system maintains a canonical roster in DynamoDB across two regions. The roster table schema includes:

  • crew_id (partition key)
  • name, phone, email
  • role (captain, mate, crew, skipper, etc.)
  • cascade_level (determines call-to-crew ordering)
  • availability (linked to the crew-availability spreadsheet in Drive)

We added Captain Abdul Danishwar (+1 818-730-5220, abduldan@aol.com) to the roster with role captain and cascade_level: 1, ensuring he receives captain-first notifications before general crew calls. This involved:

  1. Querying the current roster from DynamoDB to confirm no duplicate entry
  2. Crafting a PutItem request with the new captain record
  3. Verifying the entry propagated to the secondary region (for disaster recovery)

Magic-Link Token Decoding: Danika Crew Portal Access

The Danika crew portal (deployed as a Lambda-backed web app) uses short-lived, single-use magic links to authenticate crew members without passwords. These links encode a token stored in DynamoDB that the Lambda function decodes and validates. We reverse-engineered the token format from the deployed app:

  • Unzipped the Lambda deployment package from S3
  • Grepped the handler code for the magic-link URL routing logic
  • Located the token-validation function and its DynamoDB lookup pattern
  • Decoded existing tokens for crew members (e.g., Danika, Esmi) to understand the structure

This allowed us to generate valid links for new roster members and test the cascade-call logic end-to-end. The Lambda function pattern follows a standard architecture: API Gateway → Lambda handler → DynamoDB lookup → token validation → crew page render.

Crew Availability Spreadsheet Integration

The crew roster is paired with a Google Sheet in the JADA Business folder that tracks availability by date. We located this sheet, read its tabs (one per month), and parsed the availability grid to determine which captains are available for specific sails. The spreadsheet uses a shared-key format that the backend looks up when generating crew calls.

Infrastructure Overview: EC2, DynamoDB, S3, CloudFront, and Lambda

  • EC2 Instance: Ubuntu-based, running Python scripts for OAuth refresh, Gmail operations, and Sheets API access. SSH key stored securely; repo cloned from a private Git source.
  • DynamoDB Tables: crew-dispatch (charter records, crew assignments) and charter-chats (crew communication threads). Both replicated across regions for HA.
  • S3 & CloudFront: The shipcaptaincrew site is fronted by CloudFront distribution *.cloudfront.net, with the origin bucket configured for public read access and cache invalidation on updates.
  • Lambda: Danika crew portal backend; triggered via API Gateway; queries DynamoDB for roster and token validation; generates HTML pages dynamically.
  • Google Workspace: OAuth tokens managed via reauth_google.py; Gmail, Sheets, and Drive APIs called directly from Python scripts on EC2.

Key Architectural Decisions

Centralized Token Storage: Rather than embed credentials in environment variables or config files, we store the unified OAuth token in /home/ubuntu/repos/.secrets/unified_token.json with restricted file permissions (600). This allows multiple Python processes to share the same refreshed credential, reducing token sprawl and simplifying rotation.

Cascade-First Crew Calls: The system respects a cascade rule when sending crew notifications: captains at cascade_level: 1 are called first, then mates at level 2, then general crew. This mimics real-world dispatch practices and ensures the right people are reached first for time-sensitive charters.

Magic-Link Simplicity: Rather than manage passwords or session tokens, the Danika crew portal uses single-use magic links. This reduces account-management overhead and improves the user experience for part-time crew members who may not log in frequently.

Multi-Region DynamoDB: Charter data and crew rosters are replicated across regions, providing both disaster recovery and reduced latency for crew members in geographically dispersed locations.

Deployment Workflow

All changes