Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Multi-Domain Executive Intelligence Platform: Building C-Suite Reporting Infrastructure Across Four Revenue Entities

What Was Done

Over the course of a focused development sprint, we engineered and deployed a comprehensive executive reporting system designed to surface critical business intelligence across four distinct revenue-generating entities: JADA (luxury yacht charter), QueenofSanDiego.com (premium experiences), QuickDumpNow (service logistics), and DangerousCentaur (client portfolio management). The system generates five specialized reports—each written from a distinct C-suite perspective—and distributes them via Amazon SES to a unified inbox for stakeholder review.

In parallel, we refactored the Ship Captain Crew (SCC) booking tool Lambda function and frontend, hardened authentication flows with JWT token management, implemented dynamic event scheduling with sunset-time calculations, and built a comprehensive checklist system with role-based access controls. All infrastructure changes were deployed to production with zero downtime using CloudFront invalidation patterns.

Technical Architecture

Executive Report Generation Pipeline

The reporting system consists of two Python scripts deployed to the development workstation:

  • /Users/cb/Documents/repos/tools/send_exec_reports.py — Primary report generation and dispatch engine
  • /Users/cb/Documents/repos/tools/send_exec_reports_2.py — Secondary variant for batch processing and retry logic

Each report is generated by instantiating a domain-specific perspective class (CEO, CTO, CMO, CFO, Accounting Officer) that inherits shared query methods for asset inventory, financial data access, and metric aggregation. The system retrieves:

  • Asset inventory across all four entities from the canonical wiki at /Users/cb/Documents/repos/wiki/index.md
  • Project handoffs and task state from /Users/cb/Documents/repos/agent_handoffs/projects/
  • Email configuration and SES credentials from repos.env (verified sender: admin@queenofsandiego.com)
  • Financial transaction logs and billing records from DynamoDB tables (naming convention: qos-{entity}-{resource-type})

Reports are formatted as plaintext with markdown-style headers for email readability, then dispatched via boto3 SES client to c.b.ladd@gmail.com with BCC to admin@queenofsandiego.com. The SES configuration uses the verified sender identity to avoid quarantine filters.

Ship Captain Crew (SCC) Lambda Refactoring

The SCC booking system is a serverless stack consisting of:

  • Backend: AWS Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py
  • Frontend: Single-page application at .../shipcaptaincrew/frontend/index.html
  • Compute: AWS Lambda (Python 3.11+)
  • Storage: DynamoDB table scc-events (partition key: event_id), scc-checklists (partition key: checklist_id)
  • API Gateway: REST endpoint at https://api.queenofsandiego.com/scc
  • Frontend Hosting: S3 bucket qos-scc-frontend-prod, served via CloudFront distribution ID E2X4K9Z7Q1M3

The refactored Lambda function implements the following routes:

POST   /events              — Create new charter event (admin only)
GET    /events/{event_id}   — Retrieve event details with role-based filtering
POST   /events/{event_id}/checklist — Submit checklist answers
GET    /auth/magic-link     — Validate JWT magic link tokens
POST   /roles/{role_id}/claim      — Captain/crew claim role assignment
POST   /roles/{role_id}/release    — Release role back to pool
GET    /guests/{guest_code} — Guest waiver and checklist page (unauthenticated)

Authentication & Authorization

The system uses JWT tokens for authenticated operations. The Lambda function stores JWT_SECRET in AWS Secrets Manager and validates tokens on every protected route. Magic link tokens are short-lived (15-minute TTL) and include user identity, email, and role constraints. For guest access, we implemented a six-character alphanumeric short code stored in DynamoDB with event context, allowing unauthenticated waiver submission without exposing JWT internals.

Role assignment uses a "claim" pattern: a user receives a magic link with role constraints, navigates to the claim endpoint, and atomically updates the DynamoDB event record to assign their identity to the role. Role release follows the inverse path, clearing the assigned user while preserving historical audit data in a separate scc-audit-log table.

Dynamic Event Scheduling

The system calculates sunset and departure times for San Diego-based charters using the datetime library and geolocation data. The lambda function queries a lightweight solar calculation function (implemented via inline math, not external API calls) to determine event timing constraints. This allows the frontend checklist interface to dynamically render timing-dependent fields (departure briefing, return-to-dock deadlines, evening entertainment windows) without additional API latency.

Infrastructure & Deployment

Lambda Deployment Pipeline

Before deploying, we perform syntax validation on the Python source:

python3 -m py_compile lambda_function.py

The Lambda function is then packaged as a zip artifact (including all dependencies), uploaded to S3 at s3://qos-lambda-builds/scc/$(date +%s).zip, and deployed using the AWS CLI:

aws lambda update-function-code \
  --function-name scc-booking-api \
  --s3-bucket qos-lambda-builds \
  --s3-key scc/$(date +%s).zip

A maintenance work-log entry is created in DynamoDB after each deployment, recording the timestamp, deployer identity, and key changes. This enables rapid rollback if issues surface.

Frontend Deployment & CDN Invalidation

The SPA frontend is deployed to S3 and served via CloudFront. We use a two-step deployment process:

aws s3 sync frontend/ s3://qos-scc-frontend-prod/ --delete
aws cloudfront create-invalidation \
  --distribution-id E2X4K9Z7Q1M3 \
  --paths "/*"

The full-path invalidation ensures all users receive updated assets within 60 seconds of deployment, avoiding stale JavaScript and HTML caching issues. We use cache busting on index.html (TTL: 1 minute) while assets like main.js and styles.css carry longer TTLs (24 hours) keyed by content hash.

DynamoDB Schema

Three primary tables support the system:

  • scc-events — Partition: event_id, Sort: created_at. Attributes: