Skip to main content

​
Supabase

How rekkal uses Supabase for the database, authentication, and storage, including the client factories, session refresh, auth flows, and migrations.

​
What Supabase provides

Supabase backs three layers with one service: the Postgres database, authentication (cookie-based sessions), and file storage. Integration uses the @supabase/ssr package so sessions work correctly with the Next.js App Router.

​
Environment variables

Supabase reads three variables (all listed in .env.example):
  • NEXT_PUBLIC_SUPABASE_URL — the project URL, exposed to the browser.
  • NEXT_PUBLIC_SUPABASE_PUBLISHABLE_KEY — the publishable (client-safe) key, exposed to the browser. This is the current key name; it replaces the legacy anon key.
  • SUPABASE_SERVICE_ROLE_KEY — the secret service-role key with full access. Server-only; never import it into a "use client" module.

​
The three clients

Each execution context gets its own client factory under lib/supabase/:
  • lib/supabase/client.ts — createClient() for Client Components. Uses createBrowserClient with the public URL and publishable key. Create a fresh client per call rather than sharing a module-level singleton.
  • lib/supabase/server.ts — createClient() for Server Components, Server Actions, and Route Handlers. Uses createServerClient wired to Next.js’s request cookie store. Its setAll is wrapped in try/catch because writing cookies from a Server Component throws — which is safe to ignore when middleware handles the refresh.
  • lib/supabase/middleware.ts — updateSession(request) refreshes the auth session on every matched request. Server Components cannot write cookies, so token rotation happens here: it reads incoming cookies, lets Supabase rotate access/refresh tokens, and writes them onto both the request and the response. It calls supabase.auth.getUser() (never getSession()) to force token validation.
There is a fourth, privileged client for trusted server code only:
  • lib/supabase/admin.ts — createAdminClient() uses the service-role key and bypasses RLS. It is used solely by the Stripe webhook to write subscriptions rows on behalf of users. Never import it into a "use client" module, and never use it where a user-scoped client would do.

​
Session cookies are host-only

Both server-side clients pass AUTH_COOKIE_OPTIONS from lib/supabase/cookie-options.ts, and the decision that file records is the absence of domain. rekkal serves two hostnames (hosts.md); the session is set on the app host and every page that needs it is on that same host, so a host-only cookie is already sufficient. Scoping it to the registrable parent would hand a live session token to the marketing site and to every other subdomain, present and future, for no benefit this split needs. The full reasoning is in the file.

​
Session refresh, route protection and host routing via proxy.ts

The root proxy.ts file does two things per request: it redirects a request that arrived on the wrong one of the two hostnames (see hosts.md), then delegates to updateSession so the Supabase session is refreshed on every navigation. Host routing runs first, because a request that is leaving does not need its session refreshed. Next.js 16 renamed the middleware.ts file convention to proxy.ts — the root file is proxy.ts, while lib/supabase/middleware.ts keeps its name as an ordinary module. proxy.ts exports a matcher config that runs on all paths except Next.js internals, static assets, and the machine endpoints — api/webhooks and api/cron, neither of which carries a session cookie. Beyond refreshing the session, updateSession also enforces access: unauthenticated requests to /dashboard or /onboarding are redirected to /login (preserving the target in a redirect query param), and authenticated requests to /login or /signup are redirected to /dashboard. Redirect responses copy over the refreshed auth cookies so the session never desyncs.

​
Auth flows

Auth is email/password plus magic link (no third-party OAuth), all through the server client:
  • Pages — /login and /signup (route group app/(auth)/) render the shared client AuthForm, which drives password sign-in, sign-up, and magic-link from a single useActionState.
  • Server actions — app/actions/auth.ts holds signInWithPassword, signUpWithPassword, signInWithMagicLink, an authenticate dispatcher, and signOut.
  • Callback — app/auth/callback/route.ts exchanges the one-time code from a magic-link or confirmation email for a session, then redirects onward. Both the emailRedirectTo the actions send and the redirects the callback issues are on the app host; that host must be listed in Supabase’s Redirect URLs allow-list, or confirmation silently fails (deployment.md).

​
Schema and migrations

Database schema is the source of truth as versioned SQL under supabase/migrations/ — prefer migrations over editing the schema in the Supabase dashboard. Apply with supabase db push (or supabase migration up locally). Foundation (profiles, subscriptions):
  • profiles — 1:1 with auth.users, created automatically by the handle_new_user trigger (a SECURITY DEFINER function with a pinned search_path) on every new sign-up.
  • subscriptions — one row per user mirroring their Stripe subscription, written only by the webhook.
Analytics (17 tables, 20260725* plus 20260726120000_onboarding.sql; 20260727120000_rate_limits.sql adds one more table outside that set). The design and the reasoning behind it are in data-model-proposal.md; the short version: One table sits outside that model, deliberately:

​
Retention: rate-limit counters are kept for 7 days after their window closes

A counter row is deleted once its window has been closed for seven days. That is a stated policy, not an implementation detail: an ip subject id is a keyed hash of a visitor’s address (lib/rate-limit/address-subject.ts), which makes the row pseudonymised personal data rather than anonymous data. Configuration owns the number, and it is the one rekkal’s customer-facing privacy documentation is expected to cite, so a reader changing it here is changing what visitors are told. The period is config.limits.rateLimits.retentionSeconds, written beside the windows it must outlive rather than in SQL or in a route, and it is bounded on both sides:
  • It must strictly outlive the longest window in force (a day today). Deleting a row whose window is still open would hand its subject a fresh allowance mid-window. Seven days is 7×; lib/rate-limit/prune.test.ts walks every configured window and fails if one ever grows past it.
  • It must not be longer than the data is useful. A closed window is inert — consume_rate_limit only ever touches the current window’s row — so the only thing the week buys is an abuse report or a bill spike investigated after the weekend it happened on.
Deletion is prune_rate_limit_counters, called daily by /api/cron/prune-rate-limits (30 3 * * * in vercel.json, authorised by CRON_SECRET exactly as the runner is — an open deletion endpoint would let anyone clear the counters holding an abuser off). Two things make it safe to run against live traffic:
  • It cannot reach an open window at any retention value. The predicate is window_end < now() - retention with retention required to be positive, so the cutoff is strictly in the past. The retention period decides how long a closed row is kept; it is not what makes an open one safe.
  • The work is bounded twice. The limit is inside the delete’s subquery (config.limits.rateLimits.prune.batchRows, oldest first, skip locked), so one statement is short whatever the backlog; and the route issues at most prune.maxBatchesPerRun of them, so one fire cannot loop until the platform kills it. What is left is still expired and the next fire takes it. delete and insert both take ROW EXCLUSIVE and do not conflict, and a consuming caller only ever touches the current window’s row, which is never in range — so a prune cannot block consume_rate_limit.
Three properties are load-bearing and easy to break by accident:
  1. runs.run_date is the scheduled slot, not the wall clock. A Monday run retried on Tuesday reuses the same row. This is what makes the pipeline idempotent and the meter trustworthy.
  2. No column holds a ratio. Every rollup stores numerator and denominator separately, so any aggregate can report the n it was computed from, and check (run_count > 0) makes “no data” row absence — an empty comparison window divides to NULL rather than rendering a delta against zero.
  3. Keeping the meter off the browser takes two things. runs, run_mentions, run_citations, usage_periods and the rollups have no authenticated write policy — not a restrictive one, none — and Postgres denies by default, so only the runner (service-role) can write them. That is necessary but not sufficient: see the grant posture below.
RLS is enabled on all 20 tables. Every workspace-scoped policy routes through one SECURITY DEFINER helper, public.is_workspace_member(uuid), so adding seats later is one function change rather than a rewrite across every policy. SECURITY DEFINER + PostgREST is a standing trap, and it re-arms on every new function. Postgres grants EXECUTE to PUBLIC on every function it creates, Supabase does not revoke it, and PostgREST exposes everything in schema public as /rest/v1/rpc/<name>. A definer function therefore runs with its owner’s privileges for anyone holding the publishable anon key and bypasses the RLS on the tables it touches — which is how the metering RPCs were reachable from a browser despite those tables having no write policy at all. 20260725120600_function_grants.sql revokes them. The deliberate exemption is public.is_workspace_member(uuid) — the membership predicate every workspace-scoped RLS policy calls, and a policy is declared to authenticated and evaluated as the calling role, so revoking it would fail every policy closed. Which functions keep which EXECUTE grants is not restated in prose anywhere below: the table at the end of this section is derived from the migration SQL and asserted against it. RLS decides which rows, never which columns. workspaces carries one column the owner must not be able to write — last_dispatch_started_at, the runner’s rotation stamp, which an owner could otherwise reset to take the head of the dispatch queue on every fire. The “Owners can update their workspace” policy cannot express that, so 20260725121100_dispatch_rotation.sql drops the table-wide UPDATE grant for authenticated and grants back the customer-editable columns by name. A column-level REVOKE against a table-wide grant is a no-op in Postgres, which is why it is done in that order. rate_limit_counters states its posture by absence, and then takes the privileges away as well. RLS is on and it carries no policy of any kind — not even select — so Postgres denies every browser read and write by default, exactly as runs and usage_periods do. On top of that the migration issues revoke all on table public.rate_limit_counters from anon, authenticated, because a posture resting on “there happens to be no policy” is one careless create policy away from being wrong, and a forged counter row buys an attacker a fresh allowance. supabase/migrations/rate-limit.test.ts fails the suite if a policy is ever added there or the revoke goes missing. That migration only covered authenticated; anon kept the table-wide UPDATE Supabase grants by default, so 20260725121200_rotation_column_grants.sql takes it away too — and grants nothing back, because anon has no reason to update a workspace at all. Stating the exposure honestly: it was latent, not exploitable, since the only UPDATE policy on workspaces is declared to authenticated and RLS denied the write regardless. It is the same class as the definer EXECUTE grants above — Supabase grants both browser roles broadly by default, so anything resting on a privilege merely being unused has to revoke it explicitly. Any new SECURITY DEFINER function must ship with its REVOKE in the same migration. supabase/migrations/function-grants.test.ts replays the migration SQL through lib/supabase/definer-grants.ts and fails the suite if one does not — no credentials needed. The live check against pg_proc.proacl lives in lib/runner/store-supabase.integration.test.ts. The same file guards the column posture above: it fails if either browser role still holds table-wide UPDATE on workspaces, or if any column-scoped revoke update names only one of anon/authenticated — the asymmetry that produced that finding. That rule has a sharp edge worth stating on its own: CREATE OR REPLACE FUNCTION preserves a function’s privileges, while DROP + CREATE resets them to the EXECUTE-to-PUBLIC default. So a migration that replaces a definer function in place inherits the revokes of the migration that created it, but one that drops and recreates it — which is one way to change an argument list — silently re-opens it to anon unless it re-issues the REVOKE in the same file. 20260725120600 had to do exactly that when it added p_stale_after; 20260725121300 replaces the same function in place and restates the grants anyway, so the posture is readable in the file that touched it. The edge cuts through CREATE OR REPLACE too: Postgres only replaces when the name and the argument type list both match, so a replace carrying a different argument list creates a new overload that starts at the EXECUTE-to-PUBLIC default just as a drop-and-recreate does, and it needs its own REVOKE just the same. How the credential-free check reads the SQL — the ordered replay, the signature keying and its normalisation, the lexing, the statement-level matching, the optional schema qualifier, and the throw on a statement that resolves to nothing — is documented once, in the TSDoc of lib/supabase/definer-grants.ts. It is not restated here: a second hand-maintained copy of that reasoning is the drift this derivation exists to remove. Both live checks share one pure predicate, lib/supabase/grant-posture.ts, and it asserts the positive posture rather than the absence of substrings. That distinction is the whole point: pg_proc.proacl is NULL for a function whose privileges were never touched, and that NULL is the EXECUTE-to-PUBLIC default — so “no anon= appears” passes on exactly the regression it is meant to catch. The bare PUBLIC entry (=X/postgres, grantee-less) is invisible to the same naive check while still granting EXECUTE to anon and authenticated through PUBLIC membership. grant-posture.test.ts pins the real pre-revoke ACL captured from the hosted project and proves the predicate rejects it, with no credentials. Verify migrations against the hosted database, not against a command’s exit code. A green db push or a green deploy does not prove dthdxgrrktaavshxjqjr has the migration. Query it:
The meter has an invariant worth re-checking after any backfill — usage_periods.runs_used must equal count(runs where metered) for the period. It should always return zero rows. A migration whose application status is not recorded is itself a defect. The TypeScript half of a change deploys through Vercel and the SQL half does not, so an unrecorded migration is a silent gap between two halves that each look complete: the code ships, the function it depends on is still the old body, and the symptom is a counter that never leaves zero rather than an error. Record the state here whenever it changes. 20260725120000 through 20260725121400, plus 20260726120000_onboarding.sql — all sixteen analytics migrations — are applied to dthdxgrrktaavshxjqjr, and each claim below was checked against it by a query of its own rather than summarised from a green command, so each can be re-run on its own:
  • claim_run_slot carries the transient-retry branch of 20260725121300.
  • The brands_enforce_ceiling trigger exists on public.brands and refuses the 5001st row with sqlstate RK001 — exercised live inside a rolled-back transaction, not inferred from the DDL.
  • public.brand_profiles exists with RLS on and four member policies, and its positioning / products columns are text[] NOT NULL DEFAULT '{}'.
  • The prompts_enforce_ceiling trigger exists on public.prompts and refuses the 1001st row with sqlstate RK002 — exercised live the same way, with a negative control: the same block with 998 seed rows lets the next insert through and fails on its own CEILING DID NOT FIRE assertion, which is what proves the positive run was the trigger firing rather than the probe passing vacuously.
  • Every SECURITY DEFINER function’s EXECUTE holders match the enumeration below.
  • Neither browser role holds table-wide UPDATE on public.workspaces, and neither can write last_dispatch_started_at. That column is the one that matters because it is the dispatch rotation stamp: an owner able to write it could take the head of the queue on every fire — the starvation the rotation exists to prevent, only deliberate.
  • authenticated holds column-level UPDATE on public.workspaces for exactly seven columns — name, brand_domain, country_code, language, timezone, run_cadence, min_sample_n — granted back by name in 20260725121100 on purpose, because they are what the customer edits. anon holds no UPDATE at all.
  • The meter invariant above returns zero rows.
20260727120000_rate_limits.sql is NOT applied to dthdxgrrktaavshxjqjr yet, and applying it is a REQUIRED step of the deploy that ships it — not a follow-up. It was authored without database credentials, so nothing here claims otherwise; stating it plainly is the point of this section. Apply it at deploy, in the same release, because until it is applied onboarding generation is refused for every user, without exception: createSupabaseRateLimiter raises RateLimitUnavailableError when the RPC is missing, and generateOnboarding turns that into “We could not check your usage. Try again in a moment.” That is the correct failure direction — refused rather than unlimited, a flow that stops working rather than a bill that grows — but it is a total outage of the sign-up flow, not a degraded one, so a deploy that lands the code without the migration takes new-customer onboarding down until it is fixed. The server logs it under [onboarding] rate limit unavailable; that line appearing after a deploy means the migration did not land. One further consequence bites silently:
  • types/supabase.ts was hand-edited for it. The rate_limit_counters table and the consume_rate_limit function entries were written by hand in the generated file, in the positions and key order supabase gen types produces, so regenerating after the migration is applied should be a no-op. Regenerate anyway and re-append the aliases at the bottom.
20260728120000_rate_limit_retention.sql is likewise NOT applied yet, and it is what enforces the seven-day retention above. Until it is applied, /api/cron/prune-rate-limits returns 500 on every fire (prune_rate_limit_counters does not exist) and counter rows accumulate without bound — nothing else breaks, because no other path calls it, but the stated retention period is simply not being met, so an unqualified customer-facing statement of it would describe something the database does not do. /privacy therefore cites the period next to a caveat saying the pass is not yet in force and that deletion on request is the remedy until it is; dropping that caveat is a step of applying this migration, not a copy edit to make before it. That failure direction is deliberate: a prune that cannot run says so in the fire’s status rather than reporting a successful no-op. Verify it with the definer-grant query below for prune_rate_limit_counters (holders must be service_role alone) and by checking that a row with window_end in the future survives a call with a retention of one second. When 20260727120000_rate_limits.sql is applied, verify it the way everything above was verified rather than trusting a green db push: select relrowsecurity from pg_class for the table, select count(*) from pg_policies where tablename = 'rate_limit_counters' (must be zero), the definer-grant query below for consume_rate_limit, and the concurrency probe in lib/rate-limit/rate-limit.integration.test.ts (RUN_SUPABASE_INTEGRATION=1), which fires twenty simultaneous calls at one counter and requires exactly the limit to come back allowed. No SECURITY DEFINER function in schema public carries PUBLIC or anon EXECUTE. That is the property that matters, since PostgREST publishes the schema as RPC. The detail is a partition of all 16 SECURITY DEFINER functions, enumerated rather than written as a rule with exceptions — a rule has a count to get wrong, and successive versions of this record put that count at one and then two when it was neither. The table below is derived, not maintained. deriveDefinerGrants in lib/supabase/definer-grants.ts replays every create / drop / revoke / grant in supabase/migrations/ and produces exactly these rows — groups, membership, and counts — and function-grants.test.ts asserts them, so a definer function added or an exemption changed fails the suite with a diff until this table matches. It is asserted rather than regenerated on write so the change is reviewed rather than absorbed. The Why column is the one hand-written part, because intent is not in the SQL. Re-derive that grouping instead of trusting this snapshot — through the query endpoint above:
A NULL proacl in that output is the EXECUTE-to-PUBLIC default rather than “no grants” — the trap lib/supabase/grant-posture.ts exists to catch, so read the owner default row as a regression, not as the postgres-only group. Types in types/supabase.ts are generated, not hand-written:
Re-append the convenience aliases at the bottom of that file afterwards — they are the only hand-maintained part. The Supabase clients are parameterized with Database for query-level type safety, including RPC argument and return types. Two live-database behaviours that are not obvious:
  • PostgREST unifies the column set across a batch insert, so a column present in one row but omitted from another is sent as an explicit NULL and the column default never applies. Build uniform rows, or a NOT NULL DEFAULT column will reject the batch.
  • PostgREST caps every response at max-rows (1000 on a default project) and reports it only in Content-Range — a truncated read looks exactly like the end of the data. Any query that can exceed it must page with .range() and prove it read everything (lib/runner/store-supabase.ts:loadAliases compares what it collected against the exact count and throws otherwise). Silent truncation of a read is worse than an error here, because a brand missing from the alias index becomes a measured zero rather than a missing row.

​
Further reading

The supabase and supabase-postgres-best-practices skills are pre-bundled (see agent-skills.md) and load automatically when you work with Supabase.