# Phase 2: Supabase Edge Functions — Implementation Plan
> **For agentic workers:** REQUIRED: Use superpowers:subagent-driven-development (if subagents available) or superpowers:executing-plans to implement this plan. Steps use checkbox (`- [ ]`) syntax for tracking.
**Goal:** Implement 6 Edge Functions that replace the Node.js microservice business logic: notifications, RSVP processing, recommendations, matchmaking, content moderation, and connection depth calculation.
**Architecture:** Supabase Edge Functions (Deno/TypeScript). Each function is self-contained in `supabase/functions/<name>/index.ts`. Functions are triggered by database webhooks (INSERT on tables) or called on-demand via `supabase.functions.invoke()`. The `send-notification` function uses Firebase Admin SDK for push delivery.
**Tech Stack:** Deno, TypeScript, Supabase JS Client (service_role), Firebase Admin SDK
**Spec:** `flow_docs/docs/superpowers/specs/2026-03-29-supabase-only-migration-design.md`
**Depends on:** Phase 1 (schema additions) must be completed first.
---
## File Structure
| Action | File | Purpose |
|--------|------|---------|
| Create | `flow_backend/supabase/functions/_shared/supabase-client.ts` | Shared Supabase service_role client |
| Create | `flow_backend/supabase/functions/_shared/cors.ts` | Shared CORS headers |
| Create | `flow_backend/supabase/functions/send-notification/index.ts` | Push/email notification delivery |
| Create | `flow_backend/supabase/functions/process-rsvp/index.ts` | RSVP XP award + organizer notification |
| Create | `flow_backend/supabase/functions/compute-recommendations/index.ts` | Scheduled recommendation scoring |
| Create | `flow_backend/supabase/functions/compute-matchmaking/index.ts` | On-demand crew matchmaking |
| Create | `flow_backend/supabase/functions/moderate-content/index.ts` | Async content moderation |
| Create | `flow_backend/supabase/functions/compute-connection-depth/index.ts` | Friendship tier calculation |
---
## Chunk 1: Shared Utilities & send-notification
### Task 1: Create shared utilities
**Files:**
- Create: `flow_backend/supabase/functions/_shared/supabase-client.ts`
- Create: `flow_backend/supabase/functions/_shared/cors.ts`
- [ ] **Step 1: Create the _shared directory and supabase client**
```typescript
// supabase/functions/_shared/supabase-client.ts
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";
export const supabaseAdmin = createClient(
Deno.env.get("SUPABASE_URL")!,
Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
);
```
- [ ] **Step 2: Create CORS headers**
```typescript
// supabase/functions/_shared/cors.ts
export const corsHeaders = {
"Access-Control-Allow-Origin": "*",
"Access-Control-Allow-Headers":
"authorization, x-client-info, apikey, content-type",
};
```
- [ ] **Step 3: Commit shared utilities**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
git add supabase/functions/_shared/
git commit -m "feat(edge-fn): add shared Supabase client and CORS utilities
service_role client for privileged operations in Edge Functions.
CORS headers for cross-origin requests from mobile/web."
```
### Task 2: Implement send-notification
**Files:**
- Create: `flow_backend/supabase/functions/send-notification/index.ts`
- [ ] **Step 1: Create send-notification Edge Function**
```typescript
// supabase/functions/send-notification/index.ts
//
// Triggered by: Database webhook on INSERT into notifications table
// Purpose: Delivers push notifications via FCM and optional email via Resend
// Checks user preferences (quiet hours, disabled types) before sending
import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { supabaseAdmin } from "../_shared/supabase-client.ts";
import { corsHeaders } from "../_shared/cors.ts";
interface NotificationPayload {
record: {
id: string;
user_id: string;
type: string;
title: string;
body: string;
data: Record<string, unknown>;
};
}
serve(async (req: Request) => {
// Handle CORS preflight
if (req.method === "OPTIONS") {
return new Response("ok", { headers: corsHeaders });
}
try {
const payload: NotificationPayload = await req.json();
const { record } = payload;
const { user_id, type, title, body, data } = record;
// 1. Check user notification preferences
const { data: prefs } = await supabaseAdmin
.from("notification_preferences")
.select("*")
.eq("user_id", user_id)
.maybeSingle();
// If no preferences row, use defaults (all enabled)
const pushEnabled = prefs?.push_enabled ?? true;
const disabledTypes: string[] = prefs?.disabled_types ?? [];
// Skip if user disabled this notification type
if (disabledTypes.includes(type)) {
return new Response(JSON.stringify({ skipped: true, reason: "type_disabled" }), {
headers: { ...corsHeaders, "Content-Type": "application/json" },
});
}
// Check quiet hours
if (prefs?.quiet_hours_start && prefs?.quiet_hours_end) {
const now = new Date();
const currentTime = `${now.getHours().toString().padStart(2, "0")}:${now.getMinutes().toString().padStart(2, "0")}`;
const start = prefs.quiet_hours_start;
const end = prefs.quiet_hours_end;
const inQuietHours = start <= end
? currentTime >= start && currentTime <= end
: currentTime >= start || currentTime <= end; // overnight range
if (inQuietHours) {
return new Response(JSON.stringify({ skipped: true, reason: "quiet_hours" }), {
headers: { ...corsHeaders, "Content-Type": "application/json" },
});
}
}
// 2. Send push notification via FCM (if enabled)
let pushResult = null;
if (pushEnabled) {
const { data: tokens } = await supabaseAdmin
.from("device_tokens")
.select("token, platform")
.eq("user_id", user_id);
if (tokens && tokens.length > 0) {
const fcmKey = Deno.env.get("FCM_SERVER_KEY");
if (fcmKey) {
// Send to all user devices
const fcmPayload = {
registration_ids: tokens.map((t: { token: string }) => t.token),
notification: { title, body },
data: { ...data, notification_id: record.id, type },
};
const fcmResponse = await fetch(
"https://fcm.googleapis.com/fcm/send",
{
method: "POST",
headers: {
"Content-Type": "application/json",
Authorization: `key=${fcmKey}`,
},
body: JSON.stringify(fcmPayload),
}
);
pushResult = await fcmResponse.json();
}
}
}
// 3. Send email for critical notification types
const emailEnabled = prefs?.email_enabled ?? true;
const criticalTypes = ["security", "account", "payment"];
let emailResult = null;
if (emailEnabled && criticalTypes.includes(type)) {
const resendKey = Deno.env.get("RESEND_API_KEY");
if (resendKey) {
const { data: profile } = await supabaseAdmin
.from("profiles")
.select("email")
.eq("id", user_id)
.single();
if (profile?.email) {
const emailResponse = await fetch("https://api.resend.com/emails", {
method: "POST",
headers: {
"Content-Type": "application/json",
Authorization: `Bearer ${resendKey}`,
},
body: JSON.stringify({
from: "Flow <noreply@flowapp.it>",
to: profile.email,
subject: title,
text: body,
}),
});
emailResult = await emailResponse.json();
}
}
}
return new Response(
JSON.stringify({ success: true, push: pushResult, email: emailResult }),
{ headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
} catch (error) {
return new Response(
JSON.stringify({ error: error.message }),
{ status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
}
});
```
- [ ] **Step 2: Commit**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
git add supabase/functions/send-notification/
git commit -m "feat(edge-fn): implement send-notification Edge Function
Delivers push via FCM and email via Resend for critical types.
Respects user preferences: disabled types, quiet hours.
Triggered by database webhook on notifications INSERT."
```
---
## Chunk 2: process-rsvp & compute-recommendations
### Task 3: Implement process-rsvp
**Files:**
- Create: `flow_backend/supabase/functions/process-rsvp/index.ts`
- [ ] **Step 1: Create process-rsvp Edge Function**
```typescript
// supabase/functions/process-rsvp/index.ts
//
// Triggered by: Database webhook on INSERT/UPDATE into event_attendees
// Purpose: Award XP, notify organizer, invalidate recommendation cache
import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { supabaseAdmin } from "../_shared/supabase-client.ts";
import { corsHeaders } from "../_shared/cors.ts";
interface AttendeePayload {
type: "INSERT" | "UPDATE";
record: {
id: string;
event_id: string;
user_id: string;
status: string;
};
old_record?: {
status: string;
};
}
serve(async (req: Request) => {
if (req.method === "OPTIONS") {
return new Response("ok", { headers: corsHeaders });
}
try {
const payload: AttendeePayload = await req.json();
const { record, type: changeType, old_record } = payload;
const { event_id, user_id, status } = record;
// Only process new RSVPs (going or interested)
const isNewRsvp = changeType === "INSERT" && (status === "going" || status === "interested");
const isStatusChange = changeType === "UPDATE" && old_record?.status !== status;
if (!isNewRsvp && !isStatusChange) {
return new Response(JSON.stringify({ skipped: true }), {
headers: { ...corsHeaders, "Content-Type": "application/json" },
});
}
// 1. Award XP for RSVP
if (status === "going" || status === "interested") {
// Check if this is user's first-ever RSVP
const { count } = await supabaseAdmin
.from("event_attendees")
.select("*", { count: "exact", head: true })
.eq("user_id", user_id);
const xpAmount = (count ?? 0) <= 1 ? 50 : 20; // First RSVP = 50 XP, subsequent = 20 XP
await supabaseAdmin.rpc("increment_xp", {
p_user_id: user_id,
p_amount: xpAmount,
});
}
// 2. Notify event organizer
const { data: event } = await supabaseAdmin
.from("events")
.select("organizer_id, title")
.eq("id", event_id)
.single();
if (event && event.organizer_id !== user_id) {
const { data: user } = await supabaseAdmin
.from("profiles")
.select("username, first_name")
.eq("id", user_id)
.single();
const userName = user?.first_name || user?.username || "Someone";
await supabaseAdmin.from("notifications").insert({
user_id: event.organizer_id,
type: "event_rsvp",
title: "New RSVP",
body: `${userName} is ${status} for "${event.title}"`,
data: { event_id, attendee_id: user_id, status },
});
}
// 3. Invalidate recommendation cache for this user
await supabaseAdmin
.from("recommendations_cache")
.delete()
.eq("user_id", user_id);
return new Response(
JSON.stringify({ success: true }),
{ headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
} catch (error) {
return new Response(
JSON.stringify({ error: error.message }),
{ status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
}
});
```
- [ ] **Step 2: Commit**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
git add supabase/functions/process-rsvp/
git commit -m "feat(edge-fn): implement process-rsvp Edge Function
Awards XP (50 for first RSVP, 20 for subsequent).
Notifies event organizer. Invalidates recommendation cache.
Triggered by event_attendees INSERT/UPDATE webhook."
```
### Task 4: Implement compute-recommendations
**Files:**
- Create: `flow_backend/supabase/functions/compute-recommendations/index.ts`
- [ ] **Step 1: Create compute-recommendations Edge Function**
```typescript
// supabase/functions/compute-recommendations/index.ts
//
// Triggered by: Scheduled cron (every 6 hours) or on-demand
// Purpose: Compute personalized event recommendations per user using SQL scoring
// Scoring: distance * 0.3 + category_match * 0.3 + friends_attending * 0.4
import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { supabaseAdmin } from "../_shared/supabase-client.ts";
import { corsHeaders } from "../_shared/cors.ts";
serve(async (req: Request) => {
if (req.method === "OPTIONS") {
return new Response("ok", { headers: corsHeaders });
}
try {
// Get active users (last_active within 30 days)
const thirtyDaysAgo = new Date();
thirtyDaysAgo.setDate(thirtyDaysAgo.getDate() - 30);
const { data: activeUsers } = await supabaseAdmin
.from("profiles")
.select("id, latitude, longitude, interests")
.gte("last_active", thirtyDaysAgo.toISOString())
.not("latitude", "is", null)
.not("longitude", "is", null);
if (!activeUsers || activeUsers.length === 0) {
return new Response(JSON.stringify({ processed: 0 }), {
headers: { ...corsHeaders, "Content-Type": "application/json" },
});
}
// Get upcoming events
const { data: events } = await supabaseAdmin
.from("events")
.select("id, title, category, location, geo_point, start_date, organizer_id")
.gte("start_date", new Date().toISOString())
.eq("status", "active")
.limit(200);
if (!events || events.length === 0) {
return new Response(JSON.stringify({ processed: 0, reason: "no_events" }), {
headers: { ...corsHeaders, "Content-Type": "application/json" },
});
}
let totalRecommendations = 0;
for (const user of activeUsers) {
// Get user's friend IDs
const { data: connections } = await supabaseAdmin
.from("connections")
.select("user_id, friend_id")
.or(`user_id.eq.${user.id},friend_id.eq.${user.id}`)
.eq("status", "accepted");
const friendIds = (connections ?? []).map((c: { user_id: string; friend_id: string }) =>
c.user_id === user.id ? c.friend_id : c.user_id
);
// Get friends' attendance
const { data: friendAttendance } = friendIds.length > 0
? await supabaseAdmin
.from("event_attendees")
.select("event_id")
.in("user_id", friendIds)
.in("status", ["going", "interested"])
: { data: [] };
const friendEventIds = new Set((friendAttendance ?? []).map((a: { event_id: string }) => a.event_id));
// Get user's already-attending events (to exclude)
const { data: userAttendance } = await supabaseAdmin
.from("event_attendees")
.select("event_id")
.eq("user_id", user.id);
const attendingEventIds = new Set((userAttendance ?? []).map((a: { event_id: string }) => a.event_id));
const userInterests: string[] = user.interests ?? [];
const recommendations: Array<{ user_id: string; event_id: string; score: number; factors: Record<string, number> }> = [];
for (const event of events) {
// Skip events user already RSVPed to or organized
if (attendingEventIds.has(event.id) || event.organizer_id === user.id) continue;
// Distance score (0-1, closer = higher)
const eventLoc = event.location as { latitude?: number; longitude?: number } | null;
const eventLat = eventLoc?.latitude ?? 0;
const eventLng = eventLoc?.longitude ?? 0;
const distKm = haversineDistance(user.latitude, user.longitude, eventLat, eventLng);
const distanceScore = Math.max(0, 1 - distKm / 50); // 50km = score 0
// Category match score (0-1)
const eventCategory = event.category ?? "";
const categoryScore = userInterests.includes(eventCategory) ? 1.0 : 0.2;
// Friends attending score (0-1)
const friendsScore = friendEventIds.has(event.id) ? 1.0 : 0.0;
// Weighted final score
const score = distanceScore * 0.3 + categoryScore * 0.3 + friendsScore * 0.4;
if (score > 0.1) {
recommendations.push({
user_id: user.id,
event_id: event.id,
score: Math.round(score * 10000) / 10000,
factors: {
distance: Math.round(distanceScore * 100) / 100,
category: Math.round(categoryScore * 100) / 100,
friends: Math.round(friendsScore * 100) / 100,
},
});
}
}
// Sort by score, take top 50
recommendations.sort((a, b) => b.score - a.score);
const top50 = recommendations.slice(0, 50);
if (top50.length > 0) {
// Clear old recommendations for this user
await supabaseAdmin
.from("recommendations_cache")
.delete()
.eq("user_id", user.id);
// Insert new recommendations
await supabaseAdmin
.from("recommendations_cache")
.insert(top50.map((r) => ({ ...r, computed_at: new Date().toISOString() })));
totalRecommendations += top50.length;
}
}
return new Response(
JSON.stringify({
success: true,
users_processed: activeUsers.length,
recommendations_created: totalRecommendations,
}),
{ headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
} catch (error) {
return new Response(
JSON.stringify({ error: error.message }),
{ status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
}
});
// Haversine formula for distance in km
function haversineDistance(lat1: number, lon1: number, lat2: number, lon2: number): number {
const R = 6371;
const dLat = (lat2 - lat1) * Math.PI / 180;
const dLon = (lon2 - lon1) * Math.PI / 180;
const a =
Math.sin(dLat / 2) * Math.sin(dLat / 2) +
Math.cos(lat1 * Math.PI / 180) * Math.cos(lat2 * Math.PI / 180) *
Math.sin(dLon / 2) * Math.sin(dLon / 2);
const c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));
return R * c;
}
```
- [ ] **Step 2: Commit**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
git add supabase/functions/compute-recommendations/
git commit -m "feat(edge-fn): implement compute-recommendations Edge Function
SQL-based scoring: distance (0.3) + category match (0.3) + friends attending (0.4).
Processes active users, computes top 50 recommendations each.
Scheduled via cron every 6 hours."
```
---
## Chunk 3: compute-matchmaking & moderate-content
### Task 5: Implement compute-matchmaking
**Files:**
- Create: `flow_backend/supabase/functions/compute-matchmaking/index.ts`
- [ ] **Step 1: Create compute-matchmaking Edge Function**
```typescript
// supabase/functions/compute-matchmaking/index.ts
//
// Triggered by: On-demand from mobile app (crew matchmaking wizard)
// Purpose: Score potential crew matches based on interests, event overlap, mutual friends
// Scoring: shared_interests * 0.4 + event_overlap * 0.3 + mutual_friends * 0.3
import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { supabaseAdmin } from "../_shared/supabase-client.ts";
import { corsHeaders } from "../_shared/cors.ts";
serve(async (req: Request) => {
if (req.method === "OPTIONS") {
return new Response("ok", { headers: corsHeaders });
}
try {
const { user_id } = await req.json();
if (!user_id) {
return new Response(JSON.stringify({ error: "user_id required" }), {
status: 400,
headers: { ...corsHeaders, "Content-Type": "application/json" },
});
}
// Get current user's profile
const { data: currentUser } = await supabaseAdmin
.from("profiles")
.select("id, interests, latitude, longitude")
.eq("id", user_id)
.single();
if (!currentUser) {
return new Response(JSON.stringify({ error: "user not found" }), {
status: 404,
headers: { ...corsHeaders, "Content-Type": "application/json" },
});
}
// Get user's event attendance history
const { data: userEvents } = await supabaseAdmin
.from("event_attendees")
.select("event_id")
.eq("user_id", user_id)
.in("status", ["going", "interested"]);
const userEventIds = new Set((userEvents ?? []).map((e: { event_id: string }) => e.event_id));
// Get user's existing connections (to exclude from matches)
const { data: existingConnections } = await supabaseAdmin
.from("connections")
.select("user_id, friend_id")
.or(`user_id.eq.${user_id},friend_id.eq.${user_id}`);
const connectedIds = new Set(
(existingConnections ?? []).map((c: { user_id: string; friend_id: string }) =>
c.user_id === user_id ? c.friend_id : c.user_id
)
);
// Get user's blocked users
const { data: blocks } = await supabaseAdmin
.from("user_blocks")
.select("blocked_id")
.eq("blocker_id", user_id);
const blockedIds = new Set((blocks ?? []).map((b: { blocked_id: string }) => b.blocked_id));
// Get user's friends' IDs (for mutual friend scoring)
const acceptedFriends = (existingConnections ?? [])
.filter((c: { user_id: string; friend_id: string }) => true) // all connections
.map((c: { user_id: string; friend_id: string }) =>
c.user_id === user_id ? c.friend_id : c.user_id
);
const friendIdSet = new Set(acceptedFriends);
// Get candidate users (nearby, active)
const { data: candidates } = await supabaseAdmin
.from("profiles")
.select("id, interests, latitude, longitude")
.neq("id", user_id)
.eq("status", "active")
.not("latitude", "is", null)
.limit(500);
const matches: Array<{
user_id: string;
score: number;
factors: { interests: number; events: number; friends: number };
}> = [];
const userInterests: string[] = currentUser.interests ?? [];
for (const candidate of candidates ?? []) {
// Skip connected and blocked users
if (connectedIds.has(candidate.id) || blockedIds.has(candidate.id)) continue;
// 1. Shared interests score (0-1)
const candidateInterests: string[] = candidate.interests ?? [];
const shared = userInterests.filter((i: string) => candidateInterests.includes(i));
const interestScore = userInterests.length > 0
? shared.length / Math.max(userInterests.length, candidateInterests.length)
: 0;
// 2. Event attendance overlap score (0-1)
const { data: candidateEvents } = await supabaseAdmin
.from("event_attendees")
.select("event_id")
.eq("user_id", candidate.id)
.in("status", ["going", "interested"]);
const candidateEventIds = (candidateEvents ?? []).map((e: { event_id: string }) => e.event_id);
const eventOverlap = candidateEventIds.filter((id: string) => userEventIds.has(id)).length;
const eventScore = userEventIds.size > 0
? Math.min(1, eventOverlap / 3) // 3+ shared events = max score
: 0;
// 3. Mutual friends score (0-1)
const { data: candidateConnections } = await supabaseAdmin
.from("connections")
.select("user_id, friend_id")
.or(`user_id.eq.${candidate.id},friend_id.eq.${candidate.id}`)
.eq("status", "accepted");
const candidateFriends = (candidateConnections ?? []).map(
(c: { user_id: string; friend_id: string }) =>
c.user_id === candidate.id ? c.friend_id : c.user_id
);
const mutualFriends = candidateFriends.filter((id: string) => friendIdSet.has(id)).length;
const friendScore = Math.min(1, mutualFriends / 5); // 5+ mutual = max score
// Weighted final score
const score = interestScore * 0.4 + eventScore * 0.3 + friendScore * 0.3;
if (score > 0.05) {
matches.push({
user_id: candidate.id,
score: Math.round(score * 1000) / 1000,
factors: {
interests: Math.round(interestScore * 100) / 100,
events: Math.round(eventScore * 100) / 100,
friends: Math.round(friendScore * 100) / 100,
},
});
}
}
// Sort by score, return top 20
matches.sort((a, b) => b.score - a.score);
const top20 = matches.slice(0, 20);
// Fetch profile data for matched users
const matchedUserIds = top20.map((m) => m.user_id);
const { data: matchedProfiles } = matchedUserIds.length > 0
? await supabaseAdmin
.from("profiles")
.select("id, username, first_name, last_name, profile_picture, interests, bio")
.in("id", matchedUserIds)
: { data: [] };
const profileMap = new Map(
(matchedProfiles ?? []).map((p: Record<string, unknown>) => [p.id, p])
);
const results = top20.map((match) => ({
...match,
profile: profileMap.get(match.user_id) ?? null,
}));
return new Response(
JSON.stringify({ matches: results }),
{ headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
} catch (error) {
return new Response(
JSON.stringify({ error: error.message }),
{ status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
}
});
```
- [ ] **Step 2: Commit**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
git add supabase/functions/compute-matchmaking/
git commit -m "feat(edge-fn): implement compute-matchmaking Edge Function
Scores users by: shared interests (40%), event overlap (30%), mutual friends (30%).
Excludes connected/blocked users. Returns top 20 matches with profiles.
Called on-demand from mobile crew matchmaking wizard."
```
### Task 6: Implement moderate-content
**Files:**
- Create: `flow_backend/supabase/functions/moderate-content/index.ts`
- [ ] **Step 1: Create moderate-content Edge Function**
```typescript
// supabase/functions/moderate-content/index.ts
//
// Triggered by: Database webhook on INSERT into messages and comments
// Purpose: Async keyword filtering — message is already delivered, this flags if suspicious
// Does NOT block or delay message delivery
import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { supabaseAdmin } from "../_shared/supabase-client.ts";
import { corsHeaders } from "../_shared/cors.ts";
// Basic blocklist — extend via app_config table
const DEFAULT_BLOCKLIST = [
"spam", "phishing", "scam",
];
serve(async (req: Request) => {
if (req.method === "OPTIONS") {
return new Response("ok", { headers: corsHeaders });
}
try {
const payload = await req.json();
const { record, table } = payload;
const content = record.content ?? "";
const authorId = record.sender_id ?? record.user_id;
if (!content || !authorId) {
return new Response(JSON.stringify({ skipped: true }), {
headers: { ...corsHeaders, "Content-Type": "application/json" },
});
}
// Load custom blocklist from app_config (if exists)
const { data: configRow } = await supabaseAdmin
.from("app_config")
.select("value")
.eq("key", "content_blocklist")
.maybeSingle();
const customBlocklist: string[] = configRow?.value
? JSON.parse(configRow.value)
: [];
const blocklist = [...DEFAULT_BLOCKLIST, ...customBlocklist];
// Check content against blocklist (case-insensitive)
const contentLower = content.toLowerCase();
const flaggedTerms = blocklist.filter((term: string) =>
contentLower.includes(term.toLowerCase())
);
if (flaggedTerms.length > 0) {
// Flag content by creating a user_report
await supabaseAdmin.from("user_reports").insert({
reporter_id: authorId, // self-reported via system
reported_id: authorId,
reason: "auto_moderation",
description: `Auto-flagged ${table} content. Terms: ${flaggedTerms.join(", ")}. Content ID: ${record.id}`,
status: "pending",
});
return new Response(
JSON.stringify({ flagged: true, terms: flaggedTerms }),
{ headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
}
return new Response(
JSON.stringify({ flagged: false }),
{ headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
} catch (error) {
return new Response(
JSON.stringify({ error: error.message }),
{ status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
}
});
```
- [ ] **Step 2: Commit**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
git add supabase/functions/moderate-content/
git commit -m "feat(edge-fn): implement moderate-content Edge Function
Async keyword filtering — does NOT block message delivery.
Uses default + configurable blocklist (app_config.content_blocklist).
Flags suspicious content via user_reports for moderator review."
```
---
## Chunk 4: compute-connection-depth & Deployment
### Task 7: Implement compute-connection-depth
**Files:**
- Create: `flow_backend/supabase/functions/compute-connection-depth/index.ts`
- [ ] **Step 1: Create compute-connection-depth Edge Function**
```typescript
// supabase/functions/compute-connection-depth/index.ts
//
// Triggered by: Database webhook on INSERT into event_attendees (after check-in)
// Purpose: Update connection_depth between friends who attended the same event
// Depth: 0=acquaintance, 1=friend (1-2 shared), 2=buddy (3-5), 3=crew (6+)
import { serve } from "https://deno.land/std@0.177.0/http/server.ts";
import { supabaseAdmin } from "../_shared/supabase-client.ts";
import { corsHeaders } from "../_shared/cors.ts";
function depthFromSharedEvents(count: number): number {
if (count >= 6) return 3; // crew
if (count >= 3) return 2; // buddy
if (count >= 1) return 1; // friend
return 0; // acquaintance
}
serve(async (req: Request) => {
if (req.method === "OPTIONS") {
return new Response("ok", { headers: corsHeaders });
}
try {
const payload = await req.json();
const { record } = payload;
const { event_id, user_id } = record;
// Get all other attendees of this event who are connected to this user
const { data: connections } = await supabaseAdmin
.from("connections")
.select("id, user_id, friend_id, connection_depth")
.or(`user_id.eq.${user_id},friend_id.eq.${user_id}`)
.eq("status", "accepted");
if (!connections || connections.length === 0) {
return new Response(JSON.stringify({ updated: 0 }), {
headers: { ...corsHeaders, "Content-Type": "application/json" },
});
}
let updated = 0;
for (const conn of connections) {
const friendId = conn.user_id === user_id ? conn.friend_id : conn.user_id;
// Count shared events between the two users
const { data: userEvents } = await supabaseAdmin
.from("event_attendees")
.select("event_id")
.eq("user_id", user_id)
.in("status", ["going"]);
const userEventIds = (userEvents ?? []).map((e: { event_id: string }) => e.event_id);
const { count: sharedCount } = await supabaseAdmin
.from("event_attendees")
.select("*", { count: "exact", head: true })
.eq("user_id", friendId)
.in("event_id", userEventIds.length > 0 ? userEventIds : ["__none__"])
.eq("status", "going");
const newDepth = depthFromSharedEvents(sharedCount ?? 0);
// Only update if depth changed
if (newDepth !== conn.connection_depth) {
await supabaseAdmin
.from("connections")
.update({ connection_depth: newDepth })
.eq("id", conn.id);
updated++;
}
}
return new Response(
JSON.stringify({ success: true, connections_checked: connections.length, updated }),
{ headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
} catch (error) {
return new Response(
JSON.stringify({ error: error.message }),
{ status: 500, headers: { ...corsHeaders, "Content-Type": "application/json" } }
);
}
});
```
- [ ] **Step 2: Commit**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
git add supabase/functions/compute-connection-depth/
git commit -m "feat(edge-fn): implement compute-connection-depth Edge Function
Updates friendship depth based on shared event attendance.
0=acquaintance, 1=friend (1-2), 2=buddy (3-5), 3=crew (6+).
Triggered after event check-in via event_attendees webhook."
```
### Task 8: Deploy Edge Functions
- [ ] **Step 1: Verify Supabase CLI and project link**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
npx supabase functions list
```
- [ ] **Step 2: Deploy all Edge Functions**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
npx supabase functions deploy send-notification
npx supabase functions deploy process-rsvp
npx supabase functions deploy compute-recommendations
npx supabase functions deploy compute-matchmaking
npx supabase functions deploy moderate-content
npx supabase functions deploy compute-connection-depth
```
- [ ] **Step 3: Set required secrets**
```bash
cd /c/Users/elia-/Documents/flowproject/flow_backend
npx supabase secrets set FCM_SERVER_KEY=<firebase-cloud-messaging-key>
npx supabase secrets set RESEND_API_KEY=<resend-api-key>
```
- [ ] **Step 4: Configure database webhooks in Supabase dashboard**
Set up the following webhooks in Supabase Dashboard → Database → Webhooks:
| Webhook | Table | Event | Edge Function |
|---------|-------|-------|--------------|
| notify-on-insert | notifications | INSERT | send-notification |
| rsvp-on-change | event_attendees | INSERT, UPDATE | process-rsvp |
| moderate-messages | messages | INSERT | moderate-content |
| moderate-comments | comments | INSERT | moderate-content |
| connection-depth-on-checkin | event_attendees | INSERT | compute-connection-depth |
For `compute-recommendations`: Set up via Supabase Dashboard → Database → Extensions → pg_cron:
```sql
SELECT cron.schedule('compute-recommendations', '0 */6 * * *',
$$SELECT net.http_post(
url := '<SUPABASE_URL>/functions/v1/compute-recommendations',
headers := jsonb_build_object('Authorization', 'Bearer <SUPABASE_SERVICE_ROLE_KEY>')
)$$
);
```
- [ ] **Step 5: Test each function manually**
```bash
# Test send-notification
curl -X POST <SUPABASE_URL>/functions/v1/send-notification \
-H "Authorization: Bearer <SUPABASE_SERVICE_ROLE_KEY>" \
-H "Content-Type: application/json" \
-d '{"record": {"id": "test", "user_id": "<test-user-id>", "type": "system", "title": "Test", "body": "Hello"}}'
# Test compute-matchmaking
curl -X POST <SUPABASE_URL>/functions/v1/compute-matchmaking \
-H "Authorization: Bearer <SUPABASE_ANON_KEY>" \
-H "Content-Type: application/json" \
-d '{"user_id": "<test-user-id>"}'
```
- [ ] **Step 6: Update spec and commit**
Update Phase 2 status in spec:
```
Phase 2: Edge Functions — ✅ COMPLETED (2026-03-30)
```
```bash
cd /c/Users/elia-/Documents/flowproject
git add flow_docs/docs/superpowers/specs/2026-03-29-supabase-only-migration-design.md
git commit -m "docs: mark Phase 2 (Edge Functions) as completed"
```
---
## Summary
| Task | What | Files | Est. |
|------|------|-------|------|
| 1 | Shared utilities (_shared/) | 2 files | 2 min |
| 2 | send-notification | 1 Edge Function | 5 min |
| 3 | process-rsvp | 1 Edge Function | 3 min |
| 4 | compute-recommendations | 1 Edge Function | 5 min |
| 5 | compute-matchmaking | 1 Edge Function | 5 min |
| 6 | moderate-content | 1 Edge Function | 3 min |
| 7 | compute-connection-depth | 1 Edge Function | 3 min |
| 8 | Deploy & configure webhooks | Dashboard config | 10 min |
**Total: ~35 minutes**