SupabaseTest/docs/superpowers/specs/2026-08-15-supabase-todo-app-design.md

Supabase + .NET + React To-Do App — Design

Status: Approved Date: 2026-08-15

Purpose

Build a minimal per-user to-do list that exercises the full stack:

• React (Vite + TypeScript) SPA in the browser.
• ASP.NET Core Web API as the sole gateway to application data.
• Supabase for authentication (email + password) and hosted Postgres.

The .NET backend validates Supabase-issued JWTs and owns the database connection. Supabase's PostgREST and Row Level Security are not used; the backend enforces ownership in SQL.

Success criteria

A signed-in user can:

1. Sign up with email + password, then sign in.
2. See only their own todos.
3. Add a todo (text only).
4. Toggle a todo between complete and incomplete.
5. Delete a todo.
6. Sign out.

An unauthenticated caller of any /api/todos* endpoint receives HTTP 401.

Architecture

[React SPA (Vite+TS)]  ──(HTTPS, Bearer JWT)──▶  [ASP.NET Core Web API]  ──(Npgsql)──▶  [Supabase Postgres]
        │                                                 ▲
        └──(Supabase JS: sign-up / sign-in)───────────────┘  (auth handshake + JWKS fetch by API)

• The React app uses @supabase/supabase-js only for auth (sign up, sign in, get session, refresh, sign out). It does not call PostgREST and does not talk to the database directly.
• All CRUD requests go to the .NET API with Authorization: Bearer <access_token>.
• The .NET API validates the JWT against the Supabase JWKS URL (asymmetric verification), extracts the sub claim, and uses it as user_id in every query.
• The .NET API owns the Npgsql connection. Row Level Security stays off on the todos table because .NET is the only writer and enforces ownership in WHERE clauses.

Repository layout

supabase_test/
├── backend/                              # ASP.NET Core Web API (net9.0)
│   ├── Program.cs
│   ├── Data/AppDbContext.cs
│   ├── Models/Todo.cs
│   ├── Controllers/TodosController.cs
│   ├── appsettings.json
│   ├── appsettings.Development.json      # not committed; template committed
│   └── backend.csproj
├── frontend/                             # Vite + React + TypeScript
│   ├── src/
│   │   ├── main.tsx
│   │   ├── App.tsx
│   │   ├── lib/supabase.ts               # createClient(url, publishable_key)
│   │   ├── lib/api.ts                    # fetch wrapper that attaches Bearer token
│   │   ├── auth/AuthProvider.tsx         # session state via onAuthStateChange
│   │   ├── auth/SignIn.tsx               # sign in / sign up forms
│   │   └── todos/TodoList.tsx            # list + add + toggle + delete
│   ├── .env.local                        # not committed; .env.example committed
│   └── package.json
├── migrations/
│   └── 001_create_todos.sql
├── docs/superpowers/specs/               # this file
└── README.md

Two independent processes in local dev:

• dotnet run --project backend — listens on http://localhost:5000.
• npm run dev in frontend/ — Vite dev server on http://localhost:5173.

Data model

Single table in the public schema:

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);

Design notes:

• No foreign key to auth.users. Keeps the public schema decoupled from Supabase's internal schema and avoids ownership/permissions headaches at migration time.
• No updated_at — the only mutation in scope (toggling completed) is not something the UI needs to display a timestamp for.
• No soft delete. Delete is a hard delete.
• RLS is deliberately not enabled. If in a later iteration the React app is ever allowed to talk to PostgREST directly, RLS must be turned on and policies written first.

Auth flow

Sign up / sign in (browser only):

• React calls supabase.auth.signUp({ email, password }) or signInWithPassword({ email, password }).
• Supabase returns a session { access_token, refresh_token, expires_at, user }.
• The Supabase JS client persists the session in localStorage and auto-refreshes the access token before expiry. React subscribes via supabase.auth.onAuthStateChange.

Every API request (browser → backend):

• React calls supabase.auth.getSession() to get the current access token.
• Attaches Authorization: Bearer <access_token> to every fetch to /api/todos*.
• If a 401 comes back (e.g. token was revoked), React clears the session and shows the sign-in view.

JWT validation (backend):

• AddAuthentication().AddJwtBearer(...) verifies each incoming JWT against the Supabase JWKS URL (asymmetric, no shared secret). Keys are fetched at startup and cached with periodic refresh.
• Required claims:
  • iss = https://jrbqfctqhjttxobtoqts.supabase.co/auth/v1
  • aud = authenticated
  • Signature valid against the JWKS
  • Not expired
• [Authorize] on TodosController.
• User id extracted from the sub claim, parsed as Guid, used as user_id in every query.

The exact AddJwtBearer wiring (metadata address vs IssuerSigningKeyResolver, cache TTL) is an implementation detail for the plan; the design commitment is "asymmetric JWKS validation, no shared secret."

Sign out: React calls supabase.auth.signOut(), which clears the local session. No backend call needed (the JWT will simply expire).

HTTP API

All endpoints require a valid Bearer token. Ownership is implicit: every query filters by the caller's user_id.

Method	Path	Body	Response
GET	/api/todos	—	200 — array of todos, newest first
POST	/api/todos	{ "title": string }	201 + created todo
PATCH	/api/todos/{id}	{ "completed": bool }	200 + updated todo, or 404
DELETE	/api/todos/{id}	—	204, or 404

The PATCH endpoint accepts only completed in this iteration; the wire format allows adding editable fields (e.g. title) later without a new route.

Todo JSON shape:

{ "id": 123, "title": "Buy milk", "completed": false, "createdAt": "2026-08-15T10:00:00Z" }

user_id is never returned — the client already knows who it is, and exposing it invites confusion.

For any mutation, a WHERE user_id = @currentUser AND id = @id clause guards against ID guessing; mismatches return 404 (not 403 — do not leak existence of other users' rows).

CORS

• Development: allow origin http://localhost:5173, methods GET, POST, PATCH, DELETE, header Authorization, Content-Type.
• Not applicable in production because deployment is out of scope for this iteration.

Configuration

Backend appsettings.Development.json (not committed; a .example template is committed):

{
  "ConnectionStrings": {
    "Postgres": "Host=db.jrbqfctqhjttxobtoqts.supabase.co;Port=5432;Database=postgres;Username=postgres;Password=<db-password>;SSL Mode=Require;Trust Server Certificate=true"
  },
  "Supabase": {
    "JwksUrl": "https://jrbqfctqhjttxobtoqts.supabase.co/auth/v1/.well-known/jwks.json",
    "Issuer":  "https://jrbqfctqhjttxobtoqts.supabase.co/auth/v1",
    "Audience": "authenticated"
  }
}

Frontend .env.local (not committed; .env.example committed):

VITE_SUPABASE_URL=https://jrbqfctqhjttxobtoqts.supabase.co
VITE_SUPABASE_PUBLISHABLE_KEY=sb_publishable_QajbbpfedzxWEhCPBKTZqg_M-8qyCUy
VITE_API_URL=http://localhost:5000

The Supabase secret key is never used in this project; the backend uses direct SQL, not the Supabase REST API. It is captured in the repo only in .env.example as a documented "not used" marker so it does not get pasted into code by mistake.

Error handling

• Validation: empty title, title > 500 chars → 400 with { "error": "..." }.
• Not found / not owned: any mutation targeting an id the caller doesn't own → 404.
• Unauthenticated: framework returns 401 before hitting the controller.
• Database down: unhandled → 500. Not worth custom handling in an MVP.
• Frontend: any non-2xx surfaces as an inline error message on the affected control; the app does not crash.

Local dev workflow

One-time setup:

1. Run migrations/001_create_todos.sql against the Supabase database (via psql with the connection string, or the SQL editor in the Supabase dashboard).
2. Copy backend/appsettings.Development.example.json → backend/appsettings.Development.json and fill in the DB password.
3. Copy frontend/.env.example → frontend/.env.local and fill in the Supabase URL + publishable key + API URL.
4. Confirm Supabase Auth email confirmation is off for the dev project (Dashboard → Authentication → Providers → Email → toggle "Confirm email" off), so sign-up hands back a session immediately.

Every run:

• Terminal 1: dotnet run --project backend
• Terminal 2: cd frontend && npm run dev
• Open http://localhost:5173, sign up, add todos.

Out of scope (explicit)

• Editing a todo's text after creation.
• Due dates, priorities, tags, categories, sharing.
• Automated tests (unit or integration). The .NET code will be structured so a TodosController test using WebApplicationFactory is a straightforward follow-up.
• Password reset / email confirmation / OAuth providers / magic links.
• Dockerfile, docker-compose, Coolify deployment.
• Observability (logs beyond ASP.NET defaults, metrics, tracing).
• Rate limiting.
• Client-side routing (a single view toggles between "sign in" and "todo list" based on session state).