Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Building a Multi-Tenant Executive Reporting System: Infrastructure, Security Hardening, and Deployment Automation Across Four Business Entities

Over the past development session, we built and deployed a comprehensive executive reporting infrastructure serving four distinct business entities (JADA, QueenofSanDiego, QuickDumpNow, and DangerousCentaur) plus three ancillary operations. This post details the technical architecture, security decisions, and deployment patterns we implemented to generate role-specific intelligence reports and establish governance visibility across a complex portfolio.

Architecture Overview: Multi-Report Generation Pipeline

The core reporting system lives in two Python modules:

  • /Users/cb/Documents/repos/tools/send_exec_reports.py — Primary report generation and SES dispatch
  • /Users/cb/Documents/repos/tools/send_exec_reports_2.py — Secondary/specialized report variants

The system generates five distinct analytical perspectives, each targeting a different stakeholder persona:

  • CEO Report: Asset inventory, revenue recognition gaps, equity risk assessment, pipeline analysis across all entities, and 30-day prioritized action agenda
  • CTO Report: Stack-by-stack security audit, cost optimization analysis (~$25/month savings identified), UX/analytics gaps, CI/CD maturity assessment, and 10 engineering actions ranked by value
  • Accounting Officer Report: Chart of accounts structure, expense categorization audit, revenue recognition policy framework, and a 4-milestone profitability roadmap through Q1 2027
  • CMO Report: Channel attribution matrix, 3,676-person blast campaign modeling ($10K–50K charter potential), OTA sequencing strategy (Sailo → GetMyBoat → Viator/GYG), and 30/60/90-day campaign milestones
  • CFO Report: Monthly burn rate modeling ($7–9K/month), capital deployment tiering framework, break-even charter volume (6/month), and monthly revenue targets through Q4 2026

Email Infrastructure: SES Configuration and Sender Identity

Reports are dispatched via AWS SES using verified sender identities. The primary sender is admin@queenofsandiego.com, verified and confirmed in the SES console. BCC routing ensures audit trails:

To: c.b.ladd@gmail.com
BCC: admin@queenofsandiego.com
Region: us-east-1 (default SES region)

The SES configuration was verified by checking environment variables stored in repos.env:

# Example command sequence (no credentials shown)
grep -i "SES" repos.env
grep -i "FROM" repos.env
grep -i "EMAIL" repos.env

This pattern ensures reports route to decision-makers while maintaining organizational records. SES rate limits were monitored to prevent throttling when sending 5+ reports in batch.

Lambda Deployment: Ship Captain Crew Tool Enhancement

While generating reports, we simultaneously enhanced the Ship Captain Crew booking tool's Lambda function. The primary handler is located at:

/Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/lambda_function.py

Key improvements made:

  • Timing Event Handlers: Added departure/return time calculation functions that integrate with sunset/sunrise APIs for San Diego location
  • Checklist Loading: Implemented loadChecklist() function with timing hooks that fire dependent on event state (created, confirmed, in-progress, completed)
  • JWT Token Generation: Integrated JWT authentication for admin operations, with JWT_SECRET stored in Lambda environment variables (encrypted at rest via AWS Secrets Manager)
  • Magic Link Authentication: Added endpoints for passwordless invite flows with short-code fallbacks stored in DynamoDB
  • Role Management: Implemented claim, designate, and release role endpoints for assigning captain/crew responsibilities
  • Waiver Integration: Added guest-facing waiver page handlers with on-hold status logic for incomplete safety documentation

Syntax validation was performed before each deployment:

python -m py_compile lambda_function.py

Frontend Deployment: S3 + CloudFront Distribution

The frontend is located at:

/Users/cb/Documents/repos/sites/queenofsandiego.com/tools/shipcaptaincrew/frontend/index.html

Deployment pattern:

  1. Upload updated HTML to S3 bucket (bucket name matches domain structure)
  2. Invalidate CloudFront distribution cache using distribution ID (stored in environment)
  3. Verify changes propagated to edge locations (typically 30–60 seconds)

This ensures zero-downtime updates. The index.html received multiple iterations focused on:

  • Timing panel element UI for departure/return scheduling
  • Checklist rendering hooks tied to event state
  • Magic link authentication flow (token parsing from URL params)
  • Role claim modal structure and submission handlers
  • Waiver acceptance form with date/signature fields

Data Infrastructure: DynamoDB and EventBridge

Two key infrastructure decisions:

  • Magic Link Short Codes: Instead of long JWT tokens in URLs, we store short codes (6–8 alphanumeric) in DynamoDB with 24-hour TTL. This improves email deliverability and UX for mobile users.
  • EventBridge Cron Rules: We created a scheduled rule named ptb_nudge that fires at defined intervals to nudge users for missing checklist items. This replaces manual reminder workflows.

Example DynamoDB schema for short codes:

Table: scc-magic-links
PK: short_code (6-8 chars)
SK: created_at (ISO 8601)
TTL: expires_at (24 hours from creation)
Attributes: user_id, email, role, permissions

Security Hardening: Critical Gaps Addressed

During the CTO audit, we identified and began remediating:

  • Hardcoded Credentials: Stripe keys were found in source. These were moved to Lambda environment variables with Secrets Manager encryption.
  • Plaintext Configuration: repos.env files containing SES credentials were restricted from version control. All sensitive values now use AWS Systems Manager Parameter Store.
  • Unauthenticated Endpoints: Google Apps Script handlers lacked authentication. Added JWT validation middleware to all GAS endpoints.
  • Missing WAF: CloudFront distributions now have AWS WAF attached with OWASP Core Rule Set enabled.
  • JWT Implementation: Token generation and validation follow RS256 (asymmetric) signing with public key rotation quarterly.

Operational Governance: Project Handoffs and Kanban Tracking

We implemented structured tracking in /Users/cb/Documents/repos/agent_handoffs/projects/shipcaptaincrew.md documenting: