Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Automating Executive Reporting via AWS SES: Building a Multi-Stakeholder Report Distribution Pipeline

During this development session, we built and deployed an automated executive reporting system that generates five specialized C-suite reports and distributes them via Amazon SES. This post covers the technical architecture, infrastructure decisions, and the engineering patterns we used to safely handle sensitive business communications.

What Was Done

We created a Python-based report generation and distribution system that:

  • Generates five distinct executive reports tailored to different stakeholder perspectives (CEO, CTO, CFO, CMO, Accounting Officer)
  • Uses AWS SES for reliable, audited email delivery with BCC tracking
  • Pulls configuration from environment variables for security isolation
  • Implements proper error handling and delivery verification
  • Maintains separation of concerns between report logic and delivery infrastructure

Technical Architecture

File Structure:

The implementation lives at /Users/cb/Documents/repos/tools/send_exec_reports.py. This single-file approach keeps the tooling lightweight while maintaining clear separation from production application code.

Configuration Management:

Rather than hardcoding credentials, we rely on environment variables from repos.env. The script validates the presence of:

  • SES_REGION — AWS region for SES endpoint (e.g., us-west-2)
  • SES_FROM_ADDRESS — verified sender email (e.g., admin@queenofsandiego.com)
  • SES_BCC_ADDRESS — audit trail recipient
  • AWS credentials via standard boto3 credential chain (IAM role, ~/.aws/credentials, or environment variables)

This approach means the script never stores secrets in version control. Environment validation happens at startup, failing fast if critical vars are missing.

Report Generation Logic:

Each report is generated by a dedicated function, allowing independent maintenance and testing:

  • generate_ceo_report() — Asset inventory, shortfalls, KPIs, 30-day agenda
  • generate_cto_report() — Stack audit, security gaps, cost analysis, dev cycle improvements
  • generate_cfo_report() — Burn rate, capital deployment, break-even analysis, monthly targets
  • generate_cmo_report() — Channel visibility, blast modeling, OTA sequencing, milestone roadmap
  • generate_accounting_report() — Revenue recognition, chart of accounts, expense audit, profitability roadmap

Each function returns a tuple of (subject_line, html_body). HTML formatting ensures the reports render correctly across mail clients and allows for consistent styling.

Infrastructure & AWS SES Integration

SES Configuration:

Amazon SES provides several advantages over direct SMTP:

  • Delivery Monitoring: Bounce and complaint feedback loops integrate with SNS (if configured), giving us automatic tracking of delivery failures
  • Authentication: SES automatically signs messages with DKIM for the verified domain, improving deliverability
  • Rate Limiting: Built-in throttling prevents accidentally overwhelming recipient inboxes
  • Audit Trail: BCC to an ops email provides human verification that reports were sent

Verified Sender Setup:

The sender address (admin@queenofsandiego.com) must be verified in SES before use. This verification happens once at infrastructure setup time, not on every report run. The verification process involves either clicking a confirmation email link or adding a DNS TXT record to prove domain ownership.

Boto3 Client Configuration:

import boto3

ses_client = boto3.client(
    'ses',
    region_name=os.environ['SES_REGION']
)

We use the default credential chain, which checks for credentials in this order: environment variables, IAM role (if running on EC2/ECS), or ~/.aws/credentials. This eliminates the need to store AWS keys in code or configuration files.

Key Decisions & Rationale

Why SES Instead of Third-Party Marketing Platforms?

We chose SES because:

  • Cost: ~$0.10 per 1,000 emails vs. $20+/month per marketing automation tool
  • Control: We own the sending logic and can customize headers, formatting, and scheduling
  • Integration: boto3 integrates directly with Python; no additional SDKs needed
  • Security: Credentials stay within AWS IAM, no third-party API keys to rotate

Why Environment Variables for Configuration?

Environment variables allow the same code to run across development, staging, and production without modification:

  • Development: Points to a test SES sender and development recipient
  • Production: Points to the verified business domain and operations inbox
  • No hardcoded domains, email addresses, or regions in source code

Why BCC to an Ops Email?

The BCC field ensures that even if the primary recipients delete messages, we retain an audit trail. This is critical for compliance and troubleshooting delivery issues. The BCC recipient should be a dedicated operations inbox, not a personal account.

Why HTML Email Bodies?

All five reports are generated as HTML to ensure:

  • Consistent formatting across email clients (Gmail, Outlook, Apple Mail)
  • Proper table rendering for financial data, KPI metrics, and roadmap timelines
  • Mobile responsiveness through CSS media queries
  • Professional appearance befitting C-suite communication

Execution & Testing

The script was executed with the following steps:

# 1. Validate environment variables exist and SES sender is verified
python send_exec_reports.py --validate

# 2. Generate and send all 5 reports
python send_exec_reports.py --send

# 3. Verify delivery by checking the BCC inbox
# (manual step, allows time to catch send failures)

All five reports were successfully delivered to c.b.ladd@gmail.com with BCC to the operations inbox for audit.

What's Next

Future improvements to this system:

  • Scheduling: Move to EventBridge + Lambda to run reports on a weekly or monthly cadence, eliminating manual execution
  • Failure Alerting: Subscribe SES bounce/complaint events to SNS, triggering CloudWatch alarms if delivery fails
  • Data Integration: Connect report generation to live data sources (database queries, API calls) rather than static content
  • Template Engine: Migrate report HTML to Jinja2 templates, separating business logic from markup
  • Encryption: Add PGP signing to reports for additional security assurance

This automation establishes