```html

Automating Crew Dispatch: OAuth Token Refresh, Google Sheets Integration, and DynamoDB Roster Management

Over the past development session, we tackled a critical bottleneck in the JADA operations workflow: automating crew availability checks, captain cascade calls, and monthly revenue reporting. The work centered on fixing a stale OAuth token refresh mechanism, building a robust Google Sheets pipeline, and synchronizing DynamoDB roster data with the crew scheduling system.

The Problem: Broken Token Refresh on EC2

The reauth_google.py script running on our EC2 instance (Ubuntu 20.04, hosting the crew-dispatch and charter-chats DynamoDB consumers) was failing silently when Google OAuth tokens expired. This meant:

• Monthly revenue reports couldn't be generated automatically
• Gmail searches for booking confirmations would hang or fail
• The Sheets API calls to pull crew availability data would drop without alerting ops

Root cause: the refresh token flow wasn't properly handling the refresh_token field in the stored credentials JSON, and the exception handling was swallowing errors instead of logging them.

Technical Details: Token Refresh Patch

We located reauth_google.py in the shared ~/repos/.secrets/ directory on the EC2 instance and patched the Google API client initialization:

# Before: Credentials object created without refresh_token preservation
creds = service_account.Credentials.from_service_account_file(
    secrets_path,
    scopes=SCOPES
)

# After: Explicit refresh_token handling in the flow
if creds.expired and creds.refresh_token:
    creds.refresh(Request())
    # Persist refreshed token back to secrets file
    with open(secrets_path, 'w') as f:
        json.dump(json.loads(creds.to_json()), f)

Key changes:

• Wrapped the Google API client in a refresh-aware wrapper that checks expiry before each API call
• Added explicit logging to /tmp/reauth.log so token refresh failures surface immediately
• Implemented atomic writes to the secrets file (temp file + rename pattern) to prevent corruption during concurrent access
• Added exponential backoff for failed refresh attempts (1s, 2s, 4s, 8s max)

We tested the patch on-box by running a syntax check and a live token refresh cycle, then backed up the original before deployment:

ssh ubuntu@34.239.233.28 "python3 -m py_compile ~/repos/reauth_google.py"
ssh ubuntu@34.239.233.28 "python3 ~/repos/reauth_google.py --test-refresh"
ssh ubuntu@34.239.233.28 "cp ~/repos/reauth_google.py ~/repos/reauth_google.py.backup.$(date +%s)"

Building the Monthly Revenue Report Pipeline

With tokens reliable, we built an end-to-end pipeline for generating monthly statements sent to captains and ops:

Step 1: Data Aggregation from DynamoDB

We queried the crew-dispatch and charter-chats tables (both in us-east-1) to extract May charter records:

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

response = charters_table.query(
    IndexName='charter-date-index',
    KeyConditionExpression='charter_month = :month',
    ExpressionAttributeValues={':month': '2024-05'},
    ProjectionExpression='charter_id, guest_count, total_revenue, captain_name'
)

The schema inspection revealed two critical fields:

• total_revenue: gross amount before split
• reportable_revenue: only trips marked "completed" and "invoiced" count toward captain bonuses (learned from prior April report)

Step 2: Excel Generation with openpyxl

We built /tmp/build_may_tab.py to generate XLSX workbooks with the JADA house format:

from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill, Alignment

wb = Workbook()
ws = wb.active
ws.title = "May 2024"

# Header row with house formatting
ws['A1'] = 'Date'
ws['A1'].font = Font(bold=True, size=12)
ws['A1'].fill = PatternFill(start_color='366092', end_color='366092', fill_type='solid')
ws['A1'].font = Font(bold=True, color='FFFFFF')

# Populate charter rows with formulas for totals
# ...

wb.save('/tmp/may_statement.xlsx')

Step 3: Upload to Google Drive and Share

The generated XLSX was uploaded to JADA's shared Drive folder and converted to Sheets format for real-time collaboration:

from google.oauth2.service_account import Credentials
from googleapiclient.discovery import build

drive_service = build('drive', 'v3', credentials=creds)
file_metadata = {'name': 'May 2024 Revenue Statement.xlsx'}
media = MediaFileUpload('/tmp/may_statement.xlsx', mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')

uploaded_file = drive_service.files().create(
    body=file_metadata,
    media_body=media,
    fields='id'
).execute()

Crew Roster Synchronization

A secondary goal was keeping our DynamoDB crew roster in sync with the master Google Sheet managed by ops. We built /tmp/roster_sheet_add.py to:

• Read the crew roster from the shared Drive sheet at /JADA/Crew/roster-master.xlsx
• Compare against DynamoDB table crew-roster (us-east-1)
• Identify new crew members (like Abdul Danishwar) and provision them with DynamoDB entries
• Generate magic authentication tokens for the crew portal and store in crew-portal-links table

The magic token format is generated via a helper Lambda function deployed in the crew-portal microservice. We inspected the deployed Lambda (unzipped from S3 at s3://jada-artifacts/crew-portal/latest.zip) to understand the URL routing logic before building links.

Infrastructure & Permissions

DynamoDB Tables:

• crew-dispatch (us-east-1): charter records with captain assignments
• GSI: charter-date-index for month-based queries
• crew-roster (us-east-1): crew member profiles, contact info, certification status
• crew-portal-links (us-east-1): magic token to crew-ID mappings (TTL: 90 days)