HungLuong10/microservice
0
1-- Merged & ordered schema (ensure dependencies created before dependent tables)
2-- Date: 2025-12-20
3-- Notes:
4-- - This script assumes pgvector is available on the server.
5-- - Enums and extension are created first.
6-- - Tables are ordered so referenced tables/types exist before being used in foreign keys.
7-- - Indexes created at the end.
8
9-- 1) Extension
10CREATE EXTENSION IF NOT EXISTS vector;
11
12-- 2) ENUM types (create only if not exists)
13DO $$
14BEGIN
15 IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'flashcard_status') THEN
16 CREATE TYPE flashcard_status AS ENUM ('pending','done','miss');
17 END IF;
18 IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'roadmap_status') THEN
19 CREATE TYPE roadmap_status AS ENUM ('pending','done','skip','in_process');
20 END IF;
21 IF NOT EXISTS (SELECT 1 FROM pg_type WHERE typname = 'study_status') THEN
22 CREATE TYPE study_status AS ENUM ('pending','done','miss');
23 END IF;
24END$$;
25
26-- 3) Roles
27CREATE TABLE IF NOT EXISTS role (
28 role_id SERIAL PRIMARY KEY,
29 role_name VARCHAR(50) NOT NULL UNIQUE
30);
31
32INSERT INTO role (role_id, role_name)
33VALUES (1, 'USER'), (2, 'ADMIN')
34ON CONFLICT (role_id) DO NOTHING;
35
36-- 4) Users (depends on role)
37CREATE TABLE IF NOT EXISTS "user" (
38 user_id SERIAL PRIMARY KEY,
39 user_name VARCHAR(100) NOT NULL,
40 email VARCHAR(200) UNIQUE,
41 password_hash VARCHAR(200),
42 birthday DATE,
43 created_at TIMESTAMPTZ DEFAULT now(),
44 updated_at TIMESTAMPTZ,
45 role_id INT DEFAULT 1 REFERENCES role(role_id) ON DELETE SET NULL,
46 available BOOLEAN DEFAULT true
47);
48
49-- 5) User update / history (depends on "user")
50CREATE TABLE IF NOT EXISTS user_update (
51 user_update_id SERIAL PRIMARY KEY,
52 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE,
53 user_name VARCHAR(100),
54 email VARCHAR(200),
55 password_hash VARCHAR(200),
56 birthday DATE,
57 created_at TIMESTAMPTZ DEFAULT now(),
58 updated_by INT REFERENCES "user"(user_id) ON DELETE SET NULL
59);
60
61-- 6) Subjects
62CREATE TABLE IF NOT EXISTS subject (
63 subject_id SERIAL PRIMARY KEY,
64 subject_name VARCHAR(100) NOT NULL,
65 subject_type INT DEFAULT 1,
66 available BOOLEAN DEFAULT true
67);
68
69-- 7) Topics (depends on subject)
70CREATE TABLE IF NOT EXISTS topic (
71 topic_id SERIAL PRIMARY KEY,
72 title TEXT,
73 description TEXT,
74 subject_id INT REFERENCES subject(subject_id) ON DELETE SET NULL
75);
76
77-- 8) Roadmap steps (depends on topic)
78CREATE TABLE IF NOT EXISTS roadmap_step (
79 roadmap_step_id SERIAL PRIMARY KEY,
80 title VARCHAR(200) NOT NULL,
81 description TEXT,
82 topic_id INT REFERENCES topic(topic_id) ON DELETE SET NULL
83);
84
85-- 9) Documents (depends on topic)
86CREATE TABLE IF NOT EXISTS document (
87 document_id SERIAL PRIMARY KEY,
88 title TEXT NOT NULL,
89 description TEXT,
90 link VARCHAR(500),
91 embedding vector(1536),
92 created_at TIMESTAMPTZ DEFAULT now(),
93 topic_id INT REFERENCES topic(topic_id) ON DELETE SET NULL,
94 available BOOLEAN DEFAULT true
95);
96
97-- 10) Document history (depends on document and user)
98CREATE TABLE IF NOT EXISTS document_history (
99 document_history_id SERIAL PRIMARY KEY,
100 start_time TIMESTAMPTZ,
101 end_time TIMESTAMPTZ,
102 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE,
103 document_id INT REFERENCES document(document_id) ON DELETE CASCADE,
104 created_at TIMESTAMPTZ DEFAULT now()
105);
106
107-- 11) Chunk (depends on document)
108CREATE TABLE IF NOT EXISTS chunk (
109 chunk_id SERIAL PRIMARY KEY,
110 title TEXT NOT NULL,
111 text TEXT,
112 link VARCHAR(250),
113 embedding vector(384),
114 document_id INT REFERENCES document(document_id) ON DELETE CASCADE
115);
116
117-- 12) Roadmap step <-> document mapping (depends on roadmap_step and document)
118CREATE TABLE IF NOT EXISTS roadmap_step_document (
119 roadmap_step_id INT NOT NULL,
120 document_id INT NOT NULL,
121 PRIMARY KEY (roadmap_step_id, document_id),
122 FOREIGN KEY (roadmap_step_id) REFERENCES roadmap_step(roadmap_step_id) ON DELETE CASCADE,
123 FOREIGN KEY (document_id) REFERENCES document(document_id) ON DELETE CASCADE
124);
125
126-- 13) User roadmap step (depends on roadmap_step and user)
127CREATE TABLE IF NOT EXISTS user_roadmap_step (
128 user_roadmap_step_id SERIAL PRIMARY KEY,
129 status roadmap_status DEFAULT 'pending',
130 roadmap_step_id INT REFERENCES roadmap_step(roadmap_step_id) ON DELETE CASCADE,
131 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE
132);
133
134-- 14) Flashcard deck (depends on user)
135CREATE TABLE IF NOT EXISTS flashcard_deck (
136 flashcard_deck_id SERIAL PRIMARY KEY,
137 title VARCHAR(500) NOT NULL,
138 description TEXT,
139 created_at TIMESTAMPTZ DEFAULT now(),
140 last_reviewed TIMESTAMPTZ,
141 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE
142);
143
144-- 15) Flashcard (depends on flashcard_deck)
145CREATE TABLE IF NOT EXISTS flashcard (
146 flashcard_id SERIAL PRIMARY KEY,
147 front TEXT NOT NULL,
148 back TEXT,
149 example TEXT DEFAULT '',
150 created_at TIMESTAMPTZ DEFAULT now(),
151 status flashcard_status DEFAULT 'pending',
152 flashcard_deck_id INT REFERENCES flashcard_deck(flashcard_deck_id) ON DELETE CASCADE
153);
154
155-- 16) Study schedule (depends on user and subject)
156CREATE TABLE IF NOT EXISTS study_schedule (
157 study_schedule_id SERIAL PRIMARY KEY,
158 title VARCHAR(500) NOT NULL,
159 description TEXT,
160 start_time TIMESTAMPTZ,
161 end_time TIMESTAMPTZ,
162 status study_status DEFAULT 'pending',
163 target_question INT,
164 created_at TIMESTAMPTZ DEFAULT now(),
165 update_at TIMESTAMPTZ DEFAULT now(),
166 user_id INT REFERENCES "user"(user_id),
167 subject_id INT REFERENCES subject(subject_id) ON DELETE SET NULL
168);
169
170-- 17) User goal (depends on user and subject)
171CREATE TABLE IF NOT EXISTS user_goal (
172 user_goal_id SERIAL PRIMARY KEY,
173 target_score DECIMAL(5,2),
174 deadline TIMESTAMPTZ,
175 created_at TIMESTAMPTZ DEFAULT now(),
176 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE,
177 subject_id INT REFERENCES subject(subject_id) ON DELETE SET NULL
178);
179
180-- 18) Current progress (depends on user and optionally user_goal)
181CREATE TABLE IF NOT EXISTS current_progress (
182 current_progress_id SERIAL PRIMARY KEY,
183 current_progress DECIMAL(5,2),
184 created_at TIMESTAMPTZ DEFAULT now(),
185 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE,
186 user_goal_id INT REFERENCES user_goal(user_goal_id)
187);
188
189-- 19) Bank (depends on topic)
190CREATE TABLE IF NOT EXISTS bank (
191 bank_id SERIAL PRIMARY KEY,
192 description VARCHAR(500),
193 topic_id INT REFERENCES topic(topic_id) ON DELETE SET NULL,
194 time_limit INT,
195 available BOOLEAN DEFAULT true
196);
197
198
199
200
201
202
203-- 20) Question (standalone)
204CREATE TABLE IF NOT EXISTS question (
205 question_id SERIAL PRIMARY KEY,
206 question_name VARCHAR(1000),
207 question_content VARCHAR(10000),
208 embedding vector(1536),
209 image JSON,
210 type_question INT DEFAULT 1,
211 source VARCHAR(50),
212 available BOOLEAN DEFAULT true
213);
214
215-- 21) Answer (depends on question)
216CREATE TABLE IF NOT EXISTS answer (
217 answer_id SERIAL PRIMARY KEY,
218 question_id INT NOT NULL REFERENCES question(question_id) ON DELETE CASCADE,
219 answer_content VARCHAR(10000) NOT NULL,
220 is_correct BOOLEAN DEFAULT FALSE,
221 image JSON
222);
223
224
225
226
227
228-- 22) question_bank (depends on question and bank)
229CREATE TABLE IF NOT EXISTS question_bank (
230 question_id INT NOT NULL REFERENCES question(question_id) ON DELETE CASCADE,
231 bank_id INT NOT NULL REFERENCES bank(bank_id) ON DELETE CASCADE,
232 PRIMARY KEY (question_id, bank_id)
233);
234
235
236
237
238-- 23) Exam schedule
239CREATE TABLE IF NOT EXISTS exam_schedule (
240 exam_schedule_id SERIAL PRIMARY KEY,
241 start_time TIMESTAMPTZ,
242 end_time TIMESTAMPTZ,
243 created_at TIMESTAMPTZ DEFAULT now(),
244 updated_at TIMESTAMPTZ DEFAULT now()
245);
246
247-- 24) Exam (depends on topic and exam_schedule)
248CREATE TABLE IF NOT EXISTS exam (
249 exam_id SERIAL PRIMARY KEY,
250 exam_name VARCHAR(200) NOT NULL,
251 created_at TIMESTAMPTZ DEFAULT now(),
252 time_limit INT, -- minutes
253 topic_id INT REFERENCES topic(topic_id) ON DELETE SET NULL,
254 exam_schedule_id INT REFERENCES exam_schedule(exam_schedule_id) ON DELETE SET NULL,
255 description VARCHAR(200),
256 available BOOLEAN DEFAULT true
257);
258
259-- 25) Contestants (depends on exam and user)
260CREATE TABLE IF NOT EXISTS contestants (
261 contestants_id SERIAL PRIMARY KEY,
262 exam_id INT REFERENCES exam(exam_id) ON DELETE CASCADE,
263 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE
264);
265
266-- user từng làm exam nào, điểm số ra sao
267-- 26) history_exam (depends on exam and user)
268CREATE TABLE IF NOT EXISTS history_exam (
269 history_exam_id SERIAL PRIMARY KEY,
270 exam_id INT NOT NULL REFERENCES exam(exam_id) ON DELETE CASCADE,
271 user_id INT NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE,
272 score DECIMAL(4,2),
273 time_test BIGINT,
274 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
275);
276
277-- 27) history_bank (depends on bank and user)
278CREATE TABLE IF NOT EXISTS history_bank (
279 history_bank_id SERIAL PRIMARY KEY,
280 bank_id INT NOT NULL REFERENCES bank(bank_id) ON DELETE CASCADE,
281 user_id INT NOT NULL REFERENCES "user"(user_id) ON DELETE CASCADE,
282 score DECIMAL(4,2),
283 time_test BIGINT,
284 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
285);
286
287-- 28) user_exam_answer (depends on user, exam, history_exam)
288CREATE TABLE IF NOT EXISTS user_exam_answer (
289 user_exam_answer_id SERIAL PRIMARY KEY,
290 created_at TIMESTAMPTZ DEFAULT now(),
291 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE,
292 exam_id INT REFERENCES exam(exam_id) ON DELETE CASCADE,
293 answer_id INT, -- optional pointer to chosen answer
294 user_answer_text TEXT DEFAULT '',
295 history_exam_id INT,
296 question_id INT,
297 CONSTRAINT fk_user_exam_answer_history FOREIGN KEY (history_exam_id) REFERENCES history_exam(history_exam_id) ON DELETE CASCADE
298);
299-- ALTER TABLE user_exam_answer
300-- ADD COLUMN IF NOT EXISTS question_id INT;
301
302-- 29) user_bank_answer (depends on bank, user, answer, history_bank)
303CREATE TABLE IF NOT EXISTS user_bank_answer (
304 user_bank_answer_id SERIAL PRIMARY KEY,
305 created_at TIMESTAMPTZ DEFAULT now(),
306 bank_id INT REFERENCES bank(bank_id) ON DELETE SET NULL,
307 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE,
308 answer_id INT REFERENCES answer(answer_id) ON DELETE SET NULL,
309 user_answer_text TEXT DEFAULT '',
310 question_id INT,
311 history_bank_id INT,
312 CONSTRAINT fk_user_bank_answer_history FOREIGN KEY (history_bank_id) REFERENCES history_bank(history_bank_id) ON DELETE CASCADE
313);
314
315-- 30) question_exam mapping (depends on question and exam)
316CREATE TABLE IF NOT EXISTS question_exam (
317 question_id INT NOT NULL REFERENCES question(question_id) ON DELETE CASCADE,
318 exam_id INT NOT NULL REFERENCES exam(exam_id) ON DELETE CASCADE,
319 PRIMARY KEY (question_id, exam_id)
320);
321
322
323
324
325
326
327-- 31) Chat history (depends on user)
328CREATE TABLE IF NOT EXISTS chat_history (
329 chat_history_id SERIAL PRIMARY KEY,
330 user_id INT REFERENCES "user"(user_id) ON DELETE CASCADE,
331 is_user BOOLEAN,
332 role VARCHAR(20), -- 'user','assistant','system'
333 message TEXT,
334 embedding vector(1536),
335 created_at TIMESTAMPTZ DEFAULT now()
336);
337
338
339
340
341
342
343-- 32) Image tables (depend on question/answer)
344CREATE TABLE IF NOT EXISTS image_question (
345 image_question_id SERIAL PRIMARY KEY,
346 image_link TEXT,
347 question_id INT REFERENCES question(question_id) ON DELETE CASCADE
348);
349
350CREATE TABLE IF NOT EXISTS image_answer (
351 image_answer_id SERIAL PRIMARY KEY,
352 image_link TEXT,
353 answer_id INT REFERENCES answer(answer_id) ON DELETE CASCADE
354);
355
356-- 33) Indexes (created after tables)
357CREATE INDEX IF NOT EXISTS idx_topic_subject ON topic(subject_id);
358CREATE INDEX IF NOT EXISTS idx_document_topic ON document(topic_id);
359CREATE INDEX IF NOT EXISTS idx_document_history_user ON document_history(user_id);
360CREATE INDEX IF NOT EXISTS idx_document_history_document ON document_history(document_id);
361CREATE INDEX IF NOT EXISTS idx_chat_history_user ON chat_history(user_id);
362CREATE INDEX IF NOT EXISTS idx_answer_question ON answer(question_id);
363CREATE INDEX IF NOT EXISTS idx_question_exam_exam ON question_exam(exam_id);
364CREATE INDEX IF NOT EXISTS idx_bank_topic ON bank(topic_id);
365CREATE INDEX IF NOT EXISTS idx_user_goal_user ON user_goal(user_id);
366CREATE INDEX IF NOT EXISTS idx_user_email ON "user"(email);
367
368-- Optional pgvector ANN index suggestions (create these AFTER you have vectors and tested tuning lists):
369-- CREATE INDEX IF NOT EXISTS idx_document_embedding ON document USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
370-- CREATE INDEX IF NOT EXISTS idx_question_embedding ON question USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
371-- CREATE INDEX IF NOT EXISTS idx_chat_history_embedding ON chat_history USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
372-- CREATE INDEX IF NOT EXISTS idx_chunk_embedding ON chunk USING ivfflat (embedding vector_cosine_ops) WITH (lists = 100);
373
374-- End of ordered merged schema
375
376CREATE EXTENSION IF NOT EXISTS unaccent;
377