Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Automating Charter Pre-Departure Coordination: The July 4 Dylan Osborne Charter Case Study

What Was Done

We coordinated a full pre-departure workflow for a July 4, 7–10 PM charter (organizer: Dylan Osborne) from initial DynamoDB record through crew manifest and guest list capture. The work surfaced and fixed a systematic bug in crew call time calculation, redesigned the ledger system to eliminate Google Sheets auth friction, and integrated photo-sharing coordination into the organizer workflow.

Technical Details: The Charter Ops Flow

Our charter workflow follows five stages (defined in ~/icloud-jada-ops/CHARTER-WORKFLOW.md):

  • Step A (Calendar Parse): Event appears on calendar; system notes date/time/organizer.
  • Step B (DDB Record): Create DynamoDB record in jada-crew-dispatch table (region: us-east-1) with organizer contact, payment status, and derived fields.
  • Step C (Docs to Crew): Generate trip sheet and waiver PDFs; deploy to public S3; send print URLs to crew lead (Daniela, in this case) via SMS or email.
  • Step D (Organizer Coordination): Send organizer the manifest request template, photo-sharing code, and trip sheet; request guest names and confirmation within 48 hours of departure.
  • Step E (Final Clearance): Validate manifest population, payment status, and crew muster time; flag for captain if issues exist.

For Dylan's charter, we moved from a partial deployment (docs generated but not yet live, payment status stale, crew call time incorrect) to full coordination ready.

The Crew Call Time Bug and Fix

We discovered that the trip sheet showed crew muster at 4:45 PM for a 7 PM departure—padding an hour and forty-five minutes before the captain needed to depart. This violated the operational rule: crew muster = departure time minus one hour, no earlier.

Root cause: The rule was never codified in a central place; when I built the June 26 trip sheet, I invented a conservative buffer without documented justification. To fix this systematically:

  • Updated DynamoDB jada-crew-dispatch record: set crew_call_time to 18:00 (6 PM, one hour before 7 PM departure).
  • Redeployed the trip sheet via the crew-page generator; verified the rendered output on queenofsandiego.com/print/ now reads "6:00 PM — Crew Muster".
  • Added crew-muster rule to CHARTER-WORKFLOW.md Step D4 as binding policy: "Crew call time must equal departure time minus one hour. No padding earlier without explicit waiver from captain."
  • Saved the rule to memory (~/claude/projects/.../jada-crew-muster-rule.md) so future sessions apply it consistently.

Payment Status and DDB Sync

Dylan's payment had been recorded in the ledger as a $500 Zelle deposit, but the DDB record payment_status field and contacts registry were out of sync. We corrected this chain:

  • Updated DDB item for Dylan's charter: description field now reads "$500 deposit via Zelle · $3,250 balance due at boarding".
  • Updated ~/icloud-jada-ops/passengers/contacts.csv: marked Dylan as direct booking with deposit status 🟡 (deposit received, balance pending).
  • Queried ledger to confirm: python3 ledger.py receipt dylan-jul4 returns paid $500, balance $3,250, status 🟡 deposit received.

Docs Deployment: S3 and Public Print URLs

Trip sheet and waiver PDFs are served via a static S3 bucket backing queenofsandiego.com/print/ (CloudFront distribution with cache control). We deployed Dylan's charter docs as: 

queenofsandiego.com/print/dylan-jul4-trip-sheet.html
queenofsandiego.com/print/dylan-jul4-waiver.pdf

The crew-page generator (Python, source in ~/icloud-jada-ops) reads the DDB record and renders the trip sheet as HTML (print-friendly CSS); the waiver is a static PDF. Both live behind CloudFront; crew can print directly without auth. We verified both URLs are live before notifying Daniela.

Organizer Coordination: Photo Code and Manifest Request

Dylan receives a multi-part organizer SMS drafted to ~/icloud-jada-ops/drafts/DRAFT-dylan-jul4-balance-sms.txt:

  • Trip sheet and waiver links: Crew has printed these; organizer sees them for reference.
  • Balance due: "$3,250 due at boarding; captain cannot depart until this is cleared." (Zelle deposit already recorded.)
  • Guest manifest: "Please list your guest names using this form: [manifest URL]. This must be submitted 48 hours before departure."
  • Photo code: "Your group's photo code is BYHMJG. Share this with your guests so they can upload photos to the shared gallery after the trip."

The manifest is a form template stored in ~/icloud-jada-ops/manifests/ that Dylan populates and returns; the photo code ties into a guest-upload flow separate from core ops but referenced in every organizer pre-charter message.

Ledger Redesign: JSON Canonical, CSV Export

Our ledger was previously Google Sheets–based, but token expiration and the need for offline-available, version-controlled receivables tracking led to a redesign:

  • Canonical source: ~/icloud-jada-ops/ledger.json (append-only payment array per charter, no overwrites or silent edits).
  • Why JSON, not Google Sheets: Auth-once design (Google tokens have died repeatedly); crew pages, DDB sync, and digest checks read it without re-authenticating. Append-only structure prevents accidental balance overwrites.
  • UX export: python3 ledger.py export writes ledger.csv with charge, payment, running balance, and status columns—opens in Numbers or Excel for at-a-glance crew reconciliation.
  • DDB sync: Ledger status updates (🟢 paid, 🟡 deposit received, 🔴 balance due) propagate to DDB payment_status field via ledger.py sync.

This is not full double-entry accounting (no expense tracking, chart of accounts, or tax reporting). It's a receivables ledger—enough to answer "who paid what and what's outstanding" without external auth dependency.

Infrastructure and Tooling

  • DynamoDB: jada-crew-dispatch table, us-east-1 region. Query key: charter_id=dylan-jul4. Schema includes crew_call_time, payment_status, contact_lead, organizer email/phone.
  • S3 and CloudFront: Static site at queenofsandiego.com backed by S3 jada-ops-print bucket (example path). CloudFront distribution ID available via aws cloudfront list-distributions.
  • AWS CLI Profile: queenofsandiego profile used for all DDB and S3 operations in this session (credentials managed via ~/.aws/credentials).
  • SES: Organizer and crew emails sent via SES sender identity charter-ops@sailjada.com (configured in ~/icloud-jada-ops/send-email.py).
  • SMS: Text coordination via ~/icloud-jada-ops/send-sms.sh (text-only; links rendered as URLs, not clickable).

Key Decisions

  • Keep ledger JSON, not Google Sheets: Eliminates auth friction for background jobs; append-only prevents silent overwrites; version control lives in jada-ops where ops code lives.
  • Crew call time policy in CHARTER-WORKFLOW.md and memory: Rules written once, enforced consistently; future crew-page generations will pull the correct value from DDB, not invent padding.
  • Photo code in every organizer pre-charter message: Integrated into taste-to-ops-flow (guest logistics = ops-critical); every SMS/email template now includes it.
  • DDB as source of truth for crew metadata: Contacts.csv syncs from DDB, not vice versa; single source of truth reduces coordination bugs.

What's Next

Dylan's charter is ready for final coordination: his SMS with balance due, manifest request, and photo code is drafted and awaiting send approval. Daniela has the print links. Our next check (48 hours before departure, ~July 2 12 PM) will validate: (1) Dylan has submitted a populated manifest, (2) payment is cleared, (3) crew is confirmed. The crew muster rule is now baked into every future trip sheet, preventing this padding bug in the next charter cycle.