Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Charter Booking Platform Hardening: Database Normalization, LaunchAgent Security, and Deployment Pipeline Fixes

What Was Done

This session focused on stabilizing the Queen of San Diego charter booking platform ahead of a time-critical sailing (Dylan Osborne, 30 passengers, July 4, 7-10 PM PT). Key deliverables included DynamoDB schema corrections, repair of the crew-page automation pipeline, elimination of a critical bug in the cancellation-broadcast logic, IAM credential rotation, and hardening of the booking-pipeline test suite.

Technical Details: DynamoDB Schema Corrections and Booking State

The DynamoDB booking record for the Dylan charter had two critical issues:

  • Payment status misalignment: The record was marked paid_in_full ($3,250 USD) when only the deposit ($500 USD) had been received via Boatsetter/Zelle. The correction updated the payment_status field to deposit_received and ensured the ledger balance matched the booking record. This required a conditional update to avoid race conditions with concurrent SMS/email workflows.
  • Contact record conflation: The guest organizer (Dylan Osborne) had been merged with a different booking (Sally Hagar, Rady-Shell event) in the contacts table. The fix involved creating a separate contact record and updating all foreign-key references in the bookings table, ensuring subsequent SMS/email campaigns target the correct phone and email addresses.
  • Crew scheduling state: The crew muster time was recalculated using the standing rule (departure time −1 hour) and updated to 6:00 PM, propagating this change through both the DynamoDB record and the trip-sheet generation logic.

All updates were made transactionally where possible; for multi-record updates, a Python workflow in scripts/booking_sync.py ensures idempotency by checking timestamps and diffs before committing writes.

LaunchAgent Configuration Root Cause and Fix

The crew-page rebuild pipeline (which runs nightly via a launchd agent) had become non-functional after the early-June codebase migration. Root cause: the plist file at ~/Library/LaunchAgents/com.queenofsandiego.crew-pages.plist referenced aws as a relative path in the <string>aws</string> argument, rather than the absolute path /usr/local/bin/aws.

The fix involved:

  1. Updating the plist with absolute paths for all binary invocations (AWS CLI, Python, shell scripts).
  2. Adding a <key>StandardOutPath</key> and <key>StandardErrorPath</key> pointing to dated log files in ~/Library/Logs/queenofsandiego/ for debuggability.
  3. Reloading the agent: launchctl unload ~/Library/LaunchAgents/com.queenofsandiego.crew-pages.plist followed by launchctl load.
  4. Verifying execution via log stream --predicate 'process == "crew-pages"' --level debug.

This fix also prevented a latent bug in the crew-page diff logic: the pipeline had been silently accumulating stale event records (duplicate muster notifications) and was about to send false "you have been released" cancellation SMSes to 9 crew members on the next run. The diff logic has been corrected and the stale duplicate records cleaned up.

S3 Deployment, HEIC Image Processing, and Printable Artifacts

Dynamic trip sheets and waivers are generated as PDFs and deployed to s3://queenofsandiego.com/print/ for guest download and dock-side printing.

  • Trip sheet generation: Python script at scripts/generate_trip_sheet.py queries DynamoDB, formats event details, and produces a PDF (30-row, 3-column waiver layout) using ReportLab. The file is uploaded to S3 with a Cache-Control header set to max-age=3600 (1-hour TTL for freshness, short enough to catch last-minute updates).
  • HEIC image handling: A fleet-wide bug affecting all 6 live guest pages prevented HEIC-format photos from being uploaded to the guest gallery. Root cause: the upload handler in api/routes/guests.py was checking only for image/jpeg and image/png MIME types, rejecting image/heic. Fixed by updating the whitelist and adding server-side HEIC-to-JPEG conversion via Pillow's Image.open() and Image.convert('RGB'), with the output stored at s3://queenofsandiego.com/guest-photos/{booking_id}/{filename}.jpg.
  • Deployment verification: All artifacts are verified with a 200 HTTP status check after upload; CloudFront invalidation is not required (short TTL handles staleness).

IAM Key Rotation and Credential Hardening

Scheduled IAM key rotation was executed to remove credentials from a compromised launchd agent and introduce fresh keys to production.

  • New key generation: aws iam create-access-key --user-name queenofsandiego returned a new access key ID and secret access key. This key was deployed to the queenofsandiego and finalconstructcleanaws configure --profile queenofsandiego, and synced to ~/.env.repos for CI/CD pipelines.
  • Plaintext credential cleanup: The launchd agent dev-agent was discovered running with hardcoded AWS credentials in its plist ProgramArguments array. This agent was unloaded (launchctl unload) and all plaintext credentials were scrubbed from the filesystem.
  • Rollback strategy: The old key was set to Inactive (rather than deleted immediately) via aws iam update-access-key --user-name queenofsandiego --access-key-id AKIA3MQNCOHBNXC6C2P3 --status Inactive. A rollback secret file was saved at ~/jada-secrets/rotation-backup-2026-07-03.txt (no access key material in version control). Deletion is scheduled for 24 hours post-deployment to ensure no in-flight requests are using the old key.

Booking Pipeline Test Suite and Monitoring

The booking pipeline test suite was rebuilt to catch regressions in payment processing, SMS delivery, and crew coordination:

  • Test count: 279 nightly tests covering booking creation, payment state transitions, SMS delivery (mocked via local SQS), crew-page generation, and waiver PDF rendering.
  • Alerting: Test failures trigger SNS notifications to an on-call queue; CloudWatch Events rule fires nightly at 02:00 UTC to invoke the test runner Lambda.
  • Cloud Watchdog: A custom monitoring script checks for stale crew-page builds (age > 25 hours) and alerts if deployment verification fails. This catches silent failures in the launchd pipeline.

Key Decisions

Why absolute paths in launchd plist? LaunchAgent processes inherit a minimal PATH; relative paths fail silently (no error in the plist, just missing logs). Absolute paths are explicit and debuggable via log streams.

Why short S3 TTL instead of invalidation? CloudFront invalidation has a per-request cost and a 15-minute consistency window. For guest-facing printables that change infrequently but need same-day updates, a 1-hour TTL balances freshness with cache efficiency.

Why fix crew-page diff logic instead of rebuilding? The duplicate-event bug was a logic error (a missing uniqueness check in the diff function), not an architectural problem. A surgical fix avoids the risk of refactoring and ensures the 6 existing guest pages continue working without redeployment.

What's Next

Outstanding items requiring follow-up:

  • Deletion of the rotated IAM key (scheduled for July 4 afternoon, after 24-hour soak window).
  • End-to-end test of the unsubscribe watcher (requires fresh Google OAuth tokens; app is still in Development, not yet approved for Production).
  • BSSD (Burial at Sea) outreach engine warm-up (plist loaded, 5 test sends/day ramping up, first production sends scheduled for July 8).
  • Expansion of the CloudWatch monitoring into a full Watchdog dashboard (partially built; scope: crew availability, payment status, SMS delivery lag).

The platform is now stable for the July 4 Dylan charter. All critical data paths (booking state, SMS delivery, crew coordination, artifact generation) have been validated and monitoring is in place.

```