Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Crew Dispatch: Building a Magic-Link Authentication System for Dynamic Roster Management

Over the past development cycle, we built and deployed a crew roster management system that integrates with our existing Danika crew-page infrastructure. The challenge: allow crew members to access their availability and assignment data without managing separate passwords, while maintaining a secure, audit-able authentication flow. Here's how we solved it.

What We Built

The core system consists of three integrated components:

  • Magic-link generation and validation — short codes decoded from DynamoDB crew records that grant temporary session access
  • Crew roster synchronization — pulling crew data from DynamoDB, enriching it with availability data from Google Sheets, and maintaining a canonical source of truth
  • Call-to-crew orchestration — cascading captain-first notifications for specific sailing dates, with crew availability cross-referenced in real time

Technical Architecture

Magic-Link Token System

Our Danika deployment uses a lambda function (location: /repos/danika-lambda/handler.py) that routes incoming requests to crew pages. The magic-link URL format encodes a crew member's short code and optional sailing date:

GET /crew/access?code=abc123xyz&sail_id=june-27-anniversary

The handler decodes this short code by querying the DynamoDB crew-dispatch table (us-east-1 and us-west-2 replicas), which stores:

  • crew_id (partition key)
  • name, phone, email
  • magic_token (the decoded short code)
  • availability_reference (pointer to crew roster sheet)

Why this approach: Magic links eliminate password management overhead, reduce support burden, and create an audit trail (every access is a URL decode + DDB lookup). The short code is intentionally short (6-8 chars) to fit in SMS messages and WhatsApp without link shorteners.

Crew Roster Synchronization

The canonical crew roster lives in a Google Sheet (accessed via sheet_inspect.py and roster_sheet_add.py in our ops scripts). The sheet has tabs for each crew role (captains, first mates, deckhands) with columns:

  • Name, phone, email
  • Role (captain, mate, deckhand, etc.)
  • DDB crew_id (foreign key)
  • Status (active, inactive, on-leave)

When we add a crew member (like Abdul Danishwar in this cycle), the process is:

  1. Add row to the Google Sheet with name, phone, email, role
  2. Run /tmp/roster_sheet_add.py which:
    • Reads the new row from the sheet
    • Generates a unique crew_id and magic_token
    • Writes both to the DynamoDB crew-dispatch table
    • Updates the sheet with the new crew_id for verification

Why split the source: Google Sheets is our operational interface (non-technical users can edit it), while DynamoDB serves as the authoritative backend. The sync is one-way on add, two-way on update (phone/email changes propagate both directions via a Lambda trigger).

Availability Cross-Reference

Crew members have varying availability across sailing dates. This is stored in a separate sheet tab (e.g., "June Availability") with crew names as rows and dates as columns, cells containing "available" / "unavailable" / "pending".

When building the call-to-crew cascade for June 27, the script /tmp/send_captain_call.py:

  1. Reads the sailing event from Google Calendar (JADA Internal calendar, event title "Anniversary Sail 7:30PM $125pp")
  2. Extracts guest list (Esmi Gonzalez, Cathy Vu) and captain assignment from event description
  3. Queries the availability sheet for all crew marked "available" on June 27
  4. Builds the cascade: captain first, then first mate, then deckhands
  5. Sends initial contact via Gmail (with appropriate Gmail scopes configured in OAuth)

Infrastructure & Deployment

DynamoDB Schema

Two tables replicated across regions:

  • crew-dispatch (us-east-1, us-west-2)
    • Partition key: crew_id (String)
    • Sort key: ts (Number, epoch seconds)
    • GSI on email for reverse lookup
    • Stream enabled for Lambda trigger sync
  • charter-chats (read-only for context; stores sail assignments)

Google Drive & Sheets Organization

All crew-facing documents live in a shared folder structure:

  • JADA Business/Crew Roster/ — master roster sheet (canonical source for crew records)
  • JADA Business/Availability/ — monthly availability tabs (June, July, etc.)
  • JADA Internal/ — calendar and statements (read-only for crew)

OAuth scope required: https://www.googleapis.com/auth/spreadsheets (read/write for roster updates) and https://www.googleapis.com/auth/calendar (read for sail events).

Key Decisions

Why Magic Links Over Passwords

Crew members span multiple age groups and technical backgrounds. Passwords create friction (reset flows, forgotten credentials, support load). Magic links work on any device with SMS or messaging app, align with how captains prefer to communicate, and are industry-standard for time-sensitive, single-use access (think Slack or Figma invitation links).

Google Sheets as Operational DB

Non-technical staff (like Jada and Carole) manage crew rosters and availability manually. A spreadsheet is where they live. We sync to DynamoDB for backend queries and Lambda processing, but the "source of truth" for humans remains the sheet. This avoids a separate admin UI and keeps changes traceable (sheet version history).

Cascade-First Notifications

The captain is called first, not the whole crew. This matches real sailing operations: the captain decides crew, then calls people. It also reduces notification spam and respects the captain's role. The script implements this via a simple state machine in send_captain_call.py.

Example Commands

Adding a new crew member:

python /tmp/roster_sheet_add.py \