Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating JADA Crew Dispatch: OAuth Token Refresh, Google Sheets Integration, and Magic-Link Authentication

This post documents a multi-day engineering effort to stabilize Google API authentication, rebuild the monthly revenue reporting pipeline, and extend the Danika crew-scheduling system with magic-link access for roster members. The work touched OAuth2 token lifecycle management, Google Sheets API interaction patterns, DynamoDB schema queries, and Lambda URL routing for crew-member onboarding.

Problem Statement

The JADA operations team faced three interconnected blockers:

  • Google API token refresh was failing silently — the reauth script on the EC2 box was using an outdated token-refresh pattern, causing Gmail and Sheets operations to fail mid-pipeline.
  • Monthly revenue reports required manual assembly — charter data from DynamoDB needed to be queried, validated against business rules (reportable-revenue filtering), and formatted into an Excel workbook with proper number formatting and footnotes, then sent via email.
  • Crew roster onboarding had no self-service path — new crew members couldn't access the Danika scheduling portal without manual admin intervention to decode magic-link tokens.

Token Refresh: From Deprecated OAuth2Session to Manual HTTP Flow

The original reauth_google.py on the EC2 box (~/repos/shipcaptaincrew/reauth_google.py) relied on oauth2_session.refresh_token(), which assumes the refresh token endpoint is configured in the OAuth2 provider metadata. Google's OAuth2 implementation requires explicit endpoint specification, and the script was missing the token_uri parameter.

The fix: Replace the deprecated oauth2_session call with a direct HTTP POST to Google's token endpoint:

POST https://oauth2.googleapis.com/token
  grant_type=refresh_token
  &refresh_token={REFRESH_TOKEN}
  &client_id={CLIENT_ID}
  &client_secret={CLIENT_SECRET}

This pattern is more explicit, easier to debug, and doesn't depend on provider-metadata auto-discovery. After patching, the script successfully refreshes the access token and writes the updated token back to the local credentials file in ~/.google/. The fix was validated by running the script on the box, observing successful token refresh, and confirming Gmail and Sheets API calls succeeded downstream.

Why this matters: Many Python OAuth2 libraries abstract away the token endpoint, which works fine when the provider metadata is available but fails silently when it isn't. Explicit HTTP calls give you control and visibility into what's actually happening on the wire.

Monthly Revenue Reporting: DynamoDB to Excel Pipeline

The revenue report pipeline required pulling charter data from DynamoDB, applying business rules, and formatting the output:

Data Source: DynamoDB crew-dispatch Table

The crew-dispatch table in the us-east-1 region stores charter records with the following schema:

  • PK (Partition Key): charter-{UUID}
  • SK (Sort Key): timestamp
  • captain, crew, guests: nested records
  • total, amount: revenue fields (decimal, stored as strings in DynamoDB)
  • status: enum (completed, pending, cancelled)

The reportable-revenue rule came from inspecting prior Sheraton monthly reports: only charters with status == "completed" and non-null amount values are included. This rule was reverse-engineered by reading April's report footnotes and comparing them against the full DynamoDB scan.

Excel Workbook Generation: openpyxl and Number Formatting

Python's openpyxl library handles XLSX generation and formatting. The pipeline creates a workbook with a single tab per month (e.g., May, June), with columns for date, captain, guests, and amount. Amounts are formatted as currency with proper decimal places:

from openpyxl.styles import numbers
ws['D2'].number_format = numbers.FORMAT_CURRENCY_USD_SIMPLE
# or explicit format string
ws['D2'].number_format = '"$"#,##0.00'

The workbook includes a totals row and a footnote row explaining the reportable-revenue rule. After generation on the box, the file is downloaded via SCP to the local machine, then uploaded to Google Drive.

Google Sheets Upload: Drive API Integration

Once the Excel file is ready, it's uploaded to a shared Drive folder using the drive.files.create() method. The file is created with MIME type application/vnd.openxmlformats-officedocument.spreadsheetml.sheet to ensure it stays in Excel format rather than being converted to Sheets.

Key decision: The report is generated as a standalone XLSX attachment and uploaded separately from the interactive Google Sheet used for month-to-date bookkeeping. This separation avoids accidental overwrites and gives the operations team a clean, auditable monthly snapshot in the shared folder.

Crew Roster Magic Links: DynamoDB Token Decoding and Lambda URL Routing

The Danika crew-scheduling portal uses magic-link authentication to let crew members access their availability and schedule without passwords. The magic links are short codes that map to DynamoDB roster entries via token encoding.

DynamoDB Roster Schema

The crew-roster table stores member records with:

  • PK: member-{MEMBER_ID}
  • magic_token: base64-encoded short code (e.g., "danika-abd-2024")
  • name, phone, email: contact details
  • role: captain, crew, coordinator
  • status: active, inactive, pending

The token is decoded by splitting on hyphens and querying the table for a matching magic_token field. The Lambda function then initializes a session and redirects to the Danika UI with a signed session cookie.

Adding New Roster Members Programmatically

To onboard a new crew member (e.g., Abdul Danishwar, +1 818-730-5220), the process is:

  1. Query the crew-roster table for the next available member_id (sequence counter in a metadata record).
  2. Generate a unique magic_token using the naming convention danika-{first_3_letters_lowercase}-{year}.
  3. Put a new item in the table with the member's contact info, role, and token.
  4. Construct the magic-link URL and send it via SMS or email (handled by the captain-call logic below).

The roster member is then automatically included in crew-availability cascades when a new charter is booked.

Captain-First Call-to-Crew: DynamoDB Query and SMS Cascade

When a charter is booked (e