Supabase Database Guide
Schema design, SQL migrations, RLS patterns, indexing strategy, real-time subscriptions, and the production best practices 4Byte uses on every SaaS build.
What the Supabase Database Actually Is
The Supabase database is a dedicated PostgreSQL instance — not a hosted abstraction or a subset of PostgreSQL. Every feature of PostgreSQL is available: full SQL, foreign keys, triggers, stored procedures, views, extensions, and advanced data types. On top of that, Supabase adds an auto-generated REST API via PostgREST, real-time subscriptions via WebSockets, and Row Level Security integration with the auth system.
The result is a backend where the database is the API — reducing the amount of backend code needed for most SaaS products to near zero. 4Byte uses this architecture to ship SaaS MVPs in 28 days without sacrificing production quality.
▸ Core Features
6 Database Capabilities That Matter for SaaS
Full PostgreSQL
Not a subset or abstraction — every Supabase project gets a complete PostgreSQL instance with full SQL support, extensions, stored procedures, triggers, and views.
- Full SQL — JOINs, CTEs, window functions
- Extensions: pgvector, uuid-ossp, pg_trgm
- Stored procedures and triggers
- Views and materialized views
Auto-Generated REST API
PostgREST automatically generates a REST API from the database schema — every table and view becomes an API endpoint without writing any backend code.
- Instant REST endpoints per table
- Filtering, sorting, pagination built in
- Respects RLS policies automatically
- GraphQL via pg_graphql extension
Real-Time Subscriptions
Subscribe to database changes via WebSockets — the client receives events when rows are inserted, updated, or deleted, enabling live dashboards and collaborative features.
- INSERT / UPDATE / DELETE events
- Filter by table, row, or column value
- Broadcast for presence features
- Enabled per-table via Replication
Row Level Security
PostgreSQL RLS policies enforce data access rules at the database level — no application-layer filtering needed. Policies reference auth.uid() to scope access to the current user.
- Enforced at database level
- auth.uid() for user-scoped policies
- Per-operation policies (SELECT / INSERT)
- Service role key bypasses RLS (server only)
Database Functions
PostgreSQL functions (stored procedures) run complex business logic directly in the database — enabling atomic multi-table operations, computed aggregations, and batch processing.
- PL/pgSQL for complex logic
- Called via supabase.rpc()
- Atomic transactions across tables
- Better performance than multiple queries
Full-Text & Vector Search
pg_trgm and tsvector enable powerful full-text search, while the pgvector extension powers semantic vector search — making Supabase a complete solution for AI-enhanced search features.
- tsvector for full-text search
- pg_trgm for fuzzy matching
- pgvector for AI embeddings / RAG
- GIN and GiST indexes for speed
▸ Schema Patterns
3 Production Schema Patterns 4Byte Uses on Every SaaS
Users & Profiles Pattern
Supabase manages the auth.users table internally. The standard pattern is a public.profiles table with a foreign key to auth.users, storing additional user data like display name, avatar, and preferences.
-- Profiles table linked to auth.users
CREATE TABLE public.profiles (
id UUID REFERENCES auth.users(id) ON DELETE CASCADE PRIMARY KEY,
display_name TEXT,
avatar_url TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Auto-create profile on new user signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.profiles (id, display_name)
VALUES (NEW.id, NEW.raw_user_meta_data->>'full_name');
RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION handle_new_user();Always use ON DELETE CASCADE on the foreign key to auth.users — without it, deleting a user from auth leaves orphaned rows in your profiles table.
Multi-Tenant Organisations Pattern
For B2B SaaS, create an organisations table and a members junction table linking users to organisations. RLS policies use subqueries against the members table to scope all data to the current user's organisation.
-- Organisations and membership
CREATE TABLE public.organisations (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE TABLE public.members (
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
org_id UUID REFERENCES organisations(id) ON DELETE CASCADE,
user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
role TEXT DEFAULT 'member',
UNIQUE(org_id, user_id)
);
-- RLS: users only see their own org's data
CREATE POLICY "Org members see org data"
ON projects FOR SELECT
USING (
org_id IN (
SELECT org_id FROM members
WHERE user_id = auth.uid()
)
);The members subquery runs on every RLS-protected query. Add an index on members(user_id) and members(org_id) to keep this fast as the table grows.
Soft Delete Pattern
Instead of hard-deleting rows, add a deleted_at timestamp column. Rows with a non-null deleted_at are considered deleted. RLS policies and views filter them out automatically.
-- Add soft delete column
ALTER TABLE public.projects
ADD COLUMN deleted_at TIMESTAMPTZ;
-- View that excludes deleted rows
CREATE VIEW active_projects AS
SELECT * FROM projects WHERE deleted_at IS NULL;
-- Soft delete function
CREATE OR REPLACE FUNCTION soft_delete_project(project_id UUID)
RETURNS VOID AS $$
BEGIN
UPDATE projects SET deleted_at = NOW()
WHERE id = project_id AND auth.uid() = owner_id;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;Always include deleted_at IS NULL in every RLS policy and query filter — forgetting it in even one place can expose soft-deleted data to users.
▸ Migrations
The Production Migration Workflow
How 4Byte manages database schema changes across local, staging, and production environments.
Create migration file
supabase migration new create_projects_table generates a timestamped SQL file in supabase/migrations/
Write SQL
Add CREATE TABLE, ALTER TABLE, CREATE POLICY, and CREATE INDEX statements to the migration file.
Apply locally
supabase db reset applies all migrations to the local Docker database — verifying the SQL is valid before pushing.
Generate types
supabase gen types typescript > types/supabase.ts regenerates TypeScript types from the updated schema.
Push to production
supabase db push applies pending migrations to the remote production database — atomic and version-controlled.
Never use the Supabase Table Editor for production schema changes. Every change lives in a SQL migration file committed to Git — reproducible, reviewable, and rollbackable. The Table Editor is for exploration only.
▸ Indexing
Which Columns to Always Index
Unindexed queries are the #1 cause of slow Supabase applications at scale. These are the columns 4Byte indexes on every production database.
▸ Why 4Byte
We've Designed Production Supabase Schemas at Scale
Every schema pattern in this guide comes from real SaaS products 4Byte has built, shipped, and maintained — including multi-tenant B2B applications with complex RLS policies, vector search features, and real-time dashboards.
If you're starting a new SaaS project and want the database architecture right from day one, our free strategy call is where we review your data model and recommend the right schema design.
Currently accepting new SaaS projects
Free strategy call · Response in ≤ 4 hours · No obligation
▸ FAQ
Supabase Database — Common Questions
What database does Supabase use?+
Supabase uses PostgreSQL — the most trusted open-source relational database in the world. Every Supabase project gets a dedicated PostgreSQL instance with full SQL support, including foreign keys, joins, views, stored procedures, triggers, and advanced data types.
How do I run database migrations in Supabase?+
Supabase recommends using the Supabase CLI with SQL migration files tracked in version control. Each migration is a .sql file in the supabase/migrations directory. Running supabase db push applies pending migrations to the remote database, and supabase db pull syncs remote schema changes back to local files.
How does Supabase generate TypeScript types from the database?+
The Supabase CLI can generate TypeScript types directly from your database schema using the command supabase gen types typescript. This produces a types file where every table, view, and function is typed — enabling type-safe database queries throughout the codebase without manual type definitions.
How do I query the Supabase database from Next.js?+
Use the Supabase JavaScript client — supabase.from("table_name").select("*") for reads, .insert() for creates, .update() for updates, and .delete() for deletes. In Next.js, use the server client in Server Components and API routes, and the browser client in Client Components for real-time or interactive queries.
What is the Supabase Table Editor?+
The Supabase Table Editor is a visual UI in the Supabase dashboard for creating and editing tables, adding columns, viewing data, and running queries. It's useful for exploration and quick edits, but for production schema changes, SQL migration files tracked in version control are the recommended approach.
How do I enable real-time database subscriptions in Supabase?+
Enable Replication for the table in the Supabase dashboard under Database → Replication, then use supabase.channel("channel_name").on("postgres_changes", { event: "*", schema: "public", table: "table_name" }, callback).subscribe() in the client. The callback fires on every INSERT, UPDATE, or DELETE event matching the filter.
▸ Continue Learning
Related Technology Stack Resources
Let's Design Your Database Schema for Production.
Book a free 30-minute call with 4Byte. We'll review your data model and recommend the right schema design for your SaaS — no pressure, no obligation.
Schema Review
Included in the call
28-Day MVP
Avg. delivery time
No Obligation
Zero pressure call