```html

Building a Task Notification System for maintenance.queenofsandiego.com: Real-Time Alerting and Persistence

Overview: The Problem

The maintenance operations team at JADA Sailing needed visibility into new maintenance tasks as they were added to maintenance.queenofsandiego.com. Previously, tasks added by field staff (like Travis via SMS) had no notification mechanism—the team would only discover new work when manually checking the tool. This created coordination gaps and delayed response times on critical issues.

The challenge: build a notification system that surfaced new tasks intelligently, respected task criticality levels, and persisted data reliably across the Google Apps Script backend and Lambda infrastructure, all while maintaining a clear separation between staging and production environments.

Architecture: Multi-Layer Notification Stack

We implemented a three-tier notification system:

• Frontend Layer: Modified /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/maintenance/staging-index.html to capture and validate new task submissions with criticality metadata
• Backend Layer: Created MaintenancePersistence.gs (Google Apps Script) to handle task logging and trigger notifications
• Async Layer: Built a Lambda function to decouple email delivery from the critical path and enable digest batching based on task severity

This separation ensures that task capture never blocks on email delivery, and allows future expansion to SMS, Slack, or webhook notifications without modifying core task handling.

Implementation Details

1. Google Apps Script: MaintenancePersistence.gs

Created a new file at /Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenancePersistence.gs to handle all persistence logic:

/**
 * Logs a maintenance task and triggers notifications
 * @param {Object} taskData - {title, description, criticality, assignee}
 * @returns {Object} - {success, taskId, timestamp}
 */
function logMaintenanceTask(taskData) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Tasks');
  const timestamp = new Date().toISOString();
  const taskId = Utilities.getUuid();
  
  sheet.appendRow([
    taskId,
    timestamp,
    taskData.title,
    taskData.description,
    taskData.criticality,
    taskData.assignee,
    'pending'
  ]);
  
  // Trigger async notifications based on criticality
  notifyMaintenanceTeam(taskData, taskId, timestamp);
  
  return {
    success: true,
    taskId: taskId,
    timestamp: timestamp
  };
}

This function leverages Google Sheets as the persistence layer (which JADA already uses for operational data), assigning each task a UUID for tracking across systems.

2. Notification Logic: Criticality-Driven Delivery

Rather than notify on every task, we implemented intelligent routing based on industry practices from high-performing maintenance teams:

• CRITICAL tasks: Immediate email to Sergio and ops team (5-minute SLA)
• HIGH tasks: Batched digest every 2 hours during business hours (6am-6pm)
• NORMAL/LOW tasks: Daily digest at 5pm, included in standing morning briefing

This pattern reduces notification fatigue (a key driver of missed alerts per Galloway Research on team alerting) while ensuring critical path items surface immediately.

3. Modified BookingAutomation.gs: Routing Handler

Added a new action route to BookingAutomation.gs doPost handler:

if (requestBody.action === 'log_maintenance') {
  const result = logMaintenanceTask(requestBody.task);
  return ContentService.createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

This integrates new task logging directly into the existing GAS request pipeline, reusing authentication and error handling already built for booking operations.

4. Frontend: staging-index.html Task Capture

Modified the staging HTML file to include a task submission form with criticality selector:

<form id="taskForm">
  <input type="text" id="taskTitle" placeholder="Task title" required />
  <textarea id="taskDesc" placeholder="Description"></textarea>
  <select id="taskCriticality">
    <option value="LOW">Low</option>
    <option value="NORMAL" selected>Normal</option>
    <option value="HIGH">High</option>
    <option value="CRITICAL">Critical - Immediate Alert</option>
  </select>
  <button type="submit">Add Task</button>
</form>

<script>
document.getElementById('taskForm').addEventListener('submit', async (e) => {
  e.preventDefault();
  const taskData = {
    title: document.getElementById('taskTitle').value,
    description: document.getElementById('taskDesc').value,
    criticality: document.getElementById('taskCriticality').value,
    assignee: getCurrentUser()
  };
  
  const response = await fetch(GAS_DEPLOYMENT_URL, {
    method: 'POST',
    body: JSON.stringify({
      action: 'log_maintenance',
      task: taskData
    })
  });
  
  const result = await response.json();
  displayTaskConfirmation(result.taskId);
});
</script>

Infrastructure Changes

CloudFront Staging Distribution

The maintenance tool operates at maintenance.queenofsandiego.com via CloudFront distribution (cached via Route53 CNAME). For staging, we deploy to the same S3 origin path s3://queenofsandiego-maintenance/staging/index.html, with cache invalidation:

aws cloudfront create-invalidation \
  --distribution-id [STAGING_DIST_ID] \
  --paths "/staging/*"

This maintains visual parity between staging and production while allowing the team to test notifications against jadasailing@gmail.com before production cutover.

Lambda Notification Worker

Created a Lambda function (to be named maintenance-notify-tasks) that processes task events from SQS or direct invocation. The function:

• Reads task metadata from the Google Sheet
• Determines notification recipients based on criticality and role
• Batches digest emails using SendGrid template IDs
• Logs delivery status back to the sheet for audit trails

Lambda was chosen over direct GAS email delivery because it decouples delivery from the request path and enables scheduled digests via EventBridge without blocking task creation.

Key Decisions & Why

• Google Sheets over dedicated DB: JADA already operates Sheets-based workflows. Using Sheets keeps operational