Prayer Chain workflow
Moderate before routing.
- 1New request enters the Intake sheet as new or pending-review.
- 2Coordinator removes identifying details and chooses privacy, urgency, category, and team routing.
- 3Approved routed rows sync to Team Dashboard sheets by assignedTeamIds.
- 4Urgent rows create an immediate alert only after duplicate suppression checks.
- 5Ordinary rows are collected into same-day or daily digest sheets.
- 6Thanksgiving updates enter a separate review sheet before being shared with teams.
- 7Youth-safe and pastoral referral rows remain coordinator-only until reviewed.
Supabase schema
Tables for routed prayer support
| Table | Column | Type | Notes |
|---|---|---|---|
| prayer_chain_requests | id | uuid primary key | Internal request ID. |
| prayer_chain_requests | title | text not null | Coordinator-safe title. |
| prayer_chain_requests | request_summary | text not null | Moderated team-facing summary. |
| prayer_chain_requests | private_coordinator_note | text | Coordinator-only details. |
| prayer_chain_requests | request_category | text not null | Healing, grief, family, parish, youth, etc. |
| prayer_chain_requests | urgency | text not null | ordinary, time-sensitive, or urgent. |
| prayer_chain_requests | privacy | text not null | Controls who can see the request. |
| prayer_chain_requests | moderation_status | text not null | Coordinator workflow state. |
| prayer_chain_requests | requested_by_display | text | May be Anonymous. |
| prayer_chain_requests | requester_contact_encrypted | text | Admin-only encrypted contact. |
| prayer_chain_requests | assigned_team_ids | text[] not null | Routed team IDs. |
| prayer_chain_requests | alert_cadence | text not null | Immediate or digest mode. |
| prayer_chain_requests | prayed_count | integer default 0 | Aggregated team prayer commitments. |
| prayer_chain_requests | submitted_at | timestamptz not null | Original submission time. |
| prayer_chain_requests | routed_at | timestamptz | Set after moderation/routing. |
| prayer_chain_teams | id | text primary key | Team ID such as urgent or rosary. |
| prayer_chain_memberships | user_id | uuid references auth.users | Team member account. |
| prayer_chain_memberships | team_id | text references prayer_chain_teams | Membership team. |
| prayer_chain_commitments | request_id | uuid references prayer_chain_requests | One prayer click per member/request/team. |
| prayer_chain_alert_deliveries | dedupe_key | text unique | Suppress duplicate alerts. |
RLS policies
- Requesters can insert their own requests but cannot publish or route them.
- Requesters can read only a minimal confirmation for their own request when authenticated.
- Team members can read approved/routed requests assigned to their team.
- Team members cannot read requester contact fields or private coordinator notes.
- Coordinators can read and update moderation fields for requests in their responsibility.
- Youth-safe and pastoral-referral requests require coordinator role claims before any read access.
- Prayer commitments can be inserted once per user, request, and team.
- Alert delivery rows are writable only by server-side service role jobs.
Apps Script plan
- onFormSubmit: append request to Intake, normalize fields, and set moderationStatus to pending-review.
- routeRequest: apply the Daily Oratory routing table to suggest assignedTeamIds and alertCadence.
- dedupeAlert: build a key from requestId, teamId, alertType, and time window before sending.
- sendUrgentAlerts: email or message only trusted urgent coordinators after approval.
- sendDailyDigest: group approved requests by team and send a calm digest at a scheduled time.
- syncThanksgivings: collect approved thanksgiving updates and attach them to the original request.
- auditLog: write every moderation, routing, alert, and digest action to an immutable log sheet.