Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Automating Guest Document Delivery and Follow-ups in a Charter Operations Platform

What Was Done

This session implemented three interconnected automation systems to streamline guest communication and document delivery in a marine charter booking platform. The work spans follow-up email templating, automated morning digests, and dynamic trip sheet generation with live deployment to S3/CloudFront.

Follow-up Automation Architecture

The follow-up system lives in /Users/cb/.claude/skills/jada-followup/ and uses JSON templates to generate guest communications. The structure is:

  • reference/followup_templates.json — Template definitions with placeholders for guest names, dates, and charter details
  • scripts/build_followups.py — Python script that parses templates, queries the contacts database, and generates email payloads

The templates use a property-based design where each contact row (parsed from contacts.csv) maps to follow-up triggers. The builder identifies which templates apply to each guest based on event type and booking status, avoiding duplicates via a suppression ledger. This avoids the brittleness of hardcoded email logic and lets non-engineers update follow-up sequences by editing JSON.

Why JSON templates: Reduces code changes for marketing/ops changes; templates are version-controllable and can be reviewed independently of Python logic. The builder validates template schema at parse time to catch typos early.

Morning Digest System

A scheduled digest aggregates overnight bookings, cancellations, and task reminders into a single email, minted daily by a macOS LaunchAgent.

  • /Users/cb/icloud-jada-ops/digest/build_digest.py — Builds the digest payload by querying the contacts ledger, pending followups, and charter dates
  • /Users/cb/Library/LaunchAgents/com.jada.morning-digest.plist — LaunchAgent configuration that runs the script daily

The digest script:

  • Reads the ledger JSON to extract new bookings and payment status
  • Queries follow-up templates and contacts to identify outbound communications due today
  • Formats output as a concise HTML email sent via Gmail API

LaunchAgent was chosen over cron because it integrates with macOS authorization (reads Keychain-stored OAuth without additional configuration) and provides built-in restart-on-failure behavior. The plist is loaded into the user's session with launchctl load, ensuring the digest runs reliably across reboots.

Trip Sheet Deployment Pipeline

The most critical automation: generating trip sheets on-demand and deploying them to production with crew assignments and guest-facing details.

Generation & Schema:

Trip sheets are HTML templates populated from DynamoDB and the local ledger. The file /Users/cb/icloud-jada-ops/2026-07-04-dylan/send_daniela_print_docs_jul04.py demonstrates the pattern:

  • Query the jada-crew-dispatch DynamoDB table in us-east-1 for crew assignments matching the sail date (2026-07-04)
  • Fetch the charter ledger entry to extract boat details, captain, and photo code
  • Render the trip sheet HTML with crew names, photo code, and waiver (three-column format: Printed Name | Signature | Date, 30 rows per USCG souls capacity)
  • Upload to S3 at s3://queenofsandiego.com/print/2026-07-04-dylan-trip-sheet.html
  • Invalidate the CloudFront distribution cache to push live immediately

Why DynamoDB for crew: Crew assignments change frequently and need to be queryable by date and event ID. DynamoDB's on-demand pricing avoids over-provisioning for peak-hour traffic, and the scan operation tolerates the transient network flakiness visible in the session (smaller page sizes, retries built into the boto3 client).

Document Delivery: Once uploaded, the trip sheet and waiver are sent via Gmail API to the designated guest contact. In this case, a POST to the Gmail API with the document URLs embedded in the message body ensures guests receive production URLs (not local drafts) and eliminates the risk of local file attachments going stale.

Infrastructure & Deployment

S3 Bucket Structure:

Guest-facing documents live at s3://queenofsandiego.com/print/ with predictable naming: {YYYY-MM-DD}-{skipper-name}-{doc-type}.html. This pattern allows direct URL sharing and makes it easy to locate old trip sheets for auditing.

CloudFront Invalidation: After each S3 upload, the deployment script calls CloudFront's invalidation API to purge cache for the specific document key. This ensures guests always see the latest crew assignments and photo codes, even if they refresh within seconds of upload. The invalidation is idempotent (safe to retry) and typically completes within 30 seconds.

GitHub-style Canonical Locations: Generated documents are synced back to the local canonical path (/Users/cb/icloud-jada-ops/2026-07-04-dylan/) after upload, making it easy for ops to review what's live and when it was last modified. This mirrors the git pattern: local working copy stays in sync with the remote source of truth.

Key Decisions & Rationale

  • Python for scripting (not Bash): JSON parsing, DynamoDB queries, and Gmail API calls all have robust Python libraries. Bash would require external commands and string parsing, introducing parsing bugs and maintenance burden.
  • Scheduled automation on the ops Mac, not Lambda: LaunchAgent avoids cold-start latency and integrates natively with Keychain auth. For interactive workflows (where a human may trigger a rebuild), local execution feels more responsive. Lambda would be added for truly scheduled, backend-only tasks (e.g., daily digest if the ops team never manually triggers it).
  • DynamoDB sourced crew, not local CSV: Crew availability changes during the day (illness, no-shows). DynamoDB gives the booking system a single source of truth; the trip sheet always queries fresh data at document-generation time, not stale CSV.
  • Photo code as a standing rule, not ad-hoc: Initially, photo codes were added only to this trip sheet. After verifying it worked, the requirement was codified in /Users/cb/icloud-jada-ops/CHARTER-WORKFLOW.md as Step D4 (trip sheet + waiver requirements: photo code mandatory, crew names only from DDB, no "CB" on docs). This prevents future trip sheets from omitting it and makes the requirement visible to anyone reading the workflow.

What's Next

DynamoDB Crew Sync: The crew-dispatch table timed out during this session; a retry with smaller page sizes is running in the background. Once it returns, the trip sheet will be updated live at the same S3 URL (guests don't need a new link). If crew assignments genuinely aren't in DDB yet, an explicit override mechanism will fill them in.

Payment Reconciliation: The ledger shows only a $500 Boatsetter deposit against a $3,750 charter cost ($3,250 open the night before sail). The next step is confirming whether Boatsetter collected the balance, then recording it via ledger.py add or escalating for manual collection.

Scaling Follow-ups: Once the template system stabilizes with a few real sends, consider migrating scheduled sends to Lambda + EventBridge to decouple timing from the ops Mac's uptime and network. Digest emails could trigger from S3 events (new bookings) or DynamoDB Streams, turning the system from scheduled-polling to event-driven.