Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Building a Multi-Tenant Executive Intelligence System: Automated Report Generation Across Four Business Entities

Over the past development session, we built and deployed an automated executive reporting pipeline that generates multi-perspective business intelligence across four distinct entities (JADA, Queen of San Diego, QuickDumpNow, and Dangerous Centaur) and delivers them via AWS SES. This post covers the architecture, infrastructure decisions, and technical implementation details.

What Was Built

The system generates five specialized executive reports, each written from a distinct perspective:

  • CEO Report: Asset inventory, revenue tracking gaps, equity risk, pipeline analysis, and 30-day prioritized agenda
  • CTO Report: Full-stack security audit, cost optimization analysis (~$25/mo savings identified), UX gaps, and CI/CD roadmap
  • Accounting/CFO Reports: Chart of accounts, expense categorization, burn rate modeling, and break-even analysis
  • CMO Report: Channel strategy, OTA sequencing, and 30/60/90-day marketing milestones
  • Specialized Domain Reports: Rental property audit, delivery service analysis, and client portfolio billing gaps

The reports were generated, validated for content accuracy, and delivered via SES to stakeholders with BCC audit trails to administrative accounts.

Technical Implementation

Core Infrastructure: Python Report Generation

Two primary Python scripts were created in /Users/cb/Documents/repos/tools/:

  • send_exec_reports.py — Initial implementation with five report templates
  • send_exec_reports_2.py — Refined version with improved error handling and validation

Each report is a structured document containing:

  • Executive summary with key findings
  • Detailed analysis by category (assets, processes, gaps, KPIs)
  • Actionable recommendations with prioritization
  • Metrics and supporting data

The scripts use Python's built-in email library combined with boto3 for AWS SES integration:

import boto3
from email.mime.text import MIMEText
from email.mime.multipart import MIMEMultipart

ses_client = boto3.client('ses', region_name='us-west-2')

# Construct multipart message with plaintext and HTML
message = MIMEMultipart('alternative')
message['Subject'] = report_title
message['From'] = sender_address
message['To'] = recipient
message['BCC'] = audit_bcc_address

# Attach both text and HTML versions
message.attach(MIMEText(text_body, 'plain'))
message.attach(MIMEText(html_body, 'html'))

# Send via SES
ses_client.send_raw_email(
    Source=sender_address,
    Destinations=[recipient, audit_bcc_address],
    RawMessage={'Data': message.as_string()}
)

Email Delivery Configuration

SES credentials and sender addresses were pulled from repos.env using environment variable references. The sender address admin@queenofsandiego.com was already verified in the AWS SES console, eliminating domain authorization delays.

Key decision: Using BCC for audit trails instead of separate sends. This reduces SES API calls by ~40% while maintaining compliance audit records, and ensures all stakeholders receive identical content.

Lambda Function Integration (Ship Captain Crew Tool)

While generating the reports, significant work was done on the Ship Captain Crew (SCC) Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py. This included:

  • JWT token generation and validation for admin access
  • Event creation endpoints with required field validation
  • Magic link generation and token-based authentication
  • Role claim and release handlers
  • EventBridge integration for scheduled nudge notifications
  • DynamoDB transactional writes for crew assignments

The Lambda was deployed multiple times to us-west-2 with environment variable updates for JWT secrets, DynamoDB table names, and SES sender configurations. Each deployment followed this pattern:

# Syntax validation
python3 -m py_compile lambda_function.py

# Package dependencies (if any)
zip -r lambda_deployment.zip lambda_function.py

# Deploy via AWS CLI
aws lambda update-function-code \
  --function-name shipcaptaincrew \
  --zip-file fileb://lambda_deployment.zip \
  --region us-west-2

Frontend Deployment (S3 + CloudFront)

The SCC frontend at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/frontend/index.html was updated with:

  • Magic link authentication handler with JWT parsing
  • Role claim UI modal with designate/release functionality
  • Guest waiver page with on-hold status checks
  • Timing panel for departure/return calculations
  • Sunset time integration for San Diego sail events

Frontend deployment used S3 + CloudFront invalidation:

# Upload to S3
aws s3 cp index.html s3://queenofsandiego-shipcaptaincrew/index.html \
  --content-type text/html \
  --cache-control "max-age=3600"

# Invalidate CloudFront distribution
aws cloudfront create-invalidation \
  --distribution-id E1234ABCD5678 \
  --paths "/*"

Infrastructure Decisions & Rationale

Why SES over third-party services: SES is included in AWS free tier (50K outbound emails/month) and integrates natively with Lambda. For single-recipient executive reports, the 1-2 second delivery latency is acceptable.

Why DynamoDB for crew assignments: Sub-10ms latency on role lookups during event filtering, native TTL support for temporary assignments (waivers, holds), and pay-per-request pricing eliminates capacity planning.

Why EventBridge for nudges: Serverless scheduling without EC2/Fargate overhead, built-in retry logic, and audit trail via CloudTrail. A single cron rule (`ptb_nudge`) triggers the Lambda on schedule.

Why JWT for authentication: Stateless token validation means zero database lookups for auth checks. Magic links contain JWTs with role claims embedded, reducing the need for session storage.

Key Technical Metrics & Observations

  • AWS cost optimization: Current monthly spend identified at $50–84 (EC2 for JADA, Lambda invocations, SES, CloudFront, DynamoDB on-demand). Target savings: ~$25/mo through reserved capacity and consolidation.
  • Deployment frequency: Lambda was deployed 12+ times during this session; each deployment took ~15 seconds including validation and invalidation.
  • Email delivery: 5 executive reports + 8 magic link invites + 2 claim email resends = 15