---
name: design-supabase-postgres-schema
description: Designs and reviews a Supabase Postgres database the safe and fast way - tables and migrations through the Supabase CLI, row level security on every exposed table, fast RLS policies, the right indexes and query checks with EXPLAIN. Use when creating Supabase tables, writing RLS policies, a Supabase query is slow, or the user asks for Postgres best practices on Supabase.
license: CC-BY-4.0
compatibility: Needs a Supabase project and the Supabase CLI (npx supabase)
metadata:
  category: backend
  tags: "supabase,postgres,security,performance"
  author: johnny-bravo
  version: "1.0"
  updated: "2026-10-31"
  status: published
  aliases: "supabase-postgres-best-practices,supabase-rls,postgres-best-practices"
---

# Design a Supabase Postgres schema

Supabase exposes your `public` tables to the browser through its API. That is fast to build on, but one table without row level security (RLS) can leak every row. This skill sets up tables, security and indexes so the database is both safe and quick.

## When to use
- You are adding tables, columns or relations to a Supabase project.
- You need RLS policies so users only see and change their own data.
- A Supabase query or page is slow, or the Performance Advisor shows warnings.
- You are reviewing a schema or migration before it goes to production.

## Steps
1. **Use migrations, not the dashboard.** Run `npx supabase migration new add_projects`, write the SQL in the new file, and test it locally with `npx supabase db reset`. Apply it with `npx supabase db push`. Every change is then in git and can be reviewed.
2. **Design the tables.**
   - Use `id uuid primary key default gen_random_uuid()` or `bigint generated always as identity`.
   - Add `created_at timestamptz not null default now()`.
   - Link rows to users with `user_id uuid not null references auth.users (id) on delete cascade`, or to a `profiles` table.
   - Use `not null`, `check` and `unique` constraints so bad data cannot get in.
   - Use `text` instead of `varchar(n)` and `timestamptz` instead of `timestamp`.
3. **Turn on RLS for every table in an exposed schema.** `alter table public.projects enable row level security;` With RLS on and no policy, nobody can read the table through the API. That is the safe default.
4. **Write narrow policies.** One policy per action, for a named role:
   ```sql
   create policy "Owners can read their projects"
   on public.projects for select
   to authenticated
   using ((select auth.uid()) = user_id);
   ```
   - Wrap `auth.uid()` in `(select ...)` so Postgres runs it once per query, not once per row.
   - Always add `to authenticated` (or `anon`) so the policy is skipped for other roles.
   - `insert` needs `with check`, and `update` needs both `using` and `with check`.
5. **Index what policies and joins use.** Add an index on every column used in a policy or foreign key: `create index on public.projects (user_id);`. Postgres does not index foreign keys for you. On a big live table, use `create index concurrently` in its own migration.
6. **Keep helper functions private.** Put `security definer` functions in a schema that the API does not expose (for example `private`), and set `search_path = ''` on them. For team or role checks, call such a function from the policy instead of joining in the policy itself.
7. **Make views respect RLS.** On Postgres 15 and later, create views `with (security_invoker = true)`. Otherwise a view runs as its owner and skips RLS.
8. **Check performance.** Run slow queries with `explain analyze` in the SQL editor. Look for "Seq Scan" on big tables and add indexes. Use the dashboard's Security and Performance Advisors, or `npx supabase db lint`.
9. **Generate types.** `npx supabase gen types typescript --local > src/types/database.ts`, so app code matches the schema.
10. **Test as a real user.** Sign in as two test users and confirm each sees only their own rows. Also check that a signed-out request gets nothing.

## Gotchas
- A table made with SQL in `public` has RLS off until you enable it. The dashboard table editor turns it on, raw SQL does not.
- A policy that joins other tables runs for every row. Flip it to `user_id in (select ...)` or use a private function.
- Serverless apps should connect through the pooler (Supavisor, transaction mode on port 6543), not straight to the database.
- `select *` from the client sends every column. Ask only for what the page needs.

## Safety
- The `service_role` or secret key bypasses RLS. Keep it on the server only, never in browser code, a `NEXT_PUBLIC_` variable or git. The anon or publishable key is the only key for the browser.
- Back up before destructive migrations (dropping columns or tables). Test them on a branch or local database first.
- Do not store passwords, card numbers or other sensitive personal data in plain columns. Use Supabase Auth for passwords and a payment provider for cards.

## Works with other stacks
The SQL is plain Postgres, so the same approach works with Neon, RDS or a self-hosted Postgres with PostgREST. Instead of `auth.uid()`, use your own setting such as `current_setting('request.jwt.claims')`. With an ORM like Prisma or Drizzle, write the RLS SQL in a raw migration. For sign-in and sessions, see `set-up-supabase-auth`.

## Done when
- Every table in an exposed schema has RLS enabled and narrow policies for each action.
- Policy and foreign-key columns are indexed, and the advisors show no security warnings.
- All changes are in migration files, types are generated, and two test users cannot see each other's data.

## Credits
Written by TheSkillVault in our own words, CC BY 4.0, from the public Supabase documentation on row level security and database performance. Inspired by the topic of supabase/agent-skills; no text was copied.

## Example prompt
"Add a projects table to my Supabase app where each user can only see and edit their own projects, with the right indexes, as a migration."