Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

Building a Real-Time Maintenance Task Notification System with Lambda, Google Apps Script, and CloudFront

When Travis added new maintenance tasks to the maintenance.queenofsandiego.com tool, they disappeared into the void. Sergio had no way to know tasks were added, and CB couldn't surface them without manual polling. This post documents the architecture we built to solve this: a Lambda-backed persistence layer that detects new tasks, evaluates their criticality, and routes notifications intelligently to the team.

The Problem: Silent Task Creation

The maintenance tool is a client-side HTML + JavaScript application served through CloudFront (distribution ID varies by environment). Users could add tasks, but those tasks only lived in browser session storage. New tasks weren't persisted, weren't indexed, and critically—weren't surfaced to the operations team. Sergio and CB needed real-time visibility into what was being tracked.

The challenge: How do you add server-side persistence and notifications to a tool that was designed as a pure frontend application, without breaking the existing user experience or creating a maintenance nightmare with separate staging/production deployments?

Architecture: Three-Layer Notification Stack

We implemented a three-layer system:

  • Frontend Layer: Modified staging HTML at /sites/queenofsandiego.com/tools/maintenance/staging-index.html to capture task creation events and POST to a GAS endpoint
  • Middleware Layer: Google Apps Script handlers in BookingAutomation.gs that route log_maintenance actions to Lambda and trigger immediate notifications
  • Backend Layer: AWS Lambda function that persists tasks to DynamoDB, evaluates criticality, and publishes to SNS for digest or immediate notifications

This three-layer approach kept the staging/production separation problem tractable: we only needed to change the frontend staging HTML and add routes to the existing GAS script. The Lambda function is environment-agnostic and can be updated independently.

Frontend Modification: Event Capture

The staging HTML already had a task creation form. We instrumented the form submission with an additional XMLHttpRequest that POSTs to the GAS endpoint:

function onTaskCreated(taskData) {
  const payload = {
    action: 'log_maintenance',
    task_name: taskData.name,
    task_description: taskData.description,
    task_criticality: taskData.criticality || 'normal',
    task_assigned_to: taskData.assigned_to || 'unassigned',
    timestamp: new Date().toISOString(),
    created_by: getCurrentUser()
  };

  fetch('https://script.google.com/macros/d/YOUR_SCRIPT_ID/usercontent', {
    method: 'POST',
    headers: { 'Content-Type': 'application/json' },
    body: JSON.stringify(payload)
  })
  .catch(err => console.log('Task logged to backend'));
}

Note: This POST happens after the task is added to local storage, so network failures don't block the user experience. If the POST fails, the task still exists client-side.

GAS Middleware: Routing and Triggering

We added a new handler to BookingAutomation.gs within the existing doPost dispatcher. The dispatcher pattern was already in place for other actions; we just extended it:

function doPost(e) {
  const params = JSON.parse(e.postData.contents);
  
  if (params.action === 'log_maintenance') {
    return handleMaintenanceLog(params);
  }
  
  // ... other actions
}

function handleMaintenanceLog(params) {
  // 1. Persist to MaintenancePersistence.gs
  MaintenancePersistence.logTask(params);
  
  // 2. Invoke Lambda for backend processing
  const lambdaPayload = {
    action: 'persist_and_notify',
    task: params,
    environment: 'staging' // for routing notifications correctly
  };
  
  const response = UrlFetchApp.fetch(LAMBDA_ENDPOINT, {
    method: 'post',
    payload: JSON.stringify(lambdaPayload),
    headers: { 'Authorization': 'Bearer ' + LAMBDA_INVOKE_TOKEN }
  });
  
  return ContentService.createTextOutput('OK');
}

The GAS script uses an IAM-authenticated service account to invoke the Lambda function. The service account's role ARN was configured to allow lambda:InvokeFunction on the maintenance Lambda ARN only.

Persistence Layer: MaintenancePersistence.gs

We created a new GAS library file at /sites/queenofsandiego.com/MaintenancePersistence.gs to maintain a server-side log of tasks. This isn't a replacement for the Lambda DynamoDB table; it's a lightweight backup and audit trail:

const MaintenancePersistence = {
  logTask: function(taskData) {
    const sheet = SpreadsheetApp
      .openById(MAINTENANCE_SHEET_ID)
      .getSheetByName('Task Log');
    
    sheet.appendRow([
      new Date(),
      taskData.task_name,
      taskData.task_description,
      taskData.task_criticality,
      taskData.created_by,
      taskData.timestamp
    ]);
  }
};

This serves two purposes: (1) If Lambda is unavailable, tasks are still logged, and (2) the sheet becomes a human-readable audit trail that Sergio can review without querying DynamoDB.

Lambda Backend: Criticality Evaluation and Routing

The Lambda function (deployed via CloudFormation, runtime Node.js 18) evaluates task criticality and makes notification decisions based on industry best practices for operational notifications:

  • Critical tasks (e.g., "electrical hazard," "safety", "structural") → Immediate SMS + email to Sergio and CB
  • High-priority tasks (e.g., "engine," "rigging," "bilge") → Email within 30 minutes, batched if multiple tasks arrive
  • Normal/Low tasks → Daily digest email at 6 PM Pacific

Criticality keywords are evaluated against the task name and description using simple string matching. The Lambda function publishes to an SNS topic (jada-maintenance-notifications) with a message attribute priority that determines how each subscriber processes the message.

async function evaluateCriticality(taskData) {
  const criticalKeywords = ['electrical', 'safety', 'hazard', 'structural'];
  const highKeywords = ['engine', 'rigging', 'bilge', 'hull', 'mast'];
  
  const combined = 
    (taskData.task_name + ' ' + taskData.task_description).toLowerCase();
  
  if (criticalKeywords.some(kw => combined.includes(kw))) {
    return 'CRITICAL';
  }
  if (highKeywords.some(kw => combined.includes(kw))) {
    return 'HIGH';
  }
  return 'NORMAL';
}

Notification Routing and Testing

Because staging and production are currently the same deployment, we route staging notifications to jadasailing@gmail.com with a [STAGING] prefix. The email is sent from a SES-verified address (