# User & Employee System Architecture

**Last Updated:** December 2025
**Status:** Current State Documentation

---

## Overview

BuyerKiosk uses a **dual-system architecture** for managing people:

1. **Users** (`uf_user`) - Authentication accounts in the central database
2. **Employees** (`employees`) - Worker records in per-store databases

These systems are connected via a **bridging table** (`user_employee_links`) that links user accounts to employee records.

---

## Database Architecture

### Central Database: `kiosk_users`

The central database holds all authentication and cross-store data.

#### Core User Tables

| Table | Purpose |
|-------|---------|
| `uf_user` | User accounts (login credentials, profile) |
| `uf_group` | Roles and permission groups |
| `uf_group_user` | Many-to-many user↔group membership |
| `uf_authorize_user` | User-level permission rules |
| `uf_authorize_group` | Group-level permission rules |
| `uf_store_user` | User↔store access mapping |
| `uf_user_rememberme` | "Remember Me" session tokens |
| `uf_apiKey` | API keys |
| `uf_apiKey_user` | API key↔user assignments |

#### User-Employee Linking Tables

| Table | Purpose |
|-------|---------|
| `user_employee_links` | Links `uf_user.id` to `employees.employeeID` per store |
| `employee_invitations` | Self-registration invitation tokens |

### Per-Store Databases: `kiosk_{typeNum}`

Each store (identified by `typeNum` like `ou00`, `pa00`) has its own database.

#### Employee Tables

| Table | Purpose |
|-------|---------|
| `employees` | Worker records with employment data |
| `employee_sync_log` | External provider sync audit trail |

---

## User Table Schema (`uf_user`)

```sql
CREATE TABLE uf_user (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(50) UNIQUE,        -- Login username
    display_name VARCHAR(150),            -- Display name
    password VARCHAR(255),                -- Hashed password
    email VARCHAR(150),                   -- Email address
    activation_token VARCHAR(255),        -- Email activation token
    last_activation_request TIMESTAMP,    -- Activation email throttle
    lost_password_request VARCHAR(255),   -- Password reset token
    lost_password_timestamp TIMESTAMP,    -- Reset token timestamp
    active TINYINT(1),                    -- Account activated
    enabled TINYINT(1),                   -- Account enabled
    title VARCHAR(150),                   -- User title
    sign_up_stamp TIMESTAMP,              -- Registration date
    last_sign_in_stamp TIMESTAMP,         -- Last login
    primary_group_id INT,                 -- FK to uf_group
    locale VARCHAR(10),                   -- Language preference
    pin VARCHAR(10),                      -- Legacy PIN field
    dailyReport TINYINT(1),               -- Daily email preference

    -- Legacy fields (deprecated)
    oldEmployeeID INT,
    oldTypeNum VARCHAR(10)
);
```

---

## Employee Table Schema (`employees`)

Located in each store database (`kiosk_{typeNum}`):

```sql
CREATE TABLE employees (
    employeeID INT PRIMARY KEY AUTO_INCREMENT,

    -- Identity
    login VARCHAR(50),                    -- Legacy login (not used for auth)
    password VARCHAR(255),                -- Legacy password (not used)
    employeeFirstName VARCHAR(50),
    employeeLastName VARCHAR(50),
    email VARCHAR(150),
    phone VARCHAR(20),

    -- Profile
    photoUrl VARCHAR(500),
    avatarOverride TINYINT(1) DEFAULT 0,  -- Custom photo uploaded
    displayName VARCHAR(150),

    -- Emergency Contact
    emergencyContactName VARCHAR(100),
    emergencyContactPhone VARCHAR(20),

    -- Employment
    hireDate DATE,
    terminationDate DATE,
    leaveStartDate DATE,
    leaveEndDate DATE,
    position VARCHAR(100),
    hourlyRate DECIMAL(10,2),
    role TINYINT DEFAULT 0,
    active TINYINT(1) DEFAULT 1,

    -- External Provider
    source ENUM('homegrown', 'wheniwork', 'homebase') DEFAULT 'homegrown',
    externalId VARCHAR(50),               -- WhenIWork/Homebase user ID
    lastSyncedAt TIMESTAMP,

    -- System Integration
    drsEmployeeId VARCHAR(50),            -- DRS system ID
    clockPin VARCHAR(10),                 -- Time clock PIN
    dailyEmailEnabled TINYINT(1) DEFAULT 0,
    hasUserAccount TINYINT(1) DEFAULT 0,  -- Has linked uf_user

    -- Metadata
    createdAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updatedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);
```

---

## User-Employee Link Schema

```sql
CREATE TABLE user_employee_links (
    id INT PRIMARY KEY AUTO_INCREMENT,
    userId INT NOT NULL,                  -- FK to uf_user.id
    typeNum VARCHAR(10) NOT NULL,         -- Store identifier
    employeeId INT NOT NULL,              -- FK to employees.employeeID
    linkType ENUM('auto_name', 'auto_login', 'manual', 'promotion'),
    linkedAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    linkedBy INT,                         -- Admin user who created link

    UNIQUE KEY (userId, typeNum),         -- One employee per user per store
    UNIQUE KEY (typeNum, employeeId)      -- One user per employee
);
```

---

## Relationship Model

```
┌─────────────────────────────────────────────────────────────────┐
│                    CURRENT DATA MODEL                           │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  CENTRAL DB (kiosk_users)          STORE DBs (kiosk_{typeNum}) │
│  ────────────────────────          ─────────────────────────── │
│                                                                 │
│  ┌──────────────┐                  ┌──────────────┐            │
│  │   uf_user    │                  │  employees   │            │
│  │──────────────│                  │──────────────│            │
│  │ id           │◄────────────────►│ employeeID   │            │
│  │ user_name    │    linked via    │ firstName    │            │
│  │ email        │    user_employee │ lastName     │            │
│  │ password     │    _links table  │ email        │            │
│  │ ...          │                  │ source       │            │
│  └──────────────┘                  │ externalId   │            │
│         │                          │ ...          │            │
│         │                          └──────────────┘            │
│         │ N:M                                                  │
│         ▼                                                       │
│  ┌──────────────┐                                              │
│  │  uf_group    │                                              │
│  │──────────────│                                              │
│  │ Permissions  │                                              │
│  │ Store access │                                              │
│  └──────────────┘                                              │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘
```

### Key Relationships

| Relationship | Cardinality | Description |
|--------------|-------------|-------------|
| User → Groups | Many-to-Many | Via `uf_group_user` |
| User → Employees | One-to-Many | One per store via `user_employee_links` |
| Employee → User | One-to-One | Per store (optional) |
| User → Stores | Many-to-Many | Via group membership or `uf_store_user` |

---

## Employee Sources

Employees can come from three sources:

### 1. Homegrown (Manual)

- Created directly in BuyerKiosk
- Full CRUD operations available
- `source = 'homegrown'`

**Key File:** `userfrosting/src/BuyerKiosk/Employee/HomegrownProvider.php`

### 2. WhenIWork (External Sync)

- Synced from WhenIWork scheduling system
- Read-only locally (updates come from WhenIWork)
- `source = 'wheniwork'`
- `externalId` = WhenIWork user ID

**Key File:** `userfrosting/src/BuyerKiosk/Employee/WhenIWorkProvider.php`

**Sync Behavior:**
- Triggered manually via admin button
- Creates new employees from WhenIWork
- Updates existing employees (name, position, photo)
- Deactivates removed employees
- Downloads and caches avatars locally

### 3. Homebase (External Sync)

- Synced from Homebase scheduling system
- `source = 'homebase'`
- Currently stub implementation (not fully integrated)

**Key File:** `userfrosting/src/BuyerKiosk/Employee/HomebaseProvider.php`

---

## User-Employee Linking

### Link Types

| Type | Description | Created By |
|------|-------------|------------|
| `auto_name` | Matched by firstName + lastName | System (auto-link) |
| `auto_login` | Matched by username = login | System (legacy) |
| `manual` | Admin manually linked | Admin user |
| `promotion` | Employee promoted to user | Admin user |

### Linking Methods

#### 1. Auto-Link by Name

Admin triggers auto-link for a store. System matches users to employees by exact name match.

```
POST /:typeNum/api/user-employee/auto-link
```

#### 2. Manual Link

Admin explicitly links a user to an employee.

```
POST /:typeNum/api/user-employee/links
{ "userId": 123, "employeeId": 456 }
```

#### 3. Employee Promotion

Admin promotes an employee to a full user account.

```
POST /:typeNum/api/employees/:id/promote
{ "username": "jsmith", "email": "j@example.com", "groupIds": [5, 12] }
```

#### 4. Self-Registration Invitation

Admin sends invitation email. Employee creates their own account.

```
POST /:typeNum/api/employee-invitations
{ "employeeId": 456, "email": "j@example.com", "defaultGroups": [5] }

# Employee receives email with link:
GET /invite/:token

# Employee completes registration:
POST /invite/:token
{ "username": "jsmith", "password": "...", "confirmPassword": "..." }
```

---

## Key Code Files

### Models

| File | Purpose |
|------|---------|
| `userfrosting/models/mysql/MySqlUser.php` | User ORM model |
| `userfrosting/models/mysql/MySqlUserLoader.php` | User factory/loader |
| `userfrosting/models/mysql/MySqlGroup.php` | Group ORM model |
| `userfrosting/src/BuyerKiosk/Employee/Employee.php` | Employee data model |
| `userfrosting/src/BuyerKiosk/UserEmployee/UserEmployeeLink.php` | Link entity |

### Managers

| File | Purpose |
|------|---------|
| `userfrosting/src/BuyerKiosk/Employee/EmployeeManager.php` | Employee operations facade |
| `userfrosting/src/BuyerKiosk/Employee/HomegrownProvider.php` | Manual employee CRUD |
| `userfrosting/src/BuyerKiosk/Employee/WhenIWorkProvider.php` | WhenIWork sync |
| `userfrosting/src/BuyerKiosk/Employee/HomebaseProvider.php` | Homebase sync |
| `userfrosting/src/BuyerKiosk/UserEmployee/UserEmployeeLinkManager.php` | Link management |
| `userfrosting/src/BuyerKiosk/UserEmployee/EmployeeInvitationManager.php` | Invitation system |

### Controllers

| File | Purpose |
|------|---------|
| `userfrosting/src/BuyerKiosk/Core/Controllers/AccountController.php` | Login/logout/registration |
| `userfrosting/src/BuyerKiosk/Core/Controllers/UserController.php` | User management |
| `userfrosting/src/BuyerKiosk/Core/Controllers/EmployeeApiController.php` | Employee REST API |
| `userfrosting/src/BuyerKiosk/Core/Controllers/UserEmployeeLinkController.php` | Link management API |
| `userfrosting/src/BuyerKiosk/Core/Controllers/EmployeeInvitationController.php` | Invitation API |

### Routes

| File | Purpose |
|------|---------|
| `userfrosting/routes/employee.php` | Employee CRUD routes |
| `userfrosting/routes/user-employee.php` | User-employee linking routes |

---

## Access Control

### Store Access Check

Users access stores through group membership:

```php
// Check if user can access store
if (!$app->user->checkStoreGroup($typeNum)) {
    $app->notAuthorized();
}
```

**Implementation:** `MySqlUser::checkStoreGroup()` checks if user belongs to:
1. A group with `all_stores` access, OR
2. A group matching the store's typeNum pattern

### Permission Check

```php
// Check if user has specific permission
if (!$app->user->checkAccess('uri_employees')) {
    $app->notAuthorized();
}
```

**Implementation:** `MySqlUser::checkAccess()` checks:
1. User-level rules in `uf_authorize_user`
2. Group-level rules in `uf_authorize_group`

---

## Database Connection Pattern

### Multi-Database Access

```php
// Connect to store database
$storeController = new \BuyerKiosk\StoreController($typeNum);
$store = $storeController->getStore();
$db = dbConnectByName($store->getDbName());

// Query employees in that store
$stmt = $db->prepare("SELECT * FROM employees WHERE active = 1");
```

**Connection Pooling:** `dbConnectByName()` caches up to 50 connections.

---

## Common Patterns

### Get Employee for Current User

```php
$linkManager = new UserEmployeeLinkManager($db);
$employee = $linkManager->getLinkedEmployee($app->user->id, $typeNum);

if ($employee) {
    // User has linked employee at this store
    echo $employee->getFullName();
}
```

### Get User for Employee

```php
$linkManager = new UserEmployeeLinkManager($db);
$user = $linkManager->getLinkedUser($typeNum, $employeeId);

if ($user) {
    // Employee has a user account
    echo $user->display_name;
}
```

### Check if Employee Has User

```php
$hasUser = $linkManager->hasLinkedUser($typeNum, $employeeId);
// Also available as employees.hasUserAccount field
```

---

## Known Limitations

### Data Duplication

- Names, emails, and other data may exist in both `uf_user` and `employees`
- No automatic sync between linked records
- Manual updates required in both places

### Multi-Store Complexity

- Same person working at multiple stores has multiple employee records
- Each store's employee must be linked separately
- No automatic detection of same person across stores

### External Provider Constraints

- WhenIWork employees are read-only locally
- Local changes may be overwritten on next sync
- `avatarOverride` flag prevents photo overwrites

---

## Related Documentation

- [Authentication Flow](./authentication-flow.md)
- [Unified Users Specification](../specs/unified-users-modern-auth-spec.md)