pediatric-ai-scribe-v3/docs/database.md

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

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 clinical-style hints injected into AI prompts.

Column	Type	Notes
id	SERIAL PK
user_id	INTEGER FK users.id ON DELETE CASCADE
category	TEXT NOT NULL DEFAULT 'custom'	physical_exam, ros, encounter_format, custom, template_*, correction_*
name	TEXT NOT NULL
content	TEXT NOT NULL
created_at, updated_at	TIMESTAMPTZ DEFAULT NOW()

audio_backups

Retry store for failed-transcription audio.

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):

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