DEV Community: Mahdi BEN RHOUMA

Fix Dynamic Server Usage Error in Next.js App Router

Mahdi BEN RHOUMA — Thu, 18 Jun 2026 23:14:07 +0000

Your build fails, or a route logs at runtime:

Dynamic server usage: Route /dashboard couldn't be rendered statically
because it used `cookies`.

(Swap cookies for headers, searchParams, or no-store fetch.) This is DYNAMIC_SERVER_USAGE. It is not a bug — it's Next.js telling you a route it wanted to prerender at build time depends on request-time data. The fix is a decision: should this route be dynamic, or should it not need that data at all?

{ name: "Next.js", version: "App Router 13.4–15" },
{ name: "Rendering", version: "static vs dynamic" },
]} />

The mental model: build time vs request time

Static rendering = the route is rendered at build time, cached, CDN-shareable.
Dynamic rendering = the route is rendered per request.

The following are dynamic functions — they depend on the incoming request and force dynamic rendering:

cookies() and headers() (from next/headers)
searchParams (the page prop)
draftMode()
a fetch(..., { cache: 'no-store' }) (or revalidate: 0)

Per the docs: "During rendering, if a dynamic function or uncached data request is discovered, Next.js will switch to dynamically rendering the whole route." Normally that switch is automatic and silent — Next throws DynamicServerError internally and catches it. You see the error "when it's uncaught" — typically because the route was forced static (dynamic = 'error', a static route handler, generateStaticParams, sitemap/metadata files) while a child still calls a dynamic API, or the call ran outside Next's tracked async context.

Fix 1 — Opt into dynamic rendering (when the data IS request-specific)

// app/dashboard/page.tsx
export const dynamic = 'force-dynamic'

The docs describe this as forcing "routes being rendered for each user at request time... equivalent to getServerSideProps() in the pages directory." This is the right fix when the route genuinely needs per-request data (the user's session, a header, query params).

export const revalidate = 0 achieves the same dynamic outcome via the caching model. Both are valid; force-dynamic is the more explicit switch.

Fix 2 — Remove the dynamic dependency (when it SHOULD be static)

If the data isn't actually request-specific, don't go dynamic — fix the cause so the route stays fast and cacheable:

wrong="export const dynamic = 'force-dynamic' // slapped on to silence the error, even though the page is the same for everyone"
right="Drop the stray cookies()/headers() call, or add caching to the fetch — let the route prerender."
/>

Reading a cookie you don't need? Remove the cookies() call.
Fetching with cache: 'no-store' for data that rarely changes? Switch to cache: 'force-cache' or revalidate: N.
Want it fully static? export const dynamic = 'force-static' forces cookies(), headers(), and useSearchParams() to return empty values.

Fix 3 — Isolate the dynamic part in Suspense

Keep most of the page static and stream only the request-time piece:

import { Suspense } from 'react'

export default function Page() {
  return (
    <>
      <StaticHeader />
      <Suspense fallback={<Skeleton />}>
        <UserPanel />   {/* this one reads cookies() */}
      </Suspense>
    </>
  )
}

Without Partial Prerendering, a dynamic API still makes the whole route dynamic — Suspense buys you streaming, not a static shell. With PPR enabled it becomes the canonical way to keep a static shell plus a dynamic hole. Don't oversell Suspense as a static-shell fix on stable Next 14/15 without PPR.

Route segment config reference

export const dynamic = 'auto'      // 'auto' | 'force-dynamic' | 'error' | 'force-static'
export const revalidate = false    // false | 0 | number  (Node.js runtime only)
export const dynamicParams = true  // true | false

Values must be statically analyzable — revalidate = 600 is valid, revalidate = 60 * 10 is not.

In Next.js 15, cookies, headers, draftMode, params, and searchParams became asynchronous — you must await them: const cookieStore = await cookies(). In 13.4/14 they were synchronous. There's a codemod: npx @next/codemod@canary next-async-request-api .. Also note Next 15 stopped caching GET route handlers and client navigations by default, which shifts where this error shows up.

You call cookies()/headers() inside setTimeout/setInterval or after an un-awaited promise — that's a separate async-context bug; read the value first, then enter the deferred context.
You're on Next.js 16 with Cache Components enabled — the dynamic/revalidate route configs are removed there; this guide targets stable Next 14/15 semantics.

Official references: dynamic-server-error, app-static-to-dynamic-error, route segment config, Next.js 15 blog.

Frequently Asked Questions

What causes "Dynamic server usage" in Next.js?

A route Next wanted to prerender called a dynamic function — cookies(), headers(), draftMode(), searchParams, or a no-store fetch — which needs the incoming request. Next normally switches to dynamic rendering automatically; the error surfaces when that switch is blocked or the call ran outside Next's tracked context.

How do I make a Next.js route dynamic?

Add export const dynamic = 'force-dynamic' (or revalidate = 0) to the segment. It renders per request — the App Router equivalent of getServerSideProps.

Should I always add force-dynamic?

No. If the data isn't request-specific, remove the dynamic dependency so the route stays static and fast. force-dynamic is right only when the route must render per request.

Originally published at https://www.iloveblogs.blog

Fix ChunkLoadError: Loading Chunk Failed in Next.js

Mahdi BEN RHOUMA — Thu, 18 Jun 2026 23:14:05 +0000

You shipped a deploy. Minutes later your error tracker lights up:

ChunkLoadError: Loading chunk 5760 failed.
(error: https://yourapp.com/_next/static/chunks/5760-40c839657ea31a15.js)

Or the timeout variant:

ChunkLoadError: Loading chunk app/layout failed.
(timeout: https://yourapp.com/_next/static/chunks/app/layout.js)

The instinct is to wrap something in a try/catch. Don't — this is almost never a code bug. It is version skew: a browser is asking for a JavaScript chunk that your latest deploy already deleted. This guide explains the real causes and the official Next.js configuration that fixes each one.

{ name: "Next.js", version: "13.4–16" },
{ name: "App Router", version: "& Pages Router" },
{ name: "webpack", version: "chunk loading" },
]} />

What the two variants actually mean

The parenthetical in the message tells you which failure mode you hit:

(error: <url>) — the request for the chunk completed but failed, typically a 404. The file no longer exists at that hashed path.
(timeout: <url>) — the request never completed within webpack's chunk-load timeout (default 120000 ms, set via output.chunkLoadTimeout). This is the network/slow-connection branch.

Knowing which one you have points you at the right fix below.

Root cause #1: stale chunks after a new deploy (the common one)

Next.js fingerprints static assets with a content hash in the filename (5760-40c839657ea31a15.js). When you deploy, the bundle changes, the hashes change, and the old files are gone. Any client still running the previous version — an open tab, a bfcache restore, a CDN serving stale HTML — requests the old chunk path and gets a 404.

Next.js documents this exact failure mode under self-hosting and calls it version skew: "Missing assets: The client requests JavaScript or CSS files that no longer exist on the server." Rolling deployments make it worse, because some instances serve new assets while others still serve old ones.

Give every build a stable deployment identifier. Next.js appends it (?dpl=…) to asset URLs and exposes it via response headers; when the client detects a mismatch between its deployment ID and the server's, it triggers a hard navigation (full page reload) instead of a broken client-side navigation.

// next.config.js
module.exports = {
  deploymentId: process.env.DEPLOYMENT_VERSION || process.env.GIT_SHA,
}

This is the official, intended mechanism for exactly this cause. See the deploymentId config and self-hosting version skew docs.

deploymentId shipped as experimental.deploymentId in v13.4.10 and was promoted to a top-level config option in v14.1.4. Before 14.1.4, use the experimental. form.

Root cause #2: rolling deploys with mismatched build IDs

If you run multiple containers/instances behind a load balancer, each one generates its own build ID by default, so they disagree on chunk names during a rollout. Pin the build ID so every instance agrees:

// next.config.js
module.exports = {
  generateBuildId: async () => process.env.GIT_HASH,
}

Documented under generateBuildId.

Root cause #3: self-hosted `output: 'standalone'` builds

If you self-host with output: 'standalone' (common in Docker), the minimal server does not copy public or .next/static by default. Every chunk then 404s until you copy them. Straight from the output docs:

cp -r public .next/standalone/ && cp -r .next/static .next/standalone/.next/

Root cause #4: a CDN or proxy serving stale HTML

Next.js sets Cache-Control: public, max-age=31536000, immutable on hashed static assets — they are safe to cache forever because the hash changes when the content does. The problem appears when a CDN, reverse proxy, or service worker caches the HTML (which references the hashed chunks) past its life, or rewrites/misroutes /_next/static/ requests.

wrong="CDN caches the HTML document for hours, so it keeps pointing browsers at chunk hashes from a deploy you already replaced."
right="Let the immutable headers on /_next/static stand, keep HTML short-lived, and never strip or redirect /_next/static/ at the proxy. Use assetPrefix if assets live on a separate domain."
/>

If you serve assets from a separate domain/CDN, configure assetPrefix so chunk URLs are generated correctly.

Root cause #5: network failure / timeout (the `(timeout:)` variant)

A flaky connection, an aggressive ad-blocker, or a slow CDN can make the chunk request hang past webpack's limit. That surfaces as the (timeout:) form. The relevant knob is webpack's output.chunkLoadTimeout (default 120000 ms), reachable through a webpack override in next.config. Treat this as a webpack-level setting, not a Next.js flag.

A safety net for dynamic imports

When you load a component with next/dynamic or React.lazy, React's docs are explicit: "If the Promise rejects, React will throw the rejection reason for the nearest Error Boundary to handle." So a failed chunk load is catchable by an error boundary — you can render a "please refresh" fallback.

The error-boundary catch is documented (see React lazy). The widely-shared "catch ChunkLoadError → reload once via sessionStorage guard" pattern is a community technique, not official guidance. It is a reasonable last-resort UX layer, but it does not address the root cause — ship deploymentId first.

The chunk genuinely fails to parse in an old browser (a real SyntaxError, not version skew) — that is a code/transpile-target issue, not a deploy issue.
You only see it locally in dev — clear .next and restart; dev chunking differs from production.
Your import() path is actually wrong or the module throws at module scope — fix the import, an error boundary only masks it.

Decision guide

Symptom	Most likely cause	First fix
Spikes right after each deploy	Version skew (#1)	`deploymentId`
Only on multi-instance/rolling deploys	Mismatched build IDs (#2)	`generateBuildId`
Self-hosted Docker, 404 on every chunk	Standalone missing assets (#3)	copy `.next/static` + `public`
Behind Cloudflare/proxy, intermittent	Stale HTML cache (#4)	fix CDN/HTML cache headers
`(timeout:)` in the message	Network/slow load (#5)	check connectivity / chunkLoadTimeout

Frequently Asked Questions

Why do I get ChunkLoadError only after deploying?

Because a user had a tab open (or a CDN served cached HTML) referencing the old hashed chunk filenames. Your new deploy replaced those files with new hashes, so the old chunk URL now 404s. Next.js calls this version skew — set deploymentId so Next forces a full reload on mismatch.

Is ChunkLoadError a bug in my code?

Usually not. It is a deployment/caching class of problem, which is why upgrading Next.js rarely fixes it. The fix is configuration — deploymentId, generateBuildId, correct CDN headers.

Does a try/catch around import() fix it?

A React error boundary catches the rejected dynamic import so you can show fallback UI. The "reload once" pattern on top is a community technique, not official — use it as a safety net, not the fix.

Originally published at https://www.iloveblogs.blog

Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)

Mahdi BEN RHOUMA — Thu, 18 Jun 2026 12:34:30 +0000

Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)

A 400ms Supabase query on your laptop becomes a 3-second query in production. I have debugged this pattern across a dozen Next.js + Supabase projects. The causes are almost always the same six things.

Here is every one of them with the exact fix.

How to Find Your Slow Queries First

Before fixing, find the actual culprits. Do not guess.

Go to Supabase Dashboard → Database → Query Performance. This shows queries sorted by total execution time — meaning queries that run 10ms but fire 10,000 times show up above queries that take 2 seconds but run once.

For a specific query, run this in the SQL editor:

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM posts WHERE user_id = 'abc123';

Look for:

Seq Scan on large tables — means no index is being used
Rows Removed by Filter: 50000 — filtering millions of rows at runtime
High Buffers: shared hit — reading data not in cache

Now the six causes.

1. Missing Indexes on Filtered Columns

This is responsible for 80% of slow queries I see. If you filter, sort, or join on a column, it needs an index.

-- This query does a full table scan without an index
SELECT * FROM posts WHERE user_id = $1 ORDER BY created_at DESC;

Fix:

-- Create a composite index on both columns used in the query
CREATE INDEX CONCURRENTLY idx_posts_user_created
ON posts (user_id, created_at DESC);

CONCURRENTLY creates the index without locking the table — safe for production.

Common columns that almost always need indexes:

user_id on any user-owned table
created_at on tables you sort by date
slug on tables you look up by URL
Foreign key columns (post_id, organization_id, etc.)

Supabase does not create indexes on foreign keys automatically — you must add them yourself.

-- Check if an index exists on a column
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'posts';

2. RLS Policies Calling auth.uid() Per Row

This is the hidden killer. You enable Row Level Security, write a policy like this:

-- SLOW: auth.uid() is called once per row
CREATE POLICY "Users can read own posts"
ON posts FOR SELECT
USING (user_id = auth.uid());

On a table with 100,000 rows, auth.uid() is evaluated 100,000 times per query. It is a function call with overhead.

Fix — wrap in a subquery so it evaluates once:

-- FAST: auth.uid() is called once per query
CREATE POLICY "Users can read own posts"
ON posts FOR SELECT
USING (user_id = (SELECT auth.uid()));

Same policy, same security, dramatically faster. Apply this to every RLS policy you have.

While you are there, also check for policies that call functions or do subqueries on large tables. Every policy condition is evaluated for every row that passes the WHERE clause.

3. The N+1 Query Problem

You fetch posts, then for each post you fetch the author's profile. That is 1 + N queries.

// SLOW: 1 query for posts + 1 query per post for the author
const { data: posts } = await supabase.from('posts').select('*');

for (const post of posts) {
  const { data: author } = await supabase
    .from('profiles')
    .select('name, avatar')
    .eq('id', post.user_id)
    .single();

  post.author = author;
}

Fix — use PostgREST's relational embedding to fetch everything in one query:

// FAST: 1 query with a JOIN
const { data: posts } = await supabase
  .from('posts')
  .select(`
    id,
    title,
    slug,
    created_at,
    author:profiles (
      name,
      avatar_url
    )
  `);

This generates a single SQL query with a JOIN. PostgREST infers the relationship from the foreign key posts.user_id → profiles.id.

For this to work, the foreign key must exist in the database schema. If it does not, add it:

ALTER TABLE posts
ADD CONSTRAINT posts_user_id_fkey
FOREIGN KEY (user_id) REFERENCES profiles(id);

4. Fetching All Columns with select("*")

Every column you select is transmitted over the network and deserialized in your app. If your posts table has a content column with 10KB of markdown per row, selecting 50 posts returns 500KB just for that column — even when you only need titles for a list view.

// SLOW: returns all columns including large content field
const { data } = await supabase.from('posts').select('*');

// FAST: only the columns the list view actually needs
const { data } = await supabase
  .from('posts')
  .select('id, title, slug, excerpt, created_at, author_name');

For detail pages where you need the full content, select everything. For list views, cards, and previews — be precise.

This also matters for RLS: PostgreSQL evaluates policies before column filtering, so you are still scanning the same rows. But you reduce network overhead and deserialization time significantly.

5. No Connection Pooling in Serverless Environments

Vercel Functions (and any serverless environment) create a new database connection on every invocation. PostgreSQL has a limit of ~100 concurrent connections on the free tier. Under load, you hit too many connections errors and your app degrades completely.

Fix: use Supabase's built-in connection pooler (pgBouncer).

In your .env.local, you should have two database URLs:

# Direct connection — use for migrations and long-running scripts
DATABASE_URL=postgresql://postgres:[password]@db.[ref].supabase.co:5432/postgres

# Pooler connection — use for your app
DATABASE_URL=postgresql://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres?pgbouncer=true

For Supabase JS client in Next.js:

// lib/supabase/server.ts
import { createServerClient } from '@supabase/ssr';
import { cookies } from 'next/headers';

export function createClient() {
  const cookieStore = cookies();

  return createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        getAll() { return cookieStore.getAll(); },
        setAll(cookiesToSet) {
          cookiesToSet.forEach(({ name, value, options }) =>
            cookieStore.set(name, value, options)
          );
        },
      },
    }
  );
}

The Supabase JS client uses the REST API over HTTPS — it does not hold raw PostgreSQL connections open. The connection pooling issue mainly affects Prisma, Drizzle, and raw pg connections. If you are using only the Supabase JS client, you are already safe.

6. Fetching More Data Than You Display

Pagination is obvious in theory but missed constantly in practice.

// SLOW: fetches ALL posts every time
const { data } = await supabase
  .from('posts')
  .select('id, title, slug')
  .order('created_at', { ascending: false });

// FAST: fetches 20 posts, skipping the first (page - 1) * 20
const PAGE_SIZE = 20;
const { data, count } = await supabase
  .from('posts')
  .select('id, title, slug', { count: 'exact' })
  .order('created_at', { ascending: false })
  .range((page - 1) * PAGE_SIZE, page * PAGE_SIZE - 1);

For infinite scroll, cursor-based pagination is even faster because OFFSET gets slower as you go deeper into the table:

// Cursor-based: always fast regardless of page depth
const { data } = await supabase
  .from('posts')
  .select('id, title, slug, created_at')
  .order('created_at', { ascending: false })
  .lt('created_at', lastSeenCreatedAt) // cursor
  .limit(20);

Putting It All Together

Here is what a well-optimized Supabase query looks like:

// Good: specific columns, pagination, relational data in one query
const { data: posts, count } = await supabase
  .from('posts')
  .select(
    `
    id,
    title,
    slug,
    excerpt,
    created_at,
    author:profiles (name, avatar_url)
  `,
    { count: 'exact' }
  )
  .eq('status', 'published')
  .order('created_at', { ascending: false })
  .range(0, 19);

And the supporting index:

CREATE INDEX CONCURRENTLY idx_posts_published_created
ON posts (status, created_at DESC)
WHERE status = 'published';

The WHERE status = 'published' makes this a partial index — smaller, faster, only indexes rows your query actually touches.

Run these optimizations in order — missing indexes first (highest impact), then RLS policies, then query structure. Most apps go from 2-second queries to under 100ms after fixing just the first two.

Originally published at https://www.iloveblogs.blog

Why Your Supabase RLS Policies Are Silently Failing (And How to Debug Them)

Mahdi BEN RHOUMA — Thu, 18 Jun 2026 12:34:28 +0000

Why Your Supabase RLS Policies Are Silently Failing (And How to Debug Them)

You write a query. It returns nothing. No error, no warning — just an empty array.

You check the data. It's there. You check your query. It's correct. You spend 45 minutes before realizing: RLS is filtering out every row because your policy has a bug.

This is the most frustrating thing about Row Level Security. It doesn't fail loudly. It silently returns nothing, and your application looks broken for reasons that have nothing to do with your application code.

Here's the debugging workflow I use every time.

Step 1: Confirm RLS Is the Problem

First, rule out everything else. Run this in the Supabase SQL editor:

-- Temporarily bypass RLS to confirm data exists
SELECT * FROM your_table LIMIT 10;

The SQL editor runs as the postgres superuser by default, which bypasses RLS. If you see data here but not in your app, RLS is the culprit.

If you want to simulate your app's query exactly:

-- Simulate an authenticated user
SET LOCAL role = authenticated;
SET LOCAL "request.jwt.claims" = '{"sub": "your-actual-user-uuid"}';

SELECT * FROM your_table LIMIT 10;

If this returns nothing but the superuser query returns data, you've confirmed the policy is filtering your rows.

Step 2: Check Which Policies Are Active

SELECT
  schemaname,
  tablename,
  policyname,
  permissive,
  roles,
  cmd,
  qual,
  with_check
FROM pg_policies
WHERE tablename = 'your_table';

Look at the qual column — that's the USING expression. Look at with_check — that's the WITH CHECK expression. Read them carefully. The bug is usually obvious once you see the raw SQL.

Common things to look for:

auth.uid() = user_id — is user_id actually the column name in your table?
org_id = $1 — is there a missing join or subquery?
A policy that only covers SELECT but not INSERT, leaving inserts silently blocked

Step 3: The Most Common Bugs

Wrong column name. Your policy says user_id but your column is created_by or owner_id. Returns empty, no error.

NULL comparison. auth.uid() = user_id evaluates to NULL when user_id is NULL. NULL is not true, so the row is filtered out. If you have rows with NULL user_id, they're invisible to everyone.

Missing policy for the operation. You have a SELECT policy but no INSERT policy. Inserts return no error — they just silently do nothing. Check cmd in pg_policies to see which operations are covered.

Multiple permissive policies with unexpected OR behavior. Two FOR SELECT policies on the same table are OR'd together. If one is too permissive, it overrides the other. If one is too restrictive, the other might still let rows through. This is often surprising.

Using auth.jwt() claims that are stale. If you store roles in JWT claims, a user whose role changed won't see the update until their token expires. Their old role is still in the claim, and your policy is evaluating the old value.

Step 4: Test Each Policy in Isolation

For complex policies with subqueries, test the subquery directly:

SET LOCAL role = authenticated;
SET LOCAL "request.jwt.claims" = '{"sub": "your-user-uuid"}';

-- Test the subquery from your policy
SELECT EXISTS (
  SELECT 1 FROM org_members
  WHERE org_members.org_id = 'your-org-id'
  AND org_members.user_id = auth.uid()
);

If this returns false when you expect true, the problem is in your membership data, not your policy logic.

Step 5: Check the Service Role Bypass

If your Next.js app uses the service role key (SUPABASE_SERVICE_ROLE_KEY), RLS is bypassed entirely. This is intentional for admin operations, but it means you can't test RLS behavior using the service role client.

To test RLS from your app, temporarily use the anon key client:

// Temporary debug client — don't use in production
const debugClient = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
)

If this returns different results than your service role client, RLS is doing something you didn't expect.

Step 6: Use EXPLAIN to Check Performance

Once your policies work correctly, check that they're not killing performance:

EXPLAIN ANALYZE
SELECT * FROM projects WHERE org_id = 'your-org-id';

Look for Seq Scan on large tables. If your policy uses a subquery without an index, every row evaluation triggers a full table scan. Add indexes on columns used in policy conditions:

CREATE INDEX idx_org_members_user_id ON org_members(user_id);

The Debugging Checklist

When RLS returns empty results:

Run the query as superuser in SQL editor — does data exist?
Run with SET LOCAL role = authenticated — does it disappear?
Check pg_policies — are the right policies active for the right operations?
Read the qual expression — does the column name match your schema?
Test subqueries in isolation — does the membership/role lookup return what you expect?
Check for NULL values in columns used in policy conditions
Verify you're not accidentally using the service role key in your test

RLS bugs are almost always one of these six things. Work through the list and you'll find it.

The deeper lesson: treat RLS like application code. Write it in small pieces, test each piece in isolation, and use the SQL editor's role simulation before deploying. Silent failures are only silent if you're not looking in the right place.

Have a specific RLS pattern you're struggling with? [INTERNAL LINK: supabase-rls-policy-design-patterns] covers the advanced patterns in depth.

Originally published at https://www.iloveblogs.blog

Supabase RLS Policy Design Patterns Beyond the Basics

Mahdi BEN RHOUMA — Wed, 17 Jun 2026 22:55:10 +0000

Supabase RLS Policy Design Patterns Beyond the Basics

Row Level Security is one of the most powerful features in Supabase — and one of the most misunderstood. Most tutorials stop at auth.uid() = user_id. That gets you through a simple personal data model, but the moment you introduce teams, roles, organizations, or any shared resource, you need a more structured approach.

This guide covers advanced RLS policy patterns used in real production apps: multi-role systems, team-based access control, hierarchical permissions, and how to keep policies performant as your data grows. Every pattern includes copy-paste SQL you can adapt immediately.

Estimated read time: 16 minutes

Prerequisites

Supabase project with Auth enabled
Familiarity with basic RLS (CREATE POLICY, auth.uid())
Basic PostgreSQL knowledge (JOINs, functions)
Next.js app using @supabase/ssr or @supabase/auth-helpers-nextjs

How RLS Actually Works (The Mental Model You Need)

Before patterns, get this straight: RLS policies are predicate filters appended to every query. When you write:

CREATE POLICY "users can read own data"
ON profiles
FOR SELECT
USING (auth.uid() = user_id);

Postgres rewrites every SELECT on profiles to include WHERE auth.uid() = user_id. This happens at the database level — no application code can bypass it (unless using the service role key).

Two clauses matter:

USING — filters rows for SELECT, UPDATE, DELETE
WITH CHECK — validates rows on INSERT, UPDATE

Always define both for tables that accept writes.

Pattern 1: Role-Based Access with a Roles Table

The naive approach is storing roles in user_metadata. Don't. Metadata is controlled by the client and can be spoofed. Store roles in a database table.

-- roles table
CREATE TABLE user_roles (
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('admin', 'editor', 'viewer')),
  PRIMARY KEY (user_id)
);

-- helper function (security definer = runs as postgres, not the calling user)
CREATE OR REPLACE FUNCTION get_user_role()
RETURNS TEXT
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT role FROM user_roles WHERE user_id = auth.uid();
$$;

Now use it in policies:

-- admins can read everything, others read only published content
CREATE POLICY "role-based content access"
ON articles
FOR SELECT
USING (
  get_user_role() = 'admin'
  OR status = 'published'
);

-- only admins and editors can insert
CREATE POLICY "editors can create articles"
ON articles
FOR INSERT
WITH CHECK (
  get_user_role() IN ('admin', 'editor')
);

The SECURITY DEFINER function is critical here. Without it, the subquery inside the policy runs as the calling user, which can cause permission errors on the user_roles table itself.

[INTERNAL LINK: supabase-authentication-authorization]

Pattern 2: Team / Organization Membership

This is the most common pattern in SaaS apps. Users belong to organizations, and resources belong to organizations.

-- org membership table
CREATE TABLE org_members (
  org_id UUID NOT NULL,
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member')),
  PRIMARY KEY (org_id, user_id)
);

-- projects belong to orgs
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);

Policy: members of an org can read its projects.

CREATE POLICY "org members can read projects"
ON projects
FOR SELECT
USING (
  EXISTS (
    SELECT 1 FROM org_members
    WHERE org_members.org_id = projects.org_id
    AND org_members.user_id = auth.uid()
  )
);

-- only org admins/owners can create projects
CREATE POLICY "org admins can create projects"
ON projects
FOR INSERT
WITH CHECK (
  EXISTS (
    SELECT 1 FROM org_members
    WHERE org_members.org_id = projects.org_id
    AND org_members.user_id = auth.uid()
    AND org_members.role IN ('owner', 'admin')
  )
);

Performance note: Add an index on org_members(user_id) and org_members(org_id). The EXISTS subquery runs on every row evaluation — without indexes, this becomes a full table scan at scale.

CREATE INDEX idx_org_members_user_id ON org_members(user_id);
CREATE INDEX idx_org_members_org_id ON org_members(org_id);

Pattern 3: Hierarchical Permissions (Owner > Admin > Member)

Sometimes you need cascading permissions: owners can do everything admins can, admins can do everything members can. A helper function keeps policies clean.

CREATE OR REPLACE FUNCTION user_org_role(p_org_id UUID)
RETURNS TEXT
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT role FROM org_members
  WHERE org_id = p_org_id AND user_id = auth.uid();
$$;

-- helper: check if user has at least a given role level
CREATE OR REPLACE FUNCTION user_has_org_role(p_org_id UUID, p_min_role TEXT)
RETURNS BOOLEAN
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$
  SELECT CASE
    WHEN p_min_role = 'member' THEN
      user_org_role(p_org_id) IN ('member', 'admin', 'owner')
    WHEN p_min_role = 'admin' THEN
      user_org_role(p_org_id) IN ('admin', 'owner')
    WHEN p_min_role = 'owner' THEN
      user_org_role(p_org_id) = 'owner'
    ELSE false
  END;
$$;

Now policies read like documentation:

-- delete requires owner
CREATE POLICY "owners can delete projects"
ON projects
FOR DELETE
USING (user_has_org_role(org_id, 'owner'));

-- update requires admin or above
CREATE POLICY "admins can update projects"
ON projects
FOR UPDATE
USING (user_has_org_role(org_id, 'admin'))
WITH CHECK (user_has_org_role(org_id, 'admin'));

Pattern 4: Resource Sharing and Invitations

Users sometimes need access to specific resources without being full org members — think shared documents or guest access.

CREATE TABLE resource_shares (
  resource_id UUID NOT NULL,
  resource_type TEXT NOT NULL,
  shared_with UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  permission TEXT NOT NULL CHECK (permission IN ('read', 'write')),
  expires_at TIMESTAMPTZ,
  PRIMARY KEY (resource_id, shared_with)
);

Policy that combines ownership and sharing:

CREATE POLICY "owners and shared users can read documents"
ON documents
FOR SELECT
USING (
  -- owner
  owner_id = auth.uid()
  OR
  -- explicitly shared
  EXISTS (
    SELECT 1 FROM resource_shares
    WHERE resource_shares.resource_id = documents.id
    AND resource_shares.resource_type = 'document'
    AND resource_shares.shared_with = auth.uid()
    AND (resource_shares.expires_at IS NULL OR resource_shares.expires_at > now())
  )
);

Pattern 5: Public + Authenticated Mixed Access

A common pattern for content sites: public content is readable by anyone, private content only by the owner.

CREATE POLICY "public content is readable by all"
ON posts
FOR SELECT
USING (
  visibility = 'public'
  OR author_id = auth.uid()
);

For anonymous users, auth.uid() returns NULL. The OR author_id = auth.uid() clause evaluates to NULL = NULL which is false in SQL — so anonymous users only see public posts. This is correct behavior, but it's worth being explicit about.

If you want to allow anonymous reads of public content but block all writes:

-- no INSERT policy = nobody can insert (including authenticated users)
-- add explicit policies only for the roles that need write access

Absence of a policy is a deny. This is the default-deny model that makes RLS safe.

Pattern 6: Audit Trails Without Bypassing RLS

A common mistake: using the service role in a trigger to write audit logs, which bypasses RLS on the audit table. Instead, use a SECURITY DEFINER function that writes to the audit table directly.

CREATE TABLE audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name TEXT,
  operation TEXT,
  row_id UUID,
  changed_by UUID,
  changed_at TIMESTAMPTZ DEFAULT now(),
  old_data JSONB,
  new_data JSONB
);

-- RLS: only admins can read audit logs
ALTER TABLE audit_log ENABLE ROW LEVEL SECURITY;

CREATE POLICY "admins read audit log"
ON audit_log
FOR SELECT
USING (get_user_role() = 'admin');

-- trigger function runs as postgres (SECURITY DEFINER)
CREATE OR REPLACE FUNCTION log_changes()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO audit_log (table_name, operation, row_id, changed_by, old_data, new_data)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    COALESCE(NEW.id, OLD.id),
    auth.uid(),
    CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD)::jsonb ELSE NULL END,
    CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW)::jsonb ELSE NULL END
  );
  RETURN COALESCE(NEW, OLD);
END;
$$;

[INTERNAL LINK: supabase-postgres-functions-triggers-guide]

Common Pitfalls

Forgetting WITH CHECK on UPDATE policies. USING controls which rows can be targeted, but WITH CHECK controls what the row looks like after the update. Without WITH CHECK, a user could update a row they own to assign it to another user.

Using auth.jwt() claims for authorization. JWT claims are set at login time and don't reflect real-time role changes. A user whose role was revoked still has the old claim until their token expires. Use database lookups via SECURITY DEFINER functions instead.

Not testing with the anon key. Always test your policies using the anon key (not the service role) in the Supabase dashboard SQL editor. The service role bypasses everything.

Policies on tables without indexes. Every policy condition is evaluated per row. A subquery in a policy without an index causes a full table scan for every row in the outer query. Profile with EXPLAIN ANALYZE before going to production.

Multiple permissive policies are OR'd together. If you have two FOR SELECT policies on the same table, a row is visible if either policy passes. This is often surprising. Use a single policy with explicit OR conditions if you want clear logic.

Testing Your Policies

Use the Supabase SQL editor with SET LOCAL role = authenticated and SET LOCAL request.jwt.claims = '{"sub": "<user-id>"}' to simulate a specific user:

-- simulate a specific user
SET LOCAL role = authenticated;
SET LOCAL "request.jwt.claims" = '{"sub": "your-user-uuid-here"}';

-- now run your query — it will respect RLS as that user
SELECT * FROM projects WHERE org_id = 'your-org-id';

This is far faster than testing through your application layer.

Summary and Next Steps

Advanced RLS comes down to a few principles: store roles in the database (not JWT claims), use SECURITY DEFINER functions to encapsulate expensive lookups, index every column used in policy conditions, and always test with the anon key.

The patterns here — role tables, org membership, hierarchical roles, resource sharing — cover the vast majority of real-world authorization requirements. Combine them as needed for your data model.

Supabase Realtime Gotchas: 7 Issues and How to Fix Them

Mahdi BEN RHOUMA — Wed, 17 Jun 2026 22:55:08 +0000

Supabase Realtime Gotchas: 7 Issues and How to Fix Them

Supabase Realtime is powerful for building collaborative, real-time features. But it's also a common source of bugs, memory leaks, and performance issues. I've debugged dozens of realtime issues in production applications, and most stem from a few common mistakes.

Here are the 7 most common Supabase Realtime gotchas and how to fix them.

1. Forgetting to Enable Realtime on Tables

The Problem:

You set up a realtime subscription, but you never receive any updates. You spend hours debugging, only to discover realtime wasn't enabled on the table.

// ❌ Bad: Realtime not enabled
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const supabase = createClient()

  useEffect(() => {
    // This subscription will never fire!
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe()

    return () => subscription.unsubscribe()
  }, [])

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

Why It Happens:

By default, Supabase doesn't enable Realtime on tables for security and performance reasons. You must explicitly enable it.

The Fix:

Enable Realtime in Supabase Dashboard:

Go to Database → Replication
Find your table
Toggle Realtime on

Or use SQL:

-- Enable realtime for a table
ALTER PUBLICATION supabase_realtime ADD TABLE posts;

-- Disable realtime for a table
ALTER PUBLICATION supabase_realtime DROP TABLE posts;

2. Memory Leaks from Unsubscribed Listeners

The Problem:

Your app works fine initially, but after a few hours, it becomes sluggish. Memory usage keeps growing. You have a memory leak from realtime subscriptions.

// ❌ Bad: Memory leak
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const supabase = createClient()

  useEffect(() => {
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe()

    // Missing: unsubscribe on cleanup!
  }, [])

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

Why It Leaks:

The subscription stays active even after the component unmounts. Each time the component remounts, a new subscription is created. After many mounts/unmounts, you have dozens of active subscriptions consuming memory.

The Fix:

Always unsubscribe in the cleanup function:

// ✅ Good: Proper cleanup
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const supabase = createClient()

  useEffect(() => {
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe()

    // Cleanup: unsubscribe when component unmounts
    return () => {
      subscription.unsubscribe()
    }
  }, [])

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

Pro Tip: Use React DevTools Profiler to detect memory leaks. Watch memory usage as you navigate between pages.

3. Duplicate Subscriptions Causing Duplicate Events

The Problem:

You receive the same event twice. Or three times. Or more. Your UI updates multiple times for a single database change.

// ❌ Bad: Duplicate subscriptions
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const supabase = createClient()

  // This runs twice in development (React StrictMode)
  // and creates duplicate subscriptions
  useEffect(() => {
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe()

    return () => subscription.unsubscribe()
  }, []) // Missing dependency tracking

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

Why It Happens:

In React development mode (StrictMode), effects run twice to detect side effects. If you're not careful, you create duplicate subscriptions. Also, if you subscribe multiple times in the same component, you get duplicate events.

The Fix:

Use a ref to track subscription state:

// ✅ Good: Prevent duplicate subscriptions
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const subscriptionRef = useRef(null)
  const supabase = createClient()

  useEffect(() => {
    // Only subscribe once
    if (subscriptionRef.current) return

    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe()

    subscriptionRef.current = subscription

    return () => {
      subscription.unsubscribe()
      subscriptionRef.current = null
    }
  }, [])

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

4. RLS Policies Blocking Realtime Updates

The Problem:

Realtime subscriptions work in development but fail in production. You're not receiving any updates.

// ❌ Bad: RLS policy blocks realtime
'use client'

export function UserPosts() {
  const [posts, setPosts] = useState([])
  const supabase = createClient()

  useEffect(() => {
    // This subscription fails silently if RLS policy denies access
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe()

    return () => subscription.unsubscribe()
  }, [])

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

Why It Fails:

Realtime respects RLS policies. If your RLS policy denies access, the subscription silently fails. You won't see an error—you just won't receive updates.

The Fix:

Ensure your RLS policies allow realtime subscriptions:

-- ✅ Good: RLS policy allows realtime
CREATE POLICY "Users can view own posts"
  ON posts FOR SELECT
  USING (auth.uid() = user_id);

-- Test the policy
SELECT * FROM posts WHERE auth.uid() = user_id;

Debug Tip: Check browser console for realtime errors. Enable debug logging:

supabase.realtime.setAuth(token)

5. Not Handling Realtime Reconnection

The Problem:

The user's internet drops for a few seconds. The realtime connection is lost. They don't receive updates until they refresh the page.

// ❌ Bad: No reconnection handling
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const supabase = createClient()

  useEffect(() => {
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe()

    // Missing: handle disconnection and reconnection
    return () => subscription.unsubscribe()
  }, [])

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

Why It Fails:

Network interruptions are inevitable. If you don't handle reconnection, users miss updates.

The Fix:

Listen for connection state changes:

// ✅ Good: Handle reconnection
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const [isConnected, setIsConnected] = useState(true)
  const supabase = createClient()

  useEffect(() => {
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe(
        (status) => {
          setIsConnected(status === 'SUBSCRIBED')
        }
      )

    return () => subscription.unsubscribe()
  }, [])

  return (
    <div>
      {!isConnected && <p>Reconnecting...</p>}
      {posts.map(p => <p key={p.id}>{p.title}</p>)}
    </div>
  )
}

6. Realtime in Server Components

The Problem:

You try to use realtime in a Server Component, but it doesn't work. You get errors about browser APIs.

// ❌ Bad: Realtime in Server Component
export default async function PostsPage() {
  const supabase = createServerClient()

  // This doesn't work! Realtime requires WebSocket (browser only)
  const subscription = supabase
    .from('posts')
    .on('*', (payload) => {
      console.log(payload)
    })
    .subscribe()

  return <div>Posts</div>
}

Why It Fails:

Realtime uses WebSocket connections, which only work in browsers. Server Components run on the server and don't have WebSocket support.

The Fix:

Use Client Components for realtime:

// ✅ Good: Realtime in Client Component
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const supabase = createClient()

  useEffect(() => {
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe()

    return () => subscription.unsubscribe()
  }, [])

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

7. Large Payloads Slowing Down Realtime

The Problem:

Realtime updates are slow. Each update takes several seconds to process. Your UI feels sluggish.

// ❌ Bad: Large payloads
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const supabase = createClient()

  useEffect(() => {
    // Fetching ALL columns, including large text fields
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .subscribe()

    return () => subscription.unsubscribe()
  }, [])

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

Why It's Slow:

You're sending large payloads over the WebSocket. If your posts table has large text fields, each update sends megabytes of data.

The Fix:

Select only the columns you need:

// ✅ Good: Minimal payloads
'use client'

export function LivePosts() {
  const [posts, setPosts] = useState([])
  const supabase = createClient()

  useEffect(() => {
    // Only select needed columns
    const subscription = supabase
      .from('posts')
      .on('*', (payload) => {
        setPosts(prev => [...prev, payload.new])
      })
      .select('id, title, created_at') // Only these columns
      .subscribe()

    return () => subscription.unsubscribe()
  }, [])

  return <div>{posts.map(p => <p key={p.id}>{p.title}</p>)}</div>
}

Realtime Debugging Checklist

✅ Realtime enabled on the table (Database → Replication)
✅ RLS policies allow access
✅ Subscriptions cleaned up on unmount
✅ No duplicate subscriptions
✅ Only selecting needed columns
✅ Handling reconnection
✅ Using Client Components (not Server Components)
✅ Monitoring memory usage
✅ Testing with network throttling

Conclusion

Supabase Realtime is incredibly powerful, but it requires careful handling. Enable realtime on tables, always unsubscribe, prevent duplicate subscriptions, and handle reconnection. With these practices, you'll build smooth, real-time features that delight users.

The key is testing thoroughly—especially with network throttling and connection interruptions. That's where most realtime bugs hide.

Originally published at https://www.iloveblogs.blog

Supabase Postgres Functions and Triggers: Complete Developer Guide

Mahdi BEN RHOUMA — Wed, 17 Jun 2026 12:59:19 +0000

Supabase Postgres Functions and Triggers: Complete Developer Guide

Moving logic from your application to the database seems counterintuitive. We've spent years learning to keep business logic in our code, not in SQL.

But Postgres functions and triggers solve real problems: ensuring data consistency across multiple applications, automating repetitive tasks, enforcing complex validation rules, and improving performance by reducing round trips.

This guide shows you when and how to use Postgres functions and triggers in Supabase effectively.

Prerequisites

Supabase project
Basic SQL knowledge
Understanding of database concepts
Familiarity with Supabase RLS

When to Use Database Functions

Use database functions when you need:

Data consistency across applications: Logic that must apply regardless of which app (web, mobile, admin panel) accesses the database.

Atomic operations: Multiple related changes that must succeed or fail together.

Performance: Complex queries that benefit from running close to the data.

Reusable logic: Calculations or validations used in multiple places.

Security: Operations that require elevated privileges without exposing service role key.

Don't use database functions for:

External API calls (use Edge Functions)
Complex business logic better suited to application code
Operations requiring extensive debugging and testing
Logic that changes frequently

Your First Postgres Function

Create a function to calculate user statistics:

-- Create function
CREATE OR REPLACE FUNCTION get_user_stats(user_id UUID)
RETURNS JSON
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  result JSON;
BEGIN
  SELECT json_build_object(
    'post_count', (
      SELECT COUNT(*) FROM posts WHERE posts.user_id = get_user_stats.user_id
    ),
    'comment_count', (
      SELECT COUNT(*) FROM comments WHERE comments.user_id = get_user_stats.user_id
    ),
    'total_likes', (
      SELECT COUNT(*) FROM likes WHERE likes.user_id = get_user_stats.user_id
    )
  ) INTO result;

  RETURN result;
END;
$$;

Call from Next.js:

const { data, error } = await supabase
  .rpc('get_user_stats', { user_id: userId })

console.log(data)
// { post_count: 42, comment_count: 128, total_likes: 256 }

This executes as a single database call instead of three separate queries.

Function Basics

Return Types

Functions can return different types:

-- Return single value
CREATE FUNCTION get_post_count(user_id UUID)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN (SELECT COUNT(*) FROM posts WHERE posts.user_id = get_post_count.user_id);
END;
$$;

-- Return single row
CREATE FUNCTION get_user_profile(user_id UUID)
RETURNS TABLE(name TEXT, email TEXT, created_at TIMESTAMPTZ)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT profiles.name, profiles.email, profiles.created_at
  FROM profiles
  WHERE profiles.id = get_user_profile.user_id;
END;
$$;

-- Return multiple rows
CREATE FUNCTION get_recent_posts(days INTEGER DEFAULT 7)
RETURNS SETOF posts
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT *
  FROM posts
  WHERE created_at > NOW() - (days || ' days')::INTERVAL
  ORDER BY created_at DESC;
END;
$$;

-- Return JSON
CREATE FUNCTION get_dashboard_data()
RETURNS JSON
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN json_build_object(
    'users', (SELECT COUNT(*) FROM profiles),
    'posts', (SELECT COUNT(*) FROM posts),
    'active_today', (SELECT COUNT(DISTINCT user_id) FROM activity WHERE date = CURRENT_DATE)
  );
END;
$$;

Security Modes

SECURITY DEFINER: Function runs with creator's privileges (bypasses RLS).

CREATE FUNCTION admin_delete_user(user_id UUID)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER -- Runs as function creator
AS $$
BEGIN
  DELETE FROM profiles WHERE id = user_id;
  DELETE FROM posts WHERE user_id = user_id;
  DELETE FROM comments WHERE user_id = user_id;
END;
$$;

SECURITY INVOKER: Function runs with caller's privileges (respects RLS).

CREATE FUNCTION get_my_posts()
RETURNS SETOF posts
LANGUAGE plpgsql
SECURITY INVOKER -- Runs as caller
AS $$
BEGIN
  RETURN QUERY
  SELECT * FROM posts WHERE user_id = auth.uid();
END;
$$;

Use SECURITY DEFINER carefully. It bypasses RLS and can create security vulnerabilities if not properly validated.

Practical Function Examples

Soft Delete with Audit Trail

CREATE FUNCTION soft_delete_post(post_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
DECLARE
  current_user_id UUID;
BEGIN
  -- Get current user
  current_user_id := auth.uid();

  -- Verify ownership
  IF NOT EXISTS (
    SELECT 1 FROM posts
    WHERE id = post_id AND user_id = current_user_id
  ) THEN
    RAISE EXCEPTION 'Post not found or access denied';
  END IF;

  -- Soft delete
  UPDATE posts
  SET
    deleted_at = NOW(),
    deleted_by = current_user_id
  WHERE id = post_id;

  -- Log to audit table
  INSERT INTO audit_logs (
    action,
    table_name,
    record_id,
    user_id,
    created_at
  ) VALUES (
    'soft_delete',
    'posts',
    post_id,
    current_user_id,
    NOW()
  );

  RETURN TRUE;
END;
$$;

Increment Counter Safely

CREATE FUNCTION increment_post_views(post_id UUID)
RETURNS INTEGER
LANGUAGE plpgsql
AS $$
DECLARE
  new_count INTEGER;
BEGIN
  UPDATE posts
  SET view_count = view_count + 1
  WHERE id = post_id
  RETURNING view_count INTO new_count;

  RETURN new_count;
END;
$$;

Call from Next.js:

const { data: viewCount } = await supabase
  .rpc('increment_post_views', { post_id: postId })

This prevents race conditions that occur with read-then-write patterns.

Complex Search with Ranking

CREATE FUNCTION search_posts(search_query TEXT)
RETURNS TABLE(
  id UUID,
  title TEXT,
  content TEXT,
  rank REAL
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
  SELECT
    posts.id,
    posts.title,
    posts.content,
    ts_rank(
      to_tsvector('english', posts.title || ' ' || posts.content),
      plainto_tsquery('english', search_query)
    ) AS rank
  FROM posts
  WHERE
    to_tsvector('english', posts.title || ' ' || posts.content) @@
    plainto_tsquery('english', search_query)
  ORDER BY rank DESC
  LIMIT 20;
END;
$$;

Understanding Triggers

Triggers automatically execute functions when database events occur.

Trigger Timing

BEFORE: Execute before the operation (can modify data or cancel operation)
AFTER: Execute after the operation (cannot modify data)
INSTEAD OF: Replace the operation (for views)

Trigger Events

INSERT
UPDATE
DELETE
TRUNCATE

Basic Trigger Example

Automatically set updated_at timestamp:

-- Create trigger function
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

-- Attach trigger to table
CREATE TRIGGER set_updated_at
BEFORE UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_updated_at_column();

Now every UPDATE automatically sets updated_at:

UPDATE posts SET title = 'New Title' WHERE id = '...';
-- updated_at is automatically set to NOW()

Practical Trigger Examples

Maintain Denormalized Counts

Keep comment counts in sync:

-- Function to update comment count
CREATE OR REPLACE FUNCTION update_post_comment_count()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    UPDATE posts
    SET comment_count = comment_count + 1
    WHERE id = NEW.post_id;
    RETURN NEW;
  ELSIF TG_OP = 'DELETE' THEN
    UPDATE posts
    SET comment_count = comment_count - 1
    WHERE id = OLD.post_id;
    RETURN OLD;
  END IF;
END;
$$;

-- Trigger on INSERT
CREATE TRIGGER increment_comment_count
AFTER INSERT ON comments
FOR EACH ROW
EXECUTE FUNCTION update_post_comment_count();

-- Trigger on DELETE
CREATE TRIGGER decrement_comment_count
AFTER DELETE ON comments
FOR EACH ROW
EXECUTE FUNCTION update_post_comment_count();

Validate Data Before Insert

Prevent invalid data:

CREATE OR REPLACE FUNCTION validate_post()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  -- Title must be at least 3 characters
  IF LENGTH(NEW.title) < 3 THEN
    RAISE EXCEPTION 'Title must be at least 3 characters';
  END IF;

  -- Content must be at least 10 characters
  IF LENGTH(NEW.content) < 10 THEN
    RAISE EXCEPTION 'Content must be at least 10 characters';
  END IF;

  -- Slug must be unique
  IF EXISTS (
    SELECT 1 FROM posts
    WHERE slug = NEW.slug AND id != COALESCE(NEW.id, '00000000-0000-0000-0000-000000000000'::UUID)
  ) THEN
    RAISE EXCEPTION 'Slug already exists';
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER validate_post_before_insert
BEFORE INSERT OR UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION validate_post();

Cascade Soft Deletes

When a post is soft deleted, soft delete all comments:

CREATE OR REPLACE FUNCTION cascade_soft_delete_comments()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL THEN
    UPDATE comments
    SET deleted_at = NEW.deleted_at
    WHERE post_id = NEW.id AND deleted_at IS NULL;
  END IF;

  RETURN NEW;
END;
$$;

CREATE TRIGGER cascade_post_soft_delete
AFTER UPDATE ON posts
FOR EACH ROW
WHEN (NEW.deleted_at IS NOT NULL AND OLD.deleted_at IS NULL)
EXECUTE FUNCTION cascade_soft_delete_comments();

Audit Trail for All Changes

Log every change to sensitive tables:

-- Create audit log table
CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  table_name TEXT NOT NULL,
  record_id UUID NOT NULL,
  action TEXT NOT NULL,
  old_data JSONB,
  new_data JSONB,
  user_id UUID,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Generic audit function
CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  INSERT INTO audit_logs (
    table_name,
    record_id,
    action,
    old_data,
    new_data,
    user_id
  ) VALUES (
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,
    CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN row_to_json(NEW) ELSE NULL END,
    auth.uid()
  );

  RETURN COALESCE(NEW, OLD);
END;
$$;

-- Attach to sensitive tables
CREATE TRIGGER audit_users
AFTER INSERT OR UPDATE OR DELETE ON profiles
FOR EACH ROW
EXECUTE FUNCTION audit_trigger();

CREATE TRIGGER audit_posts
AFTER INSERT OR UPDATE OR DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION audit_trigger();

Prevent Deletion of Referenced Records

Block deletion if references exist:

CREATE OR REPLACE FUNCTION prevent_delete_if_referenced()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF EXISTS (SELECT 1 FROM comments WHERE post_id = OLD.id) THEN
    RAISE EXCEPTION 'Cannot delete post with existing comments';
  END IF;

  RETURN OLD;
END;
$$;

CREATE TRIGGER prevent_post_delete
BEFORE DELETE ON posts
FOR EACH ROW
EXECUTE FUNCTION prevent_delete_if_referenced();

Advanced Patterns

Conditional Triggers

Only execute trigger under certain conditions:

CREATE TRIGGER update_search_index
AFTER INSERT OR UPDATE ON posts
FOR EACH ROW
WHEN (NEW.published = TRUE) -- Only for published posts
EXECUTE FUNCTION update_search_index();

Trigger with Multiple Events

CREATE TRIGGER maintain_post_stats
AFTER INSERT OR UPDATE OR DELETE ON comments
FOR EACH ROW
EXECUTE FUNCTION update_post_statistics();

Statement-Level Triggers

Execute once per statement instead of per row:

CREATE TRIGGER log_bulk_delete
AFTER DELETE ON posts
FOR EACH STATEMENT
EXECUTE FUNCTION log_bulk_operation();

Useful for bulk operations where per-row triggers would be expensive.

Error Handling

Handle errors gracefully:

CREATE OR REPLACE FUNCTION safe_increment_counter(post_id UUID)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
  UPDATE posts
  SET view_count = view_count + 1
  WHERE id = post_id;

  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    RAISE NOTICE 'Error incrementing counter: %', SQLERRM;
    RETURN FALSE;
END;
$$;

Log errors for debugging:

CREATE TABLE function_errors (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  function_name TEXT,
  error_message TEXT,
  error_detail TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION process_payment(amount DECIMAL)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
  -- Payment processing logic

  RETURN TRUE;
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO function_errors (function_name, error_message, error_detail)
    VALUES ('process_payment', SQLERRM, SQLSTATE);

    RETURN FALSE;
END;
$$;

Testing Functions and Triggers

Test functions directly in SQL Editor:

-- Test function
SELECT get_user_stats('user-uuid-here');

-- Test with different inputs
SELECT search_posts('nextjs');
SELECT search_posts('supabase');

-- Test error handling
SELECT safe_increment_counter('invalid-uuid');

Test triggers by performing operations:

-- Test insert trigger
INSERT INTO posts (title, content, user_id)
VALUES ('Test', 'Test content', auth.uid());

-- Verify trigger executed
SELECT * FROM audit_logs ORDER BY created_at DESC LIMIT 1;

-- Test update trigger
UPDATE posts SET title = 'Updated' WHERE id = 'post-uuid';

-- Test delete trigger
DELETE FROM posts WHERE id = 'post-uuid';

Common Pitfalls

1. Infinite Trigger Loops

-- ❌ Infinite loop
CREATE TRIGGER update_post
AFTER UPDATE ON posts
FOR EACH ROW
EXECUTE FUNCTION update_post_timestamp();

-- Function updates posts table, triggering itself again!

Solution: Use BEFORE triggers or check if value actually changed:

CREATE TRIGGER update_post
BEFORE UPDATE ON posts
FOR EACH ROW
WHEN (OLD.* IS DISTINCT FROM NEW.*)
EXECUTE FUNCTION update_post_timestamp();

2. Forgetting RETURN in Trigger Functions

-- ❌ Missing RETURN
CREATE FUNCTION my_trigger()
RETURNS TRIGGER AS $$
BEGIN
  -- Do something
  -- Missing RETURN NEW or RETURN OLD
END;
$$ LANGUAGE plpgsql;

BEFORE triggers must return NEW (or NULL to cancel operation).

3. Using SECURITY DEFINER Without Validation

-- ❌ Dangerous - no validation
CREATE FUNCTION delete_any_post(post_id UUID)
RETURNS VOID
LANGUAGE plpgsql
SECURITY DEFINER
AS $$
BEGIN
  DELETE FROM posts WHERE id = post_id;
END;
$$;

Always validate permissions in SECURITY DEFINER functions.

4. Not Handling NULL Values

-- ❌ Fails on NULL
CREATE FUNCTION calculate_total(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b; -- NULL + 5 = NULL
END;
$$ LANGUAGE plpgsql;

-- ✅ Handle NULL
CREATE FUNCTION calculate_total(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN COALESCE(a, 0) + COALESCE(b, 0);
END;
$$ LANGUAGE plpgsql;

Performance Considerations

Functions and triggers add overhead. Optimize:

Keep functions simple: Complex logic slows down queries.

Avoid triggers on high-traffic tables: Each insert/update/delete executes the trigger.

Use statement-level triggers for bulk operations: More efficient than row-level.

Index columns used in function WHERE clauses: Functions still need indexes.

Cache function results when possible: Use STABLE or IMMUTABLE when appropriate.

-- IMMUTABLE: Always returns same result for same input
CREATE FUNCTION calculate_tax(amount DECIMAL)
RETURNS DECIMAL
IMMUTABLE
AS $$
BEGIN
  RETURN amount * 0.1;
END;
$$ LANGUAGE plpgsql;

-- STABLE: Same result within single query
CREATE FUNCTION get_current_user_id()
RETURNS UUID
STABLE
AS $$
BEGIN
  RETURN auth.uid();
END;
$$ LANGUAGE plpgsql;

Summary

Postgres functions and triggers in Supabase enable:

Automated workflows without application code
Data consistency across multiple applications
Complex validation at the database level
Performance optimization through reduced round trips
Audit trails and logging

Use them for data integrity and automation. Keep business logic in your application when it requires external APIs, complex testing, or frequent changes.

[INTERNAL LINK: supabase-authentication-authorization]
[INTERNAL LINK: nextjs-supabase-database-design-optimization]
[INTERNAL LINK: nextjs-supabase-security-best-practices]

Originally published at https://www.iloveblogs.blog

Supabase Free Tier Limits in 2026: What You Actually Get (And When You Will Hit Them)

Mahdi BEN RHOUMA — Wed, 17 Jun 2026 12:59:17 +0000

Supabase Free Tier Limits in 2026: What You Actually Get (And When You Will Hit Them)

The Supabase free tier is one of the best deals in developer infrastructure. But there are specific limits that will catch you off guard if you do not know about them. I have hit most of them while building side projects, and I want to save you the same surprises.

Here is every limit that matters, in order of how likely you are to hit it.

The Limits That Actually Matter

1. Project Pausing (The One That Hurts Most)

Limit: Projects with no activity for 7 days are automatically paused.

This is the limit that trips up the most people. You build an MVP, share it with some friends, then go on vacation. You come back and your app is dead — with a "Project paused" message.

Resuming is easy (click a button in the dashboard, wait ~30 seconds), but if your app is live and a user tries to use it during that window, they get errors.

When you will hit it: On any project you are not actively developing or receiving traffic to.

How to avoid it (free tier):
Set up a lightweight ping that runs every 3-4 days. Add this to your GitHub Actions:

# .github/workflows/keep-alive.yml
name: Keep Supabase Alive

on:
  schedule:
    - cron: "0 8 */3 * *"   # every 3 days at 8am UTC

jobs:
  ping:
    runs-on: ubuntu-latest
    steps:
      - name: Ping Supabase
        run: |
          curl -s "${{ secrets.NEXT_PUBLIC_SUPABASE_URL }}/rest/v1/" \
            -H "apikey: ${{ secrets.NEXT_PUBLIC_SUPABASE_ANON_KEY }}" \
            -o /dev/null

Add NEXT_PUBLIC_SUPABASE_URL and NEXT_PUBLIC_SUPABASE_ANON_KEY as repository secrets and this runs silently every 3 days.

The real fix: Upgrade to Pro. No pausing, ever.

2. Database Storage: 500MB

Limit: 500MB of PostgreSQL storage (data + indexes + system tables).

How much is 500MB really?

It depends on your data shape. Here are rough estimates:

Use case	Approximate row count
Simple user profiles	~2 million rows
Blog posts with content	~50,000 posts
Chat messages (short)	~5 million messages
Logs / analytics events	~500,000 events

Images and files do NOT count against database storage — those go to Supabase Storage (covered below). Only structured data in PostgreSQL tables counts.

Check your current usage:

SELECT
  pg_size_pretty(pg_database_size(current_database())) AS total_size;

Or in the dashboard: Settings → Usage → Database Size.

When you will hit it: If you are storing large text content (blog posts, documents), logging user events, or building a chat app. Not a concern for most early-stage apps.

How to delay it:

Do not store file contents in database columns — use Supabase Storage
Archive old data to a separate table and truncate periodically
Use VACUUM to reclaim space after bulk deletes

-- Check which tables are using the most space
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname || '.' || tablename)) AS size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname || '.' || tablename) DESC;

3. Bandwidth: 5GB per month

Limit: 5GB of data transferred out per month across the REST API, Realtime, Storage, and Edge Functions.

When you will hit it: Rarely on the free tier unless you are serving large files through Supabase Storage. API responses for typical app data are small (JSON). 5GB is roughly:

50 million requests returning 100 bytes each
500,000 requests returning 10KB each
Serving 5,000 images averaging 1MB each

If you are serving images or files, use Supabase Storage's CDN integration rather than the direct storage URL — CDN-served assets do not count against your bandwidth quota.

4. Auth: 50,000 Monthly Active Users

Limit: 50,000 MAUs (users who sign in at least once per month).

This is extremely generous for a free tier. 50,000 MAUs is a real product with real users. Most projects never hit this.

An MAU is counted only when a user logs in. Users who exist in auth.users but do not log in that month do not count. Tokens refreshed silently in the background do count as activity.

When you will hit it: When you have a successful app, which is a good problem to have.

One thing to watch: if you are building a B2B app with large enterprise clients who have many employees, you can hit MAU limits faster than you expect even with few paying customers.

5. Storage: 1GB

Limit: 1GB of file storage (images, videos, documents, etc.).

1GB sounds small but goes further than you think:

~500 compressed images at 2MB each
~200 PDF documents at 5MB each
~10,000 thumbnails at 100KB each

When you will hit it: If users upload content (avatars, attachments) or if you store generated assets.

Best practice: Set image size limits in your app before upload, and use transformation parameters to serve resized versions:

const { data } = supabase.storage
  .from('avatars')
  .getPublicUrl(`${userId}/avatar.jpg`, {
    transform: {
      width: 200,
      height: 200,
      resize: 'cover',
    },
  });

Transformed images are cached by Supabase's CDN and served from there. The original stored file is the source of truth; transformations are free.

6. Edge Functions: 500,000 Invocations

Limit: 500,000 Edge Function invocations per month.

Each call to a Supabase Edge Function counts as one invocation. Unless you are calling Edge Functions on every page load for a high-traffic app, this is not a concern on the free tier.

When you will hit it: Building webhooks, background jobs, or server-side processing that runs very frequently.

7. No Backups (The Silent Risk)

This is not advertised prominently enough.

Free tier projects have no automatic backups. If you accidentally delete your data, run a bad migration, or your project encounters corruption, there is no safety net. You cannot restore to a previous state.

Pro plan includes daily backups with 7-day retention. You can restore to any point within the last 7 days.

If you have any users depending on your app, the $25/month Pro plan is worth it for backups alone.

DIY backup on free tier:
You can dump your database manually via the Supabase CLI:

supabase db dump --db-url postgresql://postgres:[password]@db.[ref].supabase.co:5432/postgres > backup.sql

Set this up as a weekly GitHub Action that commits the dump to a private repository. Not as good as proper backups, but better than nothing.

Free vs Pro: Is It Worth $25/Month?

Feature	Free	Pro ($25/mo)
Database storage	500MB	8GB
Bandwidth	5GB	250GB
Auth MAUs	50,000	100,000
File storage	1GB	100GB
Edge Functions	500K invocations	2M invocations
Project pausing	After 7 days inactive	Never
Daily backups	None	7 days
Support	Community	Email
Concurrent connections (pgBouncer)	Shared	Dedicated

Upgrade to Pro when:

You have paying customers (never pause = no downtime = worth it)
You are storing user-uploaded content that will grow
Your database is approaching 400MB (you want headroom before it becomes urgent)
You want backups (always)

Stay on free when:

You are actively building and testing
You have no users depending on uptime
Your project is a weekend experiment

The free tier is genuinely designed to get you to a real product. Supabase is not trying to trick you into upgrading — the limits are reasonable for pre-traction projects.

One More Thing: The 2 Free Projects Limit

You can have a maximum of 2 active free projects per organization. If you want a third, you either have to delete one or upgrade an organization to Pro.

This catches indie hackers who build multiple MVPs simultaneously. Plan your projects accordingly — or create separate Supabase organizations (each gets 2 free projects) if you have genuinely separate projects that will not share infrastructure.

Originally published at https://www.iloveblogs.blog

Supabase Email Confirmation Not Sending Troubleshooting

Mahdi BEN RHOUMA — Tue, 16 Jun 2026 22:59:34 +0000

Supabase Email Confirmation Not Sending Troubleshooting

Supabase Email Confirmation Not Sending Troubleshooting Guide

Email confirmation not sending is one of the most common issues developers face when implementing Supabase authentication in Next.js applications. You set up email/password auth, users sign up, but the confirmation email never arrives. This guide covers every possible cause and provides tested solutions that work in production.

This article is part of our comprehensive Supabase Authentication & Authorization Patterns guide.

Why Supabase Email Confirmations Fail

There are 5 main reasons why confirmation emails don't send:

Development Mode Limitations - Supabase free tier has email rate limits
SMTP Not Configured - Default email service is unreliable
Email Template Issues - Broken confirmation links or template errors
Domain/DNS Problems - SPF/DKIM records not configured
User Already Confirmed - Email was already verified

Quick Diagnostic Checklist

Before diving into fixes, run through this checklist:

## Check Supabase logs
## Go to: Dashboard > Logs > Auth Logs
## Look for: "email_confirmation_sent" or error messages

## Check user status in database
SELECT email, email_confirmed_at, confirmation_token 
FROM auth.users 
WHERE email = 'user@example.com';

## Verify SMTP settings
## Go to: Dashboard > Authentication > Email Templates
## Check: SMTP settings are configured

Solution 1: Configure Custom SMTP (Recommended)

The default Supabase email service is unreliable. Use a custom SMTP provider:

Step 1: Choose an SMTP Provider

Best options for production:

SendGrid - 100 emails/day free, reliable
Resend - Modern API, great DX
AWS SES - Cheapest for high volume
Mailgun - Good deliverability

Step 2: Configure SMTP in Supabase

// 1. Go to Supabase Dashboard > Project Settings > Auth
// 2. Scroll to "SMTP Settings"
// 3. Enable "Enable Custom SMTP"

// Example SendGrid Configuration:
Host: smtp.sendgrid.net
Port: 587
Username: apikey
Password: YOUR_SENDGRID_API_KEY
Sender email: noreply@iloveblog.blog
Sender name: Your App Name

Step 3: Verify SMTP Connection

// Test email sending from Supabase Dashboard
// Go to: Authentication > Email Templates
// Click: "Send test email"
// Check: Email arrives in inbox (not spam)

Step 4: Update Environment Variables

## .env.local
NEXT_PUBLIC_SUPABASE_URL=your-project-url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key

## For custom email templates
SMTP_HOST=smtp.sendgrid.net
SMTP_PORT=587
SMTP_USER=apikey
SMTP_PASS=your-api-key

Solution 2: Fix Email Template Configuration

Check Confirmation URL

The confirmation link must point to your application:

// Supabase Dashboard > Authentication > URL Configuration
// Site URL: https://iloveblog.blog
// Redirect URLs: https://iloveblog.blog/auth/callback

// Email template should use: {{ .ConfirmationURL }}
// NOT: {{ .SiteURL }}/auth/confirm?token={{ .Token }}

Update Email Template

<!-- Go to: Dashboard > Authentication > Email Templates > Confirm signup -->
<h2>Confirm your signup</h2>
<p>Follow this link to confirm your account:</p>
<p><a href="{{ .ConfirmationURL }}">Confirm your email</a></p>
<p>Or copy and paste this URL into your browser:</p>
<p>{{ .ConfirmationURL }}</p>

Handle Confirmation in Next.js

// app/auth/callback/route.ts
import { createRouteHandlerClient } from '@supabase/auth-helpers-nextjs'
import { cookies } from 'next/headers'
import { NextResponse } from 'next/server'

export async function GET(request: Request) {
  const requestUrl = new URL(request.url)
  const token_hash = requestUrl.searchParams.get('token_hash')
  const type = requestUrl.searchParams.get('type')
  const next = requestUrl.searchParams.get('next') ?? '/'

  if (token_hash && type) {
    const supabase = createRouteHandlerClient({ cookies })

    const { error } = await supabase.auth.verifyOtp({
      type: type as any,
      token_hash,
    })

    if (!error) {
      return NextResponse.redirect(new URL(next, request.url))
    }
  }

  // Return error page
  return NextResponse.redirect(new URL('/auth/error', request.url))
}

Solution 3: Configure DNS Records (Production)

For production, configure SPF and DKIM records:

SPF Record

## Add TXT record to your domain:
Type: TXT
Name: @
Value: v=spf1 include:sendgrid.net ~all

## For SendGrid, check their documentation for exact value

DKIM Record

## SendGrid provides DKIM records in their dashboard
## Add 3 CNAME records they provide
## Example:
Type: CNAME
Name: s1._domainkey
Value: s1.domainkey.u12345.wl.sendgrid.net

Verify DNS Configuration

## Check SPF record
nslookup -type=TXT iloveblog.blog

## Check DKIM record
nslookup -type=CNAME s1._domainkey.iloveblog.blog

## Test email deliverability
## Use: mail-tester.com

Solution 4: Handle Rate Limits

Supabase has email rate limits on free tier:

// Implement retry logic for email sending
async function sendConfirmationEmail(email: string) {
  const supabase = createClient()

  try {
    const { error } = await supabase.auth.signUp({
      email,
      password: 'user-password',
      options: {
        emailRedirectTo: `${window.location.origin}/auth/callback`,
      },
    })

    if (error) {
      // Check if rate limit error
      if (error.message.includes('rate limit')) {
        // Wait and retry
        await new Promise(resolve => setTimeout(resolve, 5000))
        return sendConfirmationEmail(email)
      }
      throw error
    }

    return { success: true }
  } catch (error) {
    console.error('Email send failed:', error)
    return { success: false, error }
  }
}

Solution 5: Resend Confirmation Email

Allow users to resend confirmation:

// app/api/resend-confirmation/route.ts
import { createRouteHandlerClient } from '@supabase/auth-helpers-nextjs'
import { cookies } from 'next/headers'
import { NextResponse } from 'next/server'

export async function POST(request: Request) {
  const { email } = await request.json()
  const supabase = createRouteHandlerClient({ cookies })

  const { error } = await supabase.auth.resend({
    type: 'signup',
    email,
    options: {
      emailRedirectTo: `${process.env.NEXT_PUBLIC_SITE_URL}/auth/callback`,
    },
  })

  if (error) {
    return NextResponse.json({ error: error.message }, { status: 400 })
  }

  return NextResponse.json({ message: 'Confirmation email sent' })
}

Frontend Component

'use client'

import { useState } from 'react'

export function ResendConfirmation({ email }: { email: string }) {
  const [loading, setLoading] = useState(false)
  const [message, setMessage] = useState('')

  async function handleResend() {
    setLoading(true)

    const response = await fetch('/api/resend-confirmation', {
      method: 'POST',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify({ email }),
    })

    const data = await response.json()
    setMessage(data.message || data.error)
    setLoading(false)
  }

  return (
    <div>
      <p>Didn't receive the email?</p>
      <button onClick={handleResend} disabled={loading}>
        {loading ? 'Sending...' : 'Resend confirmation email'}
      </button>
      {message && <p>{message}</p>}
    </div>
  )
}

Common Mistakes

Mistake #1: Not checking spam folder - Always check spam/junk folders first
Mistake #2: Using default Supabase email - Configure custom SMTP for reliability
Mistake #3: Wrong redirect URL - Ensure Site URL matches your domain exactly
Mistake #4: Missing DNS records - SPF/DKIM required for production
Mistake #5: Not handling errors - Always show user-friendly error messages

FAQ

Why do emails work in development but not production?

Production requires proper DNS configuration (SPF/DKIM records) and a verified sender domain. Development mode uses Supabase's default email service which is less strict.

How long does it take for confirmation emails to arrive?

With proper SMTP configuration, emails should arrive within 1-2 minutes. If using default Supabase email, it can take 5-10 minutes or fail entirely.

Can I disable email confirmation?

Yes, but not recommended for production. Go to Dashboard > Authentication > Settings > Enable email confirmations (toggle off). This creates security risks.

What if users never receive the email?

Implement a "resend confirmation" feature and provide alternative verification methods (phone, social auth). Always check spam folders first.

How do I test email sending locally?

Use a service like Mailtrap or MailHog for local testing. Configure SMTP to point to these services during development.

Fix Supabase Auth Session Not Persisting After Refresh Next.js 14
Supabase Auth Redirect Not Working Next.js App Router Solution
Implement Supabase Magic Link Auth in Next.js 15
Handle Supabase Auth Errors in Next.js Middleware

Conclusion

Email confirmation issues in Supabase are usually caused by SMTP configuration, template problems, or DNS settings. The most reliable solution is configuring custom SMTP with a provider like SendGrid or Resend, along with proper DNS records for production.

Follow the solutions above in order: configure SMTP first, then fix templates, add DNS records, and implement resend functionality. Test thoroughly in development before deploying to production.

Monitor your email deliverability using services like mail-tester.com and always provide users with a way to resend confirmation emails if needed.

Originally published at https://www.iloveblogs.blog

Supabase Connection Pooling with PgBouncer on Vercel Serverless

Mahdi BEN RHOUMA — Tue, 16 Jun 2026 22:59:31 +0000

Supabase Connection Pooling with PgBouncer on Vercel Serverless

The most common production failure pattern for Next.js + Supabase apps on Vercel isn't a code bug — it's connection exhaustion. Your app works fine in development, handles moderate traffic in staging, then falls over under real load with too many connections errors.

This happens because serverless functions don't maintain persistent database connections. Every function invocation opens a new connection. Under load, you can have hundreds of concurrent connections, and Postgres has a hard limit. PgBouncer is the solution, and Supabase ships it built-in. This guide explains how to configure it correctly.

Estimated read time: 12 minutes

Prerequisites

Supabase project (any plan)
Next.js app deployed or deploying to Vercel
Basic understanding of environment variables and database connections
Familiarity with Supabase client setup

Why Serverless Breaks Traditional Connection Management

In a traditional Node.js server, you create a connection pool once at startup and reuse connections across requests. The pool might have 10–20 connections serving thousands of requests.

In serverless (Vercel Functions, Edge Functions), there is no persistent process. Each function invocation is potentially a new process. Connection pooling at the application level doesn't work because the pool is destroyed when the function exits.

Postgres itself has a connection limit based on your plan:

Supabase Free: 60 connections
Supabase Pro: 120 connections
Larger plans: scales with compute

60 connections sounds like a lot until you have 60 concurrent users each triggering a serverless function. At that point, the 61st request fails.

PgBouncer sits between your application and Postgres, maintaining a small pool of actual Postgres connections and multiplexing many application connections through them.

Supabase's Two Connection Endpoints

Every Supabase project has two ways to connect:

	Direct Connection	Supabase Pooler (PgBouncer)
Port	5432	6543
Use case	Migrations, long-lived servers	Serverless, short-lived connections
Prepared statements	Yes	No (transaction mode)
Connection limit	Your Postgres limit	Effectively unlimited for app

Find both in your Supabase dashboard: Project Settings → Database → Connection string.

Configuring Your Environment Variables

# .env.local

# For application queries (use this in your app)
DATABASE_URL="postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres"

# For migrations only (direct connection)
DIRECT_URL="postgresql://postgres.[project-ref]:[password]@db.[project-ref].supabase.co:5432/postgres"

# Supabase client (unchanged)
NEXT_PUBLIC_SUPABASE_URL="https://[project-ref].supabase.co"
NEXT_PUBLIC_SUPABASE_ANON_KEY="your-anon-key"

The pooler URL uses aws-0-[region].pooler.supabase.com as the host. The direct URL uses db.[project-ref].supabase.co. These are different hosts — make sure you're using the right one.

Pool Modes: Transaction vs Session

PgBouncer supports three modes. For Supabase + Vercel, you need transaction mode.

Transaction mode (port 6543, what Supabase uses by default):

A connection is borrowed from the pool for the duration of one transaction
Released back to the pool immediately after COMMIT or ROLLBACK
Supports hundreds of concurrent app connections with a small Postgres pool
Does NOT support: prepared statements, SET commands that persist across transactions, advisory locks, LISTEN/NOTIFY

Session mode (port 5432 direct, or configurable):

One Postgres connection per client session
Supports all Postgres features
Not suitable for serverless — you're back to the original problem

For serverless, transaction mode is the only viable option.

Using the Pooler with the Supabase JS Client

The Supabase JS client (@supabase/supabase-js) uses the REST API and Realtime, not a direct Postgres connection. It's not affected by PgBouncer configuration.

PgBouncer matters when you're using a direct Postgres client — typically with an ORM like Prisma or Drizzle, or with pg directly.

With Prisma

// prisma/schema.prisma
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")      // pooler URL (port 6543)
  directUrl = env("DIRECT_URL")        // direct URL (port 5432) for migrations
}

Prisma uses directUrl for prisma migrate and url for all runtime queries. This is the correct setup for Vercel deployments.

Also disable prepared statements in your Prisma client for transaction mode compatibility:

// src/lib/prisma.ts
import { PrismaClient } from '@prisma/client'

const globalForPrisma = globalThis as unknown as {
  prisma: PrismaClient | undefined
}

export const prisma =
  globalForPrisma.prisma ??
  new PrismaClient({
    datasources: {
      db: {
        url: process.env.DATABASE_URL,
      },
    },
  })

if (process.env.NODE_ENV !== 'production') globalForPrisma.prisma = prisma

The global singleton pattern prevents creating a new Prisma client on every hot-reload in development.

With Drizzle ORM

// src/lib/db.ts
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'

// For serverless: use pooler URL, disable prepare
const client = postgres(process.env.DATABASE_URL!, {
  prepare: false, // required for PgBouncer transaction mode
})

export const db = drizzle(client)

The prepare: false option is critical. Without it, Drizzle will attempt to use prepared statements, which fail in transaction mode.

With the `pg` Package Directly

// src/lib/db.ts
import { Pool } from 'pg'

// In serverless, a pool of 1 is often optimal
// The pooler handles the actual connection multiplexing
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 1, // one connection per function instance
  idleTimeoutMillis: 0,
  connectionTimeoutMillis: 5000,
})

export default pool

Setting max: 1 might seem counterintuitive, but in serverless each function instance only handles one request at a time. The PgBouncer pool handles multiplexing across instances.

Monitoring Connection Usage

Check your current connection count in Supabase:

-- Run in Supabase SQL editor
SELECT count(*) FROM pg_stat_activity;

-- See connections by state
SELECT state, count(*) 
FROM pg_stat_activity 
GROUP BY state;

-- See connections by application
SELECT application_name, count(*)
FROM pg_stat_activity
GROUP BY application_name
ORDER BY count DESC;

If you see many connections in idle state, your application isn't releasing connections properly. If you see connections in idle in transaction state, you have transactions that aren't being committed or rolled back.

Supabase also exposes connection metrics in the dashboard under Reports → Database.

Vercel-Specific Considerations

Function concurrency: Vercel can run many function instances simultaneously. Each instance may hold a connection. The pooler absorbs this, but you should still set reasonable connection limits per instance.

Edge Runtime: If you're using Next.js Edge Runtime (export const runtime = 'edge'), you cannot use Node.js database drivers. Use the Supabase JS client (which uses HTTP) or a Postgres driver that supports the Edge Runtime like @neondatabase/serverless with a compatible adapter. [NEEDS VERIFICATION: check current Supabase Edge Runtime Postgres driver support]

Warm vs cold starts: On cold starts, a new connection must be established. This adds 50–200ms to the first request. Subsequent requests on a warm function reuse the connection. This is normal behavior — don't try to pre-warm connections.

Connection string in environment variables: Never hardcode connection strings. Use Vercel's environment variable system and ensure DATABASE_URL is set for all environments (development, preview, production) with the appropriate values.

Common Pitfalls

Using the direct connection URL in production. The direct URL (port 5432) is for migrations and admin tasks. Using it for application queries in serverless will exhaust connections under load.

Not setting prepare: false with Drizzle or pg. Transaction mode doesn't support prepared statements. This causes cryptic errors that are hard to trace back to the connection mode.

Running migrations through the pooler. Some migration operations (like CREATE INDEX CONCURRENTLY) require a persistent session connection. Always run migrations using the direct URL.

Ignoring idle in transaction connections. These are connections that started a transaction but never committed. They hold a Postgres connection indefinitely. Always use try/catch/finally to ensure transactions are committed or rolled back.

Summary and Next Steps

The setup is straightforward: use the pooler URL (port 6543) for all application queries, the direct URL (port 5432) for migrations only, and disable prepared statements in your ORM. That's it.

The deeper lesson is that serverless and traditional connection management are fundamentally incompatible. PgBouncer bridges that gap, but you need to understand what it trades away (prepared statements, session-level state) to use it correctly.

Supabase Auth Redirect Not Working Next.js App Router

Mahdi BEN RHOUMA — Tue, 16 Jun 2026 14:28:37 +0000

Supabase Auth Redirect Not Working Next.js App Router

Supabase Auth Redirect Not Working Next.js App Router Solution

Authentication redirects failing in Next.js App Router is a common issue that leaves users stuck on the login page even after successful authentication. You implement Supabase auth, users sign in successfully, but the redirect to the dashboard never happens—or worse, they get redirected to the wrong page. As covered in our Supabase Authentication & Authorization Patterns, proper redirect handling is essential for smooth user experience.

In this guide, I'll show you exactly why redirects fail in Next.js 14 App Router and how to fix them for email/password, OAuth, and magic link authentication.

Why Auth Redirects Fail in Next.js App Router

The Next.js App Router introduced significant changes to how routing works, and Supabase auth redirects require special handling. Here's what's happening:

The Core Problem

Server vs Client Rendering - App Router uses Server Components by default, but auth state changes happen on the client
Router Cache - Next.js aggressively caches routes, so even after authentication, cached pages may not reflect the new auth state
Callback URL Mismatch - OAuth and magic link callbacks need explicit handling in App Router

Why Traditional Solutions Don't Work

// ❌ This doesn't work in App Router
const { data, error } = await supabase.auth.signInWithPassword({
  email,
  password,
})

if (!error) {
  window.location.href = '/dashboard' // Causes full page reload
}

The issue: Using window.location.href causes a full page reload and loses the React state. Using router.push() alone doesn't refresh Server Components.

Step-by-Step Solution

Step 1: Create Auth Callback Route

First, create a callback route handler for OAuth and magic link redirects:

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

export async function GET(request: Request) {
  const requestUrl = new URL(request.url)
  const code = requestUrl.searchParams.get('code')
  const next = requestUrl.searchParams.get('next') ?? '/dashboard'

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

    if (!error) {
      // Redirect to the intended destination
      return NextResponse.redirect(new URL(next, request.url))
    }
  }

  // If there's an error, redirect to login with error message
  return NextResponse.redirect(
    new URL('/login?error=Could not authenticate user', request.url)
  )
}

Step 2: Fix Email/Password Redirects

For email/password authentication, use both router.push() and router.refresh():

'use client'

import { createClient } from '@/lib/supabase/client'
import { useRouter } from 'next/navigation'
import { useState } from 'react'

export default function LoginPage() {
  const [email, setEmail] = useState('')
  const [password, setPassword] = useState('')
  const [loading, setLoading] = useState(false)
  const router = useRouter()
  const supabase = createClient()

  async function handleLogin(e: React.FormEvent) {
    e.preventDefault()
    setLoading(true)

    const { data, error } = await supabase.auth.signInWithPassword({
      email,
      password,
    })

    if (error) {
      alert(error.message)
      setLoading(false)
      return
    }

    // ✅ CORRECT: Use both push and refresh
    router.push('/dashboard')
    router.refresh() // This updates Server Components
  }

  return (
    <form onSubmit={handleLogin}>
      <input
        type="email"
        value={email}
        onChange={(e) => setEmail(e.target.value)}
        placeholder="Email"
        required
      />
      <input
        type="password"
        value={password}
        onChange={(e) => setPassword(e.target.value)}
        placeholder="Password"
        required
      />
      <button type="submit" disabled={loading}>
        {loading ? 'Signing in...' : 'Sign In'}
      </button>
    </form>
  )
}

Step 3: Fix OAuth Redirects

For OAuth providers (Google, GitHub, etc.), configure the redirect URL properly:

'use client'

import { createClient } from '@/lib/supabase/client'

export function GoogleSignIn() {
  const supabase = createClient()

  async function handleGoogleSignIn() {
    const { data, error } = await supabase.auth.signInWithOAuth({
      provider: 'google',
      options: {
        // ✅ CORRECT: Point to callback route
        redirectTo: `${window.location.origin}/auth/callback?next=/dashboard`,
        queryParams: {
          access_type: 'offline',
          prompt: 'consent',
        },
      },
    })

    if (error) {
      alert(error.message)
    }
  }

  return (
    <button onClick={handleGoogleSignIn}>
      Sign in with Google
    </button>
  )
}

Step 4: Fix Magic Link Redirects

For magic link authentication, configure the email redirect:

'use client'

import { createClient } from '@/lib/supabase/client'
import { useState } from 'react'

export function MagicLinkForm() {
  const [email, setEmail] = useState('')
  const [loading, setLoading] = useState(false)
  const [sent, setSent] = useState(false)
  const supabase = createClient()

  async function handleMagicLink(e: React.FormEvent) {
    e.preventDefault()
    setLoading(true)

    const { error } = await supabase.auth.signInWithOtp({
      email,
      options: {
        // ✅ CORRECT: Point to callback route with next parameter
        emailRedirectTo: `${window.location.origin}/auth/callback?next=/dashboard`,
      },
    })

    if (error) {
      alert(error.message)
    } else {
      setSent(true)
    }

    setLoading(false)
  }

  if (sent) {
    return <p>Check your email for the magic link!</p>
  }

  return (
    <form onSubmit={handleMagicLink}>
      <input
        type="email"
        placeholder="Email"
        value={email}
        onChange={(e) => setEmail(e.target.value)}
        required
      />
      <button type="submit" disabled={loading}>
        {loading ? 'Sending...' : 'Send Magic Link'}
      </button>
    </form>
  )
}

Step 5: Add Middleware Protection

Ensure your middleware properly handles redirects:

// middleware.ts
import { createServerClient, type CookieOptions } from '@supabase/ssr'
import { NextResponse, type NextRequest } from 'next/server'

export async function middleware(request: NextRequest) {
  let response = NextResponse.next({
    request: {
      headers: request.headers,
    },
  })

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return request.cookies.get(name)?.value
        },
        set(name: string, value: string, options: CookieOptions) {
          request.cookies.set({ name, value, ...options })
          response = NextResponse.next({ request: { headers: request.headers } })
          response.cookies.set({ name, value, ...options })
        },
        remove(name: string, options: CookieOptions) {
          request.cookies.set({ name, value: '', ...options })
          response = NextResponse.next({ request: { headers: request.headers } })
          response.cookies.set({ name, value: '', ...options })
        },
      },
    }
  )

  const { data: { user } } = await supabase.auth.getUser()

  // Redirect to login if not authenticated
  if (!user && request.nextUrl.pathname.startsWith('/dashboard')) {
    return NextResponse.redirect(new URL('/login', request.url))
  }

  // Redirect to dashboard if already authenticated
  if (user && (request.nextUrl.pathname === '/login' || request.nextUrl.pathname === '/signup')) {
    return NextResponse.redirect(new URL('/dashboard', request.url))
  }

  return response
}

export const config = {
  matcher: ['/dashboard/:path*', '/login', '/signup'],
}

Working Code Examples

Complete Login Flow with Redirect

// app/login/page.tsx
'use client'

import { createClient } from '@/lib/supabase/client'
import { useRouter, useSearchParams } from 'next/navigation'
import { useState, useEffect } from 'react'

export default function LoginPage() {
  const [email, setEmail] = useState('')
  const [password, setPassword] = useState('')
  const [loading, setLoading] = useState(false)
  const router = useRouter()
  const searchParams = useSearchParams()
  const supabase = createClient()

  // Get redirect destination from URL or default to dashboard
  const next = searchParams.get('next') ?? '/dashboard'

  // Show error if present in URL
  useEffect(() => {
    const error = searchParams.get('error')
    if (error) {
      alert(error)
    }
  }, [searchParams])

  async function handleLogin(e: React.FormEvent) {
    e.preventDefault()
    setLoading(true)

    const { data, error } = await supabase.auth.signInWithPassword({
      email,
      password,
    })

    if (error) {
      alert(error.message)
      setLoading(false)
      return
    }

    // Redirect to intended destination
    router.push(next)
    router.refresh()
  }

  return (
    <div>
      <h1>Sign In</h1>
      <form onSubmit={handleLogin}>
        <input
          type="email"
          value={email}
          onChange={(e) => setEmail(e.target.value)}
          placeholder="Email"
          required
        />
        <input
          type="password"
          value={password}
          onChange={(e) => setPassword(e.target.value)}
          placeholder="Password"
          required
        />
        <button type="submit" disabled={loading}>
          {loading ? 'Signing in...' : 'Sign In'}
        </button>
      </form>
    </div>
  )
}

OAuth with Multiple Providers

// components/OAuthButtons.tsx
'use client'

import { createClient } from '@/lib/supabase/client'

export function OAuthButtons() {
  const supabase = createClient()

  async function signInWithProvider(provider: 'google' | 'github') {
    const { data, error } = await supabase.auth.signInWithOAuth({
      provider,
      options: {
        redirectTo: `${window.location.origin}/auth/callback?next=/dashboard`,
      },
    })

    if (error) {
      alert(error.message)
    }
  }

  return (
    <div>
      <button onClick={() => signInWithProvider('google')}>
        Sign in with Google
      </button>
      <button onClick={() => signInWithProvider('github')}>
        Sign in with GitHub
      </button>
    </div>
  )
}

Common Mistakes

Mistake #1: Not using router.refresh() - After authentication, you must call router.refresh() to update Server Components. Without it, protected pages won't recognize the new auth state.
Mistake #2: Wrong callback URL - OAuth and magic links must redirect to /auth/callback, not directly to /dashboard. The callback route exchanges the code for a session.
Mistake #3: Missing next parameter - Always include a next parameter in your callback URL to specify where users should go after authentication.
Mistake #4: Using window.location.href - This causes a full page reload and loses React state. Use Next.js's router.push() and router.refresh() instead.
Mistake #5: Not handling errors in callback - Always check for errors in the callback route and redirect to login with an error message if authentication fails.

FAQ

Why does my OAuth redirect fail?

OAuth redirects fail when the callback URL is incorrect or not configured in Supabase. Ensure your callback URL is https://iloveblog.blog/auth/callback and add it to the "Redirect URLs" list in your Supabase project settings.

How do I redirect to a specific page after login?

Use the next query parameter in your redirect URL. For example: /auth/callback?next=/profile. The callback route will read this parameter and redirect accordingly.

Why do I need both router.push() and router.refresh()?

router.push() navigates to the new route, but router.refresh() is needed to update Server Components with the new auth state. Without refresh, Server Components will still show the old (unauthenticated) state.

Can I use redirectTo with email/password auth?

No, redirectTo only works with OAuth and magic link authentication. For email/password, use router.push() and router.refresh() after successful sign-in.

How do I test redirects locally?

Use http://localhost:3000/auth/callback as your redirect URL during development. Make sure to add this to your Supabase project's allowed redirect URLs.

Conclusion

Fixing auth redirects in Next.js App Router requires understanding the difference between Server and Client Components and properly handling the authentication callback. The key steps are:

Create a callback route handler for OAuth and magic links
Use router.push() + router.refresh() for email/password auth
Always include the next parameter to specify redirect destination
Configure middleware to handle protected routes

Test your implementation by signing in with each auth method and verifying users are redirected to the correct page. If redirects still fail, check your Supabase project settings to ensure callback URLs are properly configured.

With these fixes in place, your authentication flow will work smoothly across all auth methods in Next.js 14 App Router.

Originally published at https://www.iloveblogs.blog

The Supabase Auth Pattern That Saved My Startup From a $50K Security Audit Failure

Mahdi BEN RHOUMA — Tue, 16 Jun 2026 14:28:34 +0000

The Supabase Auth Pattern That Saved My Startup From a $50K Security Audit Failure

Six weeks ago, we were celebrating landing our biggest enterprise client. $180K ARR. Game-changing deal.

Then came the security audit.

"Your authentication system doesn't meet our compliance requirements. Deal's off."

The client needed SOC 2 compliance, multi-factor authentication, session management, and audit trails. Our basic Supabase auth setup wasn't even close.

Here's how we rebuilt our authentication system in 6 weeks and saved the deal.

The Wake-Up Call: What We Got Wrong

Our original auth was embarrassingly simple:

// ❌ Our "enterprise-ready" auth system
const { data, error } = await supabase.auth.signInWithPassword({
  email,
  password
})

if (data.user) {
  router.push('/dashboard')
}

That's it. No MFA. No session management. No audit logging. No role-based access control.

The security audit found 23 critical issues:

No multi-factor authentication
Sessions never expired
No failed login attempt tracking
Admin users had same permissions as regular users
No audit trail for sensitive actions
Password policies were browser-default
No device management
JWT tokens stored in localStorage (XSS vulnerable)

We had 6 weeks to fix everything or lose the deal.

Pattern #1: Bulletproof Session Management

First fix: proper session handling with automatic cleanup and security monitoring.

// lib/auth/session-manager.ts
export class SessionManager {
  private static instance: SessionManager
  private supabase = createClient()
  private sessionTimeout = 30 * 60 * 1000 // 30 minutes
  private warningTimeout = 25 * 60 * 1000 // 25 minutes

  static getInstance() {
    if (!SessionManager.instance) {
      SessionManager.instance = new SessionManager()
    }
    return SessionManager.instance
  }

  async initializeSession(user: User) {
    // Log session start
    await this.logSecurityEvent('session_started', {
      user_id: user.id,
      ip_address: await this.getClientIP(),
      user_agent: navigator.userAgent,
      timestamp: new Date().toISOString()
    })

    // Set session timeout warning
    setTimeout(() => {
      this.showSessionWarning()
    }, this.warningTimeout)

    // Auto-logout after timeout
    setTimeout(() => {
      this.forceLogout('session_timeout')
    }, this.sessionTimeout)

    // Track user activity to extend session
    this.trackUserActivity()
  }

  private trackUserActivity() {
    const events = ['mousedown', 'mousemove', 'keypress', 'scroll', 'touchstart']

    events.forEach(event => {
      document.addEventListener(event, this.resetSessionTimer.bind(this), true)
    })
  }

  private resetSessionTimer() {
    // Extend session on user activity
    clearTimeout(this.sessionTimeout)
    setTimeout(() => {
      this.forceLogout('session_timeout')
    }, this.sessionTimeout)
  }

  async forceLogout(reason: string) {
    await this.logSecurityEvent('session_ended', {
      reason,
      timestamp: new Date().toISOString()
    })

    await this.supabase.auth.signOut()
    window.location.href = '/login'
  }

  private async logSecurityEvent(event: string, metadata: any) {
    await this.supabase
      .from('security_audit_log')
      .insert({
        event_type: event,
        metadata,
        created_at: new Date().toISOString()
      })
  }
}

Pattern #2: Enterprise-Grade MFA Implementation

The client required MFA for all users. Here's our production-ready implementation:

// lib/auth/mfa-manager.ts
export class MFAManager {
  private supabase = createClient()

  async enableMFA(userId: string) {
    // Generate MFA enrollment
    const { data, error } = await this.supabase.auth.mfa.enroll({
      factorType: 'totp',
      friendlyName: 'Authenticator App'
    })

    if (error) throw error

    // Store MFA secret securely
    await this.supabase
      .from('user_mfa_settings')
      .upsert({
        user_id: userId,
        factor_id: data.id,
        enabled: false, // Will be enabled after verification
        backup_codes: this.generateBackupCodes(),
        created_at: new Date().toISOString()
      })

    return {
      qr_code: data.totp.qr_code,
      secret: data.totp.secret,
      factor_id: data.id
    }
  }

  async verifyMFASetup(factorId: string, code: string) {
    const { data, error } = await this.supabase.auth.mfa.verify({
      factorId,
      challengeId: factorId,
      code
    })

    if (error) throw error

    // Enable MFA after successful verification
    await this.supabase
      .from('user_mfa_settings')
      .update({ enabled: true })
      .eq('factor_id', factorId)

    return data
  }

  async challengeMFA(factorId: string) {
    const { data, error } = await this.supabase.auth.mfa.challenge({
      factorId
    })

    if (error) throw error
    return data
  }

  private generateBackupCodes(): string[] {
    return Array.from({ length: 10 }, () => 
      Math.random().toString(36).substring(2, 10).toUpperCase()
    )
  }

  async verifyBackupCode(userId: string, code: string): Promise<boolean> {
    const { data } = await this.supabase
      .from('user_mfa_settings')
      .select('backup_codes')
      .eq('user_id', userId)
      .single()

    if (!data?.backup_codes?.includes(code)) {
      return false
    }

    // Remove used backup code
    const updatedCodes = data.backup_codes.filter((c: string) => c !== code)

    await this.supabase
      .from('user_mfa_settings')
      .update({ backup_codes: updatedCodes })
      .eq('user_id', userId)

    return true
  }
}

Pattern #3: Role-Based Access Control (RBAC)

Enterprise clients need granular permissions. We implemented a flexible RBAC system:

-- Database schema for RBAC
CREATE TABLE roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT UNIQUE NOT NULL,
  description TEXT,
  permissions JSONB NOT NULL DEFAULT '[]',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE user_roles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
  granted_by UUID REFERENCES auth.users(id),
  granted_at TIMESTAMPTZ DEFAULT NOW(),
  UNIQUE(user_id, role_id)
);

-- Insert default roles
INSERT INTO roles (name, description, permissions) VALUES
('admin', 'Full system access', '["*"]'),
('manager', 'Team management', '["users.read", "users.update", "projects.create", "projects.update", "projects.delete"]'),
('member', 'Basic access', '["projects.read", "profile.update"]'),
('viewer', 'Read-only access', '["projects.read"]');

// lib/auth/rbac.ts
export class RBACManager {
  private supabase = createClient()

  async getUserPermissions(userId: string): Promise<string[]> {
    const { data } = await this.supabase
      .from('user_roles')
      .select(`
        role:roles(permissions)
      `)
      .eq('user_id', userId)

    const allPermissions = data?.flatMap(ur => ur.role.permissions) || []

    // Handle wildcard permissions
    if (allPermissions.includes('*')) {
      return ['*']
    }

    return [...new Set(allPermissions)]
  }

  async hasPermission(userId: string, permission: string): Promise<boolean> {
    const permissions = await this.getUserPermissions(userId)

    if (permissions.includes('*')) return true
    if (permissions.includes(permission)) return true

    // Check for wildcard matches (e.g., "users.*" matches "users.read")
    return permissions.some(p => 
      p.endsWith('.*') && permission.startsWith(p.slice(0, -1))
    )
  }

  async requirePermission(userId: string, permission: string) {
    const hasAccess = await this.hasPermission(userId, permission)

    if (!hasAccess) {
      // Log unauthorized access attempt
      await this.supabase
        .from('security_audit_log')
        .insert({
          event_type: 'unauthorized_access_attempt',
          metadata: {
            user_id: userId,
            required_permission: permission,
            timestamp: new Date().toISOString()
          }
        })

      throw new Error('Insufficient permissions')
    }
  }
}

Pattern #4: Comprehensive Audit Logging

SOC 2 requires detailed audit trails. We logged everything:

// lib/auth/audit-logger.ts
export class AuditLogger {
  private supabase = createClient()

  async logAuthEvent(event: AuthEvent) {
    await this.supabase
      .from('security_audit_log')
      .insert({
        event_type: event.type,
        user_id: event.userId,
        metadata: {
          ip_address: event.ipAddress,
          user_agent: event.userAgent,
          success: event.success,
          failure_reason: event.failureReason,
          mfa_used: event.mfaUsed,
          session_id: event.sessionId,
          timestamp: new Date().toISOString()
        }
      })
  }

  async logDataAccess(userId: string, resource: string, action: string) {
    await this.supabase
      .from('data_access_log')
      .insert({
        user_id: userId,
        resource_type: resource,
        action,
        timestamp: new Date().toISOString(),
        ip_address: await this.getClientIP()
      })
  }

  async logPermissionChange(
    adminUserId: string, 
    targetUserId: string, 
    oldRole: string, 
    newRole: string
  ) {
    await this.supabase
      .from('permission_changes_log')
      .insert({
        admin_user_id: adminUserId,
        target_user_id: targetUserId,
        old_role: oldRole,
        new_role: newRole,
        timestamp: new Date().toISOString()
      })
  }
}

Pattern #5: Secure Middleware Integration

The final piece was bulletproof middleware that enforced all our security policies:

// middleware.ts
import { createServerClient } from '@supabase/ssr'
import { NextResponse } from 'next/server'
import type { NextRequest } from 'next/server'
import { RBACManager } from '@/lib/auth/rbac'
import { AuditLogger } from '@/lib/auth/audit-logger'

export async function middleware(request: NextRequest) {
  let response = NextResponse.next()

  const supabase = createServerClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    {
      cookies: {
        get(name: string) {
          return request.cookies.get(name)?.value
        },
        set(name: string, value: string, options: any) {
          response.cookies.set({ name, value, ...options })
        },
        remove(name: string, options: any) {
          response.cookies.set({ name, value: '', ...options })
        },
      },
    }
  )

  const { data: { user } } = await supabase.auth.getUser()

  // Public routes
  if (request.nextUrl.pathname.startsWith('/login') || 
      request.nextUrl.pathname.startsWith('/signup')) {
    return response
  }

  // Require authentication
  if (!user) {
    return NextResponse.redirect(new URL('/login', request.url))
  }

  // Check MFA requirement for sensitive routes
  const sensitiveRoutes = ['/admin', '/settings', '/billing']
  const isSensitiveRoute = sensitiveRoutes.some(route => 
    request.nextUrl.pathname.startsWith(route)
  )

  if (isSensitiveRoute) {
    const { data: mfaStatus } = await supabase
      .from('user_mfa_settings')
      .select('enabled')
      .eq('user_id', user.id)
      .single()

    if (!mfaStatus?.enabled) {
      return NextResponse.redirect(new URL('/setup-mfa', request.url))
    }
  }

  // Check permissions for protected routes
  const rbac = new RBACManager()
  const requiredPermission = getRequiredPermission(request.nextUrl.pathname)

  if (requiredPermission) {
    try {
      await rbac.requirePermission(user.id, requiredPermission)
    } catch (error) {
      return NextResponse.redirect(new URL('/unauthorized', request.url))
    }
  }

  // Log access
  const auditLogger = new AuditLogger()
  await auditLogger.logDataAccess(
    user.id,
    request.nextUrl.pathname,
    request.method
  )

  return response
}

function getRequiredPermission(pathname: string): string | null {
  const permissionMap: Record<string, string> = {
    '/admin': 'admin.*',
    '/users': 'users.read',
    '/settings': 'settings.update',
    '/billing': 'billing.read'
  }

  for (const [route, permission] of Object.entries(permissionMap)) {
    if (pathname.startsWith(route)) {
      return permission
    }
  }

  return null
}

The Results

6 weeks later:

✅ Passed SOC 2 Type I audit
✅ All 23 security issues resolved
✅ $180K deal closed
✅ 3 more enterprise clients signed

Unexpected benefits:

67% reduction in support tickets (better UX)
Zero security incidents in 4 months
Faster enterprise sales cycles (security as a selling point)

The One Thing That Almost Broke Everything

Over-engineering the permissions system.

We initially built a complex, hierarchical permission system with inheritance and conditional rules. It was impossible to debug and caused performance issues.

The simple flat permission system worked better and was easier to audit.

Quick Win: Implement This Today

Add basic audit logging to your Supabase auth:

// Add this to your login function
supabase.auth.onAuthStateChange((event, session) => {
  if (event === 'SIGNED_IN' && session) {
    // Log successful login
    supabase
      .from('auth_events')
      .insert({
        user_id: session.user.id,
        event: 'login',
        ip_address: 'client_ip', // Get from headers
        timestamp: new Date().toISOString()
      })
  }
})

This simple addition gives you audit trails that enterprise clients expect.

What's Next?

We're now working on:

Device management and trusted devices
Advanced threat detection
Zero-trust architecture
Automated compliance reporting

Building an enterprise SaaS? The authentication patterns in this post are now part of our comprehensive authentication guide.

For more security best practices, check out our complete security guide and learn about multi-tenant architecture patterns.

What security requirements are blocking your enterprise deals? Share in the comments - I might have a solution.

Originally published at https://www.iloveblogs.blog

DEV Community: Mahdi BEN RHOUMA

Fix Dynamic Server Usage Error in Next.js App Router

The mental model: build time vs request time

Fix 1 — Opt into dynamic rendering (when the data IS request-specific)

Fix 2 — Remove the dynamic dependency (when it SHOULD be static)

Fix 3 — Isolate the dynamic part in Suspense

Route segment config reference

Related Articles

Frequently Asked Questions

What causes "Dynamic server usage" in Next.js?

How do I make a Next.js route dynamic?

Should I always add force-dynamic?

Fix ChunkLoadError: Loading Chunk Failed in Next.js

What the two variants actually mean

Root cause #1: stale chunks after a new deploy (the common one)

Root cause #2: rolling deploys with mismatched build IDs

Root cause #3: self-hosted output: 'standalone' builds

Root cause #4: a CDN or proxy serving stale HTML

Root cause #5: network failure / timeout (the (timeout:) variant)

A safety net for dynamic imports

Decision guide

Related Articles

Frequently Asked Questions

Why do I get ChunkLoadError only after deploying?

Is ChunkLoadError a bug in my code?

Does a try/catch around import() fix it?

Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)

Why Your Supabase Queries Are Slow (And Exactly How to Fix Them)

How to Find Your Slow Queries First

1. Missing Indexes on Filtered Columns

2. RLS Policies Calling auth.uid() Per Row

3. The N+1 Query Problem

4. Fetching All Columns with select("*")

5. No Connection Pooling in Serverless Environments

6. Fetching More Data Than You Display

Putting It All Together

Why Your Supabase RLS Policies Are Silently Failing (And How to Debug Them)

Why Your Supabase RLS Policies Are Silently Failing (And How to Debug Them)

Step 1: Confirm RLS Is the Problem

Step 2: Check Which Policies Are Active

Step 3: The Most Common Bugs

Step 4: Test Each Policy in Isolation

Step 5: Check the Service Role Bypass

Step 6: Use EXPLAIN to Check Performance

The Debugging Checklist

Supabase RLS Policy Design Patterns Beyond the Basics

Supabase RLS Policy Design Patterns Beyond the Basics

Prerequisites

How RLS Actually Works (The Mental Model You Need)

Pattern 1: Role-Based Access with a Roles Table

Pattern 2: Team / Organization Membership

Pattern 3: Hierarchical Permissions (Owner > Admin > Member)

Pattern 4: Resource Sharing and Invitations

Pattern 5: Public + Authenticated Mixed Access

Pattern 6: Audit Trails Without Bypassing RLS

Common Pitfalls

Testing Your Policies

Summary and Next Steps

Supabase Realtime Gotchas: 7 Issues and How to Fix Them

Supabase Realtime Gotchas: 7 Issues and How to Fix Them

1. Forgetting to Enable Realtime on Tables

2. Memory Leaks from Unsubscribed Listeners

3. Duplicate Subscriptions Causing Duplicate Events

4. RLS Policies Blocking Realtime Updates

5. Not Handling Realtime Reconnection

6. Realtime in Server Components

7. Large Payloads Slowing Down Realtime

Realtime Debugging Checklist

Related Articles

Conclusion

Supabase Postgres Functions and Triggers: Complete Developer Guide

Supabase Postgres Functions and Triggers: Complete Developer Guide

Prerequisites

When to Use Database Functions

Your First Postgres Function

Function Basics

Return Types

Security Modes

Practical Function Examples

Soft Delete with Audit Trail

Root cause #3: self-hosted `output: 'standalone'` builds

Root cause #5: network failure / timeout (the `(timeout:)` variant)