# Database Schema (Planned)

This schema is the baseline for moving from config-based administrator login to database-backed administration. These administrator identities are separate from the global consumer accounts stored by the central authentication platform.

## `admins`

Stores administrative users who can access `admin.openhealthmetrics.org`.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | BIGINT UNSIGNED | PK, auto increment | Internal ID |
| `email` | VARCHAR(190) | UNIQUE, NOT NULL | Login identity |
| `password_hash` | VARCHAR(255) | NOT NULL | `password_hash()` output |
| `display_name` | VARCHAR(120) | NOT NULL | Friendly name |
| `role_id` | BIGINT UNSIGNED | FK, NOT NULL | References `roles.id` |
| `is_active` | TINYINT(1) | NOT NULL default `1` | Disable access without deletion |
| `last_login_at` | DATETIME NULL |  | Last successful login |
| `created_at` | DATETIME | NOT NULL |  |
| `updated_at` | DATETIME | NOT NULL |  |

Indexes:

- `UNIQUE(email)`
- `INDEX(role_id, is_active)`

## `roles`

Role catalog for RBAC.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | BIGINT UNSIGNED | PK, auto increment |  |
| `slug` | VARCHAR(80) | UNIQUE, NOT NULL | e.g. `super_admin`, `ops_admin` |
| `name` | VARCHAR(120) | NOT NULL | Display role name |
| `created_at` | DATETIME | NOT NULL |  |
| `updated_at` | DATETIME | NOT NULL |  |

## `role_permissions`

Role-to-permission map.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | BIGINT UNSIGNED | PK, auto increment |  |
| `role_id` | BIGINT UNSIGNED | FK, NOT NULL | References `roles.id` |
| `permission_key` | VARCHAR(120) | NOT NULL | e.g. `releases.publish` |
| `created_at` | DATETIME | NOT NULL |  |

Indexes:

- `UNIQUE(role_id, permission_key)`

## `admin_sessions`

Optional persistent session audit layer.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | BIGINT UNSIGNED | PK, auto increment |  |
| `admin_id` | BIGINT UNSIGNED | FK, NOT NULL | References `admins.id` |
| `session_token_hash` | CHAR(64) | UNIQUE, NOT NULL | Hash of session token |
| `ip_address` | VARCHAR(45) | NULL | IPv4/IPv6 |
| `user_agent` | VARCHAR(500) | NULL | Browser/device details |
| `last_seen_at` | DATETIME | NOT NULL | Activity heartbeat |
| `expires_at` | DATETIME | NOT NULL | Expiration timestamp |
| `created_at` | DATETIME | NOT NULL |  |

Indexes:

- `INDEX(admin_id, expires_at)`

## `audit_logs`

Immutable history of privileged actions.

| Column | Type | Constraints | Notes |
|---|---|---|---|
| `id` | BIGINT UNSIGNED | PK, auto increment |  |
| `admin_id` | BIGINT UNSIGNED | FK, NULL | Null when system action |
| `action` | VARCHAR(120) | NOT NULL | Event type |
| `entity_type` | VARCHAR(120) | NULL | e.g. `mobile_release` |
| `entity_id` | VARCHAR(120) | NULL | Flexible identifier |
| `payload_json` | JSON | NULL | Change context |
| `ip_address` | VARCHAR(45) | NULL | IPv4/IPv6 |
| `created_at` | DATETIME | NOT NULL |  |

Indexes:

- `INDEX(admin_id, created_at)`
- `INDEX(action, created_at)`

## SQL Starter (MySQL 8+)

```sql
CREATE TABLE roles (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  slug VARCHAR(80) NOT NULL UNIQUE,
  name VARCHAR(120) NOT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL
);

CREATE TABLE admins (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  display_name VARCHAR(120) NOT NULL,
  role_id BIGINT UNSIGNED NOT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_admins_role_active (role_id, is_active),
  CONSTRAINT fk_admins_role FOREIGN KEY (role_id) REFERENCES roles(id)
);
```