Denis Cercasin

Data Model

Table of contents

Overview

All persistence lives in a single PostgreSQL database accessed through one pg connection pool (database/General.ts). The database was migrated from SQLite to PostgreSQL during this project phase to improve scalability, concurrent access, and type safety (native UUID and JSONB columns).

There is no ORM: the data layer uses parameterized SQL through domain-specific read/write modules (database/account, database/game, database/patient, database/error_definition, database/error, database/blacklist).

ER diagram 

erDiagram
    accounts {
        serial id PK
        uuid user_uuid UK
        uuid auth_uuid UK
        text email UK
        text given_name
        text surname
        text address
        bigint cooldown
        text session_id UK
        bigint session_id_created
        boolean activated
        bigint created
    }

    games {
        serial id PK
        uuid game_uuid UK
        text device_uuid UK "AES-256 encrypted"
        uuid user_uuid FK "nullable - linked instructor"
        text game_code UK "AES-256 encrypted"
        text game_status "LOBBY | SETUP | STARTED | ENDED"
        jsonb game_errors "selected error IDs"
        jsonb game_found_errors
        jsonb game_error_coordinates
        jsonb game_mistake_coordinates
        text game_patient_id "default patient_a"
        text game_patient_model "Unity model key"
        int game_mistake_amount "default 3"
        bigint created
    }

    patients {
        serial id PK
        text patient_id UK "patient_a ... patient_f"
        text unity_model_id
        text full_name
        text short_name
        text date_of_birth
        int age
        text gender
        text nationality
        text body_type
        text skin_color
        text height
        text weight
        text disabilities
        text language "nullable"
        text allergies
        text blood_group
        text face_image_path "nullable"
        text full_body_image_path "nullable"
        text accent_color
        jsonb allowed_error_ids
        int default_error_amount
        boolean active
        bigint created
        bigint updated
    }

    error_definitions {
        serial id PK
        uuid error_uuid UK
        int error_number UK "0-31"
        text error_key UK
        text error_name
        text category
        int severity_weight "CHECK 1-5"
        boolean critical
        jsonb feedback_hints
        boolean active
        bigint created
        bigint updated
    }

    game_error_results {
        serial id PK
        uuid game_uuid FK
        int error_number FK
        boolean selected
        boolean found
        bigint found_at
        bigint created
        bigint updated
    }

    game_error_coordinates {
        serial id PK
        uuid game_uuid FK
        int error_number FK
        jsonb position
        bigint created
        bigint updated
    }

    game_mistakes {
        serial id PK
        uuid game_uuid FK
        jsonb coordinates
        bigint created
    }

    game_evaluations {
        serial id PK
        uuid game_uuid FK "UNIQUE"
        text evaluation_version "rule-based-v1"
        jsonb evaluation
        bigint created
        bigint updated
    }

    token_blacklist {
        serial id PK
        text token UK
        text type "auth | recovery"
        bigint added
    }

    errors {
        serial id PK
        uuid error_uuid UK
        text origin "code location tag"
        text reqest
        bigint created
    }

    accounts ||--o{ games : "user_uuid (logical)"
    games ||--o{ game_error_results : "game_uuid, ON DELETE CASCADE"
    games ||--o{ game_error_coordinates : "game_uuid, ON DELETE CASCADE"
    games ||--o{ game_mistakes : "game_uuid, ON DELETE CASCADE"
    games ||--o| game_evaluations : "game_uuid, ON DELETE CASCADE"
    error_definitions ||--o{ game_error_results : "error_number"
    error_definitions ||--o{ game_error_coordinates : "error_number"
    patients ||--o{ games : "patient_id (logical)"

The relations accounts → games and patients → games are logical references (matching values, no FOREIGN KEY constraint), while the game_* detail tables have real foreign keys with ON DELETE CASCADE. The errors table is an error log, unrelated to the game error catalog in error_definitions.

Tables in detail

accounts

One row per registered instructor (teacher / supervisor). auth_uuid is the internal identity embedded in every JWT — rotating it (account recovery) instantly invalidates all issued tokens. session_id + session_id_created implement the one-time login handover; cooldown throttles repeated login/recovery mails.

games

One row per VR device, reused across sessions (device_uuid UNIQUE). A game is “joined” by setting user_uuid, and reset when a new round starts. The JSONB columns store the current round’s configuration and progress:

Column Content Example
game_errors Error IDs selected for the round [1, 23, 24]
game_found_errors IDs already found [23]
game_error_coordinates Per-error world position reported by Unity ([id, x, y, z]) [[15, 1.2, 0.0, 3.4]]
game_mistake_coordinates Positions of wrong clicks [[0.8, 1.1, 2.0]]
game_patient_id / game_patient_model Selected patient profile and its Unity model key patient_d

patients

The diversity patient profiles — the core content addition of Room of Horror 2.0. Six profiles are seeded at startup (upsert on patient_id), each with demographic attributes, medical master data, an accent color for the frontend, and its list of allowed error IDs:

ID Name Profile Patient-specific errors
patient_a Lina Weiss Child, 3, female 19 (bell too high), 20 (bed too big)
patient_b Alex Marin Young adult, 24, non-binary, penicillin allergy 18, 21 (bed too high), 30 (allergy medication conflict)
patient_c Julia Schneider Woman, 44 22 (too many visitors)
patient_d Fatima Diallo Senior, 80, dementia, Black 23 (sensor mat misplaced), 24/29/31 (blocked doors)
patient_e Daniel Kim Young man, 29, obese (132 kg), amputated right arm, Asian 25 (no heavy-duty bed), 26 (missing address)
patient_f Carlos García 33, Spanish speaker, no German 27 (translator decision)

All patients additionally share the general room errors (0, 3–7, 10–17) and the patient-file/critical errors 1, 2, 8. The mapping is defined in getPatientSpecificErrorIds() (database/General.ts) and mirrored in the frontend (src/utils/Data.ts).

error_definitions

Database copy of the 32-entry error catalog (evaluation/ErrorCatalog.ts), upserted at startup on error_number. Fields: stable error_key (snake_case German), display name, category (hygiene, patientSafety, contamination, organization, sharps, medication, environment, equipment), severity_weight (1–5, enforced by a CHECK constraint), critical flag, and JSONB feedback_hints used by the feedback generator. See the Error Catalog for the full list.

game_error_results / game_error_coordinates / game_mistakes

Normalized per-round detail tables introduced together with the evaluation engine. They mirror the JSONB columns in games (dual representation) with proper foreign keys and unique constraints (UNIQUE (game_uuid, error_number)), enabling relational queries (e.g. “which errors are missed most often across all games”). backfillNormalizedGameState() migrates historical JSONB data into these tables at startup.

game_evaluations

One evaluation per game (game_uuid UNIQUE), written whenever a round ends. Stores the full EvaluationResult JSON (score summary, category scores, missed criticals, generated feedback) plus an evaluation_version tag (rule-based-v1) so future engine versions can coexist with historical results.

token_blacklist / errors

Infrastructure tables: single-use enforcement for auth/recovery tokens, and the persistent API error log.

Migrations

There is no external migration tool. createTables() runs at every startup and is idempotent:

  1. CREATE TABLE IF NOT EXISTS for all tables.
  2. Additive migrations via ALTER TABLE ... ADD COLUMN IF NOT EXISTS (e.g. patients.language, games.game_patient_id).
  3. Data migrations guarded by DO $$ ... $$ blocks (e.g. renaming weight → severity_weight, adding the CHECK constraint only once).
  4. UUID columns converted from TEXT to native UUID (migrateUUIDColumns()), a leftover of the SQLite → PostgreSQL migration.
  5. Seed upserts for error_definitions and patients.
  6. Foreign keys re-created with ON DELETE CASCADE (migrateGameForeignKeys()), then JSONB backfill into the normalized tables.

Because seeding is an upsert, editing a patient or error definition in ErrorCatalog.ts / insertDefaultPatients() and restarting the API updates the database automatically — manual rows with the same keys will be overwritten.

Data lifecycle 

flowchart TD
    A[device-init] -->|first contact| B[games row created<br>status LOBBY]
    B --> C[join-game: user_uuid set<br>status SETUP]
    C --> D[edit-game: patient, errors,<br>mistake budget]
    D --> E[status STARTED:<br>found errors + mistakes reset]
    E --> F[error-found / add-mistake<br>update JSONB + detail tables]
    F --> G[status ENDED]
    G --> H[EvaluationEngine result<br>upserted into game_evaluations]
    G -->|new round| D
  • Game rows are never deleted by the application; a device keeps one row forever and rounds overwrite the progress columns.
  • Deleting a game row cascades to results, coordinates, mistakes, and evaluations.
  • No personal patient data is processed — all patient profiles are fictional (see the legal note in the Requirements Catalog).

Information sources

  • room-of-horror-api/database/General.ts (table definitions, migrations, seeds)
  • room-of-horror-api/database/game/Write.ts, database/patient/Read.ts, evaluation/PersistEvaluation.ts
  • room-of-horror-api/.env.example (connection configuration)

Missing information

  • Index usage beyond the implicit unique indexes is not defined in code; production index tuning (if any) is not available from the provided project.

Suggested improvements

  • Replace startup migrations with a versioned migration tool (e.g. node-pg-migrate) once the schema stabilizes.
  • Add real foreign keys for games.user_uuid → accounts.user_uuid and games.game_patient_id → patients.patient_id.

Last build: 19 Aug 2026, 05:20+00:00

This site uses Just the Docs, a documentation theme for Jekyll.