Profiles and users

Lesson 12 · about 15 minutes · what happens between a row landing in auth.users and a public profile card, and which writes go through the account function versus straight PostgREST.

The user layer is mostly SQL. One trigger creates the profile, RLS owns most reads and writes, and the account edge function exists for exactly the writes that need a third party (OpenAI moderation, Apple, GoTrue admin) or a service-role privilege. Knowing which side a column lives on tells you where a ticket's change goes.

Birth of a profile

1GoTrue inserts into auth.users. Signup, magic link, Apple or Google, it does not matter.

2on_auth_user_created fires handle_new_user. The function existed in the initial schema dump but the trigger was missing, so for a while signups made no profile. Migration 20260127130230 is the fix. migrations/20260127130230_add_handle_new_user_trigger.sql:14

3A random identity is seeded. generate_random_name() produces Adjective_Noun_NNN and writes it to both user_name and display_name. migrations/20260420164920_name_and_username_change.sql:3

4Everything else is opt-in. Avatar, bio, mailing address, brokerage, notifications consent, missions progress. Each has its own table or column and its own write path below.

Where each write goes

Thing	Path	Why that path
`user_name`	`POST /account/change-username` → OpenAI moderation → `change_user_name(uuid, text)`	Direct UPDATE is revoked from `authenticated`. The RPC owns format, reserved names, case-insensitive uniqueness and the 30-day rate limit. Moderation must precede the write and fail closed. migrations/20260702231724:54 · account/handlers/changeNameCore.ts
`display_name`	same, via `change_display_name`	Same rules minus the rate limit. Regex `^[A-Za-z0-9 ._'-]{3,40}$` is ASCII-only to close the homoglyph impersonation vector. src/supabase/docs/name-moderation.md
`bio`	`POST /account/change-bio`	Moderated text, same shape as names. `changeBioCore.ts` is the dependency-injected core that `feed/postTradeCore.ts` later copied.
avatar	`POST /account/avatar` plus Storage bucket policies	Storage RLS lets a user upload only under their own path; default avatars were seeded by migration 20260504120000. migrations/20260408232346:35-59
email	`POST /account/change-email`	GoTrue admin call; `double_confirm_changes = true` in `config.toml` means both addresses confirm. docs/EMAIL_CHANGE.md
watchlists, watchlist_items	PostgREST directly	Owner-only RLS on both tables, column-level UPDATE grant limited to `name` and `icon`. Ticker FK to `Stock_Current_Quotes` means only tracked tickers can be added. migrations/20260706113440:97-145
user_copies (follow graph)	PostgREST directly	RLS `auth.uid() = copier_id` on insert and delete; CHECK blocks self-copy. migrations/20260504172508:29-51
missions, points, inventory	DB triggers and RPCs only	Completing a mission inserts `mission_progress`; a trigger adds `point_value` to `Profiles.point_balance`. `redeem_storefront_item` deducts, and a CHECK keeps the balance non-negative. migrations/20260323212019:79 · 20260325160434:60 · 20260326161125
deletion	`POST /account/delete`	Calls `soft_delete_user`, which obfuscates PII in `auth.users`, deletes the Profiles row and sessions, and keeps the auth row for audit. Brokerage data goes through `delete_snaptrade_data`. Both RPCs are service-role only. migrations/20260303203624:15-68 · account/handlers/delete.ts:85

Why is public_profiles a view with security_invoker = false? Profiles carries private columns (mailing address, point balance, consent). Rather than write a policy that exposes some columns to everyone, the view projects the five public fields and runs as its owner so base-table RLS does not apply. Only authenticated can select it. Everything social, feed actors, search, follower counts, joins this view and never the base table. migrations/20260427103109 · 20260504172508:72

Reading users

Search is search_profiles(term, page, size), a security invoker function returning setof public_profiles, backed by a pg_trgm index. It clamps page size to 100 and escapes the term itself. migrations/20260630221831:19-40
Soft-deleted users vanish from social surfaces through is_active_user(uuid), which the feed policy and others call, because authenticated has no SELECT on auth.users and cannot check deleted_at directly. migrations/20260812163100:50-56
Stats (get_profile_stats, get_profile_trades) read the snaptrade tables through owner-rights functions with an explicit visibility check; see the pgTAP files named get-profile-*.

The pattern repeated across this lesson: the database decides what it can decide locally and atomically (format, uniqueness, rate limit, ownership); the edge function only adds what needs a network call or a privilege. When a ticket adds a new user-settable field, ask which side each rule belongs on before writing either.

Check yourself

Do this in the repo

Run the user-layer pgTAP tests and read one assertion from each:

cd src && npx supabase test db 2>&1 | grep -E 'name-moderation|watchlists|user-follows|public-profiles|search-profiles'

With the stack up, sign in as a seeded user and try the revoked write yourself. Predict the error before you run it:

curl -i -X PATCH "http://localhost:54321/rest/v1/Profiles?id=eq.$USER_ID" -H "apikey: $ANON_KEY" -H "Authorization: Bearer $USER_JWT" -H 'Content-Type: application/json' -d '{"user_name":"hacker"}'

Read account/handlers/changeNameCore.ts next to feed/handlers/postTradeCore.ts and note what the injected-deps pattern buys the tests.

Primary source

Supabase: Row Level Security, especially the section on security_invoker views and using functions inside policies.

Next lesson: Feed, where trade cards, market events and these profiles meet. Back: brokerage and SnapTrade.

Ask your teacher in the terminal: "show me exactly which columns of Profiles an authenticated user can still update" is answerable from the migrations.