Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building a Multi-Tenant Executive Reporting Pipeline: Lambda, SES, and Cross-Domain Analytics Architecture

Over the past development session, we built and deployed a sophisticated executive reporting infrastructure capable of generating role-specific business intelligence across four distinct maritime and logistics entities. This post details the technical architecture, infrastructure decisions, and deployment patterns we used to create an automated, scalable reporting pipeline.

What We Built

We created an end-to-end reporting system that generates five concurrent executive reports, each tailored to a specific C-suite persona (CEO, CTO/VP Engineering, CFO, CMO, and Chief Accounting Officer). The system is triggered via command-line Python scripts, processes multi-domain data, and delivers reports via AWS SES to designated stakeholders.

The architecture spans three core domains:

  • Report Generation Layer: Python scripts in /Users/cb/Documents/repos/tools/ that orchestrate data collection and template rendering
  • Email Delivery: AWS SES with verified sender identity (admin@queenofsandiego.com) for reliable transactional delivery
  • Data Sources: Project handoff metadata, financial models, tech stack inventories, and operational dashboards stored in markdown and configuration files across multiple repositories

Technical Architecture & Implementation

Report Generation Pipeline

We created two primary scripts in the tools directory:

  • send_exec_reports.py — Initial implementation with a baseline 5-report workflow
  • send_exec_reports_2.py — Refined version with improved templating and error handling

The workflow is straightforward:

1. Load environment variables from repos.env (SES credentials, sender address)
2. Read project handoff files from /repos/agent_handoffs/projects/
3. Generate role-specific report content using Python string templates
4. Compose HTML email bodies with contextual data
5. Send via boto3 SES client with BCC tracking
6. Log delivery confirmation with timestamps

Key environment variables required:

  • AWS_REGION — SES service region (us-west-2)
  • SES_FROM_ADDRESS — Verified sender identity
  • ADMIN_BCC_ADDRESS — Audit trail recipient
  • REPORT_RECIPIENTS — Comma-separated email list (stored in repos.env, not hardcoded)

SES Configuration & Verification

AWS SES in sandbox mode requires all recipients to be verified identities. We verified admin@queenofsandiego.com as the sender and validated the recipient list before deployment. The production workflow:

import boto3

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

response = ses_client.send_email(
    Source='admin@queenofsandiego.com',
    Destination={'ToAddresses': ['recipient@example.com']},
    Message={
        'Subject': {'Data': 'Executive Report: Q4 Strategic Assessment'},
        'Body': {'Html': html_body}
    }
)

We validated SES variable names via the repos.env configuration file to ensure consistency across deployment environments.

Multi-Entity Data Aggregation

The reporting pipeline ingests data from four primary entities plus three supplementary domains:

  • JADA — Jazz/event booking platform (Route53, CloudFront distribution, S3 frontend)
  • QueenofSanDiego (QOS) — Charter yacht operations and crew management
  • QuickDumpNow (QDN) — Logistics/waste services vertical
  • DangerousCentaur (DC) — Client services and portfolio management
  • 3028 51st St Rental — Real estate asset
  • Expert Yacht Delivery — Specialized transportation services
  • DangerousCentaur Client Portfolio — Billing and receivables audit

Each entity's operational metadata is stored in /repos/agent_handoffs/projects/{entity}.md, which the reporting scripts parse to extract KPIs, financial baselines, and technical debt inventory.

Infrastructure & Deployment Decisions

Lambda Function Hardening

While building the reporting infrastructure, we simultaneously refined the Ship-Captain-Crew Lambda function in /sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py. Key hardening measures:

  • JWT Authentication: All event-creation endpoints now require a valid JWT token signed with a server-side secret (stored in Lambda environment variables, not in code)
  • Syntax Validation: Pre-deployment validation via Python ast module to catch runtime errors before CloudFormation deployment
  • Environment Variable Management: Migrated hardcoded credentials to Lambda environment variables, with safe-only logging to avoid exposing secrets
  • Event Schema Validation: Strict validation of incoming event payloads (event_id, user_id, checklist_items, timing data)

Deployment process:

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

# Zip function and dependencies
zip -r function.zip lambda_function.py

# Deploy to AWS Lambda
aws lambda update-function-code \
  --function-name shipcaptaincrew \
  --zip-file fileb://function.zip \
  --region us-west-2

Frontend Deployment Pipeline

The Ship-Captain-Crew frontend (React-based, HTML + JavaScript in /frontend/index.html) was deployed with the following flow:

# Upload to S3
aws s3 cp frontend/index.html s3://queenofsandiego-tools/shipcaptaincrew/index.html --content-type text/html

# Invalidate CloudFront cache (distribution ID: E2ABC123XYZ)
aws cloudfront create-invalidation \
  --distribution-id E2ABC123XYZ \
  --paths "/*"

This two-step pattern ensures cache invalidation happens immediately after object upload, preventing stale assets from being served to users.

EventBridge Scheduling

For automated nudge campaigns and reminder workflows, we configured an EventBridge cron rule named ptb_nudge. This rule triggers Lambda functions on a schedule to send checklist reminders to event participants with JWT-authenticated payload signing.

Key Architectural Decisions & Rationale

Why Python for Report Generation?

Python was chosen for the reporting pipeline because:

  • Native boto3 integration with AWS SES requires minimal boilerplate
  • File I/O for reading project handoff markdown is straightforward and portable
  • Template rendering via f-strings and format() keeps dependencies minimal
  • Easy to add more reports without refactoring core infrastructure

SES Over SNS/Lambda Directly

We selected SES for email delivery because: