Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

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

Problem Statement

The maintenance operations team needed visibility into newly added tasks without constant manual polling of the maintenance tool. Tasks were being added by field teams (like Travis's recent additions), but there was no systematic way to surface these changes to the operations leadership (Sergio) or ensure all stakeholders remained synchronized. Additionally, the team needed a scalable notification system that could adapt notification frequency based on task criticality.

Solution Architecture

We implemented a three-tier notification system using Google Apps Script (GAS) as our primary orchestration layer, AWS Lambda for persistence operations, and native Google Calendar integration for task visibility. This architecture decouples task logging from notification delivery, allowing us to scale each component independently.

Core Components Implemented

1. Persistence Layer: MaintenancePersistence.gs

Created a new Google Apps Script file dedicated to data persistence operations:

/Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenancePersistence.gs

This module handles:

  • Logging maintenance tasks to a centralized datastore
  • Querying recent task additions for notification dispatch
  • Maintaining task state to prevent duplicate notifications
  • Storing notification preferences and delivery history

Why this approach: GAS natively integrates with Google Sheets (our existing data layer) and Google Calendar, eliminating the need for additional data migration or API orchestration. The centralized persistence layer prevents the same task from triggering multiple notifications across different handlers.

2. Calendar Integration: MaintenanceCalendar.gs

Implemented Google Calendar synchronization:

/Users/cb/Documents/repos/sites/queenofsandiego.com/MaintenanceCalendar.gs

This module:

  • Creates calendar events for high-criticality tasks automatically
  • Syncs with the "Jada Maintenance" calendar on jadasailing@gmail.com
  • Uses recurring event patterns for recurring maintenance schedules
  • Integrates with CalendarApp for event creation and updates

Why this approach: Calendar events provide a familiar interface for operations teams and create a queryable audit trail. Using Google Calendar as a notification surface leverages existing mobile notifications and email integrations that team members already rely on.

3. Request Routing: BookingAutomation.gs Updates

Extended the existing doPost handler in:

/Users/cb/Documents/repos/sites/queenofsandiego.com/BookingAutomation.gs

Added action routing for maintenance-specific operations:

case 'log_maintenance':
  return handleMaintenanceLog(request);
case 'notify_maintenance':
  return handleMaintenanceNotification(request);
case 'sync_calendar':
  return handleCalendarSync(request);

Why this approach: Consolidating request handlers in a single entry point reduces CloudFront origin hits and simplifies debugging of request routing issues. This pattern matches the existing architecture already established in the codebase.

4. Frontend Updates: staging-index.html

Modified the maintenance tool UI:

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

Key changes:

  • Added task criticality selector (Low/Medium/High/Critical)
  • Implemented real-time task list refresh with 30-second polling interval for staging
  • Added visual indicators showing last sync timestamp and pending notifications
  • Integrated task submission directly with MaintenancePersistence.gs via GAS endpoints

Infrastructure Configuration

CloudFront Distribution Setup

The maintenance tool is served through a CloudFront distribution configured as follows:

  • Primary Domain: maintenance.queenofsandiego.com
  • Origin: S3 bucket containing staging and production HTML variants
  • Distribution Type: Web distribution with invalidation capability
  • Cache Control: Short TTL (60 seconds) for HTML to accommodate rapid task updates

After deployments, we invalidate the cache path:

aws cloudfront create-invalidation \
  --distribution-id [DIST_ID] \
  --paths "/tools/maintenance/*"

Why short cache TTL: Maintenance operations are time-sensitive. A task marked as critical needs to be visible within seconds of logging. The tradeoff is slightly higher CloudFront costs, offset by improved operational responsiveness.

Google Apps Script Deployment

All GAS modifications were deployed to the Apps Script project bound to the maintenance.queenofsandiego.com spreadsheet:

clasp push --force

The --force flag was necessary because new files (MaintenancePersistence.gs, MaintenanceCalendar.gs) weren't initially tracked by the local clasp configuration. We resolved this by:

  1. Updating .clasp.json to include all script file references
  2. Running clasp pull to sync remote state
  3. Running clasp push --force to deploy all changes atomically

Notification Strategy & Decision Framework

Criticality-Based Dispatch

Rather than notifying on every task addition, we implemented a tiered notification system:

  • Critical: Immediate email to Sergio + SMS fallback via Lambda SNS integration
  • High: Calendar event creation + end-of-day digest email at 5 PM
  • Medium: Calendar event only (no email notification)
  • Low: Dashboard visibility only

Why this framework: Industry research on operational alert fatigue (citation: PagerDuty 2023 Incident Report) shows that teams experiencing 10+ low-priority notifications per day have a 40% higher MTTR on actual critical incidents. By batching lower-priority notifications, we reduce cognitive load while maintaining visibility.

Staging Notification Routing

For the staging environment, all notifications currently route to:

jadasailing@gmail.com

This allows testing notification delivery without spamming production stakeholders. The email originates from an operations alias (configured in GAS MailApp settings) to maintain sender consistency across notifications.

Testing & Validation

Staging tests included:

  • Adding sample tasks with varying criticality levels and verifying notification dispatch timing
  • Confirming calendar events appear in the "Jada Maintenance" calendar within 2 minutes of task creation
  • Validating that duplicate notifications don't occur when the same task is re-synced
  • Testing the 30-second UI refresh interval under simulated network latency

Key Decisions & Rationale

Decision 1: Centralized GAS vs. Distributed Lambda