Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building a Multi-Stakeholder Executive Intelligence System: Lambda, SES, and Real-Time Reporting Infrastructure

Over the past development session, we built and deployed a comprehensive executive reporting pipeline that generates stakeholder-specific intelligence across four business entities (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur) and three ancillary revenue streams. This post details the technical architecture, infrastructure decisions, and deployment patterns that made this possible.

The Problem Statement

Leadership needed simultaneous, specialized visibility into:

  • Asset inventory, operational shortfalls, and KPI gaps (CEO perspective)
  • Security hardening, cost optimization, and UX/infrastructure debt (CTO perspective)
  • Revenue tracking, accounting controls, and cash flow modeling (CFO/Accounting perspective)
  • Channel strategy, marketing sequencing, and demand generation (CMO perspective)
  • Billing models, client portfolio health, and ancillary revenue (3028 51st St Rental, Expert Yacht Delivery, DC Client Portfolio audits)

Generating eight independent reports manually was not scalable. We needed an automated, auditable system that could synthesize data from multiple sources, apply role-specific lenses, and deliver outputs consistently.

Architecture Overview

The solution uses a three-layer model:

  • Data Ingestion Layer: Python scripts that enumerate project handoffs, Lambda configurations, and AWS resource state
  • Synthesis Layer: LLM-powered analysis that generates stakeholder-specific narratives with actionable recommendations
  • Delivery Layer: AWS SES (Simple Email Service) for verified, auditable email distribution

Implementation: The Core Script

The primary execution script lives at:

/Users/cb/Documents/repos/tools/send_exec_reports.py

This script performs the following operations:

  • Environment Loading: Reads repos.env to retrieve SES sender credentials and recipient lists. Environment variables validated include SES_FROM_ADDRESS, SES_REGION, and recipient email lists.
  • Data Aggregation: Scans the project handoff directory at /Users/cb/Documents/repos/agent_handoffs/projects/ to extract context from all active initiatives, particularly shipcaptaincrew.md, which contains the most recent operational and technical state.
  • Report Generation: For each stakeholder persona (CEO, CTO, CFO, CMO, Accounting Officer), the script constructs a detailed prompt that includes all aggregated data and role-specific analysis directives.
  • SES Dispatch: Uses boto3 (AWS SDK for Python) to invoke ses.send_email() with HTML-formatted report bodies, BCC to admin inbox for audit trails.

Example invocation pattern (simplified):

import boto3

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

response = ses.send_email(
    Source='admin@queenofsandiego.com',
    Destination={
        'ToAddresses': ['c.b.ladd@gmail.com'],
        'BccAddresses': ['admin@queenofsandiego.com']
    },
    Message={
        'Subject': {'Data': 'Executive Report: CEO Operational Review'},
        'Body': {'Html': {'Data': html_body}}
    }
)

SES Configuration and Verification

All sender addresses must be verified in AWS SES. In this case, admin@queenofsandiego.com is the verified sender. The script validates:

  • Sender address matches a verified identity in the SES account
  • AWS region is correctly set (us-west-2 for this organization)
  • Recipient addresses are valid and, during production, whitelisted if in sandbox mode

We hardcoded the from-address rather than environment-loading it, as it serves as an integrity check—any unauthorized modifications to sender identity would require code review, not just env var changes.

Report Content and Specialization

Each report was generated with role-specific prompting:

  • CEO Report: 8 critical shortfalls (empty pipeline, no revenue tracking, zero OTA listings, etc.), 9 missing KPIs, 30-day prioritized action plan. Focus: profitability, risk, asset utilization.
  • CTO Report: Stack audit across all 4 entities, 6 security gaps (hardcoded Stripe keys, plaintext repos.env, unauthenticated GAS endpoints, no WAF), cost analysis (~$50–84/mo AWS, $25/mo optimization potential), UX shortfalls, dev cycle gaps, 10 engineering actions. Focus: scalability, security, cost efficiency.
  • CFO Report: Revenue recognition model, complete chart of accounts, expense audit, profitability roadmap (break-even at 6 charters/month), burn rate analysis (~$7–9K/mo). Focus: cash flow, unit economics, capital deployment.
  • CMO Report: Channel-by-channel visibility, case for 3,676-person blast (modeled at $10K–50K concert bookings), OTA sequencing (Sailo → GetMyBoat → Viator/GYG), QDN local SEO, 30/60/90-day milestones. Focus: demand generation, conversion, customer acquisition cost.
  • Accounting Officer Report: Inflow/outflow tracking structure, journal entry templates, general ledger design, audit readiness checklist, Q1 2027 roadmap. Focus: controls, compliance, reconciliation.

Three additional specialized audits were generated:

  • 3028 51st St Rental Audit: Revenue model validation, occupancy KPIs, maintenance backlog.
  • Expert Yacht Delivery Audit: Service delivery SLA tracking, margin analysis, fleet utilization.
  • DangerousCentaur Client Portfolio Audit: Billing gap analysis, contract compliance, churn risk assessment.

Infrastructure and Deployment Decisions

Why SES instead of SendGrid or Mailgun? SES is tightly integrated with AWS IAM, requires no external API keys in environment variables (credentials come from the Lambda/EC2 instance role), and costs ~$0.10 per 1,000 emails. For low-volume reporting, this is negligible. SES also provides bounce/complaint tracking via SNS, enabling audit and compliance workflows.

Why Python over Lambda directly? These reports require complex data aggregation and synthesis. While we have Lambda functions running in the same AWS region (e.g., lambda_function.py for Ship Captain Crew), orchestrating multi-source data gathering, LLM calls, and SES dispatch is better served by a local Python script with full filesystem access to project handoffs. This also enables testing without deploying to AWS.

BCC Pattern for Audit Trails: All reports are BCC'd to admin@queenofsandiego.com`, ensuring leadership visibility and creating an immutable record in the SES send log and organizational email. This is critical for compliance and historical reference.

Related Lambda