Public Schema

Purpose

The public schema mixes three kinds of tables:

• platform-owned control-plane tables such as tenants and tenant_settings
• tenant-scoped product tables such as users, staff, classes, and exams
• operational support tables such as event_publication, shedlock, and idempotency_keys

This page is a reference document for the boundary between those families. It keeps the ERD focused on durable ownership and RLS semantics instead of trying to dump every table in the schema.

Source Paths

Path	Role
lumie-backend/app/src/main/resources/db/migration/public/V1__create_platform_tables.sql	tenants and tenant_settings
lumie-backend/app/src/main/resources/db/migration/public/V18__rls_baseline.sql	Baseline tenant-scoped domain tables and FK relations
lumie-backend/app/src/main/resources/db/migration/public/V26__rename_admin_tables_to_staff.sql	Renames admins to staff and admin_permissions to staff_permissions
lumie-backend/app/src/main/resources/db/migration/public/V81__assignment_redesign_expand.sql	Adds assignment audience targets and exam-linked submission fields
lumie-backend/app/src/main/resources/db/migration/public/V82__assignment_redesign_tenant_safe_indexes.sql	Adds composite indexes and tenant-safe FKs for assignment redesign tables
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/app/src/main/resources/db/migration/public/V91__normalize_student_parent_phone_and_drop_unique_index.sql	Normalizes blank students.parent_phone values to NULL and removes parent-phone uniqueness
lumie-backend/app/src/main/resources/db/migration/public/V13__create_event_publication.sql	Spring Modulith event publication table
lumie-backend/app/src/main/resources/db/migration/public/V14__create_shedlock.sql	Scheduled job lock table
lumie-backend/app/src/main/resources/db/migration/public/V16__create_idempotency_keys.sql	Idempotency table
lumie-backend/libs/common/src/main/java/com/lumie/common/domain/TenantScopedEntity.java	JPA base class that supplies tenant-scoped rows with tenant_id

Boundary ERD

The diagram intentionally does not draw a hard tenants -> tenant table edge. The baseline migrations rely on tenant_id plus RLS for tenant ownership, not on a foreign key from every tenant-scoped table back to tenants(id).

Two assignment-specific caveats apply:

• assignment_targets.target_id is polymorphic. CLASS rows point to classes.id semantically and STUDENT rows point to students.id semantically, but the table itself stores only target_type plus target_id.
• assignments.class_id is still present for compatibility, but the audience model is now driven by assignment_targets.
• assignment_submissions.exam_result_id is a non-unique link. Repeated manual exam assignment submissions reuse the current (exam_id, student_id) exam result row, so more than one assignment submission can point at the same exam result.
• Active assignment rows must be linked to an exam. V86 keeps the linked exam foreign key restrictive so the exam sheet cannot be deleted out from under an assignment.
• Every assignment row must have a deadline. V89 backfills legacy null due_date values and sets the column to NOT NULL.
• V90 adds a partial index for active rows keyed by tenant and deadline so the scheduled close query can find overdue assignments efficiently.
• V91 removes the legacy unique index on students.parent_phone. Student phone remains unique when present, but guardian phone numbers can be shared by siblings and blank guardian phone values are normalized to NULL.

Operational Support Tables

event_publication, shedlock, and idempotency_keys live in the same schema but are not part of the tenant-domain ERD:

• event_publication stores Spring Modulith publication state and does not carry a tenant-domain relationship.
• shedlock coordinates scheduled job locks.
• idempotency_keys stores request replay protection keyed by tenant_slug, endpoint_path, and idempotency_key.

RLS Boundary

The runtime contract is that tenant-scoped access happens only after backend code establishes app.tenant_id. A missing tenant context should not silently fall back to cross-tenant data access.

Source anchor: lumie-backend/libs/common/src/main/java/com/lumie/common/domain/TenantScopedEntity.java keeps that contract visible in code:

@MappedSuperclass
public abstract class TenantScopedEntity extends BaseEntity {
    @Column(name = "tenant_id", nullable = false, updatable = false)
    private Long tenantId;
}

Verification

cd /path/to/Lumie
rg -n "CREATE TABLE (admins|users|students|classes|exams)|CREATE TABLE IF NOT EXISTS event_publication|CREATE TABLE IF NOT EXISTS shedlock|CREATE TABLE idempotency_keys" \
  lumie-backend/app/src/main/resources/db/migration/public/V18__rls_baseline.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V13__create_event_publication.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V14__create_shedlock.sql \
  lumie-backend/app/src/main/resources/db/migration/public/V16__create_idempotency_keys.sql

Expected success signal: hits for the baseline tenant tables plus separate hits for event_publication, shedlock, and idempotency_keys.

cd /path/to/Lumie
rg -n "ALTER TABLE admins RENAME TO staff|ALTER TABLE admin_permissions RENAME TO staff_permissions" \
  lumie-backend/app/src/main/resources/db/migration/public/V26__rename_admin_tables_to_staff.sql

Expected success signal: both rename operations appear in V26, confirming the public-schema ERD should use staff and staff_permissions.

cd /path/to/Lumie
rg -n "assignment_targets|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: the redesign migrations show the new audience table plus the composite foreign keys that connect assignments to exams and assignment submissions to exam_results, including the linked-exam delete restriction.

cd /path/to/Lumie
rg -n "idx_students_parent_phone|parent_phone = ''|parent_phone = null" \
  lumie-backend/app/src/main/resources/db/migration/public/V91__normalize_student_parent_phone_and_drop_unique_index.sql

Expected success signal: V91 shows the parent_phone = '' to NULL normalization and DROP INDEX IF EXISTS public.idx_students_parent_phone.

Notes

• V18__rls_baseline.sql still contains the original admins and admin_permissions table names because V26 performs the rename later in the migration chain.
• Newer join tables such as announcement and lecture targeting still follow the same boundary: tenant-scoped rows in public, guarded by RLS.
• Assignment targeting now follows that same pattern: assignment_targets is a tenant-scoped join table in public, not a separate schema or worker-owned store.