Configure Supabase activity-save database webhook
epic-achievement-badges-evaluation-engine-task-009 — Configure the Supabase database webhook that fires on INSERT and UPDATE events to the activities table. Register the webhook endpoint pointing to the badge-criteria-edge-function URL, include the affected peer_mentor_id and org_id in the webhook payload, and set appropriate retry and timeout policies.
Acceptance Criteria
Technical Requirements
Implementation Notes
Supabase database webhooks are implemented via the `pg_net` extension and the `supabase_functions.http_request()` Postgres function. Define the webhook as a Postgres trigger in a migration file (`supabase/migrations/YYYYMMDD_badge_evaluation_webhook.sql`). The trigger should call `supabase_functions.http_request()` with: method='POST', url=`
Use `AFTER INSERT OR UPDATE ON activities FOR EACH ROW` trigger timing. For local dev safety, wrap the trigger creation in an environment check or simply document that `supabase db push` should only be run against non-local projects for this migration.
Testing Requirements
Manual verification steps: (1) Insert a test activity row for a known peer_mentor_id in staging — confirm edge function receives the webhook within 5 seconds by checking edge function logs in Supabase Dashboard, (2) Update an existing activity row — confirm webhook fires again, (3) Delete an activity row — confirm no webhook is fired, (4) Temporarily return HTTP 500 from the edge function and verify retry behaviour in pg_net logs (`select * from net._http_response`), (5) Verify the shared secret header is present in the received request inside the edge function. Document these verification steps in a runbook comment in the migration file.
Supabase Edge Functions may experience cold start latency of 500ms–2s when they have not been invoked recently. If evaluation latency consistently exceeds the 2-second UI expectation, the celebration overlay timing SLA cannot be met without the optimistic UI fallback from the UI epic.
Mitigation & Contingency
Mitigation: Keep the edge function warm by scheduling a lightweight health-check invocation every 5 minutes in production. Optimise the function size to minimise Deno module load time. Implement the optimistic UI path in badge-bloc (from the UI epic) as the primary UX path so cold start only affects server-side reconciliation, not perceived responsiveness.
Contingency: If cold starts remain problematic, migrate badge evaluation to a Supabase database function (pl/pgsql) triggered directly by a database trigger on activity insert, eliminating the Edge Function overhead entirely for the evaluation logic while keeping Edge Function only for FCM notification dispatch.
Supabase database webhooks can fail silently if the edge function returns a non-2xx response or times out. A missed webhook means a peer mentor does not receive a badge they earned, which is both a functional defect and a trust issue for organisations relying on milestone tracking.
Mitigation & Contingency
Mitigation: Implement idempotent webhook processing: the edge function reads the activity ID from the webhook payload and checks whether evaluation for this activity has already run (via an audit log query) before proceeding. Add Supabase webhook retry configuration (3 retries with exponential backoff). Monitor webhook failure rates via Supabase logs alert.
Contingency: Implement a nightly reconciliation job (Supabase scheduled function) that scans all activities from the past 24 hours, re-evaluates badge criteria for any peer mentor with no corresponding evaluation log entry, and awards any missing badges. Alert operations if reconciliation awards more than 5% of badges, indicating systematic webhook failure.
The evaluation service loads badge definitions per organisation, but a misconfigured RLS policy or incorrect organisation scoping in the edge function could cause one organisation's badge criteria to be evaluated against another organisation's peer mentor activity data, leading to incorrect or cross-contaminated badge awards.
Mitigation & Contingency
Mitigation: The edge function must extract organisation_id from the webhook payload activity record and pass it explicitly to every database query. Write a security test that seeds two organisations with distinct badge definitions and verifies that evaluating a peer mentor in org A never reads or awards org B definitions. Use Supabase service role key only within the edge function, never the anon key.
Contingency: If cross-org contamination is detected in audit logs, immediately disable the edge function webhook, run a targeted SQL query to identify and revoke incorrectly awarded badges, notify affected organisations, and perform a full security review of all RLS policies on badge-related tables before re-enabling.