Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Building a Multi-Domain Executive Reporting Pipeline: SES Integration, Lambda Deployment, and Real-Time Dashboard Updates

Over the past development session, we built and deployed a comprehensive executive reporting system that ingests portfolio data across four distinct business entities (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur), generates perspective-specific C-suite analysis reports, and distributes them via AWS SES with real-time dashboard tracking. This post details the technical architecture, deployment challenges, and key decisions made to ship this system.

What Was Done

We created a multi-report generation pipeline that:

  • Built two Python scripts (/Users/cb/Documents/repos/tools/send_exec_reports.py and send_exec_reports_2.py) to orchestrate SES email delivery across five distinct C-suite perspectives (CEO, CTO, CFO, CMO, Accounting)
  • Deployed an updated Lambda function to the Ship Captain Crew tool at /sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py with enhanced event creation, checklist tracking, and JWT-based authentication
  • Refreshed the Ship Captain Crew frontend (frontend/index.html) with timing panel improvements and checklist UI refinements
  • Established EventBridge cron rules for automated nudge notifications
  • Generated magic link authentication flows for invite-based user onboarding
  • Created dashboard tracking cards to monitor report delivery and adoption

Technical Architecture: The Reporting Pipeline

Email Delivery via SES

The core reporting scripts leverage AWS SES for cost-effective bulk email delivery. Rather than building custom SMTP handling, we configured environment variables pointing to verified sender addresses (admin@queenofsandiego.com) and destinations pulled from repos.env. This approach avoids hardcoding credentials while maintaining auditability.

The SES integration pattern:

# Environment-driven configuration
SES_SENDER = os.getenv('SES_SENDER_ADDRESS')
SES_RECIPIENTS = os.getenv('EXEC_REPORT_RECIPIENTS').split(',')
AWS_REGION = 'us-west-2'

We verified sender addresses in the AWS SES console before deployment to avoid sandbox restrictions. Each report is sent as a separate message to enable per-recipient tracking and reduce blast-zone risk if any recipient domain has delivery issues.

Report Generation Strategy

Five reports were generated, each targeting a distinct stakeholder lens:

  • CEO Report: Asset inventory across all four entities, identified 8 critical shortfalls (empty pipeline, no revenue tracking, Sergio equity risk, zero OTA listings, DC billing model gap, QDN funnel breakage, Carole transition risk), and 9 missing KPIs with a prioritized 30-day remediation agenda.
  • CTO Report: Stack-by-stack security audit (hardcoded Stripe keys, plaintext repos.env exposure, unauthenticated GAS endpoints, missing WAF), infrastructure cost analysis (~$50–84/month AWS with $25/month optimization potential), and 10 engineering actions ranked by ROI.
  • CFO Report: Burn rate modeling (~$7–9K/month), capital deployment framework (zero-cost through revenue-producing tiers), break-even analysis at 6 charters/month, and monthly revenue targets through Q4 2026.
  • CMO Report: Channel-by-channel visibility matrix, OTA sequencing strategy (Sailo → GetMyBoat → Viator/GYG), modeled upside for immediate 3,676-person blast deployment, and 30/60/90-day marketing milestones.
  • Accounting Report: Revenue recognition issues, complete chart of accounts audit, expense categorization, and roadmap to profitability through Q1 2027.

Each report was hand-composed with specific numbers, dates, and actionable next steps rather than templated. This ensured stakeholder-specific language and prevented "spray and pray" analysis.

Lambda Deployment: Ship Captain Crew Enhancements

Event Creation and Timing Logic

The Lambda function at /sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py was updated 10 times during this session to refine:

  • Departure and return time calculations based on sunset data (computed dynamically for San Diego coordinates)
  • Checklist binding to events via event_id parameter
  • JWT validation on all protected endpoints to enforce role-based access (admin vs. crew)
  • EventBridge integration for automated nudge scheduling

The key decision here was to compute sunset times at event creation rather than deployment time. This ensures each sail reflects actual San Diego sunset data::

# Lambda event handler pattern
def create_event(event_payload, context):
    # Extract JWT from request headers
    token = extract_token_from_headers(event)
    payload = validate_jwt(token, JWT_SECRET)
    
    # Verify admin role
    if payload.get('role') != 'admin':
        return error_response(403, 'Unauthorized')
    
    # Calculate timing based on sunset
    sunset_time = get_sunset_time_for_san_diego(event_date)
    departure = sunset_time - timedelta(minutes=30)
    return_time = sunset_time + timedelta(hours=2)
    
    # Create event record and bind checklist
    event_record = create_event_in_dynamodb(...)
    create_checklist_for_event(event_record['event_id'])
    
    return success_response(event_record)

JWT Authentication Flow

Magic link authentication was implemented to avoid password management while maintaining fine-grained role control. The flow:

  1. Admin generates a magic link invite containing a JWT pre-signed with the user's role (admin or crew)
  2. Recipient clicks link, which submits the token to the authentication endpoint
  3. Lambda validates the token signature against the JWT_SECRET stored in Lambda environment variables
  4. Validated tokens are stored in DynamoDB with expiration to enable stateless session management

We avoided embedding secrets in the repository by storing JWT_SECRET solely in Lambda environment variables, decoupled from the git-committed codebase. Magic links were regenerated with full role access and resent to ensure crew members (CB and Sergio) had valid authentication on first access.

Frontend Refinements and Deployment

The Ship Captain Crew frontend was updated four times to:

  • Add timing panel UI elements for departure and return time display
  • Integrate checklist loading hooks into the event detail view
  • Bind departure/return time functions to the timing panel for real-time calculation

Frontend deployment followed this process:

# Zip Lambda for deployment
zip -r shipcaptaincrew_lambda.zip lambda_function.py

# Deploy Lambda (AWS CLI)
aws lambda update-function-code \
  --function-name shipcaptaincrew \
  --zip-file fileb://shipcaptaincrew_lambda.zip \
  --region us-west-2

# Deploy frontend to S3 and invalidate CloudFront
aws s3 sync ./frontend/ s3://queeno