# User roles and stored attributes

Written 2026-09-25 from the live schema and code. Every column name below is exact.

There are **four separate role systems**. Confusing them is the most common mistake in this codebase, so read this section first.

| System | Question it answers | Where it lives | Values |
|---|---|---|---|
| **Platform role** | Who is this person to HeavyHaul Agent? | `AUTH_USERS` env, `auth_accounts.role`, `profiles.default_role` | `broker`, `dispatcher`, `driver`, `admin` |
| **Operational role** (a mode) | What do they do, at which company? | `membership_roles.role_type` | `carrier_dispatcher`, `carrier_driver`, `freight_broker`, `pilot_company_dispatch`, `pilot_driver`, and the carrier office roles `carrier_safety_manager`, `carrier_permit_manager`, `carrier_accounting`, `carrier_fleet_manager` (migration 0050) |
| **Trip role** | What are they on this one trip? | `trip_participants.role` | `broker`, `dispatcher`, `driver`, `pilot`, `shipper`, `admin` |
| **Company membership role** (legacy) | Older single-role field on the relationship | `company_memberships.role` | `company_owner`, `company_admin`, `broker_manager`, `freight_broker`, `billing_admin`, `viewer`, `carrier_dispatcher`, `carrier_driver` |

Two more things that look like roles but are not:

- **Permission level** — `company_memberships.permission_level`: `member` or `company_admin`. Independent of the role. Someone can be Carrier Dispatcher **and** Company Admin.

**Carrier office roles (D15, Nash 2026-09-30, migration 0050).** Safety Manager (`carrier_safety_manager`), Permit Manager (`carrier_permit_manager`), Accounting (`carrier_accounting`) and Fleet Manager (`carrier_fleet_manager`) carry exactly Carrier Dispatcher powers: the same Carrier Dispatch workspace (`/cd-dashboard`, one "Carrier Dispatch Mode" label), the same trip role `dispatcher`, the same tools. Only the title differs. They exist for carrier companies only; brokerage and pilot role sets are unchanged. Code decides powers with `isCarrierDispatchRole(roleType)` (`src/lib/domain/modes.ts`), never by comparing to `'carrier_dispatcher'` alone; `CARRIER_COMPANY_ROLES` (the five plus `carrier_driver`) is what a carrier may hand out at sign-up, in Company Info invites / approvals and in the member list. The legacy `company_memberships.role` column still reads `carrier_dispatcher` for all five.
- **Moderator surface grant** — `moderator_grants.surface`: which admin page a person may open. Not a role; an access grant. Values: `moderation`, `broker_leads`, `pilot_invitations`, `email_templates`, `email_health`, `company_review`, `intake_security`, `users` (never delegated).

There is exactly one elevated platform role and it is called **Admin**. There is no Super Admin.

## Where a person's identity lives

Three places, in order of authority for sign-in:

1. **`AUTH_USERS`** (environment variable, JSON array) — who may sign in with a password. Fields: `username`, `password`, `name`, `role`, `company`, `phone`, `email`. The user id is derived deterministically from the username, so database references survive restarts.
2. **`auth_accounts`** (table) — what an admin or the person changed since. **Wins over `AUTH_USERS`.** Also the whole record for self-service accounts (`source = 'self_signup'`).
3. **`profiles`** (table) — the record every foreign key points at (trips, participants, documents). Upserted at sign-in.

## Table reference

### `profiles` — the person, referenced everywhere

| Column | Meaning |
|---|---|
| `id` | user id, the key used across the platform |
| `email` | sign-in email |
| `full_name` | display name |
| `phone` | phone, self-editable |
| `company_name` | free-text company from the account, not the verified company |
| `default_role` | platform role |
| `created_at` | member since |
| `chat_languages`, `primary_language` | AI chat languages |
| `delivery_email` | verified address email is delivered to when it differs from sign-in |
| `pending_email`, `pending_email_token_hash`, `pending_email_sent_at` | delivery-address change in flight |
| `pending_login_email`, `pending_login_email_token_hash`, `pending_login_email_sent_at` | **sign-in email** change in flight |
| `login_email_changed_at`, `previous_login_email` | audit of the last sign-in email change |
| `claim_status` | migration 0038: null for a profile the app created; `unclaimed`, `claim_candidate`, `claimed`, `manual_review`, `conflict` for an imported historical person |
| `claimed_at`, `claim_method`, `claimed_user_id` | when and how the historical identity was connected; `claimed_user_id` only when the claiming account has another id |
| `source_system`, `source_record_id` | where the imported person came from (`synchron`, the Synchron user id) |
| `source_token` | migration 0040: the source system's own handle for this person (Synchron's 16-character user token). The key their API references; `source_record_id` is the secondary reference |
| `email_normalized` | lowercase, trimmed email; the key the claim matches on |
| `historical_import`, `imported_at`, `import_batch`, `source_roles`, `import_flags` | import provenance |

### `auth_accounts` — credentials, security, preferences

| Column | Meaning |
|---|---|
| `user_id` | primary key, matches `profiles.id` |
| `username`, `email`, `name`, `phone` | identity; for a self account the username **is** the email |
| `password_hash`, `password_updated_at` | admin-set password, overrides the env |
| `role` | admin-set platform role, overrides the env |
| `source` | `env` or `self_signup` |
| `email_verified_at` | when the address was proved |
| `tokens_valid_after` | every session issued before this is dead |
| `blocked_at`, `blocked_reason` | account block |
| `intake_trust_level` | 0–4, used by public intake protection |
| `totp_secret_enc`, `totp_pending_enc`, `totp_enabled_at`, `totp_recovery_hashes`, `totp_updated_by` | multi-factor authentication |
| `default_context_key` | the mode sign-in opens |
| `last_active_context_key` | the mode last used |
| `admin_default_workspace` | admin only, the workspace their sign-in opens |
| `updated_by`, `created_at`, `updated_at` | audit |

### `company_memberships` — the person ⇄ company relationship

One row per person per company (`unique (user_id, company_id)`).

| Column | Meaning |
|---|---|
| `id`, `user_id`, `company_id` | the relationship |
| `role` | legacy single role; the roles now live in `membership_roles` |
| `status` | `pending`, `approved`, `rejected`, `revoked`, plus `historical_pending_confirmation`, `no_longer_current`, `manual_review` |
| `permission_level` | `member` or `company_admin` |
| `requested_at`, `approved_at`, `approved_by`, `approved_by_user_id` | request and approval |
| `rejected_at`, `rejected_by`, `rejected_by_user_id`, `rejection_reason`, `rejection_note` | rejection |
| `revoked_at`, `revoked_by`, `revoked_by_user_id` | revocation |
| `verification_method`, `verification_provider` | how they were verified |
| `confirmed_at` | when the person confirmed a historical relationship |
| `historically_verified`, `historical_verified_at`, `broker_lead_id` | pre-verified import origin |
| `source_system`, `source_record_id` | migration 0038: the source row behind an imported relationship |
| `created_at`, `updated_at` | audit |

### `membership_roles` — what they actually do at that company

The current source of truth for modes. Added by migration 0034.

| Column | Meaning |
|---|---|
| `id` | the **context key** used by the session and Switch Mode |
| `membership_id`, `user_id`, `company_id` | the relationship it hangs off |
| `role_type` | `carrier_dispatcher`, `carrier_driver`, `freight_broker`, `pilot_company_dispatch`, `pilot_driver`, `carrier_safety_manager`, `carrier_permit_manager`, `carrier_accounting`, `carrier_fleet_manager` (the value `broker_agent` was retired by migration 0037; the four carrier office roles were added by 0050 and carry Carrier Dispatcher powers) |
| `status` | `active`, `pending`, `removed_by_user`, `revoked` |
| `added_at`, `added_by` | when and by whom |
| `removed_at`, `removed_by`, `removed_by_user_id` | removal, never a delete |
| `source_role` | migration 0038: `synchron_client` / `synchron_driver` when imported, null when granted in the app |
| `created_at`, `updated_at` | audit |

Unique on `(membership_id, role_type)`, so one person can hold several roles at one company but never the same one twice.

### `trip_participants` — the person on one trip

| Column | Meaning |
|---|---|
| `id`, `trip_id`, `user_id` | the participation; `user_id` is null until claimed |
| `email`, `name`, `phone`, `phone_ext` | contact on this trip |
| `role` | trip role |
| `status` | `invited`, `active`, `removed`, `imported` |
| `invited_by` | who invited them |
| `completed_at`, `completion_prompt_dismissed_at` | per-person trip completion |
| `share_chat` | whether their chat is visible to others |
| `claimed_at`, `claim_method` | `email_verified` or `admin` |
| `source_system`, `source_record_id` | imported from Synchron |
| `source_token` | migration 0040: the Synchron user token for this person |
| `created_at` | audit |

### `email_preferences` — one row per user

`user_id`, then a boolean per category: `account_security` (always on), `trip_operational`, `permit_notifications`, `route_notifications`, `billing_notifications`, `product_updates`, `newsletter`, `marketing_campaigns`, `pilot_opportunities`, `broker_opportunities`, `carrier_opportunities`, plus `updated_by`, `updated_at`.

### `role_audit_log` — every role and mode action

`id`, `user_id`, `membership_role_id`, `company_id`, `role_type`, `action`, `previous_status`, `new_status`, `actor_label`, `actor_user_id`, `detail`, `created_at`.

Actions: `role_added`, `role_verified`, `role_removed_by_user`, `role_revoked_by_company`, `role_reactivated`, `default_mode_changed`, `mode_switched`, `company_relationship_changed`, `company_admin_changed`, `admin_preview_entered`, `admin_preview_exited`.

## People who are not users yet

Two tables hold people before they have an account:

- **`pilot_profiles`** — imported pilot car operators. `name`, `company_name`, `email`, `phone`, `phone_digits`, `primary_state`, `related_states`, `account_type`, `claim_status`, `invitation_status`, `email_opt_in_status`, `sms_opt_in_status`, `source`, `source_reference`, `last_contacted_at`, `next_follow_up_at`, `claimed_user_id`, `claimed_company_id`, `claimed_at`, `admin_notes`, `claim_token`, `unsubscribe_token`.
- **`broker_agent_leads`** — pre-verified freight brokers (2,983 rows today). Identity: `first_name`, `last_name`, `email`, `email_normalized`, `phone`, `phone_digits`, `state`, `mailbox_type`, `email_domain_check`. Relationship: `broker_company_id`, `broker_company_name`, `mc_number`, `usdot_number`, `historical_relationship_verified`, `historical_verification_source`, `claim_status`, `relationship_confirmation_status`. Outreach: `invitation_status`, `invited_at`, `reminded_at`, `emails_sent`, `last_emailed_at`, `last_template_key`, `last_delivered_at`, `last_opened_at`, `last_clicked_at`, `bounced_at`, `unsubscribed_at`, `suppressed_at`, `tags`, `notes`. Conversion: `signed_up_at`, `signed_up_user_id`, `claimed_user_id`, `claimed_at`, `confirmed_at`, `join_token_issued_at`.

## The session cookie

Signed JWT named `hha_session`. Payload: `sub` (user id), `email`, `name`, `role`, `company`, `internal`, `origin_sub` (who actually signed in, for admin previews), `ctx` (active mode key), `preview` (admin product view), `iat`.

**The cookie never grants anything.** Role, surfaces and modes are re-read from the database on every request.

## Live counts, 2026-09-25

| Table | Rows |
|---|---|
| profiles | 17 |
| auth_accounts | 2 |
| company_memberships | 15 |
| membership_roles | 8 |
| trip_participants | 122 |
| email_preferences | 0 |
| pilot_profiles | 4 |
| broker_agent_leads | 2,983 |
| moderator_grants | 0 |
| companies | 1,174 |
| role_audit_log | 4 |

## What the live data actually holds, 2026-09-25

`membership_roles` (the current truth) — 8 active rows:

| role_type / status | Rows |
|---|---|
| freight_broker / active (stored as `broker_agent` until migration 0037 runs) | 5 |
| carrier_dispatcher / active | 3 |

`company_memberships.role` (legacy) — note that three of these values are **permissions, not operational roles**, which is exactly why the field was replaced:

| role / status / permission_level | Rows |
|---|---|
| freight_broker / approved / member (stored as `broker_agent` until 0037) | 4 |
| freight_broker / pending / member (same) | 3 |
| carrier_dispatcher / pending / member | 3 |
| carrier_dispatcher / approved / member | 1 |
| carrier_dispatcher / revoked / member | 1 |
| company_admin / approved / company_admin | 2 |
| company_owner / approved / company_admin | 1 |

`auth_accounts` holds two rows, both `source = 'env'`: the admin with `role = 'admin'`, and one account with `role = null` meaning "no override, use AUTH_USERS".

## Known gaps

- `company_memberships.role` is legacy **and inconsistent**: three live rows store a permission (`company_admin`, `company_owner`) in the role field. New code must read `membership_roles.role_type` and `permission_level` separately. The old field is still written for compatibility.
- `pilot` exists as a **trip role** and `pilot_company_dispatch` / `pilot_driver` exist as operational roles, but no company flow grants them yet. Pilot modes are admin preview only.
- Platform role has no `pilot` value. A pilot signs in as `driver` today.
- Imported historical people (`docs/SYNCHRON-PEOPLE-IMPORT.md`) are `profiles` rows with `claim_status = 'unclaimed'` and no `auth_accounts` row until they sign up and verify the email.
- `email_preferences` is empty because defaults apply until someone changes them.
- `auth_accounts` has only 2 rows: most logins still come from `AUTH_USERS` and only gain a row when something is changed.