Data Model

CSMS uses a multi-tenant architecture: one main database holds system-level data, and each school has its own separate database for business data. They are linked by school ID, physically isolated and invisible to each other.

Multi-tenant Architecture

                   ┌─────────────────────┐
   Main csms.db    │  schools            │ ← school catalog (DB entry)
   (system-level)  │  admins             │ ← admins of all levels
                   │  applications       │ ← school onboarding applications
                   │  announcements      │ ← school-wide announcements
                   │  third_party_apis   │ ← Open API client config
                   │  api_tokens         │ ← credentials
                   │  api_audit_logs     │ ← call audit
                   │  system_settings    │ ← system settings
                   │  mail_services      │ ← mail service config
                   │  mail_templates     │ ← mail templates
                   └─────────┬───────────┘
                             │ schoolId
         ┌───────────────────┼───────────────────┐
         ▼                   ▼                   ▼
  data/schools/A.db   data/schools/B.db   data/schools/C.db
  (grades/classes/    (grades/classes/    (grades/classes/
   students/scores/    students/scores/    students/scores/
   seats)              seats)              seats)

• Main DB path: data/csms.db
• Per-school DB path: data/schools/{schoolId}.db
• The app accesses the main DB via useMainDb() and a school DB via useSchoolDb(event, schoolId).

Main DB Tables (schema.main.ts)

Table	Purpose
schools	School catalog, entry point to per-school DB
admins	Super / school / grade / class admins
applications	School onboarding applications & review status
announcements	School-wide announcements
third_party_apis	External Open API client config
api_tokens	Open API credentials (sha256 stored)
api_audit_logs	Open API call audit (30-day retention)
system_settings	System settings (e.g. password, plaintext, super_admin only)
mail_services	Mail service config (SMTP / Resend)
mail_templates	Mail templates (variable placeholders)

Role constants live in schema.main.ts; system_settings.password is stored in plaintext and readable/writable only by super admin.

Per-school DB Tables (schema.school.ts)

Table	Purpose
grades	Grades
classes	Classes
users	Students (scores, username, etc.)
score_logs	Score change records (operator, reason, before/after)
score_templates	Score templates (preset items)
seat_layout_config	Seating layout config (rows/columns)
seat_data	Student-to-seat binding data
api_idempotency	Open API idempotency key records

Migration & Access

• Tables are managed by Drizzle ORM; changes are applied via npm run db:generate + npm run db:migrate.
• Main and per-school schemas live in server/database/schema.main.ts and server/database/schema.school.ts.
• Backup strategy: copy data/csms.db and data/schools/{id}.db; see .