Supabase

Summary

RLS (Row Level Security) is the single most failure-prone surface in Supabase — across 9 projects it has caused infinite recursion, silent zero-row deletes, cross-tenant data leaks, and login loops, almost always because policies were written without understanding how Postgres evaluates them per-row. The second most common failure category is auth session management: sign-out scope, hash-fragment token handling, and the @supabase/ssr browser client's singleton behavior each have non-obvious failure modes that docs underspecify. Supabase's 1000-row default query limit silently truncates results with no warning — every query against a table that could grow beyond 1000 rows needs explicit pagination. Use supabaseAdmin (service role) for any operation that needs to bypass RLS, but never expose it client-side; use (select auth.uid()) instead of bare auth.uid() in RLS policies to avoid per-row re-evaluation overhead.

TL;DR

What we've learned
- RLS policies that reference other tables can recurse infinitely and return zero rows — seen in ContentCommand, Hazardos, and AsymXray
- Supabase silently caps query results at 1000 rows; ClientBrain and LabelCheck both shipped bugs because of this
- Sign-out with scope: 'local' only clears browser memory — cookies survive, middleware still sees the user as authenticated
- @supabase/ssr auto-detects ?code= query params but does NOT auto-detect # hash fragment tokens; these require different handling
- createBrowserClient manages its own singleton internally — wrapping it in your own singleton causes stale state after sign-out
- Signed URLs from Supabase Storage expire in 1 hour; persisting them to the database guarantees broken images

External insights

• generateLink API has a known upstream token validation bug causing immediate otp_expired errors (GitHub issue #40989); use resetPasswordForEmail instead
• CONCURRENTLY index creation is not supported inside Supabase migration pipelines — migrations run in transactions

Common Failure Modes

RLS policies that reference other tables cause infinite recursion

A policy on organizations that joins back to profiles (which itself has a policy checking organizations) creates a cycle Postgres cannot resolve. The symptom is silent: the query returns zero rows, no error is thrown, and the user sees an empty state or a login loop.

Established failure mode across ContentCommand, Hazardos, and AsymXray. The fix is to break the cycle using a SECURITY DEFINER helper function with an explicit search_path, or by restructuring the policy to avoid the cross-table reference entirely.

-- BAD: policy on profiles references organizations, which references profiles
CREATE POLICY "org_member_access" ON profiles
  USING (org_id IN (SELECT org_id FROM organizations WHERE owner_id = auth.uid()));

-- GOOD: use a security definer function to break the cycle
CREATE OR REPLACE FUNCTION get_user_org_ids()
RETURNS SETOF uuid
LANGUAGE sql SECURITY DEFINER
SET search_path = public
AS $$
  SELECT org_id FROM org_members WHERE user_id = auth.uid();
$$;

CREATE POLICY "org_member_access" ON profiles
  USING (org_id IN (SELECT get_user_org_ids()));

^[1]

Silent 1000-row truncation

Supabase's PostgREST layer returns a maximum of 1000 rows with no warning, no error, and no indication in the response that rows were dropped. ClientBrain shipped incorrect document counts because of this. LabelCheck's ingredient cache was silently incomplete, causing analysis failures on large datasets.

Every query against a table that could exceed 1000 rows needs .range() pagination or a count check:

// Get total count first
const { count } = await supabase
  .from('ingredients')
  .select('*', { count: 'exact', head: true });

// Then paginate
const pageSize = 1000;
for (let offset = 0; offset < count; offset += pageSize) {
  const { data } = await supabase
    .from('ingredients')
    .select('*')
    .range(offset, offset + pageSize - 1);
  // process data
}

^[2]

Sign-out with scope: 'local' leaves server-side session intact

Calling supabase.auth.signOut() without specifying scope: 'global' only clears the in-memory browser state. The auth cookie survives, and middleware running getSession() on the server still sees a valid session. The user appears logged out in the UI but is still authenticated for API calls.

Fix: always use scope: 'global' for sign-out, and pair it with a server-side API route that clears HttpOnly cookies (which the browser client cannot touch directly).

// Client-side
await supabase.auth.signOut({ scope: 'global' });

// Server-side route handler also needed for HttpOnly cookie cleanup
// app/api/auth/signout/route.ts
export async function POST() {
  const supabase = createServerClient(/* ... */);
  await supabase.auth.signOut({ scope: 'global' });
  return NextResponse.redirect('/login');
}

Observed in ClientBrain — the sign-out button appeared to work but middleware kept redirecting back to the app.
^[3]

Auth deadlock from nested getSession() calls

The Supabase browser client uses an internal lock for auth operations. Calling getSession() inside an async chain that was itself triggered by an outer getSession() call causes a deadlock — the inner call waits for the lock the outer call holds, and the outer call waits for the inner to complete.

Observed in Hazardos. The symptom is the app freezing silently with no error thrown. Fix: restructure initialization so getSession() is called once at the top level, and downstream code receives the session as a parameter rather than fetching it independently.

^[4]

Hash fragment tokens not auto-detected by @supabase/ssr

@supabase/ssr's createBrowserClient auto-detects ?code= query parameters (detectSessionInUrl: true is the default) and races to exchange them. But hash fragment tokens (#access_token=...&type=invite) are never sent to the server and are not auto-processed. Invite links and recovery links that deliver tokens via hash require explicit client-side parsing:

// On the invite/recovery page, parse hash manually
useEffect(() => {
  const hash = window.location.hash;
  if (hash) {
    const params = new URLSearchParams(hash.substring(1));
    const accessToken = params.get('access_token');
    const refreshToken = params.get('refresh_token');
    if (accessToken && refreshToken) {
      supabase.auth.setSession({ access_token: accessToken, refresh_token: refreshToken });
    }
  }
}, []);

For ?code= callbacks, use a server-side route handler — the browser client racing to call exchangeCodeForSession client-side causes hangs when the server-side handler also tries to exchange the same code.

Seen in ClientBrain and AsymXray.
^[5]

Wrapping createBrowserClient in your own singleton causes stale state

createBrowserClient from @supabase/ssr manages its own singleton internally. If you wrap it in a module-level singleton (a common pattern for regular API clients), the wrapper holds a reference to the pre-sign-out client state. After sign-out, the wrapper returns the stale instance instead of letting @supabase/ssr create a fresh one.

ClientBrain hit this: sign-out appeared to work but subsequent auth checks used the old session.

Fix: call createBrowserClient directly each time, or use the @supabase/ssr recommended pattern without an outer singleton wrapper.

^[6]

Ambiguous foreign key joins crash with PGRST201

When a table has two foreign key relationships to the same target table (e.g., profiles referenced as both created_by and assigned_to), PostgREST cannot infer which FK to use for a join and throws:

Error: Could not embed because more than one relationship was found for 'profiles' and 'organizations'
PGRST201

Fix: use the explicit FK hint syntax in your query:

const { data } = await supabase
  .from('tasks')
  .select(`
    *,
    creator:profiles!tasks_created_by_fkey(id, name),
    assignee:profiles!tasks_assigned_to_fkey(id, name)
  `);

Observed in Hazardos when the dashboard tried to join profiles through two different FK paths.
^[7]

.eq('column', null) silently returns zero rows

PostgREST translates .eq('deleted_at', null) to deleted_at = NULL in SQL, which is always false — SQL null comparison requires IS NULL. The query succeeds with an empty result set and no error.

// WRONG — always returns zero rows
.eq('deleted_at', null)

// CORRECT
.is('deleted_at', null)

Observed in OrbitABM. This is particularly dangerous in soft-delete patterns where the filter is the primary mechanism for excluding deleted records.
^[8]

Signed Storage URLs expire and break when persisted

Supabase Storage signed URLs expire after 1 hour by default. Persisting them to the database (as a cover image URL, profile photo, etc.) guarantees broken images after expiry. Eydn hit this twice — once for wedding party photos and once for cover images.

Fix: store the storage path, not the signed URL. Generate signed URLs at render time.

// WRONG — persisting the signed URL
await supabase.from('profiles').update({ avatar_url: signedUrl });

// CORRECT — persist the path, generate URL on read
await supabase.from('profiles').update({ avatar_path: 'avatars/user-123.jpg' });

// On read:
const { data } = await supabase.storage
  .from('avatars')
  .createSignedUrl(profile.avatar_path, 3600);

^[9]

generateLink API produces immediate otp_expired errors

auth.admin.generateLink() has a known upstream bug (GitHub issue #40989) where the generated token fails validation immediately on use. The symptom is the user clicking a valid-looking password reset link and seeing an "OTP expired" error with no delay.

Fix: use resetPasswordForEmail() instead, which handles token generation and email delivery internally and does not exhibit this bug. Observed in AsymXray.

^[10]

Module-level client initialization fails during static prerendering

Initializing a Supabase client at module scope (outside any function) causes it to execute during Next.js static prerendering, where NEXT_PUBLIC_SUPABASE_URL and NEXT_PUBLIC_SUPABASE_ANON_KEY may not be available. The result is a client initialized with undefined values that silently fails all queries.

Observed in Sieve. Fix: initialize inside a function, a useMemo, or a lazy singleton that defers until runtime.

^[11]

What Works

(select auth.uid()) in RLS policies — cache once, not per row

Bare auth.uid() in an RLS policy is evaluated for every row the query touches. Wrapping it in (select auth.uid()) causes Postgres to evaluate it once and cache the result for the query duration. For tables with thousands of rows, this is a meaningful performance difference. Supabase's Security Advisor flags bare auth.uid() calls as performance warnings.

-- SLOW: auth.uid() called for every row
CREATE POLICY "user_owns_row" ON documents
  USING (user_id = auth.uid());

-- FAST: evaluated once per query
CREATE POLICY "user_owns_row" ON documents
  USING (user_id = (select auth.uid()));

Validated in AsymXray and LabelCheck.
^[12]

supabaseAdmin (service role) for privileged operations, with application-level filtering

For operations that legitimately need to bypass RLS — admin panels, system jobs, invitation flows, bulk operations — use the service role client. The pattern that works: bypass RLS at the database level, enforce access control at the application level.

// Server-side only — never expose service role key to client
const supabaseAdmin = createClient(
  process.env.SUPABASE_URL!,
  process.env.SUPABASE_SERVICE_ROLE_KEY!, // not NEXT_PUBLIC_
  { auth: { autoRefreshToken: false, persistSession: false } }
);

// Application-level filter replaces RLS
const { data } = await supabaseAdmin
  .from('analyses')
  .select('*')
  .eq('org_id', verifiedOrgId); // you enforce the scope

Consistent pattern across AsymXray, ContentCommand, LabelCheck, and OrbitABM.
^[13]

Server-side route handlers for auth callbacks

All Supabase auth callbacks (?code= exchanges, invite completions, password reset confirmations) should be handled in server-side route handlers, not client-side pages. The browser client racing to process ?code= params client-side while a server handler also tries to exchange the same code causes hangs and double-exchange errors.

// app/auth/callback/route.ts
export async function GET(request: Request) {
  const { searchParams } = new URL(request.url);
  const code = searchParams.get('code');
  if (code) {
    const supabase = createServerClient(/* cookies */);
    await supabase.auth.exchangeCodeForSession(code);
  }
  return NextResponse.redirect('/dashboard');
}

Validated in ClientBrain. Hash fragment tokens (invite links) are the exception — they never reach the server and require client-side handling.
^[14]

Deny-all RLS policies for admin-only tables

Tables that should only be accessible via the service role (audit logs, system config, admin-only data) should have explicit deny-all RLS policies rather than no policies. No policy = Supabase linter warning. A deny-all policy makes the intent explicit and silences the linter.

-- Deny all access via user client; service role bypasses RLS automatically
CREATE POLICY "deny_all_user_access" ON audit_logs
  FOR ALL USING (false);

Used in Eydn and OrbitABM.
^[15]

Atomic UPSERT instead of DELETE + INSERT for concurrent operations

Race conditions in concurrent writes (multiple users editing the same resource simultaneously) are eliminated by replacing DELETE + INSERT sequences with atomic UPSERT. Eydn hit this with seating assignments — concurrent drag operations from two users could result in duplicate or missing assignments.

INSERT INTO seat_assignments (guest_id, seat_id, table_id)
VALUES ($1, $2, $3)
ON CONFLICT (guest_id) DO UPDATE
  SET seat_id = EXCLUDED.seat_id,
      table_id = EXCLUDED.table_id;

^[16]

SQL aggregation functions outperform JS-level aggregation

Moving aggregation into Postgres functions (get_ai_usage_summary, get_pipeline_stats) and fetching the result directly is faster than fetching raw rows and aggregating in JavaScript. The difference is significant when the underlying dataset is large. Validated in ContentCommand.

^[17]

FOR UPDATE SKIP LOCKED for queue-style processing

When multiple workers (n8n, cron jobs, background processors) compete to process the same rows, FOR UPDATE SKIP LOCKED prevents double-processing without requiring application-level locking:

SELECT * FROM enrollments
WHERE status = 'pending'
FOR UPDATE SKIP LOCKED
LIMIT 10;

Observed in OrbitABM to prevent n8n from processing the same enrollment twice.
^[18]

Gotchas and Edge Cases

CONCURRENTLY is not allowed in migration files

CREATE INDEX CONCURRENTLY cannot run inside a transaction, and Supabase migrations run inside transactions. Using CONCURRENTLY in a migration file causes the migration to fail. Remove it — standard index creation is fine in migrations since they run during deployment, not under production load.

Observed in Hazardos.
^[19]

auth.uid() returns UUID; text columns need ::text cast in RLS

If your user_id column is text (not uuid), RLS policies comparing it to auth.uid() fail with an operator mismatch error. The fix is explicit casting:

USING (user_id = (select auth.uid())::text)

Observed in Eydn during a migration that added RLS to a table with a legacy text user_id column.
^[20]

Postgres text columns reject null bytes (\u0000)

Content scraped from external sources (web pages, PDFs, APIs) can contain null bytes. Postgres text columns do not accept them and throw an error on insert. Strip null bytes before any Supabase insert when the source is external content.

const sanitized = content.replace(/\u0000/g, '');

Observed in Sieve when storing content previews from scraped pages.
^[21]

Empty date strings must be converted to null for DATE columns

HTML date inputs submit empty strings ("") when cleared. Postgres DATE columns reject empty strings. Sanitize before insert:

const sanitized = {
  ...formData,
  expiry_date: formData.expiry_date || null,
};

Observed in LabelCheck.
^[22]

Supabase middleware blocks cron jobs and webhooks

Supabase session middleware applied to all routes will intercept Vercel cron job requests and webhook callbacks, which carry their own authentication (Bearer tokens, HMAC signatures) rather than Supabase session cookies. The middleware rejects them before the route handler runs.

Fix: explicitly exempt cron and webhook paths in the middleware matcher:

export const config = {
  matcher: [
    '/((?!api/cron|api/webhooks|_next/static|_next/image|favicon.ico).*)',
  ],
};

Observed in OrbitABM where Vercel cron jobs were silently prevented from triggering email sends.
^[23]

FOR ALL RLS policies include SELECT, causing duplicate policy warnings

A FOR ALL policy covers SELECT, INSERT, UPDATE, and DELETE. If you also add a separate SELECT policy, Supabase's Security Advisor warns about duplicate SELECT coverage. Use operation-specific policies (FOR SELECT, FOR INSERT, etc.) to avoid this and make intent explicit.

Observed in AsymXray.
^[24]

Supabase overwrites redirectTo when appending auth codes

When Supabase appends ?code= to a callback URL, it can overwrite or conflict with a redirectTo query parameter you've set. Use a dedicated callback route that reads a separate next parameter for post-auth redirect:

// In your callback route handler
const next = searchParams.get('next') ?? '/dashboard';
// After exchangeCodeForSession:
return NextResponse.redirect(new URL(next, request.url));

Observed in ClientBrain.
^[25]

Dependent trigram indexes must be dropped before removing pg_trgm

If you need to move pg_trgm from the public schema to the extensions schema (or remove it), any GIN indexes using gin_trgm_ops must be dropped first. After moving the extension, recreate them with the fully-qualified operator class:

DROP INDEX IF EXISTS idx_name_trgm;
-- move extension
CREATE EXTENSION IF NOT EXISTS pg_trgm SCHEMA extensions;
-- recreate with explicit operator class
CREATE INDEX idx_name_trgm ON table USING gin(name extensions.gin_trgm_ops);

Observed in Eydn.
^[26]

File uploads to Storage require Buffer conversion server-side

File objects from formData.get('file') cannot be passed directly to supabase.storage.from().upload() in a server-side route handler. Convert to ArrayBuffer first:

const file = formData.get('file') as File;
const arrayBuffer = await file.arrayBuffer();
const buffer = Buffer.from(arrayBuffer);
await supabase.storage.from('uploads').upload(path, buffer, {
  contentType: file.type,
});

Observed in Eydn.
^[27]

Where Docs Disagree With Practice

createServiceClient vs createAdminClient — not equivalent for RLS bypass

Docs and community examples use these terms interchangeably, but in practice (observed in AsymXray) createAdminClient properly bypasses RLS while a createServiceClient implementation that uses the service role key but initializes with default options may not. The reliable pattern is to initialize with auth: { autoRefreshToken: false, persistSession: false } and the service role key explicitly.

^[28]

Docs claim generateLink is the way to send custom password reset emails — it isn't reliable

The official docs present auth.admin.generateLink() as the mechanism for generating password reset links to embed in custom emails. In practice, the generated tokens fail with otp_expired immediately on use (GitHub issue #40989). resetPasswordForEmail() is the working alternative, though it sends Supabase's own email rather than a custom one.

^[29]

Password reset token validation is case-sensitive on email

Docs don't mention this: generateLink() token validation is case-sensitive on the email address used to generate the link. If the stored email is User@Example.com but the link is generated with user@example.com, the token fails. Always lowercase the email before passing it to generateLink().

Single-project finding in AsymXray, but the upstream bug report suggests it's not isolated.
^[30]

Views need security_invoker = true, not security_definer = false

Supabase linter flags views with security_definer = false — but the correct fix is not to remove the attribute, it's to set security_invoker = true. These are not equivalent in Postgres view semantics. Setting security_invoker = true explicitly makes the view execute with the calling user's permissions, which is what RLS requires.

CREATE VIEW my_view WITH (security_invoker = true) AS
  SELECT * FROM my_table;

Observed in OrbitABM.
^[8]

Recovery tokens redirect to /login, not your configured reset page

Supabase password recovery emails redirect to the configured Site URL with the token as a hash fragment. If your Site URL is your app root and you haven't set up explicit redirect handling in middleware, the token lands on /login instead of /reset-password. The middleware must detect the #type=recovery fragment and redirect accordingly — but since hash fragments don't reach the server, this detection must happen client-side.

Observed in AsymXray.
^[31]

Tool and Version Notes

• @supabase/ssr (current): createBrowserClient manages its own singleton — do not wrap in an outer singleton. createServerClient requires cookie handlers for Next.js App Router.

• Supabase CLI migrations: must live in /supabase/migrations/ with format YYYYMMDDHHMMSS_descriptive_name.sql. A /docs/database/ directory is legacy reference only and is not picked up by the CLI. Confirmed in Hazardos.

• CONCURRENTLY in migrations: not supported — migrations run in transactions. Remove from all migration files.

• pg_trgm extension: should be in extensions schema, not public. Extensions in public are accessible to all roles and trigger Supabase Security Advisor warnings. Confirmed in Eydn.

• function_search_path_mutable linter warning: triggered on any public function without SET search_path = public (or appropriate schema). Fix by adding SET search_path to all function definitions. Consistent finding across Eydn, Hazardos, OrbitABM, ContentCommand.

• Supabase Storage signed URLs: default expiry is 1 hour. Do not persist to database. Generate at render time from stored paths.

• NEXT_PUBLIC_SUPABASE_URL at build time: not inlined during Vercel build for server-side code. For public read-only values (anon key, public URL) where RLS enforces security, hardcoding as a fallback is acceptable. Observed in Sieve.

• Test databases: require separate TEST_SUPABASE_URL and TEST_SUPABASE_SERVICE_KEY environment variables — do not reuse production credentials. Confirmed in AsymXray.

Related Topics

• Middleware
• App Router Auth
• Rls Patterns
• Migrations
• Session Management
• File Uploads
• Multi Tenant Data Isolation

Sources

Synthesized from 161 fragments: git commits across AsymXray, ClientBrain, ContentCommand, Eydn, Hazardos, LabelCheck, OrbitABM, Sieve, and Stride v2. 1 external source (GitHub issue #40989 for generateLink token bug). No post-mortems as distinct documents — failure modes reconstructed from commit messages and fix descriptions. Date range: unknown to unknown (fragment metadata incomplete).