Data Stores & ERD

Every place the platform stores data

Store	Technology	Holds	Written by	Location
Platform database	SQLite (WAL mode)	Labs, scores, reviews, audit log, lab sessions, alert snapshots, evidence image metadata, rubrics, per-student flags, Manual Check attempts, orientation state, check-failure diagnostics, research exclusions	Provisioning API only	DB_PATH, default ~/cyberrange-data/pod_mgmt.db (cyberrange/src/provisioning/config.py)
Identity database	Keycloak's embedded H2 file database (development mode; no KC_DB configured)	Realm, clients, users, hashed passwords, role mappings, sessions	Keycloak (Admin page through the admin REST API, setup scripts)	Inside the guacamole container under /opt/keycloak (Needs verification on the live host)
Evidence screenshots	Files	Re-encoded PNG/JPEG/WebP images with random names	evidence_images.py	EVIDENCE_IMAGE_DIR, default ~/cyberrange-data/review_images
SIEM data	Wazuh manager files	alerts.json and gzipped daily archives	Wazuh manager	/var/ossec/logs/alerts/ in the wazuh-manager container
Lab working data	Container filesystems	Shell history, Metasploit history, triage files, planted flags	Students and provisioning	Lab containers; deleted with the lab
Configuration and secrets	Env files	API, portal, bridge and Keycloak settings	Operator	~/cyberrange/env/.env, portal/.env.local, bridge-src/.env.bridge (mode 600), /etc/keycloak/admin.env in the gateway container
Release snapshots	tar archives	Previous code tree and portal build for rollback	pull_release.sh	~/cyberrange-data/rollback/

There is no Oracle Database

The relational data lives in SQLite. "Oracle" in this project means Oracle Cloud Infrastructure, the hosting provider. See Oracle Cloud (OCI) Setup.

Entity relationship diagram (SQLite)

Only one foreign key is declared in the schema (milestone_verification.pod_id references pods.pod_id). All other relationships are logical: they are joined in code by matching columns. student_id everywhere is the Keycloak preferred_username.

manual_check_attempts (v10) and the inert booking_account_claims (v13) are left out of the diagram; see the table list below.

Tables

Table	Purpose	Key rules	Main writers / readers
pods	One row per lab slot (1 to 6) with its current status	pod_id is unique. When a slot is reused, provisioning deletes the old row and inserts a new one, so this table holds only the latest lab per slot; history lives in lab_sessions. Partial unique index idx_pods_one_active_per_student allows one non-terminal lab per student.	provisioning, destroy, reaper / every lab route, bridge (through the API), instructor and admin views
milestone_verification	Every scoring attempt with its result and evidence	Scores are never deleted when a lab is destroyed. Unique index ux_milestone_browser_pass prevents duplicate browser PASS rows. Deleted only by the student's own reset. Since v15, source records who wrote the row (auto poller, manual check, browser, flag, instructor review); rows written before v15 stay NULL	scoring.py (insert_verification_row()), browser-score, hybrid_scoring.py, instructor review approval / progress, milestones, instructor views, export
storage_reservations	Disk accounting per lab, used when LXD cannot report free space	Removed at teardown and by the reaper's drift purge	provisioning, finalize_destroyed_pod, reaper
audit_log	Append-only business events	No route edits or deletes rows; admin reads it	db.log_event() everywhere / /admin/audit-log
review_cases	Student reports, manual review requests, scoring conflicts	case_type, status and score have CHECK constraints; SCORING_CONFLICT is staff-only	review routes, hybrid scoring
milestone_rubrics	Name, criteria, points, MITRE technique, NIST phase per milestone	Seeded by v5.sql; (scenario_id, milestone_id) unique	rubrics endpoint, hybrid scoring
pod_milestone_flags	The flag planted for each student and milestone	Unique per student, scenario and milestone	flag_planting.py / flag validation
lab_sessions	Start and end time of every lab	Written in the same transaction as the lab row; closed at teardown	lab_history.py / instructor lab history
review_alert_snapshots	Alerts frozen when a report is submitted	Stores an error message instead of failing when the SIEM is unreachable	lab_history.py / instructor review page
review_evidence_images	Metadata for uploaded screenshots	stored_name unique; files live on disk	evidence_images.py / review pages
manual_check_attempts	The one Manual Check per student, scenario and milestone	Unique per student, scenario and milestone; written only by a student's Manual Check, cleared by their own reset	verify_milestone_route in pods_router.py
user_onboarding	When each user finished the first-login orientation	Keyed by the Keycloak sub claim, not the username; insert-once (ON CONFLICT DO NOTHING)	onboarding_router.py
booking_account_claims	Former booking-account coordination	Inert. Kept only because deployed databases already applied v13; booking no longer creates accounts and the API does not read or write it	none (schema/README.md, docs/BOOKING-ACCOUNT-PROVISIONING.md)
scoring_diagnostics	Instructor-only reason a check failed, one current row per student, lab, scenario and milestone	Holds allowlisted codes only, never artifact contents or shell output. Repeated failures update the row, a PASS replaces it, and the score poller purges rows older than SCORING_DIAGNOSTICS_RETENTION_DAYS (default 14). Written only for scenarios listed in SCORING_DIAGNOSTICS_SCENARIOS	scoring_diagnostics.py, score_poller.py, /internal/browser-diagnostic / GET /instructor/students/{id}/diagnostics
research_exclusions	Accounts an Admin left out of the knowledge-gain export	Keyed by username; reason is test_account, team_member or other. Only filters the export; no scores are deleted	research_exclusions.py, users_router.py / export_knowledge_gain()
schema_version	Applied migration versions	One row per version	migrate.py

Rubric seed contains legacy flags

v5.sql seeds milestone_rubrics.expected_flag with fixed strings in this public repository. They are not accepted for real students: flag validation uses per-student HMAC flags whenever a student ID is known, and the fixed strings are only a fallback for old test fixtures (validate_flag() in cyberrange/src/provisioning/rubrics.py). This documentation intentionally does not reproduce them.

How the application accesses the database

• Only the API opens SQLite. The portal reaches data through the API, and the bridge checks ownership by calling the API.
• Connections set a 30-second timeout and PRAGMA busy_timeout=30000; migrations switch the file to WAL mode once (cyberrange/src/provisioning/db.py, migrate.py).
• All queries use parameter placeholders (?). Dynamic WHERE clauses, such as the audit log filters, are built from fixed fragments with values passed as parameters.
• Provisioning runs its admission checks inside BEGIN IMMEDIATE, so two concurrent requests cannot both take the last slot or give one student two labs; slow LXD calls happen before the write lock (provision_pod() in pods_router.py).

Migrations

Version	Change
v1	Original tables: pods, storage_reservations, audit_log, milestone_verification, schema_version
v2	One live lab per student (partial unique index)
v3	student_id on milestone_verification (column added by migrate.py), backfill and index
v4	review_cases
v5	milestone_rubrics with seed data; index for conflict lookups
v6	Unique browser-PASS index
v7	pod_milestone_flags
v8	lab_sessions, review_alert_snapshots
v9	review_evidence_images
v10	manual_check_attempts (one row per student, scenario and milestone; the one-shot Manual Check lock) and rubric rows for the flag tasks (1, 5) and (6, 5)
v11	user_onboarding (first-login orientation state)
v12	Rewrites the name and criteria of the Scenario 1 Task 5 rubric (personal flag file); flag, points and table shape unchanged
v13	booking_account_claims; now inert but must stay in the migration history
v14	scoring_diagnostics with indexes on student and last-seen time
v15	milestone_verification.source column with a CHECK on the five allowed values
v16	research_exclusions

• The API applies pending migrations at startup (init_db() → migrate.apply()).
• Manual use on the host: python3 migrate.py --check (exit 0 when current) and python3 migrate.py --apply, optionally with --db PATH.
• Policy: additive migrations only in production, no DROP without approval; v3 is forward-only; v13 must not be removed from the history; rolling back the code leaves v14 to v16 inert (cyberrange/src/provisioning/schema/README.md).
• To exclude an account that was already deleted from Keycloak, the schema README gives a manual INSERT INTO research_exclusions ... for the host.
• migrate.py also adds the milestone_verification.pod_created_at column used by knowledge-gain telemetry.

Credentials and configuration

SQLite has no user accounts; access is controlled by file permissions. The manual creates ~/cyberrange-data with mode 700, owned by the operator account that runs the API (manual/README.md). The only setting is DB_PATH.

Growth and housekeeping

• The score poller inserts a milestone_verification row and an audit_log row for every check, including FAIL, every 3 seconds per unpassed milestone of each active lab. There is no retention or purge job for these tables in the repository. Since v15 these rows are tagged source='auto', and the instructor page hides the non-PASS ones by default, but they are still stored. The only purge is for scoring_diagnostics (retention days, by the poller).
• Practical options if the file grows: record only status changes, thin old FAIL rows, archive old audit rows, then run VACUUM during a maintenance window. Needs a team decision.

Backup and recovery

No automated backup in the repository

No backup job exists for pod_mgmt.db, the evidence image folder or Keycloak's data. The release pipeline's rollback/ snapshots cover code, not data.

Suggested procedure (run on the host as the operator):

# 1. SQLite: an online, consistent copy (safe with WAL)
sqlite3 ~/cyberrange-data/pod_mgmt.db ".backup '$HOME/backups/pod_mgmt-$(date +%F).db'"

# 2. Evidence screenshots
tar czf ~/backups/review_images-$(date +%F).tgz -C ~/cyberrange-data review_images

# 3. Keycloak (users, roles, clients): snapshot the gateway container
lxc snapshot guacamole "backup-$(date +%F)"

Recovery: stop the API unit, copy the backup over pod_mgmt.db (and remove any -wal/-shm files left from the old database), start the unit, and run python3 migrate.py --check. Keep backups off the VM as well; the whole platform runs on one host.

Sources

cyberrange/src/provisioning/schema/v1.sql to v16.sql, schema/README.md, migrate.py, db.py, config.py, pods_router.py, provision.py, rubrics.py, evidence_images.py, lab_history.py, scoring.py, scoring_diagnostics.py, onboarding_router.py, research_exclusions.py, users_router.py, docs/BOOKING-ACCOUNT-PROVISIONING.md, cyberrange/deploy/keycloak/keycloak.service, manual/README.md.