Backend · How to use a skill

design-supabase-postgres-schema

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.

Version 1.0 · Updated 2026-10-31

View raw SKILL.md

curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/design-supabase-postgres-schema/raw' -o ~/'.claude/skills/design-supabase-postgres-schema/SKILL.md'

curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/design-supabase-postgres-schema/raw' -o '.cursor/skills/design-supabase-postgres-schema/SKILL.md'

curl -fsSL --create-dirs 'https://theskillvault.ai/api/skills/design-supabase-postgres-schema/raw' -o '.agents/skills/design-supabase-postgres-schema/SKILL.md'

{
  "mcpServers": {
    "theskillvault": {
      "url": "https://theskillvault.ai/mcp"
    }
  }
}

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.

1. 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.
2. Write narrow policies. One policy per action, for a named role:

   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.

1. 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.
2. 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.
3. 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.
4. 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.
5. Generate types. npx supabase gen types typescript --local > src/types/database.ts, so app code matches the schema.
6. 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."

Licence: CC BY 4.0. Attribution when you reuse it: From TheSkillVault, https://theskillvault.ai, CC BY 4.0