Public Schema

Purpose

The public schema is the main runtime schema for Lumie. It contains both platform-scoped tables and tenant-scoped tables. Tenant isolation is not modeled with separate schemas; it is enforced with tenant_id columns, backend tenant context, and PostgreSQL RLS policies.

This page is a reference document for backend developers changing persistence or reviewing whether a table should be platform-scoped or tenant-scoped.

Source Paths

Path	Role
V1__create_platform_tables.sql	Creates tenants and tenant_settings
V13__create_event_publication.sql	Spring Modulith event publication store
V14__create_shedlock.sql	Scheduled-job lock table
V16__create_idempotency_keys.sql	Request idempotency table
V18__rls_baseline.sql	Recreates tenant-scoped domain tables in public with RLS
V70__create_exam_question_types.sql	Adds tenant-scoped exam question type settings
V81__assignment_redesign_expand.sql	Adds assignment workflow flags, assignment_targets, and exam-linked submission fields
V82__assignment_redesign_tenant_safe_indexes.sql	Adds composite indexes and tenant-safe foreign keys for exam-linked assignment data
V83__drop_assignment_title_description.sql	Removes legacy assignment title and description columns
V84__drop_assignment_type_and_draft.sql	Removes assignment type and the draft status
V85__assignment_exam_only_active_guard.sql	Enforces active assignments as linked-exam manual-answer assignments
V86__assignment_exam_fk_restrict.sql	Restricts deletion of exams referenced by assignments
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
V27__langgraph_schema.sql	Creates the separate langgraph schema and role
lumie-backend/libs/common/src/main/java/com/lumie/common/tenant/	Runtime tenant context helpers

Scope Decision

Table type	Belongs in public	RLS required	Typical owner
Platform catalogue	Yes	No	Tenant, billing, app platform modules
Tenant domain data	Yes	Yes	Product modules such as exam, content, attendance
Outbox and locks	Yes	Usually no	Application infrastructure
Worker checkpoint data	No	No	Dedicated schema such as langgraph

Platform Tables

Platform tables are global control-plane records. They are not filtered by tenant RLS because Lumie needs to manage them across tenants.

Table	Purpose
tenants	Tenant identity, slug, lifecycle, contact and branding fields
tenant_settings	Per-tenant platform configuration
plans	Product plan catalogue
subscriptions	Tenant subscription state
billing_keys	Toss billing key metadata
event_publication	Spring Modulith outbox table
shedlock	Scheduled job locking
idempotency_keys	API idempotency records

Tenant-Scoped Tables In Public

Tenant data tables are still physically in public, but they carry tenant_id and are guarded by RLS. Examples include:

• identity and people: users, students, staff, staff_permissions;
• education model: classes, lectures, assignments, assignment_targets, assignment_submissions, textbooks;
• exam and grading: exams, exam_question_types, exam_results, question_results, omr_grading_jobs;
• communication and content: announcements, qna_boards, sms_messages, ai_chat_messages;
• attendance: attendance_sessions, attendance_records;
• file metadata and links: file_metadata, file_links, file_download.

Assignment Redesign Tables

The assignment redesign keeps the data in public, but it changes how audiences, linked exams, and student-visible grading are modeled.

Table	Migration source	Notes
assignments	V18, V81, V82, V83, V84, V85, V86, V89, V90	Still stores class_id, but now stores exam-backed workflow fields, required deadline, active/closed status, required linked_exam_id for active rows, and result booleans where only correct-answer visibility is configurable
assignment_targets	V81, V82	New tenant-scoped audience table. One row per CLASS or STUDENT target
assignment_submissions	V18, V81, V82	Adds answers jsonb, optional exam_result_id, and pass/fail result storage for inline linked-exam grading

Two compatibility rules matter here:

• assignments.class_id remains in place for older class-centric reads, but assignment_targets is now the source of truth for student visibility.
• linked_exam_id and exam_result_id use composite foreign keys with tenant_id, because those linked tables are tenant-scoped inside the same public schema.
• V83 and V84 remove assignment-owned title, description, type, and draft state. The student-facing assignment name comes from the linked exam.
• V85 closes legacy non-exam rows and requires active assignments to use EXAM_MANUAL plus EXAM_AUTO.
• V89 backfills null assignment deadlines from audit timestamps and then makes assignments.due_date non-null.
• V90 adds a partial (tenant_id, due_date, id) index for active rows so the scheduled overdue-close query can use the tenant boundary and deadline predicate efficiently.

Source anchor: lumie-backend/app/src/main/resources/db/migration/public/V81__assignment_redesign_expand.sql and lumie-backend/app/src/main/resources/db/migration/public/V82__assignment_redesign_tenant_safe_indexes.sql

create table if not exists assignment_targets (
    assignment_id bigint not null,
    target_type text not null,
    target_id bigint not null,
    tenant_id bigint not null
);

alter table assignments
    add constraint assignments_linked_exam_tenant_fkey
    foreign key (linked_exam_id, tenant_id)
    references exams(id, tenant_id)
    on delete restrict;

V81 also backfills every existing assignments.class_id row into assignment_targets as a CLASS target, so old data becomes visible through the new audience model without deleting the compatibility column.

RLS Contract

Tenant-scoped tables follow this representative shape:

ALTER TABLE <table_name> ENABLE ROW LEVEL SECURITY;
ALTER TABLE <table_name> FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON <table_name>
  USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::bigint);

Backend code must set app.tenant_id inside the active transaction before querying those tables. Queries that run without tenant context must fail closed or return no rows.

Verification

Use these checks when changing the public schema:

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

rg -n "assignment_targets|submission_mode|evaluation_mode|linked_exam_id|show_score|exam_result_id|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/V89__require_assignment_due_date.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V90__assignment_overdue_close_index.sql

rg -n "assignments_linked_exam_tenant_fkey|assignment_targets_assignment_tenant_fkey|assignment_submissions_exam_result_tenant_fkey" \
  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

rg -n "TenantContextHolder|app.tenant_id|SET LOCAL" lumie-backend

Expected success signals:

• the first command still finds the shared RLS pattern
• the second command finds the redesign columns and assignment_targets
• the third command finds the tenant-safe foreign-key names added by V82 and the linked-exam delete restriction updated by V86
• the tenant-context command still shows the runtime mechanism that makes those RLS-protected rows visible only inside an initialized tenant scope