# `sessions` — User Sessions

**Module:** Authentication  
**Soft Delete:** No (logical via `is_active`, `destroyed_at`)

---

## Purpose

Tracks all authenticated sessions across all user types. Session tokens stored in browser cookies are validated against this table on every authenticated request.

---

## Columns

| Column | Type | Nullable | Default | Description |
|--------|------|----------|---------|-------------|
| `id` | `BIGINT UNSIGNED AUTO_INCREMENT` | No | — | Internal surrogate |
| `session_token` | `VARCHAR(128)` | No | — | Cryptographically random opaque token |
| `user_id` | `BIGINT UNSIGNED` | No | — | → `users.id` |
| `role_snapshot` | `VARCHAR(20)` | No | — | Role at session creation: `super_admin \| couple \| crew` |
| `workspace_snapshot` | `BIGINT UNSIGNED` | Yes | NULL | workspace_id at login. NULL for Super Admin |
| `ip_address` | `VARCHAR(45)` | Yes | NULL | IPv4 or IPv6 address |
| `user_agent` | `VARCHAR(500)` | Yes | NULL | Browser/client string |
| `is_active` | `TINYINT(1)` | No | `1` | 0 = expired or destroyed |
| `created_at` | `DATETIME` | No | `CURRENT_TIMESTAMP` | Login timestamp |
| `last_activity_at` | `DATETIME` | No | `CURRENT_TIMESTAMP` | Updated on every authenticated request |
| `expires_at` | `DATETIME` | No | — | Absolute expiry. Session invalid after this |
| `destroyed_at` | `DATETIME` | Yes | NULL | Set on explicit logout |

---

## Primary Key / Indexes

| Name | Columns | Type |
|------|---------|------|
| `PRIMARY` | `id` | Primary |
| `uq_sessions_token` | `session_token` | Unique |
| `idx_sessions_user_id` | `user_id` | Index |
| `idx_sessions_expires_at` | `expires_at` | Index |

---

## Foreign Keys

| Column | References | On Delete |
|--------|-----------|-----------|
| `user_id` | `users.id` | CASCADE |

---

## Business Rules

- `session_token` is the only value stored in the browser cookie
- `role_snapshot` avoids a DB join on every request (role cannot change mid-session)
- Sessions expire absolutely at `expires_at` or when `is_active = 0`
- Logout sets `destroyed_at` and `is_active = 0`