Realtime and Webhooks

Supabase Realtime lets the Cursorist frontend react instantly to database changes — when a plugin is published, a favorite is added, or an invitation arrives, the UI updates without a page refresh. Webhooks extend this to server-side workflows like sending welcome emails or notifying authors when their plugin goes live.

Three tables have Realtime enabled: plugins, plugin_favorites, and invitations. The frontend subscribes to PostgreSQL change events filtered by team or user, so each client only receives the updates relevant to them.

Realtime Configuration

Enable Realtime on Tables

-- Enable realtime for specific tables
ALTER PUBLICATION supabase_realtime ADD TABLE plugins;
ALTER PUBLICATION supabase_realtime ADD TABLE plugin_favorites;
ALTER PUBLICATION supabase_realtime ADD TABLE invitations;

Frontend Realtime Subscriptions

Plugins Updates

import { createClient } from "@/lib/supabase/client";
import { useEffect, useState } from "react";
import { RealtimeChannel } from "@supabase/supabase-js";

export function useRealtimePlugins(teamId: string) {
  const [plugins, setPlugins] = useState<Plugin[]>([]);
  const supabase = createClient();

  useEffect(() => {
    // Initial fetch
    const fetchPlugins = async () => {
      const { data } = await supabase
        .from("plugins")
        .select("*")
        .eq("team_id", teamId);
      setPlugins(data || []);
    };

    fetchPlugins();

    // Subscribe to changes
    const channel: RealtimeChannel = supabase
      .channel(`plugins:${teamId}`)
      .on(
        "postgres_changes",
        {
          event: "*",
          schema: "public",
          table: "plugins",
          filter: `team_id=eq.${teamId}`,
        },
        (payload) => {
          if (payload.eventType === "INSERT") {
            setPlugins((prev) => [...prev, payload.new as Plugin]);
          } else if (payload.eventType === "UPDATE") {
            setPlugins((prev) =>
              prev.map((p) =>
                p.id === payload.new.id ? (payload.new as Plugin) : p
              )
            );
          } else if (payload.eventType === "DELETE") {
            setPlugins((prev) =>
              prev.filter((p) => p.id !== payload.old.id)
            );
          }
        }
      )
      .subscribe();

    return () => {
      supabase.removeChannel(channel);
    };
  }, [teamId]);

  return plugins;
}

Invitation Notifications

export function useInvitationNotifications(userId: string) {
  const [invitations, setInvitations] = useState<Invitation[]>([]);
  const supabase = createClient();

  useEffect(() => {
    const channel = supabase
      .channel(`invitations:${userId}`)
      .on(
        "postgres_changes",
        {
          event: "INSERT",
          schema: "public",
          table: "invitations",
          filter: `email=eq.${userEmail}`,
        },
        (payload) => {
          setInvitations((prev) => [...prev, payload.new as Invitation]);
          // Show toast notification
          toast.info("You have a new team invitation!");
        }
      )
      .subscribe();

    return () => {
      supabase.removeChannel(channel);
    };
  }, [userId]);

  return invitations;
}

Admin Dashboard Live Metrics

export function useAdminRealtimeMetrics() {
  const [metrics, setMetrics] = useState({
    totalUsers: 0,
    totalPlugins: 0,
  });
  const supabase = createClient();

  useEffect(() => {
    // Subscribe to users table for count updates
    const usersChannel = supabase
      .channel("admin:users")
      .on(
        "postgres_changes",
        { event: "INSERT", schema: "public", table: "users" },
        () => {
          setMetrics((prev) => ({
            ...prev,
            totalUsers: prev.totalUsers + 1,
          }));
        }
      )
      .subscribe();

    // Subscribe to plugins table
    const pluginsChannel = supabase
      .channel("admin:plugins")
      .on(
        "postgres_changes",
        { event: "INSERT", schema: "public", table: "plugins" },
        () => {
          setMetrics((prev) => ({
            ...prev,
            totalPlugins: prev.totalPlugins + 1,
          }));
        }
      )
      .subscribe();

    return () => {
      supabase.removeChannel(usersChannel);
      supabase.removeChannel(pluginsChannel);
    };
  }, []);

  return metrics;
}

// supabase/functions/webhook-new-user/index.ts
import { serve } from "https://deno.land/std@0.168.0/http/server.ts";

interface WebhookPayload {
  type: "INSERT";
  table: "users";
  record: {
    id: string;
    github_username: string;
    email: string;
  };
}

serve(async (req) => {
  const payload: WebhookPayload = await req.json();

  // Send welcome email
  await fetch("https://api.sendgrid.com/v3/mail/send", {
    method: "POST",
    headers: {
      Authorization: `Bearer ${Deno.env.get("SENDGRID_API_KEY")}`,
      "Content-Type": "application/json",
    },
    body: JSON.stringify({
      personalizations: [{ to: [{ email: payload.record.email }] }],
      from: { email: "hello@cursor.ist" },
      subject: "Welcome to Cursorist!",
      content: [
        {
          type: "text/plain",
          value: `Hi ${payload.record.github_username}, welcome to Cursorist!`,
        },
      ],
    }),
  });

  return new Response("OK", { status: 200 });
});

Plugin Published Webhook

Trigger: UPDATE on plugins table when is_published changes to true

// supabase/functions/webhook-plugin-published/index.ts
import { serve } from "https://deno.land/std@0.168.0/http/server.ts";
import { supabaseAdmin } from "../_shared/supabase.ts";

interface WebhookPayload {
  type: "UPDATE";
  table: "plugins";
  record: {
    id: string;
    name: string;
    slug: string;
    is_published: boolean;
    author_id: string;
  };
  old_record: {
    is_published: boolean;
  };
}

serve(async (req) => {
  const payload: WebhookPayload = await req.json();

  // Only trigger on publish (not unpublish)
  if (!payload.record.is_published || payload.old_record.is_published) {
    return new Response("Skipped", { status: 200 });
  }

  // Get author details
  const { data: author } = await supabaseAdmin
    .from("users")
    .select("email, github_username")
    .eq("id", payload.record.author_id)
    .single();

  if (author?.email) {
    // Notify author
    await fetch("https://api.sendgrid.com/v3/mail/send", {
      method: "POST",
      headers: {
        Authorization: `Bearer ${Deno.env.get("SENDGRID_API_KEY")}`,
        "Content-Type": "application/json",
      },
      body: JSON.stringify({
        personalizations: [{ to: [{ email: author.email }] }],
        from: { email: "hello@cursor.ist" },
        subject: `Your plugin "${payload.record.name}" is now live!`,
        content: [
          {
            type: "text/plain",
            value: `Congratulations! Your plugin has been published to the OSS repository.`,
          },
        ],
      }),
    });
  }

  return new Response("OK", { status: 200 });
});

Database Triggers

Auto-Add Owner to Organization Members

CREATE OR REPLACE FUNCTION add_owner_to_org_members()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO organization_members (user_id, organization_id, role)
  VALUES (NEW.owner_id, NEW.id, 'owner');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_add_owner_to_org_members
  AFTER INSERT ON organizations
  FOR EACH ROW
  EXECUTE FUNCTION add_owner_to_org_members();

Auto-Add Creator to Team Members

CREATE OR REPLACE FUNCTION add_creator_to_team_members()
RETURNS TRIGGER AS $$
DECLARE
  creator_id UUID;
BEGIN
  -- Get organization owner as team creator
  SELECT owner_id INTO creator_id
  FROM organizations
  WHERE id = NEW.organization_id;

  INSERT INTO team_members (user_id, team_id, role)
  VALUES (creator_id, NEW.id, 'admin');
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_add_creator_to_team_members
  AFTER INSERT ON teams
  FOR EACH ROW
  EXECUTE FUNCTION add_creator_to_team_members();

Expire Old Invitations

-- Scheduled function to expire invitations
CREATE OR REPLACE FUNCTION expire_old_invitations()
RETURNS VOID AS $$
BEGIN
  UPDATE invitations
  SET status = 'expired'
  WHERE status = 'pending'
    AND expires_at < NOW();
END;
$$ LANGUAGE plpgsql;

-- Call via pg_cron or Supabase scheduled function

Presence (Online Status)

Track Online Users

export function useOnlineUsers(teamId: string) {
  const [onlineUsers, setOnlineUsers] = useState<string[]>([]);
  const supabase = createClient();

  useEffect(() => {
    const channel = supabase.channel(`presence:${teamId}`, {
      config: {
        presence: {
          key: "user_id",
        },
      },
    });

    channel
      .on("presence", { event: "sync" }, () => {
        const state = channel.presenceState();
        const users = Object.keys(state);
        setOnlineUsers(users);
      })
      .on("presence", { event: "join" }, ({ key, newPresences }) => {
        console.log("User joined:", key);
      })
      .on("presence", { event: "leave" }, ({ key, leftPresences }) => {
        console.log("User left:", key);
      })
      .subscribe(async (status) => {
        if (status === "SUBSCRIBED") {
          await channel.track({
            user_id: currentUserId,
            online_at: new Date().toISOString(),
          });
        }
      });

    return () => {
      channel.untrack();
      supabase.removeChannel(channel);
    };
  }, [teamId]);

  return onlineUsers;
}

Broadcast Messages

Team Activity Feed

export function useTeamBroadcast(teamId: string) {
  const supabase = createClient();

  const sendActivity = async (activity: {
    type: string;
    message: string;
    userId: string;
  }) => {
    const channel = supabase.channel(`team:${teamId}`);
    
    await channel.send({
      type: "broadcast",
      event: "activity",
      payload: activity,
    });
  };

  const subscribeToActivity = (callback: (activity: any) => void) => {
    const channel = supabase
      .channel(`team:${teamId}`)
      .on("broadcast", { event: "activity" }, ({ payload }) => {
        callback(payload);
      })
      .subscribe();

    return () => {
      supabase.removeChannel(channel);
    };
  };

  return { sendActivity, subscribeToActivity };
}

Best Practices

Unsubscribe on cleanup - Always remove channels in useEffect cleanup
Use specific filters - Filter subscriptions to minimize data transfer
Handle reconnection - Implement retry logic for dropped connections
Batch updates - Debounce rapid updates to prevent UI thrashing
Secure webhooks - Verify webhook signatures in production
Monitor realtime connections - Track active connections in admin dashboard