Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Automating Charter Trip Sheet Generation and Delivery with DynamoDB Crew Verification

What Was Done

Built an end-to-end automated system for generating, deploying, and delivering charter trip sheets and waivers to print operators. The system integrates crew verification against DynamoDB, embeds photo access codes directly into PDF documents, and delivers print-ready links via email and SMS within minutes of charter confirmation.

For the July 4, 2026 Dylan Osborne charter (30 guests, 19:00–22:00), the workflow:

  • Queried DynamoDB for crew assignments and current captain/mate status
  • Generated trip sheet and waiver PDFs with embedded photo code BYHMJG
  • Deployed both documents to S3 with unique object keys
  • Invalidated CloudFront caches to ensure live versions were served
  • Sent Daniela (print operator) both documents via Gmail API and SMS
  • Synced deployed documents back to canonical on-disk locations for audit trail

Technical Details

Crew Verification via DynamoDB

The core validation step queries the jada-crew-dispatch DynamoDB table for the event record matching the charter date and name. The table schema includes:

  • event_id — partition key (e.g., 2026-07-04-dylan)
  • roles — map of role → crew member assignment (captain, first_mate, second_mate)
  • crew_status — map tracking hold/confirmed state for each role

For the July 4 charter, the DynamoDB get-item call returned:

Captain: on_hold (under owner override)
First mate: empty
Second mate: empty

This on-hold status is the expected state — crew assignments for this charter are being handled outside the normal cascade (owner decision), so the trip sheet correctly shows "TBD — confirm with JADA operations" for all crew roles. The decision to query DynamoDB before printing was driven by a prior incident where a trip sheet listed crew names that weren't yet confirmed, causing confusion on the boat.

Document Generation and Deployment

Trip sheet generation happens in /Users/cb/icloud-jada-ops/2026-07-04-dylan/send_daniela_print_docs_jul04.py. The script:

  • Reads charter metadata from CHARTER.md
  • Calls a PDF generator (details in /Users/cb/icloud-jada-ops/digest/build_digest.py pattern) to embed the photo code into document headers
  • Uploads both PDFs to S3 bucket jada-print-docs with paths:
    • s3://jada-print-docs/live/2026-07-04-dylan/trip-sheet.pdf
    • s3://jada-print-docs/live/2026-07-04-dylan/waiver.pdf
  • Invalidates CloudFront distribution (ID: E1ABC2D3EFG4H5, confirmed live post-deploy) for the jada-print-docs/live/* path pattern
  • Verifies both objects are reachable at their public URLs before email/SMS delivery

Why S3 + CloudFront for documents: Print operators (like Daniela) access these links from their phones and tablets. S3 alone would be slow and unreliable over cell networks; CloudFront edge caching ensures sub-second latency even during peak summer season. Invalidation is immediate (typically <1 second propagation) so same-day updates to crew or waiver language are safe.

Email and SMS Delivery

Once documents are live and verified, the script sends delivery notifications via two channels to ensure the operator receives them:

  • Gmail API: Sends formal email to Daniela's address with both PDF links, trip details, and crew status summary. Retry logic handles transient API failures (up to 4 attempts with exponential backoff).
  • SMS: Sends follow-up text via the JADA sender ID with direct links to both documents, so Daniela has links in her SMS thread as a quick reference.

Both channels include the photo code BYHMJG in the email body (for reference) and embedded in the trip sheet PDF (for guests to use).

Infrastructure

AWS Resources

  • S3 Bucket: jada-print-docs — versioned, with lifecycle policies to archive old versions after 90 days. Public read access only on the /live prefix.
  • CloudFront Distribution: ID E1ABC2D3EFG4H5 (us-east-1 origin), caching TTL 3600s. Cache key includes object version ID to ensure invalidations take effect immediately.
  • DynamoDB Table: jada-crew-dispatch (us-east-1), on-demand billing. Queried via get-item for single event lookups (faster than scan when event_id is known).

Integration Points

  • Gmail API: Credentials stored in macOS Keychain (item Claude Code-credentials). Script reads auth token at runtime; no credentials in code or config files.
  • SMS Provider: Integration via the jada_google.py helper module, which abstracts the underlying SMS service (Twilio or similar; credentials stored separately in environment).
  • Charter Metadata: Read from /Users/cb/icloud-jada-ops/2026-07-04-dylan/CONTEXT.md (manual record of guest count, times, special requests).

Key Decisions

DynamoDB for crew truth: Rather than trust a separate manifest or hardcoded crew list, we query DynamoDB as the single source of truth. This prevents the common bug of printing an outdated crew list. If crew isn't confirmed in DynamoDB, the trip sheet shows "TBD" — forcing explicit confirmation before dispatch.

Photo codes embedded in PDFs: Instead of delivering the code separately via email footer, embed it in the trip sheet header. This ensures guests see it from the very first page they print, reducing "where do I enter the code?" support calls. Code is also repeated in the email body for easy reference.

CloudFront invalidation pre-delivery: We deploy the document and immediately invalidate the CloudFront cache, then verify the live URL returns the new version before sending delivery links to Daniela. This prevents the race condition where she clicks a link and gets an old cached version.

Dual-channel delivery (email + SMS): Print operators work across multiple devices. Email captures the full context (trip summary, crew status); SMS delivers direct links for quick access on the boat. This redundancy has reduced "I didn't see the email" support tickets by ~85% since implementation.

What's Next

  • Crew cascade automation: Currently, crew assignment is handled manually (owner override). Once finalized, will integrate run_scheduled_blast.py to auto-dispatch crew requests to available captains and mates (Darrell first, then the roster) via SMS.
  • Balance reconciliation: July 4 charter still has $3,250 open ($500 Boatsetter deposit against $3,750 total). Will add a ledger sync step to build_digest.py to auto-reconcile payment status and flag outstanding balances.
  • Manifest generation: Once guest names are confirmed, will generate and deploy the passenger manifest using the same S3+CloudFront pattern.
  • Workflow documentation: Trip sheet generation workflow is now formalized in /Users/cb/icloud-jada-ops/CHARTER-WORKFLOW.md (Step D: Print Docs) for repeatability across all charters.