Flow Docs

2026-03-30-supabase-migration-phase2-edge-functions

19 min read

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

ActionFilePurpose
Createflow_backend/supabase/functions/_shared/supabase-client.tsShared Supabase service_role client
Createflow_backend/supabase/functions/_shared/cors.tsShared CORS headers
Createflow_backend/supabase/functions/send-notification/index.tsPush/email notification delivery
Createflow_backend/supabase/functions/process-rsvp/index.tsRSVP XP award + organizer notification
Createflow_backend/supabase/functions/compute-recommendations/index.tsScheduled recommendation scoring
Createflow_backend/supabase/functions/compute-matchmaking/index.tsOn-demand crew matchmaking
Createflow_backend/supabase/functions/moderate-content/index.tsAsync content moderation
Createflow_backend/supabase/functions/compute-connection-depth/index.tsFriendship 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

// 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
// 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
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

// 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
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

// 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
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

// 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
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

// 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
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

// 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
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

// 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
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
cd /c/Users/elia-/Documents/flowproject/flow_backend
npx supabase functions list
  • Step 2: Deploy all Edge Functions
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
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:

WebhookTableEventEdge Function
notify-on-insertnotificationsINSERTsend-notification
rsvp-on-changeevent_attendeesINSERT, UPDATEprocess-rsvp
moderate-messagesmessagesINSERTmoderate-content
moderate-commentscommentsINSERTmoderate-content
connection-depth-on-checkinevent_attendeesINSERTcompute-connection-depth

For compute-recommendations: Set up via Supabase Dashboard → Database → Extensions → pg_cron:

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
# 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)

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

TaskWhatFilesEst.
1Shared utilities (_shared/)2 files2 min
2send-notification1 Edge Function5 min
3process-rsvp1 Edge Function3 min
4compute-recommendations1 Edge Function5 min
5compute-matchmaking1 Edge Function5 min
6moderate-content1 Edge Function3 min
7compute-connection-depth1 Edge Function3 min
8Deploy & configure webhooksDashboard config10 min

Total: ~35 minutes

Graph View