Next.js boilerplate with Supabase

src/db/ holds the only database credentials in my-app. Four clients live here and they are not interchangeable.

Client	Credential	Respects RLS	Use for
sql (./client)	Postgres role, DATABASE_URL	no	ORM queries, joins, aggregates, anything transactional
supabaseAsUser(token)	anon key + caller's JWT	yes	acting on behalf of a signed-in user from server code
supabaseAdmin()	service role key	no	Storage, Auth admin, Realtime broadcast
createBrowserSupabase() (./supabase-browser)	anon key, plus the session cookie when Supabase Auth is installed	yes	realtime subscriptions and browser uploads

supabaseAsUser(token) needs a token PostgREST will accept: a Supabase (GoTrue) access token, or a third-party JWT from a provider registered under Authentication -> Third-party Auth. With the Supabase Auth battery that token comes from getSession() in @/lib/auth/server. With Clerk or Better Auth there is no such token until you configure the integration, so server code that needs the caller's permissions uses sql with an explicit owner filter instead.

Rules

• src/db/client.ts is server code, but it is deliberately not marked server-only: @/db is loaded from the terminal by the repo's own commands, and server-only is a bare throw outside Next.js. What keeps the credentials out of the browser is that the modules consuming them are server-only and that the browser client is a separate file. Never import ./client, or anything that imports it, from a "use client" component or from middleware.ts. Browser code uses ./supabase-browser.
• SUPABASE_SERVICE_ROLE_KEY never appears in a file under src/app that is not a route handler or a server action, never gets a NEXT_PUBLIC_ prefix, and never gets passed to a client component as a prop. It bypasses every policy you wrote.
• Because sql also bypasses RLS, every query built from request input must filter by the authenticated user id explicitly. where user_id = ${session.user.id} is not optional just because the ORM feels safe.
• Interpolate values through the driver's tagged template (sql`select * from t where id = ${id}`). Never build SQL by string concatenation. sql.unsafe() requires a comment justifying it.
• Do not add a second postgres() pool. Import sql from ./client; it is cached on globalThis so hot reload does not exhaust the connection limit.
• prepare: false and max: 1 on the transaction pooler (port 6543) are load bearing. Do not "optimise" them away - see the pooler solution doc.

Generated types

src/db/types.generated.ts is produced by bun run db:types against the local database, and committed. It does not exist until you have run that command once, so run it after the first bun run db:reset and again in every commit that changes SQL. Never hand-edit it: if a type looks wrong, the migration is wrong. Application-level types that are not a mirror of a table belong in src/db/types.ts or next to the feature that owns them.

The database schema of this project is defined by the SQL files in supabase/migrations, applied in filename order, plus whatever your ORM's own migrations create. Two histories, two commands, one database - and nothing else is a source of truth for either.

Which one owns a table is decided by who created it: tables you declare in the ORM's schema file belong to the ORM's history, everything else (extensions, policies, functions, triggers, grants, and any table an auth battery brought) belongs to supabase/migrations. Keep it that way; a table created in both histories is a db:reset that fails on the second run.

Non-negotiable

• Never edit a migration that has already been applied to a shared or hosted database. Migrations run forward exactly once. Fix a mistake with a new migration that alters the previous state.
• Never ask the user to "just run this in the SQL editor". A change made in the dashboard exists on exactly one machine and disappears on the next bun run db:reset. If the user has already done it, capture it with bunx supabase db diff -f <name> and commit the generated file.
• Create migrations with bunx supabase migration new <snake_case_name>. The CLI assigns the timestamp prefix; do not invent one by hand.
• Every create table in the public schema ends with alter table <name> enable row level security; in the same migration, plus at least one policy. See the /add-rls-policy skill and the supabase-db-access rule.
• The ORM's generator does not do that - it has never heard of RLS - so a table declared in the ORM schema gets its enable row level security from a hand-written migration in the ORM's history (drizzle-kit generate --custom, or prisma migrate dev --create-only), not from one here. db:reset replays supabase/migrations before the ORM's, so a statement here about an ORM-owned table fails on "relation does not exist". bun run verify names every table still missing RLS after a reset.
• drop table, drop column and destructive alter ... type need an explicit sentence in the migration comment describing what happens to existing rows, and the user's confirmation before you write them.

Workflow for any schema change

bunx supabase migration new add_widgets   # creates supabase/migrations/<ts>_add_widgets.sql
# edit the file: DDL, then RLS, then policies, then indexes
bun run db:reset                           # replays supabase/migrations + seeds, then db:migrate
bun run db:types                           # regenerates src/db/types.generated.ts
bun run verify                             # names any table still missing RLS

bun run db:reset is the test: if the full replay from an empty database fails, the migration is broken no matter how well it worked as a one-off statement. It re-runs the ORM's migrations afterwards because supabase db reset drops the tables the ORM created along with everything else - the two halves are one operation, not a choice.

Getting it to the hosted project is bun run db:link once, then bunx supabase db push --dry-run and bun run db:deploy. Seeds are never pushed.

Committing

src/db/types.generated.ts is generated but committed, in the same commit as the migration that changed it. A pull request that changes SQL without changing the generated types is either incomplete or the author skipped db:types.

Local artefacts (supabase/.branches, supabase/.temp) are gitignored. Everything else under supabase/ belongs in version control, including config.toml and every file in supabase/seed/.

Functions and triggers

Postgres functions in a migration must pin their search path (set search_path = '') and fully qualify every identifier. A security definer function without a pinned search path is a privilege escalation waiting for a schema-shadowing attack.