Building a Multi-Tenant Executive Reporting Pipeline with AWS Lambda, SES, and Dynamic Content Generation

```html

Over the past development session, we built and deployed a comprehensive executive reporting system that generates role-based strategic analysis across four business entities (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur) and delivers them via AWS SES. This post details the architecture, deployment strategy, and lessons learned.

What Was Done

We created a reporting infrastructure that:

Generated five specialized C-suite reports (CEO, CTO, CFO, CMO, Accounting Officer) tailored to each role's priorities
Implemented dynamic content generation based on role-specific data models
Deployed via AWS Lambda with SES email delivery
Integrated with existing project management dashboards for downstream task creation
Established a repeatable pipeline for future reporting cycles

Technical Architecture

Report Generation Pipeline

The core reporting system lives in two Python files:

/Users/cb/Documents/repos/tools/send_exec_reports.py — Primary report generation and SES delivery
/Users/cb/Documents/repos/tools/send_exec_reports_2.py — Secondary/iterative report variants for A/B testing or domain-specific customization

Each script instantiates role-specific report generators that read from a unified data model built from:

Asset inventory across all four entities (hosted sites, Lambda functions, S3 buckets, DynamoDB tables)
Project handoff documentation (/Users/cb/Documents/repos/agent_handoffs/projects/)
Infrastructure state queries (CloudFront distributions, Route53 records, security group rules)
Financial and operational metrics from spreadsheets and dashboard exports

SES Configuration

Email delivery relies on AWS SES with configuration stored in repos.env:

SES_REGION=us-west-2
SES_FROM_ADDRESS=admin@queenofsandiego.com
SES_TO_ADDRESS=c.b.ladd@gmail.com
SES_BCC_ADDRESS=admin@queenofsandiego.com

The SES_FROM_ADDRESS must be a verified sender identity in SES. We verified admin@queenofsandiego.com and stored credentials securely in AWS Secrets Manager (not in repos.env). The Python scripts use boto3.client('ses') to invoke send_email() with:

Source: verified sender
Destination: To/CC/BCC recipients
Message: Subject + HTML body

Report Specialization by Role

CEO Report

Covers full asset inventory, 8 critical shortfalls (empty pipeline, no revenue tracking, equity risk, zero OTA listings, billing model gaps), 9 missing KPIs, and a prioritized 30-day agenda. Data sources: balance sheets, customer acquisition funnel metrics, pipeline state from handoffs.

CTO Report

Stack-by-stack infrastructure audit for each domain, 6 security gaps (hardcoded keys, plaintext secrets, unauthenticated endpoints, no WAF), cost analysis (~$50–84/mo AWS), UX shortfalls, CI/CD gaps, and 10 prioritized engineering actions. Queries CloudFront distributions, Lambda function logs, S3 bucket policies, security groups.

CFO Report

Burn rate modeling (~$7–9K/mo), capital deployment framework, break-even analysis (6 charters/month), monthly revenue targets through Q4 2026, and enforcement of 3 financial rules. Cross-references SES costs, Lambda execution metrics, data transfer fees, and domain registrations.

CMO Report

Channel-by-channel visibility matrix, immediate blast deployment case study (3,676-person list, $10K–50K concert booking potential), OTA sequencing (Sailo → GetMyBoat → Viator), QDN local SEO roadmap, and 30/60/90-day milestones.

Accounting Officer Report

Revenue recognition policies, complete chart of accounts, expense audit by category, current accounting system gap analysis, and 4-milestone profitability roadmap through Q1 2027.

Infrastructure and Deployment

Lambda Integration

The primary reporting Lambda lives in:

/Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py

This function (deployed to AWS Lambda) can be triggered via:

EventBridge cron rules (scheduled reports)
Direct API Gateway invocation
SQS event sources for async reporting

It returns a JSON response indicating which reports were sent and any failures. Environment variables (stored in Lambda configuration, never in code) include:

SES_REGION
SES_FROM_ADDRESS (verified sender)
Role-specific recipient lists (CEO email, CTO email, etc.)

Frontend Dashboard Integration

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

Display report generation status
Show timestamps of last-sent reports
Provide manual triggers for immediate report generation
Integrate with a task creation endpoint to push follow-up actions to the progress dashboard

The HTML form submits to the Lambda function's API Gateway endpoint, which authenticates via JWT token validation.

Key Technical Decisions

Why Role-Based Report Specialization?

Generic reports waste executive time. Each role has distinct data priorities: a CEO cares about churn and CAC; a CTO cares about latency and security; a CFO cares about burn rate and margins. By generating 5 specialized reports from a single unified data model, we maximize signal-to-noise and ensure each stakeholder receives actionable insights.

Why SES Over SendGrid or Mailgun?

SES pricing scales to near-zero for low volumes and integrates natively with boto3. No external API calls, no third-party dependency, no additional credential rotation. For internal reporting (5 reports per cycle), SES cost is <$0.01/month.

Why Lambda Over Scheduled EC2?

Lambda is serverless (no idle cost), integrates directly with SES, and can scale instantly if we expand to hundreds of recipients or multiple cycles per day. EventBridge scheduling is free for the first 14 million events/month. No infrastructure to patch or monitor.

Why Store Secrets in AWS Secrets Manager, Not repos.env?

repos.env lives in version control; it must never contain credentials. Secrets Manager encrypts at rest and in transit, provides audit logging, and allows key rotation without code changes. The Lambda execution role is granted secretsmanager:GetSecretValue