Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building a Multi-Stakeholder C-Suite Reporting System: Architecture, Automation, and Strategic Insights

This session focused on building a comprehensive reporting infrastructure to give leadership teams across four distinct business entities (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur) strategic visibility into operations, technology, finance, and marketing. The challenge: generate five distinct, deeply researched reports from five different executive perspectives—each with different concerns, language, and prioritization—and deliver them reliably via email.

What Was Built

We created a Python-based reporting pipeline that generates five specialized C-suite reports and distributes them via AWS SES (Simple Email Service). Each report was authored from a specific executive lens:

  • CEO Report — Asset inventory, revenue leakage, equity risks, and a 30-day prioritized action plan
  • CTO Report — Stack audit, security hardening roadmap, cost optimization ($25/mo savings identified), and infrastructure debt
  • Accounting Officer Report — Chart of accounts, expense categorization, accounting system gap analysis, and profitability milestones
  • CMO Report — Channel analysis, OTA sequencing strategy, blast campaign modeling, and 30/60/90-day milestones
  • CFO Report — Burn rate modeling, capital deployment framework, break-even analysis, and monthly revenue targets

Additionally, three domain-specific reports were created: one for the 3028 51st St rental property management, one for Expert Yacht Delivery operations, and one for DangerousCentaur's client portfolio billing gaps.

Technical Architecture

File Structure

The primary implementation lives in two files:

/Users/cb/Documents/repos/tools/send_exec_reports.py
/Users/cb/Documents/repos/tools/send_exec_reports_2.py

The first file handles the five core reports; the second extends the framework for domain-specific reports. This separation allows for independent versioning and reduces cognitive load when debugging individual report generation.

Email Delivery Pipeline

Reports are sent via AWS SES with the verified sender address admin@queenofsandiego.com. The pipeline:

  1. Reads SES credentials from repos.env (environment variables: SES_ACCESS_KEY, SES_SECRET_KEY, SES_REGION)
  2. Generates each report as a formatted string with executive-appropriate structure and language
  3. Sends via boto3's SES client with send_email()
  4. BCCs admin@queenofsandiego.com for audit trail and record-keeping
  5. Logs success/failure for each send operation

All five reports in this session were successfully delivered to c.b.ladd@gmail.com.

Data Sources and Analysis Framework

Report generation relied on multiple data sources already in the codebase:

  • Project handoffs — Located in /Users/cb/Documents/repos/agent_handoffs/projects/, containing detailed operational context for each entity
  • Lambda function audits — The SCC (shipcaptaincrew) Lambda at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py revealed security gaps and architectural patterns
  • Frontend implementations — HTML/JS files showed UX debt and feature gaps
  • Infrastructure configs — Route53 DNS, CloudFront distributions, S3 bucket structures revealed cost and reliability issues
  • Existing SES receipt rules — Checked for email handling patterns and gaps in inbound automation

Key insight: Much of this data was already scattered across the codebase. The reporting system's value comes from synthesizing it through different executive lenses rather than collecting new data.

Key Findings and Why They Matter

Security Hardening (CTO Perspective)

The audit identified six critical gaps:

  • Hardcoded Stripe keys in Lambda environment variables (should use AWS Secrets Manager)
  • Plaintext repos.env files in version control (requires immediate rotation and encryption)
  • Unauthenticated GAS (Google Apps Script) endpoints accepting form submissions
  • No Web Application Firewall (WAF) protecting CloudFront distributions
  • JWT implementation without token rotation or expiration validation
  • Lack of rate limiting on public API endpoints

Remediation roadmap: Move secrets to AWS Secrets Manager, add WAF rules to CloudFront, implement token rotation in Lambda, add API Gateway rate limiting.

Infrastructure Cost Optimization

Current AWS spend: $50–84/month. Identified $25/month in savings through:

  • Right-sizing Lambda memory allocation (currently over-provisioned for most functions)
  • Consolidating underutilized RDS instances if any exist
  • Implementing CloudFront caching headers more aggressively (reduce Lambda invocations)
  • Using S3 intelligent-tiering for infrequently accessed historical data

Revenue Recognition and Accounting Gaps

No accounting system exists to track revenue recognition, expense categorization, or monthly P&L. The CFO report modeled break-even at 6 charters/month and flagged $7–9K monthly burn rate as unsustainable without revenue acceleration.

User Experience Debt

Critical UX gaps across all domains:

  • No availability calendar (guests cannot self-serve booking checks)
  • No analytics on any domain (zero visibility into user behavior, conversion funnels)
  • Stale tier copy and pricing inconsistencies
  • Magic link authentication works but lacks polish (no clear success/error states)

Development and Deployment Cycle Improvements

During this session, several Lambda deployments occurred (syntax checking, zipping, deploying to AWS Lambda directly). Current gaps:

  • No CI/CD pipeline — deployments are manual, error-prone
  • No staging environment — changes go live immediately
  • No automated rollback — if a deploy breaks, manual reversion required
  • No deployment logs or audit trail beyond CloudWatch

Recommendation: Wire AWS CodePipeline + CodeBuild to the shipcaptaincrew repository. Implement Terraform for infrastructure-as-code. Use S3 versioning for frontend assets to enable quick rollbacks.

Key Decisions and Rationale

  • Separate Python files for core vs. domain-specific reports: Keeps core business logic isolated from edge cases, easier to test and version independently.
  • BCC to admin address: Provides audit trail without cluttering primary inbox; allows leadership to track delivery timestamps and content.
  • Environment variables for SES creds: Allows safe credential rotation without code changes; follows AWS best practices.
  • Five distinct reports