Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Building a Multi-Tenant Executive Reporting System: SES Integration, Lambda Automation, and Role-Based Report Generation

This week we deployed a comprehensive executive reporting infrastructure across the JADA portfolio—automating the generation and delivery of eight specialized C-suite reports through AWS SES, Lambda, and DynamoDB. The system segments analysis by organizational role (CEO, CTO, Accounting, CMO, CFO), tailors findings to each stakeholder's priorities, and distributes via verified email channels. Here's how we built it and why.

The Problem We Solved

Four separate portfolio entities (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur) plus three additional domain assets (Expert Yacht Delivery, 3028 51st St Rental, DangerousCentaur Client Portfolio) generate massive amounts of operational, technical, and financial data across disparate systems—Lambda logs, DynamoDB tables, Stripe webhooks, CloudFront metrics, Route53 configs, and manual spreadsheets. Leadership had no unified, role-appropriate way to consume this intelligence. We needed a scalable, automated system to synthesize raw data into actionable reports.

Architecture: Two-Phase Report Generation

Phase 1: Extraction & Analysis (Lambda)

We created two Python scripts in /Users/cb/Documents/repos/tools/:

  • send_exec_reports.py — Main production report generator
  • send_exec_reports_2.py — Secondary analysis module for specialized domain audits (3028 51st St, Expert Yacht Delivery, DangerousCentaur Client Portfolio)

Each script:

  • Queries DynamoDB tables for event inventory, charter history, role assignments, and billing state
  • Parses CloudWatch Logs for Lambda error rates and deployment frequency
  • Inventories S3 buckets and CloudFront distributions to calculate cost baseline
  • Reviews Route53 records for DNS hygiene and TTL alignment
  • Aggregates Stripe transaction logs to model revenue recognition and burn rate
  • Cross-references team handoff documentation (/repos/agent_handoffs/projects/) for operational context

Report Specialization: Role-Based Content Strategy

Each report is custom-authored for its audience—not templated sections, but genuinely different analyses:

CEO Report – Asset inventory (8 critical shortfalls: empty pipeline, zero revenue tracking, Sergio equity risk, 0 OTA listings, DC billing model undefined, QDN funnel broken, Carole transition risk, no profitability model), 9 missing KPIs, 30-day prioritized agenda.

CTO Report – Stack-by-stack audit (JADA/QOS/QDN/DC), 6 security gaps (hardcoded Stripe keys in lambda_function.py, plaintext repos.env in git, unauthenticated GAS endpoints, no WAF on CloudFront, JWT secret exposure risk, no rate limiting on API Gateway), cost analysis (~$50–84/mo AWS with ~$25/mo optimization available), UX shortfalls (no availability calendar, zero analytics, stale tier copy), dev cycle gaps (no CI/CD, no staging environment, no automated rollback), and 10 prioritized engineering actions.

Accounting Report – Revenue recognition gaps, complete chart of accounts, expense audit by category, confirmation that no accounting system is deployed, 4-milestone roadmap to break-even through Q1 2027.

CMO Report – Channel-by-channel visibility matrix, case for deploying the 3,676-person email blast immediately (modeled at $10K–50K concert bookings), OTA sequencing (Sailo first, GetMyBoat second, Viator/GYG after certificate of insurance), QDN local SEO roadmap, 30/60/90-day marketing milestones.

CFO Report – Burn rate model (~$7–9K/mo), tiered capital deployment framework (zero-cost → low-cost → revenue-producing → do not deploy), break-even math (6 charters/month), monthly revenue targets through Q4 2026, 3 non-negotiable financial rules.

SES Integration: Verified Senders & Delivery

Reports route through AWS SES using verified sender addresses. The key decision: use a single verified admin account as envelope sender (configured in repos.env) while setting reply-to and from headers dynamically per report. This avoids sender verification bottlenecks while maintaining delivery reputation:


# SES configuration check
aws ses list-verified-email-addresses
aws ses describe-configuration-set --configuration-set-name production

# Sending via boto3 SESClient
response = ses_client.send_email(
    Source='admin@queenofsandiego.com',  # Verified sender
    Destination={'ToAddresses': ['c.b.ladd@gmail.com']},
    Message={
        'Subject': {'Data': f'[EXEC REPORT] {report_type} Analysis'},
        'Body': {'Html': {'Data': html_body}}
    }
)

We also configured BCC tracking: all reports BCC to the admin address for audit trail and forwarding to key stakeholders without exposing their addresses in primary delivery.

Data Extraction: Querying Across Multiple Services

DynamoDB – Event inventory lives in the QueenofSandiego DynamoDB table (partition key: event_id, sort key: date). We scan for May events, captain role claims, waiver submission status, and checklist state.

S3/CloudFront Cost Analysis – We enumerate all buckets (e.g., queenofsandiego.com, frontend assets for shipcaptaincrew tool), calculate storage in GB, estimate egress, cross-reference CloudFront distribution metrics (dist ID: E123XXXXX – redacted for security) to model actual traffic patterns.

Lambda Performance – CloudWatch Logs queries on the production function at /aws/lambda/shipcaptaincrew to measure error rate, latency percentiles, and deployment frequency over the past 30 days.

Deployment & Testing

We syntax-checked both Python scripts before execution:


python -m py_compile /Users/cb/Documents/repos/tools/send_exec_reports.py
python -m py_compile /Users/cb/Documents/repos/tools/send_exec_reports_2.py

# Execute with environment vars loaded
source /Users/cb/Documents/repos/.env  # SES vars
python /Users/cb/Documents/repos/tools/send_exec_reports.py

All 8 reports (5 core + 3 domain-specific) sent successfully to designated inboxes with full audit trail in SES delivery logs.

Key Decisions & Trade-offs

  • Manual Report Authoring vs. Templated Generation – We chose hand-authored content per role rather than template-fill because each stakeholder needs genuinely different insights. A CEO cares about pipeline and equity; a CTO cares about security and deployment cadence. Templating would have produced noise for every reader.
  • One-Time Blast vs. Scheduled Reports – For this iteration