Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

```html

Automating Burial-at-Sea Compliance with Google Apps Script and Lambda Integration

What Was Built

A compliance automation system for burial-at-sea ceremonies that orchestrates post-ceremony workflows: captain data capture, certificate generation, regulatory filing drafting, and notifications. The system integrates with the existing booking system and eliminates manual compliance tracking.

The core workflow: ceremony occurs → captain submits GPS coordinates (via mobile capture page) → system generates GPS release certificate → system drafts §7117 verified statement (California requirement) → system stages EPA BASR record → notifications sent to family and operations team.

Architecture and Components

Google Apps Script Project (Main Engine)

  • ComplianceProcessor.gs — Core compliance orchestration. Handles logging, certificate generation, regulatory statement drafting, EPA batch record creation, and daily digest emails. Entry points: logCeremony(), generateGPSCertificate(), draftSection7117(), stageBASRRecord(), sendOperationalsDigest().
  • BookingAutomation.gs — Dispatch hooks integrated into the existing booking workflow. When a ceremony moves to "completed" status, onCeremonyCompletion() triggers the compliance pipeline. Passes booking ID and ceremony metadata to ComplianceProcessor.
  • TemplateRenderer.gs (inferred from architecture) — Renders Google Docs templates for certificates and regulatory statements. Takes booking data and template ID, returns generated document with family name, ceremony date, GPS coordinates populated.

Captain's Mobile Capture Page

A lightweight HTML form hosted via Google Apps Script's doGet() handler. URL pattern: https://script.google.com/macros/s/{DEPLOYMENT_ID}/userweb. Form captures:

  • Booking ID (pre-filled via query param)
  • GPS coordinates (manual entry or chartplotter image upload)
  • Ceremony timestamp (with timezone)
  • Photo/evidence attachment

Submits via POST to doPost() handler, which validates input and triggers ComplianceProcessor.logCeremony().

Google Docs Templates

  • GPS_Release_Certificate_Template — Family-facing document. Includes ceremony date, deceased name, scattering location, GPS coordinates, and JADA's marina address.
  • Section_7117_Verified_Statement_Template — California registrar filing. Legal boilerplate for burial-at-sea disposition verification. Must be filed with San Diego County Registrar within 10 days of scattering.

Templates use {PLACEHOLDER} syntax. TemplateRenderer clones the template, replaces placeholders, and returns the generated doc URL.

Lambda Integration Point

The captain's mobile page submits to a Lambda function (endpoint: likely API_GATEWAY_ENDPOINT/compliance/capture) as a fallback or for data validation. Lambda receives JSON payload (booking ID, GPS, timestamp), validates against booking system, and returns a webhook ACK to Apps Script. This decouples the front-end submission from Apps Script's execution limits.

Key Technical Decisions

Why Google Apps Script Instead of a Separate Service

The booking system already lives in Google Sheets + Apps Script. Adding compliance logic to the same environment eliminates cross-service integration friction, shares authentication (no additional API keys to rotate), and keeps ceremony data in one place. Apps Script's built-in email and Google Docs integration made template rendering trivial.

Why the Mobile Capture Page is So Simple

Captains operate from a vessel with spotty connectivity and don't have desktop access post-ceremony. A single-page HTML form with minimal dependencies (no React, no build step) loads instantly on 4G. Coordinates can be pasted from their chartplotter screenshot without requiring a photo upload (though photos are supported for audit trails).

Regulatory File Staging, Not Direct Submission

The EPA BASR and §7117 statements are staged in Google Sheets (a ComplianceLog sheet) rather than auto-submitted to county/EPA systems. Reason: both systems have different submission windows (§7117 within 10 days, EPA within 30 days) and require manual review. Staging allows batch review before submission and provides an audit trail of what was filed when.

Deployment and Integration

Pre-Deploy Checklist

  • Google Apps Script Project: Create new standalone project in Google Cloud Console (or add to existing JADA GCP project).
  • Script Properties: Store shared secret (used for captain form CSRF) and Lambda endpoint URL via Project Settings → Script Properties. Example: LAMBDA_COMPLIANCE_ENDPOINT = https://api.queenofsandiego.com/v1/compliance/capture
  • Google Docs: Create GPS certificate and §7117 statement templates in a shared JADA Google Drive folder. Record their document IDs in Script Properties (GPS_CERT_TEMPLATE_ID, SECTION_7117_TEMPLATE_ID).
  • Sheets: Add ComplianceLog and OperationalDigest sheets to the existing booking sheet workbook. ComplianceLog columns: BookingID, CeremonyDate, DeceasedName, GPSCoordinates, Section7117DocURL, BASRStagedDate, Status (pending/filed/verified).
  • clasp push: Deploy the .gs files from the local development environment to the Apps Script project.
  • Trigger Setup: Add time-based trigger for sendOperationalsDigest() to run daily at 8 AM PT via Apps Script UI.

Integration with Existing Booking System

In BookingAutomation.gs, the ceremony completion handler now calls:

ComplianceProcessor.onCeremonyCompletion({
  bookingId: booking.id,
  deceasedName: booking.deceased_name,
  ceremonyDate: booking.ceremony_date_iso,
  captainEmail: booking.captain_email
});

This queues the compliance workflow without blocking the booking UI. The captain receives an email (generated by sendCaptainCapturePrompt()) with a link to the mobile capture page, pre-populated with the booking ID.

What Happens Next

Post-deployment, legacy ceremonies (conducted before system go-live) must be manually logged via ComplianceProcessor.logCeremony(bookingId, manualGPS, ceremonyDate) to backfill the compliance log. The system then generates certificates and drafts §7117 statements for those ceremonies on demand.

Future enhancements: direct EPA BASR submission via API (currently staged for manual upload), SMS-based GPS submission for captains without email access, and automated reminders for missed §7117 filing deadlines.

```