supabase

Set up or extend a Supabase integration from schema to type-safe queries.

Context to read

• supabase/ directory if it exists
• Any *.sql files in the repo
• .env.local or .env for SUPABASE_URL and SUPABASE_ANON_KEY
• Auth config (Clerk, Auth0, or Supabase Auth)
• package.json for @supabase/supabase-js version

Steps

1. Check what exists

ls supabase/ 2>/dev/null
cat supabase/config.toml 2>/dev/null

Is Supabase initialized? Are there existing migrations? Which tables exist?

2. Schema design

For each new table:

• uuid primary key with gen_random_uuid() default
• created_at timestamptz default now()
• updated_at timestamptz default now()
• user_id for user-owned rows

If using an external auth provider (Clerk, Auth0), store their user ID as text — not a reference to auth.users:

user_id text not null  -- clerk user_id, not uuid ref to auth.users

3. RLS policies

Enable RLS on every table that holds user data. Never leave RLS enabled with no policies — every query will fail silently and return empty results, not an error.

alter table public.your_table enable row level security;

Three canonical patterns. Pick the one that matches the table:

Pattern A — User owns row (most common: posts, files, credits, settings)

-- Supabase Auth
create policy "select own" on public.your_table for select using (user_id = auth.uid()::text);
create policy "insert own" on public.your_table for insert with check (user_id = auth.uid()::text);
create policy "update own" on public.your_table for update using (user_id = auth.uid()::text);
create policy "delete own" on public.your_table for delete using (user_id = auth.uid()::text);

-- External auth (Clerk / Auth0) — user_id is the JWT sub claim
create policy "select own" on public.your_table for select using (user_id = (auth.jwt() ->> 'sub'));
create policy "insert own" on public.your_table for insert with check (user_id = (auth.jwt() ->> 'sub'));
create policy "update own" on public.your_table for update using (user_id = (auth.jwt() ->> 'sub'));
create policy "delete own" on public.your_table for delete using (user_id = (auth.jwt() ->> 'sub'));

Pattern B — Team owns row (workspaces, shared projects)

-- Users can access rows where their user_id is in the team_members join table
create policy "select team rows" on public.your_table for select
  using (
    team_id in (
      select team_id from team_members where user_id = (auth.jwt() ->> 'sub')
    )
  );
-- Repeat for insert/update/delete with same team_id check

Pattern C — Admin bypass (service role for background jobs, webhooks)

-- Webhooks and cron jobs use the service role key, which bypasses RLS entirely.
-- Never use the service role key in client-side code.
-- In Next.js route handlers that need admin access:
import { createClient } from '@supabase/supabase-js';
const supabaseAdmin = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!  // server-side only, never expose
);

For each new table: generate all 4 CRUD policies (select, insert, update, delete). Don't leave gaps — a table with only a select policy will silently block all inserts.

4. Migrations

supabase db diff --file describe_the_change
supabase migration up

Always generate a named migration file. Never edit the schema directly in production. If no Supabase CLI:

npx supabase db diff --file describe_the_change

5. Generate TypeScript types

supabase gen types typescript --local > src/types/supabase.ts
# or for remote:
supabase gen types typescript --project-id your-project-id > src/types/supabase.ts

Import the generated types in every query. Do not use any for Supabase results.

6. Client setup

// lib/supabase.ts — browser client
import { createBrowserClient } from "@supabase/ssr";
import type { Database } from "@/types/supabase";

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

// lib/supabase-server.ts — server components / route handlers
import { createServerClient } from "@supabase/ssr";
import { cookies } from "next/headers";
import type { Database } from "@/types/supabase";

export function createSupabaseServer() {
  const cookieStore = cookies();
  return createServerClient<Database>(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    { cookies: { get: (name) => cookieStore.get(name)?.value } }
  );
}

7. Output

Report:

• Tables created or modified
• RLS policies set (and which auth strategy they use)
• Migration files written
• Type generation command confirmed
• Any auth edge cases (external provider JWT claims, service role usage)