Capability-token RPCs & RLS

The anon key ships in the public JS bundle — anyone can read it. The security model assumes that and gives the anon role no direct table access at all. Every public interaction is mediated by SECURITY DEFINER RPCs keyed on a single capability token.

The core idea: a capability token

Each project has a status_token (a UUID). Holding that token is the only way the public site can read or write that project — and it can only ever see or affect the single project whose token it holds. There is no “list all projects” path for the anon role; the token is the capability.

The token is unguessable (a v4 UUID) and appears only in the client’s private status URL: sitecrate.ca/#status/{token}.

RLS posture

RLS is enabled on projects in both schemas. The anon role:

• cannot SELECT the table
• cannot INSERT into the table
• cannot UPDATE the table

All anon access goes through four RPCs, owned by a privileged role with their search_path locked. EXECUTE is granted to anon + authenticated only (revoked from public).

Two rules that must never break

1. Never grant the anon role direct table privileges.
2. Never disable RLS.

The anon key is intentionally public and ships in the bundle. The capability-RPC layer is the only thing standing between that public key and every client’s data.

The four RPCs

RPC	Used by	Does
create_intake(payload jsonb)	Intake form	Inserts a new project, returns its status_token
get_project_status(p_token uuid)	Status page	Returns client-safe columns for the one matching project — no client_email, no admin_notes
submit_project_feedback(p_token, p_feedback)	Status page	Writes feedback to the matching project
approve_project(p_token uuid)	Status page	Sets client_approved when stage = review

Why SECURITY DEFINER

Each RPC runs with the privileges of its owner (a privileged role), not the caller. That lets a tokenless-but-authenticated anon request perform exactly one narrowly-scoped operation against exactly one row — without ever granting the anon role broad table rights. The locked search_path prevents search-path injection from redirecting the function to a malicious schema.

Column safety

get_project_status deliberately omits client_email and admin_notes. The status page is client-facing, so internal notes and even the client’s own email are never returned by the read path. The admin reads those columns directly, as an authenticated user.

The admin’s access

The admin app runs as the authenticated role and has direct table access for the dashboard (full reads/writes across all projects, gated by Supabase Auth + the user_roles check). The capability-RPC layer is specifically about the public anon surface.

How the pieces line up

Actor	Role	Access path
Prospect submitting intake	anon	create_intake RPC only
Client on the status page	anon	get_project_status / submit_project_feedback / approve_project — scoped to their token
send-email function	service role	Resolves the project by token server-side
Ahmed in the admin	authenticated	Direct table access, gated by user_roles

See the data model for the columns each path touches, and studio-website for where the RPCs are called.