Database timeline
Trang này là bản curated để ôn database, bổ sung cho DBeaver ERD và Full CodeMap.
Migration mốc cần nhớ
| Migration | Nội dung |
|---|---|
V1__lms_complete_schema.sql |
Schema nền ban đầu |
V54__seed_users_courses_content.sql |
Seed user/course/content |
V55__seed_assessment_enrollments.sql |
Seed assessment/enrollment |
V70__course_categories_and_tags.sql |
Category/tag taxonomy |
V74__upload_sessions.sql |
Presigned upload session |
V92__course_publications_and_version_modes.sql |
Publication snapshot và version mode |
V94__learning_classes_publication_fk.sql |
Class gắn FK publication |
V101__grading_audit_log.sql |
Audit chấm điểm |
V114__user_external_identities.sql |
External identity/Google login |
V119__multi_org_foundation.sql |
Multi-organization foundation |
V130__stcw_competency_mapping.sql |
STCW/competency mapping |
V135__video_storage_governance.sql |
Video storage governance |
Domain -> tables -> code
| Domain | Tables tiêu biểu | Entity/controller |
|---|---|---|
| Identity | users, organizations, user_external_identities, organization_invites |
UserJpaEntity, AuthControllerV3, UserControllerV3 |
| Course authoring | courses, chapters, lessons, sections, course_tags |
CourseJpaEntity, CourseAuthoringControllerV3; content_blocks là cột JSONB |
| Publication/classes | course_publications, learning_classes, class_teachers |
CoursePublicationJpaEntity, LearningClassJpaEntity |
| Learning progress | enrollments, student_lesson_progress, certificates |
EnrollmentJpaEntity, ProgressControllerV3 |
| Assessment | quizzes, questions, quiz_attempts, assignments, assignment_submissions, assignment_rubrics |
QuizControllerV3, AssignmentControllerV3 |
| Payment | payment_transactions, refund/payout/revenue tables |
PaymentTransactionJpaEntity, PaymentControllerV3 |
| Media/upload | upload_sessions, file_attachments, video_assets, video_ingest_jobs |
VideoAssetJpaEntity, PresignedUploadUseCase |
| Competency | maritime_standards, standard_competencies, lesson_competency_mappings |
CompetencyMappingController |
Catalog đầy đủ
Xem SQL schema catalog để tra đủ 78 application tables, 2 materialized views, danh sách cột, index và migration tạo/thay đổi từng bảng.
DBeaver trình bày cho thầy
- Nếu chụp toàn schema, zoom 10-20% để thấy cluster tổng.
- Nếu muốn rõ, chụp từng domain: identity, course/publication, learning/assessment, media/payment.
- Dùng DBML trong
docs/lms_schema.dbmlvàdocs/lms_schema_a3.dbmlkhi muốn xuất sơ đồ gọn hơn ERD auto-layout. - Không đưa screenshot chứa secrets, connection string nhạy cảm hoặc production credential.