TypeScript 98.3%
CSS 0.9%
Shell 0.7%
1// Schéma SQLite d'Immbot AI — idempotent (CREATE TABLE IF NOT EXISTS).2// Conçu pour rester portable vers PostgreSQL (types simples, pas de trigger exotique).34export const SCHEMA = `5PRAGMA journal_mode = WAL;6PRAGMA foreign_keys = ON;78-- ============================= Utilisateurs et sécurité =============================9CREATE TABLE IF NOT EXISTS users (10 id INTEGER PRIMARY KEY AUTOINCREMENT,11 username TEXT NOT NULL UNIQUE,12 display_name TEXT NOT NULL DEFAULT '',13 email TEXT,14 password_hash TEXT NOT NULL,15 role TEXT NOT NULL DEFAULT 'student' CHECK (role IN ('student','instructor','admin')),16 must_change_password INTEGER NOT NULL DEFAULT 0,17 is_initial_admin INTEGER NOT NULL DEFAULT 0,18 auth_provider TEXT NOT NULL DEFAULT 'local',19 external_id TEXT,20 disabled INTEGER NOT NULL DEFAULT 0,21 created_at TEXT NOT NULL DEFAULT (datetime('now')),22 last_login_at TEXT23);2425CREATE TABLE IF NOT EXISTS sessions (26 token_hash TEXT PRIMARY KEY,27 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,28 created_at TEXT NOT NULL DEFAULT (datetime('now')),29 expires_at TEXT NOT NULL,30 ip TEXT, user_agent TEXT31);32CREATE INDEX IF NOT EXISTS idx_sessions_user ON sessions(user_id);3334CREATE TABLE IF NOT EXISTS auth_events (35 id INTEGER PRIMARY KEY AUTOINCREMENT,36 user_id INTEGER, username TEXT, event TEXT NOT NULL, ip TEXT, detail TEXT,37 created_at TEXT NOT NULL DEFAULT (datetime('now'))38);39CREATE INDEX IF NOT EXISTS idx_auth_events_time ON auth_events(created_at);4041-- ============================= Cours et contenu =============================42CREATE TABLE IF NOT EXISTS courses (43 code TEXT PRIMARY KEY, -- 'IMM1003'44 full_code TEXT NOT NULL, -- 'IMM1003-20'45 title TEXT NOT NULL,46 session_label TEXT NOT NULL,47 description TEXT NOT NULL DEFAULT '',48 color TEXT NOT NULL DEFAULT '#003E7E',49 active INTEGER NOT NULL DEFAULT 1,50 source_path TEXT NOT NULL DEFAULT ''51);5253CREATE TABLE IF NOT EXISTS enrollments (54 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,55 course_code TEXT NOT NULL REFERENCES courses(code) ON DELETE CASCADE,56 created_at TEXT NOT NULL DEFAULT (datetime('now')),57 PRIMARY KEY (user_id, course_code)58);5960CREATE TABLE IF NOT EXISTS documents (61 id INTEGER PRIMARY KEY AUTOINCREMENT,62 course_code TEXT REFERENCES courses(code),63 space TEXT NOT NULL, -- official-imm1003 | official-imm1033 | instructor-private | student-temporary-upload | student-persistent-files64 path TEXT NOT NULL UNIQUE,65 filename TEXT NOT NULL,66 doc_type TEXT NOT NULL, -- slides | plan | exercise | solution | aide-memoire | glossary | markdown | upload | exam67 title TEXT NOT NULL,68 category TEXT NOT NULL DEFAULT '',69 week INTEGER,70 checksum TEXT NOT NULL,71 status TEXT NOT NULL DEFAULT 'ok', -- ok | error | pending72 error TEXT,73 visible_to_students INTEGER NOT NULL DEFAULT 1,74 ingested_at TEXT,75 chunk_count INTEGER NOT NULL DEFAULT 076);77CREATE INDEX IF NOT EXISTS idx_documents_course ON documents(course_code, space);7879CREATE TABLE IF NOT EXISTS chunks (80 id INTEGER PRIMARY KEY AUTOINCREMENT,81 document_id INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,82 course_code TEXT,83 space TEXT NOT NULL,84 seq INTEGER NOT NULL,85 ref_type TEXT NOT NULL, -- slide | section | exercise | glossary | page | sheet86 ref_number INTEGER,87 ref_label TEXT NOT NULL DEFAULT '', -- 'Séance 4 — Diapositive 18'88 section_title TEXT NOT NULL DEFAULT '',89 title TEXT NOT NULL DEFAULT '',90 content TEXT NOT NULL, -- texte indexable (détexifié)91 display_content TEXT NOT NULL DEFAULT '', -- version affichable (markdown + $math$)92 box_types TEXT NOT NULL DEFAULT '', -- 'definition,important,example,formula'93 week INTEGER,94 owner_user_id INTEGER, -- pour les espaces étudiants95 conversation_id INTEGER, -- pour student-temporary-upload96 embedding BLOB97);98CREATE INDEX IF NOT EXISTS idx_chunks_doc ON chunks(document_id);99CREATE INDEX IF NOT EXISTS idx_chunks_space ON chunks(space, course_code);100101CREATE VIRTUAL TABLE IF NOT EXISTS chunks_fts USING fts5(102 title, content,103 tokenize = 'unicode61 remove_diacritics 2'104);105106CREATE TABLE IF NOT EXISTS ingestion_runs (107 id INTEGER PRIMARY KEY AUTOINCREMENT,108 started_at TEXT NOT NULL DEFAULT (datetime('now')),109 finished_at TEXT,110 triggered_by TEXT NOT NULL DEFAULT 'script',111 files_scanned INTEGER NOT NULL DEFAULT 0,112 files_ingested INTEGER NOT NULL DEFAULT 0,113 files_skipped INTEGER NOT NULL DEFAULT 0,114 chunks_created INTEGER NOT NULL DEFAULT 0,115 status TEXT NOT NULL DEFAULT 'running',116 report TEXT NOT NULL DEFAULT ''117);118119-- ============================= Conversations =============================120CREATE TABLE IF NOT EXISTS conversations (121 id INTEGER PRIMARY KEY AUTOINCREMENT,122 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,123 course_code TEXT REFERENCES courses(code),124 title TEXT NOT NULL DEFAULT 'Nouvelle conversation',125 folder TEXT NOT NULL DEFAULT '',126 pinned INTEGER NOT NULL DEFAULT 0,127 archived INTEGER NOT NULL DEFAULT 0,128 mode TEXT NOT NULL DEFAULT 'ask',129 knowledge_mode TEXT NOT NULL DEFAULT 'course-only',130 model TEXT NOT NULL DEFAULT '',131 parent_conversation_id INTEGER,132 branched_from_message_id INTEGER,133 created_at TEXT NOT NULL DEFAULT (datetime('now')),134 updated_at TEXT NOT NULL DEFAULT (datetime('now'))135);136CREATE INDEX IF NOT EXISTS idx_conversations_user ON conversations(user_id, archived, updated_at);137138CREATE TABLE IF NOT EXISTS messages (139 id INTEGER PRIMARY KEY AUTOINCREMENT,140 conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,141 role TEXT NOT NULL CHECK (role IN ('user','assistant','system')),142 content TEXT NOT NULL,143 citations TEXT NOT NULL DEFAULT '[]', -- JSON résolu côté serveur144 attachments TEXT NOT NULL DEFAULT '[]', -- JSON [{id, filename, mime}]145 model TEXT NOT NULL DEFAULT '',146 mode TEXT NOT NULL DEFAULT '',147 knowledge_mode TEXT NOT NULL DEFAULT '',148 tokens_in INTEGER NOT NULL DEFAULT 0,149 tokens_out INTEGER NOT NULL DEFAULT 0,150 cost REAL NOT NULL DEFAULT 0,151 feedback INTEGER NOT NULL DEFAULT 0,152 flagged INTEGER NOT NULL DEFAULT 0,153 saved INTEGER NOT NULL DEFAULT 0,154 created_at TEXT NOT NULL DEFAULT (datetime('now'))155);156CREATE INDEX IF NOT EXISTS idx_messages_conv ON messages(conversation_id, id);157158CREATE TABLE IF NOT EXISTS uploads (159 id INTEGER PRIMARY KEY AUTOINCREMENT,160 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,161 conversation_id INTEGER,162 filename TEXT NOT NULL,163 mime TEXT NOT NULL,164 size INTEGER NOT NULL,165 path TEXT NOT NULL,166 extracted_text TEXT NOT NULL DEFAULT '',167 persistent INTEGER NOT NULL DEFAULT 0,168 created_at TEXT NOT NULL DEFAULT (datetime('now'))169);170171CREATE TABLE IF NOT EXISTS report_flags (172 id INTEGER PRIMARY KEY AUTOINCREMENT,173 message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,174 user_id INTEGER NOT NULL,175 reason TEXT NOT NULL DEFAULT '',176 resolved INTEGER NOT NULL DEFAULT 0,177 created_at TEXT NOT NULL DEFAULT (datetime('now'))178);179180-- ============================= Modèles et usage =============================181CREATE TABLE IF NOT EXISTS model_overrides (182 model_id TEXT PRIMARY KEY,183 enabled INTEGER NOT NULL DEFAULT 1,184 note TEXT NOT NULL DEFAULT '',185 favorite INTEGER NOT NULL DEFAULT 0186);187188CREATE TABLE IF NOT EXISTS usage_log (189 id INTEGER PRIMARY KEY AUTOINCREMENT,190 user_id INTEGER NOT NULL,191 model TEXT NOT NULL,192 kind TEXT NOT NULL DEFAULT 'chat', -- chat | quiz-gen | flashcard-gen | exam-gen | summary | title193 tokens_in INTEGER NOT NULL DEFAULT 0,194 tokens_out INTEGER NOT NULL DEFAULT 0,195 cost REAL NOT NULL DEFAULT 0,196 latency_ms INTEGER NOT NULL DEFAULT 0,197 ok INTEGER NOT NULL DEFAULT 1,198 error TEXT,199 created_at TEXT NOT NULL DEFAULT (datetime('now'))200);201CREATE INDEX IF NOT EXISTS idx_usage_user_time ON usage_log(user_id, created_at);202203-- ============================= Paramètres, prompts, annonces =============================204CREATE TABLE IF NOT EXISTS settings (205 key TEXT PRIMARY KEY,206 value TEXT NOT NULL207);208209CREATE TABLE IF NOT EXISTS prompt_versions (210 id INTEGER PRIMARY KEY AUTOINCREMENT,211 name TEXT NOT NULL, -- ex. 'base-system', 'course-imm1003', 'tutor-mode'212 content TEXT NOT NULL,213 version INTEGER NOT NULL,214 active INTEGER NOT NULL DEFAULT 1,215 created_by TEXT NOT NULL DEFAULT 'seed',216 created_at TEXT NOT NULL DEFAULT (datetime('now'))217);218CREATE INDEX IF NOT EXISTS idx_prompt_name ON prompt_versions(name, active);219220CREATE TABLE IF NOT EXISTS announcements (221 id INTEGER PRIMARY KEY AUTOINCREMENT,222 title TEXT NOT NULL,223 body TEXT NOT NULL,224 course_code TEXT,225 pinned INTEGER NOT NULL DEFAULT 0,226 active INTEGER NOT NULL DEFAULT 1,227 created_by INTEGER,228 created_at TEXT NOT NULL DEFAULT (datetime('now'))229);230231-- ============================= Concepts et maîtrise =============================232CREATE TABLE IF NOT EXISTS concepts (233 id INTEGER PRIMARY KEY AUTOINCREMENT,234 course_code TEXT NOT NULL REFERENCES courses(code),235 slug TEXT NOT NULL,236 name TEXT NOT NULL,237 description TEXT NOT NULL DEFAULT '',238 week INTEGER,239 importance INTEGER NOT NULL DEFAULT 2, -- 1 faible, 2 moyenne, 3 haute240 axis TEXT NOT NULL DEFAULT 'connaissances', -- connaissances|calcul|interpretation|jugement|communication241 UNIQUE (course_code, slug)242);243244CREATE TABLE IF NOT EXISTS concept_links (245 from_id INTEGER NOT NULL REFERENCES concepts(id) ON DELETE CASCADE,246 to_id INTEGER NOT NULL REFERENCES concepts(id) ON DELETE CASCADE,247 type TEXT NOT NULL DEFAULT 'relation', -- prerequis | approfondissement | relation | application248 PRIMARY KEY (from_id, to_id, type)249);250251CREATE TABLE IF NOT EXISTS mastery (252 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,253 concept_id INTEGER NOT NULL REFERENCES concepts(id) ON DELETE CASCADE,254 score REAL NOT NULL DEFAULT 0,255 observations INTEGER NOT NULL DEFAULT 0,256 updated_at TEXT NOT NULL DEFAULT (datetime('now')),257 PRIMARY KEY (user_id, concept_id)258);259260CREATE TABLE IF NOT EXISTS mastery_events (261 id INTEGER PRIMARY KEY AUTOINCREMENT,262 user_id INTEGER NOT NULL,263 concept_id INTEGER NOT NULL,264 kind TEXT NOT NULL, -- flashcard | quiz | exam | correction265 correct INTEGER NOT NULL,266 difficulty INTEGER NOT NULL DEFAULT 3,267 autonomy REAL NOT NULL DEFAULT 1, -- 1 sans aide, <1 avec indices268 confidence INTEGER, -- 1-5 déclaré, null si non demandé269 created_at TEXT NOT NULL DEFAULT (datetime('now'))270);271CREATE INDEX IF NOT EXISTS idx_mastery_events ON mastery_events(user_id, concept_id, created_at);272273-- ============================= Flashcards (SM-2) =============================274CREATE TABLE IF NOT EXISTS flashcards (275 id INTEGER PRIMARY KEY AUTOINCREMENT,276 course_code TEXT NOT NULL REFERENCES courses(code),277 concept_id INTEGER REFERENCES concepts(id),278 type TEXT NOT NULL DEFAULT 'qa', -- qa | definition | formula | error | comparison | calc279 front TEXT NOT NULL,280 back TEXT NOT NULL,281 source_chunk_id INTEGER,282 created_by TEXT NOT NULL DEFAULT 'seed', -- seed | ai | instructor | user283 owner_user_id INTEGER, -- null = partagée (officielle)284 validated INTEGER NOT NULL DEFAULT 0,285 created_at TEXT NOT NULL DEFAULT (datetime('now'))286);287CREATE INDEX IF NOT EXISTS idx_flashcards_course ON flashcards(course_code, concept_id);288289CREATE TABLE IF NOT EXISTS card_states (290 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,291 card_id INTEGER NOT NULL REFERENCES flashcards(id) ON DELETE CASCADE,292 ef REAL NOT NULL DEFAULT 2.5,293 interval_days REAL NOT NULL DEFAULT 0,294 reps INTEGER NOT NULL DEFAULT 0,295 lapses INTEGER NOT NULL DEFAULT 0,296 due_at TEXT NOT NULL DEFAULT (datetime('now')),297 suspended INTEGER NOT NULL DEFAULT 0,298 favorite INTEGER NOT NULL DEFAULT 0,299 last_reviewed_at TEXT,300 PRIMARY KEY (user_id, card_id)301);302CREATE INDEX IF NOT EXISTS idx_card_states_due ON card_states(user_id, suspended, due_at);303304CREATE TABLE IF NOT EXISTS review_log (305 id INTEGER PRIMARY KEY AUTOINCREMENT,306 user_id INTEGER NOT NULL,307 card_id INTEGER NOT NULL,308 q INTEGER NOT NULL, -- qualité 2/3/4/5309 interval_before REAL NOT NULL DEFAULT 0,310 reviewed_at TEXT NOT NULL DEFAULT (datetime('now'))311);312313-- ============================= Quiz =============================314CREATE TABLE IF NOT EXISTS quiz_questions (315 id INTEGER PRIMARY KEY AUTOINCREMENT,316 course_code TEXT NOT NULL REFERENCES courses(code),317 concept_id INTEGER REFERENCES concepts(id),318 type TEXT NOT NULL DEFAULT 'mcq', -- mcq | short | calc | error-detect | case | order | match319 difficulty INTEGER NOT NULL DEFAULT 3, -- 1-5320 question TEXT NOT NULL,321 options TEXT NOT NULL DEFAULT '[]', -- JSON322 answer TEXT NOT NULL,323 explanation TEXT NOT NULL DEFAULT '',324 source_chunk_id INTEGER,325 created_by TEXT NOT NULL DEFAULT 'seed',326 validated INTEGER NOT NULL DEFAULT 0,327 created_at TEXT NOT NULL DEFAULT (datetime('now'))328);329CREATE INDEX IF NOT EXISTS idx_quiz_course ON quiz_questions(course_code, concept_id, difficulty);330331CREATE TABLE IF NOT EXISTS quiz_sessions (332 id INTEGER PRIMARY KEY AUTOINCREMENT,333 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,334 course_code TEXT NOT NULL,335 focus_concept_id INTEGER,336 started_at TEXT NOT NULL DEFAULT (datetime('now')),337 finished_at TEXT,338 n_correct INTEGER NOT NULL DEFAULT 0,339 n_total INTEGER NOT NULL DEFAULT 0340);341342CREATE TABLE IF NOT EXISTS quiz_answers (343 id INTEGER PRIMARY KEY AUTOINCREMENT,344 session_id INTEGER NOT NULL REFERENCES quiz_sessions(id) ON DELETE CASCADE,345 question_id INTEGER NOT NULL,346 user_answer TEXT NOT NULL DEFAULT '',347 correct INTEGER NOT NULL,348 confidence INTEGER,349 hints_used INTEGER NOT NULL DEFAULT 0,350 answered_at TEXT NOT NULL DEFAULT (datetime('now'))351);352353-- ============================= Examens blancs =============================354CREATE TABLE IF NOT EXISTS mock_exams (355 id INTEGER PRIMARY KEY AUTOINCREMENT,356 course_code TEXT NOT NULL REFERENCES courses(code),357 kind TEXT NOT NULL DEFAULT 'intra', -- intra | final | thematic | cumulative358 title TEXT NOT NULL,359 description TEXT NOT NULL DEFAULT '',360 duration_minutes INTEGER NOT NULL DEFAULT 120,361 question_ids TEXT NOT NULL DEFAULT '[]', -- JSON [questionId]362 config TEXT NOT NULL DEFAULT '{}',363 created_by TEXT NOT NULL DEFAULT 'seed',364 official INTEGER NOT NULL DEFAULT 0,365 created_at TEXT NOT NULL DEFAULT (datetime('now'))366);367368CREATE TABLE IF NOT EXISTS exam_attempts (369 id INTEGER PRIMARY KEY AUTOINCREMENT,370 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,371 exam_id INTEGER NOT NULL REFERENCES mock_exams(id) ON DELETE CASCADE,372 mode TEXT NOT NULL DEFAULT 'practice', -- timed | practice373 started_at TEXT NOT NULL DEFAULT (datetime('now')),374 finished_at TEXT,375 answers TEXT NOT NULL DEFAULT '{}', -- JSON {questionId: {answer, correct, confidence}}376 score REAL NOT NULL DEFAULT 0,377 total REAL NOT NULL DEFAULT 0,378 analysis TEXT NOT NULL DEFAULT '{}' -- JSON par compétence/concept379);380381-- ============================= Apprentissage : plans, erreurs, résumés, activité =============================382CREATE TABLE IF NOT EXISTS study_plans (383 id INTEGER PRIMARY KEY AUTOINCREMENT,384 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,385 course_code TEXT NOT NULL,386 exam_date TEXT NOT NULL,387 config TEXT NOT NULL DEFAULT '{}',388 plan TEXT NOT NULL DEFAULT '[]', -- JSON [{date, items:[{kind, conceptId, label, done}]}]389 active INTEGER NOT NULL DEFAULT 1,390 created_at TEXT NOT NULL DEFAULT (datetime('now'))391);392393CREATE TABLE IF NOT EXISTS error_notebook (394 id INTEGER PRIMARY KEY AUTOINCREMENT,395 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,396 course_code TEXT NOT NULL,397 concept_id INTEGER,398 question TEXT NOT NULL,399 given_answer TEXT NOT NULL DEFAULT '',400 correction TEXT NOT NULL DEFAULT '',401 explanation TEXT NOT NULL DEFAULT '',402 source TEXT NOT NULL DEFAULT 'quiz', -- quiz | exam | chat403 status TEXT NOT NULL DEFAULT 'a-revoir', -- comprise | a-revoir | maitrisee404 created_at TEXT NOT NULL DEFAULT (datetime('now')),405 updated_at TEXT NOT NULL DEFAULT (datetime('now'))406);407CREATE INDEX IF NOT EXISTS idx_errors_user ON error_notebook(user_id, status);408409CREATE TABLE IF NOT EXISTS summaries (410 id INTEGER PRIMARY KEY AUTOINCREMENT,411 course_code TEXT NOT NULL,412 scope TEXT NOT NULL DEFAULT 'week', -- week | concept | document | exam-prep413 ref TEXT NOT NULL DEFAULT '',414 title TEXT NOT NULL,415 content TEXT NOT NULL,416 citations TEXT NOT NULL DEFAULT '[]',417 owner_user_id INTEGER, -- null = partagé418 created_by TEXT NOT NULL DEFAULT 'ai',419 created_at TEXT NOT NULL DEFAULT (datetime('now'))420);421422CREATE TABLE IF NOT EXISTS saved_items (423 id INTEGER PRIMARY KEY AUTOINCREMENT,424 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,425 kind TEXT NOT NULL, -- answer | summary | quiz | plan | note426 course_code TEXT,427 title TEXT NOT NULL,428 content TEXT NOT NULL DEFAULT '',429 meta TEXT NOT NULL DEFAULT '{}',430 created_at TEXT NOT NULL DEFAULT (datetime('now'))431);432433CREATE TABLE IF NOT EXISTS activity_log (434 id INTEGER PRIMARY KEY AUTOINCREMENT,435 user_id INTEGER NOT NULL,436 kind TEXT NOT NULL, -- chat | flashcards | quiz | exam | plan | summary437 course_code TEXT,438 duration_s INTEGER NOT NULL DEFAULT 0,439 meta TEXT NOT NULL DEFAULT '{}',440 created_at TEXT NOT NULL DEFAULT (datetime('now'))441);442CREATE INDEX IF NOT EXISTS idx_activity_user_time ON activity_log(user_id, created_at);443444CREATE TABLE IF NOT EXISTS weekly_goals (445 id INTEGER PRIMARY KEY AUTOINCREMENT,446 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,447 week_start TEXT NOT NULL, -- lundi ISO448 target TEXT NOT NULL DEFAULT '{}', -- JSON {cards: 40, quiz: 3, minutes: 120}449 UNIQUE (user_id, week_start)450);451`;452