Chuyển tới nội dung chính

LinguFlow Database Design

Stack: PostgreSQL (production / Docker) · SQLAlchemy 2 async ORM · Alembic migrations
Source of truth for shapes: backend/app/models/
Migrations: backend/alembic/versions/ (head: 0021_exam_blueprints)

Field names below are snake_case (DB / ORM). The API exposes camelCase via Pydantic aliases.

Related pages: Architecture & Database Schema (ERD summary) · Question & Exam Design (authoring / sitting lifecycle) · Exam-Type Feature Flags · AI Service Layer

Assessment v2 is current. Migrations 0017_assessment_v2_schema + 0018_drop_legacy_assessment replaced exam_templates, exam_sessions, answer_records, and the flat v1 questions body columns. ai_generation_jobs was dropped in 0018 (generation is synchronous + ai_provenance now).


1. Big picture​

LinguFlow stores several product domains on one Postgres database:

DomainPurposeCore tables
AccountsIdentity, guests, prefsusers, user_settings
Flashcards / libraryDecks, cards, per-learner SM-2decks, cards, card_progress
SharingVisibility + members (polymorphic)item_members, item_effective_access, item_effective_grantees
Question bankShared stimuli + versioned itemsstimuli, stimulus_versions, questions, question_versions, question_keys, ai_provenance
Exams & attemptsDefinitions, sittings, scoresexams, exam_versions, exam_nodes, exam_slots, attempts, attempt_items, responses, scores, attempt_sections, question_notes
Ops flagsKill switches / structure gatesexam_type_flags, exam_type_blueprints, feature_flags
AI cacheExplain responsesai_explanations
┌──────────────┐
│ users │
└──────┬───────┘
┌────────────────────┼────────────────────┬─────────────────┐
│ │ │ │
▼ ▼ ▼ ▼
┌────────┐ ┌──────────┐ ┌────────────┐ ┌──────────────┐
│ decks │ │ cards │ │ exams │ │user_settings │
└───┬────┘ └────┬─────┘ └──────┬─────┘ └──────────────┘
│ │ │
│ 1:N (ORM cascade) │ │ versions → nodes → slots
└────────────────────┘ │
│ ▼
▼ ┌─────────────┐
┌──────────────┐ │ attempts │
│card_progress │ └──────┬──────┘
└──────────────┘ │ items → responses
│ scores / sections

Bank (shared): stimuli → stimulus_versions
questions → question_versions → question_keys
└─(optional)→ stimulus_versions
exam_slots → questions (identity only)
attempt_items → question_versions (+ optional stimulus_versions)

2. Conventions​

ConventionRule
Primary keysUUID (uuid.uuid4), Postgres UUID type
TimestampsDateTime(timezone=True), UTC; most tables have created_at / updated_at
OwnershipUser content rows carry user_id FK (SET NULL for shared bank/exams, CASCADE for personal rows)
JSON columnsDeclared JSON().with_variant(JSONB, "postgresql") so SQLite tests work; GIN indexes live in migrations only
Visibilityprivate | protected | public on decks, cards, questions, stimuli, exams
Content statusdraft | in_review | published | retired on stimuli, questions, exams
CascadesPrefer ORM cascade="all, delete-orphan" where ownership is exclusive; column ondelete= may differ (see §6)
Soft deleteBank/exam listings use archived_at; attempt resolution does not filter it

3. Tables​

3.1 users​

Identity and lightweight activity metadata.

ColumnTypeNotes
idUUID PK
usernameString, unique, nullableClassic signup
emailString, unique, nullable
password_hashString, nullableAbsent for Google / guest
google_idString, unique, nullableOAuth
is_guestBooleanGuest lifecycle cleanup uses (is_guest, last_active)
is_activeBoolean, default trueDeactivation checked at login / get_current_user (0007)
daily_streakIntegerFlashcard habit counter
last_activetimestamptz, indexedGuest sweep + activity
last_ipString(45), nullableAudit only — not an identity key (NAT)
avatar_urlString, nullableR2 object key (0008)
created_at / updated_attimestamptz

Children (ownership): decks, cards, card_progress, user_settings, attempts (delete cascade).
Bank / exams: user_id SET NULL on user delete so authored content can detach.


3.2 decks​

Named collections of flashcards belonging to one user.

ColumnTypeNotes
idUUID PK
user_idUUID → users.id ON DELETE CASCADE
nameString
descriptionText, nullable
visibilityString, indexedprivate | protected | public (0015)
created_at / updated_attimestamptz

Relationship to cards: ORM Deck.cards uses cascade="all, delete-orphan". Deleting a deck deletes its cards in the application session, even though cards.deck_id is declared ON DELETE SET NULL at the column level.


3.3 cards​

Flashcard content + presentation order. SM-2 state is not on this table — see card_progress.

ColumnTypeNotes
idUUID PK
user_idUUID → users.id ON DELETE CASCADEOwner (required even when unfiled)
deck_idUUID → decks.id ON DELETE SET NULL, nullableNULL = Unfiled
front / backTextMarkdown-capable content
positionIntegerOrder within a deck (study / match / workspace). Independent of SRS
image_urlText, nullable
notesText, nullable
visibilityString, indexed(0015)
created_at / updated_attimestamptz

Behaviors that shape the UI:

  • Unfiled cards (deck_id IS NULL) always get position = 0 on write — no server reorder for Unfiled.
  • Creating a card or changing deck_id appends it (position = max+1 for that deck).
  • Reorder API requires an exact cover of all card IDs in the deck.

3.4 card_progress​

Per-learner SM-2 schedule for a card (0015_item_sharing). Enables studying a shared/public card without writing onto the author's row.

ColumnTypeNotes
user_idUUID → users.id ON DELETE CASCADEPK part
card_idUUID → cards.id ON DELETE CASCADEPK part
srs_intervalIntegerDays until next review
srs_ease_factorFloat, default 2.5Floor 1.3 in the SM-2 service
srs_repetitionsIntegerSuccessful consecutive reviews
srs_next_reviewtimestamptz, indexedDue date
updated_attimestamptz

3.5 Sharing tables (0015)​

Polymorphic membership / projection tables. There is no FK to the item row (ids are plain UUIDs), so 0018 had to purge stale exam_template rows by hand when v1 dropped.

TableRole
item_membersExplicit grantees: (item_type, item_id, user_id) unique
item_effective_accessProjected visibility per item: PK (item_type, item_id) — used by decks/cards
item_effective_granteesProjected member set: PK (item_type, item_id, user_id) — decks/cards

Two access paths:

  • Flashcards (services/access.py): decks/cards may inherit visibility; the effective-* tables are the materialized projection.
  • Assessment v2 (services/assessment/access.py): questions, stimuli, and exams carry their own visibility; members use item_members with types question | stimulus | exam. No inheritance from exam → bank item, so nothing is projected into item_effective_* for those types.

3.6 Stimulus (stimuli / stimulus_versions)​

Shared source material (passage, conversation, photo, chart). Identity + facets on stimuli; immutable DSL body on stimulus_versions.

stimuli

ColumnTypeNotes
idUUID PK
user_idUUID → users, SET NULL
statusString, indexedcontent status
localeString(32), indexedContent language (not UI locale)
tagsJSONB list
visibilityString, indexed
current_version_idUUID, nullableDenormalized; not an FK (avoids cycle)
archived_attimestamptz, indexed, nullableSoft delete
created_at / updated_attimestamptz

stimulus_versions

ColumnTypeNotes
idUUID PK
stimulus_idUUID → stimuli CASCADE
versionIntegerUnique with stimulus_id
schema_versionString(16)DSL schema
titleString(200), nullable
bodyJSONBapp.domain.dsl.StimulusBody
created_byUUID → users, SET NULL
created_attimestamptz

3.7 Question bank (questions / question_versions / question_keys)​

A Question is a shared bank identity. The authored body lives in immutable QuestionVersion rows; the answer key is in QuestionKey so a careless bank SELECT cannot return it.

questions — listing / filter facets only (never open version JSON to browse)

ColumnTypeNotes
idUUID PK
user_idUUID → users, SET NULL
statusString, indexeddraft | in_review | published | retired
originString, indexedhuman | ai | import
response_kindString, indexedDenormalized from body for filter scans
localeString(32), indexed
difficultyString, indexeddefault medium
skillString(40), indexed, nullable
tagsJSONB list
search_textString(500), nullableDenormalized prompt text (0019)
visibilityString, indexed
current_version_idUUID, nullableDenormalized; not an FK
archived_attimestamptz, indexed, nullableSoft delete
created_at / updated_attimestamptz

Browse index: ix_questions_bank_browse on (status, locale, response_kind, created_at).

question_versions

ColumnTypeNotes
idUUID PK
question_idUUID → questions CASCADE
versionIntegerUnique with question_id
schema_versionString(16)
bodyJSONBQuestionBody — never contains the key
stimulus_version_idUUID → stimulus_versions RESTRICT, nullablePassage-set link
order_in_stimulusInteger, nullableStem order within a set
created_byUUID → users, SET NULL
created_attimestamptz

question_keys

ColumnTypeNotes
question_version_idUUID PK → question_versions CASCADE1:1
keyJSONBAnswerKey
scoringJSONBServer-owned scoring config

ai_provenance — how an AI-authored version was produced

ColumnTypeNotes
question_version_idUUID PK → question_versions CASCADE
provider / modelString
prompt_versionString(32), indexed
paramsJSONB
raw_completionText, nullableDebug failed parses
regenerated_from_version_idUUID → question_versions SET NULL
created_attimestamptz

Bank invariants:

  1. Soft-delete only (archived_at) for listings; attempts still resolve archived items.
  2. Keys are versioned and recomputable — v1's "409 freeze after first answer" is gone.
  3. Passage sets share one stimulus_version_id + order_in_stimulus, not a free-text passage_group.

3.8 Exams (exams / exam_versions / exam_nodes / exam_slots)​

exams — stable identity

ColumnTypeNotes
idUUID PK
user_idUUID → users, SET NULL
nameString
exam_typeString, indexedtoeic | ielts | hsk | jlpt | custom
descriptionText, nullable
levelString, nullable
tagsJSONB list
visibilityString, indexed
statusString, indexedcontent status
current_version_idUUID, nullableNull until first publish
archived_attimestamptz, indexed, nullable
blueprintJSONBWorking structure gate for the type
draft_structureJSONB, nullableIncomplete composer draft
draft_saved_attimestamptz, nullable
created_at / updated_attimestamptz

exam_versions — published immutable composition

ColumnTypeNotes
idUUID PK
exam_idUUID → exams CASCADE
versionIntegerUnique with exam_id
total_questionsIntegerDenormalized on publish
time_limit_secondsInteger, nullableWhole-exam limit
scoringJSONBScale tables (TOEIC 10–990, IELTS bands, …)
blueprint / blueprint_metJSONB / BooleanSnapshot + gate result (0021)
published_at / published_bytimestamptz / UUID
created_attimestamptz

exam_nodes — recursive section/part/module tree

ColumnTypeNotes
idUUID PK
exam_version_idUUID → exam_versions CASCADE
parent_idUUID → exam_nodes CASCADE, nullableRoot = null
roleString(32)section | part | module (free-form)
keyString(64), indexed, nullableCatalog key (e.g. part5)
labelString(200), nullable
positionInteger
time_limit_secondsInteger, nullablePer-section clock
configJSONB

exam_slots — placement of a question identity (not a version)

ColumnTypeNotes
idUUID PK
node_idUUID → exam_nodes CASCADE
question_idUUID → questions RESTRICT
positionIntegerUnique with node_id

3.9 Attempts (attempts / attempt_items / responses / scores / attempt_sections / question_notes)​

attempts — one sitting (0020 added practice/pause fields)

ColumnTypeNotes
idUUID PK
user_idUUID → users CASCADE
exam_version_idUUID → exam_versions RESTRICT, nullableNull for bank practice draws
statusString, indexedin_progress | completed | abandoned
modeString, indexedexam | practice
feedback_modeStringnone | immediate
practice_specJSONB, nullable
started_at / finished_attimestamptz
time_limit_secondsInteger, nullable
paused_at / resumed_attimestamptz, nullable
accumulated_secondsIntegerPause-aware elapsed
created_at / updated_attimestamptz

attempt_items — frozen composition; pins versions

ColumnTypeNotes
idUUID PK
attempt_idUUID → attempts CASCADE
positionIntegerUnique with attempt
question_version_idUUID → question_versions RESTRICT
stimulus_version_idUUID → stimulus_versions RESTRICT, nullable
option_orderJSONB, nullableShuffle as sat
node_keyString(64), indexed, nullableSection subscores

responses — learner write + derived grade

ColumnTypeNotes
attempt_item_idUUID PK → attempt_items CASCADE1:1
rawJSONBOnly field the sitter writes
gradedBoolean, nullableNull = not machine-scorable
points / max_pointsFloat
detailJSONB, nullablePer-blank / per-pair breakdown
secondsInteger
answered_attimestamptz

scores — one row per scale (replaces a single float on the sitting)

ColumnTypeNotes
attempt_idUUID PK → attempts CASCADE
scaleString(64) PKe.g. percent, section:listening, toeic_total
valueFloat
detailJSONB, nullable
computed_attimestamptz

attempt_sections — frozen section clocks/locks

ColumnTypeNotes
attempt_idUUID PK → attempts CASCADE
node_keyString(64) PK
positionInteger
labelString(200), nullable
time_limit_secondsInteger, nullable
statusStringlocked | active | closed
started_at / closed_attimestamptz, nullable

question_notes — one learner note per question identity (upsert)

ColumnTypeNotes
idUUID PK
user_idUUID → users CASCADE
question_idUUID → questions CASCADEUnique with user
note_textText
created_at / updated_attimestamptz

3.10 Ops / AI support tables​

exam_type_flags (0006) — kill switch per exam type string PK. Missing row ⇒ enabled.

exam_type_blueprints (0021) — admin override of catalog default blueprint. Missing row ⇒ catalog default.

feature_flags (0012) — product-wide switches (e.g. ai). Missing row ⇒ enabled.

ai_explanations (0013) — cached explain output; unique (kind, subject_key, locale).

user_settings (0005) — one row per user; freeform settings JSON.

Known settings keys (application-level):

KeyMeaning
locale"vi" | "en"
theme"light" | "dark" | "simple"
crtEnabledbool (legacy; scanlines follow theme)
dailyXpGoalint
audioSfxEnabledbool
notificationsEnabledbool
speechLang"en-US" | "vi-VN" | "zh-CN" | "ja-JP"

4. Relationship summary​

FromToCardinalityDelete behaviour (effective)
UserDeck / Card / CardProgress / Settings / Attempt1:NCascade delete
UserQuestion / Stimulus / Exam1:N optionaluser_id SET NULL
DeckCard1:N optionalORM cascade deletes cards (column SET NULL overridden)
StimulusStimulusVersion1:NCascade versions
QuestionQuestionVersion1:NCascade versions
QuestionVersionQuestionKey1:0..1Cascade key
QuestionVersionStimulusVersionN:0..1RESTRICT (can't drop a pinned stimulus version)
ExamExamVersion1:NCascade
ExamVersionExamNode1:NCascade tree
ExamNodeExamNode1:NSelf-parent cascade
ExamNodeExamSlot1:NCascade links only
ExamSlotQuestionN:1RESTRICT
ExamVersionAttempt1:NRESTRICT on version
AttemptAttemptItem / Response / Score / AttemptSection1:NCascade
AttemptItemQuestionVersionN:1RESTRICT

5. Domain invariants (assessment v2)​

  1. Shared bank. Composition is exam_slots → question identity. Bodies live only on versions.
  2. Attempts pin versions. attempt_items.question_version_id (and optional stimulus_version_id) freeze what the sitter saw. Editing the bank cannot rewrite finished results.
  3. Soft-delete questions/exams/stimuli via archived_at so history survives.
  4. Keys are separate and recomputable. responses.raw is authoritative; graded / points may be recomputed after a key fix.
  5. Scores are multi-scale. Do not reintroduce a single float score column on attempts.
  6. Blueprints. Working copy on exams.blueprint; publish freezes onto exam_versions with blueprint_met. Per-type admin overrides in exam_type_blueprints (missing = catalog default).
  7. Exam-type kill switch. Missing exam_type_flags row means enabled; starting an attempt for a disabled type is refused (403). In-progress/completed attempts keep working.

6. Cascade pitfalls (cards / decks)​

cards.deck_id → ON DELETE SET NULL (column)
Deck.cards → cascade="all, delete-orphan" (ORM)

db.delete(deck) runs the ORM cascade → cards are deleted.
A raw SQL DELETE FROM decks without ORM might SET NULL instead. Prefer the service/ORM path.


7. Migrations (history)​

RevNameWhat it introduced
0001initial schemausers, decks, cards, early exam shape
0002card position / image / notespresentation fields on cards
0003question bankshared v1 questions, join table, soft delete, seed keys
0004guest lifecycleguest fields / cleanup support on users
0005user settingsuser_settings
0006exam type flagsexam_type_flags
0007user is_activedeactivation flag
0008user avatar_urlavatar R2 key
0009–0011exam draft / listening / answer_keyv1-era exam & question columns (later replaced)
0012feature flagsfeature_flags
0013ai explanationsai_explanations
0014ai generation jobsai_generation_jobs (dropped in 0018)
0015item sharingvisibility, sharing tables, card_progress; SRS leaves cards
0016question bank list indexv1 list index
0017assessment v2 schemastimuli/questions/exams/attempts v2 (+ rename legacy)
0018drop legacy assessmentdrop v1 tables + ai_generation_jobs
0019question search textquestions.search_text
0020attempt runtimepractice/pause, attempt_sections, question_notes
0021exam blueprintsblueprint columns + exam_type_blueprints

Apply with:

cd backend
alembic upgrade head

On SQLite local boot, prefer python run_local.py (create_all + alembic stamp head) — do not run alembic upgrade head against SQLite.


8. Indexes (high-signal)​

Besides PKs/uniques:

  • Users: username, email, google_id, last_active
  • Cards: user_id, deck_id, visibility
  • Card progress: card_id, srs_next_review
  • Questions: facet columns + ix_questions_bank_browse (status, locale, response_kind, created_at); archived_at
  • Question versions: (stimulus_version_id, order_in_stimulus)
  • Exams / attempts: exam_type, visibility, status, mode, user_id
  • Exam nodes: (parent_id, position); slots unique (node_id, position)
  • Attempt items: unique (attempt_id, position)
  • Settings: unique user_id
  • AI provenance: prompt_version

9. What is not in the database​

ConceptWhere it lives
JWT auth tokensClient storage / signed JWT
Unfiled card countAggregated via card query (deck_id IS NULL)
Live exam timer deadlineDerived from timestamps + time_limit_seconds / section clocks
Flat passage / passage_group / audio_url on questionsRemoved with v1; use stimulus + DSL body
ai_generation_jobsDropped in 0018; provenance is ai_provenance
Media blobsCloudflare R2; URLs/keys in card image_url or DSL media blocks
Exam structure catalog defaultsCode (exam_structure_catalog / blueprint helpers), overridden by exam_type_blueprints

ConcernLocation
ORM modelsbackend/app/models/ (assessment.py for bank/exams/attempts)
Alembicbackend/alembic/versions/
Bank servicesbackend/app/services/assessment/bank.py
Exam authoring / publishbackend/app/services/assessment/exams.py
Sitting / gradingbackend/app/services/assessment/attempts.py, scoring.py
Sharing / visibilitybackend/app/services/access.py, models/access.py
DSL shapesbackend/app/domain/dsl/
Pydantic I/O (camelCase)backend/app/schemas/assessment.py
Card SM-2backend/app/services/sm2_service.py + card_progress