National Commerce Docs
Database

Users & Access

Tables that attach roles and account or parent-brand access to Supabase auth users, plus the sign-in history.

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.

ColumnTypeNotes
iduuidPrimary key. Defaults to gen_random_uuid().
nametextThe role's display name.
created_attimestamptzAudit timestamp.

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

There are three roles. See Roles and access for what each one can do.

Roleid
NC Super Admin80bcdfd8-6931-4fba-9b07-5920050ec6e8
NC Internal User84911258-e621-43cd-b643-b9c5d5955c56
Client Userdaa53391-c118-41d1-ac89-b06a741f0d33

Code checks the id, not the name

The app, the MCP server, the alert flows, and the database functions all identify a role by its id. Renaming a role changes only what screens display. NC Super Admin is set here in the database only; no screen offers it.

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.

ColumnTypeNotes
iduuidPrimary key. Defaults to gen_random_uuid().
userIduuidThe user who holds the grant → Supabase auth user.
roleIduuidThe role granted → references roles.
brandintegerThe account the grant applies to → references Brands. CHECK (brand > 0).
brand_revoked_attimestamptzWhen the grant was revoked. Null while active.
revocation_reasontextFree-text reason recorded when the grant is revoked.
created_attimestamptzAudit 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.

A person has one role: every row they hold carries the same roleId, and changing someone's type updates all their rows. NC Super Admins hold a row on every account.

On the other tables, the function is_nc_staff() (an NC Super Admin or NC Internal User on an active row) adds a second rule on top of the account check. Client Users can't read or change the internal tables (draft POs and their lines, the SKU Sync queue and runs, and the PO re-import and retrigger queues), and they can read but not change brand data (SKUs, inventory, categories, POs and their lines, replenishment cycles, accounts, stores, vendors, warehouses, orders, and shipments). n8n and the workers use the service key, which these rules don't apply to.

Row-Level Security lets a user read their own active rows. Their own revoked rows are hidden from them, so a policy that looks up the caller's grants never counts a revoked one. Only NC Super Admins (through is_admin_for_brand) can read other users' rows, add rows, or change them, so nobody can change their own role. For NC Internal Users, the Users and Client Connections screens read other users' rows on the server and show them read-only.

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.

ColumnTypeNotes
idbigintPrimary key. Generated always as identity.
user_iduuidThe user granted access → Supabase auth user.
parentBrandintegerThe parent brand the grant applies to → references Parent Brand.
emailtextThe user's email, denormalized for convenience.
created_attimestamptzAudit timestamp.

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

Row-Level Security lets a signed-in user read their own rows and nothing else. No user login can add, change, or remove rows, so nobody can give themselves a parent brand. Rows are written with the service key only, which is what the n8n flows use.

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.

ColumnTypeNotes
user_iduuidPrimary key → Supabase auth user.
preferencesjsonbThe user's preferences. Defaults to {}.
created_attimestamptzAudit timestamp.
updated_attimestamptzAudit 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.

auth_events

An append-only record of sign-ins and sign-outs. It powers the Recent activity list on the Security screen and the new-sign-in emails, which need to know whether an account has used an address before. Live sessions can't answer that, because they disappear on sign-out and expiry. This table keeps the history.

ColumnTypeNotes
idbigintPrimary key. Generated always as identity.
occurred_attimestamptzWhen it happened. Defaults to now().
subject_user_iduuidThe account the event is about → Supabase auth user.
actor_user_iduuidWho did it. The same as the subject for sign-ins and sign-outs.
actiontextsign_in or sign_out.
subject_emailtextThe user's email, copied in so the history still reads after a user is deleted.
ip_addresstextThe internet address the session came from.
user_agenttextThe browser's raw user-agent string. The app only ever shows a parsed label like "Chrome on macOS".
session_iduuidThe session the event belongs to.
detailsjsonbReserved for extra context. Never holds tokens or codes.
citytextCity from Vercel's edge headers, filled in by middleware shortly after sign-in.
regiontextRegion code from the same headers (for example 21, not a name).
countrytextTwo-letter country code from the same headers.

No application code writes this table. Two triggers on Supabase's auth.sessions fill it: a new session writes sign_in, and a deleted session writes sign_out. See Automation.

Reading the rows

  • One sign-in can write two sign_in rows within a minute of each other. Anything comparing a sign-in with history has to skip the last minute or it matches itself.
  • sign_out also covers expiry. When a session times out and Supabase deletes it, the same trigger fires.
  • Rows older than 12 months are deleted every night at 03:15 UTC by the prune-auth-events cron job.

Row-Level Security is on with no policies, which blocks every direct read and write from the app. The portal reads it only through the get_my_auth_events function, which returns the caller's own rows.

On this page