Building a Maintenance Task Notification System for maintenance.queenofsandiego.com

Overview: The Problem

The maintenance.queenofsandiego.com tool was created to surface electrical work and facility maintenance tasks for the Queen of San Diego vessel. However, there was a critical gap: when Travis or other crew members added new maintenance tasks, there was no mechanism to notify stakeholders like Sergio that work needed attention. Tasks could languish undiscovered, creating operational blind spots.

This article details the multi-layer solution implemented across Google Apps Script, Lambda, S3, and CloudFront to establish a notification pipeline that surfaces new tasks intelligently based on criticality while maintaining a clean staging/production separation.

Architecture: Five-Layer Notification Pipeline

The solution implements a event-driven notification system:

• Presentation Layer: staging-index.html with enhanced task input UI
• API Layer: BookingAutomation.gs doPost handler routing maintenance actions
• Persistence Layer: MaintenancePersistence.gs (new) for task storage operations
• Business Logic Layer: MaintenanceCalendar.gs (new) for calendar sync and task processing
• Notification Layer: Lambda function for email dispatch with criticality-aware throttling

Technical Implementation Details

Google Apps Script: New Core Modules

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenancePersistence.gs

This new module handles all persistence operations for maintenance tasks. It manages writes to Google Sheets (the backing data store) and exposes a clean interface for task creation:

function createMaintenanceTask(taskData) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('Maintenance Tasks');
  const timestamp = new Date();
  const row = [
    timestamp,
    taskData.title,
    taskData.description,
    taskData.criticality, // 'critical', 'high', 'medium', 'low'
    taskData.assignee,
    'open', // status
    taskData.dueDate
  ];
  sheet.appendRow(row);
  return { success: true, timestamp: timestamp };
}

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenanceCalendar.gs

This module syncs maintenance tasks to the "Jada Maintenance" Google Calendar (created for jadasailing@gmail.com if not present). It also triggers the Lambda notification function:

function syncTaskToCalendar(taskData) {
  const calendar = CalendarApp.getCalendarById('jada-maintenance-calendar-id');
  const event = calendar.createEvent(
    taskData.title,
    new Date(taskData.dueDate),
    new Date(taskData.dueDate),
    { description: taskData.description }
  );
  
  // Trigger Lambda notification
  invokeMaintenanceNotification(taskData);
  return event.getId();
}

function invokeMaintenanceNotification(taskData) {
  const payload = {
    action: 'notify_maintenance_task',
    task: taskData,
    timestamp: new Date().toISOString()
  };
  
  UrlFetchApp.fetch('https://lambda-endpoint.amazonaws.com/maintenance-notify', {
    method: 'post',
    payload: JSON.stringify(payload),
    headers: { 'Content-Type': 'application/json' }
  });
}

Google Apps Script: Modified BookingAutomation.gs

File: /Users/cb/Documents/repos/sites/queenofsandiego.com/BookingAutomation.gs

The existing doPost handler was extended to route maintenance-specific actions:

function doPost(e) {
  const params = JSON.parse(e.postData.contents);
  
  switch(params.action) {
    case 'log_maintenance':
      return handleMaintenanceTask(params);
    case 'log_booking':
      return handleBooking(params);
    // ... other routes
  }
}

function handleMaintenanceTask(params) {
  try {
    const taskData = {
      title: params.title,
      description: params.description,
      criticality: params.criticality || 'medium',
      assignee: params.assignee,
      dueDate: params.dueDate
    };
    
    MaintenancePersistence.createMaintenanceTask(taskData);
    MaintenanceCalendar.syncTaskToCalendar(taskData);
    
    return ContentService.createTextOutput(
      JSON.stringify({ success: true })
    ).setMimeType(ContentService.MimeType.JSON);
  } catch(error) {
    Logger.log('Maintenance task error: ' + error);
    return ContentService.createTextOutput(
      JSON.stringify({ success: false, error: error.toString() })
    ).setMimeType(ContentService.MimeType.JSON);
  }
}

Frontend: staging-index.html Modifications

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

The staging HTML was modified to include a task input form with criticality selection and real-time form validation:

<div id="task-input-section">
  <h3>Add New Maintenance Task</h3>
  <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="medium" selected>Medium</option>
      <option value="high">High</option>
      <option value="critical">Critical</option>
    </select>
    
    <input type="date" id="taskDue" required />
    <button type="submit">Add Task</button>
  </form>
</div>

<script>
document.getElementById('taskForm').addEventListener('submit', async (e) => {
  e.preventDefault();
  
  const payload = {
    action: 'log_maintenance',
    title: document.getElementById('taskTitle').value,
    description: document.getElementById('taskDesc').value,
    criticality: document.getElementById('taskCriticality').value,
    dueDate: document.getElementById('taskDue').value,
    assignee: 'crew' // placeholder
  };
  
  const response = await fetch(
    'https://script.google.com/macros/d/YOUR-DEPLOYMENT-ID/usable',
    {
      method: 'POST',
      body: JSON.stringify(payload)
    }
  );
  
  const result = await response.json();
  if (result.success) {
    document.getElementById('taskForm').reset();
    showNotification('Task added successfully');
  }