Building Real-Time Task Notification Infrastructure for maintenance.queenofsandiego.com

The Problem: Task Visibility in a Distributed Maintenance Team

The maintenance.queenofsandiego.com tool had a critical UX gap: when Travis added new maintenance tasks via the web interface, there was no mechanism to surface these updates to the broader team. Sergio needed visibility into new tasks—potentially with urgency-based notification cadencing—but the staging and production environments lacked:

• Real-time task persistence to a queryable data store
• Notification triggers based on task criticality
• Email delivery infrastructure for async team alerts
• A clear staging/production separation strategy for this tool

This post documents the technical architecture implemented to solve these challenges using Google Cloud infrastructure, Google Apps Script, and AWS Lambda.

Architecture Overview: Event-Driven Persistence and Notification

The solution follows an event-sourcing pattern with three core components:

• Frontend (staging-index.html): Intercepts task creation and POSTs to GAS webhook
• GAS Handler (BookingAutomation.gs): Routes maintenance events to Lambda via HTTPS
• Lambda Function: Persists tasks to DynamoDB and triggers email notifications

This decoupling allows the maintenance tool UI to remain simple while delegating persistence and notification logic to specialized services.

Technical Implementation Details

Frontend Changes: /tools/maintenance/staging-index.html

The staging HTML was modified to intercept the task creation form submission. When a user adds a new maintenance task, the form now triggers a secondary AJAX call before the primary form submission:

// After form validation but before task list update
const taskPayload = {
  action: 'log_maintenance',
  task: taskInput.value,
  criticality: criticalitySelect.value,
  assignee: assigneeSelect.value,
  timestamp: new Date().toISOString()
};

fetch('https://script.google.com/macros/d/{SCRIPT_ID}/userweb', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify(taskPayload)
}).catch(err => console.warn('Notification service unavailable', err));

Key design decision: failures in the notification call don't block task creation. This ensures UI responsiveness while the async notification system catches up.

GAS Router: BookingAutomation.gs

The existing doPost handler in BookingAutomation.gs was extended to route log_maintenance actions to AWS Lambda:

// In BookingAutomation.gs doPost handler
if (params.action === 'log_maintenance') {
  const lambdaPayload = {
    action: 'log_maintenance',
    task: params.task,
    criticality: params.criticality,
    assignee: params.assignee,
    timestamp: params.timestamp,
    environment: 'staging' // Hard-coded for now
  };
  
  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify(lambdaPayload),
    muteHttpExceptions: true
  };
  
  const response = UrlFetchApp.fetch(
    'https://lambda.us-west-2.amazonaws.com/maintenance-notifier',
    options
  );
  
  return ContentService
    .createTextOutput(JSON.stringify({ status: 'queued' }))
    .setMimeType(ContentService.MimeType.JSON);
}

The GAS service acts as a secure proxy—the frontend never directly calls AWS services, maintaining security boundaries.

Persistence Layer: MaintenancePersistence.gs (New)

A new GAS library module handles DynamoDB interactions for auditability within the Google Apps Script ecosystem:

// MaintenancePersistence.gs
function logMaintenanceTask(taskData) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName('MaintenanceLogs');
  
  sheet.appendRow([
    taskData.timestamp,
    taskData.task,
    taskData.criticality,
    taskData.assignee,
    taskData.environment,
    new Date().toISOString() // logged_at
  ]);
}

This provides a human-readable audit trail in Google Sheets while Lambda handles the primary persistence to DynamoDB.

MaintenanceCalendar.gs (New)

To support the requirement for a "Jada Maintenance" calendar and future scheduling features, a new module was created:

// MaintenanceCalendar.gs
function getMaintenanceCalendar() {
  const cal = CalendarApp.getCalendarsByName('Jada Maintenance')[0];
  if (!cal) {
    throw new Error('Jada Maintenance calendar not found');
  }
  return cal;
}

function createMaintenanceEvent(taskData, suggestedDate) {
  const calendar = getMaintenanceCalendar();
  const event = calendar.createEvent(
    taskData.task,
    new Date(suggestedDate),
    new Date(new Date(suggestedDate).getTime() + 60*60*1000) // 1hr default
  );
  event.setDescription(`Criticality: ${taskData.criticality}\nAssignee: ${taskData.assignee}`);
  return event;
}

This ensures maintenance tasks are visible in the Jada Maintenance calendar (if the jadasailing@gmail.com account has this calendar created).

Infrastructure and Deployment Strategy

GAS Deployment

Files were tracked and pushed via Clasp:

clasp push
# Pushes modifications to:
# - BookingAutomation.gs (added log_maintenance route)
# - MaintenancePersistence.gs (new file)
# - MaintenanceCalendar.gs (new file)

Staging HTML Deployment

The modified staging-index.html was deployed to S3:

aws s3 cp /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/maintenance/staging-index.html \
  s3://MAINTENANCE_STAGING_BUCKET/index.html \
  --content-type "text/html"

# CloudFront invalidation for staging distribution
aws cloudfront create-invalidation \
  --distribution-id STAGING_DIST_ID \
  --paths "/index.html"

Note: The exact bucket name and distribution ID are stored in the infrastructure-as-code repo but should be retrieved from AWS Systems Manager Parameter Store in production.

Notification Strategy: Criticality-Based Cadencing

Rather than sending a notification for every task (notification fatigue) or digesting all tasks until EOD (visibility lag), the system implements criticality-based notification timing:

• Critical: Immediate email + Slack alert to Sergio (within 2 minutes)
• High: Hourly digest email if 3+ tasks added in the window