ποΈ Architecture & Database Schema
This document details the database model architecture, entity relationships, migration workflows, and schema design for LinguFlow's PostgreSQL database.
Full column-level reference: database-design.md (assessment v2, flashcards, sharing).
Authoring / sitting lifecycle: Question & Exam Design.
π Entity Relationship Diagram (ERD)β
Note: Assessment v2 (
0017/0018) replacedexam_templates,exam_sessions, andanswer_records. SM-2 leftcardsforcard_progressin0015(per-learner schedules on shared cards).EXAM_TYPE_FLAGShas no FK toexamsβ it is keyed by theexam_typestring ("toeic","hsk", β¦). A missing row means enabled. See Exam-Type Feature Flags and Question & Exam Design.
ποΈ Database Tables Specificationsβ
1. users Tableβ
Stores user accounts (standard email/password, Google OAuth2, and temporary guest accounts).
id:UUID(Primary Key)email/username: unique, indexed, nullablepassword_hash: nullable for Google / guest accountsgoogle_id: unique, nullableis_guest:BOOLEAN(defaultfalse)is_active:BOOLEAN(defaulttrue; deactivation checked at login β0007)daily_streak,last_active,last_ip: activity / guest cleanupavatar_url: R2 object key (0008)created_at/updated_at:TIMESTAMPTZ
2. decks Tableβ
Groups flashcards into custom study decks.
id:UUID(Primary Key)user_id:UUIDβusers.idON DELETE CASCADEname: requireddescription: optionalvisibility:private|protected|public(0015)- Aggregation:
card_countis calculated via join oncards.deck_id(not a stored column). - Deleting a deck ORM-cascades to its cards even though
cards.deck_idisON DELETE SET NULLat the column level.
3. cards Tableβ
Flashcard content and presentation order. SM-2 state is not stored here.
id:UUID(Primary Key)user_id:UUIDβusers.idON DELETE CASCADEdeck_id:UUIDβdecks.idON DELETE SET NULL (nullable = Unfiled)front/back: prompt and answer textposition: order within a deck (independent of SRS)image_url/notes: optionalvisibility:private|protected|public(0015)
3b. card_progress Tableβ
Per-learner SuperMemo-2 schedule (0015_item_sharing). Enables studying a shared/public card without writing onto the author's row.
- PK:
(user_id, card_id)βusers/cardsON DELETE CASCADE srs_interval,srs_ease_factor(default2.5, floor1.3in the service),srs_repetitionssrs_next_review: indexed due dateupdated_at
4. Assessment v2 tables (exams, questions, attempts, β¦)β
Introduced in 0017_assessment_v2_schema; legacy v1 tables (and
ai_generation_jobs) dropped in 0018_drop_legacy_assessment. Denormalized bank
search column in 0019_question_search_text. Runtime columns (mode, pause,
attempt_sections, question_notes) in 0020_attempt_runtime. Blueprint columns
and exam_type_blueprints in 0021_exam_blueprints. Full design:
Question & Exam Design Β· column lists in
database-design.md. Models: backend/app/models/assessment.py.
| Table | Role |
|---|---|
stimuli / stimulus_versions | Shared source material; version content in body (DSL) |
questions / question_versions / question_keys | Bank identity, immutable body, answer key (separate table) |
ai_provenance | Prompt/model metadata for AI-authored question versions |
exams / exam_versions | Exam identity + published immutable composition |
exam_nodes / exam_slots | Recursive section/part tree; slots point at question identity |
attempts / attempt_items / responses | Sitting; pins question/stimulus versions (exam_version_id nullable for bank practice) |
scores | One row per scale (percent, section, TOEIC total, β¦) |
attempt_sections | Frozen section locks/clocks per sitting |
question_notes | One learner note per (user, question) |
exam_type_blueprints | Optional admin override of the per-type structure gate |
Question bank browse columns on questions (listing never opens version JSON):
status (draft | in_review | published | retired), origin
(human | ai | import), response_kind, locale, difficulty, skill,
tags (JSONB), search_text (from 0019), visibility, archived_at.
GET /api/questions/facets returns only the vocabularies actually present:
response kinds, difficulties, locales, skills, tags. Bodies and media are not
flat columns on questions β they live in question_versions.body /
stimulus_versions.body (DSL; blocks are nested inside that JSON). Soft-delete
via archived_at; attempts still resolve archived items.
5. exam_type_flags Tableβ
Backend-owned enable/disable switch per exam type β content-readiness gating and an
ops kill switch, added in migration 0006_exam_type_flags. See
Exam-Type Feature Flags for the full design.
exam_type:VARCHAR(Primary Key β"toeic","ielts","hsk","jlpt", ...)enabled:BOOLEAN(Defaulttrue)label:VARCHAR(Display name, e.g."TOEIC")updated_at:TIMESTAMPTZ- A missing row (e.g. for
"custom", which is never represented here) means the type is enabled β absence is not the same as an explicit disable.
6. feature_flags Tableβ
Product-wide kill switches (starting with ai), added in 0012_feature_flags.
See AI Service Layer.
key:VARCHAR(Primary Key β e.g."ai")enabled:BOOLEAN(Defaulttrue)updated_at:TIMESTAMPTZ- A missing row means the feature is enabled.
7. ai_explanations Tableβ
Cache for explain output (0013_ai_explanations). Unique (kind, subject_key, locale).
kind:cardor attempt-item explain subjects (see assessment AI router)subject_key: card UUID, or attempt/item-scoped keycontent,provider,created_at
8. Sharing tables (0015)β
item_members: explicit grantees(item_type, item_id, user_id)β used by decks/cards and assessment (question|stimulus|exam)item_effective_access/item_effective_grantees: materialized projection for decks/cards only (assessment v2 reads visibility off the row; no examβbank inheritance)
ai_generation_jobsis gone. Created in0014, dropped in0018with the legacy assessment tables. AI draft provenance lives onai_provenancekeyed byquestion_version_id.
π οΈ Alembic Database Migration Workflowβ
Alembic handles version control and schema migrations for PostgreSQL.
Current head: 0021_exam_blueprints.
# Generate a new migration script automatically from SQLAlchemy models
alembic revision --autogenerate -m "Add new table"
# Upgrade database to latest schema version
alembic upgrade head
# Downgrade database by 1 migration step
alembic downgrade -1
On SQLite local boot, prefer python run_local.py (create_all + alembic stamp head) β do not run alembic upgrade head against SQLite.