SQL schema catalog
Trang này được sinh từ backend/src/main/resources/db/migration/*.sql bằng scripts/generate-schema-catalog.mjs. Đây là catalog để trả lời câu hỏi “database có bao nhiêu bảng và bảng nào làm gì”.
Số liệu kiểm chứng
| Chỉ số | Giá trị | Ghi chú |
|---|---|---|
| Flyway migration SQL | 107 | Số file migration trong repo |
| Application tables | 78 | Bảng do migration LMS tạo ra trong schema public |
| Materialized views | 2 | mv_course_stats, mv_teacher_performance |
| Latest migration file | V135__video_storage_governance.sql |
File cuối theo thứ tự Flyway |
Con số đúng theo Flyway hiện tại là 78 application tables + 2 materialized views. Nếu DBeaver thấy thêm flyway_schema_history, đó là bảng quản trị Flyway, không tính vào bảng nghiệp vụ LMS. 107 là số file migration SQL, không phải số bảng. Version cuối là V135 vì có version bị bỏ trống và có V54.1 dạng hotfix.
Để xem ảnh ERD tổng từ DBeaver, sơ đồ domain dễ học và row estimate từ DB sống, mở DB sống và ERD DBeaver.
Migration SQL inventory
| # | Migration file | Main topic |
|---|---|---|
| 1 | V1__lms_complete_schema.sql |
lms complete schema |
| 2 | V26__normalize_enums.sql |
normalize enums |
| 3 | V27__add_performance_indexes.sql |
add performance indexes |
| 4 | V28__add_foreign_key_constraints.sql |
add foreign key constraints |
| 5 | V29__complete_assignment_entities.sql |
complete assignment entities |
| 6 | V30__add_missing_indexes.sql |
add missing indexes |
| 7 | V31__student_mvp_enhancements.sql |
student mvp enhancements |
| 8 | V32__auto_course_code.sql |
auto course code |
| 9 | V33__seed_categories.sql |
seed categories |
| 10 | V34__add_delivery_mode_to_courses.sql |
add delivery mode to courses |
| 11 | V35__quiz_multi_question_type.sql |
quiz multi question type |
| 12 | V36__question_bank_foundation.sql |
question bank foundation |
| 13 | V37__student_experience.sql |
student experience |
| 14 | V38__video_progress_tracking.sql |
video progress tracking |
| 15 | V39__gamification_system.sql |
gamification system |
| 16 | V40__add_org_admin_role.sql |
add org admin role |
| 17 | V41__rubric_library_support.sql |
rubric library support |
| 18 | V42__quiz_attempt_optimistic_locking.sql |
quiz attempt optimistic locking |
| 19 | V43__teacher_invitations.sql |
teacher invitations |
| 20 | V44__seed_teacher_student_accounts.sql |
seed teacher student accounts |
| 21 | V45__wiii_ai_insights_alerts.sql |
wiii ai insights alerts |
| 22 | V46__password_reset_tokens.sql |
password reset tokens |
| 23 | V47__vnpay_payment_fields.sql |
vnpay payment fields |
| 24 | V48__bookmarks.sql |
bookmarks |
| 25 | V49__student_notes.sql |
student notes |
| 26 | V50__email_verification_tokens.sql |
email verification tokens |
| 27 | V51__quiz_attempt_is_passed.sql |
quiz attempt is passed |
| 28 | V52__quiz_availability_dates.sql |
quiz availability dates |
| 29 | V53__quiz_availability_indexes.sql |
quiz availability indexes |
| 30 | V54__seed_users_courses_content.sql |
seed users courses content |
| 31 | V54.1__production_schema_catchup.sql |
production schema catchup |
| 32 | V55__seed_assessment_enrollments.sql |
seed assessment enrollments |
| 33 | V56__seed_default_test_accounts.sql |
seed default test accounts |
| 34 | V57__fix_vietnamese_diacritics.sql |
fix vietnamese diacritics |
| 35 | V58__fix_enrollments_updated_at.sql |
fix enrollments updated at |
| 36 | V59__payment_version_column.sql |
payment version column |
| 37 | V60__payment_refund_fields.sql |
payment refund fields |
| 38 | V61__payment_indexes.sql |
payment indexes |
| 39 | V62__optimistic_locking_version_columns.sql |
optimistic locking version columns |
| 40 | V63__refund_status_index.sql |
refund status index |
| 41 | V64__organizations_and_invites.sql |
organizations and invites |
| 42 | V69__user_token_expiry_days.sql |
user token expiry days |
| 43 | V70__course_categories_and_tags.sql |
course categories and tags |
| 44 | V71__rename_course_tags_element_collection.sql |
rename course tags element collection |
| 45 | V72__fix_vietnamese_diacritics.sql |
fix vietnamese diacritics |
| 46 | V73__drop_orphaned_new_category_id.sql |
drop orphaned new category id |
| 47 | V74__upload_sessions.sql |
upload sessions |
| 48 | V75__align_courses_category_fk_with_course_categories.sql |
align courses category fk with course categories |
| 49 | V76__allow_offline_download.sql |
allow offline download |
| 50 | V77__course_content_version_and_stream_video.sql |
course content version and stream video |
| 51 | V78__messages_unread_performance_index.sql |
messages unread performance index |
| 52 | V79__admin_settings_table.sql |
admin settings table |
| 53 | V80__add_sepay_payment_fields.sql |
add sepay payment fields |
| 54 | V81__org_payment_configs.sql |
org payment configs |
| 55 | V82__teacher_bank_accounts.sql |
teacher bank accounts |
| 56 | V83__revenue_splits.sql |
revenue splits |
| 57 | V84__payout_requests.sql |
payout requests |
| 58 | V85__payment_gateway_admin_settings.sql |
payment gateway admin settings |
| 59 | V86__reenable_sepay.sql |
reenable sepay |
| 60 | V87__auto_verify_existing_bank_accounts.sql |
auto verify existing bank accounts |
| 61 | V88__allow_cancelled_payout_status.sql |
allow cancelled payout status |
| 62 | V89__localize_cancelled_payout_note.sql |
localize cancelled payout note |
| 63 | V90__enable_unaccent_extension.sql |
enable unaccent extension |
| 64 | V91__quiz_assessment_metadata.sql |
quiz assessment metadata |
| 65 | V92__course_publications_and_version_modes.sql |
course publications and version modes |
| 66 | V93__offline_storage_telemetry.sql |
offline storage telemetry |
| 67 | V94__learning_classes_publication_fk.sql |
learning classes publication fk |
| 68 | V95__video_assets_and_renditions.sql |
video assets and renditions |
| 69 | V96__course_intro_video_assets.sql |
course intro video assets |
| 70 | V97__video_assets_adaptive_playback.sql |
video assets adaptive playback |
| 71 | V98__upload_sessions_multipart_support.sql |
upload sessions multipart support |
| 72 | V99__upload_sessions_multipart_upload_id_text.sql |
upload sessions multipart upload id text |
| 73 | V100__assessment_schema_remediation.sql |
assessment schema remediation |
| 74 | V101__grading_audit_log.sql |
grading audit log |
| 75 | V102__quiz_access_password.sql |
quiz access password |
| 76 | V103__diversify_quiz_types.sql |
diversify quiz types |
| 77 | V104__quiz_attempts_add_timeout_status.sql |
quiz attempts add timeout status |
| 78 | V105__quiz_max_score_scale.sql |
quiz max score scale |
| 79 | V106__user_avatar_url.sql |
user avatar url |
| 80 | V107__announcements.sql |
announcements |
| 81 | V108__message_reactions.sql |
message reactions |
| 82 | V109__message_recall.sql |
message recall |
| 83 | V110__message_content_nullable.sql |
message content nullable |
| 84 | V111__message_reply.sql |
message reply |
| 85 | V112__class_teachers.sql |
class teachers |
| 86 | V113__course_review_events_and_release_notes.sql |
course review events and release notes |
| 87 | V114__user_external_identities.sql |
user external identities |
| 88 | V115__course_rejection_category.sql |
course rejection category |
| 89 | V116__add_must_change_password_to_users.sql |
add must change password to users |
| 90 | V117__video_assets_source_attachment_unique.sql |
video assets source attachment unique |
| 91 | V118__user_account_status.sql |
user account status |
| 92 | V119__multi_org_foundation.sql |
multi org foundation |
| 93 | V120__repair_default_platform_org.sql |
repair default platform org |
| 94 | V121__assignment_instruction_attachments.sql |
assignment instruction attachments |
| 95 | V122__seed_quiz_attempts_realistic.sql |
seed quiz attempts realistic |
| 96 | V123__seed_engagement_data.sql |
seed engagement data |
| 97 | V124__seed_assignments_notes_bookmarks.sql |
seed assignments notes bookmarks |
| 98 | V125__enrich_nav_saf_lesson_content.sql |
enrich nav saf lesson content |
| 99 | V126__attach_safety_video_saf101.sql |
attach safety video saf101 |
| 100 | V127__normalize_video_block_schema.sql |
normalize video block schema |
| 101 | V128__refresh_materialized_views.sql |
refresh materialized views |
| 102 | V129__file_attachment_fk.sql |
file attachment fk |
| 103 | V130__stcw_competency_mapping.sql |
stcw competency mapping |
| 104 | V131__expand_competencies_solas_marpol_colregs.sql |
expand competencies solas marpol colregs |
| 105 | V133__add_rubric_data_to_submissions.sql |
add rubric data to submissions |
| 106 | V134__allow_simulation_sections.sql |
allow simulation sections |
| 107 | V135__video_storage_governance.sql |
video storage governance |
Domain summary
| Domain | Số bảng | Bảng |
|---|---|---|
| AI/Wiii | 4 | ai_alerts, ai_insights, chat_messages, chat_sessions |
| Assessment | 15 | assignment_allocation_students, assignment_allocations, assignment_attachments, assignment_rubrics, assignment_submissions, assignments, grading_audit_log, packages, question_bank_categories, question_options, questions, quiz_assignments, quiz_attempts, quiz_questions, quizzes |
| Communication | 3 | conversations, message_reactions, messages |
| Competency/STCW | 3 | lesson_competency_mappings, maritime_standards, standard_competencies |
| Course authoring | 18 | announcement_reads, announcements, categories, chapters, course_categories, course_embedded_tags, course_review_events, course_reviews, course_tag_assignments, course_tags, course_tags_legacy, course_versions, courses, lesson_assignments, lesson_attachments, lessons, sections, teacher_invitations |
| Identity/Admin | 9 | admin_settings, audit_log, email_verification_tokens, login_attempts, organization_invites, organizations, password_reset_tokens, user_external_identities, users |
| Learning delivery | 11 | achievements, bookmarks, certificates, enrollments, learning_events, learning_streaks, notifications, student_achievements, student_lesson_progress, student_notes, video_progress |
| Media/Upload | 6 | client_offline_storage_telemetry, file_attachments, upload_sessions, video_assets, video_ingest_jobs, video_renditions |
| Payment/Revenue | 5 | org_payment_configs, payment_transactions, payout_requests, revenue_splits, teacher_bank_accounts |
| Publication/Class | 3 | class_teachers, course_publications, learning_classes |
| Shared/System | 1 | outbox_messages |
All tables quick index
| # | Table | Domain | Created/first seen | Columns | Indexes | Purpose |
|---|---|---|---|---|---|---|
| 1 | achievements |
Learning delivery | V39__gamification_system.sql |
7 | 0 | Bảng thuộc domain Learning delivery. |
| 2 | admin_settings |
Identity/Admin | V79__admin_settings_table.sql |
3 | 0 | Bảng thuộc domain Identity/Admin. |
| 3 | ai_alerts |
AI/Wiii | V45__wiii_ai_insights_alerts.sql |
8 | 3 | Bảng thuộc domain AI/Wiii. |
| 4 | ai_insights |
AI/Wiii | V45__wiii_ai_insights_alerts.sql |
7 | 3 | Bảng thuộc domain AI/Wiii. |
| 5 | announcement_reads |
Course authoring | V107__announcements.sql |
3 | 1 | Bảng thuộc domain Course authoring. |
| 6 | announcements |
Course authoring | V107__announcements.sql |
9 | 3 | Bảng thuộc domain Course authoring. |
| 7 | assignment_allocation_students |
Assessment | V1__lms_complete_schema.sql |
5 | 4 | Bảng thuộc domain Assessment. |
| 8 | assignment_allocations |
Assessment | V1__lms_complete_schema.sql |
8 | 4 | Bảng thuộc domain Assessment. |
| 9 | assignment_attachments |
Assessment | V1__lms_complete_schema.sql |
11 | 4 | Bảng thuộc domain Assessment. |
| 10 | assignment_rubrics |
Assessment | V1__lms_complete_schema.sql |
9 | 4 | Bảng thuộc domain Assessment. |
| 11 | assignment_submissions |
Assessment | V1__lms_complete_schema.sql |
19 | 12 | Bảng thuộc domain Assessment. |
| 12 | assignments |
Assessment | V1__lms_complete_schema.sql |
15 | 5 | Bảng thuộc domain Assessment. |
| 13 | audit_log |
Identity/Admin | V1__lms_complete_schema.sql |
8 | 3 | Bảng thuộc domain Identity/Admin. |
| 14 | bookmarks |
Learning delivery | V48__bookmarks.sql |
9 | 2 | Bảng thuộc domain Learning delivery. |
| 15 | categories |
Course authoring | V1__lms_complete_schema.sql |
6 | 1 | Bảng thuộc domain Course authoring. |
| 16 | certificates |
Learning delivery | V31__student_mvp_enhancements.sql |
6 | 4 | Bảng thuộc domain Learning delivery. |
| 17 | chapters |
Course authoring | V1__lms_complete_schema.sql |
7 | 3 | Bảng thuộc domain Course authoring. |
| 18 | chat_messages |
AI/Wiii | V1__lms_complete_schema.sql |
6 | 2 | Bảng thuộc domain AI/Wiii. |
| 19 | chat_sessions |
AI/Wiii | V1__lms_complete_schema.sql |
8 | 2 | Bảng thuộc domain AI/Wiii. |
| 20 | class_teachers |
Publication/Class | V112__class_teachers.sql |
5 | 2 | Bảng thuộc domain Publication/Class. |
| 21 | client_offline_storage_telemetry |
Media/Upload | V93__offline_storage_telemetry.sql |
16 | 3 | Bảng thuộc domain Media/Upload. |
| 22 | conversations |
Communication | V1__lms_complete_schema.sql |
11 | 4 | Bảng thuộc domain Communication. |
| 23 | course_categories |
Course authoring | V70__course_categories_and_tags.sql |
12 | 3 | Bảng thuộc domain Course authoring. |
| 24 | course_embedded_tags |
Course authoring | V71__rename_course_tags_element_collection.sql |
2 | 1 | Bảng thuộc domain Course authoring. |
| 25 | course_publications |
Publication/Class | V92__course_publications_and_version_modes.sql |
9 | 2 | Snapshot learner-facing sau submit/approve. |
| 26 | course_review_events |
Course authoring | V113__course_review_events_and_release_notes.sql |
7 | 2 | Bảng thuộc domain Course authoring. |
| 27 | course_reviews |
Course authoring | V31__student_mvp_enhancements.sql |
7 | 4 | Bảng thuộc domain Course authoring. |
| 28 | course_tag_assignments |
Course authoring | V70__course_categories_and_tags.sql |
2 | 1 | Bảng thuộc domain Course authoring. |
| 29 | course_tags |
Course authoring | V70__course_categories_and_tags.sql |
4 | 0 | Bảng thuộc domain Course authoring. |
| 30 | course_tags_legacy |
Course authoring | V1__lms_complete_schema.sql |
2 | 0 | Bảng tag cũ được rename ở V70 để giữ tương thích dữ liệu. |
| 31 | course_versions |
Course authoring | V1__lms_complete_schema.sql |
5 | 2 | Bảng thuộc domain Course authoring. |
| 32 | courses |
Course authoring | V1__lms_complete_schema.sql |
31 | 11 | Khóa học draft/metadata của teacher. |
| 33 | email_verification_tokens |
Identity/Admin | V50__email_verification_tokens.sql |
6 | 2 | Bảng thuộc domain Identity/Admin. |
| 34 | enrollments |
Learning delivery | V1__lms_complete_schema.sql |
12 | 6 | Ghi danh student vào class/course. |
| 35 | file_attachments |
Media/Upload | V1__lms_complete_schema.sql |
17 | 7 | Bảng thuộc domain Media/Upload. |
| 36 | grading_audit_log |
Assessment | V101__grading_audit_log.sql |
13 | 3 | Bảng thuộc domain Assessment. |
| 37 | learning_classes |
Publication/Class | V1__lms_complete_schema.sql |
15 | 4 | Lớp học gắn course/publication và teacher. |
| 38 | learning_events |
Learning delivery | V38__video_progress_tracking.sql |
7 | 3 | Bảng thuộc domain Learning delivery. |
| 39 | learning_streaks |
Learning delivery | V39__gamification_system.sql |
7 | 1 | Bảng thuộc domain Learning delivery. |
| 40 | lesson_assignments |
Course authoring | V1__lms_complete_schema.sql |
5 | 2 | Bảng thuộc domain Course authoring. |
| 41 | lesson_attachments |
Course authoring | V1__lms_complete_schema.sql |
11 | 1 | Bảng thuộc domain Course authoring. |
| 42 | lesson_competency_mappings |
Competency/STCW | V130__stcw_competency_mapping.sql |
6 | 2 | Map lesson với competency. |
| 43 | lessons |
Course authoring | V1__lms_complete_schema.sql |
13 | 4 | Bài học; content_blocks là cột JSONB, không phải bảng riêng. |
| 44 | login_attempts |
Identity/Admin | V1__lms_complete_schema.sql |
7 | 4 | Bảng thuộc domain Identity/Admin. |
| 45 | maritime_standards |
Competency/STCW | V130__stcw_competency_mapping.sql |
7 | 0 | Chuẩn hàng hải như STCW/SOLAS/MARPOL/COLREGs. |
| 46 | message_reactions |
Communication | V108__message_reactions.sql |
5 | 1 | Bảng thuộc domain Communication. |
| 47 | messages |
Communication | V1__lms_complete_schema.sql |
10 | 6 | Bảng thuộc domain Communication. |
| 48 | notifications |
Learning delivery | V39__gamification_system.sql |
8 | 2 | Bảng thuộc domain Learning delivery. |
| 49 | org_payment_configs |
Payment/Revenue | V81__org_payment_configs.sql |
5 | 0 | Bảng thuộc domain Payment/Revenue. |
| 50 | organization_invites |
Identity/Admin | V64__organizations_and_invites.sql |
13 | 4 | Bảng thuộc domain Identity/Admin. |
| 51 | organizations |
Identity/Admin | V64__organizations_and_invites.sql |
10 | 2 | Bảng thuộc domain Identity/Admin. |
| 52 | outbox_messages |
Shared/System | V1__lms_complete_schema.sql |
11 | 3 | Bảng thuộc domain Shared/System. |
| 53 | packages |
Assessment | V1__lms_complete_schema.sql |
15 | 3 | Bảng thuộc domain Assessment. |
| 54 | password_reset_tokens |
Identity/Admin | V46__password_reset_tokens.sql |
6 | 2 | Bảng thuộc domain Identity/Admin. |
| 55 | payment_transactions |
Payment/Revenue | V31__student_mvp_enhancements.sql |
21 | 6 | Giao dịch VNPay/SePay/simulated và refund. |
| 56 | payout_requests |
Payment/Revenue | V84__payout_requests.sql |
10 | 2 | Bảng thuộc domain Payment/Revenue. |
| 57 | question_bank_categories |
Assessment | V36__question_bank_foundation.sql |
9 | 2 | Bảng thuộc domain Assessment. |
| 58 | question_options |
Assessment | V1__lms_complete_schema.sql |
8 | 1 | Bảng thuộc domain Assessment. |
| 59 | questions |
Assessment | V1__lms_complete_schema.sql |
16 | 10 | Ngân hàng câu hỏi; content_blocks là cột JSONB cho rich question. |
| 60 | quiz_assignments |
Assessment | V1__lms_complete_schema.sql |
7 | 2 | Bảng thuộc domain Assessment. |
| 61 | quiz_attempts |
Assessment | V1__lms_complete_schema.sql |
12 | 5 | Bảng thuộc domain Assessment. |
| 62 | quiz_questions |
Assessment | V1__lms_complete_schema.sql |
7 | 2 | Bảng thuộc domain Assessment. |
| 63 | quizzes |
Assessment | V1__lms_complete_schema.sql |
21 | 5 | Bảng thuộc domain Assessment. |
| 64 | revenue_splits |
Payment/Revenue | V83__revenue_splits.sql |
13 | 3 | Bảng thuộc domain Payment/Revenue. |
| 65 | sections |
Course authoring | V1__lms_complete_schema.sql |
12 | 1 | Bảng thuộc domain Course authoring. |
| 66 | standard_competencies |
Competency/STCW | V130__stcw_competency_mapping.sql |
10 | 2 | Năng lực/competency theo chuẩn. |
| 67 | student_achievements |
Learning delivery | V39__gamification_system.sql |
4 | 1 | Bảng thuộc domain Learning delivery. |
| 68 | student_lesson_progress |
Learning delivery | V1__lms_complete_schema.sql |
10 | 4 | Tiến độ granular theo bài học. |
| 69 | student_notes |
Learning delivery | V49__student_notes.sql |
10 | 2 | Bảng thuộc domain Learning delivery. |
| 70 | teacher_bank_accounts |
Payment/Revenue | V82__teacher_bank_accounts.sql |
8 | 2 | Bảng thuộc domain Payment/Revenue. |
| 71 | teacher_invitations |
Course authoring | V43__teacher_invitations.sql |
12 | 2 | Bảng thuộc domain Course authoring. |
| 72 | upload_sessions |
Media/Upload | V74__upload_sessions.sql |
13 | 3 | Phiên presigned upload lên storage. |
| 73 | user_external_identities |
Identity/Admin | V114__user_external_identities.sql |
10 | 3 | Bảng thuộc domain Identity/Admin. |
| 74 | users |
Identity/Admin | V1__lms_complete_schema.sql |
19 | 6 | Tài khoản, role, org, trạng thái và hồ sơ người dùng. |
| 75 | video_assets |
Media/Upload | V95__video_assets_and_renditions.sql |
32 | 8 | Tài sản video nguồn/adaptive/offline. |
| 76 | video_ingest_jobs |
Media/Upload | V95__video_assets_and_renditions.sql |
10 | 2 | Job ingest/package video cho worker. |
| 77 | video_progress |
Learning delivery | V38__video_progress_tracking.sql |
12 | 3 | Bảng thuộc domain Learning delivery. |
| 78 | video_renditions |
Media/Upload | V95__video_assets_and_renditions.sql |
11 | 2 | Bảng thuộc domain Media/Upload. |
Table details
achievements · Learning delivery · 7 columns · 0 indexes
Purpose: Bảng thuộc domain Learning delivery.
Migrations: V39__gamification_system.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V39__gamification_system.sql |
|
code |
VARCHAR(50) |
no | V39__gamification_system.sql |
|
name |
VARCHAR(255) |
no | V39__gamification_system.sql |
|
description |
TEXT |
yes | V39__gamification_system.sql |
|
icon |
VARCHAR(100) |
yes | V39__gamification_system.sql |
|
category |
VARCHAR(50) |
no | V39__gamification_system.sql |
|
threshold |
INTEGER |
no | V39__gamification_system.sql |
admin_settings · Identity/Admin · 3 columns · 0 indexes
Purpose: Bảng thuộc domain Identity/Admin.
Migrations: V79__admin_settings_table.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
setting_key |
VARCHAR(100) |
no | V79__admin_settings_table.sql |
|
setting_value |
TEXT |
no | V79__admin_settings_table.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V79__admin_settings_table.sql |
ai_alerts · AI/Wiii · 8 columns · 3 indexes
Purpose: Bảng thuộc domain AI/Wiii.
Migrations: V45__wiii_ai_insights_alerts.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V45__wiii_ai_insights_alerts.sql |
|
course_id |
UUID |
yes | V45__wiii_ai_insights_alerts.sql |
|
alert_type |
VARCHAR(50) |
no | V45__wiii_ai_insights_alerts.sql |
|
content |
TEXT |
no | V45__wiii_ai_insights_alerts.sql |
|
student_ids |
JSONB |
yes | V45__wiii_ai_insights_alerts.sql |
|
metadata |
JSONB |
yes | V45__wiii_ai_insights_alerts.sql |
|
is_resolved |
BOOLEAN |
yes | V45__wiii_ai_insights_alerts.sql |
|
created_at |
TIMESTAMPTZ |
no | V45__wiii_ai_insights_alerts.sql |
Indexes: idx_ai_alerts_course_id, idx_ai_alerts_created_at, idx_ai_alerts_type
ai_insights · AI/Wiii · 7 columns · 3 indexes
Purpose: Bảng thuộc domain AI/Wiii.
Migrations: V45__wiii_ai_insights_alerts.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V45__wiii_ai_insights_alerts.sql |
|
student_id |
UUID |
no | V45__wiii_ai_insights_alerts.sql |
|
insight_type |
VARCHAR(50) |
no | V45__wiii_ai_insights_alerts.sql |
|
content |
TEXT |
no | V45__wiii_ai_insights_alerts.sql |
|
metadata |
JSONB |
yes | V45__wiii_ai_insights_alerts.sql |
|
is_read |
BOOLEAN |
yes | V45__wiii_ai_insights_alerts.sql |
|
created_at |
TIMESTAMPTZ |
no | V45__wiii_ai_insights_alerts.sql |
Indexes: idx_ai_insights_created_at, idx_ai_insights_student_id, idx_ai_insights_type
announcement_reads · Course authoring · 3 columns · 1 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V107__announcements.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
announcement_id |
UUID |
no | announcements |
V107__announcements.sql |
user_id |
UUID |
no | users |
V107__announcements.sql |
read_at |
TIMESTAMPTZ |
no | V107__announcements.sql |
Indexes: idx_announcement_reads_user
announcements · Course authoring · 9 columns · 3 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V107__announcements.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V107__announcements.sql |
|
course_id |
UUID |
no | courses |
V107__announcements.sql |
author_id |
UUID |
no | users |
V107__announcements.sql |
title |
VARCHAR(200) |
no | V107__announcements.sql |
|
content |
TEXT |
no | V107__announcements.sql |
|
priority |
VARCHAR(20) |
no | V107__announcements.sql |
|
target_type |
VARCHAR(20) |
no | V107__announcements.sql |
|
published_at |
TIMESTAMPTZ |
no | V107__announcements.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V107__announcements.sql |
Indexes: idx_announcements_author_id, idx_announcements_course_id, idx_announcements_published_at
assignment_allocation_students · Assessment · 5 columns · 4 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V30__add_missing_indexes.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
allocation_id |
UUID |
no | V1__lms_complete_schema.sql |
|
student_id |
UUID |
no | V1__lms_complete_schema.sql |
|
assigned_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
custom_deadline |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
note |
TEXT |
yes | V1__lms_complete_schema.sql |
Indexes: idx_alloc_students_allocation_id, idx_alloc_students_student_id, idx_allocation_students_allocation_id, idx_allocation_students_student_id
assignment_allocations · Assessment · 8 columns · 4 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V30__add_missing_indexes.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
assignment_id |
UUID |
no | V1__lms_complete_schema.sql |
|
distribution_type |
VARCHAR(50) |
yes | V1__lms_complete_schema.sql |
|
class_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
allocator_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
due_date |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
is_active |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
allocated_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
Indexes: idx_allocations_assignment_id, idx_allocations_class_id, idx_assignment_allocations_assignment_id, idx_assignment_allocations_class_id
assignment_attachments · Assessment · 11 columns · 4 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V29__complete_assignment_entities.sql, V121__assignment_instruction_attachments.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
assignment_id |
UUID |
no | V1__lms_complete_schema.sql |
|
submission_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
file_name |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
file_url |
VARCHAR(512) |
no | V1__lms_complete_schema.sql |
|
file_size |
BIGINT |
yes | V1__lms_complete_schema.sql |
|
file_type |
VARCHAR(128) |
yes | V1__lms_complete_schema.sql |
|
storage_key |
VARCHAR(512) |
yes | V1__lms_complete_schema.sql |
|
uploaded_by |
UUID |
yes | V1__lms_complete_schema.sql |
|
uploaded_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
display_order |
INTEGER |
no | V121__assignment_instruction_attachments.sql |
Indexes: idx_assign_attach_assignment_id, idx_assign_attach_submission_id, idx_assignment_attach_instruction, idx_assignment_attachments_submission_id
assignment_rubrics · Assessment · 9 columns · 4 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V29__complete_assignment_entities.sql, V41__rubric_library_support.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
assignment_id |
UUID |
no | V1__lms_complete_schema.sql |
|
title |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
description |
TEXT |
yes | V1__lms_complete_schema.sql |
|
max_points |
DOUBLE PRECISION |
yes | V1__lms_complete_schema.sql |
|
criteria |
JSONB |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
teacher_id |
UUID |
yes | users |
V41__rubric_library_support.sql |
Indexes: idx_assignment_rubrics_assignment_id, idx_rubrics_assignment_id, idx_rubrics_criteria_gin, idx_rubrics_teacher_id
assignment_submissions · Assessment · 19 columns · 12 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V28__add_foreign_key_constraints.sql, V29__complete_assignment_entities.sql, V54.1__production_schema_catchup.sql, V62__optimistic_locking_version_columns.sql, V100__assessment_schema_remediation.sql, V129__file_attachment_fk.sql, V133__add_rubric_data_to_submissions.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
assignment_id |
UUID |
no | V1__lms_complete_schema.sql |
|
student_id |
UUID |
no | V1__lms_complete_schema.sql |
|
course_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
content |
TEXT |
yes | V1__lms_complete_schema.sql |
|
file_url |
VARCHAR(512) |
yes | V1__lms_complete_schema.sql |
|
file_name |
VARCHAR(255) |
yes | V1__lms_complete_schema.sql |
|
grade |
DOUBLE PRECISION |
yes | V1__lms_complete_schema.sql |
|
max_grade |
DOUBLE PRECISION |
yes | V1__lms_complete_schema.sql |
|
feedback |
TEXT |
yes | V1__lms_complete_schema.sql |
|
graded_by |
UUID |
yes | V1__lms_complete_schema.sql |
|
graded_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
submitted_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
version |
BIGINT |
no | V62__optimistic_locking_version_columns.sql |
|
file_attachment_id |
UUID |
yes | V129__file_attachment_fk.sql |
|
rubric_data |
JSONB |
yes | V133__add_rubric_data_to_submissions.sql |
Indexes: idx_assignment_submissions_assignment_id, idx_assignment_submissions_course_id, idx_assignment_submissions_status, idx_assignment_submissions_student_id, idx_assignment_submissions_submitted_at, idx_submissions_assignment_id, idx_submissions_course_status, idx_submissions_file_attachment, idx_submissions_pending_grading, idx_submissions_status, idx_submissions_status_assignment, idx_submissions_student_id
assignments · Assessment · 15 columns · 5 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V28__add_foreign_key_constraints.sql, V54.1__production_schema_catchup.sql, V55__seed_assessment_enrollments.sql, V100__assessment_schema_remediation.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
lesson_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
course_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
title |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
description |
TEXT |
yes | V1__lms_complete_schema.sql |
|
instructions |
TEXT |
yes | V1__lms_complete_schema.sql |
|
assignment_type |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
max_score |
NUMERIC(10, 2) |
yes | V1__lms_complete_schema.sql |
|
passing_score |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
due_date |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
max_attempts |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
allow_late_submission |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_assignments_course_id, idx_assignments_lesson_id, idx_assignments_published, idx_assignments_status, idx_assignments_status_due_date
audit_log · Identity/Admin · 8 columns · 3 indexes
Purpose: Bảng thuộc domain Identity/Admin.
Migrations: V1__lms_complete_schema.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
BIGSERIAL |
no | V1__lms_complete_schema.sql |
|
table_name |
VARCHAR(64) |
no | V1__lms_complete_schema.sql |
|
record_id |
UUID |
no | V1__lms_complete_schema.sql |
|
action |
VARCHAR(10) |
no | V1__lms_complete_schema.sql |
|
old_data |
JSONB |
yes | V1__lms_complete_schema.sql |
|
new_data |
JSONB |
yes | V1__lms_complete_schema.sql |
|
changed_by |
UUID |
yes | V1__lms_complete_schema.sql |
|
changed_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
Indexes: idx_audit_log_changed_at_brin, idx_audit_log_changed_by, idx_audit_log_table_record
bookmarks · Learning delivery · 9 columns · 2 indexes
Purpose: Bảng thuộc domain Learning delivery.
Migrations: V48__bookmarks.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V48__bookmarks.sql |
|
user_id |
UUID |
no | users |
V48__bookmarks.sql |
course_id |
UUID |
no | courses |
V48__bookmarks.sql |
lesson_id |
UUID |
yes | lessons |
V48__bookmarks.sql |
title |
VARCHAR(255) |
no | V48__bookmarks.sql |
|
url |
VARCHAR(1024) |
no | V48__bookmarks.sql |
|
position |
INTEGER |
no | V48__bookmarks.sql |
|
metadata |
JSONB |
yes | V48__bookmarks.sql |
|
created_at |
TIMESTAMPTZ |
no | V48__bookmarks.sql |
Indexes: idx_bookmarks_user_course, idx_bookmarks_user_id
categories · Course authoring · 6 columns · 1 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V1__lms_complete_schema.sql, V30__add_missing_indexes.sql, V32__auto_course_code.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
code |
VARCHAR(50) |
no | V1__lms_complete_schema.sql |
|
name |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
prefix |
VARCHAR(10) |
no | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_categories_code
certificates · Learning delivery · 6 columns · 4 indexes
Purpose: Bảng thuộc domain Learning delivery.
Migrations: V31__student_mvp_enhancements.sql, V37__student_experience.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V31__student_mvp_enhancements.sql |
|
enrollment_id |
UUID |
no | enrollments |
V31__student_mvp_enhancements.sql |
student_id |
UUID |
no | users |
V31__student_mvp_enhancements.sql |
course_id |
UUID |
no | courses |
V31__student_mvp_enhancements.sql |
verification_token |
UUID |
yes | V31__student_mvp_enhancements.sql |
|
issued_at |
TIMESTAMPTZ |
no | V31__student_mvp_enhancements.sql |
Indexes: idx_certificates_student, idx_certificates_token, idx_certs_student, idx_certs_verify
chapters · Course authoring · 7 columns · 3 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V28__add_foreign_key_constraints.sql, V54__seed_users_courses_content.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
course_id |
UUID |
no | V1__lms_complete_schema.sql |
|
title |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
description |
TEXT |
yes | V1__lms_complete_schema.sql |
|
order_index |
INTEGER |
no | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_chapters_course_id, idx_chapters_course_order, idx_chapters_order
chat_messages · AI/Wiii · 6 columns · 2 indexes
Purpose: Bảng thuộc domain AI/Wiii.
Migrations: V1__lms_complete_schema.sql, V30__add_missing_indexes.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
session_id |
UUID |
no | V1__lms_complete_schema.sql |
|
role |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
content |
TEXT |
no | V1__lms_complete_schema.sql |
|
tokens_used |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
Indexes: idx_chat_messages_created_at_brin, idx_chat_messages_session_id
chat_sessions · AI/Wiii · 8 columns · 2 indexes
Purpose: Bảng thuộc domain AI/Wiii.
Migrations: V1__lms_complete_schema.sql, V30__add_missing_indexes.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
user_id |
UUID |
no | V1__lms_complete_schema.sql |
|
title |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
context_type |
VARCHAR(50) |
yes | V1__lms_complete_schema.sql |
|
context_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
is_archived |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_chat_sessions_context_type, idx_chat_sessions_user_id
class_teachers · Publication/Class · 5 columns · 2 indexes
Purpose: Bảng thuộc domain Publication/Class.
Migrations: V112__class_teachers.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V112__class_teachers.sql |
|
class_id |
UUID |
no | learning_classes |
V112__class_teachers.sql |
teacher_id |
UUID |
no | users |
V112__class_teachers.sql |
role |
VARCHAR(20) |
no | V112__class_teachers.sql |
|
created_at |
TIMESTAMPTZ |
no | V112__class_teachers.sql |
Indexes: idx_class_teachers_class, idx_class_teachers_teacher
client_offline_storage_telemetry · Media/Upload · 16 columns · 3 indexes
Purpose: Bảng thuộc domain Media/Upload.
Migrations: V93__offline_storage_telemetry.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V93__offline_storage_telemetry.sql |
|
user_id |
UUID |
no | users |
V93__offline_storage_telemetry.sql |
event_type |
VARCHAR(50) |
no | V93__offline_storage_telemetry.sql |
|
availability |
VARCHAR(30) |
no | V93__offline_storage_telemetry.sql |
|
recovery_action |
VARCHAR(30) |
no | V93__offline_storage_telemetry.sql |
|
db_name |
VARCHAR(255) |
no | V93__offline_storage_telemetry.sql |
|
requires_redownload |
BOOLEAN |
no | V93__offline_storage_telemetry.sql |
|
error_name |
VARCHAR(255) |
yes | V93__offline_storage_telemetry.sql |
|
error_message |
TEXT |
yes | V93__offline_storage_telemetry.sql |
|
route |
VARCHAR(1024) |
yes | V93__offline_storage_telemetry.sql |
|
user_agent |
TEXT |
yes | V93__offline_storage_telemetry.sql |
|
platform |
VARCHAR(255) |
yes | V93__offline_storage_telemetry.sql |
|
connection_type |
VARCHAR(64) |
yes | V93__offline_storage_telemetry.sql |
|
occurred_at |
TIMESTAMPTZ |
no | V93__offline_storage_telemetry.sql |
|
payload_json |
JSONB |
no | V93__offline_storage_telemetry.sql |
|
created_at |
TIMESTAMPTZ |
no | V93__offline_storage_telemetry.sql |
Indexes: idx_client_offline_storage_telemetry_created_at, idx_client_offline_storage_telemetry_event_type, idx_client_offline_storage_telemetry_user_id
conversations · Communication · 11 columns · 4 indexes
Purpose: Bảng thuộc domain Communication.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V30__add_missing_indexes.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
participant1_id |
UUID |
no | V1__lms_complete_schema.sql |
|
participant2_id |
UUID |
no | V1__lms_complete_schema.sql |
|
last_message_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
last_message_preview |
VARCHAR(500) |
yes | V1__lms_complete_schema.sql |
|
unread_count_1 |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
unread_count_2 |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
is_archived_1 |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
is_archived_2 |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_conversations_participant1, idx_conversations_participant1_id, idx_conversations_participant2, idx_conversations_participant2_id
course_categories · Course authoring · 12 columns · 3 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V70__course_categories_and_tags.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V70__course_categories_and_tags.sql |
|
parent_id |
UUID |
yes | course_categories |
V70__course_categories_and_tags.sql |
code |
VARCHAR(50) |
no | V70__course_categories_and_tags.sql |
|
name |
VARCHAR(255) |
no | V70__course_categories_and_tags.sql |
|
slug |
VARCHAR(100) |
no | V70__course_categories_and_tags.sql |
|
prefix |
VARCHAR(10) |
yes | V70__course_categories_and_tags.sql |
|
description |
TEXT |
yes | V70__course_categories_and_tags.sql |
|
icon |
VARCHAR(50) |
yes | V70__course_categories_and_tags.sql |
|
sort_order |
INT |
no | V70__course_categories_and_tags.sql |
|
is_active |
BOOLEAN |
no | V70__course_categories_and_tags.sql |
|
created_at |
TIMESTAMPTZ |
no | V70__course_categories_and_tags.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V70__course_categories_and_tags.sql |
Indexes: idx_course_categories_active, idx_course_categories_parent, uq_course_categories_prefix
course_embedded_tags · Course authoring · 2 columns · 1 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V71__rename_course_tags_element_collection.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
course_id |
UUID |
no | courses |
V71__rename_course_tags_element_collection.sql |
tag_name |
VARCHAR(255) |
yes | V71__rename_course_tags_element_collection.sql |
Indexes: idx_course_embedded_tags_course
course_publications · Publication/Class · 9 columns · 2 indexes
Purpose: Snapshot learner-facing sau submit/approve.
Migrations: V92__course_publications_and_version_modes.sql, V113__course_review_events_and_release_notes.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V92__course_publications_and_version_modes.sql |
|
course_id |
UUID |
no | courses |
V92__course_publications_and_version_modes.sql |
publication_number |
INTEGER |
no | V92__course_publications_and_version_modes.sql |
|
content_version |
INTEGER |
no | V92__course_publications_and_version_modes.sql |
|
snapshot |
JSONB |
no | V92__course_publications_and_version_modes.sql |
|
published_at |
TIMESTAMPTZ |
no | V92__course_publications_and_version_modes.sql |
|
published_by_id |
UUID |
yes | V92__course_publications_and_version_modes.sql |
|
created_at |
TIMESTAMPTZ |
no | V92__course_publications_and_version_modes.sql |
|
release_notes |
TEXT |
yes | V113__course_review_events_and_release_notes.sql |
Indexes: idx_course_publications_course_id, idx_course_publications_course_id_pub_no
course_review_events · Course authoring · 7 columns · 2 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V113__course_review_events_and_release_notes.sql, V115__course_rejection_category.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V113__course_review_events_and_release_notes.sql |
|
course_id |
UUID |
no | courses |
V113__course_review_events_and_release_notes.sql |
reviewer_id |
UUID |
yes | users |
V113__course_review_events_and_release_notes.sql |
action |
VARCHAR(30) |
no | V113__course_review_events_and_release_notes.sql |
|
comment |
TEXT |
yes | V113__course_review_events_and_release_notes.sql |
|
created_at |
TIMESTAMPTZ |
no | V113__course_review_events_and_release_notes.sql |
|
rejection_category |
VARCHAR(50) |
yes | V115__course_rejection_category.sql |
Indexes: idx_course_review_events_category, idx_course_review_events_course_id
course_reviews · Course authoring · 7 columns · 4 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V31__student_mvp_enhancements.sql, V37__student_experience.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V31__student_mvp_enhancements.sql |
|
course_id |
UUID |
no | courses |
V31__student_mvp_enhancements.sql |
student_id |
UUID |
no | users |
V31__student_mvp_enhancements.sql |
rating |
INTEGER |
no | V31__student_mvp_enhancements.sql |
|
comment |
TEXT |
yes | V31__student_mvp_enhancements.sql |
|
created_at |
TIMESTAMPTZ |
no | V31__student_mvp_enhancements.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V31__student_mvp_enhancements.sql |
Indexes: idx_course_reviews_course, idx_course_reviews_student, idx_reviews_course, idx_reviews_student
course_tag_assignments · Course authoring · 2 columns · 1 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V70__course_categories_and_tags.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
course_id |
UUID |
no | courses |
V70__course_categories_and_tags.sql |
tag_id |
UUID |
no | course_tags |
V70__course_categories_and_tags.sql |
Indexes: idx_course_tag_assignments_tag
course_tags · Course authoring · 4 columns · 0 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V70__course_categories_and_tags.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V70__course_categories_and_tags.sql |
|
name |
VARCHAR(100) |
no | V70__course_categories_and_tags.sql |
|
slug |
VARCHAR(100) |
no | V70__course_categories_and_tags.sql |
|
created_at |
TIMESTAMPTZ |
no | V70__course_categories_and_tags.sql |
course_tags_legacy · Course authoring · 2 columns · 0 indexes
Purpose: Bảng tag cũ được rename ở V70 để giữ tương thích dữ liệu.
Renamed from: course_tags
Migrations: V1__lms_complete_schema.sql, V70__course_categories_and_tags.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
course_id |
UUID |
no | V1__lms_complete_schema.sql |
|
tag_name |
VARCHAR(100) |
no | V1__lms_complete_schema.sql |
course_versions · Course authoring · 5 columns · 2 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V1__lms_complete_schema.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
course_id |
UUID |
no | V1__lms_complete_schema.sql |
|
version_number |
INTEGER |
no | V1__lms_complete_schema.sql |
|
snapshot_content |
JSONB |
no | V1__lms_complete_schema.sql |
|
published_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
Indexes: idx_course_versions_course_id, idx_course_versions_snapshot_gin
courses · Course authoring · 31 columns · 11 indexes
Purpose: Khóa học draft/metadata của teacher.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V34__add_delivery_mode_to_courses.sql, V37__student_experience.sql, V54.1__production_schema_catchup.sql, V70__course_categories_and_tags.sql, V73__drop_orphaned_new_category_id.sql, V75__align_courses_category_fk_with_course_categories.sql, V76__allow_offline_download.sql, V77__course_content_version_and_stream_video.sql, V92__course_publications_and_version_modes.sql, V96__course_intro_video_assets.sql, V113__course_review_events_and_release_notes.sql, V129__file_attachment_fk.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
code |
VARCHAR(64) |
no | V1__lms_complete_schema.sql |
|
title |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
description |
TEXT |
yes | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
teacher_id |
UUID |
no | V1__lms_complete_schema.sql |
|
category_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
welcome_message |
TEXT |
yes | V1__lms_complete_schema.sql |
|
course_information |
TEXT |
yes | V1__lms_complete_schema.sql |
|
benefits |
TEXT |
yes | V1__lms_complete_schema.sql |
|
intro_video_url |
VARCHAR(500) |
yes | V1__lms_complete_schema.sql |
|
credits |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
visibility |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
price_type |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
price |
NUMERIC(19, 2) |
yes | V1__lms_complete_schema.sql |
|
sale_price |
NUMERIC(19, 2) |
yes | V1__lms_complete_schema.sql |
|
delivery_mode |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
review_comment |
TEXT |
yes | V1__lms_complete_schema.sql |
|
reviewed_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
reviewed_by_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
thumbnail_url |
VARCHAR(500) |
yes | V37__student_experience.sql |
|
new_category_id |
UUID |
yes | course_categories |
V70__course_categories_and_tags.sql |
allow_offline_download |
BOOLEAN |
no | V76__allow_offline_download.sql |
|
content_version |
INTEGER |
no | V77__course_content_version_and_stream_video.sql |
|
draft_change_status |
VARCHAR(32) |
no | V92__course_publications_and_version_modes.sql |
|
intro_video_asset_id |
UUID |
yes | video_assets |
V96__course_intro_video_assets.sql |
pending_release_notes |
TEXT |
yes | V113__course_review_events_and_release_notes.sql |
|
thumbnail_attachment_id |
UUID |
yes | V129__file_attachment_fk.sql |
|
intro_video_attachment_id |
UUID |
yes | V129__file_attachment_fk.sql |
Indexes: idx_courses_category_id, idx_courses_delivery_mode, idx_courses_description_trgm, idx_courses_intro_video_asset_id, idx_courses_intro_video_attachment, idx_courses_new_category, idx_courses_published, idx_courses_status, idx_courses_teacher_id, idx_courses_thumbnail_attachment, idx_courses_title_trgm
email_verification_tokens · Identity/Admin · 6 columns · 2 indexes
Purpose: Bảng thuộc domain Identity/Admin.
Migrations: V50__email_verification_tokens.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V50__email_verification_tokens.sql |
|
user_id |
UUID |
no | users |
V50__email_verification_tokens.sql |
token_hash |
VARCHAR(64) |
no | V50__email_verification_tokens.sql |
|
expires_at |
TIMESTAMPTZ |
no | V50__email_verification_tokens.sql |
|
verified_at |
TIMESTAMPTZ |
yes | V50__email_verification_tokens.sql |
|
created_at |
TIMESTAMPTZ |
no | V50__email_verification_tokens.sql |
Indexes: idx_verification_token_expires, idx_verification_token_user
enrollments · Learning delivery · 12 columns · 6 indexes
Purpose: Ghi danh student vào class/course.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V28__add_foreign_key_constraints.sql, V31__student_mvp_enhancements.sql, V54.1__production_schema_catchup.sql, V55__seed_assessment_enrollments.sql, V58__fix_enrollments_updated_at.sql, V62__optimistic_locking_version_columns.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
class_id |
UUID |
no | V1__lms_complete_schema.sql |
|
student_id |
UUID |
no | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
progress |
JSONB |
yes | V1__lms_complete_schema.sql |
|
completion_percent |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
completed_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
enrolled_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
joined_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
last_accessed_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V58__fix_enrollments_updated_at.sql |
|
version |
BIGINT |
no | V62__optimistic_locking_version_columns.sql |
Indexes: idx_enrollments_active, idx_enrollments_class_id, idx_enrollments_progress_gin, idx_enrollments_status, idx_enrollments_student_id, idx_enrollments_student_status
file_attachments · Media/Upload · 17 columns · 7 indexes
Purpose: Bảng thuộc domain Media/Upload.
Migrations: V1__lms_complete_schema.sql, V30__add_missing_indexes.sql, V54.1__production_schema_catchup.sql, V129__file_attachment_fk.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
entity_type |
VARCHAR(50) |
yes | V1__lms_complete_schema.sql |
|
entity_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
file_category |
VARCHAR(50) |
no | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
yes | V1__lms_complete_schema.sql |
|
stored_filename |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
original_filename |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
storage_path |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
file_size |
BIGINT |
no | V1__lms_complete_schema.sql |
|
content_type |
VARCHAR(100) |
no | V1__lms_complete_schema.sql |
|
uploaded_by |
UUID |
no | V1__lms_complete_schema.sql |
|
uploaded_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
bucket_name |
VARCHAR(100) |
yes | V129__file_attachment_fk.sql |
|
storage_key |
VARCHAR(500) |
yes | V129__file_attachment_fk.sql |
|
sha256_hex |
CHAR(64) |
yes | V129__file_attachment_fk.sql |
|
visibility |
VARCHAR(20) |
yes | V129__file_attachment_fk.sql |
|
deleted_at |
TIMESTAMPTZ |
yes | V129__file_attachment_fk.sql |
Indexes: idx_file_attachments_deleted, idx_file_attachments_entity, idx_file_attachments_entity_type_id, idx_file_attachments_sha256, idx_file_attachments_storage_key, idx_file_attachments_uploaded_by, idx_file_attachments_uploader
grading_audit_log · Assessment · 13 columns · 3 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V101__grading_audit_log.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
BIGSERIAL |
no | V101__grading_audit_log.sql |
|
assignment_id |
UUID |
no | V101__grading_audit_log.sql |
|
submission_id |
UUID |
no | V101__grading_audit_log.sql |
|
student_id |
UUID |
no | V101__grading_audit_log.sql |
|
event_type |
VARCHAR(50) |
no | V101__grading_audit_log.sql |
|
graded_by |
UUID |
no | V101__grading_audit_log.sql |
|
graded_by_name |
VARCHAR(255) |
yes | V101__grading_audit_log.sql |
|
old_grade |
DOUBLE PRECISION |
yes | V101__grading_audit_log.sql |
|
new_grade |
DOUBLE PRECISION |
yes | V101__grading_audit_log.sql |
|
old_feedback |
TEXT |
yes | V101__grading_audit_log.sql |
|
new_feedback |
TEXT |
yes | V101__grading_audit_log.sql |
|
occurred_at |
TIMESTAMPTZ |
no | V101__grading_audit_log.sql |
|
created_at |
TIMESTAMPTZ |
no | V101__grading_audit_log.sql |
Indexes: idx_grading_audit_assignment, idx_grading_audit_student, idx_grading_audit_submission
learning_classes · Publication/Class · 15 columns · 4 indexes
Purpose: Lớp học gắn course/publication và teacher.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V28__add_foreign_key_constraints.sql, V54.1__production_schema_catchup.sql, V55__seed_assessment_enrollments.sql, V92__course_publications_and_version_modes.sql, V94__learning_classes_publication_fk.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
name |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
code |
VARCHAR(64) |
yes | V1__lms_complete_schema.sql |
|
course_version_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
course_id |
UUID |
no | V1__lms_complete_schema.sql |
|
teacher_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
start_date |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
end_date |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
schedule_type |
VARCHAR(20) |
yes | V1__lms_complete_schema.sql |
|
semester |
VARCHAR(50) |
yes | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
max_students |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
version_mode |
VARCHAR(32) |
no | V92__course_publications_and_version_modes.sql |
Indexes: idx_learning_classes_course_id, idx_learning_classes_open, idx_learning_classes_status, idx_learning_classes_teacher_id
learning_events · Learning delivery · 7 columns · 3 indexes
Purpose: Bảng thuộc domain Learning delivery.
Migrations: V38__video_progress_tracking.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V38__video_progress_tracking.sql |
|
student_id |
UUID |
no | users |
V38__video_progress_tracking.sql |
lesson_id |
UUID |
no | V38__video_progress_tracking.sql |
|
section_id |
VARCHAR(255) |
yes | V38__video_progress_tracking.sql |
|
event_type |
VARCHAR(50) |
no | V38__video_progress_tracking.sql |
|
event_data |
JSONB |
yes | V38__video_progress_tracking.sql |
|
created_at |
TIMESTAMPTZ |
no | V38__video_progress_tracking.sql |
Indexes: idx_learning_events_created, idx_learning_events_student, idx_learning_events_type
learning_streaks · Learning delivery · 7 columns · 1 indexes
Purpose: Bảng thuộc domain Learning delivery.
Migrations: V39__gamification_system.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V39__gamification_system.sql |
|
student_id |
UUID |
no | users |
V39__gamification_system.sql |
current_streak |
INTEGER |
no | V39__gamification_system.sql |
|
longest_streak |
INTEGER |
no | V39__gamification_system.sql |
|
last_activity_date |
DATE |
yes | V39__gamification_system.sql |
|
streak_frozen_until |
DATE |
yes | V39__gamification_system.sql |
|
updated_at |
TIMESTAMPTZ |
no | V39__gamification_system.sql |
Indexes: idx_learning_streaks_student
lesson_assignments · Course authoring · 5 columns · 2 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V1__lms_complete_schema.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
lesson_id |
UUID |
no | V1__lms_complete_schema.sql |
|
assignment_id |
UUID |
no | V1__lms_complete_schema.sql |
|
order_index |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
Indexes: idx_lesson_assignments_assignment_id, idx_lesson_assignments_lesson_id
lesson_attachments · Course authoring · 11 columns · 1 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V1__lms_complete_schema.sql, V28__add_foreign_key_constraints.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
lesson_id |
UUID |
no | V1__lms_complete_schema.sql |
|
file_name |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
original_file_name |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
file_url |
VARCHAR(500) |
no | V1__lms_complete_schema.sql |
|
file_size |
BIGINT |
yes | V1__lms_complete_schema.sql |
|
content_type |
VARCHAR(100) |
yes | V1__lms_complete_schema.sql |
|
file_type |
VARCHAR(20) |
yes | V1__lms_complete_schema.sql |
|
display_order |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
uploaded_by |
UUID |
yes | V1__lms_complete_schema.sql |
|
uploaded_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
Indexes: idx_lesson_attachments_lesson_id
lesson_competency_mappings · Competency/STCW · 6 columns · 2 indexes
Purpose: Map lesson với competency.
Migrations: V130__stcw_competency_mapping.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V130__stcw_competency_mapping.sql |
|
lesson_id |
UUID |
no | lessons |
V130__stcw_competency_mapping.sql |
competency_id |
UUID |
no | standard_competencies |
V130__stcw_competency_mapping.sql |
mapped_by |
UUID |
yes | users |
V130__stcw_competency_mapping.sql |
mapped_at |
TIMESTAMPTZ |
no | V130__stcw_competency_mapping.sql |
|
note |
TEXT |
yes | V130__stcw_competency_mapping.sql |
Indexes: idx_mapping_competency, idx_mapping_lesson
lessons · Course authoring · 13 columns · 4 indexes
Purpose: Bài học; content_blocks là cột JSONB, không phải bảng riêng.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V28__add_foreign_key_constraints.sql, V54__seed_users_courses_content.sql, V54.1__production_schema_catchup.sql, V77__course_content_version_and_stream_video.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
chapter_id |
UUID |
no | V1__lms_complete_schema.sql |
|
title |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
description |
TEXT |
yes | V1__lms_complete_schema.sql |
|
lesson_type |
VARCHAR(20) |
yes | V1__lms_complete_schema.sql |
|
video_url |
VARCHAR(500) |
yes | V1__lms_complete_schema.sql |
|
duration_minutes |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
order_index |
INTEGER |
no | V1__lms_complete_schema.sql |
|
is_free |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
content_blocks |
JSONB |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
stream_video_uid |
VARCHAR(255) |
yes | V77__course_content_version_and_stream_video.sql |
Indexes: idx_lessons_chapter_id, idx_lessons_chapter_order, idx_lessons_content_blocks_gin, idx_lessons_order
login_attempts · Identity/Admin · 7 columns · 4 indexes
Purpose: Bảng thuộc domain Identity/Admin.
Migrations: V1__lms_complete_schema.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
BIGSERIAL |
no | V1__lms_complete_schema.sql |
|
email |
VARCHAR(100) |
no | V1__lms_complete_schema.sql |
|
ip_address |
INET |
yes | V1__lms_complete_schema.sql |
|
success |
BOOLEAN |
no | V1__lms_complete_schema.sql |
|
failure_reason |
VARCHAR(255) |
yes | V1__lms_complete_schema.sql |
|
user_agent |
VARCHAR(512) |
yes | V1__lms_complete_schema.sql |
|
attempted_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
Indexes: idx_login_attempts_at_brin, idx_login_attempts_email, idx_login_attempts_ip, idx_login_attempts_recent_failures
maritime_standards · Competency/STCW · 7 columns · 0 indexes
Purpose: Chuẩn hàng hải như STCW/SOLAS/MARPOL/COLREGs.
Migrations: V130__stcw_competency_mapping.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V130__stcw_competency_mapping.sql |
|
code |
VARCHAR(20) |
no | V130__stcw_competency_mapping.sql |
|
name |
VARCHAR(100) |
no | V130__stcw_competency_mapping.sql |
|
description |
TEXT |
yes | V130__stcw_competency_mapping.sql |
|
is_active |
BOOLEAN |
no | V130__stcw_competency_mapping.sql |
|
created_at |
TIMESTAMPTZ |
no | V130__stcw_competency_mapping.sql |
|
updated_at |
TIMESTAMPTZ |
no | V130__stcw_competency_mapping.sql |
message_reactions · Communication · 5 columns · 1 indexes
Purpose: Bảng thuộc domain Communication.
Migrations: V108__message_reactions.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V108__message_reactions.sql |
|
message_id |
UUID |
no | messages |
V108__message_reactions.sql |
user_id |
UUID |
no | users |
V108__message_reactions.sql |
emoji |
VARCHAR(10) |
no | V108__message_reactions.sql |
|
created_at |
TIMESTAMPTZ |
no | V108__message_reactions.sql |
Indexes: idx_message_reactions_message
messages · Communication · 10 columns · 6 indexes
Purpose: Bảng thuộc domain Communication.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V54.1__production_schema_catchup.sql, V78__messages_unread_performance_index.sql, V109__message_recall.sql, V110__message_content_nullable.sql, V111__message_reply.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
conversation_id |
UUID |
no | V1__lms_complete_schema.sql |
|
sender_id |
UUID |
no | V1__lms_complete_schema.sql |
|
content |
TEXT |
no | V1__lms_complete_schema.sql |
|
is_read |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
read_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
sent_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
recalled |
BOOLEAN |
no | V109__message_recall.sql |
|
recalled_at |
TIMESTAMPTZ |
yes | V109__message_recall.sql |
|
reply_to_id |
UUID |
yes | messages |
V111__message_reply.sql |
Indexes: idx_messages_conversation_id, idx_messages_reply_to, idx_messages_sender_id, idx_messages_sent_at, idx_messages_sent_at_brin, idx_messages_unread_by_conv_sender
notifications · Learning delivery · 8 columns · 2 indexes
Purpose: Bảng thuộc domain Learning delivery.
Migrations: V39__gamification_system.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V39__gamification_system.sql |
|
user_id |
UUID |
no | users |
V39__gamification_system.sql |
type |
VARCHAR(50) |
no | V39__gamification_system.sql |
|
title |
VARCHAR(255) |
no | V39__gamification_system.sql |
|
message |
TEXT |
yes | V39__gamification_system.sql |
|
link |
VARCHAR(500) |
yes | V39__gamification_system.sql |
|
is_read |
BOOLEAN |
no | V39__gamification_system.sql |
|
created_at |
TIMESTAMPTZ |
no | V39__gamification_system.sql |
Indexes: idx_notifications_unread, idx_notifications_user
org_payment_configs · Payment/Revenue · 5 columns · 0 indexes
Purpose: Bảng thuộc domain Payment/Revenue.
Migrations: V81__org_payment_configs.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
org_id |
UUID |
no | organizations |
V81__org_payment_configs.sql |
platform_fee_pct |
NUMERIC(5,2) |
no | V81__org_payment_configs.sql |
|
teacher_share_pct |
NUMERIC(5,2) |
no | V81__org_payment_configs.sql |
|
min_payout_amount |
NUMERIC(19,2) |
no | V81__org_payment_configs.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V81__org_payment_configs.sql |
organization_invites · Identity/Admin · 13 columns · 4 indexes
Purpose: Bảng thuộc domain Identity/Admin.
Migrations: V64__organizations_and_invites.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V64__organizations_and_invites.sql |
|
organization_id |
UUID |
no | organizations |
V64__organizations_and_invites.sql |
type |
VARCHAR(20) |
no | V64__organizations_and_invites.sql |
|
code |
VARCHAR(12) |
yes | V64__organizations_and_invites.sql |
|
email |
VARCHAR(255) |
yes | V64__organizations_and_invites.sql |
|
token_hash |
VARCHAR(128) |
yes | V64__organizations_and_invites.sql |
|
max_uses |
INT |
yes | V64__organizations_and_invites.sql |
|
use_count |
INT |
no | V64__organizations_and_invites.sql |
|
status |
VARCHAR(20) |
no | V64__organizations_and_invites.sql |
|
expires_at |
TIMESTAMPTZ |
no | V64__organizations_and_invites.sql |
|
created_by |
UUID |
no | users |
V64__organizations_and_invites.sql |
created_at |
TIMESTAMPTZ |
no | V64__organizations_and_invites.sql |
|
version |
INT |
no | V64__organizations_and_invites.sql |
Indexes: idx_org_invites_code, idx_org_invites_expires, idx_org_invites_org_id, idx_org_invites_token
organizations · Identity/Admin · 10 columns · 2 indexes
Purpose: Bảng thuộc domain Identity/Admin.
Migrations: V64__organizations_and_invites.sql, V69__user_token_expiry_days.sql, V119__multi_org_foundation.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V64__organizations_and_invites.sql |
|
name |
VARCHAR(255) |
no | V64__organizations_and_invites.sql |
|
code |
VARCHAR(50) |
no | V64__organizations_and_invites.sql |
|
description |
TEXT |
yes | V64__organizations_and_invites.sql |
|
enabled |
BOOLEAN |
no | V64__organizations_and_invites.sql |
|
token_expiry_days |
INT |
no | V64__organizations_and_invites.sql |
|
created_at |
TIMESTAMPTZ |
no | V64__organizations_and_invites.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V64__organizations_and_invites.sql |
|
type |
VARCHAR(32) |
no | V119__multi_org_foundation.sql |
|
is_default |
BOOLEAN |
no | V119__multi_org_foundation.sql |
Indexes: idx_organizations_code, uq_organizations_default
outbox_messages · Shared/System · 11 columns · 3 indexes
Purpose: Bảng thuộc domain Shared/System.
Migrations: V1__lms_complete_schema.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
aggregate_type |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
aggregate_id |
UUID |
no | V1__lms_complete_schema.sql |
|
event_type |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
payload |
JSONB |
no | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
yes | V1__lms_complete_schema.sql |
|
attempts |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
next_attempt_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
last_error |
TEXT |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
processed_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_outbox_created_at_brin, idx_outbox_payload_gin, idx_outbox_pending
packages · Assessment · 15 columns · 3 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V36__question_bank_foundation.sql, V54.1__production_schema_catchup.sql, V55__seed_assessment_enrollments.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
name |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
description |
TEXT |
yes | V1__lms_complete_schema.sql |
|
subject |
VARCHAR(100) |
yes | V1__lms_complete_schema.sql |
|
owner_id |
UUID |
no | V1__lms_complete_schema.sql |
|
visibility |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
capacity |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
price |
NUMERIC(19, 2) |
yes | V1__lms_complete_schema.sql |
|
duration_days |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
is_active |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
bank_type |
VARCHAR(20) |
yes | V36__question_bank_foundation.sql |
|
status |
VARCHAR(20) |
yes | V36__question_bank_foundation.sql |
|
question_count |
INTEGER |
yes | V36__question_bank_foundation.sql |
Indexes: idx_packages_bank_type, idx_packages_owner_id, idx_packages_status
password_reset_tokens · Identity/Admin · 6 columns · 2 indexes
Purpose: Bảng thuộc domain Identity/Admin.
Migrations: V46__password_reset_tokens.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V46__password_reset_tokens.sql |
|
user_id |
UUID |
no | users |
V46__password_reset_tokens.sql |
token_hash |
VARCHAR(64) |
no | V46__password_reset_tokens.sql |
|
expires_at |
TIMESTAMPTZ |
no | V46__password_reset_tokens.sql |
|
used_at |
TIMESTAMPTZ |
yes | V46__password_reset_tokens.sql |
|
created_at |
TIMESTAMPTZ |
no | V46__password_reset_tokens.sql |
Indexes: idx_reset_token_expires, idx_reset_token_user
payment_transactions · Payment/Revenue · 21 columns · 6 indexes
Purpose: Giao dịch VNPay/SePay/simulated và refund.
Migrations: V31__student_mvp_enhancements.sql, V47__vnpay_payment_fields.sql, V54.1__production_schema_catchup.sql, V59__payment_version_column.sql, V60__payment_refund_fields.sql, V61__payment_indexes.sql, V63__refund_status_index.sql, V80__add_sepay_payment_fields.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V31__student_mvp_enhancements.sql |
|
student_id |
UUID |
no | users |
V31__student_mvp_enhancements.sql |
course_id |
UUID |
no | courses |
V31__student_mvp_enhancements.sql |
amount |
NUMERIC(19,2) |
no | V31__student_mvp_enhancements.sql |
|
currency |
VARCHAR(3) |
no | V31__student_mvp_enhancements.sql |
|
payment_method |
VARCHAR(50) |
no | V31__student_mvp_enhancements.sql |
|
transaction_id |
VARCHAR(255) |
no | V31__student_mvp_enhancements.sql |
|
status |
VARCHAR(20) |
no | V31__student_mvp_enhancements.sql |
|
paid_at |
TIMESTAMPTZ |
yes | V31__student_mvp_enhancements.sql |
|
created_at |
TIMESTAMPTZ |
no | V31__student_mvp_enhancements.sql |
|
vnp_transaction_no |
VARCHAR(50) |
yes | V47__vnpay_payment_fields.sql |
|
vnp_bank_code |
VARCHAR(20) |
yes | V47__vnpay_payment_fields.sql |
|
vnp_response_code |
VARCHAR(5) |
yes | V47__vnpay_payment_fields.sql |
|
vnp_card_type |
VARCHAR(20) |
yes | V47__vnpay_payment_fields.sql |
|
version |
BIGINT |
yes | V59__payment_version_column.sql |
|
refund_status |
VARCHAR(20) |
yes | V60__payment_refund_fields.sql |
|
refund_requested_at |
TIMESTAMP |
yes | V60__payment_refund_fields.sql |
|
refund_completed_at |
TIMESTAMP |
yes | V60__payment_refund_fields.sql |
|
refund_reason |
TEXT |
yes | V60__payment_refund_fields.sql |
|
refund_admin_note |
TEXT |
yes | V60__payment_refund_fields.sql |
|
sepay_transaction_code |
VARCHAR(100) |
yes | V80__add_sepay_payment_fields.sql |
Indexes: idx_payment_student_course, idx_payment_transaction_id, idx_payment_transactions_course_id, idx_payment_transactions_paid_at, idx_payment_transactions_refund_status, idx_payment_transactions_status
payout_requests · Payment/Revenue · 10 columns · 2 indexes
Purpose: Bảng thuộc domain Payment/Revenue.
Migrations: V84__payout_requests.sql, V88__allow_cancelled_payout_status.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V84__payout_requests.sql |
|
teacher_id |
UUID |
no | users |
V84__payout_requests.sql |
bank_account_id |
UUID |
no | teacher_bank_accounts |
V84__payout_requests.sql |
amount |
NUMERIC(19,2) |
no | V84__payout_requests.sql |
|
status |
VARCHAR(20) |
no | V84__payout_requests.sql |
|
teacher_note |
TEXT |
yes | V84__payout_requests.sql |
|
admin_note |
TEXT |
yes | V84__payout_requests.sql |
|
processed_by |
UUID |
yes | users |
V84__payout_requests.sql |
requested_at |
TIMESTAMPTZ |
no | V84__payout_requests.sql |
|
processed_at |
TIMESTAMPTZ |
yes | V84__payout_requests.sql |
Indexes: idx_payout_requests_status, idx_payout_requests_teacher
question_bank_categories · Assessment · 9 columns · 2 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V36__question_bank_foundation.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V36__question_bank_foundation.sql |
|
bank_id |
UUID |
no | packages |
V36__question_bank_foundation.sql |
parent_id |
UUID |
yes | question_bank_categories |
V36__question_bank_foundation.sql |
name |
VARCHAR(255) |
no | V36__question_bank_foundation.sql |
|
description |
TEXT |
yes | V36__question_bank_foundation.sql |
|
sort_order |
INTEGER |
no | V36__question_bank_foundation.sql |
|
question_count |
INTEGER |
no | V36__question_bank_foundation.sql |
|
created_at |
TIMESTAMPTZ |
no | V36__question_bank_foundation.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V36__question_bank_foundation.sql |
Indexes: idx_qbc_bank_id, idx_qbc_parent_id
question_options · Assessment · 8 columns · 1 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V28__add_foreign_key_constraints.sql, V55__seed_assessment_enrollments.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
question_id |
UUID |
no | V1__lms_complete_schema.sql |
|
option_key |
VARCHAR(10) |
no | V1__lms_complete_schema.sql |
|
content |
JSONB |
yes | V1__lms_complete_schema.sql |
|
is_correct |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
display_order |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_question_options_question_id
questions · Assessment · 16 columns · 10 indexes
Purpose: Ngân hàng câu hỏi; content_blocks là cột JSONB cho rich question.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V28__add_foreign_key_constraints.sql, V35__quiz_multi_question_type.sql, V36__question_bank_foundation.sql, V54.1__production_schema_catchup.sql, V55__seed_assessment_enrollments.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
content_blocks |
JSONB |
yes | V1__lms_complete_schema.sql |
|
difficulty |
VARCHAR(10) |
no | V1__lms_complete_schema.sql |
|
tags |
TEXT |
yes | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
correct_option |
VARCHAR(10) |
no | V1__lms_complete_schema.sql |
|
created_by |
UUID |
no | V1__lms_complete_schema.sql |
|
course_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
package_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
usage_count |
INTEGER |
no | V1__lms_complete_schema.sql |
|
correct_rate |
NUMERIC(5, 2) |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
answer_key |
JSONB |
yes | V35__quiz_multi_question_type.sql |
|
category_id |
UUID |
yes | V36__question_bank_foundation.sql |
|
question_type |
VARCHAR(30) |
no | V54.1__production_schema_catchup.sql |
Indexes: idx_questions_active, idx_questions_answer_key, idx_questions_category_id, idx_questions_content_blocks_gin, idx_questions_course_id, idx_questions_created_by, idx_questions_package_id, idx_questions_question_type, idx_questions_status, idx_questions_tags_trgm
quiz_assignments · Assessment · 7 columns · 2 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
quiz_id |
UUID |
no | V1__lms_complete_schema.sql |
|
class_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
course_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
due_date |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
is_active |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
assigned_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
Indexes: idx_quiz_assignments_class_id, idx_quiz_assignments_quiz_id
quiz_attempts · Assessment · 12 columns · 5 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V42__quiz_attempt_optimistic_locking.sql, V51__quiz_attempt_is_passed.sql, V54.1__production_schema_catchup.sql, V104__quiz_attempts_add_timeout_status.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
quiz_id |
UUID |
no | V1__lms_complete_schema.sql |
|
student_id |
UUID |
no | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
answers |
JSONB |
yes | V1__lms_complete_schema.sql |
|
score |
DOUBLE PRECISION |
yes | V1__lms_complete_schema.sql |
|
max_score |
DOUBLE PRECISION |
yes | V1__lms_complete_schema.sql |
|
started_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
submitted_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
version |
BIGINT |
no | V42__quiz_attempt_optimistic_locking.sql |
|
is_passed |
BOOLEAN |
yes | V51__quiz_attempt_is_passed.sql |
Indexes: idx_quiz_attempts_answers_gin, idx_quiz_attempts_created_at_brin, idx_quiz_attempts_quiz_id, idx_quiz_attempts_student_id, idx_quiz_attempts_student_quiz
quiz_questions · Assessment · 7 columns · 2 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V28__add_foreign_key_constraints.sql, V54.1__production_schema_catchup.sql, V55__seed_assessment_enrollments.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
quiz_id |
UUID |
no | V1__lms_complete_schema.sql |
|
question_id |
UUID |
no | V1__lms_complete_schema.sql |
|
display_order |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
points |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_quiz_questions_question_id, idx_quiz_questions_quiz_id
quizzes · Assessment · 21 columns · 5 indexes
Purpose: Bảng thuộc domain Assessment.
Migrations: V1__lms_complete_schema.sql, V27__add_performance_indexes.sql, V28__add_foreign_key_constraints.sql, V52__quiz_availability_dates.sql, V53__quiz_availability_indexes.sql, V54.1__production_schema_catchup.sql, V55__seed_assessment_enrollments.sql, V91__quiz_assessment_metadata.sql, V102__quiz_access_password.sql, V105__quiz_max_score_scale.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
lesson_id |
UUID |
no | V1__lms_complete_schema.sql |
|
title |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
description |
TEXT |
yes | V1__lms_complete_schema.sql |
|
time_limit_minutes |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
max_attempts |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
passing_score |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
shuffle_questions |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
shuffle_options |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
show_results_immediately |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
show_correct_answers |
BOOLEAN |
yes | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
available_from |
TIMESTAMPTZ |
yes | V52__quiz_availability_dates.sql |
|
due_at |
TIMESTAMPTZ |
yes | V52__quiz_availability_dates.sql |
|
lock_at |
TIMESTAMPTZ |
yes | V52__quiz_availability_dates.sql |
|
quiz_type |
VARCHAR(30) |
no | V91__quiz_assessment_metadata.sql |
|
counts_toward_certificate |
BOOLEAN |
no | V91__quiz_assessment_metadata.sql |
|
access_password |
VARCHAR(50) |
yes | V102__quiz_access_password.sql |
|
max_score_scale |
DOUBLE PRECISION |
yes | V105__quiz_max_score_scale.sql |
Indexes: idx_quizzes_available_from, idx_quizzes_lesson_id, idx_quizzes_lock_at, idx_quizzes_published, idx_quizzes_status
revenue_splits · Payment/Revenue · 13 columns · 3 indexes
Purpose: Bảng thuộc domain Payment/Revenue.
Migrations: V83__revenue_splits.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V83__revenue_splits.sql |
|
payment_id |
UUID |
no | payment_transactions |
V83__revenue_splits.sql |
course_id |
UUID |
no | V83__revenue_splits.sql |
|
teacher_id |
UUID |
no | users |
V83__revenue_splits.sql |
org_id |
UUID |
yes | organizations |
V83__revenue_splits.sql |
gross_amount |
NUMERIC(19,2) |
no | V83__revenue_splits.sql |
|
platform_fee_pct |
NUMERIC(5,2) |
no | V83__revenue_splits.sql |
|
teacher_share_pct |
NUMERIC(5,2) |
no | V83__revenue_splits.sql |
|
org_share_pct |
NUMERIC(5,2) |
no | V83__revenue_splits.sql |
|
platform_amount |
NUMERIC(19,2) |
no | V83__revenue_splits.sql |
|
teacher_amount |
NUMERIC(19,2) |
no | V83__revenue_splits.sql |
|
org_amount |
NUMERIC(19,2) |
no | V83__revenue_splits.sql |
|
created_at |
TIMESTAMPTZ |
no | V83__revenue_splits.sql |
Indexes: idx_revenue_splits_created, idx_revenue_splits_org, idx_revenue_splits_teacher
sections · Course authoring · 12 columns · 1 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V1__lms_complete_schema.sql, V134__allow_simulation_sections.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
lesson_id |
UUID |
no | V1__lms_complete_schema.sql |
|
title |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
type |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
content |
TEXT |
yes | V1__lms_complete_schema.sql |
|
video_url |
VARCHAR(500) |
yes | V1__lms_complete_schema.sql |
|
file_url |
VARCHAR(500) |
yes | V1__lms_complete_schema.sql |
|
is_required |
BOOLEAN |
no | V1__lms_complete_schema.sql |
|
duration |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
order_index |
INTEGER |
no | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_sections_lesson_id
standard_competencies · Competency/STCW · 10 columns · 2 indexes
Purpose: Năng lực/competency theo chuẩn.
Migrations: V130__stcw_competency_mapping.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V130__stcw_competency_mapping.sql |
|
standard_id |
UUID |
no | maritime_standards |
V130__stcw_competency_mapping.sql |
code |
VARCHAR(30) |
no | V130__stcw_competency_mapping.sql |
|
title |
VARCHAR(200) |
no | V130__stcw_competency_mapping.sql |
|
description |
TEXT |
yes | V130__stcw_competency_mapping.sql |
|
category |
VARCHAR(100) |
yes | V130__stcw_competency_mapping.sql |
|
display_order |
INT |
no | V130__stcw_competency_mapping.sql |
|
is_active |
BOOLEAN |
no | V130__stcw_competency_mapping.sql |
|
created_at |
TIMESTAMPTZ |
no | V130__stcw_competency_mapping.sql |
|
updated_at |
TIMESTAMPTZ |
no | V130__stcw_competency_mapping.sql |
Indexes: idx_competency_category, idx_competency_standard
student_achievements · Learning delivery · 4 columns · 1 indexes
Purpose: Bảng thuộc domain Learning delivery.
Migrations: V39__gamification_system.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V39__gamification_system.sql |
|
student_id |
UUID |
no | users |
V39__gamification_system.sql |
achievement_id |
UUID |
no | achievements |
V39__gamification_system.sql |
earned_at |
TIMESTAMPTZ |
no | V39__gamification_system.sql |
Indexes: idx_student_achievements_student
student_lesson_progress · Learning delivery · 10 columns · 4 indexes
Purpose: Tiến độ granular theo bài học.
Migrations: V1__lms_complete_schema.sql, V28__add_foreign_key_constraints.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
student_id |
UUID |
no | V1__lms_complete_schema.sql |
|
lesson_id |
UUID |
no | V1__lms_complete_schema.sql |
|
enrollment_id |
UUID |
yes | V1__lms_complete_schema.sql |
|
status |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
watch_time_seconds |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
completion_percent |
INTEGER |
yes | V1__lms_complete_schema.sql |
|
completed_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
started_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
last_accessed_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
Indexes: idx_slp_enrollment_id, idx_slp_lesson_id, idx_slp_student_id, idx_student_lesson_progress_lesson_id
student_notes · Learning delivery · 10 columns · 2 indexes
Purpose: Bảng thuộc domain Learning delivery.
Migrations: V49__student_notes.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V49__student_notes.sql |
|
user_id |
UUID |
no | users |
V49__student_notes.sql |
course_id |
UUID |
no | courses |
V49__student_notes.sql |
lesson_id |
UUID |
yes | lessons |
V49__student_notes.sql |
title |
VARCHAR(255) |
no | V49__student_notes.sql |
|
content |
TEXT |
no | V49__student_notes.sql |
|
tags |
TEXT[] |
yes | V49__student_notes.sql |
|
is_public |
BOOLEAN |
yes | V49__student_notes.sql |
|
created_at |
TIMESTAMPTZ |
yes | V49__student_notes.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V49__student_notes.sql |
Indexes: idx_notes_user, idx_notes_user_course
teacher_bank_accounts · Payment/Revenue · 8 columns · 2 indexes
Purpose: Bảng thuộc domain Payment/Revenue.
Migrations: V82__teacher_bank_accounts.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V82__teacher_bank_accounts.sql |
|
teacher_id |
UUID |
no | users |
V82__teacher_bank_accounts.sql |
bank_code |
VARCHAR(20) |
no | V82__teacher_bank_accounts.sql |
|
account_number |
VARCHAR(30) |
no | V82__teacher_bank_accounts.sql |
|
account_name |
VARCHAR(100) |
no | V82__teacher_bank_accounts.sql |
|
is_default |
BOOLEAN |
no | V82__teacher_bank_accounts.sql |
|
verified |
BOOLEAN |
no | V82__teacher_bank_accounts.sql |
|
created_at |
TIMESTAMPTZ |
no | V82__teacher_bank_accounts.sql |
Indexes: idx_teacher_bank_accounts_default, idx_teacher_bank_accounts_teacher
teacher_invitations · Course authoring · 12 columns · 2 indexes
Purpose: Bảng thuộc domain Course authoring.
Migrations: V43__teacher_invitations.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V43__teacher_invitations.sql |
|
course_id |
UUID |
no | courses |
V43__teacher_invitations.sql |
invited_by_id |
UUID |
no | users |
V43__teacher_invitations.sql |
invited_teacher_id |
UUID |
no | users |
V43__teacher_invitations.sql |
status |
VARCHAR(20) |
no | V43__teacher_invitations.sql |
|
message |
TEXT |
yes | V43__teacher_invitations.sql |
|
can_edit_content |
BOOLEAN |
no | V43__teacher_invitations.sql |
|
can_manage_students |
BOOLEAN |
no | V43__teacher_invitations.sql |
|
can_view_analytics |
BOOLEAN |
no | V43__teacher_invitations.sql |
|
can_manage_settings |
BOOLEAN |
no | V43__teacher_invitations.sql |
|
created_at |
TIMESTAMP |
no | V43__teacher_invitations.sql |
|
responded_at |
TIMESTAMP |
yes | V43__teacher_invitations.sql |
Indexes: idx_teacher_invitations_course, idx_teacher_invitations_invited
upload_sessions · Media/Upload · 13 columns · 3 indexes
Purpose: Phiên presigned upload lên storage.
Migrations: V74__upload_sessions.sql, V98__upload_sessions_multipart_support.sql, V99__upload_sessions_multipart_upload_id_text.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V74__upload_sessions.sql |
|
storage_key |
VARCHAR(500) |
no | V74__upload_sessions.sql |
|
user_id |
UUID |
no | users |
V74__upload_sessions.sql |
content_type |
VARCHAR(100) |
no | V74__upload_sessions.sql |
|
declared_size |
BIGINT |
no | V74__upload_sessions.sql |
|
folder |
VARCHAR(100) |
no | V74__upload_sessions.sql |
|
status |
VARCHAR(20) |
no | V74__upload_sessions.sql |
|
created_at |
TIMESTAMPTZ |
no | V74__upload_sessions.sql |
|
confirmed_at |
TIMESTAMPTZ |
yes | V74__upload_sessions.sql |
|
expires_at |
TIMESTAMPTZ |
yes | V74__upload_sessions.sql |
|
upload_strategy |
VARCHAR(20) |
no | V98__upload_sessions_multipart_support.sql |
|
multipart_upload_id |
VARCHAR(255) |
yes | V98__upload_sessions_multipart_support.sql |
|
multipart_completed_at |
TIMESTAMPTZ |
yes | V98__upload_sessions_multipart_support.sql |
Indexes: idx_upload_sessions_expires, idx_upload_sessions_key_user, idx_upload_sessions_strategy_status
user_external_identities · Identity/Admin · 10 columns · 3 indexes
Purpose: Bảng thuộc domain Identity/Admin.
Migrations: V114__user_external_identities.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V114__user_external_identities.sql |
|
user_id |
UUID |
no | users |
V114__user_external_identities.sql |
provider |
VARCHAR(32) |
no | V114__user_external_identities.sql |
|
external_subject |
VARCHAR(255) |
no | V114__user_external_identities.sql |
|
email_at_link |
VARCHAR(255) |
no | V114__user_external_identities.sql |
|
email_verified_at |
TIMESTAMPTZ |
yes | V114__user_external_identities.sql |
|
linked_at |
TIMESTAMPTZ |
no | V114__user_external_identities.sql |
|
last_login_at |
TIMESTAMPTZ |
yes | V114__user_external_identities.sql |
|
created_at |
TIMESTAMPTZ |
no | V114__user_external_identities.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V114__user_external_identities.sql |
Indexes: idx_user_external_identities_user_id, uq_user_external_identities_provider_subject, uq_user_external_identities_user_provider
users · Identity/Admin · 19 columns · 6 indexes
Purpose: Tài khoản, role, org, trạng thái và hồ sơ người dùng.
Migrations: V1__lms_complete_schema.sql, V39__gamification_system.sql, V40__add_org_admin_role.sql, V54.1__production_schema_catchup.sql, V64__organizations_and_invites.sql, V69__user_token_expiry_days.sql, V106__user_avatar_url.sql, V116__add_must_change_password_to_users.sql, V118__user_account_status.sql, V119__multi_org_foundation.sql, V129__file_attachment_fk.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V1__lms_complete_schema.sql |
|
username |
VARCHAR(50) |
no | V1__lms_complete_schema.sql |
|
email |
VARCHAR(100) |
no | V1__lms_complete_schema.sql |
|
password |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
full_name |
VARCHAR(255) |
no | V1__lms_complete_schema.sql |
|
role |
VARCHAR(20) |
no | V1__lms_complete_schema.sql |
|
enabled |
BOOLEAN |
no | V1__lms_complete_schema.sql |
|
created_at |
TIMESTAMPTZ |
no | V1__lms_complete_schema.sql |
|
updated_at |
TIMESTAMPTZ |
yes | V1__lms_complete_schema.sql |
|
career_goal |
VARCHAR(255) |
yes | V39__gamification_system.sql |
|
daily_goal_minutes |
INTEGER |
yes | V39__gamification_system.sql |
|
organization_id |
UUID |
yes | organizations |
V64__organizations_and_invites.sql |
token_expiry_days |
INT |
yes | V69__user_token_expiry_days.sql |
|
avatar_url |
VARCHAR(500) |
yes | V106__user_avatar_url.sql |
|
must_change_password |
BOOLEAN |
no | V116__add_must_change_password_to_users.sql |
|
account_status |
VARCHAR(16) |
yes | V118__user_account_status.sql |
|
status_reason |
TEXT |
yes | V118__user_account_status.sql |
|
status_updated_at |
TIMESTAMPTZ |
yes | V118__user_account_status.sql |
|
avatar_attachment_id |
UUID |
yes | V129__file_attachment_fk.sql |
Indexes: idx_users_account_status_non_active, idx_users_avatar_attachment, idx_users_email_lower, idx_users_fullname_trgm, idx_users_organization_id, idx_users_role
video_assets · Media/Upload · 32 columns · 8 indexes
Purpose: Tài sản video nguồn/adaptive/offline.
Migrations: V95__video_assets_and_renditions.sql, V97__video_assets_adaptive_playback.sql, V117__video_assets_source_attachment_unique.sql, V135__video_storage_governance.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V95__video_assets_and_renditions.sql |
|
owner_id |
UUID |
no | users |
V95__video_assets_and_renditions.sql |
source_attachment_id |
UUID |
no | file_attachments |
V95__video_assets_and_renditions.sql |
source_storage_key |
VARCHAR(500) |
no | V95__video_assets_and_renditions.sql |
|
source_file_url |
TEXT |
no | V95__video_assets_and_renditions.sql |
|
original_file_name |
VARCHAR(500) |
no | V95__video_assets_and_renditions.sql |
|
content_type |
VARCHAR(150) |
no | V95__video_assets_and_renditions.sql |
|
source_file_size |
BIGINT |
no | V95__video_assets_and_renditions.sql |
|
source_kind |
VARCHAR(50) |
no | V95__video_assets_and_renditions.sql |
|
status |
VARCHAR(30) |
no | V95__video_assets_and_renditions.sql |
|
stream_video_uid |
VARCHAR(255) |
yes | V95__video_assets_and_renditions.sql |
|
playback_url |
TEXT |
yes | V95__video_assets_and_renditions.sql |
|
duration_seconds |
INTEGER |
yes | V95__video_assets_and_renditions.sql |
|
width |
INTEGER |
yes | V95__video_assets_and_renditions.sql |
|
height |
INTEGER |
yes | V95__video_assets_and_renditions.sql |
|
error_message |
TEXT |
yes | V95__video_assets_and_renditions.sql |
|
processing_started_at |
TIMESTAMPTZ |
yes | V95__video_assets_and_renditions.sql |
|
processed_at |
TIMESTAMPTZ |
yes | V95__video_assets_and_renditions.sql |
|
created_at |
TIMESTAMPTZ |
no | V95__video_assets_and_renditions.sql |
|
updated_at |
TIMESTAMPTZ |
no | V95__video_assets_and_renditions.sql |
|
adaptive_packaging_status |
VARCHAR(30) |
no | V97__video_assets_adaptive_playback.sql |
|
hls_manifest_storage_key |
VARCHAR(500) |
yes | V97__video_assets_adaptive_playback.sql |
|
dash_manifest_storage_key |
VARCHAR(500) |
yes | V97__video_assets_adaptive_playback.sql |
|
adaptive_packaged_at |
TIMESTAMPTZ |
yes | V97__video_assets_adaptive_playback.sql |
|
adaptive_error_message |
TEXT |
yes | V97__video_assets_adaptive_playback.sql |
|
content_sha256 |
VARCHAR(64) |
yes | V135__video_storage_governance.sql |
|
content_fingerprint_status |
VARCHAR(30) |
no | V135__video_storage_governance.sql |
|
duplicate_of_asset_id |
UUID |
yes | video_assets |
V135__video_storage_governance.sql |
package_size_bytes |
BIGINT |
yes | V135__video_storage_governance.sql |
|
storage_state |
VARCHAR(30) |
no | V135__video_storage_governance.sql |
|
storage_deleted_at |
TIMESTAMPTZ |
yes | V135__video_storage_governance.sql |
|
source_retained |
BOOLEAN |
no | V135__video_storage_governance.sql |
Indexes: idx_video_assets_adaptive_status, idx_video_assets_content_sha256, idx_video_assets_duplicate_of_asset_id, idx_video_assets_owner_id, idx_video_assets_source_attachment_id, idx_video_assets_status, idx_video_assets_storage_state, uq_video_assets_source_attachment
video_ingest_jobs · Media/Upload · 10 columns · 2 indexes
Purpose: Job ingest/package video cho worker.
Migrations: V95__video_assets_and_renditions.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V95__video_assets_and_renditions.sql |
|
video_asset_id |
UUID |
no | video_assets |
V95__video_assets_and_renditions.sql |
status |
VARCHAR(30) |
no | V95__video_assets_and_renditions.sql |
|
attempt_count |
INTEGER |
no | V95__video_assets_and_renditions.sql |
|
last_error |
TEXT |
yes | V95__video_assets_and_renditions.sql |
|
started_at |
TIMESTAMPTZ |
yes | V95__video_assets_and_renditions.sql |
|
finished_at |
TIMESTAMPTZ |
yes | V95__video_assets_and_renditions.sql |
|
next_run_at |
TIMESTAMPTZ |
no | V95__video_assets_and_renditions.sql |
|
created_at |
TIMESTAMPTZ |
no | V95__video_assets_and_renditions.sql |
|
updated_at |
TIMESTAMPTZ |
no | V95__video_assets_and_renditions.sql |
Indexes: idx_video_ingest_jobs_asset_id, idx_video_ingest_jobs_status_next_run
video_progress · Learning delivery · 12 columns · 3 indexes
Purpose: Bảng thuộc domain Learning delivery.
Migrations: V38__video_progress_tracking.sql, V54.1__production_schema_catchup.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V38__video_progress_tracking.sql |
|
student_id |
UUID |
no | users |
V38__video_progress_tracking.sql |
lesson_id |
UUID |
no | V38__video_progress_tracking.sql |
|
section_id |
VARCHAR(255) |
no | V38__video_progress_tracking.sql |
|
duration_seconds |
INTEGER |
no | V38__video_progress_tracking.sql |
|
watched_segments |
BYTEA |
yes | V38__video_progress_tracking.sql |
|
watched_seconds |
INTEGER |
no | V38__video_progress_tracking.sql |
|
progress_percent |
DOUBLE PRECISION |
no | V38__video_progress_tracking.sql |
|
completed |
BOOLEAN |
no | V38__video_progress_tracking.sql |
|
last_position |
DOUBLE PRECISION |
no | V38__video_progress_tracking.sql |
|
created_at |
TIMESTAMPTZ |
no | V38__video_progress_tracking.sql |
|
updated_at |
TIMESTAMPTZ |
no | V38__video_progress_tracking.sql |
Indexes: idx_video_progress_lesson, idx_video_progress_student, idx_video_progress_student_lesson
video_renditions · Media/Upload · 11 columns · 2 indexes
Purpose: Bảng thuộc domain Media/Upload.
Migrations: V95__video_assets_and_renditions.sql
| Column | Type | Nullable | References | Source migration |
|---|---|---|---|---|
id |
UUID |
no | V95__video_assets_and_renditions.sql |
|
video_asset_id |
UUID |
no | video_assets |
V95__video_assets_and_renditions.sql |
playback_kind |
VARCHAR(30) |
no | V95__video_assets_and_renditions.sql |
|
profile |
VARCHAR(30) |
no | V95__video_assets_and_renditions.sql |
|
actual_resolution |
VARCHAR(30) |
yes | V95__video_assets_and_renditions.sql |
|
file_size_bytes |
BIGINT |
yes | V95__video_assets_and_renditions.sql |
|
storage_key |
VARCHAR(500) |
yes | V95__video_assets_and_renditions.sql |
|
file_url |
TEXT |
yes | V95__video_assets_and_renditions.sql |
|
status |
VARCHAR(30) |
no | V95__video_assets_and_renditions.sql |
|
created_at |
TIMESTAMPTZ |
no | V95__video_assets_and_renditions.sql |
|
updated_at |
TIMESTAMPTZ |
no | V95__video_assets_and_renditions.sql |
Indexes: idx_video_renditions_asset_profile, idx_video_renditions_asset_status
Materialized views
| View | Created in | Role |
|---|---|---|
mv_course_stats |
V54.1__production_schema_catchup.sql |
Tổng hợp course stats cho admin/analytics. |
mv_teacher_performance |
V54.1__production_schema_catchup.sql |
Tổng hợp hiệu suất teacher. |
Ghi nhớ khi trả lời thầy
content_blockslà cột JSONB tronglessonsvàquestions, không phải bảng riêng.payment_transactionslà bảng giao dịch chính; API path vẫn là/api/v3/payments/....student_lesson_progresslà bảng tiến độ bài học; không có bảnglesson_progressriêng.course_tags_legacytồn tại vì V70 rename bảng tag cũ trước khi tạo taxonomy tag mới.- Muốn đối chiếu DB sống trong DBeaver thì lọc schema
public, bỏ quaflyway_schema_historykhi nói số bảng nghiệp vụ.