Users & Access

Tables that attach roles and account or parent-brand access to Supabase auth users, driving Row-Level Security.

These tables control who can do what in National Commerce. They define the set of roles, grant those roles to users at the account level, grant broader access at the parent-brand level, and store per-user preferences.

User identity itself lives in Supabase's auth.users table. The tables on this page do not store users; they attach roles and account or parent-brand access to those users. Row-Level Security (RLS) keys off these grants to decide which rows each user can see. For the meaning of brand, account, and parent-brand, see the glossary.

roles

The set of role names a user can hold. Each row is one named role that other tables reference.

Column	Type	Notes
id	uuid	Primary key. Defaults to `gen_random_uuid()`.
name	text	The role's display name.
created_at	timestamptz	Audit timestamp.

userRoles references this table through its roleId column, so each grant points at exactly one role defined here.

userRoles

Grants one user one role on one account. This is the main join between users, roles, and accounts, and it is what most RLS policies check.

Column	Type	Notes
id	uuid	Primary key. Defaults to `gen_random_uuid()`.
userId	uuid	The user who holds the grant → Supabase auth user.
roleId	uuid	The role granted → references roles.
brand	integer	The account the grant applies to → references Brands. `CHECK (brand > 0)`.
brand_revoked_at	timestamptz	When the grant was revoked. Null while active.
revocation_reason	text	Free-text reason recorded when the grant is revoked.
created_at	timestamptz	Audit timestamp.

Each row ties a user (userId → auth user) to a role (roleId → roles) on a single account (brand → Brands). Despite the column name, brand holds an account id; the CHECK (brand > 0) constraint keeps it a valid positive reference.

Grants are revoked, not deleted. Setting brand_revoked_at (and, ideally, revocation_reason) turns off a grant while keeping the row for history. Any query or policy that treats a user as still having access must ignore rows where brand_revoked_at is set.

userParentBrands

Grants a user access at the parent-brand level, which covers every account beneath that parent brand. Use this for users who should reach all accounts under one parent brand rather than one account at a time.

Column	Type	Notes
id	bigint	Primary key. Generated always as identity.
user_id	uuid	The user granted access → Supabase auth user.
parentBrand	integer	The parent brand the grant applies to → references Parent Brand.
email	text	The user's email, denormalized for convenience.
created_at	timestamptz	Audit timestamp.

Each row links a user (user_id → auth user) to one parent brand (parentBrand → Parent Brand).

The email column is a denormalized copy of the user's Supabase email. A trigger on auth.users (user_email_change, which runs update_user_email_in_user_parent_brands()) keeps it in sync whenever the user changes their email, so you never write it by hand. See Automation for the full trigger catalog.

user_settings

Per-user UI and app preferences, stored as JSON. There is exactly one row per user, keyed directly by the auth user id.

Column	Type	Notes
user_id	uuid	Primary key → Supabase auth user.
preferences	jsonb	The user's preferences. Defaults to `{}`.
created_at	timestamptz	Audit timestamp.
updated_at	timestamptz	Audit timestamp.

The primary key is the foreign key to the auth user, which enforces the one-row-per-user rule. Because settings are opaque JSON in preferences, the application decides the shape of the object.

roles

userRoles

userParentBrands

user_settings

On this page