FR-01 — IAM Database Design
Introduction
This section defines the physical database design required to support SalesFam Authentication, Identity & Access Management. It translates the FR-01 Domain & Data Model into persistent database structures for user identity, account verification, onboarding, authentication sessions, password recovery, roles, and account lifecycle management.
Design Scope
The IAM database design covers the persistence requirements for:
- User identities and credentials
- Company and Sales Representative profiles
- Email verification
- Authentication sessions
- Password recovery
- Roles and account status
- Referential integrity between IAM entities
Detailed database structures are documented here and extended by later functional areas where the same entities acquire additional business data.
1. Database Scope
The IAM MVP requires these core tables: users, companies, sales_representatives, verification_codes, sessions, password_resets.
2. users
This is the central IAM table. It represents the platform identity, not the Company or Rep business information.
| Column | Type | Null | Key | Description |
|---|---|---|---|---|
| id | UUID / CHAR(36) | No | PK | Unique user ID |
| VARCHAR(255) | No | UNIQUE | User's login email | |
| password_hash | VARCHAR(255) | No | Hashed password | |
| role | ENUM | Yes | COMPANY, REP, ADMIN | |
| account_status | ENUM | No | Account lifecycle | |
| email_verified_at | DATETIME | Yes | Time email was verified | |
| created_at | DATETIME | No | Creation time | |
| updated_at | DATETIME | No | Last update |
role: COMPANY, REP, ADMIN. Nullable initially because the user selects the account type after email verification — e.g. at registration role = NULL, after verification role = NULL, and once the user selects Company during onboarding, role = COMPANY.
account_status (MVP): PENDING_VERIFICATION, ACTIVE, SUSPENDED, DEACTIVATED. We should not put EMAIL_VERIFIED here because email verification is already represented by email_verified_at. This keeps account state and verification state separate.
3. companies
This table represents the Company profile associated with a User.
| Column | Type | Null | Key | Description |
|---|---|---|---|---|
| id | UUID / CHAR(36) | No | PK | Company ID |
| user_id | UUID / CHAR(36) | No | UNIQUE, FK | Owner User |
| onboarding_status | ENUM | No | Company onboarding state | |
| created_at | DATETIME | No | Creation time | |
| updated_at | DATETIME | No | Last update |
onboarding_status: NOT_STARTED, IN_PROGRESS, COMPLETED.
Relationship: companies.user_id → users.id. user_id is UNIQUE, enforcing 1 User → max 1 Company profile. The detailed merchant fields will be expanded in FR-02.
4. sales_representatives
This table represents the Sales Representative profile associated with a User.
| Column | Type | Null | Key | Description |
|---|---|---|---|---|
| id | UUID / CHAR(36) | No | PK | Rep ID |
| user_id | UUID / CHAR(36) | No | UNIQUE, FK | Owner User |
| onboarding_status | ENUM | No | Rep onboarding state | |
| created_at | DATETIME | No | Creation time | |
| updated_at | DATETIME | No | Last update |
Relationship: sales_representatives.user_id → users.id. Again, user_id is UNIQUE. The detailed Rep fields will be expanded in FR-03.
5. verification_codes
Stores temporary email-verification records.
| Column | Type | Null | Key | Description |
|---|---|---|---|---|
| id | UUID / CHAR(36) | No | PK | Verification record ID |
| user_id | UUID / CHAR(36) | No | FK | Associated user |
| code_hash | VARCHAR(255) | No | Hashed verification code | |
| expires_at | DATETIME | No | Code expiration | |
| used_at | DATETIME | Yes | When code was consumed | |
| created_at | DATETIME | No | Creation time |
Relationship: users 1 ─── N verification_codes. A user can therefore have code 1 → expired, code 2 → expired, code 3 → valid. The raw verification code should not be stored.
6. sessions
Stores authenticated session/refresh information.
| Column | Type | Null | Key | Description |
|---|---|---|---|---|
| id | UUID / CHAR(36) | No | PK | Session ID |
| user_id | UUID / CHAR(36) | No | FK | Session owner |
| refresh_token_hash | VARCHAR(255) | No | Hashed refresh credential | |
| expires_at | DATETIME | No | Session expiration | |
| revoked_at | DATETIME | Yes | Session revocation time | |
| created_at | DATETIME | No | Session creation time |
Relationship: users 1 ─── N sessions. This supports Login, Logout, and Refresh, and allows a user to have multiple sessions/devices.
7. password_resets
Stores temporary password-reset records.
| Column | Type | Null | Key | Description |
|---|---|---|---|---|
| id | UUID / CHAR(36) | No | PK | Reset record ID |
| user_id | UUID / CHAR(36) | No | FK | Associated user |
| token_hash | VARCHAR(255) | No | Hashed reset token | |
| expires_at | DATETIME | No | Token expiration | |
| used_at | DATETIME | Yes | When token was consumed | |
| created_at | DATETIME | No | Creation time |
Relationship: users 1 ─── N password_resets.
8. Foreign-Key Rules
For IAM, the relationships should enforce referential integrity:
verification_codes.user_id→users.idsessions.user_id→users.idpassword_resets.user_id→users.idcompanies.user_id→users.idsales_representatives.user_id→users.id
Delete behavior: for a financial platform, cascade deletion should not be used on User → Company/Rep or transactional identity records. A user's account may later be suspended or deactivated, but historical records must remain traceable. The preferred rule is: a User cannot be physically deleted while dependent records that must be retained exist. Account lifecycle should use SUSPENDED / DEACTIVATED rather than deleting the identity.
9. Important Constraints
- Email:
users.email→UNIQUE. There must be only one account for a given email. - Company profile:
companies.user_id→UNIQUE. One User cannot create multiple Company profiles. - Rep profile:
sales_representatives.user_id→UNIQUE. One User cannot create multiple Rep profiles. - Verification/reset/session records: these remain one-to-many because one user can generate multiple records over time.
10. Recommended Indexes
At minimum:
users:UNIQUE(email),INDEX(account_status)verification_codes:INDEX(user_id),INDEX(expires_at)sessions:INDEX(user_id),INDEX(expires_at),INDEX(revoked_at)password_resets:INDEX(user_id),INDEX(expires_at)companies:UNIQUE(user_id)sales_representatives:UNIQUE(user_id)
These support the most common IAM lookups without adding unnecessary indexes.