Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Multi-Stakeholder Executive Reports via AWS SES: Architecture, Security Hardening, and Lessons Learned

Over the past development session, we built and deployed an automated executive reporting system capable of generating eight specialized reports across four business entities (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur) and three supporting domains (3028 51st St Rental, Expert Yacht Delivery, DangerousCentaur Client Portfolio). This post details the technical architecture, security decisions, and infrastructure patterns that enable stakeholders to receive tailored strategic intelligence without manual intervention.

What We Built

The core deliverable is a Python-based report generation and distribution system that:

  • Dynamically generates 8 context-specific reports (CEO, CTO, Accounting Officer, CMO, CFO, plus three domain-specific audits)
  • Validates AWS SES sender configuration from environment variables
  • Distributes reports via authenticated SES endpoints with BCC tracking
  • Logs all send operations for audit compliance
  • Handles transient failures gracefully with structured error reporting

Technical Architecture

Primary Implementation Files:

  • /Users/cb/Documents/repos/tools/send_exec_reports.py — Main report generation and SES dispatch logic
  • /Users/cb/Documents/repos/tools/send_exec_reports_2.py — Secondary variant for A/B testing recipient lists and template formatting

Both scripts follow a consistent pattern: load environment variables from repos.env, instantiate an AWS SES client, generate report bodies tailored to each stakeholder persona, and dispatch via authenticated SES endpoints.

SES Configuration Discovery:

Before executing any sends, we validated the following environment variables from repos.env:

  • AWS_SES_REGION — Region where SES is verified (typically us-east-1 for legacy accounts)
  • AWS_SES_FROM_ADDRESS — Verified sender identity (admin@queenofsandiego.com)
  • AWS_SES_FROM_NAME — Display name for From header
  • AWS_RECIPIENT_EMAIL — Primary recipient (cc. c.b.ladd@gmail.com)
  • AWS_BCC_EMAIL — Audit trail recipient (admin@queenofsandiego.com)

This separation between from-address and BCC enables sender reputation isolation: production sends originate from a verified organizational domain, while internal tracking uses a secondary inbox.

Report Personas and Content Strategy

CEO Report: Comprehensive asset inventory across all four entities, 8 critical shortfalls (empty pipeline, revenue tracking gaps, Sergio equity risk mitigation, zero OTA listings, DC billing model absence, QDN funnel breakage, Carole transition risk), 9 missing KPIs, and a 30-day execution agenda. This report prioritizes business unit health and P&L impact.

CTO Report: Stack-by-stack technical audit (JADA/QOS/QDN/DC), 6 security gaps (hardcoded Stripe keys in codebase, plaintext repos.env in version control, unauthenticated Google Apps Script endpoints, missing Web Application Firewall), AWS cost analysis (~$50–84/month baseline with $25/month optimization opportunity), UX shortfalls (missing availability calendar, zero analytics instrumentation, stale pricing tier copy), and dev cycle gaps (no CI/CD pipeline, no staging environment, no automated rollback capability). Prioritized 10 engineering actions ranked by security impact and user value.

Accounting Officer Report: Revenue recognition methodology gaps, complete chart of accounts audit, expense categorization by function, critical finding: no accounting system is currently deployed, and a 4-milestone roadmap to profitability through Q1 2027 with specific account structure recommendations.

CMO Report: Channel-by-channel visibility matrix, business case for immediate 3,676-person email blast (modeled at $10K–50K concert booking potential ROI), OTA sequencing strategy (Sailo first, GetMyBoat second, Viator/GYG after certificate of insurance hardening), QDN local SEO roadmap, and 30/60/90-day campaign milestones with attribution tracking requirements.

CFO Report: Burn rate model ($7–9K/month), tiered capital deployment framework (zero-cost initiatives → low-cost infrastructure → revenue-producing features → no-deploy guardrails), break-even at 6 charters/month, monthly revenue targets through Q4 2026, and 3 non-negotiable financial control rules (no hardcoded keys, audit trail on all expenses, COI verification before charter acceptance).

Domain-Specific Reports:

  • 3028 51st St Rental Audit: Property asset valuation, rental revenue visibility, expense tracking by maintenance category, seasonal demand modeling, and occupancy rate targets.
  • Expert Yacht Delivery Operations Report: Current service delivery model, capacity constraints, cost-per-delivery analysis, and integration opportunities with JADA charter workflow.
  • DangerousCentaur Client Portfolio Audit: Billing gap identification, revenue recognition by client tier, contract renewal calendar, and churn risk assessment.

SES Implementation Details

The system leverages AWS SES for email delivery rather than SMTP relay or third-party services. This decision was made for three reasons:

  • Cost: SES charges $0.10 per 1,000 emails after the first 62,000 emails per month; at our volume (8 reports × 2–3 cycles/month), the marginal cost is zero.
  • Compliance: SES integrates with CloudWatch and CloudTrail for audit logging; every send attempt is recorded with bounce/complaint metadata.
  • Sender Reputation: AWS manages DKIM/SPF/DMARC for verified domains automatically, reducing bounce rates and spam folder placement.

Both scripts use the boto3 SES client with the following pattern:


import boto3
import os
from datetime import datetime

ses_client = boto3.client(
    'ses',
    region_name=os.getenv('AWS_SES_REGION', 'us-east-1')
)

response = ses_client.send_email(
    Source=os.getenv('AWS_SES_FROM_ADDRESS'),
    Destination={
        'ToAddresses': [os.getenv('AWS_RECIPIENT_EMAIL')],
        'BccAddresses': [os.getenv('AWS_BCC_EMAIL')]
    },
    Message={
        'Subject': {'Data': subject_line},
        'Body': {'Text': {'Data': report_body}}
    }
)

The BCC pattern ensures that admin@queenofsandiego.com receives a copy of every outbound report without polluting the primary recipient's inbox or creating visible distribution lists in headers.

Key Security and Operations Decisions

Environment Variable Validation: Before any SES calls, we validate that all required variables are present and non-empty. This prevents silent failures where reports fail to send but no alert is triggered.

Hardcoded vs.