Automating JADA Crew Operations: Building a Magic-Link Access System and OAuth Token Management Pipeline

```html

What Was Done

This session focused on two interconnected infrastructure challenges for the Queen of San Diego crew-operations backend: establishing a reliable OAuth token refresh mechanism for Google Workspace API access, and building a secure magic-link authentication system for crew roster management without storing plaintext credentials.

The work spanned three main areas:

Diagnosing and patching the Google OAuth token refresh pipeline on the EC2 host
Generating and distributing a monthly revenue statement via Gmail with proper formatting and audit trails
Decoding and implementing magic-link token generation for crew-page access, tied to DynamoDB roster records

Technical Details: OAuth Token Refresh Pipeline

The root issue was in /home/ubuntu/repos/reauth_google.py on the EC2 instance. The script handles credential refresh for Google Sheets and Gmail APIs but was failing silently when the local token cache expired.

Diagnosis steps:

SSH to ubuntu@34.239.233.28 and located the reauth script
Ran python3 -m py_compile reauth_google.py to validate syntax
Tested token refresh by calling the function and inspecting error logs
Identified that the script was trying to write tokens to a directory path that had incorrect permissions

The fix involved three changes to the reauth script:

# Original (broken) token storage path
token_path = ~/secrets/google_token.json

# Corrected path with proper home expansion
token_path = os.path.expanduser('~/.secrets/google_token.json')

# Ensured directory permissions after token write
os.chmod(secrets_dir, 0o700)
os.chmod(token_path, 0o600)

After redeployment and validation, the script successfully refreshed the unified token containing both gmail.send and spreadsheets scopes, enabling both email dispatch and Sheets API operations.

Revenue Statement Generation and Distribution

With OAuth fixed, we built an automated monthly revenue-report pipeline that:

Pulls May charter records from the crew-dispatch DynamoDB table (using GSI queries on date range)
Generates an Excel workbook with proper formatting, tax-calculation rows, and footnotes
Uploads to Google Drive at a shared link for stakeholder access
Sends a formatted HTML email to Sergio, Jennifer, and other recipients

Key files created in the pipeline:

/tmp/build_sheet.py — Pulls charter data, calculates reportable revenue per charter rules
/tmp/build_may_tab.py — Formats a single-month sheet with template styling from existing workbooks
/tmp/upload.py — Uses Google Drive API to push the workbook to the shared folder
/tmp/send_statement.py — Composes and sends the monthly statement via Gmail

The reportable-revenue rule learned from prior statements: include only charters with guest names and confirmed totals, excluding internal crew training sails and entries flagged as "pending." This filtering is applied at the DynamoDB query stage to minimize post-processing.

Magic-Link Token System for Crew Page Access

The crew roster page needs to allow access without a shared password. We implemented a magic-link system by decoding short tokens stored in DynamoDB and mapping them to authorized users.

Token structure and storage:

Tokens are 8–12 character alphanumeric strings, stored in the crew-roster table under the magic_token attribute
Each token is associated with a single crew member record (captain, first mate, etc.)
Tokens are generated during initial roster onboarding and never expire (though they can be rotated)

URL routing in the Lambda handler:

The deployed Lambda (unzipped from the CloudFront origin) contains the magic-link logic in its route handlers. A request to /crew?token=ABC123 is matched against the DynamoDB roster:

# Pseudocode for the token validation flow
def validate_crew_token(token):
    response = dynamodb.query(
        TableName='crew-roster',
        IndexName='magic_token-index',
        KeyConditionExpression='magic_token = :token',
        ExpressionAttributeValues={':token': token}
    )
    if response['Items']:
        return response['Items'][0]  # crew record
    return None

Once decoded, the crew member's role determines what data they can view (roster for all captains, availability for scheduling, etc.).

Adding New Crew: Abdul Danishwar

To support the June 27 Anniversary Sail, we added a new captain to the roster:

Name: Abdul Danishwar
Phone: +1 818-730-5220
Email: abduldan@aol.com
Role: Captain
DynamoDB action: PutItem to crew-roster table with generated magic token and default availability

After the roster update, the cascade logic for the call-to-crew email runs: when a sail is confirmed, Carole (operations) is CC'd, and all captains matching the sail's date and availability window receive the assignment notification via Gmail.

Infrastructure and Key Decisions

Why EC2 SSH for token refresh? The reauth script needs to run on the host where the app is deployed because it manages the local token cache and has permissions to write to the app's secrets directory. Centralizing token refresh on the deployed host (rather than in Lambda) avoids the complexity of distributed cache invalidation and keeps the single source of truth for credentials.

Why DynamoDB for magic tokens instead of a lookup table? Tokens are crew-specific and already live in the roster table; adding a secondary index on magic_token provides O(1) lookup with no extra infrastructure. The index scales automatically with DynamoDB on-demand billing.

Why HTML email for the revenue statement? The statement includes multi-row tables, currency formatting, and footnotes that plain text cannot convey clearly. HTML allows proper alignment and styling without requiring the recipient to open an attachment (though we also attach the Excel file as backup).

What's Next

Crew availability sync: The crew-roster availability sheet in Google Drive is the source of truth for scheduling; we need to set up a periodic sync (daily, 6am UTC) that pulls availability from Drive and updates the DynamoDB availability attribute for each crew member.
Call-to-crew automation: Currently triggered manually; next step is to create a CloudWatch Event rule that fires when a charter is marked "confirmed" in crew-dispatch, triggering the Lambda call-to-crew function automatically.
← All posts