Supabase

This document explains the key Supabase and database concepts introduced in Phase 1, with enough context that you understand not just what to do but why each piece exists.

Persistent vs Pooler Connections§

When your application connects to a PostgreSQL database, it opens a TCP connection. That connection has some startup cost — authentication handshake, memory allocation on the server, etc.

Persistent connection (direct): Your app opens one connection and keeps it open for the duration of its lifetime. Queries go directly to Postgres on port 5432. No middleman. The connection is stable and long-lived.

Pooler connection: A middleware process (PgBouncer, in Supabase’s case) sits between your app and Postgres. Your app connects to the pooler, the pooler maintains a pool of connections to Postgres, and it routes queries from many app connections through a smaller number of database connections. Each query may be handled by a different underlying Postgres connection.

Why does this matter?

Serverless environments (like Next.js Route Handlers on Vercel) spin up and tear down hundreds of short-lived function instances. If each instance opened a direct Postgres connection, you’d quickly exceed Postgres’s maximum connection limit. The pooler handles this: many serverless instances share a small pool of underlying connections.

Stateful operations — like Drizzle’s migration runner — need a persistent, direct connection because they run sequences of SQL statements that must execute on the same connection (DDL transactions, advisory locks, etc.). The pooler’s connection-switching behavior would break these.

In Sidekick

Variable	Port	Type	Used For
DATABASE_URL	6543	Pooler	Runtime queries (all application code)
DATABASE_DIRECT_URL	5432	Direct	Drizzle migrations only

packages/core/src/db/index.ts connects using DATABASE_URL (port 6543). This is correct for production.

drizzle.config.ts uses DATABASE_DIRECT_URL (port 5432). This is only invoked when you run pnpm db:migrate — never during the application’s request lifecycle.

Secret vs Publishable Keys — and Why They Are Not the Same Thing as Connection Type§

This is a common source of confusion. There are two independent dimensions here:

1. Authorization level — what the key lets you do
2. Connection type — how you connect (pooler vs direct)

They are completely separate concerns.

The two Supabase key types

Publishable key (NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY):

• Safe to expose in browser JavaScript
• Used by browser clients, server clients, and the proxy client
• Gives the user anonymous access — can only do what Supabase’s Row Level Security policies permit for the current user
• Previously called the “anon key” in older Supabase documentation

Secret key (SUPABASE_SECRET_KEY):

• Must never be exposed to the browser — it bypasses RLS entirely
• Used only by createAdminClient() for trusted server-side operations (e.g. managing users, accessing data regardless of who owns it)
• Previously called the “service role key”

Clearing up the confusion

You might think: “migrations are admin-level operations, so they need the secret key.” That is not correct.

Migrations are not run by a Supabase client at all. Drizzle Kit connects directly to Postgres using the DATABASE_DIRECT_URL connection string. That connection string includes the database credentials (username and password) in the URL itself — it has nothing to do with Supabase’s JWT-based key system.

Similarly, the direct URL connection type does not require or imply the secret key. You can use the direct URL with the pooler credentials if you wanted — the URL and the key are independent.

	Uses Publishable Key	Uses Secret Key
Browser client	Yes	No
Server client	Yes	No
Proxy client	Yes	No
Admin client	No	Yes
Drizzle migrations	Neither — uses DB credentials in the URL	—

The Four Supabase Clients§

Supabase is not just a database — it also manages authentication and sessions. That session state lives in a cookie. Reading and writing that cookie requires different approaches depending on where your code runs.

createBrowserClient() — packages/core/src/supabase/browser.ts

What it does: Creates a Supabase client that manages the session in the browser. It automatically reads the session from localStorage (or a cookie) and handles token refresh in the background.

When to use it: In any 'use client' component that needs to interact with Supabase — calling supabase.auth.signInWithPassword(), supabase.auth.signOut(), or reading the current user on the client side.

Key point: It uses the publishable key. It never has elevated permissions.

createServerClient() — packages/core/src/supabase/server.ts

What it does: Creates a Supabase client that reads and writes session cookies via next/headers — Next.js’s API for accessing cookies in Server Components and Route Handlers.

When to use it: In Server Components, server actions, or API Route Handlers that need to read the current user’s session. For example, a protected layout that checks whether the user is logged in before rendering.

Why the try/catch in setAll: Server Components can read cookies but cannot write them — cookie mutation requires an HTTP response, and Server Components don’t directly control the response headers. If a Server Component calls setAll (e.g. because the session token was refreshed), it would throw. The try/catch silently swallows this — the proxy handles session refresh for real, so the Server Component does not need to.

Why it’s async: cookies() from next/headers in Next.js 15+ returns a Promise. The function must await it before constructing the client.

createProxyClient(request, response) — packages/core/src/supabase/proxy.ts

What it does: A specialized Supabase client for use exclusively in apps/web/src/proxy.ts.

The Edge runtime distinction: Next.js middleware (and its successor proxy.ts in Next.js 16) runs in the Edge runtime — a lightweight JavaScript environment similar to a browser’s Service Worker, not a full Node.js environment. The Edge runtime does not have access to Node.js APIs, including next/headers.

createServerClient() calls cookies() from next/headers — this would crash in the Edge runtime. createProxyClient instead accepts the incoming NextRequest and outgoing NextResponse objects directly, reads cookies from request.cookies, and writes updated cookies back to response.cookies. No next/headers needed.

Never use this client outside proxy.ts — it requires access to the raw request/response cycle, which only exists there.

createAdminClient() — packages/core/src/supabase/admin.ts

What it does: Creates a Supabase client using the secret key. This client bypasses all Row Level Security policies — it can read and modify any user’s data regardless of who is logged in.

When to use it: Trusted server-side operations where you need elevated permissions — creating or deleting users programmatically, accessing data across multiple users, administrative scripts.

Never use it in browser code. If the secret key leaks to the browser, anyone can bypass your RLS policies and access all user data. The admin client has autoRefreshToken: false and persistSession: false because it should not maintain any session — it is a one-off privileged client, not a logged-in user.

The npm Packages§

@supabase/supabase-js

The core Supabase client library. Provides createClient() — the low-level constructor that all other clients are built on. Use this directly only for createAdminClient(). For browser and server clients, use @supabase/ssr instead.

@supabase/ssr

Supabase’s SSR-aware wrapper. Provides createBrowserClient and createServerClient (note: these are the @supabase/ssr functions, which our own functions of the same name wrap). The SSR package handles the session cookie management that supabase-js alone cannot — it knows how to read cookies in different environments. Without this package, sessions would not persist across page loads in a server-rendered app.

drizzle-orm

The query builder used to construct and execute SQL queries from TypeScript. It provides:

• Schema definition with full TypeScript types
• A fluent query API (db.select().from(profiles).where(...))
• SQL template literals for raw SQL when needed

Drizzle is not an ORM in the traditional sense (it does not manage object identity or lazy-load relationships). It is better described as a type-safe SQL builder. You write SQL-like queries in TypeScript and get typed results back.

postgres

The PostgreSQL driver that Drizzle uses to actually send queries to the database over TCP. drizzle-orm/postgres-js is the Drizzle adapter for this driver. You create a postgres(url) connection and pass it to drizzle().

Think of the layers: postgres opens the connection → Drizzle builds the SQL and sends it through postgres → results come back typed.

drizzle-kit

The CLI companion to Drizzle ORM. You use it for:

• drizzle-kit generate — reads your schema files and generates .sql migration files describing the changes
• drizzle-kit migrate — applies those migration files to the database

drizzle-kit is a devDependency because it only runs on developer machines and in CI — never in the running application.

Row Level Security (RLS) — What It Is and Why It Matters§

The problem it solves

Imagine you have a notes table. Every note has a user_id column. A normal SQL query — SELECT * FROM notes — returns every note from every user. You have to remember to add WHERE user_id = $1 to every query, every time. If you forget once, you have a data leak.

Row Level Security moves that filter into the database itself. You define a policy once: “a user can only see rows where user_id matches the current user.” After that, the database enforces it automatically on every query. You cannot accidentally forget it.

How it works in Supabase

Supabase enables RLS on tables you create. You define policies in SQL. A policy looks like this:

CREATE POLICY "users can only access their own profiles"
ON public.profiles
USING (id::text = current_setting('app.current_user_id', true));

This policy says: “when querying profiles, only return rows where the id column (cast to text) matches the current setting app.current_user_id.”

current_setting('app.current_user_id', true) is a PostgreSQL session variable — a value you set at the start of a query session. Drizzle connects as app_runtime, a non-superuser role that is subject to RLS. Before every query, withRLS sets this variable to the current user’s ID inside a transaction, and the RLS policy reads it to filter rows.

What “canonical RLS policy” means

In the Supabase community, a “canonical policy” refers to the standard, well-established pattern for writing user-owns-rows policies. The canonical form is:

USING (user_id = auth.uid())

In Sidekick’s case we use a session variable instead of auth.uid() because Drizzle connects directly to Postgres and does not go through Supabase’s auth layer. Our “canonical” form is:

USING (id::text = current_setting('app.current_user_id', true))

“Canonical” just means: this is the agreed-upon, reusable pattern we apply consistently to every user-owned table. Not creative. Not one-off. The same shape every time.

The withRLS helper

await withRLS(userId, async (tx) => {
  return tx.select().from(profiles)
})

withRLS opens a database transaction, sets app.current_user_id inside it, then runs your query function — passing the transaction object (tx) rather than the global db instance. All queries inside the callback share that same connection and transaction, so they all see the user ID you just set.

The true in set_config('app.current_user_id', ${userId}, true) means “local to the current transaction” — the setting is automatically cleared when the transaction ends.

Why the transaction wrapper matters: If you call set_config(..., true) outside a transaction, the “local” guarantee has nothing to bind to. The setting effectively becomes session-scoped for that connection. In a connection pool, that connection may be reused by the next request from a completely different user — and they would inherit the wrong app.current_user_id. Wrapping in db.transaction() closes this gap entirely.

Never set app.current_user_id manually inline. Always use withRLS.

Triggers and the Limits of createAdminClient()

Supabase’s service role (used by createAdminClient()) bypasses Row Level Security — but it does not bypass PostgreSQL triggers. Triggers are role-agnostic: they fire for every role, including service role and superuser.

This means if you call:

await createAdminClient().from('notes').delete().eq('id', id)

…the BEFORE DELETE trigger on notes will fire and reject the operation, even though the service role has full data access. The trigger runs before the delete executes, regardless of who is asking.

The only way to legitimately hard-delete a row from a syncable table is through a Drizzle transaction that explicitly opts in:

await db.transaction(async (tx) => {
  await tx.execute(sql`SET LOCAL app.allow_hard_delete = 'true'`)
  await tx.delete(notes).where(eq(notes.id, id))
})

SET LOCAL sets the flag for this transaction only — it resets on commit or rollback. The trigger reads this flag and permits the delete only if it is present.

This is intentional. Hard-deletes must be deliberate, server-side, auditable operations. They must never flow through createAdminClient() or the standard API path.

db:generate vs db:migrate — which to run for custom SQL

db:generate reads your Drizzle TypeScript schema files and generates SQL migration files automatically. Run it when you change a schema definition (schema.ts) and want Drizzle to write the corresponding SQL for you.

db:migrate reads the migration journal and applies any pending SQL files to the database. It does not care how those files were created.

For hand-written SQL migrations — RLS policies, trigger functions, GRANT statements — you skip db:generate entirely. You write the SQL file yourself, register it in meta/_journal.json, then run db:migrate directly.

You changed	Run
A TypeScript schema file (schema.ts)	db:generate → db:migrate
A custom SQL file (RLS, triggers, grants)	db:migrate only

Running db:generate on a custom SQL migration is harmless but wasteful — Drizzle would detect no schema change and produce nothing.

The public. prefix in trigger functions

Trigger functions execute in the schema context of the table that fired the trigger. Our profile creation trigger fires on auth.users — so the trigger function’s schema context is auth. Inside the function, writing INSERT INTO profiles would look for auth.profiles, not public.profiles. Since auth.profiles does not exist, the insert would fail.

Always write public.profiles (fully qualified) in trigger functions that reference tables in other schemas. This is a subtle Postgres behavior that produces confusing errors if you miss it.

Email Confirmation in Development§

Supabase requires email confirmation by default. When testing locally, you do not want to confirm every email — this would slow development to a crawl.

To disable email confirmation:

1. Go to your Supabase project dashboard
2. Navigate to Authentication → Providers → Email
3. Toggle off “Confirm email”

This is a development-only change. For production, re-enable email confirmation to prevent fake accounts.

Hydration Mismatch with Mantine’s Color Scheme§

What is a hydration mismatch?

Next.js renders pages on the server first (producing HTML), then sends that HTML to the browser and “hydrates” it — attaching React’s event handlers and making it interactive. Hydration works by React rendering the component tree in the browser and comparing it to the server-rendered HTML. If they differ, React throws a hydration mismatch error.

Why Mantine’s color scheme causes it

Mantine supports light/dark/auto color schemes. In “auto” mode, Mantine reads the user’s OS preference to decide which scheme to apply. The problem: this OS preference is only available in the browser, not on the server. The server renders in one color scheme; the browser may render in another. The HTML doesn’t match → hydration mismatch.

The fix: ColorSchemeScript + suppressHydrationWarning

Mantine provides ColorSchemeScript — a small script tag that runs before React hydrates, sets the correct color scheme in the DOM, and ensures the server and browser agree.

// apps/web/src/app/layout.tsx
<html lang="en" suppressHydrationWarning>
  <head>
    <ColorSchemeScript defaultColorScheme="auto" />
  </head>
  <body>
    <Providers>{children}</Providers>
  </body>
</html>

// apps/web/src/app/providers.tsx
<MantineProvider defaultColorScheme="auto">

Three things must align:

1. ColorSchemeScript must be in <head> — it injects a script that runs before React
2. defaultColorScheme="auto" on both ColorSchemeScript and MantineProvider — they must match
3. suppressHydrationWarning on the <html> element — tells React that the class attribute on <html> will differ between server and client (because the color scheme script modifies it), and to not treat that specific difference as an error

Without suppressHydrationWarning, React would see the color scheme class added by the script and error. With it, React accepts that the <html> element’s attributes may legitimately differ.

Other causes of hydration mismatches

Before reaching for Mantine-specific fixes, check:

• Browser extensions that modify the DOM (ad blockers, password managers) — test in an incognito window without extensions
• Dates and times rendered differently on server vs client
• Components that read window or localStorage before checking if they’re in a browser context