Alerts & Digests — Build Reference

This module replicates the parts of HelloGM/Otelier you actually use:

Daily Snapshot email (replaces HelloGM's daily email)
SMS or email alerts when rules trigger (rate spikes, OTB shortfall, etc.)

Architecture

PMS auto-emails  →  /api/ingest/pms (Resend Inbound + Svix primary; Postmark + `INGEST_SECRET` deprecated until Module 20)  →  pms_ingest_log
                                                                        ↓
                                                    /api/ingest/parse (cron + session)
                                                                        ↓
                                                    daily_kpi / pace_snapshot / comp_set_rate / …
                                                                        ↓
                          ┌─────────────────────┬────────────────────┬─────────────────┐
                          ↓                     ↓                    ↓                 ↓
              /api/alerts/evaluate    /api/digest/send       /alerts UI         (future) Slack
                  (cron + on-demand)   /api/digest/cron       (rules + digest grid)
                          ↓                     ↓
                 Twilio SMS / Resend Email   Resend Email

Files

Path	Purpose
`supabase/migrations/003_alerts_and_digests.sql`	Schema: recipients, alert_rules, alert_events, digest_subscriptions, digest_sends, pms_ingest_log, daily_kpi, pace_snapshot, comp_set_rate
`src/lib/alerts/types.ts`	Type definitions
`src/lib/alerts/channels.ts`	Twilio (SMS) + Resend (email) + optional Web Push (VAPID; critical alerts only)
`src/lib/alerts/evaluator.ts`	Rule evaluator — reads subject tables, applies condition, fires
`src/lib/digests/daily-snapshot.ts`	Renders Daily Snapshot HTML email (replicates HelloGM layout)
`src/lib/digests/pickup-cancellation.ts`	Pickup & cancellation digest (comp set + pace deltas)
`src/lib/digests/portfolio-flash.ts`	Portfolio flash — both properties in one email
`src/lib/digests/labor-scorecard.ts`	Labor scorecard digest
`src/lib/digests/subscription-types.ts`	Allowed digest types, property/portfolio rules, cron fan-out vs placeholder lists
`src/components/cockpit/digest-subscription-grid.tsx`	Recipient × digest-type matrix; clock dialog for send hour / weekday
`supabase/migrations/014_digest_subscription_types_expand.sql`	Widens `digest_subscriptions.digest_type` CHECK (HelloGM parity names)
`src/app/api/alerts/rules/route.ts`	CRUD for alert_rules
`src/app/api/alerts/evaluate/route.ts`	Run all active rules (cron + manual)
`src/app/api/recipients/route.ts`	CRUD for recipients
`src/app/api/digest/send/route.ts`	Render & send one digest type (session or cron)
`src/app/api/digest/cron/route.ts`	Hourly Vercel Cron: fan out digests whose `send_hour_local` matches current hour in `America/New_York`, and `send_weekday_local` (0=Sun…6=Sat) when set
`src/lib/time/clock-hour-in-tz.ts`	NY wall-clock hour + weekday for digest scheduling
`src/lib/time/zoned-calendar.ts`	Civil-date helpers; last completed Mon–Sun week for weekly recap
`src/lib/digests/weekly-recap.ts`	Weekly recap email (rolled-up `daily_kpi` for last completed week)
`supabase/migrations/019_weekly_recap_weekday_and_client_rls.sql`	`send_weekday_local` on `digest_subscriptions`; explicit deny-all RLS policies for `anon` / `authenticated` on service tables
`src/lib/digests/execute-digest-send.ts`	Shared pipeline used by send + cron
`src/lib/digests/digest-send-dedupe.ts`	Optional recent-send check (cron: 25m window)
`src/app/api/digest/subscriptions/route.ts`	CRUD for `digest_subscriptions` (recipients × digest type × property); PATCH rejects stray `send_weekday_local` unless type is `weekly_recap`
`src/app/api/ingest/pms/route.ts`	Inbound PMS email webhook
`src/app/(cockpit)/cockpit/uploads/page.tsx`	Ingest health: queue counts, recent rows, filter by `business_date`
`src/app/api/ingest/log/route.ts`	GET ingest log + optional summary counts (`include_summary=1`)
`src/app/(cockpit)/alerts/page.tsx`	Alerts & digests admin UI
`src/components/cockpit/alerts-client.tsx`	UI client component (rules + recipients + digest grid + dry-run tools + recent fires)

Setup

Run the migration against your Supabase project:

supabase db push
# or paste the SQL into Supabase SQL Editor

Set env vars (copy from .env.example):
- RESEND_API_KEY, RESEND_FROM_EMAIL (or legacy RESEND_FROM) — for email sending; if both omitted, sends use Chavi <onboarding@resend.dev> until your domain is verified in Resend
- TWILIO_ACCOUNT_SID, TWILIO_AUTH_TOKEN, TWILIO_FROM — for SMS
- CRON_SECRET — required for scheduled /api/alerts/evaluate and /api/digest/cron
- RESEND_INBOUND_SECRET — Svix signing secret for Resend Inbound on /api/ingest/pms
- INGEST_SECRET — legacy Postmark path for /api/ingest/pms (deprecated)
Seed properties (one-time): insert lq-edgewood and qi-edgewood into properties table. The login page already references the slugs.
Add a recipient via /alerts UI — e.g. yourself with phone +14109008029 and email alex@bluegrass-management.com.
Pick a preset rule in the UI to seed your first alert rule. Or build one from scratch.
Test the rule: click "Evaluate now" in the UI. It runs every active rule against current data. Empty daily_kpi table = no fires.

Cron setup (Vercel)

Add to vercel.json:

{
  "crons": [
    { "path": "/api/alerts/evaluate", "schedule": "*/15 * * * *" },
    { "path": "/api/digest/cron",     "schedule": "0 * * * *"    }
  ]
}

Vercel Cron sends Authorization: Bearer <CRON_SECRET> when CRON_SECRET is set on the project; manual runs may use x-cron-secret. See docs/SECRETS.md and src/lib/cron/request.ts.

Inbound PMS email (webhook)

Primary: Resend Inbound posts email.received JSON to /api/ingest/pms with Svix headers (svix-id, svix-timestamp, svix-signature) verified using RESEND_INBOUND_SECRET (same signing model as Resend webhooks).

Deprecated (Module 9–19): Postmark-style JSON with header x-ingest-secret: <INGEST_SECRET> still works and logs a deprecation warning; removed in Module 20.

Configure:

LQ → Resend Inbound route → https://<your-domain>/api/ingest/pms (signed). Legacy: Postmark with x-ingest-secret: <INGEST_SECRET>.
Address mapping in pms-ingest-plan.ts / route:
- lq-pms@<domain> → property_slug lq-edgewood, source synxis
- qi-pms@<domain> → property_slug qi-edgewood, source choiceadvantage

Then in SynXis subscriptions, change the recipient from 52916-upload@hellogm.com to lq-pms@<your-domain>. Same for Choice — point the audit pack at qi-pms@<your-domain> (and keep alex@bluegrass-management.com on it for backup).

Parsing

pms_ingest_log rows land with parsed=false until /api/ingest/parse (cron or session) processes them.

Implemented (representative):

sphStatistics → daily_kpi (CSV + SynXis XML via sph-statistics-xml.ts)
sphForecast → pace_snapshot (source=forecast)
business_on_books → pace_snapshot (source=business_on_books)
pickup_report_html → comp_set_rate (HelloGM Pickup HTML; source=hello_gm_pickup_html)
audit_pack (Choice STANDARD AUDIT PACK PDF) → daily_kpi, tax_exempt, cancellation, transaction_closeout, etc. (process-audit-pack.ts, Azure Document Intelligence)
sphHotelLedger (XML) → ar_account_balance + pms_guest_balance (sph-hotel-ledger-xml.ts)
sphHotelAging / sphHotelAgingDetail (XML) → ar_aging (sph-hotel-aging-xml.ts)
sphTaxExempt (XML) → tax_exempt (sph-tax-exempt-xml.ts)
sphRevenueRecap (XML; Tablix sample) → revenue_by_rate_plan (sph-revenue-recap-xml.ts — extend when full SSRS XML export is available)

Still evolving:

Additional SynXis / OTA / ledger report types from docs/CURSOR_BUILD_PROMPT.md and real inbox samples.

Ground-truth column names and sections: hellogm-innrly-reverse-engineering.md

Quick alert recipes

"Wake me up if comp set raises rate >$15"

Subject: comp_set_rate
Condition: { "metric": "delta_vs_prior", "op": ">", "value": 15 }
Channels: SMS

"Daily digest at 6am"

Add a digest_subscription row with digest_type="daily_snapshot", property_slug="lq-edgewood", your recipient_id

"Yelp/Google review-bomb test rule" (manual subject)

Subject: manual
Won't fire from the cron — only from the "Test fire" button in the UI

Limits / known gaps

rate_variance rules: prefer v_latest_daily_kpi when metric (default lowrates_count) has numeric today / current rows; otherwise fall back to legacy alerts rows with title LowRates% (24h). See docs/PRODUCT_GAPS_BUNDLED.md Bundle A. Ingest: Choice STANDARD AUDIT PACK PDFs with a rate_discrepancy section contribute lowrates_count (heuristic row count) into daily_kpi for that business date.
ar_balance rules: read ar_account_balance (latest as_of_date); require metric: "balance" and account_name_like. Wyndham preset works when ledger ingest is present.
Historical pace depth: same-time-LY and pickup-trail rules need sufficient pace_snapshot history (business_on_books, forecast, etc.); sparse LY rows → rule may return “insufficient history.”
Digest placeholders: types beyond the five in DIGEST_TYPES_CRON_FANOUT subscribe in UI but use placeholder HTML until each renderer is promoted (dry-run via /api/digest/send on /alerts). Placeholder emails include a small footer (digest key, property, UTC time, optional /alerts link when VERCEL_URL is set — see docs/SECRETS.md).

Alerts & morning email