# `users` — User Accounts

**Module:** Authentication  
**Type:** Tenant + Platform  
**Soft Delete:** Partial (`is_active` flag, `deactivated_at`)

---

## Purpose

Central user account table for all three roles: Super Admin, Couple, and Event Crew. Each row represents one login identity in the system.

---

## Columns

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| `id` | `BIGINT UNSIGNED AUTO_INCREMENT` | No | — | Internal surrogate key |
| `workspace_id` | `BIGINT UNSIGNED` | Yes | NULL | → `workspaces.id`. NULL for Super Admin |
| `role_id` | `TINYINT UNSIGNED` | No | — | → `roles.id` |
| `email` | `VARCHAR(191)` | Yes | NULL | Login email for Admin and Couple. NULL for Crew |
| `username` | `VARCHAR(100)` | Yes | NULL | Login username for Crew. NULL for Admin/Couple |
| `password_hash` | `VARCHAR(255)` | No | — | bcrypt hash. Never plain text |
| `display_name` | `VARCHAR(100)` | No | — | Name shown in UI and audit logs |
| `is_active` | `TINYINT(1)` | No | `1` | 0 = deactivated, cannot authenticate |
| `created_by` | `BIGINT UNSIGNED` | Yes | NULL | → `users.id`. NULL for seeded Super Admin |
| `created_at` | `DATETIME` | No | `CURRENT_TIMESTAMP` | Creation timestamp |
| `updated_at` | `DATETIME` | Yes | NULL | Last modification |
| `last_login_at` | `DATETIME` | Yes | NULL | Last successful authentication |
| `deactivated_at` | `DATETIME` | Yes | NULL | NULL if active |

---

## Primary Key

`id` — `BIGINT UNSIGNED`

---

## Indexes

| Name | Columns | Type |
|------|---------|------|
| `PRIMARY` | `id` | Primary |
| `uq_users_email` | `email` | Unique |
| `idx_users_workspace_id` | `workspace_id` | Index |
| `idx_users_is_active` | `is_active` | Index |

---

## Foreign Keys

| Column | References | On Delete | On Update |
|--------|-----------|-----------|-----------|
| `workspace_id` | `workspaces.id` | CASCADE | CASCADE |
| `role_id` | `roles.id` | RESTRICT | CASCADE |
| `created_by` | `users.id` (self) | SET NULL | CASCADE |

---

## Referenced By

| Table | Column | On Delete |
|-------|--------|-----------|
| `sessions` | `user_id` | CASCADE |
| `crew_accounts` | `user_id` | CASCADE |
| `checkins` | `operator_id` | RESTRICT |
| `communication_logs` | `sent_by` | RESTRICT |
| `support_sessions` | `admin_user_id` | RESTRICT |

---

## Business Rules

- Couple users use `email` to log in; Crew users use `username` — never both
- Passwords are always stored as bcrypt hashes
- Deactivated users (`is_active = 0`) cannot authenticate but records are preserved
- Super Admin has `workspace_id = NULL`
- User deletion cascades to sessions and crew accounts

---

## Notes

- The `email` field has a UNIQUE constraint that allows NULL (standard SQL behavior allows multiple NULLs in unique indexes)
- `created_by` is a self-referential FK tracking which Super Admin created the account