Supabase

Supabase

Purpose

Build production backends on Supabase — PostgreSQL schema design, Row Level Security policies, Auth providers, Realtime subscriptions, Storage buckets, and Edge Functions for serverless compute.

Agent Protocol

Trigger

User request includes: Supabase, Supabase database, RLS, Row Level Security, Supabase Auth, Supabase Realtime, Supabase Storage, Supabase Edge Functions, Supabase CLI, Supabase client, Supabase , pgvector, Supabase migration, Supabase project.

Input Context

• Required Supabase features (DB, Auth, Realtime, Storage, Edge Functions)
• PostgreSQL schema requirements (tables, relations, indexes)
• Auth providers needed (email, OAuth, magic link, phone)
• Estimated scale (concurrent users, data volume)

Output Artifact

Supabase project config, SQL schema + RLS policies, client setup, edge function code, storage bucket config.

Response Format

Produce artifact directly. No preamble, no postamble, no explanations. No filler, no hedging, no transitions. Strip articles a/an/the where unambiguous. Compress output — why use many token when few do trick.

Completion Criteria

• PostgreSQL schema defined with proper types, indexes, and relations
• RLS policies applied to every table (select, insert, update, delete)
• Auth providers configured with proper redirect URLs
• Realtime channels designed with proper filters
• Storage buckets with public/private policies
• Edge Functions with proper CORS and error handling
• Client SDK configured (supabase-js)

Max Response Length

4096 tokens

Workflow

Step 1: Project Setup

npm install @supabase/supabase-js @supabase/ssr
npm install -g supabase

# Login and init
supabase login
supabase init

# Link to existing project
supabase link --project-ref <ref>

# Start local dev
supabase start

// src/lib/supabase-client.ts
import { createClient } from '@supabase/supabase-js';

const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL!;
const supabaseAnonKey = process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!;

export const supabase = createClient(supabaseUrl, supabaseAnonKey);

// Server-side (service role — bypasses RLS)
export const supabaseAdmin = createClient(
  supabaseUrl,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { autoRefreshToken: false, persistSession: false } }
);

Step 2: Database Schema

-- Enable required extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
CREATE EXTENSION IF NOT EXISTS "pgvector";

-- Users table (extends Supabase auth.users)
CREATE TABLE public.profiles (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  display_name TEXT,
  avatar_url TEXT,
  role TEXT NOT NULL DEFAULT 'user' CHECK (role IN ('user', 'moderator', 'admin')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Posts table
CREATE TABLE public.posts (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  content TEXT,
  author_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE,
  published BOOLEAN NOT NULL DEFAULT false,
  tags TEXT[] DEFAULT '{}',
  like_count INTEGER NOT NULL DEFAULT 0,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX idx_posts_author ON public.posts(author_id);
CREATE INDEX idx_posts_published ON public.posts(published) WHERE published = true;
CREATE INDEX idx_posts_created ON public.posts(created_at DESC);

-- Vector embeddings (pgvector)
CREATE TABLE public.embeddings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  content TEXT NOT NULL,
  embedding vector(1536),
  metadata JSONB
);

CREATE INDEX idx_embeddings_vector ON public.embeddings
  USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);

Step 3: Row Level Security

-- Enable RLS on all tables
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.posts ENABLE ROW LEVEL SECURITY;

-- Profiles: users can read all profiles, update only own
CREATE POLICY "profiles_select" ON public.profiles
  FOR SELECT USING (true);

CREATE POLICY "profiles_insert" ON public.profiles
  FOR INSERT WITH CHECK (auth.uid() = id);

CREATE POLICY "profiles_update" ON public.profiles
  FOR UPDATE USING (auth.uid() = id)
  WITH CHECK (auth.uid() = id);

-- Posts: users can read published, CRUD own
CREATE POLICY "posts_select" ON public.posts
  FOR SELECT USING (
    published = true OR auth.uid() = author_id
  );

CREATE POLICY "posts_insert" ON public.posts
  FOR INSERT WITH CHECK (auth.uid() = author_id);

CREATE POLICY "posts_update" ON public.posts
  FOR UPDATE USING (auth.uid() = author_id);

CREATE POLICY "posts_delete" ON public.posts
  FOR DELETE USING (auth.uid() = author_id);

Step 4: Realtime Subscriptions

// Enable replication for table in Supabase dashboard
// ALTER PUBLICATION supabase_realtime ADD TABLE posts;

// Subscribe to changes
const channel = supabase
  .channel('public:posts')
  .on(
    'postgres_changes',
    {
      event: 'INSERT',
      schema: 'public',
      table: 'posts',
      filter: `published=eq.true`,
    },
    (payload) => {
      console.log('New post:', payload.new);
    }
  )
  .subscribe();

// Unsubscribe
supabase.removeChannel(channel);

// Presence (multiplayer)
const presenceChannel = supabase.channel('room:1');
presenceChannel
  .on('presence', { event: 'sync' }, () => {
    const state = presenceChannel.presenceState();
  })
  .subscribe(async (status) => {
    if (status === 'SUBSCRIBED') {
      await presenceChannel.track({ onlineAt: new Date().toISOString() });
    }
  });

Step 5: Storage

// Upload file
const { data, error } = await supabase.storage
  .from('avatars')
  .upload(`public/${userId}.jpg`, file, {
    cacheControl: '3600',
    upsert: true,
  });

// Get public URL
const { data: { publicUrl } } = supabase.storage
  .from('avatars')
  .getPublicUrl(`public/${userId}.jpg`);

// Signed URL (private bucket)
const { data: { signedUrl } } = await supabase.storage
  .from('documents')
  .createSignedUrl(`private/doc-${id}.pdf`, 3600);

Step 6: Edge Functions

// supabase/functions/hello/index.ts
import { serve } from 'https://deno.land/std@0.177.0/http/server.ts';
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2';

serve(async (req) => {
  const supabase = createClient(
    Deno.env.get('SUPABASE_URL')!,
    Deno.env.get('SUPABASE_ANON_KEY')!,
    { global: { headers: { Authorization: req.headers.get('Authorization')! } } }
  );

  const { data: posts } = await supabase.from('posts').select('*').limit(10);

  return new Response(JSON.stringify(posts), {
    headers: { 'Content-Type': 'application/json' },
  });
});

Architecture Decision Trees

Schema Design Decision Tree

What type of data are you storing?
  ├── User profiles, settings (per-user)
  │   └── Extends auth.users via profiles table with FK + ON DELETE CASCADE
  ├── Content with ownership (posts, comments)
  │   └── Has author_id FK to profiles, RLS filters by auth.uid()
  ├── Multi-tenant data
  │   └── Every table has tenant_id, RLS checks tenant membership
  ├── Time-series / logs
  │   └── Partitioned tables, no RLS (service-role only), retention policies
  ├── Vector embeddings (AI / semantic search)
  │   └── pgvector extension, IVFFlat or HNSW index, RLS on metadata
  └── File metadata / storage references
      └── References storage objects, path-based RLS using storage extension

RLS Policy Decision Tree

Who can access the data?
  ├── Public read (blog posts, products)
  │   └── FOR SELECT USING (true) — no auth check
  ├── Authenticated users only
  │   └── FOR SELECT USING (auth.role() = 'authenticated')
  ├── Owner only (user's own data)
  │   └── FOR SELECT USING (auth.uid() = user_id)
  ├── Owner + admin
  │   └── FOR SELECT USING (auth.uid() = user_id OR is_admin(auth.uid()))
  ├── Team / organization
  │   └── FOR SELECT USING (exists(select 1 from team_members where user_id = auth.uid() and team_id = team_id))
  ├── Role-based (RBAC)
  │   └── FOR SELECT USING (auth.jwt() ->> 'role' IN ('admin', 'moderator'))
  └── Hierarchical (parent-child)
      └── FOR SELECT USING (exists(select 1 from parent_table where id = parent_id and owner_id = auth.uid()))

Realtime Decision Tree

What kind of real-time updates do you need?
  ├── Database changes (INSERT/UPDATE/DELETE)
  │   └── postgres_changes channel — replication slot on specific table
  │   └── Cost: WAL overhead, ~5-10% write performance hit
  ├── Presence (who's online)
  │   └── Broadcast channel with presence tracking — no DB overhead
  ├── Typing indicators, cursor positions
  │   └── Broadcast channel — ephemeral, no persistence
  │   └── Use with rate limiting (max 5 updates/sec per user)
  └── Multiplayer state sync
      └── Broadcast + presence — merge server state with client state

Advanced RLS Patterns with Roles

-- Custom role-checking function (avoids duplicating JWT checks)
CREATE OR REPLACE FUNCTION auth.user_role()
RETURNS text AS $$
  SELECT COALESCE(
    current_setting('request.jwt.claims', true)::json->>'user_role',
    'anonymous'
  );
$$ LANGUAGE sql STABLE;

-- Multi-role policy for organization data
CREATE POLICY org_data_access ON documents FOR ALL
USING (
  EXISTS (
    SELECT 1 FROM org_members
    WHERE org_members.org_id = documents.org_id
    AND org_members.user_id = auth.uid()
    AND (
      org_members.role = 'owner' -- owners see everything
      OR (
        org_members.role = 'editor'
        AND documents.status != 'archived'
      )
      OR (
        org_members.role = 'viewer'
        AND documents.visibility = 'public'
      )
    )
  )
);

-- Row-level update restrictions based on status
CREATE POLICY prevent_archived_updates ON documents FOR UPDATE
USING (status != 'archived')
WITH CHECK (status != 'archived');

// Client: subscribe with role-aware filters
const channel = supabase.channel('documents')
  .on('postgres_changes',
    {
      event: '*',
      schema: 'public',
      table: 'documents',
      filter: `org_id=eq.${userOrgId}`,
    },
    (payload) => {
      // RLS already filtered — only authorized events arrive
      updateDocumentList(payload.new);
    }
  )
  .subscribe();

Performance Considerations

Security Patterns

-- Row-Level Security: Multi-tenant isolation
CREATE TABLE tenant_data (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  data TEXT NOT NULL
);

ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;

-- Users can only see their tenant's data
CREATE POLICY tenant_isolation ON tenant_data
  FOR ALL USING (
    tenant_id IN (
      SELECT tenant_id FROM tenant_members
      WHERE user_id = auth.uid()
    )
  );

-- Admin bypass
CREATE POLICY admin_access ON tenant_data
  FOR ALL USING (
    auth.jwt() ->> 'role' = 'admin'
  );

// Server-side only: service role operations
// Use for admin tasks, background jobs, webhooks
import { createClient } from '@supabase/supabase-js';

const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!,
  { auth: { autoRefreshToken: false, persistSession: false } }
);

// Admin-only operations bypass RLS
async function deleteUserAccount(userId: string) {
  // Delete auth user (service role required)
  await supabaseAdmin.auth.admin.deleteUser(userId);
  // Profile deleted via CASCADE from auth.users
}

// Database webhooks for async processing
-- Enable webhook on insert
CREATE OR REPLACE FUNCTION notify_new_order()
RETURNS trigger AS $$
BEGIN
  PERFORM pg_notify('new_order', row_to_json(NEW)::text);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER order_created_trigger
  AFTER INSERT ON orders
  FOR EACH ROW EXECUTE FUNCTION notify_new_order();

Cost Optimization

Production Considerations

Anti-Patterns

Rules

• Every table must have RLS enabled — no exceptions.
• Service role key is for server-side/admin operations — never expose to client.
• Always use parameterized queries or Supabase client — no raw SQL strings in client code.
• Realtime: enable replication only on tables that need it (performance cost).
• Storage: public buckets for user content, private buckets for sensitive docs with signed URLs.
• Edge Functions: set --no-verify-jwt flag for webhooks, verify JWT manually for custom auth.
• Use migration workflow for production schema changes, never modify via dashboard.
• Enable PITR for production projects (additional cost).
• Set up database webhooks for async workflows instead of triggers when possible.
• Use EXPLAIN ANALYZE before adding any new index — verify it's actually used.
• Never expose service_role_key in client-side code or version control.
• Keep Edge Functions stateless and < 50ms execution time (cold start excluded).
• Use auth.uid() in RLS policies, not current_user or current_setting.
• Limit Realtime subscriptions to 100 concurrent channels per client.
• Test RLS policies with supabase test or by simulating auth.uid() values.
• Set up database webhooks for async workflows instead of triggers when possible.
• Enable branching for development workflows (supabase branches).

References

• references/edge-functions.md — Edge Functions
• references/postgres-rls.md — PostgreSQL & Row Level Security
• references/supabase-auth.md — Supabase Auth
• references/supabase-backup-migration.md — Supabase Backup and Migration
• references/supabase-realtime.md — Supabase Realtime
• references/supabase-storage.md — Supabase Storage

Handoff

Hand off to ai/vector-databases/SKILL.md for pgvector embedding workflows or mobile/*/SKILL.md for client SDK integration.

Implementation Patterns

Observer Pattern for Event Handling

` interface EventObserver { onEvent(event: T): Promise; }

class EventBus { private observers: Set<EventObserver> = new Set(); subscribe(observer: EventObserver): void { this.observers.add(observer); } unsubscribe(observer: EventObserver): void { this.observers.delete(observer); } async emit(event: T): Promise { const results = Array.from(this.observers).map(o => o.onEvent(event)); await Promise.allSettled(results); } } `

Configuration-Driven Approach

config: defaults: timeout: 30s retryCount: 3 overrides: production: timeout: 60s retryCount: 5 development: timeout: 300s retryCount: 1

Production Considerations

Deployment Checklist

• Configuration validated against schema before startup
• Health check endpoints registered and monitored
• Graceful shutdown with draining period (30s timeout)
• Resource limits configured (CPU, memory, file descriptors)
• Log level set appropriate for environment
• Metrics endpoint secured and exposed
• Rate limiting configured per-tier
• TLS certificates valid and auto-renewing
• Database migrations run as separate deployment step
• Feature flags ready for gradual rollout

Monitoring and Alerting

Anti-Patterns

Performance Optimization

Caching Strategy

Cache hierarchy: L1 (in-memory local) → L2 (distributed Redis/Memcached) → L3 (CDN/Edge). Cache invalidation: TTL-based (simple, stale), event-based (complex, fresh), write-through (consistent, higher write latency), write-behind (fast writes, eventual consistency).

Resource Pooling

• Database connections: Pool of reusable connections (HikariCP, pgBouncer)
• HTTP connections: Keep-alive + connection pooling for external calls
• Thread pool: Bounded thread pools for async task execution

Profiling Methodology

1. Establish baseline with production traffic profile
2. Profile CPU with sampling profiler (pprof, perf, async-profiler)
3. Profile memory with heap dumps and allocation tracking
4. Profile I/O with strace/perf trace for syscall analysis
5. Profile latency with distributed tracing (OpenTelemetry)
6. Identify bottleneck, formulate hypothesis, implement fix
7. Re-profile to verify improvement, repeat

Security Considerations

Threat Modeling (STRIDE)

• Spoofing: Identity validation, authentication
• Tampering: Integrity checks, digital signatures
• Repudiation: Audit logs, non-repudiation
• Information disclosure: Encryption, access control
• Denial of service: Rate limiting, resource quotas
• Elevation of privilege: Principle of least privilege

Supply Chain Security

• Dependency scanning: Snyk, Dependabot, Trivy
• SBOM generation: CycloneDX or SPDX format
• Signed commits: GPG or SSH commit signing
• Artifact verification: Checksum validation, signature verification

Secrets Management

• Secrets never in code — always in secrets manager (Vault, AWS Secrets Manager)
• Rotation policy: Rotate database credentials every 90 days
• Access audit: Log every secrets access, alert on anomalies
• Encryption at rest and in transit for all secrets
• Principle of least privilege: each service gets only its own secrets

Rules

• Default-deny security posture — allow only explicitly required access.
• All inputs validated, all outputs encoded, all errors handled.
• Defend in depth — multiple layers of security controls.
• Fail securely — errors default to safe behavior.
• Log security-relevant events for audit and investigation.
• Keep dependencies updated — automate vulnerability scanning.
• Design for observability from day one, not as an afterthought.
• Document all architectural decisions with rationale.
• Review code for security, performance, and correctness before merging.