Supabase PostgreSQL Toolkit

Production-ready Supabase and PostgreSQL integration guide covering database design, Row Level Security (RLS), real-time subscriptions, edge functions, storage, and authentication patterns for full-stack applications.

When to Use This Skill

Choose Supabase PostgreSQL when:

• Building full-stack apps with PostgreSQL backend via Supabase
• Implementing Row Level Security for multi-tenant data isolation
• Setting up real-time data subscriptions for live features
• Configuring Supabase Auth with social providers
• Designing database schemas with proper indexing and constraints

Consider alternatives when:

• Need a non-relational database — use Firebase or DynamoDB
• Need bare-metal PostgreSQL — use direct PG with Prisma/Drizzle
• Need a full backend framework — use Supabase alongside Express/FastAPI

Quick Start

Copy
# Install Supabase client
npm install @supabase/supabase-js

# Activate toolkit
claude skill activate dynamic-supabase-postgresql-toolkit

# Design schema
claude "Design a Supabase schema for a project management app with RLS"

Example: Schema with Row Level Security

Copy
-- Create tables with proper constraints
CREATE TABLE projects (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  name TEXT NOT NULL CHECK (char_length(name) BETWEEN 1 AND 200),
  description TEXT,
  owner_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tasks (
  id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
  project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'todo' CHECK (status IN ('todo', 'in_progress', 'done')),
  assignee_id UUID REFERENCES auth.users(id),
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- RLS Policies: Users can only see their own projects
CREATE POLICY "Users see own projects"
  ON projects FOR SELECT
  USING (owner_id = auth.uid());

CREATE POLICY "Users create own projects"
  ON projects FOR INSERT
  WITH CHECK (owner_id = auth.uid());

CREATE POLICY "Users update own projects"
  ON projects FOR UPDATE
  USING (owner_id = auth.uid());

-- Tasks inherit access from parent project
CREATE POLICY "Users see tasks in their projects"
  ON tasks FOR SELECT
  USING (
    EXISTS (
      SELECT 1 FROM projects WHERE projects.id = tasks.project_id AND projects.owner_id = auth.uid()
    )
  );

-- Indexes for performance
CREATE INDEX idx_projects_owner ON projects(owner_id);
CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_status ON tasks(project_id, status);

Core Concepts

Supabase Features

Feature	Description	Use Case
Database	Managed PostgreSQL with extensions	Data storage, queries
Auth	JWT-based auth with social providers	User management
RLS	Row Level Security policies	Data isolation
Realtime	WebSocket subscriptions to DB changes	Live updates
Storage	S3-compatible file storage with RLS	File uploads
Edge Functions	Deno-based serverless functions	Custom server logic
Vector	pgvector for AI/ML embeddings	Semantic search

Row Level Security Patterns

Pattern	Description	Example
Owner Access	Users access only their own rows	owner_id = auth.uid()
Role-Based	Access based on user role in metadata	auth.jwt() ->> 'role' = 'admin'
Team-Based	Access via team membership join	EXISTS (SELECT FROM team_members WHERE ...)
Public Read	Anyone can read, owners can write	SELECT without auth check
Time-Based	Access within a time window	created_at > NOW() - INTERVAL '30 days'

Copy
// Supabase client usage patterns
import { createClient } from '@supabase/supabase-js';
import type { Database } from './database.types';

const supabase = createClient<Database>(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
);

// Typed queries with generated types
const { data: projects, error } = await supabase
  .from('projects')
  .select('*, tasks(count)')
  .eq('owner_id', userId)
  .order('created_at', { ascending: false });

// Real-time subscriptions
const channel = supabase
  .channel('tasks-changes')
  .on('postgres_changes', {
    event: '*',
    schema: 'public',
    table: 'tasks',
    filter: `project_id=eq.${projectId}`,
  }, (payload) => {
    console.log('Change:', payload);
  })
  .subscribe();

// File upload with RLS
const { data, error } = await supabase.storage
  .from('avatars')
  .upload(`${userId}/avatar.png`, file, {
    upsert: true,
    contentType: 'image/png',
  });

Configuration

Parameter	Description	Default
rls_enabled	Enable Row Level Security by default	true
realtime_enabled	Enable realtime subscriptions	true
auth_providers	Social auth providers	["email", "google"]
jwt_expiry	Access token expiry	3600 (1 hour)
storage_limit	Max upload file size	50MB
db_pool_size	Connection pool size	15

Best Practices

1. Always enable RLS on every table, even if policies are permissive — Tables without RLS enabled are accessible to any authenticated user via the client SDK. Enable RLS first, then add policies. A table with RLS enabled and no policies denies all access by default — the safe starting point.

2. Generate TypeScript types from your database schema — Run supabase gen types typescript to create type-safe database types. This catches column name typos, type mismatches, and missing fields at compile time.

3. Use database functions for complex RLS policies — Instead of inline SQL in policies, create SECURITY DEFINER functions for complex access checks (team membership, role hierarchies). This keeps policies readable and the logic reusable.

4. Implement optimistic updates with real-time fallback — Update the UI immediately on user action, then let the real-time subscription confirm or correct the state. This creates a responsive feel while ensuring data consistency.

5. Use database triggers for updated_at columns — Create a trigger function that sets updated_at = NOW() on every UPDATE. This ensures consistency regardless of which client or service modifies the record.

Common Issues

RLS policies cause "permission denied" errors despite correct auth. Verify that auth.uid() returns the expected value by checking the JWT. Common issues: using the service role key instead of the anon key (bypasses RLS), expired tokens, or policies referencing the wrong column for user ID.

Real-time subscriptions stop receiving events. Check that the table has REPLICA IDENTITY FULL set (required for UPDATE/DELETE events). Verify the channel is properly subscribed and handle reconnection. Supabase real-time has connection limits per project — consolidate channels where possible.

Database queries are slow despite small data volumes. Check for missing indexes on columns used in WHERE, JOIN, and ORDER BY clauses. RLS policies that use subqueries (EXISTS) need indexes on the join columns. Use EXPLAIN ANALYZE in the Supabase SQL editor to identify slow query plans.