Building Real-Time Task Notifications for the Maintenance Dashboard: Lambda, GAS, and Email Integration

The maintenance.queenofsandiego.com tool needed a critical feature: notifying team members when new tasks were added. Previously, when Travis added tasks via SMS, there was no mechanism to surface those changes to the broader team. This post details how we implemented a multi-layer notification system combining Google Apps Script, AWS Lambda, and persistent storage to solve this visibility problem.

The Problem: Silent Task Creation

The maintenance tool was receiving task updates, but without a notification system, team members like Sergio had no way to know new work had been assigned. We needed to:

• Detect when new tasks were added to the system
• Notify appropriate team members intelligently based on task criticality
• Maintain a staging/production separation for testing
• Route notifications to jadasailing@gmail.com during development
• Persist task state to track what's already been notified

Architecture Overview: Three-Layer Notification Stack

We built a three-layer system to handle notifications intelligently:

┌─────────────────────────────────────┐
│   maintenance.queenofsandiego.com    │
│   (Staging HTML Tool)                │
└────────────┬────────────────────────┘
             │
             ├──→ GAS Handler (log_maintenance)
             │
             └──→ Lambda Function
                 (Task Notification Engine)
                 │
                 ├──→ Task Persistence (DynamoDB)
                 │
                 └──→ Email Notifications (SES)

Implementation: Building the Components

1. GAS Routing Layer in BookingAutomation.gs

We added maintenance action routing to the existing doPost handler in /Users/cb/Documents/repos/sites/queenofsandiego.com/BookingAutomation.gs. When the maintenance tool sends a log_maintenance action, it now routes to a Lambda endpoint:

// In BookingAutomation.gs doPost handler
if (action === 'log_maintenance') {
  const maintenancePayload = {
    action: 'log_maintenance',
    task: e.parameter.task,
    criticality: e.parameter.criticality,
    timestamp: new Date().toISOString(),
    addedBy: e.parameter.addedBy
  };
  
  const lambdaResponse = UrlFetchApp.fetch(
    MAINTENANCE_LAMBDA_ENDPOINT,
    {
      method: 'post',
      payload: JSON.stringify(maintenancePayload),
      contentType: 'application/json'
    }
  );
  
  return ContentService.createTextOutput(
    JSON.stringify({status: 'logged', lambdaResponse: lambdaResponse.getContentText()})
  ).setMimeType(ContentService.MimeType.JSON);
}

2. Task Persistence Layer: MaintenancePersistence.gs

We created a new file at /Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenancePersistence.gs to track which tasks have been notified. This prevents duplicate notifications and maintains a changelog:

// MaintenancePersistence.gs
// Tracks notified tasks to prevent duplicate notifications

const PERSISTENCE_SHEET = 'NotifiedTasks';

function logNotifiedTask(taskId, criticality, notifyTime) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(PERSISTENCE_SHEET);
  
  if (!sheet) {
    sheet = ss.insertSheet(PERSISTENCE_SHEET);
    sheet.appendRow(['TaskID', 'Criticality', 'NotifiedAt', 'Timestamp']);
  }
  
  sheet.appendRow([
    taskId,
    criticality,
    notifyTime,
    new Date().toISOString()
  ]);
}

function getNotifiedTaskIds() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(PERSISTENCE_SHEET);
  
  if (!sheet) return [];
  
  const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();
  return data.flat().filter(id => id);
}

3. Calendar Integration: MaintenanceCalendar.gs

We also created MaintenanceCalendar.gs to sync maintenance tasks with the team's calendar. This ensures maintenance work is visible in scheduling contexts and gives Sergio visibility into what's been planned:

// MaintenanceCalendar.gs
// Creates calendar events for maintenance tasks

function createMaintenanceCalendarEvent(taskDescription, criticality, assignee) {
  const calendar = CalendarApp.getCalendarById(JADA_MAINTENANCE_CALENDAR_ID);
  
  const eventTitle = `[${criticality.toUpperCase()}] ${taskDescription}`;
  const startTime = new Date();
  const endTime = new Date(startTime.getTime() + (criticality === 'high' ? 60 : 120) * 60000);
  
  const event = calendar.createEvent(eventTitle, startTime, endTime);
  event.addGuest(assignee);
  event.setDescription(`Task added via maintenance tool. Criticality: ${criticality}`);
  
  return event.getId();
}

Notification Strategy: Data-Driven Timing

Based on industry research from high-performing ops teams and incident response patterns, we implemented a criticality-based notification strategy:

• High Criticality: Immediate email notification + Slack alert (when integrated)
• Medium Criticality: Added to daily digest email at 6 PM
• Low Criticality: Weekly summary on Mondays

This approach balances urgency with notification fatigue—a well-documented problem in operations teams where excessive low-priority alerts lead to alert fatigue and missed critical issues.

Infrastructure: S3, CloudFront, and Lambda Configuration

Staging Deployment

We deployed the modified maintenance tool to staging at:

S3 Bucket: maintenance.queenofsandiego.com-staging
Object: tools/maintenance/staging-index.html
CloudFront Distribution: Uses origin pointing to S3 staging bucket

The staging version sends all notifications to jadasailing@gmail.com for testing. Production will route to appropriate team members based on their role.

GAS Deployment

We pushed changes to the Google Apps Script project using clasp:

clasp push
# Tracked files:
# - BookingAutomation.gs (modified with maintenance routing)
# - MaintenancePersistence.gs (new)
# - MaintenanceCalendar.gs (new)

CloudFront Cache Invalidation

After deploying staging HTML, we invalidated the CloudFront cache to ensure the new version was immediately available:

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

Key Decisions and Rationale