feat(onboarding): user_profiles table with RLS and CHECK constraints (SQL inline)

[AnayDhawan]

The table behind the first-run questionnaire (#204). One row per user, plain scalar columns.

Run this yourself

Migrations in supabase/migrations/ are not applied automatically; the existing files say so in their headers. Open the Supabase dashboard SQL editor, paste, run. You need dashboard access on the project; if you do not have it, say so on this issue before starting.

Commit it as supabase/migrations/<YYYYMMDD>_create_user_profiles.sql too, matching the naming of 20260701_create_user_events.sql.

create table if not exists public.user_profiles (
  user_id          uuid primary key default auth.uid() references auth.users(id) on delete cascade,
  graduation_year  int,
  board            text,
  field            text,
  country          text,
  referral_source  text,
  referral_other   text,
  created_at       timestamptz not null default now(),
  updated_at       timestamptz not null default now(),
  constraint user_profiles_graduation_year_check
    check (graduation_year is null or graduation_year between 2024 and 2040),
  constraint user_profiles_board_check
    check (board is null or board in
      ('IB','IGCSE','CBSE','ICSE','A-Levels','AP','State board','Other')),
  constraint user_profiles_field_check
    check (field is null or field in
      ('STEM','Humanities','Commerce','Arts','Undecided')),
  constraint user_profiles_country_check
    check (country is null or country in
      ('IN','US','GB','CA','AU','SG','DE','FR','CN','JP','KR','BR','ZA','Other')),
  constraint user_profiles_referral_source_check
    check (referral_source is null or referral_source in
      ('GitHub','Google','Instagram','Friend or school','Reddit','Other'))
);

alter table public.user_profiles enable row level security;

create policy "own profile is selectable" on public.user_profiles
  for select using (auth.uid() = user_id);
create policy "own profile is insertable" on public.user_profiles
  for insert with check (auth.uid() = user_id);
create policy "own profile is updatable" on public.user_profiles
  for update using (auth.uid() = user_id) with check (auth.uid() = user_id);

Why it is shaped this way

Every column is nullable. The questionnaire is skippable and resumable, so a partially filled row has to be legal. Requiring answers at the database level would make "skip" impossible to implement.

CHECK constraints rather than Postgres enums. Matches the pattern in 20260822_harden_user_places.sql:37-48 and means adding a board later is one alter table, not a type migration. Note that migration added its checks NOT VALID and then VALIDATE specifically to avoid an ACCESS EXCLUSIVE lock stalling a deploy on a populated table. This table starts empty, so that dance is unnecessary here.

Scalar columns, not a JSON blob. This is the whole point of the feature: the answers have to be queryable. select board, count(*) from user_profiles group by board should just work, with no unnesting.

No update policy on updated_at maintenance. Set it from the client on write, or add a trigger later if it drifts. Not worth a trigger yet.

Reading the answers

Owner-only RLS means the anon key returns only the requesting user's own row, which is correct. The Supabase SQL editor runs as service role and bypasses RLS, so from the dashboard:

select * from user_profiles;
select board, count(*) from user_profiles group by board order by count(*) desc;
select referral_source, count(*) from user_profiles group by referral_source;
select graduation_year, count(*) from user_profiles group by graduation_year order by graduation_year;

That is the intended way to read this data. Do not add a public read policy to make it easier.

Acceptance criteria

• Table exists with RLS enabled and three owner-only policies
• A row with only graduation_year set inserts successfully
• An invalid board value is rejected by the constraint
• A user cannot read another user's profile row
• select * from user_profiles in the dashboard SQL editor returns all rows
• Migration file committed under supabase/migrations/
• SELF-HOSTING.md documents the table