195 lines
13 KiB
Markdown
195 lines
13 KiB
Markdown
# 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:**
|
||
```bash
|
||
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:
|
||
```bash
|
||
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:**
|
||
```bash
|
||
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`)
|
||
|
||
```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:
|
||
|
||
```sql
|
||
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.
|