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

Pediatric AI Scribe - Database Schema

Overview

The application uses PostgreSQL 16 with the pgvector extension enabled for vector similarity search. The database runs in a pgvector/pgvector:pg16 container with a persistent pgdata volume.

Connection Pool

Setting	Value
Max connections	20
Idle timeout	30 seconds
Connection timeout	5 seconds

---

Extensions

CREATE EXTENSION IF NOT EXISTS vector;  -- pgvector for embedding search

---

Tables

users

Core user accounts with authentication, TOTP two-factor, OIDC federation, and per-user preferences.

Column	Type	Notes
id	SERIAL PRIMARY KEY
email	VARCHAR UNIQUE NOT NULL
password	VARCHAR	bcrypt hash; NULL for OIDC-only users
name	VARCHAR	Display name
role	VARCHAR	user, admin
totp_secret	VARCHAR	TOTP shared secret (encrypted)
totp_enabled	BOOLEAN	Whether 2FA is active
email_verified	BOOLEAN
verify_token	VARCHAR	Email verification token
verify_expires	TIMESTAMP	Expiry for verify_token
reset_token	VARCHAR	Password reset token
reset_expires	TIMESTAMP	Expiry for reset_token
oidc_sub	VARCHAR	OpenID Connect subject identifier
disabled	BOOLEAN	Soft-disable account
nextcloud_url	VARCHAR	User's Nextcloud/WebDAV server URL
nextcloud_user	VARCHAR	WebDAV username
nextcloud_pass	VARCHAR	WebDAV password (encrypted)
stt_model	VARCHAR	Preferred speech-to-text model
tts_voice	VARCHAR	Preferred text-to-speech voice
webdav_learning_path	VARCHAR	WebDAV path for learning exports
created_at	TIMESTAMP	DEFAULT NOW()
updated_at	TIMESTAMP	DEFAULT NOW()

---

app_settings

Key-value store for application configuration. Values are cached in memory with a 2-minute TTL to avoid repeated DB reads on every request.

Column	Type	Notes
key	VARCHAR PRIMARY KEY	Setting name
value	TEXT	JSON or plain text value
updated_at	TIMESTAMP	DEFAULT NOW()
updated_by	INTEGER	FK to users.id

---

audit_log

High-level audit trail for security-relevant and AI-related actions.

Column	Type	Notes
id	SERIAL PRIMARY KEY
user_id	INTEGER	FK to users.id
action	VARCHAR	Action name (e.g., generate_note, login)
category	VARCHAR	Grouping category
details	TEXT	Free-form detail string or JSON
ip_address	VARCHAR	Client IP
user_agent	VARCHAR	Client User-Agent header
model_used	VARCHAR	LLM model identifier (if applicable)
tokens_used	INTEGER	Total tokens consumed
duration_ms	INTEGER	Wall-clock time of the operation
status	VARCHAR	success, error, etc.
timestamp	TIMESTAMP	DEFAULT NOW()

---

api_log

Per-request API telemetry with cost tracking.

Column	Type	Notes
id	SERIAL PRIMARY KEY
user_id	INTEGER	FK to users.id
endpoint	VARCHAR	Route path
method	VARCHAR	HTTP method
status_code	INTEGER	Response status
request_size	INTEGER	Request body bytes
response_size	INTEGER	Response body bytes
model_used	VARCHAR	LLM model identifier
tokens_input	INTEGER	Input/prompt tokens
tokens_output	INTEGER	Output/completion tokens
cost_estimate	NUMERIC	Estimated USD cost
duration_ms	INTEGER	Request duration
ip_address	VARCHAR	Client IP
error	TEXT	Error message if status >= 400
timestamp	TIMESTAMP	DEFAULT NOW()

---

access_log

Lightweight authentication event log.

Column	Type	Notes
id	SERIAL PRIMARY KEY
user_id	INTEGER	FK to users.id
action	VARCHAR	login, logout, failed_login, etc.
ip_address	VARCHAR	Client IP
user_agent	VARCHAR	Client User-Agent header
success	BOOLEAN	Whether the action succeeded
timestamp	TIMESTAMP	DEFAULT NOW()

---

saved_encounters

Transcribed clinical encounters with generated notes. Rows auto-expire after 7 days.

Column	Type	Notes
id	SERIAL PRIMARY KEY
user_id	INTEGER	FK to users.id
label	VARCHAR	User-assigned label
enc_type	VARCHAR	Encounter type (e.g., well_child, sick)
transcript	TEXT	Raw transcript text
generated_note	TEXT	AI-generated clinical note
partial_data	JSONB	In-progress form state
status	VARCHAR	draft, complete, etc.
idempotency_key	VARCHAR	Prevents duplicate submissions
created_at	TIMESTAMP	DEFAULT NOW()
updated_at	TIMESTAMP	DEFAULT NOW()
expires_at	TIMESTAMP	DEFAULT NOW() + INTERVAL '7 days'

---

user_memories

Persistent per-user preferences and correction history that the AI uses to personalize output.

Column	Type	Notes
id	SERIAL PRIMARY KEY
user_id	INTEGER	FK to users.id
category	VARCHAR	See categories below
name	VARCHAR	Human-readable label
content	TEXT	Memory content
created_at	TIMESTAMP	DEFAULT NOW()
updated_at	TIMESTAMP	DEFAULT NOW()

Categories:

• physical_exam -- Default physical exam templates
• ros -- Review of systems preferences
• encounter_format -- Note formatting preferences
• custom -- Free-form user preferences
• template_* -- User-defined note templates (prefix pattern)
• correction_* -- Learned corrections from user edits (prefix pattern)

---

audio_backups

Temporary storage for raw audio recordings. Data is gzip-compressed before storage. Rows auto-expire after 24 hours.

Column	Type	Notes
id	SERIAL PRIMARY KEY
user_id	INTEGER	FK to users.id
module	VARCHAR	Source module (e.g., encounter, dictation)
mime_type	VARCHAR	Original audio MIME type
size_bytes	INTEGER	Original uncompressed size
compressed_bytes	INTEGER	Stored compressed size
audio_data	BYTEA	Gzip-compressed audio binary
created_at	TIMESTAMP	DEFAULT NOW()
expires_at	TIMESTAMP	DEFAULT NOW() + INTERVAL '24 hours'

---

user_documents

Metadata for user-uploaded documents stored in S3-compatible object storage.

Column	Type	Notes
id	SERIAL PRIMARY KEY
user_id	INTEGER	FK to users.id
s3_key	VARCHAR	Object storage key
filename	VARCHAR	Original filename
mime_type	VARCHAR	File MIME type
size_bytes	INTEGER	File size
description	TEXT	User-provided description
created_at	TIMESTAMP	DEFAULT NOW()

---

learning_categories

Top-level groupings for educational content.

Column	Type	Notes
id	SERIAL PRIMARY KEY
name	VARCHAR	Category display name
slug	VARCHAR UNIQUE	URL-safe identifier
description	TEXT	Category description
sort_order	INTEGER	Display ordering
created_at	TIMESTAMP	DEFAULT NOW()

---

learning_content

Educational articles and reference material with vector embeddings for semantic search.

Column	Type	Notes
id	SERIAL PRIMARY KEY
title	VARCHAR	Content title
slug	VARCHAR UNIQUE	URL-safe identifier
body	TEXT	Full content body (Markdown or HTML)
category_id	INTEGER	FK to learning_categories.id
subject	VARCHAR	Subject area tag
content_type	VARCHAR	article, reference, case, etc.
published	BOOLEAN	Visibility flag
author_id	INTEGER	FK to users.id
embedding	VECTOR(768)	pgvector embedding for similarity search
created_at	TIMESTAMP	DEFAULT NOW()
updated_at	TIMESTAMP	DEFAULT NOW()

---

learning_questions

Quiz questions attached to learning content.

Column	Type	Notes
id	SERIAL PRIMARY KEY
content_id	INTEGER	FK to learning_content.id ON DELETE CASCADE
question_text	TEXT	The question prompt
question_type	VARCHAR	multiple_choice, true_false, etc.
explanation	TEXT	Post-answer explanation
sort_order	INTEGER	Display ordering within content

---

learning_options

Answer options for quiz questions.

Column	Type	Notes
id	SERIAL PRIMARY KEY
question_id	INTEGER	FK to learning_questions.id ON DELETE CASCADE
option_text	TEXT	Answer text
is_correct	BOOLEAN	Whether this is the correct answer
explanation	TEXT	Option-specific explanation
sort_order	INTEGER	Display ordering within question

---

learning_progress

Tracks user scores on learning content quizzes.

Column	Type	Notes
id	SERIAL PRIMARY KEY
user_id	INTEGER	FK to users.id ON DELETE CASCADE
content_id	INTEGER	FK to learning_content.id ON DELETE CASCADE
score	INTEGER	Number of correct answers
total	INTEGER	Total number of questions
completed_at	TIMESTAMP	DEFAULT NOW()

---

developmental_milestones

Pediatric developmental milestone reference data, organized by age group and domain.

Column	Type	Notes
id	SERIAL PRIMARY KEY
age_group	VARCHAR	e.g., 2 months, 4 months, 6 months
domain	VARCHAR	e.g., motor, language, social, cognitive
milestone_text	TEXT	Description of the milestone
sort_order	INTEGER	Display ordering within age group + domain
created_at	TIMESTAMP	DEFAULT NOW()
updated_at	TIMESTAMP	DEFAULT NOW()

---

Indexes

The schema defines 22 indexes to support query patterns across the application:

#	Table	Index	Columns
1	users	unique	email
2	users	index	oidc_sub
3	users	index	verify_token
4	users	index	reset_token
5	audit_log	index	user_id
6	audit_log	index	timestamp
7	audit_log	index	action
8	audit_log	index	category
9	api_log	index	user_id
10	api_log	index	timestamp
11	api_log	index	endpoint
12	access_log	index	user_id
13	access_log	index	timestamp
14	saved_encounters	index	user_id
15	saved_encounters	index	expires_at
16	saved_encounters	index	idempotency_key
17	user_memories	index	user_id, category
18	audio_backups	index	user_id
19	audio_backups	index	expires_at
20	user_documents	index	user_id
21	learning_content	index	category_id
22	learning_progress	index	user_id, content_id

---

Auto-Cleanup

Expired rows are automatically purged by a scheduled cleanup job:

Target	Expiry Rule	Affected Table
Encounters	expires_at < NOW() (default 7 days after creation)	saved_encounters
Audio backups	expires_at < NOW() (default 24 hours after creation)	audio_backups

Schedule

• The cleanup function runs hourly via setInterval.
• An initial cleanup also runs 10 seconds after server startup to clear any rows that expired while the application was down.

Behavior

The cleanup executes two DELETE statements inside the hourly tick:

DELETE FROM saved_encounters WHERE expires_at < NOW();
DELETE FROM audio_backups WHERE expires_at < NOW();

Deleted row counts are logged at the info level via the Winston logger.