supabase-patterns

# Supabase Patterns

## Critical Rules

- **Enable RLS on every table — no exceptions**, even internal tables.
- **Always validate session server-side** — never trust client-side `getUser()` alone.
- **Use `@supabase/ssr`** — never `@supabase/auth-helpers-nextjs` (deprecated).
- **Handle errors explicitly** — never ignore the `error` return value.
- **Use `LIMIT`** on all queries — never fetch unbounded result sets.
- **RLS policies on storage buckets** — no exceptions.

## Authentication

- Use `@supabase/ssr` for Next.js server-side auth.
- Create two client helpers:
  - `createClient()` in `src/lib/supabase/client.ts` for Client Components
  - `createServerClient()` in `src/lib/supabase/server.ts` for Server Components / Actions
- Always validate session server-side before granting access.
- Use middleware (`middleware.ts`) to refresh the session on every request:
  ```ts
  const { data: { user } } = await supabase.auth.getUser()
  if (!user && protectedRoutes.includes(request.nextUrl.pathname)) {
    return NextResponse.redirect(new URL('/login', request.url))
  }
  ```
- Support multiple auth strategies: email/password, OAuth (Google, GitHub), magic link.
- Store user metadata in a separate `profiles` table linked by `auth.users.id`.

## Row Level Security (RLS)

- Write policies that reference `auth.uid()` directly:
  ```sql
  CREATE POLICY "Users read own data"
    ON profiles FOR SELECT
    USING (auth.uid() = user_id);
  ```
- Avoid `security definer` functions unless absolutely necessary. Prefer RLS policies.
- Test policies explicitly: try accessing data as different users/roles.
- Use `auth.jwt() ->> 'role'` for role-based access if implementing RBAC.

## Database Schema

- Use `uuid` for primary keys, generated by `gen_random_uuid()`.
- Add `created_at` and `updated_at` timestamps to every table:
  ```sql
  created_at timestamptz DEFAULT now() NOT NULL,
  updated_at timestamptz DEFAULT now() NOT NULL
  ```
- Create an `updated_at` trigger using `moddatetime` extension.
- Use foreign key constraints for referential integrity.
- Add indexes on columns used in WHERE clauses and JOIN conditions.

## Query Patterns

- Use the Supabase client query builder for simple CRUD:
  ```ts
  const { data, error } = await supabase
    .from('posts')
    .select('*, author:profiles(name, avatar_url)')
    .eq('published', true)
    .order('created_at', { ascending: false })
    .limit(20)
  ```
- Handle errors explicitly — never ignore `error`:
  ```ts
  if (error) throw new Error(error.message)
  ```
- Use `.single()` when expecting exactly one row. Use `.maybeSingle()` when the row may not exist.
- Use server-side Supabase client for mutations in Server Actions.

## Real-time

- Use Supabase Realtime only for features that genuinely need live updates (chat, notifications, collaboration).
- Subscribe in Client Components and clean up on unmount:
  ```ts
  useEffect(() => {
    const channel = supabase.channel('room').on('postgres_changes', { event: '*', schema: 'public', table: 'messages' }, handler).subscribe()
    return () => { supabase.removeChannel(channel) }
  }, [])
  ```
- Prefer polling or revalidation for data that changes infrequently.

## Storage

- Use Supabase Storage for file uploads (images, documents).
- Create separate buckets for public vs private files.
- Set RLS policies on storage buckets — **no exceptions**.
- Generate signed URLs for private files with short expiration times.
- File upload pattern:
  ```ts
  const { data, error } = await supabase.storage
    .from("avatars")
    .upload(`${userId}/${fileName}`, file, {
      cacheControl: "3600",
      upsert: true,
    });
  ```
- Get public URL: `supabase.storage.from("avatars").getPublicUrl(path)`.

## Do

- Enable RLS on every table immediately after creation — no exceptions.
- Validate sessions server-side with `getUser()` in middleware and Server Actions.
- Use `@supabase/ssr` for all Next.js Supabase integration.
- Handle `error` on every Supabase query — log it, throw it, or show it to the user.
- Add `LIMIT` to every query — protect against unbounded result sets.
- Use `.single()` for queries expecting exactly one row, `.maybeSingle()` when it might not exist.
- Use separate Supabase clients for client components (`createClient`) and server components (`createServerClient`).
- Set RLS policies on storage buckets — treat them like tables.
- Use `gen_random_uuid()` for primary keys and add `created_at`/`updated_at` to every table.
- Clean up Realtime subscriptions on component unmount.

## Don't

- Don't skip RLS on "internal" or "admin" tables — attackers find them too.
- Don't trust `getSession()` alone — it reads from cookies and can be spoofed; always use `getUser()`.
- Don't use `@supabase/auth-helpers-nextjs` — it is deprecated; use `@supabase/ssr`.
- Don't ignore the `error` return — silent failures hide bugs and security issues.
- Don't use `select('*')` on large tables — select only the columns you need.
- Don't use `security definer` functions unless absolutely necessary — prefer RLS policies.
- Don't subscribe to Realtime for data that changes infrequently — use polling or revalidation.
- Don't connect directly to the database at scale — use Supavisor connection pooling.
- Don't store user profile data in `auth.users` metadata — use a separate `profiles` table.
- Don't create signed URLs with long expiration times for private files.

## Anti-Patterns

| Anti-Pattern | Problem | Fix |
|-------------|---------|-----|
| **RLS disabled on table** | Any authenticated user can read/write all rows | Enable RLS and add policies referencing `auth.uid()` |
| **Client-side `getSession()` only** | Session can be spoofed from cookies | Always validate with `getUser()` server-side |
| **`select('*')` without LIMIT** | Fetches entire table, causes timeouts and high bandwidth | Use `select('col1, col2').limit(20)` |
| **Ignoring `error` return** | Bugs silently pass, data inconsistencies go unnoticed | Check `if (error)` and throw or handle on every query |
| **Realtime for everything** | Unnecessary WebSocket connections, wasted resources | Use Realtime only for chat, notifications, collaboration |
| **No `updated_at` trigger** | `updated_at` column never updates, stale timestamps | Add `moddatetime` trigger on every table |
| **Direct DB connection at scale** | Connection exhaustion under load | Use Supavisor connection pooling in production |
| **`security definer` overuse** | Bypasses RLS, creates hidden privilege escalation | Use RLS policies; reserve `security definer` for rare edge cases |

## Performance

- Use connection pooling (Supavisor) in production — never connect directly at scale.
- Add `LIMIT` to all queries. Never fetch unbounded result sets.
- Use `select('column1, column2')` instead of `select('*')` for large tables.
- Create partial indexes for frequently filtered subsets of data.

No additional files