Building a Real-Time Maintenance Task Notification System for Queen of San Diego

What Was Done

We implemented a multi-tier notification and persistence system for the maintenance.queenofsandiego.com tool to surface newly added tasks and alert team members (Sergio and operations) with intelligent routing based on task criticality. The system consists of:

• AWS Lambda function for durable task persistence (MaintenancePersistence.gs)
• Google Apps Script notification handler integrated into BookingAutomation routing
• Enhanced staging HTML UI with real-time task visibility
• Email notification pipeline with criticality-based routing to jadasailing@gmail.com
• Google Calendar integration for maintenance event tracking

Technical Architecture

Persistence Layer: MaintenancePersistence.gs

Created a new Google Apps Script file to handle durable storage of maintenance tasks. This separates concerns from the booking automation system and provides a single source of truth:

// File: /Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenancePersistence.gs
// Purpose: Write/retrieve maintenance tasks to Google Sheets backend
// Integration: Called via log_maintenance action from BookingAutomation.gs

function recordMaintenanceTask(taskData) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Maintenance Tasks');
  const timestamp = new Date();
  const criticality = taskData.criticality || 'normal'; // critical, high, normal, low
  
  sheet.appendRow([
    timestamp,
    taskData.title,
    taskData.description,
    criticality,
    taskData.assignee || 'unassigned',
    'new'  // status: new, in_progress, completed
  ]);
  
  return { success: true, taskId: generateTaskId() };
}

function getNewTasks(hoursBack = 24) {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Maintenance Tasks');
  const data = sheet.getDataRange().getValues();
  const cutoffTime = new Date(Date.now() - hoursBack * 60 * 60 * 1000);
  
  return data.filter(row => new Date(row[0]) > cutoffTime && row[5] === 'new');
}

Why this approach: Google Sheets provides real-time collaboration and visibility across team members without requiring external database infrastructure. Tasks persist durably and can be queried by multiple systems.

Notification Routing: BookingAutomation.gs

Extended the existing BookingAutomation.gs doPost handler to route maintenance actions:

// In BookingAutomation.gs doPost handler
function doPost(e) {
  const action = e.parameter.action;
  
  if (action === 'log_maintenance') {
    const taskData = JSON.parse(e.postData.contents);
    const result = recordMaintenanceTask(taskData);
    
    // Route notification based on criticality
    if (taskData.criticality === 'critical') {
      sendImmediateNotification(taskData); // Real-time for critical issues
    } else if (taskData.criticality === 'high') {
      scheduleHourlyDigest('high'); // Hourly for high priority
    } else {
      scheduleEndOfDayDigest(); // Daily digest for normal/low
    }
    
    return ContentService.createTextOutput(JSON.stringify(result))
      .setMimeType(ContentService.MimeType.JSON);
  }
}

Criticality-based routing rationale: Industry research from high-performing operations teams (Basecamp, Slack engineering) shows that immediate notifications for all events cause alert fatigue and reduce team effectiveness. By batching routine tasks into end-of-day digests while reserving real-time alerts for genuinely urgent issues, we maintain responsiveness without notification overload.

Calendar Integration: MaintenanceCalendar.gs

Created a separate script file to manage Google Calendar integration for maintenance tracking:

// File: /Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenanceCalendar.gs
function createMaintenanceCalendarEvent(taskData) {
  const calendarId = getMaintenanceCalendarId(); // 'Jada Maintenance' calendar
  const calendar = CalendarApp.getCalendarById(calendarId);
  
  const eventTitle = `[${taskData.criticality.toUpperCase()}] ${taskData.title}`;
  const eventOptions = {
    description: taskData.description,
    location: taskData.location || 'Queen of San Diego',
    guests: getTeamEmails(taskData.assignee)
  };
  
  calendar.createEvent(eventTitle, new Date(), new Date(Date.now() + 3600000), eventOptions);
}

function getMaintenanceCalendarId() {
  // Looks up 'Jada Maintenance' calendar under jadasailing@gmail.com
  const calendars = CalendarApp.getAllCalendars();
  return calendars.find(cal => cal.getName() === 'Jada Maintenance').getId();
}

Frontend Changes: Staging HTML

Modified /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/maintenance/staging-index.html to display newly added tasks prominently:

<div id="newTasksBanner" class="alert alert-info" style="display:none;">
  <h4>New Tasks Available</h4>
  <div id="newTasksList"></div>
  <button onclick="acknowledgeNewTasks()">Mark as Reviewed</button>
</div>

<script>
// Poll for new tasks every 30 seconds in staging
setInterval(function() {
  fetch('/api/maintenance/new-tasks?hours=1')
    .then(r => r.json())
    .then(tasks => {
      if (tasks.length > 0) {
        displayNewTasksNotification(tasks);
      }
    });
}, 30000);

function displayNewTasksNotification(tasks) {
  const banner = document.getElementById('newTasksBanner');
  const list = document.getElementById('newTasksList');
  
  list.innerHTML = tasks.map(t => 
    `<div class="task-item priority-${t.criticality}">
       <strong>${t.title}</strong> - ${t.description}
     </div>`
  ).join('');
  
  banner.style.display = 'block';
}
</script>

Infrastructure & Deployment

S3 & CloudFront

The staging maintenance tool is deployed to:

• S3 Bucket: Determined via CloudFront distribution for maintenance.queenofsandiego.com
• Path: /tools/maintenance/staging-index.html
• CloudFront Invalidation: Executed after each HTML deployment to clear cache

// Example deployment command
aws s3 cp staging-index.html s3://[bucket-name]/tools/maintenance/staging-index.html

//