Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building a Real-Time Task Notification System for maintenance.queenofsandiego.com

The maintenance tool at maintenance.queenofsandiego.com needed a critical capability: surfacing newly added tasks to the team and notifying stakeholders. Without visibility into task additions, crew members like Travis couldn't easily communicate changes, and operations lead Sergio had no way to stay informed about evolving maintenance requirements. This post details the architecture and implementation of a production-ready notification system built on Google Apps Script, AWS Lambda, and intelligent notification pacing based on task criticality.

The Problem: Task Discovery Without Visibility

The maintenance tool is a web-based interface deployed to CloudFront (distribution ID tied to the maintenance.queenofsandiego.com subdomain), with backend logic in Google Apps Script (GAS) and data persistence in Google Sheets. When Travis added new maintenance tasks, there was no mechanism to:

  • Surface those new tasks in the UI in a discoverable way
  • Notify relevant team members (particularly Sergio)
  • Differentiate notification urgency based on task criticality
  • Implement staging/production separation for testing

The challenge was particularly acute because the maintenance tool sits at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/maintenance/ with its staging deployment at staging-index.html, but there was no clear mechanism to test notifications before going live.

Architecture Overview: Event-Driven Notifications

We implemented a three-tier architecture:

  • Data Layer: Google Sheets as the task source of truth, with two new persistence scripts for managing notification state
  • Processing Layer: Google Apps Script handlers that detect new tasks and route notifications based on criticality
  • Delivery Layer: AWS Lambda for asynchronous email delivery with retry logic and digest compilation
  • Frontend Layer: Enhanced HTML UI to surface new tasks with visual indicators

Technical Implementation Details

New GAS Files and Functions

We created two new Google Apps Script files:

MaintenancePersistence.gs - This file manages the state of tasks and notifications. Key functions:

  • logTaskAddition(taskId, taskName, criticality) - Records when a new task is created, with criticality level (CRITICAL, HIGH, NORMAL, LOW)
  • getNewTasksSinceLast(timestamp) - Returns tasks added since the last notification cycle
  • markTasksNotified(taskIds, notificationType) - Updates state to prevent duplicate notifications
  • getNeedsReviewTasks() - Queries tasks flagged for crew review

MaintenanceCalendar.gs - Manages integration with the "Jada Maintenance" Google Calendar created in the jadasailing@gmail.com account. Functions:

  • createCalendarEvent(taskName, criticality, dueDate) - Syncs critical and high-priority tasks to the calendar
  • updateEventStatus(eventId, status) - Marks events complete when tasks are finished

Modifications to BookingAutomation.gs - Added routing in the doPost handler to catch log_maintenance actions:


if (action === "log_maintenance") {
  const taskData = JSON.parse(e.postData.contents);
  handleMaintenanceTask(taskData);
  return ContentService.createTextOutput(
    JSON.stringify({status: "task_logged"})
  ).setMimeType(ContentService.MimeType.JSON);
}

Notification Routing Logic

The notification system uses criticality-based routing derived from industry best practices in incident management:

  • CRITICAL: Immediate email + SMS + calendar event (reserved for safety issues or total asset unavailability)
  • HIGH: Immediate email + calendar event (used for significant capability degradation)
  • NORMAL: Daily digest email at 6 PM Pacific (default for scheduled maintenance)
  • LOW: Weekly digest on Monday mornings (non-urgent improvements)

This tiered approach prevents notification fatigue while ensuring critical issues reach the right people immediately. The daily digest batching for NORMAL priority tasks follows patterns used by high-performing DevOps teams (similar to incident management systems like PagerDuty).

AWS Lambda Deployment

Email delivery is handled by AWS Lambda, deployed to the same account used for other queenofsandiego.com functions. The Lambda function:

  • Function name: maintenance-notification-handler
  • Runtime: Python 3.11
  • IAM Role ARN: Reused the existing queenofsandiego-lambda-execution-role with added SES permissions
  • Environment variables:
    • GAS_SCRIPT_ID - Points to the Google Apps Script project ID
    • NOTIFICATION_EMAIL_SENDER - Set to ops@queenofsandiego.com (or appropriate ops email alias)
    • STAGING_MODE - When true, sends all notifications to jadasailing@gmail.com for testing

The Lambda function is invoked by scheduled CloudWatch Events rules:

  • maintenance-immediate-check - Runs every 5 minutes for CRITICAL tasks (cron: */5 * * * ? *)
  • maintenance-daily-digest - Runs at 18:00 UTC for NORMAL tasks (cron: 0 18 * * ? *)
  • maintenance-weekly-digest - Runs Monday at 08:00 UTC for LOW tasks (cron: 0 8 ? * MON *)

Frontend Enhancements in staging-index.html

The staging maintenance HTML was modified to surface new tasks prominently:

  • Added an "ops-banner" section at the top that displays unseen tasks with color-coded criticality indicators
  • New task rows include a blue "NEW" badge that persists until the user acknowledges them
  • Added a markTasksAcknowledged(taskIds) function that calls back to GAS to update viewing state
  • Implemented subscribeToNewTasks() using Server-Sent Events to real-time push new tasks as they're added (fallback to polling every 30 seconds)

These changes were deployed to S3 at path /maintenance/staging-index.html and served through the CloudFront distribution, with cache invalidation applied to path /maintenance/staging*.

Staging vs. Production Separation

Since the maintenance tool serves both staging and production from the same CloudFront distribution, we implemented separation through:

  • STAGING_MODE environment variable in Lambda