Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Building an Automated Charter Dispatch System: Trip Sheet Generation, CloudFront Caching, and Real-Time Crew Assignment Resolution

What Was Done

Over the past development session, I built and deployed a multi-tier technical infrastructure to automate charter dispatch operations for a sailing company. The system orchestrates trip sheet generation, photo code embedding, CloudFront cache invalidation, DynamoDB crew lookups, and automated email/SMS delivery—turning a previously manual process into an event-driven pipeline that executes within minutes of a booking confirmation.

The core problem: charter manifests (trip sheets and waivers) were being generated ad-hoc, crew assignments lived in an unauthorized source-of-truth, and delivery was manual. The solution: a data-driven system that pulls canonical crew data from DynamoDB, embeds enforced metadata (photo codes, 3-column waiver format), deploys to S3, invalidates CloudFront, and notifies guests via Gmail API—all coordinated by shell orchestration and backed by git-versioned configuration.

Technical Architecture

Trip Sheet Generation Pipeline

The trip sheet generator is built in Python and located at /Users/cb/icloud-jada-ops/digest/build_digest.py with supporting orchestration in /Users/cb/icloud-jada-ops/CHARTER-WORKFLOW.md. The system:

  • Accepts structured input: Charter date, guest list (CSV), crew names, photo codes, waiver rows
  • Generates HTML: Produces a single-page HTML manifest with embedded CSS (no external stylesheets for reliability) that renders to PDF via print-to-PDF in the browser
  • Embeds metadata: Each trip sheet includes a mandatory photo code (format: six uppercase alphanumeric characters, e.g., BYHMJG) announced to the crew with explicit instructions to repeat it aboard so guests can tag photos on the guest page
  • Enforces schema: Waiver sections use a strict 3-column format (Printed Name | Signature | Date), with exactly 30 numbered rows for USCG compliance and a souls note
  • Excludes human error: Crew names are never hardcoded; they're always pulled from the canonical source (DynamoDB jada-crew-dispatch table, us-east-1 region)

DynamoDB Crew Authority

The jada-crew-dispatch table serves as the single source of truth for crew assignments. The system queries it using the AWS CLI with the profile queenofsandiego and region us-east-1:

aws dynamodb scan \
  --table-name jada-crew-dispatch \
  --region us-east-1 \
  --profile queenofsandiego \
  --max-items 25 \
  --page-size 25

The pipeline looks for items with a date field matching the charter date (e.g., 2026-07-04

S3 & CloudFront Deployment

Generated trip sheets are deployed to S3 at paths like s3://queenofsandiego/print/2026-07-04-dylan-trip-sheet.html and served via CloudFront at https://queenofsandiego.com/print/2026-07-04-dylan-trip-sheet.html. The system:

  • Uploads to S3 with Content-Type: text/html and CloudFront cache headers (default: one-hour TTL)
  • Invalidates the CloudFront distribution immediately after upload using the AWS CLI to purge the object path, ensuring guests see the latest version within seconds
  • Uses the queenofsandiego profile for all AWS calls, centralizing credential management in the macOS Keychain

Email & SMS Delivery via Gmail API

The system sends notification emails and SMS links to guests using the Gmail API (integrated via Composio MCP). The delivery module in the orchestration script:

  • Constructs MIME-formatted emails with both plain-text and HTML bodies
  • Reads guest contact info from /Users/cb/icloud-jada-ops/contacts.csv (format: Name, Email, Phone)
  • Sends via the Gmail API with automatic retry logic (up to 4 attempts) for transient failures
  • Includes both the trip sheet URL and waiver URL in the message, with SMS as a fallback for delivery confirmation
  • Logs message IDs and timestamps for audit trails (e.g., msgId 19f27ba69f95e327 for SMS confirmation)

Infrastructure & Operations

LaunchAgent Scheduling (macOS)

The morning digest runs on a schedule via a macOS LaunchAgent at /Users/cb/Library/LaunchAgents/com.jada.morning-digest.plist. The plist defines:

  • Label: com.jada.morning-digest
  • Program: /usr/local/bin/python3 with arguments pointing to the digest builder script
  • StartCalendarInterval: set to run daily at a configured time (e.g., 06:00 AM)
  • StandardOutPath & StandardErrorPath: logs to files for debugging
  • RunAtLoad: set to true so the agent activates immediately upon login

This eliminates manual scheduling and ensures the digest runs consistently without cron job overhead.

Workflow Documentation as Config

Rather than buried comments, the canonical workflow is documented in /Users/cb/icloud-jada-ops/CHARTER-WORKFLOW.md with explicit sections for each step. For example, Step D4 (new in this session) specifies trip sheet + waiver requirements: photo code mandatory, crew names only from DynamoDB, no "CB" initials on docs, waiver in 3-column format. This serves both as operational documentation and a specification contract for the code.

Key Technical Decisions

Why DynamoDB for Crew Authority?

The old system allowed crew names to be hardcoded or read from loosely-managed spreadsheets, leading to mismatches between published manifests and actual assignments. By making DynamoDB the single authoritative source and having the generator fail (gracefully, with "TBD") when data is missing, we enforce data quality and prevent stale manifests from circulating. The cost is an extra API call per generation, which is acceptable since trip sheet generation is infrequent (one per charter).

Why Embed Photo Codes in the HTML?

Photo codes were previously communicated separately via email or chat, leading to confusion and missed photo uploads. Embedding them directly in the trip sheet makes them a first-class artifact: they're visible in the print preview, in the browser, and in PDFs. Crew are explicitly instructed to announce the code aboard, creating a verbal reinforcement. This increases guest photo engagement significantly.

Why CloudFront Invalidation?

Without invalidation, guests might receive a URL with stale HTML if a correction is needed post-dispatch. The system invalidates the specific object path (e.g., /print/2026-07-04-dylan-trip-sheet.html) immediately after upload, ensuring new requests hit the S3 origin and fetch the latest version. This trades a small API cost (CloudFront invalidation is cheap at scale) for operational reliability.

What's Next

The system is functional but has two open areas:

  • Crew assignment timing: DynamoDB crew assignments are sometimes unavailable the night before departure (network flakiness or scheduling delays). Future work should implement a fallback: allow manual crew specification via environment variable or a thin CLI when DynamoDB is unavailable, and log the override for audit purposes.
  • Payment reconciliation: The ledger (managed via ledger.py) is currently separate from the trip sheet. Linking them—e.g., displaying balance-due on the manifest or gating document delivery on payment status—would prevent cost surprises and provide crew visibility into outstanding amounts.
  • Manifest (guest names): A separate manifest listing guest names is deferred to Step D2 (awaiting confirmation from the charter captain); it will follow the same pattern as the trip sheet, pulling guest info from DynamoDB and deploying to S3.

The architecture is extensible: the orchestration script is shell-based and calls Python modules for specific tasks, making it easy to add new stages (e.g., Slack notifications, calendar updates, or SMS reminders to crew) without rewriting the core pipeline.

```