Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Multi-Stakeholder Executive Reporting System: Building Automated C-Suite Analytics Across Four Business Entities

Over the past development session, we built and deployed an automated executive reporting pipeline that generates role-specific strategic analyses across four distinct business entities (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur) plus three supporting operational units. This post details the technical architecture, infrastructure decisions, and deployment patterns used to deliver five simultaneous executive reports via AWS SES, each tailored to different organizational perspectives.

What Was Built

The core deliverable was a Python-based reporting engine that generates five strategically distinct reports:

  • CEO Report — Asset inventory, shortfall analysis, KPI framework, 30-day action agenda
  • CTO Report — Stack audit, security gaps, cost analysis, UX shortfalls, dev cycle improvements
  • Accounting Report — Revenue recognition, chart of accounts, expense audit, profitability roadmap
  • CMO Report — Channel visibility matrix, blast campaign economics, OTA sequencing strategy
  • CFO Report — Burn rate modeling, capital deployment framework, revenue targets, financial rules

Each report analyzes the same operational data but surfaces different metrics, risks, and recommendations based on stakeholder role and mandate.

Technical Architecture

Report Generation Engine

The primary implementation lives in /Users/cb/Documents/repos/tools/send_exec_reports.py. The script follows a modular pattern:

  • Data Ingestion Layer — Reads from project handoff markdown files and environment configuration to build a unified operational view
  • Report Templates — Five distinct template functions, each generating domain-specific analysis from the same underlying dataset
  • SES Integration — Batch email dispatch using AWS SES via boto3, with verified sender identity and BCC routing
  • Error Handling — Graceful fallbacks for missing data, with explicit logging of which reports succeeded/failed

The script reads environment variables from repos.env for SES configuration:

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

This approach allows the sender identity to remain flexible without hardcoding; the verified SES identity (admin@queenofsandiego.com) is the only sender that can be used from this AWS account without DKIM/SPF setup for other addresses.

Data Model

Rather than querying live databases, the reporting system aggregates data from:

  • Project Handoff Markdown — /Users/cb/Documents/repos/agent_handoffs/projects/ directory contains structured notes on each entity's status, financials, and technical state
  • Lambda Function State — /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py reflects current API capabilities and authentication patterns
  • Infrastructure Config — Route53, CloudFront, S3 bucket names extracted from deployment scripts and verified via AWS CLI queries
  • Financial Snapshots — Burn rate, revenue, and expense data manually curated from recent transaction logs and projections

This design choice reflects the current operational reality: we don't yet have a unified metrics warehouse or analytics database. The handoff files serve as the system of record, which trades some freshness/automation for human-curated accuracy and narrative context.

Infrastructure & Deployment

AWS SES Configuration

All reports are sent via AWS SES in the us-west-2 region. Key constraints:

  • Only verified sender identities can dispatch emails. admin@queenofsandiego.com is verified; any other sender address requires separate DKIM/SPF configuration.
  • SES is in the standard sending limit tier (240 messages/day per verified identity), sufficient for our use case.
  • No custom email headers or DKIM signing is applied; SES adds default headers automatically.
  • BCC routing to admin@queenofsandiego.com ensures audit trail without cluttering the primary recipient's inbox.

The boto3 implementation uses send_email() with explicit Source, Destination, and Message structures rather than bulk send, allowing per-email customization and error isolation.

Version Control & Iteration

Two versions of the script were created during development:

  • send_exec_reports.py — Production version, refined after initial test runs
  • send_exec_reports_2.py — Experimental branch for testing different report structures

The production branch was used for all live SES dispatch. Git history on this tool should be sparse (no credentials committed), with SES keys stored only in the encrypted repos.env file.

Key Technical Decisions

Why Not a Database-Backed Reporting System?

A full BI/analytics stack (Looker, Tableau, or custom Metabase) would be ideal long-term but requires:

  • Normalized data pipelines from each operational system into a data warehouse
  • ETL orchestration (Airflow, dbt, or similar)
  • Role-based access control on dashboards
  • Monthly licensing ($500–$5K+ depending on scale)

At our current scale (four entities, quarterly strategic reviews), hand-curated markdown + Python templating is defensible. The reports are generated on-demand (not on a schedule), require human context that a data warehouse wouldn't capture, and serve a small executive audience. If we scale to 10+ entities or need daily refresh cycles, this decision should be revisited.

Why Markdown-Based Data Ingestion?

The handoff files in /repos/agent_handoffs/projects/ are already the source of truth for project status, decisions, and blockers. Using them as input to the reporting engine creates a single source of truth and forces us to keep operational documentation current. The tradeoff is that reports are only as fresh as the last markdown edit; a live database would be more current but wouldn't enforce documentation discipline.

Why Email Distribution Over Dashboard?

For executives with varied schedules (CEO dealing with fundraising, CFO with board meetings, CTO focused on sprint planning), async email delivery removes the burden of logging into yet another tool. The reports are text-rich and narrative-driven, designed for offline reading and printing. A dashboard would be better for real-time KPI monitoring; these reports are better for monthly strategic review cycles.

Operational Outcomes

Five reports successfully dispatched on [date of last execution]:

  • CEO Report identified 8 critical shortfalls (empty pipeline, no revenue tracking, zero OTA listings, broken QDN funnel) and a prioritized 30-day agenda
  • CTO Report surfaced 6 security gaps (hard