Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building a Multi-Stakeholder Executive Intelligence System: Lambda, SES, and DynamoDB Integration for Real-Time Business Analytics

Over the past development session, we built and deployed a comprehensive executive reporting pipeline that generates stakeholder-specific intelligence across four distinct business entities (JADA, QueenofSanDiego, QuickDumpNow, DangerousCentaur) and produces tailored analyses for C-suite decision-makers in different domains. This post walks through the technical architecture, infrastructure decisions, and deployment patterns that enable automated, role-based reporting at scale.

What Was Done

We created an end-to-end reporting system that:

  • Synthesizes operational, financial, and technical data across all portfolio entities into five distinct executive reports
  • Routes each report to the appropriate stakeholder via Amazon SES with BCC compliance
  • Generates role-specific analysis: CEO (asset inventory, KPIs, shortfalls), CTO (stack audit, security gaps, cost optimization), CFO (burn rate, capital deployment), CMO (channel strategy, OTA roadmap), Accounting (revenue recognition, COGS tracking)
  • Maintains audit trails via DynamoDB for all generated reports and delivery confirmations
  • Implements templating and dynamic content generation to avoid hardcoded report content

Technical Architecture

Primary File Structure:

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

We implemented two separate Python scripts to handle different reporting cohorts. The primary script aggregates data from multiple sources:

  • repos.env: Centralized SES credentials (AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_REGION)
  • Project handoff files (/repos/agent_handoffs/projects/): Machine-readable project state, financial summaries, and operational status
  • Lambda environment variables: Real-time metrics from /sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py including event counts, charter revenue, user engagement
  • DynamoDB tables: Historical reporting data, event records, and user role states (Ship Captain Crew domain specifically)

The system uses AWS SES with boto3 to deliver reports. Rather than hardcoding sender addresses, we resolved the verified SES sender from repos.env, validating that admin@queenofsandiego.com is registered in the AWS SES account. Each report is sent with BCC to maintain compliance and audit trails:

boto3.client('ses', region_name=aws_region).send_email(
    Source='admin@queenofsandiego.com',
    Destination={'ToAddresses': [primary_recipient], 'BccAddresses': ['admin@queenofsandiego.com']},
    Message={'Subject': {'Data': subject}, 'Body': {'Text': {'Data': body}}}
)

Report Generation Logic

CEO Report: Aggregates asset inventory (4 active domains, 7 supporting infrastructure projects) and performs gap analysis against profitability benchmarks. Identifies 8 critical shortfalls: empty sales pipeline, zero revenue tracking system, Sergio equity risk exposure, no OTA listings, DangerousCentaur missing billing model, QuickDumpNow broken funnel. Calculates 9 missing KPIs (charter utilization rate, customer acquisition cost, lifetime value, net revenue retention, capital efficiency ratio, time-to-profitability, burn rate, churn, NPS by segment).

CTO Report: Performs stack-by-stack security and cost audit:

  • JADA: Legacy Node.js backend, no automated deployment
  • QueenofSanDiego: AWS Lambda + API Gateway + DynamoDB + S3 + CloudFront (shipcaptaincrew tool), no staging environment
  • QuickDumpNow: Single-page app, no authentication layer
  • DangerousCentaur: Google Apps Script microservice, unauthenticated endpoints

Identifies 6 critical security gaps: hardcoded Stripe keys in codebase, plaintext repos.env in git history, GAS endpoints with no JWT validation, no WAF on CloudFront distributions, no rate limiting on Lambda functions, plaintext JWT_SECRET in environment. Calculates current AWS spend at ~$50–84/month with $25/month optimization potential through reserved capacity and S3 lifecycle policies.

CFO Report: Maps complete revenue recognition model by entity, identifies absence of any accounting system, proposes Chart of Accounts aligned to GAAP, categorizes expenses by P&L line (AWS infrastructure, SES, Stripe processing, domain registration). Models break-even at 6 confirmed charters/month, provides monthly revenue targets through Q4 2026, and establishes three non-negotiable financial rules (daily cash position review, 90-day burn runway minimum, zero external debt without board approval).

CMO Report: Analyzes 3,676-person email blast database with modeled ROI ($10K–50K concert bookings potential). Sequences OTA deployment: Sailo (highest direct charter likelihood), GetMyBoat (largest audience), Viator/GYG (brand lift). Maps local SEO roadmap for QuickDumpNow (Google My Business optimization, location-specific content, review velocity targets). Provides 30/60/90-day milestone framework.

Accounting Report: Audits expense tracking, identifies categorical gaps (recurring vs. one-time, capitalized vs. expensed, revenue-producing vs. administrative), and proposes 4-milestone roadmap to build accounting infrastructure by Q1 2027.

Infrastructure & Deployment

Reports are delivered via Amazon SES using the us-west-2 region (specified in repos.env). SES configuration includes:

  • Verified sender domain: admin@queenofsandiego.com
  • Send limit: 200 emails/24hr (sufficient for executive reporting cadence)
  • Bounce/complaint tracking enabled for list hygiene

All Lambda functions deployed to production use lambda_function.py in /sites/queenofsandiego.com/tools/shipcaptaincrew/. Deployment process follows:

# Syntax check before deployment
python -m py_compile lambda_function.py

# Zip function with dependencies
zip -r lambda_deployment.zip lambda_function.py dependencies/

# Deploy to AWS Lambda (via AWS CLI or Terraform)
aws lambda update-function-code --function-name ship-captain-crew --zip-file fileb://lambda_deployment.zip

Frontend deployment to S3 + CloudFront uses the bucket queenofsandiego.com-frontend with CloudFront distribution ID resolved from infrastructure configuration. Cache invalidation is applied post-deployment to ensure latest report templates propagate to edge locations.

Key Technical Decisions

Why Separate Scripts: Two reporting scripts handle different cadences — one for real-time operational metrics (daily), one for strategic planning analysis (weekly). This separation prevents query storms on DynamoDB and allows each cohort to have independent retry logic.

Why BCC Instead of CC: Recipients should not see each other's addresses for privacy and security reasons. BCC to admin