Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building Real-Time Task Notifications for the Maintenance Tool: Lambda + GAS + CloudFront Architecture

The maintenance.queenofsandiego.com tool needed a critical feature: surface newly added maintenance tasks and notify the operations team in real-time. This post details the infrastructure and code patterns we implemented to solve task visibility and team notification at scale.

What Was Done

  • Created MaintenancePersistence.gs — a Google Apps Script module for durable task storage and email notifications
  • Created MaintenanceCalendar.gs — calendar integration to sync maintenance tasks into Google Calendar
  • Enhanced BookingAutomation.gs with log_maintenance and notify_maintenance action routes
  • Modified staging maintenance tool HTML at /tools/maintenance/staging-index.html to expose new task creation endpoints
  • Set up email notifications to jadasailing@gmail.com for task intake and team visibility
  • Implemented criticality-based notification pacing using industry best practices from high-performing maintenance teams

Technical Architecture

Why This Approach?

We faced a classic operations problem: Travis adds tasks, but they disappear into a static HTML tool with no persistence layer, no audit trail, and no way for Sergio to know something new was added. The solution needed to:

  • Persist data durably — Google Sheets via Apps Script provides free, queryable storage without managing infrastructure
  • Notify intelligently — Use task criticality to determine notification frequency (urgent = immediate, routine = daily digest)
  • Integrate with existing tools — Google Calendar is already in use; sync maintenance events there
  • Live in staging first — Test the full flow before production cutover

GAS Handler Architecture

The BookingAutomation.gs doPost handler already routes requests by action type. We extended it with two new routes:

// In BookingAutomation.gs doPost handler
if (params.action === 'log_maintenance') {
  return MaintenancePersistence.logTask(params);
}
if (params.action === 'notify_maintenance') {
  return MaintenanceCalendar.scheduleNotification(params);
}

This keeps the request routing centralized and leverages the existing POST endpoint infrastructure at https://script.google.com/macros/d/{SCRIPT_ID}/usercopy.

MaintenancePersistence.gs Module

This new file handles the core persistence and notification logic:

  • logTask(params) — Appends task to a Google Sheet named "Maintenance Log", captures timestamp, creator, description, criticality level (1-5), and status
  • evaluateNotification(taskData) — Determines notification strategy based on criticality:
    • Criticality 5 (emergency): Immediate email to jadasailing@gmail.com
    • Criticality 4 (urgent): Immediate email
    • Criticality 3 (standard): Immediate email (ops team reviews batch)
    • Criticality 1-2 (routine): Daily digest at 06:00 UTC via time-based trigger
  • sendTaskNotification(taskData, recipients) — Formats and sends emails with task details, includes calendar link

MaintenanceCalendar.gs Module

This new file integrates with Google Calendar to make maintenance tasks visible in Sergio's calendar view:

  • scheduleNotification(taskData) — Creates a Google Calendar event in the "Jada Maintenance" calendar if it doesn't exist, uses the task due date or defaults to 24 hours from creation
  • syncTask(taskData) — Updates existing calendar events if a task is modified
  • Uses CalendarApp native GAS API rather than external Calendar API to avoid auth complexity

Staging HTML Modifications

The file at /Users/cb/Documents/repos/sites/queenofsandiego.com/tools/maintenance/staging-index.html was modified to:

  • Add a form section for "New Task" with fields: Task Description, Criticality (1-5 dropdown), Due Date (date picker), Assigned To (dropdown)
  • POST to BookingAutomation.gs with action=log_maintenance on form submit
  • Display confirmation: "Task created and notifications sent to operations team"
  • Add a "View in Calendar" button that links to https://calendar.google.com/calendar/u/0/r/search?q=Jada%20Maintenance
  • Render existing tasks from the Sheets API, sorted by criticality descending, then by date ascending

The modified staging version was deployed to S3 at:

s3://maintenance-queenofsandiego-com/staging-index.html

Infrastructure Changes

CloudFront Distribution

The maintenance tool uses CloudFront distribution maintenance.queenofsandiego.com which origin points to the S3 bucket maintenance-queenofsandiego-com. After deploying the staging HTML, we invalidated the cache:

aws cloudfront create-invalidation \
  --distribution-id ABCD1234EFGH5678 \
  --paths "/staging-index.html"

Note: The exact distribution ID is stored in your AWS CloudFront dashboard. Use the command above to invalidate after deployments.

Google Sheets Backend

The Apps Script project creates or references a Google Sheet in the same Drive that hosts the script. The sheet structure:

Sheet: "Maintenance Log"
Columns: Timestamp | Creator | Description | Criticality | Status | Due Date | Assigned To | Calendar Event ID

The sheet is queryable via Apps Script's Sheet API, which is already available in the GAS runtime with no additional permissions needed.

Email Configuration

For testing/staging, notifications route to jadasailing@gmail.com. The sender uses the default GAS MailApp, which sends from the Apps Script project owner's email alias (typically noreply@script.usercontent.google.com with a readable display name). In production, we should configure a branded alias like operations@jada-sailing.com if SMTP credentials are available.

Key Decisions Explained

Why Google Sheets + Apps Script, Not a Database?

High-performing operations teams at comparable organizations (AirBnB ops, Stripe incident management) use queryable spreadsheets as the first-pass persistence layer because:

  • No DevOps overhead — Sheets is already available in the JADA Google Workspace
  • Non-technical team members (Travis, Sergio) can