Building a Multi-Domain Executive Reporting System with AWS Lambda, SES, and DynamoDB

```html

Over the past development session, we built and deployed a comprehensive executive reporting pipeline that generates custom C-suite intelligence across four distinct business entities—each with its own strategic lens and KPI framework. This post covers the technical architecture, infrastructure decisions, and deployment patterns we used to create a scalable, maintainable reporting system.

What Was Done

We created an automated executive reporting system that:

Generates five specialized reports (CEO, CTO, Accounting, CMO, CFO) tailored to different stakeholder perspectives
Sends reports via AWS SES to stakeholder inboxes with BCC tracking
Stores report artifacts and metadata in DynamoDB for audit trail and historical analysis
Integrates with existing project handoff documentation and asset inventory
Includes a secondary reporting path for three additional domain-specific reports (3028 51st St Rental, Expert Yacht Delivery, DangerousCentaur Client Portfolio)

The core implementation lives in two Python scripts: /Users/cb/Documents/repos/tools/send_exec_reports.py (primary production version) and /Users/cb/Documents/repos/tools/send_exec_reports_2.py (secondary/experimental variant). Both leverage the existing infrastructure we've already deployed across our four main domains.

Technical Architecture

Data Ingestion and Context Gathering

Each report type requires a different data model. Rather than building separate ETL pipelines, we unified data collection around three primary sources:

Project Handoff Registry: Structured markdown files at /Users/cb/Documents/repos/agent_handoffs/projects/ containing mission statements, tech stacks, deployment status, and financial models for each entity
AWS Infrastructure State: Live queries against Lambda functions, S3 buckets, CloudFront distributions, and DynamoDB tables to surface actual deployment topology and resource utilization
Git Repository Audit: File modification timestamps and commit history to establish development velocity, stability metrics, and technical debt indicators

The CEO report, for example, requires a full asset inventory. Rather than hard-coding asset lists, we:


# Pseudo-code pattern used in send_exec_reports.py
def gather_asset_inventory():
    assets = {}
    for entity in ENTITIES:
        # Pull from project handoff
        handoff = load_handoff(f'agent_handoffs/projects/{entity}.md')
        assets[entity]['dns'] = handoff.get('domains')
        assets[entity]['infra'] = handoff.get('aws_resources')
        assets[entity]['team'] = handoff.get('team_assignments')
    return assets

This approach allows reports to stay current without manual updates.

Report Generation Strategy

Each report follows a structured template but with domain-specific content:

CEO Report: Inventory of all assets, critical shortfalls ranked by business impact, missing KPIs, and 30-day priority agenda
CTO Report: Stack audit (QOS + JADA + QDN + DC), security gap analysis, cost optimization opportunities (~$25/mo savings identified), UX deficits, and dev cycle maturity assessment
Accounting Report: Revenue recognition framework, chart of accounts design, expense audit by category, and Q1 2027 profitability roadmap
CMO Report: Channel visibility matrix, case for email blast deployment (3,676 recipients, modeled at $10K–50K upside), OTA sequencing strategy, and 30/60/90-day milestones
CFO Report: Monthly burn rate model (~$7–9K/mo), capital deployment framework (zero-cost → low-cost → revenue-producing tiers), break-even analysis (6 charters/month), and financial guardrails

Email Delivery and Configuration

Reports are delivered via AWS SES, which we've already integrated into the infrastructure. The key configuration points:

Verified Sender: admin@queenofsandiego.com (pre-verified in SES console for production account)
Recipient: Primary delivery to c.b.ladd@gmail.com with BCC to admin@queenofsandiego.com for audit trail
SES Region: us-west-2 (configured in repos.env under AWS_SES_REGION)
Environment Variable Pattern: Sender address, recipient lists, and SES config sourced from repos.env rather than hard-coded

The sending pattern uses boto3's SES client with HTML email formatting:


import boto3
from botocore.exceptions import ClientError

ses_client = boto3.client('ses', region_name=os.getenv('AWS_SES_REGION'))

try:
    response = ses_client.send_email(
        Source=SENDER_EMAIL,
        Destination={'ToAddresses': [recipient], 'BccAddresses': [audit_email]},
        Message={
            'Subject': {'Data': f'Executive Report: {report_type}'},
            'Body': {'Html': {'Data': html_content}}
        }
    )
    print(f"Email sent. Message ID: {response['MessageId']}")
except ClientError as e:
    print(f"SES error: {e.response['Error']['Message']}")

Infrastructure and Deployment

The reporting system spans multiple AWS services and repositories:

S3 Buckets: Report artifacts archived to s3://qos-reports-archive-prod/exec-reports/YYYY/MM/ for historical analysis and compliance
DynamoDB Table: executive_reports table with partition key report_type and sort key generated_timestamp for rapid lookups and audit logging
Lambda Integration: Reports can be triggered via scheduled Lambda function (EventBridge rule) or invoked directly from CLI for ad-hoc generation
CloudWatch Logs: All SES send operations logged to /aws/lambda/send-exec-reports for monitoring and troubleshooting

The ShipCaptainCrew Lambda function at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py was also updated during this session to handle event creation, role management, and waiver processing—work that informed some of the data models used in the reporting system.

Key Technical Decisions

Why Markdown Handoffs Instead of Database-First Design?

We chose to keep project metadata in markdown handoff files rather than migrating to a dedicated config database. Rationale: version-controlled, human-readable, easy to update during standup, and already integrated into agent handoff workflow. The reports layer sits on top, parsing these files programmatically.

Why Five + Three Report Model?