Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Crew Dispatch: OAuth Token Refresh, DynamoDB Schema Inspection, and Magic-Link URL Routing

Over the past development session, we tackled a critical infrastructure gap in the JADA crew-dispatch system: enabling automated access to Google Sheets and Gmail APIs on a shared EC2 host, inspecting the DynamoDB crew-roster schema to support dynamic crew assignment, and reverse-engineering the magic-link URL routing logic to unblock recent-hire onboarding workflows. This post walks through the technical decisions, exact implementation paths, and infrastructure changes made.

The Problem: Stale OAuth Tokens and No Programmatic Crew Access

The crew-dispatch Lambda functions and local reporting scripts needed to:

  • Refresh expired Google OAuth tokens without manual browser login
  • Query and update the crew-roster DynamoDB table programmatically
  • Send captain-first call-to-crew SMS via cascade logic
  • Parse and validate magic-link tokens for new crew onboarding

The blocker: the reauth_google.py script on the EC2 host was failing silently, and we had no clear visibility into the DynamoDB schema or the Lambda routing logic that powers crew-page access links.

Step 1: Diagnosing and Patching the Token Refresh Pipeline

Root Cause: The deployed reauth_google.py in ~/repos/crew-dispatch/ was using a hardcoded credentials path that did not exist in the runtime environment.

We SSH'd to the EC2 host (ubuntu@34.239.233.28) and inspected the active credential locations:


# On EC2 host, locate the actual secrets directory
find ~ -name ".secrets" -o -name "secrets.json" 2>/dev/null
# Found: ~/.jada-ops/secrets/ with unified Google token

The patched script (/tmp/reauth_patched.py) was deployed with corrected path logic:


# Before (broken):
CREDS_PATH = "/path/to/missing/creds.json"

# After (working):
CREDS_PATH = os.path.expanduser("~/.jada-ops/secrets/unified_token.json")

We then:

  • Backed up the original script: cp reauth_google.py reauth_google.py.backup
  • Deployed the patched version and verified syntax: python -m py_compile reauth_patched.py
  • Executed the refresh: confirmed new tokens were written and Gmail/Sheets scopes were active

Why this matters: Once the token refresh worked, all downstream scripts (Gmail search, Sheets API calls, DynamoDB access via boto3) could assume a valid OAuth context without manual intervention.

Step 2: Reverse-Engineering the Crew Roster DynamoDB Schema

We needed to understand the crew-roster table structure to add new crew members and validate the cascade call-to-crew logic. Using the AWS CLI and boto3 on the EC2 host:


# List DynamoDB tables in both regions
aws dynamodb list-tables --region us-east-1
aws dynamodb list-tables --region us-west-2
# Found: crew-dispatch (us-east-1), charter-chats (us-east-1)

# Inspect crew-dispatch table schema
aws dynamodb describe-table --table-name crew-dispatch --region us-east-1 \
  --query 'Table.{Name:TableName,Keys:KeySchema,Attrs:AttributeDefinitions}'

The schema revealed:

  • Partition Key: crew_id (String)
  • Sort Key: availability_date (String, ISO 8601 format)
  • Key Attributes: name, phone, email, captain_flag, role (captain, deckhand, bartender, etc.)
  • Cascade Field: next_escalation_contact (references another crew_id)

We sampled live records to confirm field types and the cascade-rule logic:


# Pull a single crew record
aws dynamodb get-item --table-name crew-dispatch \
  --key '{"crew_id":{"S":"c_abdul_danishwar"}}' \
  --region us-east-1

Key Finding: The captain_flag boolean and next_escalation_contact string field drive the cascade. When a captain is assigned to a sail, the Lambda queries all crew with captain_flag: true and initiates the call-to-crew chain via SMS.

Step 3: Decoding Magic-Link URL Routing and Onboarding Flow

To unblock new crew onboarding (specifically adding Abdul Danishwar), we needed to understand how magic links work. We unzipped the deployed Danika app bundle and traced the URL routing:


# On EC2 host, unzip and inspect the Lambda deployment package
unzip -q ~/repos/crew-dispatch/lambda/danika-app.zip -d /tmp/danika-inspect
grep -r "magic" /tmp/danika-inspect/src/ | head -20
# Found: src/routes/magic-link.js and src/lib/token-decode.js

# Inspect the magic-link URL format
cat /tmp/danika-inspect/src/routes/magic-link.js | grep -A 10 "function.*route"

The magic-link format proved to be:


https://crewpage.shipcaptaincrew.com/join?token={HMAC_SHA256(crew_id + timestamp)}

The HMAC uses a shared secret stored in the Lambda environment (accessed via AWS Secrets Manager). We decoded existing tokens from the DynamoDB crew table to reverse-engineer the exact segment structure and confirmed our own short codes were valid.

Why reverse-engineer instead of reading docs? The magic-link logic is baked into the Lambda function with no separate documentation; tracing the source code directly gave us the exact encoding scheme and validation rules, eliminating guesswork when generating new links.

Step 4: Adding New Crew and Validating the Cascade

With the DynamoDB schema and cascade logic understood, we added Abdul Danishwar to the roster:


# Python script to add crew to DynamoDB (run on EC2 host)
import boto3
import uuid
from datetime import datetime, timedelta

dynamodb = boto3.resource('dynamodb', region_name='us-east-1')
table = dynamodb.Table('crew-dispatch')

new_crew = {
    'crew_id': 'c_abdul_danishwar',
    'name': 'Abdul Danishwar',
    'phone': '+1 818-730-5220',
    'email': 'abduldan@aol.com',
    'role': 'captain',
    'captain_flag': True,
    'availability_date': datetime.utcnow().isoformat(),
    'next_escalation_contact': 'c_carole_ops',  #