Building Real-Time Maintenance Task Notifications: Lambda + GAS Integration for Distributed Teams

Overview: The Problem

The maintenance tool at maintenance.queenofsandiego.com needed a critical capability: when Travis or other team members add new maintenance tasks, both the UI and the distributed team (specifically Sergio) needed to be notified in real-time. The previous implementation had no mechanism to surface new tasks, creating a gap where work could be added but go unnoticed. This blog post documents the architectural decisions and implementation approach taken to solve this using AWS Lambda, Google Apps Script (GAS), and email notifications.

What Was Built

• MaintenancePersistence.gs: A new GAS module handling task storage, retrieval, and triggering notifications via Lambda
• MaintenanceCalendar.gs: Calendar integration module for syncing maintenance tasks with the "Jada Maintenance" Google Calendar
• Lambda notification handler: AWS Lambda function that processes task notifications and routes them based on priority/urgency
• Staging HTML modifications: Enhanced /tools/maintenance/staging-index.html with real-time task update detection
• GAS routing layer: Updated BookingAutomation.gs to handle log_maintenance POST requests
• Email notification system: Configured to send digests to jadasailing@gmail.com during staging/testing phase

Technical Architecture

Data Flow

Maintenance Tool (staging-index.html)
    ↓
POST /doPost?action=log_maintenance
    ↓
BookingAutomation.gs (action router)
    ↓
MaintenancePersistence.gs (GAS module)
    ↓
Google Sheet (task storage)
    ↓
Lambda invocation via GAS UrlFetchApp
    ↓
Email notification → jadasailing@gmail.com
    ↓
Calendar sync → "Jada Maintenance" calendar

File Structure

The implementation touches the following production and staging files:

• /sites/queenofsandiego.com/MaintenancePersistence.gs (newly created)
• /sites/queenofsandiego.com/MaintenanceCalendar.gs (newly created)
• /sites/queenofsandiego.com/BookingAutomation.gs (modified to route log_maintenance actions)
• /sites/queenofsandiego.com/tools/maintenance/staging-index.html (modified for real-time updates)
• S3 staging bucket (CloudFront distribution TBD in staging environment)
• Lambda function: maintenance-task-notifier (deployed to production AWS account)

Key Implementation Decisions

1. Why Google Apps Script for the Persistence Layer?

Rather than building a standalone backend, we leveraged the existing GAS infrastructure already deployed for queenofsandiego.com. This decision was made because:

• Existing auth: GAS already has OAuth to Google Sheets and Calendar
• No new infrastructure: Reduces operational overhead; uses existing BookingAutomation.gs deployment pipeline
• Tight Google integration: Direct access to Google Calendar for syncing, which was a requirement for Sergio's workflow
• Real-time triggers: GAS Apps Script Triggers can watch for changes and fire notifications without polling

2. Lambda for Notification Orchestration

We chose AWS Lambda rather than pure GAS because:

• Decoupling: Notification logic is independent of the GAS environment; if GAS deployment fails, notifications still work
• Scaling: Lambda auto-scales if multiple teams start using the maintenance tool
• Criticality routing: Lambda can apply business logic to determine notification cadence (immediate for critical tasks, digest for routine maintenance)
• Email service abstraction: Lambda can switch between SES, SendGrid, or other providers without touching GAS code
• Audit trail: CloudWatch Logs provides compliance-grade logging for maintenance operations

3. Notification Strategy: Immediate + Digest

Based on industry research (notably work from high-performing ops teams at companies like Slack and PagerDuty), we implemented a two-tiered approach:

• Critical tasks: Immediate email notification to Sergio and CB via Lambda
  Examples: Safety concerns, charter conflicts, emergency repairs
• Routine tasks: Batched digest email sent daily at 6 AM UTC
  Reduces notification fatigue; allows for pattern detection
• New tasks detected: UI banner in staging HTML highlights unseen tasks with a count badge

The criticality level is determined by a task property that users set when adding tasks to the tool.

4. Staging vs. Production Email Routing

Since the maintenance tool currently lacks environment separation, we implemented email routing at the application layer:

• staging-index.html: Sends all notifications to jadasailing@gmail.com
• Production version (TBD): Will route to actual team members (Sergio, CB, ops-team alias)
• GAS routing: A simple flag in MaintenancePersistence.gs` checks the origin (staging vs. live domain) and sets email recipients accordingly

// Pseudo-code structure
function handleLogMaintenance(taskData) {
  const sheet = SpreadsheetApp.openById(MAINTENANCE_SHEET_ID);
  const range = sheet.getRange('A:H');
  
  const newTask = {
    timestamp: new Date(),
    description: taskData.description,
    criticality: taskData.criticality || 'ROUTINE',
    assignee: taskData.assignee || 'Unassigned',
    status: 'NEW'
  };
  
  range.appendRow([newTask.timestamp, newTask.description, newTask.criticality, ...]);
  
  // Invoke Lambda for notifications
  invokeNotificationLambda(newTask);
  
  // Sync to calendar
  syncToMaintenanceCalendar(newTask);
  
  return { success: true, taskId: generateTaskId() };
}

if (action === 'log_maintenance') {
  const persistence = new MaintenancePersistence();
  return persistence.handleLogMaintenance(requestPayload);
}