Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Monthly Revenue Reports and Crew Dispatch Integration: A Case Study in Cross-System Orchestration

This post walks through a recent engineering effort to automate JADA's monthly revenue reporting pipeline and integrate real-time crew availability data with the Danika crew-dispatch system. The work involved OAuth token refresh patching, Google Sheets API integration, DynamoDB schema inspection, and Lambda routing logic for crew magic-link generation.

The Problem: Manual Report Generation and Crew Communication Bottlenecks

JADA's previous workflow required manual assembly of monthly revenue reports from multiple sources—Gmail bookings, trip sheets in Google Drive, and charter records in DynamoDB. Crew notifications for upcoming sails relied on manual email chains and phone calls. We needed to:

  • Extract and aggregate charter data from DynamoDB tables across regions (us-east-1 and us-west-2)
  • Pull booking context from Gmail and Google Drive
  • Generate compliant revenue statements in Excel format
  • Send statements to stakeholders automatically
  • Decode and validate crew magic tokens for Danika integration
  • Implement a captain-first call-to-crew cascade based on roster availability

Technical Architecture and Components

OAuth Token Refresh and Google API Access

The first blocker was Google API authentication. The existing reauth_google.py script on the EC2 host was failing token refresh cycles, preventing downstream Sheets and Gmail access. The issue: the token cache wasn't being properly refreshed after credential updates.

Solution: We patched the reauth script to force a full OAuth flow on stale tokens, ensuring the gmail.send and drive.readonly scopes were explicitly requested and cached. The patched script was deployed to ~/repos/reauth_google.py on the EC2 instance, backed up, and verified with remote syntax checking before use in downstream tasks.

Why this approach: Rather than debugging token state across multiple services, we ensured the OAuth handshake was idempotent and could be re-run safely. This isolated credential issues from application logic.

DynamoDB Schema Inspection and Charter Record Extraction

Charter booking data lives in DynamoDB tables crew-dispatch and charter-chats across both AWS regions. We needed to understand the schema before building queries.

Commands used (no secrets):
- List all DynamoDB tables in us-east-1 and us-west-2
- Sample full schema of crew-dispatch and charter-chats tables
- Query charter records filtered by date and "reportable-revenue" field
- Extract guest names, totals, and trip-sheet references from charter items

The key discovery: charter records include a reportable_revenue boolean flag and reference fields to Drive trip sheets (Quinn's sheet, Jonathan's sheet). By joining this data with actual drive files, we could reconstruct a complete revenue ledger.

Why DynamoDB over direct Drive reads: DynamoDB gives us structured, queryable financial records. Drive is the source of truth for per-trip details (guest count, special requests), but DDB is the transactional ledger. Combining both gives us audit trail + detail.

Google Sheets API and Excel Workbook Generation

Revenue reports must match a specific format (columns for date, captain, vessel, pax, rate, total, notes) and support historical tabs (April, May, June, etc.). We chose to:

  1. Build reports locally in-memory using openpyxl (Python xlsx library)
  2. Generate a single-tab attachment for email recipients
  3. Upload the master workbook (multi-tab) to Google Drive for stakeholder review

The process:

1. Query DynamoDB for all charters in the reporting month
2. For each charter, fetch trip sheet from Drive and extract dollar amounts
3. Create xlsx workbook with template sheet (formulas, formatting intact)
4. Populate data rows and recalculate totals
5. Generate a single-tab "for distribution" copy
6. Upload master workbook to Drive (Sheets API)
7. Email statement attachment + Drive link to recipients

Why two workbooks: The multi-tab master lives in Drive for historical reference and audit. The single-tab attachment is distribution-friendly (no formulas that break in recipients' local Excel, no implicit dependencies).

Gmail Search and Statement Delivery

To identify statement recipients, we searched Gmail for prior booking confirmations and statements, extracting recipient addresses programmatically. The send flow:

gmail_contacts.py: Extract all recipients from prior Jennifer Sanderson booking emails
send_statement.py: Format statement email, attach xlsx, send via Gmail API with CC to operations lead

Why Gmail API over direct SMTP: Gmail handles authentication, threading, and label management. We avoid managing SMTP credentials separately.

Crew Dispatch and Magic-Link Integration

Danika Token Decoding and Magic Link Generation

Danika crew members authenticate via short-lived magic links. These links encode a crew ID and timestamp in a token stored in DynamoDB. We needed to:

  1. Decode existing tokens from the roster to understand the encoding scheme
  2. Build valid magic links for new crew members
  3. Trace the Lambda function that validates these tokens

The investigation revealed the magic-link URL format in the deployed Lambda code. By grepping the unzipped app bundle, we found the token validation logic and the route pattern:

/crew/join?code={MAGIC_TOKEN}

We traced back to the roster schema in DynamoDB (crew-roster table) and identified the token generation rules. New crew members (like Abdul Danishwar) need an entry in the roster with a valid token before receiving a magic link.

Call-to-Crew Cascade Logic

For a given sail (e.g., June 27 Anniversary Sail at 7:30 PM, $125pp), the crew notification flow is:

  1. Captain-first call: Contact the assigned captain (Abdul) first to confirm availability
  2. Crew availability check: Cross-reference the crew roster and availability spreadsheet in Drive
  3. Cascade send: If captain confirms, send magic links to available crew and email to operations lead (Carole) for coordination

Why captain-first: Captains are responsible for assembling their crew and deciding final lineup. This reduces email churn and ensures captain input before public notifications.

Infrastructure and Permission Boundaries

EC2 Host: ubuntu@34.239.233.28 (hosted in us-east-1, jada-key.pem identity) runs the orchestration scripts. This is where OAuth refresh, DynamoDB queries, Gmail operations, and Drive uploads execute. All secrets (service account keys, token cache) live in ~/.secrets/ with 0700 permissions.

S3 and CloudFront: The shipcaptaincrew site is served from an S3 bucket origin behind CloudFront. We identified the distribution and origin to understand CDN invalidation paths for future static crew-page updates.

Route53 DNS: Domain records for JADA ops and crew-facing services point to CloudFront distributions and API endpoints. No changes were needed this cycle, but the DNS