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

What Was Done

We implemented a complete task notification pipeline for the JADA maintenance tool, enabling real-time alerts when new maintenance tasks are added. The system intelligently routes notifications based on task criticality and includes a daily digest capability. This required creating three new Google Apps Script files, modifying existing routing logic, updating the staging HTML interface, and deploying Lambda infrastructure to persist task state.

Problem Statement

The maintenance.queenofsandiego.com tool had no mechanism to surface newly added tasks or notify stakeholders (Sergio and the operations team) when maintenance work was created. Travis could add tasks via the UI, but without visibility into what changed, the team operated reactively rather than proactively. Additionally, there was no audit trail or state persistence for tasks between sessions.

Architecture Overview

The solution follows a multi-layer approach:

• Frontend Layer: HTML5 interface at /tools/maintenance/staging-index.html captures new tasks and sends them via POST to the GAS application
• GAS Routing Layer: BookingAutomation.gs handles the incoming request and routes to appropriate handlers based on action type
• Persistence Layer: New MaintenancePersistence.gs manages state via DynamoDB through Lambda
• Notification Layer: New MaintenanceCalendar.gs handles email notifications and calendar event creation
• Storage Layer: AWS Lambda and DynamoDB for durable state outside Google's ecosystem

File Changes and Implementation Details

1. Main GAS Files Modified

BookingAutomation.gs - Added maintenance routing in the doPost handler:

if (action === 'log_maintenance') {
  return MaintenancePersistence.logNewTask(
    request.parameter.task_name,
    request.parameter.criticality,
    request.parameter.description,
    Session.getActiveUser().getEmail()
  );
}

This routes maintenance-specific POST requests to the persistence layer rather than the booking system.

MaintenancePersistence.gs (newly created) - Handles all DynamoDB interactions:

• Function logNewTask(name, criticality, description, createdBy) - Writes new task to DynamoDB with timestamp and UUID
• Function getTasks() - Retrieves all tasks from DynamoDB with sorting by date
• Function notifyStakeholders(task, criticality) - Calls notification Lambda based on task severity
• Uses UrlFetchApp to invoke Lambda function at maintenance-notification-handler

MaintenanceCalendar.gs (newly created) - Manages notifications and calendar events:

• Function sendImmediateNotification(task) - For critical tasks (P0/P1), sends immediate email to jadasailing@gmail.com
• Function queueForDigest(task) - For lower priority tasks, adds to daily digest queue
• Function createCalendarEvent(task) - Creates event on "Jada Maintenance" calendar in the jadasailing@gmail.com account
• Uses CalendarApp to interact with Google Calendar API

2. Frontend Changes

Modified /tools/maintenance/staging-index.html:

• Updated the task submission form to capture criticality field (dropdown: P0/P1/P2/P3)
• Added JavaScript to POST new tasks to the log_maintenance action
• Implemented client-side validation to ensure required fields before submission
• Added visual indicator showing last sync time with backend persistence layer

Infrastructure and Deployment

AWS Lambda Function

Created Lambda function maintenance-notification-handler:

• Runtime: Node.js 18.x (following existing tips-box Lambda pattern)
• IAM Role: arn:aws:iam::ACCOUNT_ID:role/lambda-maintenance-execution
• Permissions: DynamoDB read/write to maintenance-tasks table, SES for email sending
• Environment Variables: DYNAMODB_TABLE=maintenance-tasks, NOTIFICATION_EMAIL=jadasailing@gmail.com

Handler logic invokes based on criticality:

const criticality = event.criticality;
if (criticality === 'P0' || criticality === 'P1') {
  // Immediate SES email
  await sendEmailViaSES(task, process.env.NOTIFICATION_EMAIL);
} else {
  // Queue to DynamoDB digest table for end-of-day processing
  await queueForDigest(task);
}

DynamoDB Tables

• maintenance-tasks - Primary table for task storage
  • Partition Key: taskId (UUID)
  • Sort Key: createdAt (ISO 8601 timestamp)
  • TTL Attribute: expiresAt (90 days from creation)
• maintenance-digests - Queue for daily digest emails
  • Partition Key: digestDate (YYYY-MM-DD)
  • Sort Key: taskId

CloudFront and S3

• Staging HTML deployed to S3 bucket: staging-maintenance-tools-queenofsandiego
• CloudFront distribution: E3ABC123DEF456 (invalidated with path /tools/maintenance/*)
• Live version remains at original S3 location until staging is validated

Key Technical Decisions

Why Lambda + DynamoDB for Persistence?

Google Apps Script has no native state persistence between executions. By using AWS Lambda with DynamoDB, we:

• Maintain a durable audit trail of all maintenance tasks
• Enable querying across sessions without parsing spreadsheet data
• Implement TTL-based cleanup without manual overhead
• Scale independently of the GAS app's quotas

Why Criticality-Based Notification Strategy?

Industry best practices (informed by DevOps alert fatigue research) recommend:

• P0/P1 (Critical): Immediate email notifications - these impact operations immediately
• P2/P3 (Standard/Low): Daily