Data model

Norboten keeps data in four places, and each place holds only what belongs there:

The schema is SCHEMA in api/src/norboten_api/db.py: plain SQL, applied on every start, every statement idempotent (CREATE … IF NOT EXISTS), under a PostgreSQL advisory lock so two API workers starting together do not race. A column that holds data is never renamed or dropped, so an older API image runs against a newer database — which is what makes a rollback safe. The one exception came before any account existed: the password and email columns of the earlier sign-ins, and the login_codes table, went on 2026-09-16. Times are timestamptz in the database and Unix seconds in Python.

PostgreSQL

credentials 1──* tokens            users 1──* attempts
     │                               │
     └──────── user_id ──────────────┤ 1──* ratings (one per topic)
                                     │
                                     ├─ nick shown on play_sessions 1──* play_batches
                                     │
                                     ├──* rated_attempts ──────┐
                                     └──* rated_quiz_sessions ─┴─ closed ─▶ one attempts row

events

user_id is a random 32-character hex id created by a GitHub account's first sign-in. It is the key everywhere and never changes; the nick is a label on top of it and is chosen once.

credentials

Who an account is: a GitHub account, and nothing else. Created by the first sign-in with that GitHub account (POST /auth/github/poll from a terminal, POST /auth/github/exchange on the site). There is no password and no email column (both dropped 2026-09-16), and no GitHub token is ever stored.

tokens

Opaque bearer tokens. The client keeps the token; the server keeps only its SHA-256.

Index: tokens_user_idx (user_id) — "where am I signed in" and revoking all.

pending_sign_ins

A sign-in or a Discord link that has started and not finished (norboten_api/pending.py), in the database rather than Redis so that a restart strands nobody mid-sign-in. Every row is used once — it is deleted as it is read — and expires.

Index: pending_sign_ins_expiry_idx (expires_at).

users

The public profile. Created by POST /me once a signed-in account chooses a nick.

attempts

One graded lab attempt or one finished theory run. Written by POST /attempts, and by a rated attempt when it closes (rated_attempts); never updated.

Indexes: (user_id, started_at desc) for a profile, (lab_id, started_at desc) for analytics.

ratings

The current Glicko-2 rating per learner per topic. Upserted after each rated attempt.

Index: ratings_topic_idx (topic) for a topic board. The overall rating is not stored: it is the inverse-variance-weighted mean of the topic ratings, computed when read.

rated_attempts

A rated lab attempt between being issued and being judged (docs/lab-spec.md §13). Created by POST /rated/attempts; closed by the last fact record, by …/abandon, by starting another one, or by the hourly sweep once it expires. Closing writes one attempts row with rated = true.

Indexes: (user_id) WHERE open — one open attempt per learner is looked up on every start — and (expires_at) WHERE open for the sweep. The key is stored because the server verifies with it; it signs this attempt's records and nothing else.

rated_quiz_sessions

A rated theory run (docs/quiz-spec.md §6), in the same five columns as rated_attempts: the run's id as attempt_id, its owner, open, expires_at (an hour after it started) and data — the topic, the questions it will ask in order, each served question with when it was served and what came back, the outcome and the rating change. Closing it writes one attempts row with kind = 'quiz' and rated = true. Indexes as for rated_attempts.

play_sessions

A recorded or live terminal session. Created by POST /play/sessions.

Index: play_sessions_recent_idx (last_frame_at desc).

play_batches

What the recorder flushed every couple of seconds. Deleted with its session.

events

Append-only operational events, as JSON: chat (answered, blocked), progress (anonymous, opt-in), stuck_point (opt-in telemetry). No question is stored: the banks are files in the repository (a questions table from before 2026-09-15 is dropped on start).

Index: events_kind_idx (kind, created_at desc).

Redis

api/src/norboten_api/live.py. No persistence (--save "" --appendonly no), 256 MB with LRU eviction.

Rate limits: chat 20 a minute per client (NORBOTEN_CHAT_PER_MINUTE); starting a sign-in and exchanging a one-time code 10 a minute per client each (NORBOTEN_SIGN_INS_PER_MINUTE); starting a Discord link 10 a minute. The client is the first X-Forwarded-For address Caddy sets.

The learner's machine

~/.norboten, or NORBOTEN_HOME.

Nothing here is sent anywhere unless the learner signs in (graded attempts, rated theory runs) or streams a session.