Production-grade database operations and schema design for Supabase/PostgreSQL. Master Row Level Security, query optimization, migrations, indexing strategies, and ready-to-use schemas for users, orgs, projects, leads, pipelines, and audit logs. Deep coverage of RLS policies, performance tuning, backup patterns, and Edge Function database patterns. Write secure, scalable Postgres applications with confidence.
Supabase provides a PostgreSQL database with first-class JavaScript/TypeScript support. This skill covers the complete lifecycle: schema design, migrations, security (RLS), query patterns, performance optimization, and backup strategies.
Tables: Use snake_case, singular names.
CREATE TABLE users (
id uuid PRIMARY KEY,
email text UNIQUE NOT NULL,
created_at timestamp with time zone DEFAULT now()
);
CREATE TABLE organizations (
id uuid PRIMARY KEY,
name text NOT NULL,
owner_id uuid NOT NULL REFERENCES users(id)
);
Why singular? users is a table of users. A row represents one user. Singular is clearer and aligns with REST conventions.
: snake_case for all columns. Use suffixes for clarity:
_id: foreign key reference_at: timestamp_count: numeric aggregate_status: enum values_data: JSONB columnsUUID vs Serial:
UUID (recommended):
id uuid PRIMARY KEY DEFAULT gen_random_uuid()
Serial (use sparingly):
id bigserial PRIMARY KEY
Always use UUID for user-facing identifiers.
Every table should have audit timestamps:
CREATE TABLE documents (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
title text NOT NULL,
owner_id uuid NOT NULL REFERENCES users(id),
-- Audit timestamps
created_at timestamp with time zone DEFAULT now(),
updated_at timestamp with time zone DEFAULT now(),
deleted_at timestamp with time zone DEFAULT NULL,
CONSTRAINT documents_owner_fk FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE CASCADE
);
-- Trigger to auto-update updated_at
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = now();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER update_documents_updated_at
BEFORE UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();
Soft Deletes: Use deleted_at instead of hard deletes. Allows recovery, maintains referential integrity, supports audit trails.
Create enum types for status fields:
CREATE TYPE membership_role AS ENUM ('owner', 'admin', 'member', 'guest');
CREATE TABLE organization_members (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
user_id uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role membership_role NOT NULL DEFAULT 'member',
created_at timestamp with time zone DEFAULT now(),
CONSTRAINT org_members_unique UNIQUE (organization_id, user_id)
);
Enums prevent invalid states at the database level. Default to a sensible value.
When multiple tables reference a common entity, use a tag column + ID:
CREATE TABLE audit_logs (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
-- Polymorphic: what changed?
entity_type text NOT NULL, -- 'user', 'project', 'document'
entity_id uuid NOT NULL,
action text NOT NULL, -- 'created', 'updated', 'deleted'
changes jsonb,
performed_by uuid NOT NULL REFERENCES users(id),
created_at timestamp with time zone DEFAULT now()
);
CREATE INDEX idx_audit_logs_entity ON audit_logs(organization_id, entity_type, entity_id);
Don't create separate tables for each entity type. One audit table with polymorphic lookups is cleaner and scalable.
Server-side (Node.js, Edge Functions):
import { createClient } from "@supabase/supabase-js";
const supabase = createClient(
process.env.SUPABASE_URL,
process.env.SUPABASE_SERVICE_ROLE_KEY // Full database access
);
Browser client (React, Vue, etc.):
import { createClient } from "@supabase/supabase-js";
const supabase = createClient(
process.env.REACT_APP_SUPABASE_URL,
process.env.REACT_APP_SUPABASE_ANON_KEY // Limited by RLS
);
Key difference:
SERVICE_ROLE_KEY: Bypasses RLS. Never expose to browser.ANON_KEY: Public key. Respects RLS policies.# .env.local
SUPABASE_URL=https://your-project.supabase.co
SUPABASE_SERVICE_ROLE_KEY=eyJhbGc...
SUPABASE_ANON_KEY=eyJhbGc...
NEXT_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGc...
Only variables prefixed with NEXT_PUBLIC_ (or framework equivalent) leak to browser. Keep service role key in .env.local, not .env.
Generate types from your schema automatically:
npx supabase gen types typescript --project-id your-project > types/database.ts
Use in your code:
import { Database } from "@/types/database";
type User = Database["public"]["Tables"]["users"]["Row"];
type InsertUser = Database["public"]["Tables"]["users"]["Insert"];
type UpdateUser = Database["public"]["Tables"]["users"]["Update"];
const user: User = { id: "...", email: "..." };
Generated types match your schema exactly. Update after migrations.
Supabase provides two connection pools:
In supabase/config.toml:
db_url = "postgres://user:[email protected]:6543/postgres"
db_shadow_database_url = "postgres://user:[email protected]:6543/postgres_shadow"
For Edge Functions, always use the transaction pool (Supabase's default).
RLS is mandatory for multi-tenant applications. Without it, any authenticated user can read/write all data.
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
Create a helper to enable RLS:
-- Enable RLS on all tables in public schema
DO $$
DECLARE
row RECORD;
BEGIN
FOR row IN
SELECT tablename FROM pg_tables WHERE schemaname = 'public'
LOOP
EXECUTE 'ALTER TABLE ' || row.tablename || ' ENABLE ROW LEVEL SECURITY;';
END LOOP;
END $$;
Pattern 1: User owns row
-- Users can only read their own profile
CREATE POLICY "users_select_own"
ON users
FOR SELECT
USING (auth.uid() = id);
-- Users can only update their own profile
CREATE POLICY "users_update_own"
ON users
FOR UPDATE
USING (auth.uid() = id)
WITH CHECK (auth.uid() = id);
Pattern 2: Org membership
-- Users can read projects in organizations they're members of
CREATE POLICY "projects_select_org_members"
ON projects
FOR SELECT
USING (
organization_id IN (
SELECT organization_id
FROM organization_members
WHERE user_id = auth.uid()
)
);
Pattern 3: Public read, authenticated write
CREATE POLICY "documents_select_public"
ON documents
FOR SELECT
USING (is_public = true OR owner_id = auth.uid());
CREATE POLICY "documents_insert_auth"
ON documents
FOR INSERT
WITH CHECK (owner_id = auth.uid());
Pattern 4: Admin override
-- Admins can access everything in their org
CREATE POLICY "projects_admin_access"
ON projects
FOR ALL
USING (
(SELECT role FROM organization_members
WHERE organization_id = projects.organization_id
AND user_id = auth.uid()) = 'admin'
);
Pattern 5: Nested relation access
-- Access tasks through project membership
CREATE POLICY "tasks_access_via_project"
ON tasks
FOR SELECT
USING (
project_id IN (
SELECT id FROM projects
WHERE organization_id IN (
SELECT organization_id FROM organization_members
WHERE user_id = auth.uid()
)
)
);
Test policies before deploying:
-- Set a user context
SET "request.jwt.claims" = '{"sub": "user-uuid-here"}';
-- Test SELECT
SELECT * FROM projects; -- Should only return user's projects
-- Test INSERT
INSERT INTO projects (name, organization_id)
VALUES ('Test', 'org-uuid')
RETURNING *;
-- Reset context
RESET "request.jwt.claims";
In JavaScript:
// Switch auth context
const { data, error } = await supabase
.from("projects")
.select("*");
// If no RLS policies match, returns []
Mistake 1: Forgetting deleted_at filter
-- WRONG: Deleted records still visible
CREATE POLICY "projects_select"
ON projects
FOR SELECT
USING (owner_id = auth.uid());
-- RIGHT: Exclude soft-deleted rows
CREATE POLICY "projects_select"
ON projects
FOR SELECT
USING (
owner_id = auth.uid()
AND deleted_at IS NULL
);
Mistake 2: N+1 subqueries in policies
-- SLOW: Subquery runs for every row
CREATE POLICY "tasks_slow"
ON tasks
FOR SELECT
USING (
project_id IN (
SELECT id FROM projects
WHERE organization_id IN (
SELECT organization_id FROM organization_members
WHERE user_id = auth.uid()
)
)
);
-- FASTER: Use CTE and index
CREATE POLICY "tasks_fast"
ON tasks
FOR SELECT
USING (
EXISTS (
SELECT 1 FROM projects p
JOIN organization_members om
ON p.organization_id = om.organization_id
WHERE p.id = tasks.project_id
AND om.user_id = auth.uid()
)
);
Mistake 3: Updating columns without checking permissions
-- WRONG: Any user can bypass permissions via UPDATE
CREATE POLICY "projects_update"
ON projects
FOR UPDATE
USING (owner_id = auth.uid())
WITH CHECK (true); -- INSECURE
-- RIGHT: Check permissions on both old and new data
CREATE POLICY "projects_update"
ON projects
FOR UPDATE
USING (owner_id = auth.uid())
WITH CHECK (owner_id = auth.uid());
Create migration files in supabase/migrations/:
Naming convention:
YYYY-MM-DD_HHmmss_descriptive_name.sql
Example migration:
-- File: supabase/migrations/2026-04-02_120000_create_projects_table.sql
-- Up migration
CREATE TABLE projects (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
name text NOT NULL,
description text,
created_at timestamp with time zone DEFAULT now(),
updated_at timestamp with time zone DEFAULT now(),
deleted_at timestamp with time zone DEFAULT NULL,
CONSTRAINT projects_unique_name_per_org UNIQUE (organization_id, name)
);
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE INDEX idx_projects_organization ON projects(organization_id)
WHERE deleted_at IS NULL;
CREATE POLICY "projects_select_org_members"
ON projects
FOR SELECT
USING (
organization_id IN (
SELECT organization_id FROM organization_members
WHERE user_id = auth.uid() AND deleted_at IS NULL
)
AND deleted_at IS NULL
);
Supabase auto-handles up migrations. For manual reversions:
-- File: supabase/migrations/2026-04-02_120000_create_projects_table.sql
-- Up
CREATE TABLE projects (...);
-- Down (commented for safety)
/*
DROP POLICY IF EXISTS "projects_select_org_members" ON projects;
DROP INDEX IF EXISTS idx_projects_organization;
DROP TABLE IF EXISTS projects;
*/
To revert in development:
supabase db reset # Applies all migrations from scratch
Create seed files in supabase/seed.sql:
-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Insert test user
INSERT INTO users (id, email)
VALUES
('11111111-1111-1111-1111-111111111111', '[email protected]')
ON CONFLICT DO NOTHING;
-- Insert test org
INSERT INTO organizations (id, name, owner_id)
VALUES
('22222222-2222-2222-2222-222222222222', 'Test Org', '11111111-1111-1111-1111-111111111111')
ON CONFLICT DO NOTHING;
-- Insert membership
INSERT INTO organization_members (organization_id, user_id, role)
VALUES
('22222222-2222-2222-2222-222222222222', '11111111-1111-1111-1111-111111111111', 'owner')
ON CONFLICT DO NOTHING;
Run with:
supabase db push
supabase seed restore
Track schema version in a table:
CREATE TABLE schema_versions (
id serial PRIMARY KEY,
version text NOT NULL UNIQUE,
applied_at timestamp with time zone DEFAULT now()
);
INSERT INTO schema_versions (version) VALUES ('1.0.0');
Query before running migrations:
const { data } = await supabase
.from("schema_versions")
.select("version")
.order("applied_at", { ascending: false })
.limit(1);
console.log("Current schema version:", data?.[0]?.version);
Simple join:
const { data: projects } = await supabase
.from("projects")
.select("*, organization:organizations(id, name)")
.eq("organization_id", orgId);
Deep nesting:
const { data: tasks } = await supabase
.from("tasks")
.select(`
id,
title,
project:projects(
id,
name,
organization:organizations(id, name)
),
assignee:users(id, email)
`)
.eq("project_id", projectId);
Many-to-many through junction table:
CREATE TABLE project_tags (
project_id uuid NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
tag_id uuid NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (project_id, tag_id)
);
-- Query
SELECT p.*, array_agg(t.name) as tags
FROM projects p
LEFT JOIN project_tags pt ON p.id = pt.project_id
LEFT JOIN tags t ON pt.tag_id = t.id
WHERE p.id = $1
GROUP BY p.id;
// Equals
.eq("status", "active")
// Not equals
.neq("status", "deleted")
// Greater/less than
.gt("price", 100)
.lt("created_at", "2026-01-01")
// IN list
.in("id", ["id1", "id2", "id3"])
// Text search
.ilike("email", "%@example.com%")
// AND (multiple filters)
.eq("status", "active")
.eq("organization_id", orgId)
// OR (use raw SQL)
const { data } = await supabase
.from("projects")
.select("*")
.or("owner_id.eq." + userId + ",is_public.eq.true");
const PAGE_SIZE = 20;
const pageNum = 1; // 1-indexed
const from = (pageNum - 1) * PAGE_SIZE;
const to = from + PAGE_SIZE - 1;
const { data: projects, count } = await supabase
.from("projects")
.select("*", { count: "exact" })
.range(from, to);
const totalPages = Math.ceil((count || 0) / PAGE_SIZE);
// Fetch next page using last row's ID
const pageSize = 20;
const { data: projects } = await supabase
.from("projects")
.select("id, name, created_at")
.order("created_at", { ascending: false })
.limit(pageSize + 1); // Fetch extra to detect if more exists
const hasMore = projects.length > pageSize;
const items = projects.slice(0, pageSize);
const lastCursor = items[items.length - 1]?.id;
// Next request
const { data: nextProjects } = await supabase
.from("projects")
.select("id, name, created_at")
.lt("created_at", lastCursor)
.order("created_at", { ascending: false })
.limit(pageSize);
Enable full-text search:
ALTER TABLE documents ADD COLUMN search_vector tsvector;
CREATE INDEX idx_documents_search ON documents USING gin(search_vector);
CREATE TRIGGER documents_search_update
BEFORE INSERT OR UPDATE ON documents
FOR EACH ROW
EXECUTE FUNCTION tsvector_update_trigger(search_vector, 'pg_catalog.english', title, content);
Query:
const { data: results } = await supabase
.from("documents")
.select("id, title")
.textSearch("search_vector", "postgres & database");
-- Count
SELECT COUNT(*) as total_projects
FROM projects
WHERE organization_id = $1;
-- Sum
SELECT SUM(price) as total_revenue
FROM orders
WHERE status = 'completed';
-- Group by
SELECT
status,
COUNT(*) as count,
AVG(price) as avg_price
FROM orders
GROUP BY status;
-- Window functions (running totals, ranks)
SELECT
name,
price,
SUM(price) OVER (ORDER BY created_at) as running_total,
ROW_NUMBER() OVER (ORDER BY price DESC) as rank
FROM products;
In Supabase (use RPC or raw SQL for aggregations):
const { data } = await supabase.rpc("count_projects", { org_id: orgId });
Define a function in the database:
CREATE OR REPLACE FUNCTION count_projects(org_id uuid)
RETURNS bigint AS $$
SELECT COUNT(*)::bigint
FROM projects
WHERE organization_id = org_id
AND deleted_at IS NULL;
$$ LANGUAGE sql STABLE;
Call from JavaScript:
const { data: count } = await supabase.rpc("count_projects", { org_id: orgId });
console.log("Projects:", count);
Index on:
Don't index:
B-tree (default):
CREATE INDEX idx_users_email ON users(email);
Used for: equality, range queries (=, <, >, IN).
Hash:
CREATE INDEX idx_users_email_hash ON users USING hash(email);
Used for: exact match only, rarely needed.
GIN (Generalized Inverted Index):
CREATE INDEX idx_documents_search ON documents USING gin(search_vector);
CREATE INDEX idx_documents_tags ON documents USING gin(tags);
Used for: full-text search, array containment, JSONB operators.
GiST (Generalized Search Tree):
CREATE INDEX idx_points_spatial ON points USING gist(location);
Used for: spatial data, range types, custom data types.
BRIN (Block Range Index):
CREATE INDEX idx_events_time ON events USING brin(created_at);
Used for: very large tables with naturally sorted data, compact.
Index multiple columns together:
CREATE INDEX idx_org_members_lookup ON organization_members(organization_id, user_id);
Order matters: most selective columns first.
Index only rows matching a condition:
-- Only index active projects (exclude soft-deletes)
CREATE INDEX idx_projects_active ON projects(organization_id)
WHERE deleted_at IS NULL;
Reduces index size, faster inserts.
Include non-indexed columns to avoid table lookups:
-- Index includes name so query doesn't need table lookup
CREATE INDEX idx_users_email_covering ON users(email) INCLUDE (name, avatar_url);
Query can satisfy entirely from index: "index-only scan".
Analyze query performance:
EXPLAIN ANALYZE
SELECT p.*, COUNT(t.id) as task_count
FROM projects p
LEFT JOIN tasks t ON p.id = t.project_id
WHERE p.organization_id = 'org-uuid'
GROUP BY p.id;
Output shows:
Look for:
ANALYZE table_name)Ready-to-use schemas included in references/schema-recipes.md.
Key schemas:
Copy and adapt to your needs.
// supabase/functions/process-order/index.ts
import { createClient } from "https://esm.sh/@supabase/supabase-js@2";
Deno.serve(async (req) => {
// Service role key: bypass RLS
const supabase = createClient(
Deno.env.get("SUPABASE_URL")!,
Deno.env.get("SUPABASE_SERVICE_ROLE_KEY")!
);
// Insert order
const { data: order, error } = await supabase
.from("orders")
.insert({ user_id: userId, total: 100 })
.select()
.single();
if (error) {
return new Response(JSON.stringify({ error: error.message }), {
status: 400,
});
}
return new Response(JSON.stringify(order), { status: 200 });
});
Deploy:
supabase functions deploy process-order
Service role key bypasses RLS. Use for:
Never expose to browser or public APIs.
PostgreSQL transactions in Edge Functions:
const { data: result, error } = await supabase.rpc("create_order_with_items", {
user_id: userId,
items: [
{ product_id: "p1", quantity: 2 },
{ product_id: "p2", quantity: 1 },
],
});
Define the function:
CREATE OR REPLACE FUNCTION create_order_with_items(
user_id uuid,
items jsonb
)
RETURNS uuid AS $$
DECLARE
order_id uuid;
item jsonb;
BEGIN
-- Create order
INSERT INTO orders (user_id, total)
VALUES (user_id, 0)
RETURNING id INTO order_id;
-- Add items (atomic)
FOR item IN SELECT * FROM jsonb_array_elements(items)
LOOP
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (order_id, (item->>'product_id')::uuid, (item->>'quantity')::int);
END LOOP;
RETURN order_id;
EXCEPTION WHEN OTHERS THEN
-- Rollback handled automatically
RAISE;
END;
$$ LANGUAGE plpgsql;
Supabase manages connection pooling. For Edge Functions:
Monitor in Supabase dashboard: Settings → Database → Connection pooling.
N+1 Prevention:
Bad:
const projects = await supabase
.from("projects")
.select("id, name");
for (const project of projects) {
const tasks = await supabase
.from("tasks")
.select("id, title")
.eq("project_id", project.id); // One query per project!
}
Good:
const projectIds = projects.map(p => p.id);
const { data: tasks } = await supabase
.from("tasks")
.select("id, title, project_id")
.in("project_id", projectIds); // Single query
Cache expensive queries:
CREATE MATERIALIZED VIEW project_stats AS
SELECT
p.id,
p.name,
COUNT(t.id) as task_count,
COUNT(t.id) FILTER (WHERE t.status = 'completed') as completed_count,
AVG(EXTRACT(EPOCH FROM (t.completed_at - t.created_at))) / 3600 as avg_hours
FROM projects p
LEFT JOIN tasks t ON p.id = t.project_id
WHERE p.deleted_at IS NULL
GROUP BY p.id, p.name;
CREATE INDEX idx_project_stats_id ON project_stats(id);
-- Refresh when data changes
REFRESH MATERIALIZED VIEW CONCURRENTLY project_stats;
Query materialized views like tables:
const { data: stats } = await supabase
.from("project_stats")
.select("*")
.eq("id", projectId);
Refresh on-demand or with cron.
Enable the extension:
CREATE EXTENSION IF NOT EXISTS pg_cron;
Schedule tasks:
-- Refresh materialized views every hour
SELECT cron.schedule('refresh-project-stats', '0 * * * *',
'REFRESH MATERIALIZED VIEW CONCURRENTLY project_stats');
-- Clean up old audit logs every week
SELECT cron.schedule('cleanup-old-logs', '0 3 * * 0',
'DELETE FROM audit_logs WHERE created_at < now() - INTERVAL ''1 year''');
-- Send daily digest emails
SELECT cron.schedule('send-daily-digest', '0 6 * * *',
'SELECT send_daily_digest()');
View scheduled jobs:
SELECT * FROM cron.job;
Supabase maintains 7-day PITR for Pro/Team plans.
Recover to a specific timestamp:
# In Supabase dashboard: Settings → Backups → Restore
# Select date/time and restore to new project
Or use Postgres directly:
# Get WAL archive
aws s3 cp s3://supabase-backups/project_id/ ./backups --recursive
# Restore to specific time
pg_basebackup -D ./backup_data -h backup.supabase.co -U postgres
Export full database:
pg_dump postgres://user:[email protected]/postgres > backup.sql
Export specific schema:
pg_dump -n public postgres://user:[email protected]/postgres > schema.sql
Export data only (no DDL):
pg_dump --data-only postgres://user:[email protected]/postgres > data.sql
Export with custom format (faster, compressible):
pg_dump -F custom postgres://user:[email protected]/postgres > backup.dump
pg_restore backup.dump > restored.sql
Restore:
psql postgres://user:[email protected]/postgres < backup.sql
Export table as CSV:
psql postgres://user:[email protected]/postgres -c "COPY projects TO STDOUT CSV HEADER" > projects.csv
Or in application:
const { data: projects } = await supabase
.from("projects")
.select("*")
.then(({ data }) => {
const csv = [Object.keys(data[0]).join(',')];
csv.push(...data.map(row => Object.values(row).join(',')));
return csv.join('\n');
});
Enable verbose logging:
ALTER SYSTEM SET log_rls_statements = on;
SELECT pg_reload_conf();
Check logs:
supabase logs --follow
Test with different users:
// Simulate user context
const { data: myProjects } = await supabase
.from("projects")
.select("*");
// Simulate different user
const otherUser = createClient(url, anonKey);
const { data: theirProjects } = await otherUser
.from("projects")
.select("*");
// Should be empty (RLS blocks access)
console.assert(theirProjects?.length === 0);
Error: Row level security policy violated
auth.uid() matches expected user IDError: Foreign key constraint violated
SELECT * FROM orders WHERE user_id = $1 AND user_id NOT IN (SELECT id FROM users)Error: Unique constraint violated
ON CONFLICT DO UPDATE for upserts: INSERT INTO users (id, email) VALUES ($1, $2) ON CONFLICT (id) DO UPDATE SET email = $2Slow queries
EXPLAIN ANALYZE to identify missing indexesSELECT count(*) FROM pg_stat_activity;Out of disk space
SELECT pg_database_size('postgres'); in Dashboard → SQL Editorcreated_at, updated_at, deleted_at timestampsdeleted_at filter<sub>Open RX by CutTheChexx — The Prescription.</sub>