Row Level Security (RLS)

RLS policies run as implicit WHERE clauses on every query. Supabase exposes auth.uid() and auth.jwt() as helper functions inside policies.

Extensions

Supabase pre-installs many popular Postgres extensions and lets you enable additional SQL extensions via the dashboard or SQL editor. Upgrading an extension may require initiating a software upgrade from the Infrastructure Settings. Use extensions when they materially simplify an implementation (e.g., PostGIS, pgcrypto, pg_ivm for incremental view maintenance). (Source: Supabase extensions docs; pg_ivm noted in Postgres signals)

Migration safety

Migrations in production must be backwards-compatible. Never rename a column — add a new one, backfill, switch code, then drop the old column.

Workflow

Step 1 — Define schema with migrations

-- supabase/migrations/001_create_projects.sql
CREATE TABLE projects (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  owner_id    uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name        text NOT NULL CHECK (char_length(name) BETWEEN 1 AND 200),
  slug        text NOT NULL UNIQUE,
  description text,
  is_archived boolean NOT NULL DEFAULT false,
  created_at  timestamptz NOT NULL DEFAULT now(),
  updated_at  timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX idx_projects_owner ON projects (owner_id);
CREATE INDEX idx_projects_slug ON projects (slug);

-- Updated-at trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_projects_updated_at
  BEFORE UPDATE ON projects
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Step 2 — Add RLS policies

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Users can read their own projects
CREATE POLICY "Users read own projects"
  ON projects FOR SELECT
  USING (owner_id = auth.uid());

-- Users can insert projects they own
CREATE POLICY "Users insert own projects"
  ON projects FOR INSERT
  WITH CHECK (owner_id = auth.uid());

-- Users can update their own non-archived projects
CREATE POLICY "Users update own projects"
  ON projects FOR UPDATE
  USING (owner_id = auth.uid() AND NOT is_archived)
  WITH CHECK (owner_id = auth.uid());

-- Users can soft-delete (archive) their own projects
CREATE POLICY "Users archive own projects"
  ON projects FOR UPDATE
  USING (owner_id = auth.uid())
  WITH CHECK (owner_id = auth.uid() AND is_archived = true);

Step 3 — Optimize with targeted indexes

-- Partial index for active (non-archived) projects
CREATE INDEX idx_projects_active
  ON projects (owner_id, created_at DESC)
  WHERE NOT is_archived;

-- GIN index for full-text search on name + description
CREATE INDEX idx_projects_search
  ON projects
  USING GIN (to_tsvector('english', coalesce(name, '') || ' ' || coalesce(description, '')));

Step 4 — Query with the Supabase client

import { createClient } from "@supabase/supabase-js";
import type { Database } from "@/lib/database.types";

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

async function listActiveProjects(userId: string) {
  const { data, error } = await supabase
    .from("projects")
    .select("id, name, slug, created_at")
    .eq("owner_id", userId)
    .eq("is_archived", false)
    .order("created_at", { ascending: false })
    .limit(50);

  if (error) throw new Error(`listActiveProjects: ${error.message}`);
  return data;
}

Step 5 — Run and verify migrations

# Local development
supabase db reset
supabase db diff --use-migra

# Production (via CI)
supabase db push --linked

Examples

Multi-tenant RLS with org membership

CREATE TABLE org_members (
  org_id  uuid REFERENCES orgs(id) ON DELETE CASCADE,
  user_id uuid REFERENCES auth.users(id) ON DELETE CASCADE,
  role    text NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  PRIMARY KEY (org_id, user_id)
);

CREATE POLICY "Org members read org data"
  ON org_data FOR SELECT
  USING (
    org_id IN (
      SELECT org_id FROM org_members WHERE user_id = auth.uid()
    )
  );

Safe column migration (zero-downtime)

-- Step 1: Add new column
ALTER TABLE projects ADD COLUMN display_name text;

-- Step 2: Backfill (batched to avoid long locks)
UPDATE projects SET display_name = name WHERE display_name IS NULL;

-- Step 3: (deploy code that reads display_name with fallback to name)

-- Step 4: Make non-null after code is deployed
ALTER TABLE projects ALTER COLUMN display_name SET NOT NULL;
ALTER TABLE projects ALTER COLUMN display_name SET DEFAULT '';

-- Step 5: (deploy code that only reads display_name, drop old references)

Decision tree

New table or column?
├── Add migration file in supabase/migrations/
├── Need per-user isolation? → Add RLS policy
├── Queried frequently by a non-PK column? → Add index
├── Column holds large text searched often? → Add GIN index with to_tsvector
└── Column needs uniqueness? → Add UNIQUE constraint in migration

Slow query?
├── Run EXPLAIN ANALYZE
├── Sequential scan on large table? → Add index on filter/join columns
├── Index exists but not used? → Check for type mismatch or function wrapping
├── Lots of dead tuples? → Run VACUUM ANALYZE
└── Still slow? → Consider materialized view or denormalization

Edge cases and gotchas

• RLS is bypassed by service_role key: The service_role key skips all RLS. Never expose it to the client. Use anon key in browsers.
• Transaction-mode pooling breaks prepared statements: PgBouncer or Supavisor running in transaction mode will break server-side prepared statements and some ORMs' expectations (e.g., asyncpg). Use session mode or application-side workarounds when your driver depends on prepared statements. (Source: Supavisor FAQ and community reports)
• Migrations with locks: ALTER TABLE ... ADD COLUMN with a DEFAULT on Postgres < 11 locks the table. On 11+, it's instant for non-volatile defaults. ADD COLUMN NOT NULL without a default still locks.
• UUID primary keys: Use gen_random_uuid() (Postgres 13+) over uuid_generate_v4() (requires extension). UUIDs are great for distributed systems but make range scans slower than integer IDs.
• Cascading deletes: ON DELETE CASCADE is convenient but dangerous on large tables — it can lock rows across multiple tables. Prefer soft deletes for user-facing data.
• Real-time depends on RLS: Supabase Realtime subscriptions respect RLS policies. If RLS is misconfigured, clients receive no events — not an error, just silence.
• Supabase Log Drains (Pro): Supabase now offers Log Drains on the Pro tier so you can export Postgres, Auth, Storage, Edge Functions, Realtime, and API logs to Datadog, Sentry, Grafana Loki, Axiom, S3 or a custom endpoint. This is useful to correlate database query errors, auth failures, and Edge Function traces in your existing observability tools. (Source: Supabase blog/changelog)
• Supabase CLI diff limitations: supabase db diff may miss some changes (triggers, functions). Always review generated SQL before applying.

Evaluation criteria

Research-backed changes

This refresh pulled recent signals and updated guidance for poolers, observability, and extensions.

Fresh signals incorporated

• Supabase launched Log Drains on Pro to export Postgres and service logs to external observability tools (Supabase blog, Mar 5, 2026).
• Supabase's Supavisor connection-pooler docs and FAQ clarify port/mode semantics (transaction vs session) and caveats for transaction-mode pooling (Supabase docs, Feb 2026).
• Postgres ecosystem signals (pg_ivm 1.14 and other extension updates) indicate growing availability of incremental view maintenance and other extensions; Supabase supports many extensions and provides an extensions dashboard to enable them.

What changed

• Rewrote the Connection pooling section to include Supavisor and clarify port/mode behavior and impacts on prepared statements.
• Added an Extensions subsection explaining extension upgrades and where to enable them.
• Added Log Drains as an operational note under gotchas/observability.

Next edits

• Tighten one ambiguous instruction.
• Swap in a higher-signal source if Supabase publishes a pooler migration guide or PgBouncer→Supavisor migration docs.