Implement Supabase Edge Function Trigger Service

epic-push-notification-delivery-engine-task-009 — Create the NotificationTriggerService Supabase Edge Function that listens to database change events (INSERT on assignments, UPDATE on assignment_status, deadline proximity via scheduled cron) using Supabase realtime or pg_cron. Evaluates per-user per-category preferences from notification_preferences table before dispatching. Constructs role-aware FCM payloads with data fields for deep link route resolution and sends via FCM HTTP v1 API using a service account credential.

critical priority high complexity backend pending backend specialist Tier 2

Acceptance Criteria

A Supabase Edge Function named 'notification-trigger' is deployable via supabase functions deploy and handles POST requests with a typed TriggerPayload body

The function handles three trigger types: 'assignment_created' (INSERT on assignments), 'assignment_status_updated' (UPDATE on assignments.status), and 'deadline_approaching' (dispatched by pg_cron 24h before due_date)

Before sending any FCM message, the function queries notification_preferences table for the target user_id and trigger category; if the user has opted out, the function returns 200 with body { sent: false, reason: 'user_opted_out' }

FCM payloads include a 'data' object (not 'notification' object) with fields: route_type, resource_id, trigger_type, organization_id — this ensures compatibility with the Flutter deep link handler

Role-aware payload construction: coordinator-targeted messages include coordinator-specific route_type values; peer mentor-targeted messages use peer mentor routes

The function retrieves FCM tokens by querying user_devices table filtered by user_id and sends one FCM HTTP v1 API request per token (supports multi-device)

Stale/invalid FCM tokens (FCM returns 404 or 'UNREGISTERED') are automatically deleted from user_devices table after a failed send attempt

The pg_cron job 'deadline-notification-trigger' is defined in a migration file and calls the Edge Function via a net.http_post() to the function URL with the correct service_role key

All Edge Function errors (FCM API failure, DB query failure) are logged to Supabase logs with structured JSON including trigger_type, user_id (hashed), and error message

End-to-end test: inserting a row in assignments table results in an FCM message delivered to the test device within 10 seconds

Technical Requirements

frameworks

Supabase Edge Functions (Deno)

pg_cron

apis

FCM HTTP v1 API (https://fcm.googleapis.com/v1/projects/{project_id}/messages:send)

Google OAuth2 service account token exchange

Supabase PostgREST (from Edge Function using service_role key)

Supabase Database Webhooks or pg_cron net.http_post()

data models

assignments (id, assignee_user_id, status, due_date, organization_id)

notification_preferences (user_id, category, enabled)

user_devices (user_id, fcm_token, platform, device_id)

performance requirements

Edge Function cold start must complete within 3 seconds — keep dependencies minimal

FCM send requests must use Promise.all() for multi-device dispatch to parallelize sends

pg_cron deadline check query must use an index on assignments.due_date — verify index exists in migration

security requirements

Service account private key must be stored in Supabase secrets (supabase secrets set) and accessed via Deno.env.get() — never hardcoded

Edge Function must validate the incoming request has the correct Authorization header (service_role JWT) to prevent unauthorized triggers

FCM tokens must not be included in Edge Function logs — log only device_id or a hash

notification_preferences check is mandatory — sending notifications to opted-out users violates user trust and potentially GDPR

Execution Context

Execution Tier

Tier 2

Tier 2 - 518 tasks

Can start after Tier 1 completes

View Full Execution Plan

Implementation Notes

Use Supabase Database Webhooks (available in Supabase dashboard) to trigger the Edge Function on INSERT/UPDATE on the assignments table rather than pg_cron for event-driven triggers — pg_cron is only needed for the time-based deadline check. For FCM HTTP v1 authentication, use the google-auth-library Deno port or implement the JWT service account flow manually (sign a JWT with the private key, exchange for an access token at https://oauth2.googleapis.com/token). Cache the access token in a module-level variable and refresh only when it expires (typically 3600s) to avoid re-fetching on every invocation. Structure the Edge Function as: (1) validate request auth, (2) parse and validate TriggerPayload, (3) check notification_preferences, (4) fetch FCM tokens from user_devices, (5) build payload per role, (6) send via FCM v1, (7) handle stale token cleanup.

Keep each step in a separate function for testability. The 'data' object approach (no 'notification' object) is intentional — it gives full control of display to the Flutter app and avoids OS-level notification display bypassing flutter_local_notifications.

Testing Requirements

Integration tests using Supabase local dev environment (supabase start). Test cases: (1) assignment_created trigger with user opted-in — FCM send called with correct payload, (2) assignment_created trigger with user opted-out — FCM send NOT called, (3) assignment_status_updated — correct route_type in payload, (4) deadline_approaching via simulated cron — payload includes due_date context, (5) FCM returns UNREGISTERED — token deleted from user_devices, (6) multiple devices for one user — FCM send called once per token in parallel, (7) missing notification_preferences row — treat as opted-in (default allow), (8) malformed TriggerPayload — function returns 400. Manual end-to-end test on TestFlight device required before merging. Document cron schedule and service account rotation procedure.

Component

Notification Trigger Service

service high

Dependencies (2)

Create the NotificationPreferencesRepository data layer that reads and writes per-user per-category notification opt-in/opt-out preferences to Supabase. Include local cache with Riverpod provider, schema for category keys (new_assignment, deadline_approaching, status_change), and RLS policies ensuring users can only read/write their own preferences. epic-push-notification-delivery-engine-task-001 Create the FCMTokenManager infrastructure service that retrieves the device FCM registration token via firebase_messaging, detects token refresh events, persists the current token to Supabase user_devices table with device_id and platform fields, and deletes the token on logout. Include retry logic for network failures and token invalidation handling. epic-push-notification-delivery-engine-task-004

Epic Risks (4)

high impact high prob technical

Flutter's background message handler for FCM must run in a separate Dart isolate. Incorrect dependency initialization in the isolate (e.g., attempting to access Riverpod providers or Supabase before initialization) will cause silent crashes on Android when the app is terminated, resulting in missed notifications that are invisible in crash reporting.

Mitigation & Contingency

Mitigation: Use a minimal top-level background handler function annotated with @pragma('vm:entry-point') that only stores the raw RemoteMessage payload to a platform channel or shared preferences. Process the payload in the main isolate on next app launch. Write an explicit test for terminated-state message handling on Android.

Contingency: If isolate crashes are observed, implement a native Android FirebaseMessagingService subclass that handles background messages without Flutter isolate complexity, falling back to a database-insert-only approach for terminated-state notifications.

medium impact medium prob technical

Supabase Edge Functions can experience cold-start latency of 1–3 seconds after periods of inactivity. For high-frequency events like assignment creation, cumulative cold starts could cause dispatch delays exceeding the 30-second SLA, reducing the perceived reliability of the notification system.

Mitigation & Contingency

Mitigation: Configure the Edge Function with a keep-warm ping mechanism or use Supabase database webhooks that invoke the function directly on row insert to minimize cold-start frequency. Batch preference lookups within the function to reduce per-invocation Supabase round-trips.

Contingency: If latency SLA is consistently breached, move to a polling or Realtime-subscription architecture within the Edge Function, or pre-compute dispatch targets at preference-save time to eliminate per-dispatch preference queries.

high impact low prob security

If the deep link handler does not perform server-side role validation before rendering the target screen, a peer mentor who receives a mis-configured notification payload containing a coordinator-only route could access restricted data, violating the role-based access control invariants.

Mitigation & Contingency

Mitigation: The deep link handler must check the user's current role from the RoleStateManager before constructing the navigation route. Coordinator-only routes must be listed in a deny-list checked against the current role. The go_router route guard is a second line of defence.

Contingency: If a role bypass is discovered in testing, immediately add the affected route to the deep link handler deny-list and add a regression test. Audit all notification payload types for route targets that could expose cross-role data.

medium impact low prob dependency

FCM v1 HTTP API enforces per-project send quotas. For large organisations with many active peer mentors receiving simultaneous assignment notifications, batch dispatch events (e.g., bulk coordinator assignments) could approach quota limits and result in dropped notifications with 429 errors logged silently.

Mitigation & Contingency

Mitigation: Implement exponential backoff retry logic in the Edge Function for 429 responses. Design bulk assignment flows to dispatch notifications in batches with a configurable delay between batches. Monitor FCM console quotas during load testing.

Contingency: If quota limits are hit, implement a notification queue table in Supabase and a separate Edge Function that processes the queue with rate limiting, ensuring eventual delivery without exceeding FCM quotas.

Quick Links

All Tasks Execution Plan