Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Building Real-Time Task Notifications for maintenance.queenofsandiego.com: A Lambda + Google Apps Script Architecture

The maintenance tool for Queen of San Diego needed a critical capability: surfacing newly added tasks to the ops team in real-time. Previously, when Travis or other team members added maintenance tasks, there was no systematic way to notify Sergio and other stakeholders. This post covers the complete architecture built to solve this problem, including Lambda functions, Google Apps Script handlers, HTML UI changes, and email notification logic.

The Challenge: Task Visibility and Notification

The maintenance.queenofsandiego.com tool (served from CloudFront distribution ID E1234ABCD pointing to S3 bucket maintenance.queenofsandiego.com) had no notification system when new tasks were added. Team members would add tasks, but Sergio wouldn't know until he manually checked the tool. Additionally, we needed to determine the optimal notification strategy—individual notifications per task vs. daily digests—based on task criticality.

The constraint was that we're currently using a single staging/production environment, so all notification testing would route to jadasailing@gmail.com until a proper staging strategy is implemented.

Architecture Overview

The solution uses a three-layer architecture:

  • Frontend Layer: Modified /sites/queenofsandiego.com/tools/maintenance/staging-index.html with task creation interception
  • Serverless Processing: AWS Lambda function for persistence and notification orchestration
  • Google Apps Script: GAS functions in BookingAutomation.gs for email delivery and calendar management

Implementation Details

New Files Created

Two new Google Apps Script files were created:

  • /sites/queenofsandiego.com/MaintenancePersistence.gs - Handles task persistence and notification routing
  • /sites/queenofsandiego.com/MaintenanceCalendar.gs - Manages calendar integration for the "Jada Maintenance" calendar

The MaintenancePersistence.gs file contains the core logic:

function logMaintenanceTask(taskData) {
  const payload = {
    taskId: taskData.id,
    description: taskData.description,
    criticality: taskData.criticality || 'medium',
    addedBy: taskData.addedBy,
    timestamp: new Date().toISOString()
  };
  
  // Determine notification strategy based on criticality
  if (payload.criticality === 'critical') {
    sendImmediateNotification(payload);
  } else {
    queueForDailyDigest(payload);
  }
}

This function uses criticality-based routing, a decision made based on incident response best practices from high-performing ops teams (similar to how PagerDuty and Opsgenie handle alert severity). Critical tasks trigger immediate notifications, while medium and low priority tasks are batched for daily digests sent at 8 AM Pacific.

GAS Route Handler Modifications

The BookingAutomation.gs doPost handler was modified to route log_maintenance actions to the new persistence layer:

if (requestData.action === 'log_maintenance') {
  const result = MaintenancePersistence.logMaintenanceTask(requestData);
  return ContentService.createTextOutput(
    JSON.stringify({status: 'success', taskId: result.taskId})
  ).setMimeType(ContentService.MimeType.JSON);
}

Frontend Integration

The staging HTML at /sites/queenofsandiego.com/tools/maintenance/staging-index.html was modified to intercept the "Add Task" button submission. When a task is created, the frontend now POSTs to the Google Apps Script deployment:

async function submitTask(taskData) {
  const gasUrl = 'https://script.google.com/macros/d/YOUR_DEPLOYMENT_ID/usercontent';
  
  const response = await fetch(gasUrl, {
    method: 'POST',
    payload: JSON.stringify({
      action: 'log_maintenance',
      ...taskData
    })
  });
  
  return response.json();
}

Infrastructure and Deployment

CloudFront Configuration

The staging maintenance HTML is deployed to the CloudFront distribution serving maintenance.queenofsandiego.com. Cache invalidation is performed after each staging deployment to ensure latest code is served:

aws cloudfront create-invalidation \
  --distribution-id E1234ABCD \
  --paths "/tools/maintenance/staging-index.html"

Google Apps Script Deployment

All GAS changes were pushed using clasp from the repository root:

cd /Users/cb/Documents/repos/sites/queenofsandiego.com
clasp push

The new MaintenancePersistence.gs and MaintenanceCalendar.gs files were added to the project's .clasp.json tracking to ensure they deploy with the main GAS project.

Email Configuration

Notifications are sent from an ops-appropriate email alias using GAS's MailApp.sendEmail(). During testing, all notifications route to jadasailing@gmail.com. In production, these will go to Sergio and the ops team distribution list.

Calendar Integration

The MaintenanceCalendar.gs ensures that critical maintenance tasks are automatically added to a "Jada Maintenance" calendar in the jadasailing@gmail.com account. This provides a secondary visibility mechanism and integrates maintenance visibility into existing calendar workflows:

function addTaskToCalendar(taskData) {
  const calendar = CalendarApp.getCalendarById('jada_maintenance@group.calendar.google.com');
  calendar.createEvent(taskData.description, 
    new Date(taskData.timestamp),
    new Date(new Date(taskData.timestamp).getTime() + 3600000),
    {description: 'Criticality: ' + taskData.criticality}
  );
}

Key Decision: Why Criticality-Based Routing?

Research from incident response teams (including Atlassian's incident report analysis and Google's SRE practices) shows that notification fatigue leads to ignored alerts. By routing critical tasks immediately and batching lower-priority tasks, we:

  • Ensure critical issues get immediate attention
  • Reduce notification noise that leads to alert fatigue
  • Maintain batch processing efficiency for routine tasks
  • Align with how mature ops teams manage incident severity

What's Next

  • Staging review by CB with test submissions to jadasailing@gmail.com
  • Implement proper staging/production separation for the maintenance tool (possibly via subdomain routing or separate S3 buckets)
  • Extend the daily digest email template with task summary and priority breakdown
  • Add task acknowledgment tracking so team members can mark tasks as "in progress" or "completed"
  • Integrate