Arhitektura podataka

Sažetak

Jedan relacijski izvor istine — Cloud SQL PostgreSQL 16 — drži klinički sloj, uz objektnu pohranu za snimke i dokumente te fizički odvojenu bazu za istraživanje. Identifikacijski podaci šifriraju se po pacijentu ključevima omotanima ključem klinike u Cloud KMS-u, a brisanje je osmišljeno i za sigurnosne kopije.

Tehnička specifikacija vodi se na engleskom jeziku; naslovi i sažeci poglavlja prevedeni su na hrvatski.

Stores

Cloud SQL PostgreSQL 16
Holds
The clinical plane: 220+ tables
Notes
Private IP, row-level security, high availability with point-in-time recovery
Research database
Holds
Datasets, annotations, adjudications, experiments: 20+ tables
Notes
Separate connection string and schema; nothing in the clinical app connects to it
Cloud Storage
Holds
Imaging originals and derivatives, document renditions, model weights, Data Room files, bug reports
Notes
Per-purpose buckets with their own service accounts
Cloud KMS
Holds
Token-signing key, one encryption key per clinic, backup encryption key
Notes
EU key location validated at boot
Secret Manager
Holds
OAuth client secret, password pepper, MFA encryption key, database URL
Notes
Read at start; never in images or logs

Store	Holds	Notes
Cloud SQL PostgreSQL 16	The clinical plane: 220+ tables	Private IP, row-level security, high availability with point-in-time recovery
Research database	Datasets, annotations, adjudications, experiments: 20+ tables	Separate connection string and schema; nothing in the clinical app connects to it
Cloud Storage	Imaging originals and derivatives, document renditions, model weights, Data Room files, bug reports	Per-purpose buckets with their own service accounts
Cloud KMS	Token-signing key, one encryption key per clinic, backup encryption key	EU key location validated at boot
Secret Manager	OAuth client secret, password pepper, MFA encryption key, database URL	Read at start; never in images or logs

There is no NoSQL store and no cache tier holding clinical data. Every asynchronous queue is a Postgres table written in the same transaction as the change it describes (see System topology).

Schema by domain

Identity and access
Representative tables
auth_users, refresh tokens, MFA secrets, native sign-in tokens, users, clinics, roles
Patients
Representative tables
patients, anamnesis, name-search tokens, patient keys, Patient Mode sessions
Clinical spine
Representative tables
clinical_encounters, sessions and segments, clinical_artifacts and transitions, clinical_facts, safety events, assertions
Capture
Representative tables
Perio exams, teeth and sites, voice records, note versions, unbound dictations, listener and capture leases
Copilot and evidence
Representative tables
Clinical responses, claims, claim–evidence links, evidence snapshots and passages, guideline documents
Imaging
Representative tables
Studies, series, instances, assets, jobs, outbox, model releases and deployments, runs, findings, reviews, annotations
Documents
Representative tables
Uploaded documents and value snapshots, attribution, clinical document snapshots, bundles, renditions, deliveries
Planning and operatory
Representative tables
Treatment plans and versions, steps, acceptances, fee schedule, resources, appointments, requirements, waitlist and recall
Platform
Representative tables
Audit trail, AI decision log and provenance, usage and billing ledgers, notifications, integrations queues, compliance records
Data Room
Representative tables
Its own schema file with rooms, documents, members, share packages and a hash-chained activity log

Domain	Representative tables
Identity and access	`auth_users`, refresh tokens, MFA secrets, native sign-in tokens, `users`, clinics, roles
Patients	`patients`, anamnesis, name-search tokens, patient keys, Patient Mode sessions
Clinical spine	`clinical_encounters`, sessions and segments, `clinical_artifacts` and transitions, `clinical_facts`, safety events, assertions
Capture	Perio exams, teeth and sites, voice records, note versions, unbound dictations, listener and capture leases
Copilot and evidence	Clinical responses, claims, claim–evidence links, evidence snapshots and passages, guideline documents
Imaging	Studies, series, instances, assets, jobs, outbox, model releases and deployments, runs, findings, reviews, annotations
Documents	Uploaded documents and value snapshots, attribution, clinical document snapshots, bundles, renditions, deliveries
Planning and operatory	Treatment plans and versions, steps, acceptances, fee schedule, resources, appointments, requirements, waitlist and recall
Platform	Audit trail, AI decision log and provenance, usage and billing ledgers, notifications, integrations queues, compliance records
Data Room	Its own schema file with rooms, documents, members, share packages and a hash-chained activity log

Encryption and keys

Clinic key (Cloud KMS)
one per clinic · EU

Patient data key
wrapped · `patient_keys`
Search keys
per clinic · versioned

Encrypted fields
AES-256-GCM + AAD
Search tokens
keyed hashes

Connections (3)

Clinic key (Cloud KMS) → Patient data key · wraps
Patient data key → Encrypted fields · encrypts
Search keys → Search tokens

Envelope encryption. Patient keys are rows wrapped by the clinic key; the clinic key never leaves Cloud KMS.

Identifying patient fields are encrypted with AES-256-GCM under a per-patient data key. The current format binds each ciphertext to its clinic, patient and field as authenticated additional data, so a value copied to another field, patient or tenant fails to decrypt; an earlier format without that binding is still readable for values written before it.
Lookups by name or identifier use per-clinic keyed hashes (HMAC search tokens), which are versioned and rotatable without re-encrypting the data.
MFA secrets and push tokens are encrypted separately; access tokens are signed by a KMS key whose public half is published as JWKS.
Backups are encrypted with a customer-managed key.

Erasure (GDPR Article 17)

An administrator requests erasure for a patient; role and clinic scope are checked.
In one transaction the patient’s rows are deleted, audit rows are pseudonymised (the fact of the erasure stays auditable), the patient’s wrapped data key is destroyed, and an erasure tombstone is written.
A backup-purge job is queued for retained backups. The worker that walks backup objects and executes those jobs is not built yet; until it is, steps 4 and 5 are what protect backups.
On any restore from backup, the tombstones are replayed before the database serves traffic, so an erased patient cannot be resurrected.
Clinic offboarding destroys the clinic’s key versions in Cloud KMS, which makes every backup of that clinic cryptographically unreadable at once.

Retention and the audit trail

Retention follows the written retention policy; statutory clinical retention takes precedence over deletion where the law requires it.
Locked clinical content is never mutated; corrections are successors, so history survives every edit.
The audit trail is append-only, enforced by database triggers that refuse UPDATE and DELETE, and hash-chained per clinic with a separate system chain. A scheduled job verifies every chain and appends its own verification to it.
Scheduled purges remove what is no longer allowed to exist: expired authentication material, Scribe working data past its window, imaging erasure re-sweeps.

SOURCE OF TRUTH

server/db/schema/
server/research/db/
server/services/kmsKeyManager.ts
server/services/phiCrypto.ts
server/services/backupPurgeQueue.ts
server/services/auditTrailVerifier.ts
docs/runbooks/search-hmac-rotation.md