Tenant Schema

Purpose

Tenant domain tables model academy operations inside a single tenant. This ERD shows the product relationships that most often matter when changing backend modules, worker callbacks, or frontend data assumptions.

Source Paths

Path	Role
lumie-backend/app/src/main/resources/db/migration/public/V18__rls_baseline.sql	Baseline tenant tables and foreign keys
lumie-backend/app/src/main/resources/db/migration/public/V25__drop_omr_grading_jobs_results.sql	Removes the dead omr_grading_jobs.results column
lumie-backend/app/src/main/resources/db/migration/public/V26__rename_admin_tables_to_staff.sql	Admin-to-staff terminology migration
lumie-backend/app/src/main/resources/db/migration/public/V40__omr_grading_jobs_add_version.sql	Adds the missing optimistic-lock version column to omr_grading_jobs
lumie-backend/app/src/main/resources/db/migration/public/V41__exam_results_add_omr_grading_job_id.sql	Links exam_results rows back to the producing OMR job
lumie-backend/app/src/main/resources/db/migration/public/V54__create_file_links.sql	Tenant-safe file link model
lumie-backend/app/src/main/resources/db/migration/public/V55__create_announcement_class_targets.sql	Announcement-to-class targeting
lumie-backend/app/src/main/resources/db/migration/public/V57__create_lecture_class_targets.sql	Lecture-to-class targeting
lumie-backend/app/src/main/resources/db/migration/public/V62__add_read_receipt_tables.sql	Read receipt tables
lumie-backend/app/src/main/resources/db/migration/public/V65__repair_read_receipt_and_file_download_rls.sql	RLS repair migration
lumie-backend/app/src/main/resources/db/migration/public/V81__assignment_redesign_expand.sql	Assignment workflow columns, submission answer fields, and assignment_targets
lumie-backend/app/src/main/resources/db/migration/public/V82__assignment_redesign_tenant_safe_indexes.sql	Tenant-safe assignment redesign indexes and composite foreign keys
lumie-backend/app/src/main/resources/db/migration/public/V83__drop_assignment_title_description.sql	Removes assignment-owned title and description fields
lumie-backend/app/src/main/resources/db/migration/public/V84__drop_assignment_type_and_draft.sql	Removes assignment type and draft state
lumie-backend/app/src/main/resources/db/migration/public/V85__assignment_exam_only_active_guard.sql	Enforces active assignments as linked-exam manual-answer assignments
lumie-backend/app/src/main/resources/db/migration/public/V86__assignment_exam_fk_restrict.sql	Restricts deletion of exams referenced by assignments
lumie-backend/app/src/main/resources/db/migration/public/V89__require_assignment_due_date.sql	Backfills legacy null deadlines and requires assignments.due_date
lumie-backend/app/src/main/resources/db/migration/public/V90__assignment_overdue_close_index.sql	Adds the partial index for overdue active assignment close queries
lumie-backend/modules/assignment/src/main/java/com/lumie/assignment/domain/entity/AssignmentTarget.java	Current JPA mapping for assignment_targets
lumie-backend/modules/exam/src/main/java/com/lumie/exam/domain/entity/OmrGradingJob.java	Current JPA mapping for omr_grading_jobs
lumie-backend/modules/exam/src/main/java/com/lumie/exam/domain/entity/ReportGenerationJob.java	Current JPA mapping for report_generation_jobs
lumie-backend/libs/common/src/main/java/com/lumie/common/domain/TenantScopedEntity.java	Inherited tenant_id, created_at, and updated_at contract

Core Tenant ERD

assignment_targets is the canonical assignment audience relation. The older assignments.class_id relation is still present for compatibility reads, but student visibility now comes from assignment_targets rows whose target_type is either CLASS or STUDENT.

Exam And Worker ERD

EXAM_MANUAL assignments grade manually entered objective answers inline inside the backend. The assignment submission can point to the resulting exam_results row through assignment_submissions.exam_result_id. That link is added as a tenant-safe composite foreign key in V82, and it does not imply an OMR job or worker-owned result path. Active assignment rows must keep a linked exam, and V86 prevents deleting an exam while an assignment references it. Every assignment row must also have a deadline; V89 backfills legacy null due_date values and then makes the column non-null. V90 adds the active-assignment deadline index used by the scheduled close query.

tenant_id is inherited from TenantScopedEntity; tenant_slug is stored explicitly on both job rows for downstream correlation. The current schema does not contain a request_id column on either job table, so the ERD stays aligned with the real fields used by the backend.

Worker services do not own these product tables. The backend creates job rows, publishes work messages, and records callbacks or result state through backend-owned services so tenant context and exam ownership remain centralized.

Content And File ERD

file_links and file_download are tenant-safe attachment tables. When a relation can cross module boundaries, the migration must preserve the tenant_id guard and document whether the relation is hard FK or soft reference.

Verification

cd /path/to/Lumie
rg -n "CREATE TABLE omr_grading_jobs|CREATE TABLE report_generation_jobs|DROP COLUMN IF EXISTS results|ADD COLUMN version" \
  lumie-backend/app/src/main/resources/db/migration/public/V18__rls_baseline.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V25__drop_omr_grading_jobs_results.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V40__omr_grading_jobs_add_version.sql

Expected success signal: the baseline create statements appear, results is dropped in V25, and only omr_grading_jobs receives a later version column.

cd /path/to/Lumie
rg -n "class OmrGradingJob|class ReportGenerationJob|tenantSlug|zipFileKey|savedCount|studentIds" \
  lumie-backend/modules/exam/src/main/java/com/lumie/exam/domain/entity

Expected success signal: OmrGradingJob exposes savedCount and imageKeys, while ReportGenerationJob exposes studentIds and zipFileKey.

cd /path/to/Lumie
rg -n "assignment_targets|linked_exam_id|exam_result_id|assignments_linked_exam_tenant_fkey|assignment_submissions_exam_result_tenant_fkey|due_date" \
  lumie-backend/app/src/main/resources/db/migration/public/V81__assignment_redesign_expand.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V82__assignment_redesign_tenant_safe_indexes.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V86__assignment_exam_fk_restrict.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V89__require_assignment_due_date.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V90__assignment_overdue_close_index.sql

Expected success signal: V81 defines assignment_targets and the assignment submission linkage fields, while V82 defines the tenant-safe linked-exam and exam-result foreign keys and V86 preserves the linked-exam delete restriction.

Review Checklist

1. Does every table in the relation carry the same tenant boundary?
2. Does the migration keep tenant_id in join tables?
3. Does a unique constraint need tenant_id to avoid cross-tenant collisions?
4. Is a soft reference more appropriate than a hard FK across module ownership?
5. Do worker callbacks go through backend-owned tenant-aware APIs?