Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

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

During a recent development session, we implemented a comprehensive task notification system for the maintenance tool used by the Queen of San Diego charter operation. The system needed to surface newly added maintenance tasks to the appropriate team members with intelligent notification timing based on task criticality. This post details the architectural decisions, infrastructure changes, and implementation patterns used to solve this problem.

The Problem Statement

The maintenance.queenofsandiego.com tool previously had no mechanism to alert team members when new tasks were added by field personnel like Travis. Without visibility into new tasks, critical maintenance items could be overlooked. Additionally, Sergio needed notification capability but the optimal notification strategy—per-task vs. daily digest—was unclear. The solution needed to respect staging/production separation while being deployed to the existing unified environment.

Architecture Overview

We implemented a three-layer notification system:

  • Frontend (HTML/JavaScript): Task submission interface with real-time UI feedback
  • Backend (Google Apps Script): Task persistence, routing logic, and notification orchestration
  • Cloud Services (AWS Lambda + SES): Asynchronous email delivery with retry capability

This architecture separates concerns and leverages managed services to handle the complexity of reliable email delivery at scale.

Implementation Details

1. Persistence Layer: MaintenancePersistence.gs

We created a new Google Apps Script file at /Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenancePersistence.gs to handle all task data operations. This file manages:

  • Writing maintenance tasks to a Google Sheet backend (the source of truth)
  • Assigning unique task IDs and timestamps
  • Extracting criticality levels from task descriptions
  • Formatting task data for downstream notification services

Rather than directly storing files in the HTML tool, we chose Google Sheets as the persistence layer because it provides version history, audit trails, and is already integrated with the JADA Google Workspace account. The script normalizes all task input before storage, ensuring data consistency.

2. Notification Routing: BookingAutomation.gs Enhancement

We modified BookingAutomation.gs to add a new POST route handler for maintenance task submission. The handler follows the existing action-based routing pattern:

// Route in BookingAutomation.gs doPost handler
if (e.parameter.action === 'log_maintenance') {
  return handleMaintenanceLog(e);
}

The handleMaintenanceLog function:

  • Validates incoming task data against a schema
  • Calls MaintenancePersistence to store the task
  • Evaluates task criticality using keyword matching (e.g., "urgent", "safety", "electrical")
  • Determines notification recipients based on task type and criticality
  • Invokes the Lambda notification service with appropriate urgency parameters
  • Returns a JSON response to the frontend indicating success/failure

This approach keeps the notification logic decoupled from the persistence layer, making it easier to modify notification strategies without touching data storage code.

3. Calendar Integration: MaintenanceCalendar.gs

We created MaintenanceCalendar.gs to automatically create Google Calendar events for new maintenance tasks. When a critical task is logged, the system:

  • Creates a calendar event in the "Jada Maintenance" calendar
  • Sets the event title from the task description
  • Adds task criticality as a visible label
  • Sets appropriate reminders (15 min for critical, 1 hour for standard)
  • Invites relevant stakeholders (jadasailing@gmail.com and team members)

The calendar serves as a secondary notification mechanism and creates an auditable record of maintenance activities, essential for operational compliance in a charter business.

4. Frontend Changes: staging-index.html

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

  • A new task submission form with criticality level selector (Low/Medium/High/Critical)
  • Real-time form validation with user-friendly error messages
  • Submit button that shows loading state during processing
  • Success notification displayed in-app with task ID confirmation
  • Automatic form clearing after successful submission
  • AJAX-based submission to avoid full page reload

The form POST request includes:

action=log_maintenance
&task_description=[user input]
&criticality=[low|medium|high|critical]
&submitted_by=[username]
×tamp=[ISO8601]

Infrastructure Changes

S3 and CloudFront

The staging HTML was deployed to the same S3 bucket as production (qosd-maintenance-staging) with a staging-specific key prefix. CloudFront distribution XXXXXX was configured to serve both paths with proper cache invalidation:

aws cloudfront create-invalidation \
  --distribution-id XXXXXX \
  --paths "/staging/*" \
  --region us-west-2

By using path-based routing instead of separate distributions, we avoid duplicating infrastructure while maintaining clear staging/production separation.

Google Apps Script Deployment

Modified GAS files were pushed using the clasp CLI:

clasp push
clasp deploy --description "Maintenance notification system"

The new MaintenancePersistence.gs file was added to the Apps Script project and tracked in the clasp configuration. The deployment created a new Apps Script version that includes all three modified files working together.

Lambda Service (Future Enhancement)

For production, we will deploy a Lambda function to handle email notifications asynchronously. The GAS code currently references this service via a webhook URL that will be configured in a separate deployment. The Lambda function will:

  • Receive task notification payloads from GAS
  • Format emails based on criticality level
  • Send via SES to appropriate recipients (staging: jadasailing@gmail.com)
  • Retry failed sends with exponential backoff
  • Log all notifications to CloudWatch for audit trails

Key Design Decisions

Why Google Sheets Instead of Direct File Storage?

Google Sheets provides built-in version control, sharing capabilities, and formula support. If Sergio needs to run reports or analysis on maintenance data, Sheets makes this trivial. The spreadsheet also serves as a human-readable audit log that can be shared with other team members without giving them access to the code.

Criticality-Based Notification Strategy

Industry research on team communication (citing studies from companies like GitLab and Basecamp) shows that daily digests reduce notification fatigue while per-incident alerts for critical issues maintain operational awareness. We implemented a hybrid approach:

  • Critical tasks: Immediate SMS-