SupabaseTest/CLAUDE.md

supabase_test — Supabase + .NET + React boilerplate

Reusable template for a per-user CRUD app backed by Supabase Auth + Postgres. The stack:

• React (Vite + TypeScript) SPA in the browser — uses @supabase/supabase-js only for authentication.
• ASP.NET Core 9 Web API — validates Supabase-issued JWTs against the project's JWKS and owns all database access.
• Supabase — hosted auth + Postgres.

The .NET backend is the sole gateway to data. The browser never talks to PostgREST. Row-level security stays off; the backend enforces ownership in SQL.

Architecture at a glance

[React SPA]  ──(bearer JWT)──▶  [ASP.NET Core Web API]  ──(Npgsql / EF Core)──▶  [Supabase Postgres]
      │                                   ▲
      └──(auth handshake with supabase-js)─┘
      └────(JWKS fetch via OpenID discovery)─────┘

• React reads its session with supabase.auth.getSession() and attaches Authorization: Bearer <access_token> to every /api/todos* request.
• The .NET backend validates the JWT asymmetrically against https://<ref>.supabase.co/auth/v1/.well-known/openid-configuration — no shared secret. The sub claim (a UUID) becomes user_id on every query.
• Every mutation is scoped by WHERE user_id = @currentUser. Non-owned rows return 404 (not 403) — no existence leak.
• Backend connects to Postgres via Supabase's session pooler (aws-<n>-<region>.pooler.supabase.com:5432). The direct DB host is IPv6-only for new Supabase projects and unreachable from IPv4-only environments.

Repo layout

backend/                              ASP.NET Core 9 Web API
├── Program.cs                        DbContext, JWT auth, controllers, CORS (dev-only), SPA fallback, /health
├── Controllers/TodosController.cs    GET / POST / PATCH / DELETE, [Authorize]-gated
├── Data/AppDbContext.cs              EF Core context (single Todo entity)
├── Models/Todo.cs
├── Dtos/                             Wire DTOs (never expose user_id)
├── appsettings.json                  Shared, non-secret defaults
├── appsettings.Development.example.json   Template with placeholders
└── appsettings.Development.json      REAL values, gitignored

frontend/                             Vite + React + TypeScript SPA
├── src/lib/supabase.ts               createClient(url, publishable_key)
├── src/lib/api.ts                    Authed fetch wrapper (attaches Bearer)
├── src/auth/AuthProvider.tsx         Session state via onAuthStateChange
├── src/auth/SignIn.tsx               Email + password sign-in / sign-up
├── src/todos/TodoList.tsx            List + add + toggle + delete
├── src/App.tsx / main.tsx            Route between SignIn and TodoList
├── .env.example                      Template with placeholders
└── .env.local                        REAL values, gitignored

migrations/001_create_todos.sql       Plain SQL applied once against Supabase Postgres
docs/superpowers/specs/               Original design spec
docs/superpowers/plans/               Implementation plan
Dockerfile / docker-compose.yml       Coolify-ready single-image packaging
.dockerignore                         Keeps host bin/, obj/, node_modules/ out of the build context
deploy.json                           Coolify per-app config (gitignored — holds runtime secrets)

Configuring for a new Supabase project

Six values change per Supabase project. Everything else is project-agnostic — same schema, same auth flow, same code.

Value	Where to get it (Supabase dashboard)	Where it goes
Project URL (e.g. https://<ref>.supabase.co)	Project Settings → API → Project URL	backend/appsettings.Development.json (Supabase:MetadataAddress, Supabase:Issuer) and frontend/.env.local (VITE_SUPABASE_URL)
Publishable (anon) key	Project Settings → API → Project API Keys → publishable	frontend/.env.local (VITE_SUPABASE_PUBLISHABLE_KEY)
Secret (service-role) key	Project Settings → API → Project API Keys → secret	NOT used by the app; only useful for admin-API E2E tests
Database password	Set once during project creation; can be reset	backend/appsettings.Development.json (part of ConnectionStrings:Postgres)
Session pooler hostname	Project Settings → Database → Connection string → Session pooler tab (port 5432)	backend/appsettings.Development.json (host + username in ConnectionStrings:Postgres)
Pooler username	Same tab; format is postgres.<project-ref> — always postgres + dot + project ref	Same connection string

Concrete steps for a new project

1. Copy the templates and fill them in:

   cp backend/appsettings.Development.example.json backend/appsettings.Development.json
   cp frontend/.env.example frontend/.env.local
   

   Substitute the six values above.
2. Apply the migration against the new Supabase project:

   PGPASSWORD='<db-password>' psql \
     "host=aws-<n>-<region>.pooler.supabase.com port=5432 dbname=postgres user=postgres.<project-ref> sslmode=require" \
     -f migrations/001_create_todos.sql
   

   (Or paste the SQL into the Supabase SQL Editor.)
3. Disable email confirmation for dev: Supabase dashboard → Authentication → Providers → Email → toggle Confirm email OFF. Otherwise supabase.auth.signUp won't return a session and the SignIn UI hangs at "Working…" while a confirmation email is queued (and often silently rate-limited to 2–4/hour on the default SMTP).
4. Install and run:

   cd frontend && npm install
   # Terminal 1
   cd backend && dotnet run             # http://localhost:5057
   # Terminal 2
   cd frontend && npm run dev           # http://localhost:5173
   

Config surface — every knob

Backend (backend/appsettings.Development.json)

{
  "ConnectionStrings": {
    "Postgres": "Host=aws-<n>-<region>.pooler.supabase.com;Port=5432;Database=postgres;Username=postgres.<ref>;Password=<db-password>;SSL Mode=Require;Trust Server Certificate=true"
  },
  "Supabase": {
    "MetadataAddress": "https://<ref>.supabase.co/auth/v1/.well-known/openid-configuration",
    "Issuer":          "https://<ref>.supabase.co/auth/v1",
    "Audience":        "authenticated"
  }
}

Audience is always authenticated for user-issued JWTs — do not change unless you're validating service-role tokens (you shouldn't be, in this app).

The backend's port comes from backend/Properties/launchSettings.json (applicationUrl — default http://localhost:5057). If you move the backend to a different port, also update VITE_API_URL and the CORS WithOrigins(...) clause in Program.cs.

Frontend (frontend/.env.local)

VITE_SUPABASE_URL=https://<ref>.supabase.co
VITE_SUPABASE_PUBLISHABLE_KEY=sb_publishable_...
VITE_API_URL=http://localhost:5057

VITE_API_URL empty (or unset) means same-origin — used when the backend serves the built SPA from wwwroot/ in production (Dockerfile path). Set it explicitly during local dev when frontend and backend run on different ports.

Container / Coolify (docker-compose.yml)

Six env vars flow in from Coolify's env-var UI:

Coolify env	Purpose
VITE_SUPABASE_URL	Baked into the client bundle at image-build time (compose build.args)
VITE_SUPABASE_PUBLISHABLE_KEY	Same
DB_CONNECTION_STRING	Runtime, mapped to ConnectionStrings__Postgres
SUPABASE_METADATA_ADDRESS	Runtime, mapped to Supabase__MetadataAddress
SUPABASE_ISSUER	Runtime, mapped to Supabase__Issuer
—	Supabase__Audience is hard-coded to authenticated in the compose

The compose file also declares SERVICE_FQDN_APP_8080: /. Coolify substitutes this with the app's real domain and injects the matching Traefik routing labels — only if docker_compose_domains is set on the Coolify application. Skipping that field turns every request into a 404 (see the deploying-to-coolify-via-api skill for the fix).

Data model

Single table:

create table public.todos (
  id           bigserial primary key,
  user_id      uuid        not null,           -- Supabase auth.users.id (JWT sub claim)
  title        text        not null check (length(title) between 1 and 500),
  completed    boolean     not null default false,
  created_at   timestamptz not null default now()
);
create index todos_user_id_created_at_idx on public.todos (user_id, created_at desc);

No FK to auth.users — decouples from Supabase's internal schema and avoids permission headaches at migration time.

Extending the pattern

To add a new entity (e.g. notes), the same shape repeats:

1. Add a table with user_id uuid not null + a (user_id, created_at desc) index. Ship as migrations/00N_create_notes.sql.
2. Add backend/Models/Note.cs, backend/Dtos/{NoteDto,CreateNoteRequest,UpdateNoteRequest}.cs.
3. Register the DbSet in AppDbContext with HasIndex(...) matching the SQL index name.
4. Add NotesController — copy TodosController verbatim, swap entity/DTO names. Keep the CurrentUserId() + WHERE user_id == userId pattern for every read/write; that's what enforces per-user isolation without RLS.
5. Frontend: an api.notes object with list/create/setX/remove alongside the existing api todos block, plus a NotesList component modelled on TodoList.

The critical invariant, everywhere: every query filters by userId = CurrentUserId(). Missing that on a single endpoint leaks other users' data.

Known gotchas (all bit us during initial build — documented so they don't bite you)

Symptom	Cause	Fix
connection to server at "db.<ref>.supabase.co" failed: Network is unreachable	Direct DB host is IPv6-only on new Supabase projects; sandbox has no IPv6 egress	Use the session pooler (aws-<n>-<region>.pooler.supabase.com, port 5432, user postgres.<ref>)
dotnet add package X restore fails with NU1202	CLI grabbed a newer major version incompatible with net9.0	Pin: dotnet add package X --version 9.*
Sign-up hangs / "email rate limit exceeded"	Email confirmation ON; Supabase default SMTP limits to 2–4/hour	Toggle Auth → Providers → Email → Confirm email OFF for dev; or wire real SMTP
Sign-up rejects @example.com with email_address_invalid	Supabase blocklists disposable domains on the ordinary signup endpoint	For E2E scripts, use the admin API (POST /auth/v1/admin/users with email_confirm:true) — see docs/superpowers/plans/2026-08-15-supabase-todo-app.md Task 8
Deploy on Coolify finishes green, live URL returns 404 page not found	Missing / mis-shaped docker_compose_domains on the Coolify app	See the deploying-to-coolify-via-api skill: PATCH the array-form after the first deploy loads the compose file
Vite template's index.css styles fight your inline styles	Scaffolded index.css ships opinionated marketing styles (large h1, fixed #root width)	Replace with the minimal reset in frontend/src/index.css
Backend port 5000 collisions across dev sessions	Common default for other Linux services / stale processes	This project uses 5057 — check the same for any port you pick

What NOT to change without thinking

• RLS off is a deliberate choice. If you enable RLS on public.todos, you must also either (a) write USING (auth.uid() = user_id) policies, or (b) have the backend run as a role with BYPASSRLS. Otherwise the backend's own queries stop returning rows.
• The DTO response shape deliberately omits user_id. Adding it back exposes the caller's own id (harmless) but invites confusion.
• RequireHttpsMetadata = true on the JwtBearer options. If you're testing against a non-HTTPS Supabase project (there is no such thing in practice), set to false — otherwise leave it. This prevents downgrade attacks on the JWKS fetch.
• MapInboundClaims = false — keeps the raw sub claim instead of remapping to ClaimTypes.NameIdentifier. If you flip this, update the CurrentUserId() helper.

Related skills / docs

• docs/superpowers/specs/2026-08-15-supabase-todo-app-design.md — the original design decisions and out-of-scope list.
• docs/superpowers/plans/2026-08-15-supabase-todo-app.md — the 17-task implementation plan, including manual E2E verification recipes for backend (curl) and frontend (Playwright).
• Global skill: creating-supabase-app-from-boilerplate — clones this repo and reconfigures it against a new Supabase project.
• Global skill: preparing-dotnet-react-app-for-coolify — the Dockerfile + compose pattern this repo already implements.
• Global skill: deploying-to-coolify-via-api — how the Coolify project + application were created; run again against a new project or copy deploy.json and change the values.