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.
| 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.
There are three roles. See Roles and access for what each one can do.
| Role | id |
|---|---|
| NC Super Admin | 80bcdfd8-6931-4fba-9b07-5920050ec6e8 |
| NC Internal User | 84911258-e621-43cd-b643-b9c5d5955c56 |
| Client User | daa53391-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.
| 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.
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.
| 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. |
| 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).
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.
| 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.
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.
| Column | Type | Notes |
|---|---|---|
| id | bigint | Primary key. Generated always as identity. |
| occurred_at | timestamptz | When it happened. Defaults to now(). |
| subject_user_id | uuid | The account the event is about → Supabase auth user. |
| actor_user_id | uuid | Who did it. The same as the subject for sign-ins and sign-outs. |
| action | text | sign_in or sign_out. |
| subject_email | text | The user's email, copied in so the history still reads after a user is deleted. |
| ip_address | text | The internet address the session came from. |
| user_agent | text | The browser's raw user-agent string. The app only ever shows a parsed label like "Chrome on macOS". |
| session_id | uuid | The session the event belongs to. |
| details | jsonb | Reserved for extra context. Never holds tokens or codes. |
| city | text | City from Vercel's edge headers, filled in by middleware shortly after sign-in. |
| region | text | Region code from the same headers (for example 21, not a name). |
| country | text | Two-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_inrows within a minute of each other. Anything comparing a sign-in with history has to skip the last minute or it matches itself. sign_outalso 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-eventscron 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.
Reporting & Financials
Precomputed financial reporting tables: weekly account rollups, net-revenue snapshots, and the global model inputs that feed the math.
Shipping Audit Tool
Database tables for the standalone Shipping Audit Tool that compares a lead's shipments against provider rate cards and returns savings deliverables.