# Database schema

PostgreSQL 16 with `pgvector`. Image `pgvector/pgvector:pg16`, data in the
`pgdata` volume. Connection pool: 20 max, 30 s idle timeout, 5 s connect
timeout.

Schema is managed in two layers:

1. **Baseline init** — `src/db/database.js`. Idempotent
   `CREATE TABLE IF NOT EXISTS` + `ALTER TABLE ADD COLUMN IF NOT EXISTS`.
   Runs on every boot. Represents everything that predated the migration tool.
2. **Versioned migrations** — `migrations/` via `node-pg-migrate`. All new
   schema changes go here. See `docs/migrations.md`.

## Extensions

```sql
CREATE EXTENSION IF NOT EXISTS vector;
```

## Tables

### `users`

Core accounts. Local-auth + OIDC federation + per-user preferences.

| Column | Type | Notes |
|---|---|---|
| id | SERIAL PK | |
| email | TEXT UNIQUE NOT NULL | |
| password | TEXT NOT NULL | argon2id hash (primary) or bcrypt hash (legacy / rehashed on next login). For OIDC-auto-created users: random hex, not verifiable. |
| name | TEXT | |
| role | TEXT | `user` / `admin` / `moderator` |
| email_verified | BOOLEAN DEFAULT false | |
| verify_token, verify_expires | TEXT, BIGINT | Email verification |
| totp_secret, totp_enabled | TEXT, BOOLEAN DEFAULT false | 2FA |
| totp_backup_codes | TEXT | JSON array of bcrypt hashes of 10-character recovery codes. Consumed atomically on login. |
| oidc_sub | TEXT | IdP subject identifier (when linked) |
| disabled | BOOLEAN DEFAULT false | Soft disable |
| nextcloud_url, nextcloud_user, nextcloud_token, nextcloud_folder | TEXT | WebDAV credentials. `nextcloud_token` stored AES-256-GCM encrypted (prefix `enc1:`). |
| reset_token, reset_expires | TEXT, BIGINT | Password reset |
| stt_model, tts_voice | TEXT | Per-user STT/TTS override |
| webdav_learning_path | TEXT | Learning Hub file-browser root |
| created_at, updated_at | TIMESTAMPTZ DEFAULT NOW() | |

### `user_sessions`

Authoritative session registry.

| Column | Type | Notes |
|---|---|---|
| id | TEXT PK (UUID) | |
| user_id | INTEGER FK users.id | |
| token_hash | TEXT NOT NULL | SHA-256 of the JWT. Index `idx_sessions_token_hash` uses `COLLATE "C"` for ICU-drift immunity. |
| ip_address, user_agent | TEXT | |
| device_label | TEXT | Parsed from UA (`Chrome on Android`, `PedScribe (Android)`, etc.) |
| created_at, last_activity | TIMESTAMPTZ DEFAULT NOW() | `last_activity` only updated on POST/PUT/DELETE/PATCH, throttled to once per 10 min |

### `app_settings`

Key-value runtime config. 2-minute in-memory cache.

| Column | Type | Notes |
|---|---|---|
| key | TEXT PK | Also `COLLATE "C"` |
| value | TEXT | Plain or JSON |
| updated_at | TIMESTAMPTZ DEFAULT NOW() | |
| updated_by | INTEGER FK users.id | |

### `audit_log`

Human-level security and action audit. Writes are batched (1 s flush) by
`src/utils/auditQueue.js`.

| Column | Type | Notes |
|---|---|---|
| id | SERIAL PK | |
| user_id | INTEGER FK users.id | Null for unknown-user attempts |
| action | TEXT NOT NULL | e.g. `login`, `login_failed`, `session_idle_timeout`, `password_changed`, `generate_soap`, `2fa_backup_code_used` |
| category | TEXT DEFAULT 'general' | `auth`, `clinical`, `integration`, `export`, `documents`, `phi_access` |
| details | TEXT | Free-form, PHI-redacted via `src/utils/redact.js` |
| ip_address, user_agent | TEXT | |
| model_used, tokens_used, duration_ms | TEXT, INT, INT | LLM-call fields (optional) |
| status | TEXT DEFAULT 'success' | |
| timestamp | TIMESTAMPTZ DEFAULT NOW() | |

### `api_log`

Per-request AI-call telemetry.

| Column | Type | Notes |
|---|---|---|
| id | SERIAL PK | |
| user_id | INTEGER FK users.id | |
| endpoint | TEXT | Route path |
| method | TEXT | |
| status_code | INTEGER | |
| request_size, response_size | INTEGER | Bytes |
| model_used | TEXT | |
| tokens_input, tokens_output | INTEGER | |
| cost_estimate | NUMERIC | USD estimate (hardcoded rates; OpenRouter uses live pricing) |
| duration_ms | INTEGER | |
| ip_address, error | TEXT | |
| timestamp | TIMESTAMPTZ DEFAULT NOW() | |

### `access_log`

Auth-only event stream.

| Column | Type | Notes |
|---|---|---|
| id | SERIAL PK | |
| user_id | INTEGER FK users.id | |
| action | TEXT | `login`, `logout`, `failed_login`, … |
| ip_address, user_agent | TEXT | |
| success | BOOLEAN | |
| timestamp | TIMESTAMPTZ DEFAULT NOW() | |

### `saved_encounters`

Draft/complete encounter workspace. Auto-expires (default 7 d,
`site.auto_delete_days`).

| Column | Type | Notes |
|---|---|---|
| id | SERIAL PK | |
| user_id | INTEGER FK users.id ON DELETE CASCADE | |
| label | TEXT NOT NULL DEFAULT 'Untitled' | Unique-per-user within active rows |
| enc_type | TEXT NOT NULL DEFAULT 'encounter' | `encounter`, `dictation`, `soap`, `sickvisit`, `wellvisit`, `hospital`, `chart`, `milestones` |
| transcript | TEXT | |
| generated_note | TEXT | |
| partial_data | TEXT | JSON of in-progress form state |
| status | TEXT DEFAULT 'active' | |
| version | INTEGER NOT NULL DEFAULT 1 | Optimistic lock. POST with `expected_version` mismatch returns 409. |
| idempotency_key | TEXT | Prevents duplicate creates from double-submit |
| created_at, updated_at | TIMESTAMPTZ DEFAULT NOW() | |
| expires_at | TIMESTAMPTZ | Default `NOW() + 7 days` |

### `user_memories`

Per-user template and preference rows. Only selected categories are injected
into AI generation through `/api/memories/context`; `custom` rows are stored
for the user but not included in prompt context.

| Column | Type | Notes |
|---|---|---|
| id | SERIAL PK | |
| user_id | INTEGER FK users.id ON DELETE CASCADE | |
| category | TEXT NOT NULL DEFAULT 'custom' | Valid categories: `physical_exam`, `ros`, `encounter_format`, `family_history`, `assessment_plan`, `custom`, `template_soap`, `template_hpi`, `template_wellvisit`, `template_sickvisit`, `template_ed`. Legacy `correction_*` rows may exist but are filtered out. |
| name | TEXT NOT NULL | Encrypted with `enc1:` for new rows |
| content | TEXT NOT NULL | Encrypted with `enc1:` for new rows |
| created_at, updated_at | TIMESTAMPTZ DEFAULT NOW() | |

### `audio_backups`

Optional 24-hour encrypted recovery store for recordings when transcription
fails, so users can retry without re-recording.

| Column | Type | Notes |
|---|---|---|
| id | SERIAL PK | |
| user_id | INTEGER FK users.id | |
| module | TEXT | `encounter`, `dictation`, etc. |
| mime_type | TEXT | |
| size_bytes, compressed_bytes | INTEGER | |
| audio_data | BYTEA | Gzip → AES-256-GCM (0x01 version prefix). Legacy rows (prefix `0x1F` = raw gzip) pass through. |
| created_at, expires_at | TIMESTAMPTZ | 24 h default |

### `user_documents`

Metadata for files in S3-compatible object storage. File bytes stay in S3.

| Column | Type | Notes |
|---|---|---|
| id | SERIAL PK | |
| user_id | INTEGER FK users.id | |
| s3_key | TEXT | Object storage key (prefixed with user id) |
| filename, mime_type | TEXT | |
| size_bytes | INTEGER | |
| description | TEXT | |
| created_at | TIMESTAMPTZ DEFAULT NOW() | |

### `learning_categories`, `learning_content`, `learning_questions`, `learning_options`, `learning_progress`

Learning Hub CMS tables. `learning_content.embedding` is `VECTOR(768)` for
semantic search (pgvector IVFFLAT index). See `docs/learning-hub.md`.

### `developmental_milestones`

AAP-aligned pediatric milestone reference data. Age group + domain keyed.

| Column | Type | Notes |
|---|---|---|
| id | SERIAL PK | |
| age_group | TEXT | `2 months`, `4 months`, `1 year`, … |
| domain | TEXT | `motor`, `language`, `social`, `cognitive` |
| milestone_text | TEXT | |
| sort_order | INTEGER | |
| created_at, updated_at | TIMESTAMPTZ DEFAULT NOW() | |

### `pgmigrations`

Created and managed by `node-pg-migrate`. Records applied migration filenames
+ run time. Never edit by hand.

## Indexes

Core btree indexes — see `database.js` for the full list.

- `users(email)` — **`COLLATE "C"`** (lookup-critical auth path)
- `user_sessions(token_hash)` — **`COLLATE "C"`**
- `audit_log(user_id)`, `audit_log(timestamp)`, `audit_log(action)`, `audit_log(category)`
- `api_log(user_id)`, `api_log(timestamp)`, `api_log(endpoint)`
- `access_log(user_id)`, `access_log(timestamp)`
- `saved_encounters(user_id)`, `saved_encounters(expires_at)`, `saved_encounters(idempotency_key)`
- `user_memories(user_id, category)`
- `audio_backups(user_id)`, `audio_backups(expires_at)`
- `user_documents(user_id)`
- `learning_content(category_id)`
- `learning_progress(user_id, content_id)`
- `developmental_milestones(age_group, domain)`

The `COLLATE "C"` indexes are immune to ICU library version changes between
Postgres image upgrades — silent index corruption from libc / ICU drift
cannot affect auth lookups.

## Collation drift handling

On startup, `src/db/database.js` compares `pg_database.datcollversion` with
`pg_database_collation_actual_version()`. On mismatch it runs
`REINDEX DATABASE` + `ALTER DATABASE … REFRESH COLLATION VERSION` and logs
the event. `npm run maint:reindex` runs the same operation manually.

## Auto-cleanup

Hourly job (plus 10 s after startup):

```sql
DELETE FROM saved_encounters WHERE expires_at < NOW();
DELETE FROM audio_backups    WHERE expires_at < NOW();
DELETE FROM user_sessions    WHERE last_activity < NOW() - INTERVAL '30 days';
```

(The session cleanup is optional safety — the idle middleware deletes stale
rows eagerly.)

## PHI at rest

| Column | Protection |
|---|---|
| `users.nextcloud_token` | AES-256-GCM via `src/utils/crypto.js`, prefix `enc1:` |
| `audio_backups.audio_data` | Gzip → AES-256-GCM, 0x01 version prefix |
| `audit_log.details` | Redacted (SSN, phone, email, DoB regex; 500-char cap; note-body heuristic truncation) |
| Error responses | Generic `'Request failed'` on 500s; full detail stays in `logger.error` / Loki |