Tenant Schema

Purpose

Lumie no longer uses schema-per-tenant persistence. Tenant-owned records live in shared public tables and are isolated by tenant_id plus RLS. This page describes the tenant-scoped part of the model and the rules for changing it.

Source Paths

Path	Role
V18__rls_baseline.sql	Baseline tenant-scoped tables and policies
V26__rename_admin_tables_to_staff.sql	Staff terminology migration
V54__create_file_links.sql	Tenant-safe file link model
V55__create_announcement_class_targets.sql	Announcement targeting join table
V57__create_lecture_class_targets.sql	Lecture targeting join table
V62__add_read_receipt_tables.sql	Announcement and textbook read tracking
V65__repair_read_receipt_and_file_download_rls.sql	RLS repair for read/download tables
V81__assignment_redesign_expand.sql	Assignment workflow columns, submission answer fields, and assignment_targets
V82__assignment_redesign_tenant_safe_indexes.sql	Tenant-safe assignment redesign indexes and composite foreign keys
V83__drop_assignment_title_description.sql	Removes legacy assignment-owned title and description fields
V84__drop_assignment_type_and_draft.sql	Removes assignment type and the draft status from the assignment model
V85__assignment_exam_only_active_guard.sql	Closes non-exam legacy rows and enforces active rows as linked-exam assignments
V86__assignment_exam_fk_restrict.sql	Prevents linked exams from being deleted while assignments reference them
V89__require_assignment_due_date.sql	Backfills legacy null assignment deadlines and requires assignments.due_date
V90__assignment_overdue_close_index.sql	Adds the partial index for overdue active assignment close queries

Domain Groups

Group	Tables	Notes
Identity	users, staff, staff_permissions, students	User rows are tenant-scoped and role-bearing
Class, assignment, and lecture	classes, class_enrollments, assignments, assignment_targets, assignment_submissions, lectures, lecture_class_targets	Target joins keep tenant_id in the join row
Content	announcements, announcement_class_targets, qna_boards, textbooks, textbook_folders	Read receipts and file links are also tenant-scoped
Exam	exams, questions, question_results, exam_results, omr_grading_jobs	Worker callbacks still land in backend-owned tenant tables
Attendance	attendance_sessions, attendance_records	Session uniqueness is maintained per class/date pattern
Communication	sms_messages, sms_templates, ai_chat_messages, ai_conversations	Chat persistence remains backend-owned
File metadata	file_metadata, file_links, file_download	Tenant-safe FK patterns include (id, tenant_id) pairs

Cross-Module Reference Policy

Not every relationship is a database FK. Lumie keeps hard FKs when they support the same module boundary or when tenant-safe FK shape is explicit. It uses soft references when a hard FK would couple modules too tightly.

Examples:

• file links reference file metadata with (file_id, tenant_id) to avoid cross-tenant attachment mistakes;
• announcement and lecture target joins include tenant_id and tenant-safe references;
• assignment-linked exam results use tenant-safe composite references for assignments.linked_exam_id, assignment_targets.assignment_id, and assignment_submissions.exam_result_id;
• tuition tables reference some user/class concepts semantically without hard FKs because billing and identity/class modules remain separate boundaries.

Assignment Redesign Tables

assignments remains the assignment aggregate row, but V81 adds workflow and student-facing result controls:

Column group	Columns	Notes
Submission workflow	submission_mode, evaluation_mode, linked_exam_id	Active rows use EXAM_MANUAL plus EXAM_AUTO, and linked_exam_id is required for active rows. Legacy non-exam rows are closed by V85.
Deadline	due_date	Required for every assignment. Legacy null values are backfilled by V89 before the column is made non-null.
Result visibility	show_score, show_pass_fail, show_grade, show_question_results, show_correct_answers	Active assignments always expose score, pass/fail, and per-question correctness; grade stays disabled and only show_correct_answers is product-configurable.

assignment_targets is the canonical audience table for the redesign:

Column	Role
assignment_id	Assignment row being distributed
target_type	CLASS or STUDENT
target_id	Class ID for CLASS, student ID for STUDENT
tenant_id	Required RLS boundary inherited from the tenant-scoped table pattern

Existing assignments.class_id is still populated for compatibility with older class-centric reads. New student visibility should use assignment_targets.

assignment_submissions stores the student's manually entered objective-answer payload and the exam result produced by inline grading:

Column	Role
answers	JSONB map of manually entered objective-answer choices for EXAM_MANUAL
exam_result_id	Optional link to the exam result produced by inline manual grading
passed	Pass/fail grading output from the linked exam policy
grade_value	Legacy column retained for schema compatibility; assignments do not expose grades

V82 adds the tenant-safe composite foreign keys after creating the required indexes. The linked exam and exam result relationships include tenant_id, so a cross-tenant ID collision cannot satisfy the database reference. V86 tightens the linked exam relation to ON DELETE RESTRICT so an active assignment cannot lose the exam sheet it depends on. V90 adds a partial (tenant_id, due_date, id) index on active assignments for the scheduled overdue-close query.

Mutation Rules

1. New tenant-scoped tables must include tenant_id bigint not null.
2. RLS must be enabled and forced.
3. Insert/update code must write the current tenant id, not trust user input.
4. Unique indexes should include tenant_id unless the value is globally unique by design.
5. Cross-module references must be documented in the migration comment when no hard FK is declared.

Operational Failure Modes

Symptom	Likely cause	First check
Empty result for valid tenant data	Missing app.tenant_id context	Search caller for tenant context setup
Cross-tenant write rejected	RLS WITH CHECK blocked the write	Verify inserted tenant_id
Duplicate-key conflict across tenants	Unique index missing tenant_id	Inspect migration index definition
Worker callback cannot update job	Missing or wrong tenantSlug/tenant context	Check backend callback listener and tenant lookup

Verification

rg -n "CREATE TABLE|tenant_id|ENABLE ROW LEVEL SECURITY|tenant_isolation" \
  lumie-backend/app/src/main/resources/db/migration/public

rg -n "assignment_targets|linked_exam_id|exam_result_id|show_correct_answers|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/V85__assignment_exam_only_active_guard.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

rg -n "tenant_id|TenantContextHolder" lumie-backend/modules

Success means the migrations show tenant-owned tables with tenant_id, RLS enablement, tenant-isolation policies, the assignment redesign fields, and tenant-safe assignment foreign keys, while backend modules show request-time tenant context propagation before tenant-scoped reads or writes.