Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building a Multi-Stakeholder C-Suite Reporting System: Architecture, Automation, and Infrastructure for Executive Decision-Making

Over the past development session, we built and deployed a comprehensive automated reporting system designed to surface critical business intelligence to five distinct executive personas across a multi-entity portfolio (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur, Expert Yacht Delivery, and 3028 51st St Rental). This post details the technical architecture, infrastructure decisions, and implementation patterns that enable weekly executive insights with minimal operational overhead.

What Was Done

We created an automated reporting pipeline that generates eight specialized executive reports—each written from a specific stakeholder's perspective and constraints—and distributes them via Amazon SES to a verified sender address with BCC tracking. The system orchestrates complex Python logic across local development and AWS Lambda environments, manages multi-domain data aggregation, and maintains strict separation between development and production secrets.

Key deliverables:

  • Five persona-driven reports: CEO (asset inventory, shortfalls, KPIs), CTO (stack audit, security gaps, cost analysis), Accounting Officer (revenue recognition, chart of accounts), CMO (channel visibility, OTA sequencing), and CFO (burn rate, capital deployment framework)
  • Three specialized domain reports: 3028 51st St Rental operations, Expert Yacht Delivery P&L, DangerousCentaur client portfolio audit
  • Automated distribution pipeline with verified SES sender, BCC tracking, and error handling
  • Executive dashboard integration for task creation and progress tracking

Technical Architecture

Core File Structure

The implementation spans two primary Python modules in /Users/cb/Documents/repos/tools/:

  • send_exec_reports.py — Main orchestration logic for all eight reports, SES integration, and error handling
  • send_exec_reports_2.py — Secondary iteration for testing and alternative dispatch patterns

Both files share a common pattern: they import SES credentials and sender configuration from repos.env, iterate through a list of report definitions (each with a name, perspective/persona, and data aggregation logic), render Markdown-to-plain-text reports, and invoke boto3.client('ses') to send via the admin@queenofsandiego.com verified address.

Data Aggregation Strategy

Rather than querying live databases (which would introduce latency and access complexity), the reporting system aggregates data from:

  • Project handoff documentation (/Users/cb/Documents/repos/agent_handoffs/projects/) — stored as Markdown files with structured metadata on projects, status, and financials
  • Lambda environment variables — deployment-time injected configuration for each domain
  • DynamoDB snapshots — event count, user counts, and checklist completion rates pulled via Lambda's native DynamoDB access
  • CloudFront and S3 logs — available but not directly queried; instead, we track deployment frequency and S3 object counts as proxy metrics for content volume

This approach trades real-time precision for operational simplicity: the reports are regenerated weekly, and data freshness is acceptable for executive-level decision-making.

SES Integration and Email Dispatch

The core email dispatch pattern:

import boto3
import os

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

# Load from repos.env
from_address = os.getenv('SES_FROM_ADDRESS')  # admin@queenofsandiego.com
to_address = os.getenv('EXEC_REPORT_TO')      # c.b.ladd@gmail.com
bcc_address = os.getenv('EXEC_REPORT_BCC')    # admin@queenofsandiego.com

# For each report persona:
response = ses_client.send_email(
    Source=from_address,
    Destination={
        'ToAddresses': [to_address],
        'BccAddresses': [bcc_address]
    },
    Message={
        'Subject': {'Data': f'Executive Report: {persona_name}'},
        'Body': {'Text': {'Data': rendered_report_text}}
    }
)

Critical decisions made:

  • Plain text, not HTML — reduces rendering complexity, improves deliverability, and makes reports searchable in Gmail archives
  • BCC tracking — ensures admin@ receives a copy for audit and proof-of-delivery without adding extra send operations
  • Verified sender only — admin@queenofsandiego.com is the only SES-verified address in the account, so all sends route through it regardless of the persona writing the report
  • repos.env in local development — keeps secrets out of version control; the Lambda function uses AWS Secrets Manager for production secrets

Infrastructure and Deployment

SES Configuration

The reporting system assumes a working SES setup:

  • Region: us-west-2 (matches the primary AWS region for QueenofSanDiego infrastructure)
  • Verified sender: admin@queenofsandiego.com (domain verified via Route53 CNAME and DKIM)
  • Sending limits: Default sandbox limits (~1 email per second) are sufficient for 8 reports at ~0.5 req/sec
  • Receipt rules: Configured but not used in this pipeline (reports are outbound only)

Dashboard Task Creation

After sending reports, the system updates the progress dashboard by:

  1. Parsing the CEO report to extract top 3 shortfalls
  2. Invoking a dashboard update function (via HTTP or Lambda invoke) to create kanban cards for each priority
  3. Tagging cards with persona (CEO, CTO, CFO) and urgency (critical, high, medium)

The dashboard infrastructure lives in a separate system (not detailed here) but receives JSON payloads with card metadata and auto-generates tracking tasks.

Key Technical Decisions

Why We Didn't Use Lambda Layers or Step Functions

The reporting pipeline is intentionally simple:

  • Single Python script — no need for containerization, Lambda layers, or orchestration frameworks; the entire logic fits in ~500 lines of Python
  • Local execution — reports are generated on-demand by a developer (or a scheduled local cron job), not triggered by API Gateway or EventBridge, so Lambda isn't the right abstraction
  • No state machine — the pipeline is linear: aggregate data → render reports → send emails → log results. No branching, retries, or conditional dispatch logic that would justify Step Functions

Why Plain Text Over HTML Email

HTML emails introduce rendering inconsistencies across Outlook, Gmail, Apple Mail, and mobile clients. For executive reports focused on data, plain text is:

  • Guaranteed readable on any client