InfraUserv8FreePublic

Supabase Database Patterns

Practical Supabase database patterns: schema, RLS, Supavisor pooling, migrations, extensions, and observability (Log Drains).

LoopVerified6 sources · Updated 6d ago

Run in sandbox

Content

Supabase Database Patterns

Supabase wraps Postgres with auth, real-time subscriptions, storage, and auto-generated APIs. This skill covers the database layer — schema design, RLS, migrations, indexing, and query optimization.

When to use

Designing schema for a new Supabase-backed application
Adding Row Level Security policies to protect data per-user or per-org
Writing migrations that are safe for zero-downtime deployments
Diagnosing slow queries and adding appropriate indexes
Setting up connection pooling for serverless workloads
Optimizing Postgres for high-read, low-write web app patterns
Implementing soft deletes, audit trails, or temporal data patterns

When NOT to use

Graph-shaped data that needs recursive traversals (consider a graph DB)
Time-series at extreme write volume (consider TimescaleDB or ClickHouse)
Full-text search as the primary access pattern (consider a dedicated search engine or a purpose-built BM25/vector service for large collections)
Blob storage (use Supabase Storage or S3, not bytea columns)
When the app only needs a key-value store (use Redis or KV)

Core concepts

Connection pooling

Supabase provides a managed server-side pooler (Supavisor) and exposes connection strings for pooled and direct modes. Understanding modes and ports is essential when designing apps that run on serverless or edge environments.

Key facts (Supavisor docs, last edited 2026-04-08):

← Back to skills

InfraUserv8FreePublic

Supabase Database Patterns

Practical Supabase database patterns: schema, RLS, Supavisor pooling, migrations, extensions, and observability (Log Drains).

LoopVerified6 sources · Updated 6d ago

Run in sandbox

Content

Supabase Database Patterns

When to use

Designing schema for a new Supabase-backed application
Adding Row Level Security policies to protect data per-user or per-org
Writing migrations that are safe for zero-downtime deployments
Diagnosing slow queries and adding appropriate indexes
Setting up connection pooling for serverless workloads
Optimizing Postgres for high-read, low-write web app patterns
Implementing soft deletes, audit trails, or temporal data patterns

When NOT to use

Graph-shaped data that needs recursive traversals (consider a graph DB)
Time-series at extreme write volume (consider TimescaleDB or ClickHouse)
Full-text search as the primary access pattern (consider a dedicated search engine or a purpose-built BM25/vector service for large collections)
Blob storage (use Supabase Storage or S3, not bytea columns)
When the app only needs a key-value store (use Redis or KV)

Core concepts

Connection pooling

Key facts (Supavisor docs, last edited 2026-04-08):

Principles

Criteria	Target
Query P95 latency	< 100 ms for indexed queries
Migration rollback plan	Every migration has a documented reverse
RLS coverage	100% of user-facing tables have RLS enabled
Index bloat	Unused indexes identified and dropped
Connection pool usage	< 80% of pool capacity under peak load
Dead tuple ratio	< 5% (autovacuum tuned)

Every user-facing table must have RLS enabled with at least one policy per operation (SELECT, INSERT, UPDATE, DELETE).

Migrations are append-only files. Never edit a migration that has already been applied.

Use connection pooling (port 6543) for all serverless functions. Direct connections (5432) only for migrations.

Prefer `gen_random_uuid()` for UUID generation — no extension required on Postgres 13+.

Schema design

Use `timestamptz` for all timestamp columns, never `timestamp` (timezone-naive).

Add `created_at` and `updated_at` to every table with a trigger for `updated_at`.

Use CHECK constraints for enum-like columns instead of a separate lookup table when the set is small and stable.

Foreign keys should specify ON DELETE behavior explicitly — CASCADE, SET NULL, or RESTRICT.

RLS patterns

Use `auth.uid()` for direct ownership checks.

For org/team access, join through a membership table inside the policy.

Test RLS with `SET LOCAL role = 'authenticated'; SET LOCAL request.jwt.claim.sub = 'user-uuid';` in psql.

Never use `SECURITY DEFINER` functions to bypass RLS unless absolutely necessary and well-documented.

Indexing

Always index foreign key columns — Postgres does not auto-index them.

Use partial indexes for common filtered queries: `WHERE NOT is_deleted`, `WHERE status = 'active'`.

Monitor with `pg_stat_user_indexes` — drop indexes with zero scans.

Use `CONCURRENTLY` for production index creation to avoid table locks.

Common mistakes

Forgetting to enable RLS on a new table (data is publicly readable via PostgREST).

Using `service_role` key in client-side code.

Running `ALTER TABLE ... ADD COLUMN ... NOT NULL` without a DEFAULT (locks table).

Not indexing columns used in WHERE clauses of RLS policies (policy becomes a seq scan).

Content

Supabase Database Patterns

When to use

When NOT to use

Core concepts

Connection pooling

Content

Supabase Database Patterns

When to use

When NOT to use

Core concepts

Connection pooling

Row Level Security (RLS)

Extensions

Migration safety

Workflow

Step 1 — Define schema with migrations

Step 2 — Add RLS policies

Step 3 — Optimize with targeted indexes

Step 4 — Query with the Supabase client

Step 5 — Run and verify migrations

Examples

Multi-tenant RLS with org membership

Safe column migration (zero-downtime)

Decision tree

Edge cases and gotchas

Postgres features to watch

Edge observability checklist

Evaluation criteria

Research-backed changes

Fresh signals incorporated

What changed

Next edits

Agent docs

Codex — Supabase Database Patterns

Principles

Schema design

RLS patterns

Indexing

Migration safety

Common mistakes

Activity

Automation & run history

Latest refresh trace

Research engine

Sources