Queen of San Diego — Tech Blog

Under the hood at queenofsandiego.com

```html

Building a Compliance Automation Layer for Burial Services: Integrating EPA/State Filing with Booking Systems

What Was Done

We built an end-to-end compliance automation system that transforms booking data into regulatory filing artifacts for burial-at-sea services in California. The system ingests booking events from Google Sheets, validates against EPA and §7117 (California Coastal Commission) requirements, generates the required documentation, and tracks filing deadlines automatically.

The implementation spans two Google Apps Script projects that live in the Queen of San Diego Google Workspace:

  • BookingAutomation.gs — event listener and booking state normalization
  • ComplianceProcessor.gs — EPA BASR (Burial At Sea Report) generation, Coastal Commission filing workflows, and deadline tracking

Technical Details

Architecture: Event-Driven Sheet Transformation

The core pattern is simple but load-bearing: a Google Sheets form submission (via the "Bookings" tab in the JADA Booking Requests sheet) triggers BookingAutomation.gs, which normalizes the raw form data into a stable schema and routes it to ComplianceProcessor.gs.

ComplianceProcessor.gs performs three sequential checks:

  • Date validation — memorial date must be ≥14 days from submission (EPA processing window)
  • Regulatory requirement mapping — each memorial maps to a compliance class:
    • Ceremonial burial → EPA BASR (due 30 days post-ceremony)
    • Coastal Commission waters → §7117 permit filing (due before ceremony, ~7-day review)
    • Multi-decedent ceremony → consolidated filing with Coastal Commission
  • Artifact generation — produces PDF forms from template and booking data, ready for county/state submission

Data Flow

Google Form (booking)
    ↓
Sheets (Bookings tab)
    ↓
BookingAutomation.gs trigger
    ↓
Normalize {date, location, decedent_name, memorial_type}
    ↓
ComplianceProcessor.gs
    ↓
Query compliance rules table (embedded)
    ↓
Generate EPA BASR or §7117 artifact
    ↓
Append to "Compliance Queue" sheet
    ↓
Create Calendar event (deadline reminder)
    ↓
Log to audit sheet (immutable record)

The compliance rules are stored as a data object within the script itself — not externalized to a sheet, to avoid accidental mutation:

const COMPLIANCE_RULES = {
  'burial_at_sea': {
    epa_form: 'EPA-BASR-2024',
    deadline_days: 30,
    scope: 'post_ceremony',
    requirement: 'Must submit within 30 days of ceremony completion'
  },
  'coastal_commission': {
    form: 'CCCC-§7117-Permit',
    deadline_days: 0,
    scope: 'pre_ceremony',
    requirement: 'Must file ≥7 days before ceremony for review'
  }
};

Infrastructure

The system operates entirely within Google Workspace with no external infrastructure dependencies, which was a deliberate choice to minimize operational surface area and keep data within a familiar, auditable boundary:

  • Google Sheets API — sheets.spreadsheets.values.batchUpdate() to append normalized bookings and compliance records
  • Google Calendar API — creates deadline reminders in a dedicated "Compliance Deadlines" calendar (shared with ops team)
  • Google Drive — generated PDFs are stored in a folder at `Google Drive/JADA/Compliance/[YYYY-MM]`, with a naming convention: `[Decedent]-[Memorial-Type]-[YYYYMMDD]`
  • Audit logging — immutable "Audit Log" sheet (protected from editing) records every compliance action: timestamp, user, booking_id, rule applied, artifact generated

No databases, no Lambda (yet), no external APIs beyond Google. This keeps the system verifiable, auditable, and maintainable by non-engineers via the Sheets UI.

Key Decisions

Why Google Apps Script, Not a Standalone Service

We chose GAS over building a dedicated Node.js Lambda service because:

  • Bookings live in Sheets — the single source of truth. A GAS script runs in the same auth context, eliminating credential management and API gateway complexity.
  • Deadline tracking lives in Calendar — GAS can write to Calendar directly; a standalone service would require webhook polling or external orchestration.
  • Audit trail and state visibility — appending to an Audit Log sheet means the ops team can inspect the system's decisions in real time, without dashboards or logs infrastructure.
  • No secrets rotation — GAS scripts deployed to a Workspace inherit the Workspace's OAuth context. No API keys to rotate, no .env files to manage.

The tradeoff: GAS has execution limits (6 min timeout per run), so we avoid long-running tasks. Multi-decedent ceremonies (where 5+ ashes are filed in one EPA submission) are queued and processed in a separate daily batch trigger, not inline.

Why Embed Compliance Rules, Not Externalize to Sheets

Tempting to make the COMPLIANCE_RULES table editable in a Sheets tab. We rejected this because:

  • A single accidental edit to the deadline_days field could silently cause missed filings.
  • Regulatory requirements are normative — they shouldn't drift. They change when California or EPA policy changes, not because of a booking quirk.
  • Embedding them in code keeps them under version control (GAS script history), auditable, and requires a deliberate code change and redeployment to modify.

Any future policy changes (e.g., EPA shortens BASR deadline from 30 to 20 days) require updating the script and logging the change. This creates a clear audit trail.

What's Next

The system is code-complete and ready for go-live, pending final verification of the Lambda integration for downstream artifact signing (not yet wired). Two immediate follow-ups:

  • EPA BASR integration (July 27 deadline) — Wire the Lambda function that fetches generated PDFs from Drive, signs them with the notarized certificate, and uploads to the EPA submission portal. This step is non-blocking for internal testing; we can manually submit PDFs until Lambda is live.
  • Coastal Commission §7117 batching (July 7 deadline) — For the pending Pearl Hwa Tan ceremony, consolidate her filing with any other concurrent memorials and batch-submit to the county. The script generates the artifacts; a human ops person currently handles submission. Post-deployment, we'll automate the submission step via a GAS `doPost()` handler.

Post-deployment, monitoring will focus on:

  • Audit log completeness — every booking must generate exactly one Audit entry
  • Calendar deadline accuracy — spot-check generated deadlines against regulatory docs
  • Artifact coverage — all compliant ceremonies must have a corresponding generated PDF, even if not yet signed

Source code lives at /Users/cb/Library/Mobile Documents/com~apple~CloudDocs/repos/sites/queenofsandiego.com/ (GAS files committed to Drive's version history; exported to Git on major releases).

```