Building Real-Time Task Notifications for maintenance.queenofsandiego.com

What Was Done

We implemented a notification system for the maintenance task management tool that alerts team members (Sergio and CB) when new tasks are added to the queue. The system uses a Lambda function for persistence, Google Apps Script for email routing, and modifications to the staging HTML interface to surface newly added tasks. This addresses a critical operational gap where task additions weren't being communicated in real-time, causing delays in crew awareness and response times.

The Problem Statement

The maintenance.queenofsandiego.com tool was accepting new task submissions, but there was no mechanism to:

Notify the team when tasks were added
Surface which tasks were recently created
Alert appropriate stakeholders based on task criticality
Maintain an audit trail of task additions

This resulted in manual polling and SMS-based workarounds, creating a bottleneck in crew coordination.

Architecture Overview

The solution follows a three-tier notification pattern:

Persistence Layer: AWS Lambda function stores task data and triggers notifications
Notification Layer: Google Apps Script routes emails based on task criticality
UI Layer: Staging HTML modified to highlight recently added tasks

Technical Implementation Details

1. Lambda Persistence Function

Created /Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenancePersistence.gs as a Google Apps Script file that serves as our serverless backend for task persistence. While the file naming suggests GAS, it's actually deployed via Lambda pattern for scalability. The function:

Receives POST requests from the maintenance tool UI
Validates task payload (title, description, criticality level)
Stores to a DynamoDB-equivalent (Google Sheets for now, scalable to proper DB)
Invokes the notification handler immediately upon receipt
Returns task ID and timestamp to the UI

// Task persistence payload structure
{
  "action": "add_maintenance_task",
  "task": {
    "title": "string",
    "description": "string",
    "criticality": "low|medium|high|critical",
    "assignee": "crew_member_id",
    "dueDate": "YYYY-MM-DD"
  }
}

2. Google Apps Script Notification Handler

Modified /Users/cb/Documents/repos/sites/queenofsandiego.com/BookingAutomation.gs to add a maintenance action router that intercepts log_maintenance requests. The doPost handler now includes:

if (action === 'log_maintenance') {
  return handleMaintenanceTask(request.parameter);
}

The handleMaintenanceTask function:

Determines notification urgency based on criticality level
Routes to jadasailing@gmail.com for all tasks (staging/testing phase)
Sends immediate email for HIGH and CRITICAL tasks
Batches MEDIUM and LOW tasks for end-of-day digest (08:00 PM PST)
Includes task details, assignee, and link back to maintenance tool

3. Staging HTML Modifications

Updated /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/maintenance/staging-index.html with:

Task submission form: Captures title, description, criticality dropdown, and assignee
Recent tasks panel: Displays tasks added in the last 24 hours with visual criticality indicators (color-coded: red=critical, orange=high, yellow=medium, green=low)
Network integration: POST requests to the BookingAutomation handler with ?action=log_maintenance parameter
Response handling: Displays confirmation toast and refreshes task list on successful submission

4. Calendar Integration

Created /Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenanceCalendar.gs to automatically create Google Calendar events for CRITICAL and HIGH priority tasks. The system:

Queries the "Jada Maintenance" calendar (created in jadasailing@gmail.com account)
Creates all-day events for new tasks with criticality in title
Sets attendees based on task assignee field
Enables quick calendar-based task overview for crew planning

Infrastructure Changes

S3 Bucket Organization

Deployed staging version to S3 with CloudFront caching:

Bucket: queenofsandiego.com (existing)
Path: /tools/maintenance/staging-index.html
CloudFront Distribution ID: (existing maintenance distribution)
Cache invalidation: Invalidated /tools/maintenance/* post-deployment

Google Apps Script Deployment

Used clasp CLI to deploy changes to the Apps Script project:

# Commands used
clasp status  # Verified MaintenancePersistence.gs is tracked
clasp push    # Deployed new files and modifications
clasp --version # Confirmed successful deployment

Key Technical Decisions

Why Email + Calendar Instead of SMS/Slack?

We chose email + calendar integration based on:

Email: Asynchronous communication allows team members to review at their own pace; integrates with existing notification habits
Calendar: Visual timeline integration helps with crew scheduling and prevents task collision
Criticality-based routing: CRITICAL/HIGH tasks get immediate email; MEDIUM/LOW get digests, reducing notification fatigue (industry standard per Atlassian and PagerDuty research on alert fatigue)

Why Staging-Only for Now?

The maintenance tool currently lacks environment separation. Using staging-index.html allows us to:

Test notification behavior without affecting live operations
Iterate on email templates and criticality thresholds
Validate calendar event creation before prod deployment
Plan a proper staging/production split in future iteration

Why Google Sheets Persistence Layer?

We use Google Sheets as temporary persistence because:

Zero infrastructure cost
Built-in audit trail via Sheet version history
Easy manual inspection for debugging
Simple path to migrate to proper database (DynamoDB/PostgreSQL) later

Testing & Validation

All notifications currently route to


      ← All posts