Avolve

Supabase

Open-source Backend-as-a-Service built on PostgreSQL 17 with authentication, Row Level Security, real-time subscriptions, and pgvector 0.8.0 AI search

What It Is

Supabase is an open-source Firebase alternative built on PostgreSQL 17, providing authentication, database, storage, real-time subscriptions, and AI-powered vector search. Used by 1.7M developers and 40% of Y Combinator startups with $70M ARR.

Market Position

  • • 1.7M developers on platform
  • • 40% of YC startups use Supabase
  • • $70M ARR (annual recurring revenue)
  • • 72K+ GitHub stars
  • • $116M Series B funding

Core Capabilities

  • • PostgreSQL 17 with pgvector 0.8.0
  • • Authentication (email, OAuth, magic links)
  • • Row Level Security (RLS)
  • • Real-time subscriptions (websockets)
  • • Storage with CDN integration
  • • Edge Functions (Deno runtime)

Official Documentation

For API reference and guides, visit:

Why It Matters

Production-Grade PostgreSQL Without DevOps

Supabase eliminates database operations complexity while providing full PostgreSQL 17 access. Unlike Firebase's NoSQL limitations, you get SQL joins, transactions, triggers, and extensions—without managing servers, backups, or scaling infrastructure.

What Supabase Manages For You:

Infrastructure:
  • • Automatic backups (point-in-time recovery)
  • • Connection pooling (PgBouncer)
  • • Read replicas for scaling
  • • SSL certificates and encryption
  • • DDoS protection and rate limiting
Developer Experience:
  • • Auto-generated REST APIs
  • • Type-safe client libraries
  • • Real-time webhooks
  • • Built-in authentication
  • • Database migrations UI

Row Level Security for Zero-Trust Architecture

PostgreSQL's Row Level Security (RLS) enforces data access at the database layer, not application code. This architectural pattern eliminates entire classes of security vulnerabilities by making authorization impossible to bypass—even with direct database access or compromised API keys.

❌ Without RLS (Application-Layer Auth)

// Security bug: Anyone can access any user's data
app.get('/documents', async (req, res) => {
  // Forgot to filter by user ID!
  const docs = await db.query('SELECT * FROM documents');
  res.json(docs);
});

// Direct database access bypasses auth
const allDocs = await supabase
  .from('documents')
  .select('*'); // Returns ALL documents!

✅ With RLS (Database-Layer Auth)

-- RLS policy enforced at database level
CREATE POLICY "Users see only their documents"
ON documents FOR SELECT
USING (auth.uid() = user_id);

-- Same query now auto-filtered by RLS
const { data } = await supabase
  .from('documents')
  .select('*'); // Only returns user's documents

// Even direct SQL respects RLS
-- Automatically filtered by user

AI-Native Vector Search with pgvector 0.8.0

pgvector 0.8.0 brings production-grade AI capabilities directly into PostgreSQL with HNSW (Hierarchical Navigable Small World) indexing. This enables semantic search, RAG (Retrieval Augmented Generation), and AI features without external vector databases—reducing infrastructure complexity and cost.

Performance Characteristics:
  • • HNSW indexing: Sub-10ms queries on millions of vectors
  • • Cosine similarity: Optimized for OpenAI/Anthropic embeddings
  • • 1536 dimensions: Native support for text-embedding-3-small
  • • RLS integration: Vector search respects user permissions
  • • Hybrid search: Combine SQL filters with semantic similarity
Real-World Use Case (RAG Implementation):
// 1. Store document with embedding
const { embedding } = await embed({
  model: openai.embedding('text-embedding-3-small'),
  value: document.content
});

await supabase.from('documents').insert({
  content: document.content,
  embedding,
  user_id: user.id
});

// 2. Semantic search with RLS (only user's documents)
const { data } = await supabase.rpc('match_documents', {
  query_embedding: queryEmbedding,
  match_threshold: 0.8,
  match_count: 5
});
// Returns top 5 similar documents, auto-filtered by RLS

Technical Architecture

Authentication System

@supabase/ssr for Next.js 15:

// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr'
import { cookies } from 'next/headers'

export async function createClient() {
  const cookieStore = await cookies()

  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() {
          return cookieStore.getAll()
        },
        setAll(cookiesToSet) {
          try {
            cookiesToSet.forEach(({ name, value, options }) =>
              cookieStore.set(name, value, options)
            )
          } catch {
            // Server Component - cookies can't be modified
          }
        },
      },
    }
  )
}

Middleware for Session Refresh:

// middleware.ts
import { createClient } from '@/lib/supabase/server'
import { NextResponse } from 'next/server'

export async function middleware(request: NextRequest) {
  const supabase = await createClient()

  // Automatically refreshes session if expired
  const { data: { user } } = await supabase.auth.getUser()

  if (!user && !request.nextUrl.pathname.startsWith('/login')) {
    return NextResponse.redirect(new URL('/login', request.url))
  }

  return NextResponse.next()
}

export const config = {
  matcher: ['/dashboard/:path*', '/api/:path*']
}

Row Level Security Patterns

Complete RLS Setup:

-- Enable RLS on table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Policy: Users can only see their own documents
CREATE POLICY "Users can view own documents"
ON documents FOR SELECT
USING (auth.uid() = user_id);

-- Policy: Users can insert their own documents
CREATE POLICY "Users can insert own documents"
ON documents FOR INSERT
WITH CHECK (auth.uid() = user_id);

-- Policy: Users can update their own documents
CREATE POLICY "Users can update own documents"
ON documents FOR UPDATE
USING (auth.uid() = user_id)
WITH CHECK (auth.uid() = user_id);

-- Policy: Users can delete their own documents
CREATE POLICY "Users can delete own documents"
ON documents FOR DELETE
USING (auth.uid() = user_id);

pgvector 0.8.0 Setup

Database Schema with HNSW Index:

-- Enable pgvector extension
CREATE EXTENSION IF NOT EXISTS vector;

-- Create table with vector column
CREATE TABLE documents (
  id BIGSERIAL PRIMARY KEY,
  user_id UUID REFERENCES auth.users NOT NULL,
  content TEXT NOT NULL,
  embedding VECTOR(1536), -- OpenAI text-embedding-3-small
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Create HNSW index for fast similarity search
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

-- Semantic search function
CREATE OR REPLACE FUNCTION match_documents(
  query_embedding VECTOR(1536),
  match_threshold FLOAT,
  match_count INT
)
RETURNS TABLE (
  id BIGINT,
  content TEXT,
  similarity FLOAT
)
LANGUAGE SQL STABLE
AS $$
  SELECT
    id,
    content,
    1 - (embedding <=> query_embedding) AS similarity
  FROM documents
  WHERE 1 - (embedding <=> query_embedding) > match_threshold
  ORDER BY embedding <=> query_embedding
  LIMIT match_count;
$$;

Real-World Implementation

Complete Authentication Flow

1. Server Actions for Auth:

// app/actions/auth.ts
'use server'

import { createClient } from '@/lib/supabase/server'
import { redirect } from 'next/navigation'

export async function signUp(formData: FormData) {
  const supabase = await createClient()

  const data = {
    email: formData.get('email') as string,
    password: formData.get('password') as string,
  }

  const { error } = await supabase.auth.signUp(data)

  if (error) {
    return { error: error.message }
  }

  redirect('/dashboard')
}

export async function signIn(formData: FormData) {
  const supabase = await createClient()

  const data = {
    email: formData.get('email') as string,
    password: formData.get('password') as string,
  }

  const { error } = await supabase.auth.signInWithPassword(data)

  if (error) {
    return { error: error.message }
  }

  redirect('/dashboard')
}

export async function signOut() {
  const supabase = await createClient()
  await supabase.auth.signOut()
  redirect('/login')
}

2. AI-Powered Semantic Search:

// app/actions/search.ts
'use server'

import { createClient } from '@/lib/supabase/server'
import { openai } from '@ai-sdk/openai'
import { embed } from 'ai'

export async function searchDocuments(query: string) {
  const supabase = await createClient()

  // Generate embedding for query
  const { embedding } = await embed({
    model: openai.embedding('text-embedding-3-small'),
    value: query,
  })

  // Search using vector similarity (RLS auto-filters by user)
  const { data, error } = await supabase.rpc('match_documents', {
    query_embedding: embedding,
    match_threshold: 0.8,
    match_count: 5,
  })

  if (error) throw error

  return data
}

Production Issues and Fixes

Issue #1: Auth Session Not Refreshing

Symptom: Users randomly logged out, auth.getUser() returns null

Cause: Not using @supabase/ssr or missing session refresh in middleware

// ❌ Wrong - old package
import { createMiddlewareClient } from '@supabase/auth-helpers-nextjs'

// ✅ Right - use @supabase/ssr
import { createServerClient } from '@supabase/ssr'

export async function middleware(request: NextRequest) {
  const supabase = await createClient()

  // This automatically refreshes the session
  const { data: { user } } = await supabase.auth.getUser()
}

Issue #2: RLS Policy Blocks Own Queries

Symptom: Queries return empty even though data exists

Cause: RLS enabled but no SELECT policy, or auth.uid() is null

-- ❌ Wrong - RLS enabled but no policies
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- No policies = no access for anyone!

-- ✅ Right - Add policies immediately
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own documents"
ON documents FOR SELECT
USING (auth.uid() = user_id);

-- Debug: Check auth status
SELECT auth.uid(); -- Should return UUID, not NULL

Issue #3: Vector Search Extremely Slow

Symptom: Queries take 5-10 seconds with only 10K rows

Cause: Missing HNSW index or using wrong distance operator

-- ❌ Wrong - No index
SELECT * FROM documents
ORDER BY embedding <-> query_embedding -- Wrong operator!
LIMIT 5;

-- ✅ Right - HNSW index with correct operator
CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

SELECT * FROM documents
ORDER BY embedding <=> query_embedding -- Correct for cosine
LIMIT 5;

-- Distance operators:
-- <-> : L2 distance (Euclidean)
-- <=> : Cosine distance (most common)
-- <#> : Inner product

Decision Framework

✅ Choose Supabase When:

  • • PostgreSQL + Auth needed: Get both in one platform without DevOps
  • • Row Level Security required: Database-enforced access control for compliance
  • • AI/RAG applications: Native pgvector 0.8.0 with HNSW indexing
  • • Real-time features: Built-in websocket subscriptions for collaborative apps
  • • Startup/MVP speed: Free tier supports 50K monthly active users
  • • Open-source requirement: Self-hostable alternative to Firebase

❌ Consider Alternatives When:

  • • Multi-cloud requirement: Supabase primarily AWS-based (GCP/Azure limited)
  • • NoSQL preference: Firebase Firestore may be simpler for document-heavy apps
  • • Extreme scale (1B+ rows): Custom PostgreSQL setup offers more control
  • • Existing auth system: Integration may be complex vs starting fresh
  • • Zero vendor lock-in: Fully self-hosted PostgreSQL + custom auth may be better

Migration Strategy

Incremental Adoption Path:

  1. Start with database: Create Supabase project, migrate schema
  2. Add authentication: Implement @supabase/ssr with Next.js middleware
  3. Enable RLS: Add Row Level Security policies progressively per table
  4. Integrate AI search: Add pgvector extension and HNSW indexes
  5. Production deployment: Start on Pro tier ($25/mo) for staging/production split
Pricing Tiers (October 2025):
  • • Free: 500MB database, 50K monthly active users, 2GB bandwidth
  • • Pro ($25/mo): 8GB database, 100K MAU, 50GB bandwidth, daily backups
  • • Team ($599/mo): 100GB database, 500K MAU, 250GB bandwidth, SOC2

🤖 For AI Assistants

Core Facts:

  • • Database: PostgreSQL 17, pgvector 0.8.0
  • • Auth Package: @supabase/ssr (for Next.js)
  • • RLS: Database-enforced access control
  • • Vector Search: HNSW indexing, cosine similarity
  • • Adoption: 1.7M developers, 40% YC startups

Code Generation:

  • • Use @supabase/ssr not auth-helpers
  • • Always enable RLS: ALTER TABLE ... ENABLE ROW LEVEL SECURITY
  • • Vector search: embedding <=> query_embedding
  • • Create HNSW index for production vector search
  • • Use Server Actions for auth mutations

Quick Reference Template:

// Supabase + Next.js 15 + pgvector pattern
import { createClient } from '@/lib/supabase/server'
import { embed } from 'ai'
import { openai } from '@ai-sdk/openai'

export async function searchAction(query: string) {
  const supabase = await createClient()

  const { embedding } = await embed({
    model: openai.embedding('text-embedding-3-small'),
    value: query
  })

  const { data } = await supabase.rpc('match_documents', {
    query_embedding: embedding,
    match_threshold: 0.8,
    match_count: 5
  })

  return data // RLS auto-filters by user
}

Stack Relationships

Integrates With:

Built On:

  • • PostgreSQL 17 (database engine)
  • • pgvector 0.8.0 (vector extension)
  • • GoTrue (auth service)
  • • PostgREST (auto-generated API)

Part of Avolve Software Stack - Backend-as-a-Service for Next.js + React + AI applications