Database Schema: Sessions, Teams, Attempts, and Defenses

game.db is a SQLite database initialized by init_db() in database.py. It stores the complete game state for every active classroom session: session codes, team membership and progress, every code submission, hint consumption, MLflow mission run results, and final defense forms. The file is created on first launch inside the DATA_DIR directory (configurable via the MLFLOW_JEDI_DATA_DIR environment variable, defaulting to data/ relative to the app root).

Tables overview

init_db() creates six tables with CREATE TABLE IF NOT EXISTS so the schema is safe to call on every startup:

sessions — top-level classroom sessions identified by a human-readable code such as JEDI-4821
teams — student groups that join a session and progress through phases
attempts — every code submission from every team, valid or not
hint_usage — individual hint unlocks with a unique constraint that prevents double-charging
mission_runs — the MLflow run result for each of the three missions per team
defenses — the structured final defense form submitted at the end of the game

All child tables reference sessions or teams with ON DELETE CASCADE so deleting a session cleans up all downstream data automatically.

sessions

CREATE TABLE IF NOT EXISTS sessions (
    id              TEXT PRIMARY KEY,
    code            TEXT UNIQUE NOT NULL,
    created_at      TEXT NOT NULL,
    status          TEXT NOT NULL DEFAULT 'open',
    global_hint     TEXT NOT NULL DEFAULT '',
    scoreboard_visible INTEGER NOT NULL DEFAULT 0,
    question_ids_json  TEXT NOT NULL DEFAULT '[]'
);

Column	Type	Notes
`id`	TEXT	UUID v4 primary key
`code`	TEXT	Human-readable join code, e.g. `JEDI-4821`; unique across all sessions
`created_at`	TEXT	ISO 8601 timestamp in UTC
`status`	TEXT	`open` (teams can join) or `closed` (locked by teacher)
`global_hint`	TEXT	Optional hint broadcast to all teams by the teacher (max 500 chars)
`scoreboard_visible`	INTEGER	`0` = hidden, `1` = scoreboard shown to students
`question_ids_json`	TEXT	JSON array of 6 question IDs chosen for this session

teams

CREATE TABLE IF NOT EXISTS teams (
    id              TEXT PRIMARY KEY,
    session_id      TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
    name            TEXT NOT NULL,
    joined_at       TEXT NOT NULL,
    chamber_index   INTEGER NOT NULL DEFAULT 0,
    phase           TEXT NOT NULL DEFAULT 'chambers',
    score           INTEGER NOT NULL DEFAULT 0,
    hints_used      INTEGER NOT NULL DEFAULT 0,
    holocron_id     TEXT,
    UNIQUE(session_id, name)
);

Column	Type	Notes
`id`	TEXT	UUID v4 primary key
`session_id`	TEXT	Foreign key to `sessions`
`name`	TEXT	Team name chosen from the predefined Star Wars roster
`joined_at`	TEXT	ISO 8601 join timestamp; used to calculate holocron assignment order
`chamber_index`	INTEGER	Current chamber (0–6); 6 means all chambers are complete
`phase`	TEXT	State-machine phase: `chambers` → `missions` → `comparator` → `defense` → `completed`
`score`	INTEGER	Running total; modified by every scoring event
`hints_used`	INTEGER	Cumulative hint count across all challenges
`holocron_id`	TEXT	Assigned debate scenario (nullable until all 3 missions complete)

The UNIQUE(session_id, name) constraint ensures a team name can only be registered once per session. A second join_team() call with the same name returns the existing row rather than creating a duplicate.

attempts

CREATE TABLE IF NOT EXISTS attempts (
    id              INTEGER PRIMARY KEY AUTOINCREMENT,
    team_id         TEXT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    challenge_id    TEXT NOT NULL,
    submitted_code  TEXT NOT NULL,
    valid           INTEGER NOT NULL,
    feedback        TEXT NOT NULL,
    created_at      TEXT NOT NULL
);

Column	Type	Notes
`id`	INTEGER	Auto-incrementing surrogate key
`team_id`	TEXT	Foreign key to `teams`
`challenge_id`	TEXT	Chamber ID (e.g. `experiment`) or mission ID (e.g. `tatooine`)
`submitted_code`	TEXT	Student submission, truncated to 12,000 characters before storage
`valid`	INTEGER	`1` = validation passed, `0` = validation failed
`feedback`	TEXT	Validator message returned to the student, truncated to 1,000 characters
`created_at`	TEXT	ISO 8601 submission timestamp in UTC

Every submission — whether it passes or fails — is recorded. record_attempt() returns the count of failed attempts for the given (team_id, challenge_id) pair, which the app uses to trigger forced advance after 5 failures.

hint_usage

CREATE TABLE IF NOT EXISTS hint_usage (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    team_id       TEXT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    challenge_id  TEXT NOT NULL,
    level         INTEGER NOT NULL,
    used_at       TEXT NOT NULL,
    UNIQUE(team_id, challenge_id, level)
);

Column	Type	Notes
`id`	INTEGER	Auto-incrementing surrogate key
`team_id`	TEXT	Foreign key to `teams`
`challenge_id`	TEXT	The chamber or mission the hint belongs to
`level`	INTEGER	Hint level 1, 2, or 3 (progressively more specific)
`used_at`	TEXT	ISO 8601 unlock timestamp

The UNIQUE(team_id, challenge_id, level) constraint means INSERT OR IGNORE is idempotent — requesting the same hint twice does not deduct a second −5 points.

mission_runs

CREATE TABLE IF NOT EXISTS mission_runs (
    id            INTEGER PRIMARY KEY AUTOINCREMENT,
    team_id       TEXT NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    mission_id    TEXT NOT NULL,
    run_id        TEXT,
    submitted_code TEXT NOT NULL,
    metrics_json  TEXT NOT NULL,
    completed_at  TEXT NOT NULL,
    UNIQUE(team_id, mission_id)
);

Column	Type	Notes
`id`	INTEGER	Auto-incrementing surrogate key
`team_id`	TEXT	Foreign key to `teams`
`mission_id`	TEXT	`tatooine`, `coruscant`, or `mustafar`
`run_id`	TEXT	The MLflow run UUID created by the internal execution template (nullable)
`submitted_code`	TEXT	The validated student code as written
`metrics_json`	TEXT	JSON-serialised result dict returned by `execute_safe_mission()`
`completed_at`	TEXT	ISO 8601 completion timestamp

The UNIQUE(team_id, mission_id) constraint enforces that each mission is completed only once per team. A repeat submission is silently ignored and no additional +30 points are awarded.

defenses

CREATE TABLE IF NOT EXISTS defenses (
    team_id         TEXT PRIMARY KEY REFERENCES teams(id) ON DELETE CASCADE,
    selected_run    TEXT NOT NULL,
    evidence        TEXT NOT NULL,
    artifact        TEXT NOT NULL,
    discarded_run   TEXT NOT NULL,
    discard_reason  TEXT NOT NULL,
    limitation      TEXT NOT NULL,
    recommendation  TEXT NOT NULL,
    sealed_at       TEXT NOT NULL
);

Column	Type	Notes
`team_id`	TEXT	Primary key and foreign key to `teams`; one defense per team
`selected_run`	TEXT	The run the team chose to present
`evidence`	TEXT	Free-text description of the evidence supporting the choice
`artifact`	TEXT	The specific artifact (matrix/report) cited
`discarded_run`	TEXT	The run the team rejected (must differ from `selected_run`)
`discard_reason`	TEXT	Reason for discarding the alternative run
`limitation`	TEXT	Acknowledged limitation of the chosen model
`recommendation`	TEXT	Final deployment or action recommendation
`sealed_at`	TEXT	ISO 8601 submission timestamp

save_defense() uses INSERT OR REPLACE so a team can revise their defense before the session closes. The +25 score bonus is awarded only on the first submission.

Key functions

create_session() → dict

Generates a unique JEDI-XXXX code using secrets.randbelow, inserts a new session row, and returns the full session dict including pre-selected question_ids.

get_session(code: str) → dict | None

Looks up a session by its join code (case-insensitive). Returns None if no matching session exists.

join_team(session_code, team_name) → dict

Validates the session is open, sanitises the team name against the Star Wars roster, and either inserts a new team row or returns the existing one. Raises ValueError if the session is full (MAX_TEAMS = 10).

record_attempt(team_id, challenge_id, code, valid, feedback) → int

Inserts an attempt row (code truncated to 12,000 chars, feedback to 1,000 chars) and returns the current failure count for that (team_id, challenge_id) pair.

use_hint(team_id, challenge_id) → int

Increments hint level (max 3), inserts a hint_usage row with INSERT OR IGNORE, deducts 5 points from the team score, and returns the new level.

complete_chamber(team_id, chamber_index) → dict

Advances chamber_index by 1, adds +20 to score, and transitions phase to missions when chamber_index reaches 6.

save_mission_run(team_id, mission_id, submitted_code, result) → dict

Inserts a mission_runs row (first time only, +30 points), checks if all 3 missions are done (+15 bonus, phase → comparator), then calls assign_holocron().

assign_holocron(team_id, holocron_id) → str | None

Auto-assigns a holocron based on team join-order position modulo 7 once all 3 missions are complete. Returns the holocron_id string or None if conditions are not yet met.

save_defense(team_id, payload) → None

Validates all 7 required fields are non-empty and that selected_run ≠ discarded_run, then upserts the defense row and awards +25 points on first submission, transitioning phase to completed.

export_session(session_id, format) → bytes

Returns a UTF-8 encoded JSON blob (pretty-printed) or a UTF-8-BOM CSV of team summary data. Embeds each team’s mission runs and defense inline.

Every call to connect() enables two SQLite PRAGMAs before any query runs:

PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;

foreign_keys = ON ensures ON DELETE CASCADE is enforced — SQLite disables foreign key checks by default. journal_mode = WAL (Write-Ahead Logging) allows concurrent readers while a write is in progress, which matters when the Streamlit teacher dashboard and multiple student sessions are active simultaneously.

MLflow Concepts

Notebooks

Technical Reference

Tables overview

sessions

teams

attempts

hint_usage

mission_runs

defenses

Key functions

create_session() → dict

get_session(code: str) → dict | None

join_team(session_code, team_name) → dict

record_attempt(team_id, challenge_id, code, valid, feedback) → int

use_hint(team_id, challenge_id) → int

complete_chamber(team_id, chamber_index) → dict

save_mission_run(team_id, mission_id, submitted_code, result) → dict

assign_holocron(team_id, holocron_id) → str | None

save_defense(team_id, payload) → None

export_session(session_id, format) → bytes

Build docs developers (and LLMs) love

MLflow Concepts

Notebooks

Technical Reference

Documentation Index

​Tables overview

​sessions

​teams

​attempts

​hint_usage

​mission_runs

​defenses

​Key functions

create_session() → dict

get_session(code: str) → dict | None

join_team(session_code, team_name) → dict

record_attempt(team_id, challenge_id, code, valid, feedback) → int

use_hint(team_id, challenge_id) → int

complete_chamber(team_id, chamber_index) → dict

save_mission_run(team_id, mission_id, submitted_code, result) → dict

assign_holocron(team_id, holocron_id) → str | None

save_defense(team_id, payload) → None

export_session(session_id, format) → bytes

Build docs developers (and LLMs) love

Tables overview

sessions

teams

attempts

hint_usage

mission_runs

defenses

Key functions