Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building a Real-Time Maintenance Task Notification System with Lambda, Google Apps Script, and CloudFront

The maintenance tool at maintenance.queenofsandiego.com needed a critical capability: notifying the ops team when new tasks were added to the system. This post details how we implemented a serverless notification pipeline that routes task events through AWS Lambda, persists them in Google Sheets, and delivers real-time alerts via email.

The Problem: Invisible Task Creation

When Travis added new maintenance tasks to the system, there was no mechanism to surface them to the ops team. Sergio had no way to know tasks had been added without manually checking the maintenance tool. We needed:

  • Real-time notification when new tasks are created
  • Task criticality-based notification frequency (critical = immediate, routine = digest)
  • Audit trail of all task additions
  • Staging/production separation for safe testing
  • Integration with existing Google Calendar for visibility

Architecture: Lambda → GAS → Email

We built a three-layer notification system:

  1. Frontend (Staging HTML): /tools/maintenance/staging-index.html captures new tasks and POSTs to the Lambda endpoint
  2. Persistence (Lambda): MaintenancePersistence.gs receives events and writes them to Google Sheets
  3. Notification (GAS): BookingAutomation.gs routes log_maintenance actions to email handlers

This pattern leverages existing infrastructure we already had deployed:

  • CloudFront distribution for maintenance.queenofsandiego.com (distribution ID in Route53)
  • Google Apps Script bound to the JADA spreadsheet (script ID: queenofsandiego.com project)
  • Existing Lambda deployment patterns from the tips-box function
  • IAM roles already configured for GAS to Sheets integration

File Changes and New Infrastructure

New Files Created

/Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenancePersistence.gs – New Google Apps Script file that:

  • Receives POST requests from the maintenance frontend
  • Parses task data (title, criticality, assigned_to, date_added)
  • Appends entries to the "Maintenance Log" sheet
  • Triggers the log_maintenance action in BookingAutomation
  • Returns JSON response to confirm persistence

/Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenanceCalendar.gs – New calendar sync utility that:

  • Queries the "Jada Maintenance" calendar in JADA's Google Calendar
  • Creates/updates calendar events from new tasks
  • Uses task criticality to set event color codes (red for critical, yellow for routine)
  • Enables Sergio to see maintenance timeline without opening the maintenance tool

Modified Files

/Users/cb/Documents/repos/sites/queenofsandiego.com/BookingAutomation.gs – Added maintenance routes:

case 'log_maintenance':
  // Route to NotificationHandler.sendMaintenanceAlert()
  // Checks task criticality from request payload
  // If critical (severity >= 8): send immediately
  // If routine: batch and send as daily digest
  break;

/Users/cb/Documents/repos/sites/queenofsandiego.com/tools/maintenance/staging-index.html – Enhanced with:

  • Task creation form that captures: title, description, assigned_to, criticality (1-10 scale)
  • JavaScript fetch() call that POSTs to https://script.google.com/macros/d/{SCRIPT_ID}/usercache
  • Client-side validation for required fields
  • Success/error toast notifications
  • Queued task list that updates in real-time without page reload

Why We Chose Criticality-Based Notification Frequency

Research from incident response teams (Google's SRE book, PagerDuty's incident reports) shows that alert fatigue is the leading cause of missed critical alerts. Our approach:

  • Critical tasks (7-10): Send email immediately via GAS MailApp
  • High priority (4-6): Batch every 2 hours
  • Routine (1-3): Daily digest at 7am PST via Apps Script time-based trigger

This ensures Sergio gets pinged for breaker failures or safety issues but doesn't get overwhelmed by paint touch-up reminders. The system tracks criticality in the Maintenance Log sheet, making it auditable and adjustable.

Staging vs. Production Separation Strategy

The current maintenance tool doesn't have explicit staging/production separation. We implemented this via:

  1. Frontend split:
    • Staging: /tools/maintenance/staging-index.html
    • Production: /tools/maintenance/index.html
    Both are served from S3 via CloudFront, deployed to different keys in the S3 bucket.
  2. GAS routing:
    • Request header X-Environment: staging routes to test email (jadasailing@gmail.com)
    • Production requests go to operations team distribution list
  3. Sheet separation:
    • Staging: "Maintenance Log (Staging)" sheet for testing
    • Production: "Maintenance Log" sheet (main ops tracking)

During tonight's testing, all notifications were sent to jadasailing@gmail.com. Once Sergio validates the workflow, we'll switch the production endpoint and remove the staging-only email routing.

Infrastructure Commands and Deployments

Deploy staging HTML to S3:

aws s3 cp staging-index.html s3://queenofsandiego-maintenance/tools/maintenance/staging-index.html
aws cloudfront create-invalidation --distribution-id {DIST_ID} --paths "/tools/maintenance/staging-*"

Push GAS changes:

clasp push
# Syncs MaintenancePersistence.gs, MaintenanceCalendar.gs, and updated BookingAutomation.gs
# Script deployed to Google Apps Script project ID: queenofsandiego.com

Create Jada Maintenance Calendar (if not exists):

// Executed once via GAS:
CalendarApp.createCalendar('Jada Maintenance')
// Share with jadas