Claude Code for Supabase Advanced: Vector Search, Edge Functions, and RLS

Supabase extends PostgreSQL with a full backend platform — pgvector for semantic search, Row Level Security for declarative access control, Edge Functions via Deno Deploy, and Realtime subscriptions over WebSocket. pgvector stores dense embeddings and executes approximate nearest-neighbor queries with <=> cosine distance. RLS policies use auth.uid() and custom security definer functions for multi-tenant isolation. Edge Functions are Deno TypeScript functions — first-class @supabase/supabase-js client with service role access. Realtime channels subscribe to database changes with server-side row filters. pg_cron schedules SQL jobs from within PostgreSQL. Claude Code generates pgvector schemas, RLS policies, Edge Function implementations, Realtime subscription handlers, and the full-stack Supabase patterns for production applications.

CLAUDE.md for Supabase Advanced

## Supabase Advanced Stack
- Version: @supabase/supabase-js >= 2.45, @supabase/ssr >= 0.5
- Vector: CREATE EXTENSION pgvector; vector(1536) columns, ivfflat/hnsw indexes
- RLS: always enable — use auth.uid() for user isolation, security definer for complex rules
- Edge Functions: supabase/functions/fn-name/index.ts — Deno runtime, service role client
- Realtime: supabase.channel('orders').on('postgres_changes', ...) with filter
- Auth: @supabase/ssr for server-side auth in Next.js / SvelteKit
- Webhooks: database webhooks in dashboard → Edge Function or external URL

pgvector Schema and Semantic Search

-- migrations/001_pgvector.sql — pgvector setup for semantic search
CREATE EXTENSION IF NOT EXISTS vector;

-- Store product embeddings from OpenAI/Cohere
CREATE TABLE product_embeddings (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  product_id uuid NOT NULL REFERENCES products(id) ON DELETE CASCADE,
  content text NOT NULL,           -- The text that was embedded
  embedding vector(1536) NOT NULL, -- OpenAI text-embedding-3-small output
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(product_id)
);

-- HNSW index for fast approximate nearest-neighbor search
-- ef_construction=128 for build quality, m=16 for graph connectivity
CREATE INDEX product_embeddings_vector_idx ON product_embeddings
  USING hnsw(embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

-- Semantic search function — cosine similarity
CREATE OR REPLACE FUNCTION search_products(
  query_embedding vector(1536),
  match_threshold float DEFAULT 0.78,
  match_count int DEFAULT 10
)
RETURNS TABLE(
  product_id uuid,
  content text,
  similarity float
)
LANGUAGE sql
STABLE
AS $$
  SELECT
    pe.product_id,
    pe.content,
    1 - (pe.embedding <=> query_embedding) AS similarity
  FROM product_embeddings pe
  WHERE 1 - (pe.embedding <=> query_embedding) > match_threshold
  ORDER BY pe.embedding <=> query_embedding
  LIMIT match_count;
$$;

-- Combined full-text + semantic search (hybrid)
CREATE OR REPLACE FUNCTION hybrid_search_products(
  query_text text,
  query_embedding vector(1536),
  ft_weight float DEFAULT 0.5,
  semantic_weight float DEFAULT 0.5,
  match_count int DEFAULT 10
)
RETURNS TABLE(product_id uuid, score float)
LANGUAGE sql
STABLE
AS $$
  WITH
  semantic AS (
    SELECT product_id,
           1 - (embedding <=> query_embedding) AS semantic_score
    FROM product_embeddings
    ORDER BY embedding <=> query_embedding
    LIMIT 50
  ),
  fulltext AS (
    SELECT p.id as product_id,
           ts_rank(to_tsvector('english', p.name || ' ' || p.description),
                   plainto_tsquery('english', query_text)) AS ft_score
    FROM products p
    WHERE to_tsvector('english', p.name || ' ' || p.description)
          @@ plainto_tsquery('english', query_text)
    LIMIT 50
  )
  SELECT
    COALESCE(s.product_id, f.product_id) as product_id,
    COALESCE(s.semantic_score * semantic_weight, 0) +
    COALESCE(f.ft_score * ft_weight, 0) as score
  FROM semantic s
  FULL OUTER JOIN fulltext f ON s.product_id = f.product_id
  ORDER BY score DESC
  LIMIT match_count;
$$;

TypeScript Semantic Search

// lib/semantic-search.ts — pgvector search with embeddings
import { createClient } from "@supabase/supabase-js"
import OpenAI from "openai"

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!
)

const openai = new OpenAI({ apiKey: process.env.OPENAI_API_KEY! })

export async function embedText(text: string): Promise<number[]> {
  const response = await openai.embeddings.create({
    model: "text-embedding-3-small",
    input: text,
    dimensions: 1536,
  })
  return response.data[0].embedding
}

export async function semanticSearch(query: string, matchCount = 10) {
  const queryEmbedding = await embedText(query)

  const { data, error } = await supabase.rpc("search_products", {
    query_embedding: queryEmbedding,
    match_threshold: 0.78,
    match_count: matchCount,
  })

  if (error) throw error

  // Fetch full product details for matched IDs
  const productIds = data.map((r: any) => r.product_id)
  const { data: products } = await supabase
    .from("products")
    .select("*")
    .in("id", productIds)

  // Merge similarity scores
  return data.map((result: any) => ({
    ...products?.find(p => p.id === result.product_id),
    similarity: result.similarity,
  }))
}

// Index a product's embedding
export async function indexProduct(productId: string, content: string) {
  const embedding = await embedText(content)

  await supabase
    .from("product_embeddings")
    .upsert({
      product_id: productId,
      content,
      embedding,
    }, { onConflict: "product_id" })
}

Advanced Row Level Security

-- migrations/002_rls.sql — multi-tenant RLS policies

-- Helper function for team membership check
-- security definer: runs as the function owner, not calling user
CREATE OR REPLACE FUNCTION is_team_member(team_id uuid)
RETURNS boolean
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
  RETURN EXISTS (
    SELECT 1 FROM team_members
    WHERE team_id = $1
      AND user_id = auth.uid()
      AND status = 'active'
  );
END;
$$;

-- Orders: users see only their team's orders
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

CREATE POLICY "users_read_own_team_orders"
  ON orders FOR SELECT
  USING (is_team_member(team_id));

CREATE POLICY "users_insert_own_team_orders"
  ON orders FOR INSERT
  WITH CHECK (
    is_team_member(team_id)
    AND auth.uid() = created_by
  );

CREATE POLICY "users_update_own_orders"
  ON orders FOR UPDATE
  USING (
    is_team_member(team_id)
    AND (
      auth.uid() = created_by
      OR is_team_admin(team_id)
    )
  );

-- Admins can delete; regular users cannot
CREATE POLICY "admins_delete_orders"
  ON orders FOR DELETE
  USING (is_team_admin(team_id));

-- Service role bypasses RLS — Edge Functions use service role

Edge Function

// supabase/functions/process-order/index.ts — Deno Edge Function
import { serve } from "https://deno.land/[email protected]/http/server.ts"
import { createClient } from "https://esm.sh/@supabase/supabase-js@2"

const supabase = createClient(
  Deno.env.get("SUPABASE_URL")!,
  Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!  // Service role bypasses RLS
)

serve(async (req) => {
  if (req.method !== "POST") {
    return new Response("Method not allowed", { status: 405 })
  }

  // Verify caller is authenticated
  const authHeader = req.headers.get("Authorization")
  if (!authHeader) return new Response("Unauthorized", { status: 401 })

  const { data: { user }, error: authError } = await supabase.auth.getUser(
    authHeader.replace("Bearer ", "")
  )
  if (authError || !user) return new Response("Unauthorized", { status: 401 })

  const { orderId } = await req.json()

  // Fetch order (service role sees all rows)
  const { data: order } = await supabase
    .from("orders")
    .select("*, items(*)")
    .eq("id", orderId)
    .single()

  if (!order) return new Response("Not found", { status: 404 })

  // Call external fulfillment API
  const fulfillResult = await fetch("https://fulfillment.api.com/orders", {
    method: "POST",
    headers: { "Content-Type": "application/json" },
    body: JSON.stringify({ orderId, items: order.items }),
  })

  const { fulfillmentId } = await fulfillResult.json()

  // Update order
  await supabase
    .from("orders")
    .update({
      status: "processing",
      fulfillment_id: fulfillmentId,
      processed_at: new Date().toISOString(),
    })
    .eq("id", orderId)

  return Response.json({ fulfillmentId })
})

Realtime Subscription

// src/hooks/useOrderRealtime.ts — filtered Realtime subscription
import { createClient } from "@supabase/supabase-js"
import { useEffect, useState } from "react"

export function useOrderRealtime(orderId: string) {
  const [order, setOrder] = useState<Order | null>(null)
  const supabase = createClient(/* ... */)

  useEffect(() => {
    // Initial fetch
    supabase.from("orders").select("*").eq("id", orderId).single()
      .then(({ data }) => setOrder(data))

    // Subscribe to realtime changes for this specific order
    const channel = supabase
      .channel(`order:${orderId}`)
      .on(
        "postgres_changes",
        {
          event: "UPDATE",
          schema: "public",
          table: "orders",
          filter: `id=eq.${orderId}`,
        },
        (payload) => {
          setOrder(payload.new as Order)
        }
      )
      .subscribe()

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

  return order
}

For the standalone pgvector setup on Neon or RDS when more PostgreSQL control is needed without Supabase’s opinionated auth/storage/realtime stack, see the Neon guide for serverless PostgreSQL configuration. For the PocketBase self-hosted alternative that provides similar auth + realtime + storage with Go hooks and SQLite instead of PostgreSQL, see the PocketBase guide for collection schema patterns. The Claude Skills 360 bundle includes advanced Supabase skill sets covering pgvector, RLS policies, and Edge Functions. Start with the free tier to try Supabase advanced configuration generation.