Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Charter Operations: Building a Distributed Crew & Document System

What Was Done

Over the course of a single operational cycle, we built and deployed a complete automation infrastructure for managing charter operations, crew assignments, and guest communications. The system handles dynamic document generation (trip sheets, waivers, manifests), scheduled email digests, guest follow-ups, and real-time crew coordination across multiple AWS and local services.

The Problem

Charter operations involved manual steps: verifying crew assignments in DynamoDB, generating manifests, printing trip sheets, coordinating email notifications, and tracking guest communications. These tasks were error-prone, time-consuming, and created coordination bottlenecks when crew or charter details changed close to departure time.

Architecture Overview

The solution uses a distributed system with three layers:

  • Local Automation (macOS): Python scripts in /Users/cb/icloud-jada-ops/ for digest building, manifest generation, and document coordination, triggered by LaunchAgent (native macOS scheduling)
  • Cloud Data Layer (DynamoDB): jada-crew-dispatch table for crew assignments indexed by charter date; jada-bookings for charter metadata
  • Content Delivery (S3 + CloudFront): Generated PDFs (trip sheets, waivers, manifests) stored in S3, served via CloudFront distribution with aggressive cache invalidation on updates

Technical Implementation

Manifest Generation: Dynamic Role Resolution

The manifest generator (/Users/cb/icloud-jada-ops/generate_manifest.py) queries DynamoDB to fetch crew assignments for a given charter date, then constructs a passenger list with crew roles. The key insight: crew rows in the contacts database have a special crew: true flag and must be validated against DynamoDB to determine their actual role on that date (captain, first mate, deckhand). 


# Pseudocode: manifest generation flow
crew_records = ddb.scan("jada-crew-dispatch", Key={"charter_date": "2026-07-04"})
manifest = []
for guest in booking.guests:
    if guest["crew"]:
        role = crew_records.get(guest["id"], {}).get("role")
        manifest.append(f"{guest['name']} ({role})")
    else:
        manifest.append(guest["name"])

This approach decouples crew role assignment (live in DynamoDB) from the contact database (static), allowing crew slots to be reassigned in real-time without manual document regeneration.

Document Generation with Photo Codes

Trip sheets and waivers are generated via /Users/cb/icloud-jada-ops/generate_waiver.py and share a common templating pattern. Each document includes a dynamically injected photo_code—a unique identifier that allows crew to tag guest photos post-charter for gallery organization.

The template substitution pattern uses Python string formatting with a dictionary of charter metadata: 


charter_context = {
    "charter_date": "2026-07-04",
    "guest_count": 30,
    "captain": "Capt. Joseph",
    "photo_code": "DYLAN-26-07-04",
}
waiver_html = template.format(**charter_context)

These documents are written to /Users/cb/.claude/jobs/de14863d/tmp/ (per-job scratch space), validated, then uploaded to S3 at paths like s3://jada-print/2026-07-04-dylan/trip-sheet.html.

CloudFront Cache Invalidation Strategy

Because trip sheets and waivers may be reprinted after crew changes, we invalidate the CloudFront distribution immediately after upload. The invalidation targets the specific charter date path: 


aws cloudfront create-invalidation \
  --distribution-id E2ABC123DEF456 \
  --paths "/2026-07-04-dylan/*"

This ensures guests retrieve the latest version (with updated captain/crew names) even if they've already fetched the page. The distribution is configured with a 24-hour TTL for most static content but shorter TTLs for print documents.

Digest & Follow-Up Template System

The follow-up system (/Users/cb/.claude/skills/jada-followup/) decouples email templates from delivery logic. Templates are stored in JSON (reference/followup_templates.json), each with placeholders: 


{
  "post_charter_guest_followup": {
    "subject": "Thanks for sailing with Queen of San Diego!",
    "body": "Hi {{guest_name}},\nYour photos are ready at {{gallery_link}}...",
    "exclude_crew": true
  }
}

The builder script (scripts/build_followups.py) reads the contacts database, hydrates templates with guest/crew metadata, and queues emails for delivery via Gmail API. Key safety feature: the exclude_crew flag prevents crew from receiving guest-only follow-ups, and the system scrubs prospective/test rows (identified by event name parsing) from delivery.

Morning Digest with LaunchAgent

The digest builder (/Users/cb/icloud-jada-ops/digest/build_digest.py) runs on a schedule via macOS LaunchAgent (/Users/cb/Library/LaunchAgents/com.jada.morning-digest.plist). The plist is configured for daily execution: 


<key>StartCalendarInterval</key>
<array>
  <dict>
    <key>Hour</key>
    <integer>6</integer>
    <key>Minute</key>
    <integer>30</integer>
  </dict>
</array>

The digest aggregates overnight bookings, crew changes, and pending waivers into a summary email. By running locally on the ops machine, it avoids Lambda cold-start latency and keeps ops coordination instant.

DynamoDB Query Patterns

The crew dispatch table uses a composite key:

  • Partition Key: charter_date (e.g., "2026-07-04")
  • Sort Key: crew_id

For large result sets, we use paginated scans with exponential backoff to handle throttling: 


paginator = ddb.get_paginator('scan')
pages = paginator.paginate(
  TableName='jada-crew-dispatch',
  FilterExpression='begins_with(charter_date, :date)',
  ExpressionAttributeValues={':date': '2026-07'}
)
for page in pages:
    process(page['Items'])

This pattern scales safely across thousands of crew assignments without exceeding DynamoDB's provisioned capacity.

Key Decisions & Rationale

  • LaunchAgent over Lambda for scheduled tasks: The digest runs every morning at 06:30 on the ops machine. We chose LaunchAgent (native macOS) over Lambda because it has zero cold-start latency, keeps logs local for audit, and avoids AWS API calls for a synchronous morning routine. Lambda shines for sporadic, decoupled tasks; this is a critical-path daily ritual.
  • CloudFront invalidation on every document update: Guest-facing documents (trip sheets, waivers) must always reflect the latest crew assignments. Aggressive invalidation (24-hour cache for content, immediate for crew changes) prioritizes correctness over cache hit rate. The cost is negligible; correctness is paramount.
  • DynamoDB for crew dispatch instead of relational DB: Crew assignments are schemaless (roles vary per charter type), sparse (many empty slots), and queried heavily by date. DynamoDB's partition by date and on-demand scaling fits this pattern better than maintaining a normalized schema in RDS.
  • Template-based document generation: Rather than hardcoding trip sheet structure in code, we use HTML templates with placeholder substitution. This decouples content (templates, managed separately) from logic (Python script). Non-technical staff can update trip sheet wording without touching code.

Infrastructure & Deployments

All generated documents are deployed to a single S3 bucket with a path structure for easy reference and archive: 


s3://jada-print/
  2026-07-04-dylan/
    trip-sheet.html
    waiver.html
  2026-07-03-sunset/
    trip-sheet.html
    waiver.html

The CloudFront distribution serves this bucket with a custom domain, routing print.queenofsandiego.com to s3://jada-print. Crew members and guests receive short, memorable URLs (e.g., print.queenofsandiego.com/2026-07-04-dylan/trip-sheet) that can be printed or shared.

Lambda functions (shipcaptaincrew, dc-chat-api) in the same AWS account consume the DynamoDB crew dispatch table to power web dashboards and chatbot features, creating a single source of truth.

What's Next

  • Crew onboarding automation: New crew members currently require manual entry in contacts, DynamoDB, and crew-pages. We plan to build a self-service intake form that auto-populates all three systems and generates a crew-specific credential package.
  • Real-time crew status dashboard: Display live crew assignments, waiver status, and manifest readiness in a web UI. Currently, this information is scattered across email, DynamoDB, and local files.
  • Ledger integration: Charter revenue and crew payouts are manually tracked. Automating deposit reconciliation (Zelle, bank transfers) against booked charters will close the financial loop end-to-end.
  • Guest photo gallery automation: Photo codes embedded in trip sheets can trigger automatic gallery organization. We'll build a post-charter workflow that retrieves photos tagged with the charter's code and publishes them to a guest-facing gallery.

Conclusion

This system transforms charter operations from a manual, error-prone process into an automated, event-driven workflow. By separating concerns (DynamoDB for state, S3 for content, LaunchAgent for scheduling), we've built a foundation that scales with the business while keeping operational overhead flat. The next phase focuses on closing gaps in crew onboarding and financial reconciliation, but the core infrastructure is now live and handling real charters with zero manual intervention.

```