Supabase Implementation Patterns

Ce skill couvre les patterns concrets pour construire un backend Supabase production-grade. Il complète l'agent supabase-architect en donnant des recettes prêtes à coller.

MCP server disponible : ce plugin déclare supabase dans .mcp.json. Claude Code peut invoquer le MCP HTTP officiel pour lister les projets, appliquer des migrations, déployer des Edge Functions.

1. Setup initial

# Install CLI
brew install supabase/tap/supabase

# Init local
supabase init

# Login + link au projet remote
supabase login
supabase link --project-ref <project-ref>

# Lancer la stack locale (Postgres + Auth + Storage + Studio)
supabase start

Structure créée :

supabase/
├── config.toml
├── migrations/
├── functions/
└── seed.sql

2. Migrations versionnées

# Créer une migration
supabase migration new create_projects_table

# Édition du SQL généré
# supabase/migrations/20260408120000_create_projects_table.sql

CREATE TABLE projects (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id uuid NOT NULL,
  owner_id uuid NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  name text NOT NULL,
  description text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX projects_tenant_id_idx ON projects(tenant_id);
CREATE INDEX projects_owner_id_idx ON projects(owner_id);

-- Trigger auto updated_at
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN NEW.updated_at = now(); RETURN NEW; END;
$$;

CREATE TRIGGER projects_updated_at
BEFORE UPDATE ON projects
FOR EACH ROW EXECUTE FUNCTION set_updated_at();

# Apply local
supabase db reset

# Apply remote
supabase db push

3. RLS policies multi-tenant

-- 1. Activer RLS
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- 2. Helper function (cached)
CREATE OR REPLACE FUNCTION auth.tenant_id()
RETURNS uuid LANGUAGE sql STABLE AS $$
  SELECT NULLIF(current_setting('request.jwt.claims', true)::json->>'tenant_id', '')::uuid
$$;

-- 3. Policies SELECT / INSERT / UPDATE / DELETE
CREATE POLICY "Tenant members see tenant projects"
ON projects FOR SELECT
USING (tenant_id = auth.tenant_id());

CREATE POLICY "Tenant members create in tenant"
ON projects FOR INSERT
WITH CHECK (
  tenant_id = auth.tenant_id()
  AND owner_id = auth.uid()
);

CREATE POLICY "Owners update their projects"
ON projects FOR UPDATE
USING (owner_id = auth.uid())
WITH CHECK (owner_id = auth.uid() AND tenant_id = auth.tenant_id());

CREATE POLICY "Owners delete their projects"
ON projects FOR DELETE
USING (owner_id = auth.uid());

Tests RLS avec pgTAP

-- supabase/tests/rls_test.sql
BEGIN;
SELECT plan(4);

-- Setup
SELECT tests.create_supabase_user('alice@test.com', '{"tenant_id": "tenant-a"}');
SELECT tests.create_supabase_user('bob@test.com', '{"tenant_id": "tenant-b"}');

-- Alice voit ses projects
SELECT tests.authenticate_as('alice@test.com');
INSERT INTO projects (tenant_id, owner_id, name)
VALUES ('tenant-a', tests.get_supabase_uid('alice@test.com'), 'Alice Project');

SELECT results_eq(
  $$ SELECT count(*)::int FROM projects $$,
  ARRAY[1],
  'Alice voit 1 project'
);

-- Bob ne voit PAS le project d'Alice
SELECT tests.authenticate_as('bob@test.com');
SELECT results_eq(
  $$ SELECT count(*)::int FROM projects $$,
  ARRAY[0],
  'Bob ne voit pas le project d Alice'
);

SELECT * FROM finish();
ROLLBACK;

supabase test db

4. Auth flows

Email + OAuth dans Next.js

// app/auth/callback/route.ts
import { createServerClient } from '@supabase/ssr'
import { NextResponse } from 'next/server'

export async function GET(request: Request) {
  const { searchParams, origin } = new URL(request.url)
  const code = searchParams.get('code')

  if (code) {
    const supabase = createServerClient(...)
    await supabase.auth.exchangeCodeForSession(code)
  }

  return NextResponse.redirect(`${origin}/dashboard`)
}

// Sign in avec OAuth
const { data, error } = await supabase.auth.signInWithOAuth({
  provider: 'google',
  options: { redirectTo: `${location.origin}/auth/callback` },
})

Custom claims via Auth Hook

-- Trigger function qui ajoute tenant_id au JWT
CREATE OR REPLACE FUNCTION public.custom_access_token_hook(event jsonb)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE
  claims jsonb;
  user_tenant_id uuid;
BEGIN
  SELECT tenant_id INTO user_tenant_id
  FROM public.user_tenants
  WHERE user_id = (event->>'user_id')::uuid
  LIMIT 1;

  claims := event->'claims';
  IF user_tenant_id IS NOT NULL THEN
    claims := jsonb_set(claims, '{tenant_id}', to_jsonb(user_tenant_id::text));
  END IF;

  RETURN jsonb_set(event, '{claims}', claims);
END;
$$;

GRANT EXECUTE ON FUNCTION public.custom_access_token_hook TO supabase_auth_admin;

Activer dans supabase/config.toml :

[auth.hook.custom_access_token]
enabled = true
uri = "pg-functions://postgres/public/custom_access_token_hook"

5. Edge Functions

supabase functions new send-welcome-email

// supabase/functions/send-welcome-email/index.ts
import { Hono } from 'jsr:@hono/hono'
import { createClient } from 'jsr:@supabase/supabase-js@2'

const app = new Hono()

app.post('/', async (c) => {
  const { record } = await c.req.json()

  const RESEND_KEY = Deno.env.get('RESEND_API_KEY')!

  const res = await fetch('https://api.resend.com/emails', {
    method: 'POST',
    headers: {
      'Authorization': `Bearer ${RESEND_KEY}`,
      'Content-Type': 'application/json',
    },
    body: JSON.stringify({
      from: 'noreply@example.com',
      to: record.email,
      subject: 'Bienvenue !',
      html: `<p>Bonjour ${record.name}</p>`,
    }),
  })

  if (!res.ok) {
    return c.json({ error: 'Failed to send' }, 500)
  }

  return c.json({ success: true })
})

Deno.serve(app.fetch)

# Set secret
supabase secrets set RESEND_API_KEY=re_xxx

# Deploy
supabase functions deploy send-welcome-email

# Logs
supabase functions logs send-welcome-email

Trigger via Database Webhook

Dans Supabase Studio → Database → Webhooks → New webhook :

• Table : auth.users
• Events : INSERT
• Type : Supabase Edge Functions
• Function : send-welcome-email

6. Realtime — Postgres CDC

import { createClient } from '@supabase/supabase-js'

const supabase = createClient(url, anonKey)

const channel = supabase
  .channel('messages')
  .on(
    'postgres_changes',
    { event: 'INSERT', schema: 'public', table: 'messages', filter: `room_id=eq.${roomId}` },
    (payload) => {
      console.log('New message:', payload.new)
    }
  )
  .subscribe()

// Cleanup
return () => { supabase.removeChannel(channel) }

Important : les events Realtime sont filtrés par les RLS policies du user qui écoute. Bien testé en RLS = bien testé en Realtime.

Activer la publication

ALTER PUBLICATION supabase_realtime ADD TABLE messages;

7. Storage

// Upload
const { data, error } = await supabase.storage
  .from('avatars')
  .upload(`${userId}/avatar.png`, file, {
    cacheControl: '3600',
    upsert: true,
  })

// Download (signed URL pour bucket privé)
const { data } = await supabase.storage
  .from('avatars')
  .createSignedUrl(`${userId}/avatar.png`, 60 * 60) // 1h

RLS policy storage

CREATE POLICY "Users upload their own avatar"
ON storage.objects FOR INSERT
WITH CHECK (
  bucket_id = 'avatars'
  AND (storage.foldername(name))[1] = auth.uid()::text
);

CREATE POLICY "Avatars publicly readable"
ON storage.objects FOR SELECT
USING (bucket_id = 'avatars');

8. pgvector pour RAG

CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE document_chunks (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  document_id uuid NOT NULL,
  content text NOT NULL,
  embedding vector(1536) NOT NULL,
  metadata jsonb DEFAULT '{}'::jsonb
);

CREATE INDEX document_chunks_embedding_idx
ON document_chunks USING hnsw (embedding vector_cosine_ops);

-- Function de search hybride (vector + filter)
CREATE OR REPLACE FUNCTION match_documents(
  query_embedding vector(1536),
  match_threshold float,
  match_count int,
  filter_metadata jsonb DEFAULT '{}'
)
RETURNS TABLE (id uuid, content text, similarity float)
LANGUAGE sql STABLE AS $$
  SELECT
    id,
    content,
    1 - (embedding <=> query_embedding) AS similarity
  FROM document_chunks
  WHERE metadata @> filter_metadata
    AND 1 - (embedding <=> query_embedding) > match_threshold
  ORDER BY embedding <=> query_embedding
  LIMIT match_count;
$$;

9. Branching pour les PRs

# Crée une branche temporaire pour une PR
supabase branches create feature-x --persistent
supabase branches list

# Pousse les migrations vers la branche
git checkout feature-x
supabase db push --branch feature-x

# Supabase clone le schéma + les Edge Functions automatiquement

Activer auto-branching dans Studio → Settings → Branching → Enable.

Anti-patterns critiques

1. service_role key dans le client browser — bypass total des RLS
2. Pas de RLS sur une table — anyone can read everything
3. Auth dans le code app au lieu de RLS — contournable via SQL direct
4. Migrations via SQL Editor sans Git — drift garanti
5. Realtime CDC sur grosse table sans filter — coût explosif
6. pg_cron pour des jobs lourds — bloque les connexions, préférer Edge Functions
7. Storage public bucket pour des fichiers user — toute personne avec l'URL accède
8. Pas de pooler PgBouncer — fuite de connexions
9. Pas de monitoring pg_stat_statements — slow queries non détectées
10. Helper functions non STABLE — relancées à chaque appel, perf RLS écroulée

Checklist

• Project Supabase + region
• Schéma défini via migrations versionnées
• RLS activée + policies + tests pgTAP
• Auth providers configurés
• Custom JWT claims via Auth Hook
• Edge Functions déployées + secrets
• Storage buckets + RLS
• Realtime publications
• pgvector si RAG
• Branching auto pour PRs
• Backup auto + restore testé
• CI/CD avec supabase db push

Quand déléguer

• Schema design lourd → agent database-reviewer (PR review)
• Postgres avancé hors Supabase → skill postgres-patterns
• Comparaison Firebase vs Supabase → agents firebase-expert + supabase-architect
• RAG models → agent ml-engineer (atum-ai-ml)