How to Limit API Requests in Supabase

1// Check rate limit headers in API responses
2const response = await fetch(
3  `${process.env.NEXT_PUBLIC_SUPABASE_URL}/rest/v1/your_table`,
4  {
5    headers: {
6      'apikey': process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
7      'Authorization': `Bearer ${session.access_token}`,
8    },
9  }
10)
11
12console.log('Rate limit:', response.headers.get('X-RateLimit-Limit'))
13console.log('Remaining:', response.headers.get('X-RateLimit-Remaining'))
14console.log('Reset at:', response.headers.get('X-RateLimit-Reset'))

1-- Create a rate limiting table
2CREATE TABLE public.rate_limits (
3  id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
4  identifier text NOT NULL,
5  endpoint text NOT NULL,
6  request_count integer DEFAULT 1,
7  window_start timestamptz DEFAULT now(),
8  UNIQUE(identifier, endpoint, window_start)
9);
10
11-- Create index for fast lookups
12CREATE INDEX idx_rate_limits_lookup
13  ON public.rate_limits (identifier, endpoint, window_start);
14
15-- RLS: only service role can access this table
16ALTER TABLE public.rate_limits ENABLE ROW LEVEL SECURITY;
17-- No policies = no access via anon/authenticated roles
18-- Only service role (in Edge Functions) can read/write

1CREATE OR REPLACE FUNCTION public.check_rate_limit(
2  p_identifier text,
3  p_endpoint text,
4  p_max_requests integer DEFAULT 100,
5  p_window_seconds integer DEFAULT 60
6)
7RETURNS boolean
8LANGUAGE plpgsql
9SECURITY DEFINER
10SET search_path = ''
11AS $$
12DECLARE
13  v_window_start timestamptz;
14  v_current_count integer;
15BEGIN
16  -- Calculate the start of the current window
17  v_window_start := date_trunc('minute', now());
18
19  -- Try to insert or increment atomically
20  INSERT INTO public.rate_limits (identifier, endpoint, request_count, window_start)
21  VALUES (p_identifier, p_endpoint, 1, v_window_start)
22  ON CONFLICT (identifier, endpoint, window_start)
23  DO UPDATE SET request_count = public.rate_limits.request_count + 1
24  RETURNING request_count INTO v_current_count;
25
26  -- Check if over limit
27  IF v_current_count > p_max_requests THEN
28    RETURN false;
29  END IF;
30
31  RETURN true;
32END;
33$$;
34
35-- Only allow service role to execute
36REVOKE EXECUTE ON FUNCTION public.check_rate_limit FROM public, anon, authenticated;

1// supabase/functions/rate-limited-api/index.ts
2import { createClient } from 'npm:@supabase/supabase-js@2'
3
4const corsHeaders = {
5  'Access-Control-Allow-Origin': '*',
6  'Access-Control-Allow-Headers': 'authorization, x-client-info, apikey, content-type',
7}
8
9Deno.serve(async (req) => {
10  if (req.method === 'OPTIONS') {
11    return new Response('ok', { headers: corsHeaders })
12  }
13
14  // Use service role to check rate limits
15  const supabase = createClient(
16    Deno.env.get('SUPABASE_URL')!,
17    Deno.env.get('SUPABASE_SERVICE_ROLE_KEY')!
18  )
19
20  // Get identifier from JWT or IP
21  const authHeader = req.headers.get('Authorization')
22  let identifier = req.headers.get('x-forwarded-for') || 'unknown'
23
24  if (authHeader) {
25    const { data: { user } } = await supabase.auth.getUser(
26      authHeader.replace('Bearer ', '')
27    )
28    if (user) identifier = user.id
29  }
30
31  // Check rate limit: 100 requests per minute
32  const { data: allowed } = await supabase.rpc('check_rate_limit', {
33    p_identifier: identifier,
34    p_endpoint: '/api/data',
35    p_max_requests: 100,
36    p_window_seconds: 60,
37  })
38
39  if (!allowed) {
40    return new Response(
41      JSON.stringify({ error: 'Rate limit exceeded. Try again in 60 seconds.' }),
42      {
43        status: 429,
44        headers: {
45          ...corsHeaders,
46          'Content-Type': 'application/json',
47          'Retry-After': '60',
48        },
49      }
50    )
51  }
52
53  // Process the actual request
54  const { data, error } = await supabase.from('your_table').select('*')
55
56  return new Response(JSON.stringify(data), {
57    headers: { ...corsHeaders, 'Content-Type': 'application/json' },
58  })
59})

1import { supabase } from '@/lib/supabase'
2
3// Wrapper with retry logic for rate-limited endpoints
4async function fetchWithRetry<T>(
5  fn: () => Promise<{ data: T | null; error: any }>,
6  maxRetries: number = 3
7): Promise<T> {
8  for (let attempt = 0; attempt <= maxRetries; attempt++) {
9    const { data, error } = await fn()
10
11    if (!error) return data as T
12
13    // Check for rate limit error
14    if (error.message?.includes('429') || error.status === 429) {
15      if (attempt < maxRetries) {
16        const waitTime = Math.pow(2, attempt) * 1000 // 1s, 2s, 4s
17        console.log(`Rate limited. Retrying in ${waitTime}ms...`)
18        await new Promise(resolve => setTimeout(resolve, waitTime))
19        continue
20      }
21      throw new Error('Rate limit exceeded. Please wait a moment and try again.')
22    }
23
24    throw error
25  }
26  throw new Error('Max retries exceeded')
27}
28
29// Usage
30const data = await fetchWithRetry(() =>
31  supabase.from('projects').select('*')
32)

1-- Enable pg_cron extension (if not already enabled)
2CREATE EXTENSION IF NOT EXISTS pg_cron;
3
4-- Schedule cleanup: delete rate limit records older than 1 hour
5SELECT cron.schedule(
6  'cleanup-rate-limits',
7  '*/15 * * * *',  -- Every 15 minutes
8  $$DELETE FROM public.rate_limits WHERE window_start < now() - interval '1 hour'$$
9);