Profiles

The profiles SQL module gives every user a profile row. A trigger on auth.users creates it on sign-up from the user's metadata, allocates a unique username and keeps the email in step with auth.users. Users update their own name and avatar through the Data API, while the email, the active organization and disabled_at stay with the server.

pnpm better-supabase sql add profiles

What sign-up fills inCopy Anchor Link

sync_profile(user_id) runs after each insert into auth.users. When the user has no profile yet, it inserts one:

avatar_path is never filled from metadata, and a metadata option that names it is refused. It holds the Storage object path of an avatar the user uploads (<user id>/avatar.webp in a public bucket), so a provider picture in avatar_url never overwrites it. Users update it like their name, and update_my_profile writes it. The Next.js example uploads to an avatars bucket, saves the object path in avatar_path and shows avatar_url when the user has no upload.

Usernames are lowercased, stripped to a-z, 0-9 and _ (plus the . or - of a usernameFrom separator), start with a letter, and get a number suffix while the name is reserved or another profile has it (ada, then ada1). allocate_username(base) returns a free one for a "pick a username" form. On a managed table, a check constraint applies the same rules to names users pick themselves: the length limits, the characters and reservedUsernames (admin, api, support, www and similar by default).

After the module creates a profile, it calls your after_profile_sync(user_id) SQL hook when it exists, to create rows that hang off the profile. It doesn't call the hook for users who already had a profile.

A failure while creating the profile, in the insert or in the hook, never blocks the sign-up. The user gets an account without a profile, Postgres logs a warning with the error, and select better_supabase.backfill_profiles() as the service role creates the missing profiles once the cause is fixed. Run it once after installing on a project that already has users.

Who can change whatCopy Anchor Link

With readPolicy: 'members', authenticated gets select on the public columns only, so select them by name instead of *. email, the active organization and team and onboarding stay private: read your own through better_supabase.my_profile(), which returns your full row as jsonb. update_my_profile(attrs) updates the updatable columns present in attrs. createProfiles from better-supabase/blocks/profiles calls both:

import { createProfiles } from "better-supabase/blocks/profiles";

const profiles = createProfiles({ transport });
const row = await profiles.mine().orThrow();
await profiles.updateMine({ full_name: "Ada Lovelace" }).orThrow();

Pass a Standard Schema as fields to type your own columns (from extraColumns, or any column of an adopted table): mine() parses the row with it and updateMine validates the columns it sets. hooks refuses, rewrites or observes mine and updateMine. In SQL, before_profile_update(attrs jsonb, user_id uuid) runs before every update and can raise to refuse it, and after_profile_update(user_id uuid) runs after a change. See Extending blocks.

const profiles = createProfiles({
  transport,
  fields: z.object({ locale: z.enum(["en", "nl"]) }),
});
const profile = await profiles.mine().orThrow();
profile?.locale; // "en" | "nl"

On a managed table the guard also sets updated_at. An adopted table keeps its own updated_at trigger: the guard neither checks nor writes the column.

Security definer functions, such as switch_organization from the organizations module and the email mirror, write the service columns. To disable users through the profile, point the access contract at its column:

sql: {
  modules: {
    access: { disabled: { user: "better_supabase.profiles.disabled_at" } },
    profiles: {},
  },
},

OptionsCopy Anchor Link

sql.modules.profiles.options:

An entry of usernameFrom can join several metadata keys when all of them are set, so a "first_last" convention is a setting:

profiles: {
  options: {
    usernameFrom: [
      { names: ["first_name", "last_name"], separator: "_" },
      "user_name",
    ],
  },
},

Ada Lovelace becomes ada_lovelace; a user without a last name falls back to user_name, then the email. allocate_username still lowercases, strips and suffixes the result, and keeps a . or - separator, which the username check then also accepts.

{ columns, separator } joins the values the profile's own columns get instead, after metadata and splitName. A sign-up that only sends full_name: "Grace Hopper" becomes grace.hopper:

profiles: {
  options: {
    usernameFrom: [{ columns: ["first_name", "last_name"], separator: "." }],
  },
},

readPolicy also takes { members, platform }. platform is a permission key that is_platform() checks, so platform staff read every profile, which admin consoles need. It writes its own policy, bs_profiles_platform_read, in managed and adopt mode, and pulls in the access module. Column grants still apply, so the private columns stay behind them.

profiles: { options: { readPolicy: { members: true, platform: "platform.user.read" } } },

Existing profilesCopy Anchor Link

With mode: 'adopt' the module writes its functions and triggers over your table and leaves its columns, policies and grants alone. Map the key and the columns you have, and set the others to null:

sql: {
  modules: {
    profiles: {
      mode: "adopt",
      tables: { profiles: "public.profiles" },
      columns: {
        profiles: {
          key: "user_id",
          fullName: null,
          avatar: null,
          avatarPath: "avatar_path",
          activeTenant: "active_organization_id",
          onboarding: null,
        },
      },
      hooks: {
        schema: "public",
        functions: { after_profile_sync: "create_contact_profile" },
      },
      options: { syncTrigger: false, usernameMaxLength: 30 },
    },
  },
},

If a trigger of yours already creates profiles (handle_new_user), installing warns about it. Keep it and set syncTrigger: false, calling better_supabase.sync_profile(new.id) from it, or drop it and move its extra work into after_profile_sync. With mode: 'custom' the module writes nothing and you provide sync_profile and backfill_profiles.

An adopted table has no avatarPath column until you map one, so a table without it keeps working.

Avatar and logo uploads use the avatarBucket and organizationLogoBucket presets.