Data model
One SQLite file, WAL, one connection. Twenty-five tables are created by the
host's squashed baseline (internal/infrastructure/sqlite/migrations/001_schema.sql),
plus schema_migrations (the host's own migration ledger) and the plugin's
re_* and bun_* tables, which the register migrates itself at boot before
this file runs. Nothing in the baseline writes a register row — the pre-register
ledger (cash_boxes, ledger, cash_box_methods, fee_types and the old
movements/lines/boxes/drawer_* tables) is gone entirely, and
payments/payment_lines carry no box_id: the box is the register line's
property.
| Group | Tables | Concept |
|---|---|---|
| Identity & access | users, roles, sessions, guardian_links |
identity |
| School structure | academic_years, grades, subjects, grade_subjects, classes |
school structure |
| Students | students, student_transfers, enrollments, year_fees, student_fees |
students |
| Money (host side) | payments, payment_lines, salaries |
money |
| Attendance | attendance, attendance_students |
attendance |
| Marks | subject_marks, subject_mark_students |
marks |
| Transport | buses, bus_students |
buses |
| Settings | settings (one JSON row) |
settings |
| Audit | audit_log |
audit |
| Plugin (nx-register) | re_boxes, re_movements, re_lines, re_closes, re_counts, re_settings, re_migrations (+ bun_migrations, bun_migration_locks) |
plugin tables |
Relationships
erDiagram
ROLES ||--o{ USERS : "role of"
USERS ||--o{ SESSIONS : "signs in"
USERS ||--o{ GUARDIAN_LINKS : "is parent of"
STUDENTS ||--o{ GUARDIAN_LINKS : "child of"
GRADES ||--o{ CLASSES : "contains"
USERS ||--o{ CLASSES : "teaches"
SUBJECTS ||--o{ GRADE_SUBJECTS : "offered"
GRADES ||--o{ GRADE_SUBJECTS : "course list of"
GRADES ||--o{ STUDENTS : "placed in"
CLASSES ||--o{ STUDENTS : "has"
STUDENTS ||--o{ STUDENT_TRANSFERS : "moves"
ACADEMIC_YEARS ||--o{ ENROLLMENTS : "covers"
STUDENTS ||--o{ ENROLLMENTS : "enrolled"
GRADES ||--o{ ENROLLMENTS : "into"
CLASSES ||--o{ ENROLLMENTS : "into"
ACADEMIC_YEARS ||--o{ YEAR_FEES : "defines"
ENROLLMENTS ||--o{ STUDENT_FEES : "materialises"
STUDENTS ||--o{ STUDENT_FEES : "charged"
YEAR_FEES ||--o{ STUDENT_FEES : "inherited from"
STUDENTS ||--o{ PAYMENTS : "pays"
USERS ||--o{ PAYMENTS : "employee payee"
PAYMENTS ||--o{ PAYMENT_LINES : "breaks into"
STUDENT_FEES ||--o{ PAYMENT_LINES : "settled by"
PAYMENT_LINES |o--o| RE_MOVEMENTS : "posts"
USERS ||--o{ SALARIES : "receives"
CLASSES ||--o{ ATTENDANCE : "taken for"
ATTENDANCE ||--o{ ATTENDANCE_STUDENTS : "lists"
STUDENTS ||--o{ ATTENDANCE_STUDENTS : "marked"
CLASSES ||--o{ SUBJECT_MARKS : "marked in"
SUBJECTS ||--o{ SUBJECT_MARKS : "for"
SUBJECT_MARKS ||--o{ SUBJECT_MARK_STUDENTS : "lists"
STUDENTS ||--o{ SUBJECT_MARK_STUDENTS : "scored"
USERS ||--o{ BUSES : "caregives"
BUSES ||--o{ BUS_STUDENTS : "carries"
STUDENTS ||--o{ BUS_STUDENTS : "rides"
RE_BOXES ||--o{ RE_LINES : "holds"
RE_MOVEMENTS ||--o{ RE_LINES : "decomposes into"
RE_CLOSES ||--o{ RE_COUNTS : "records"
RE_BOXES ||--o{ RE_COUNTS : "counted"
Notes the diagram cannot carry:
student_transferspoints at grades and classes twice over (from_grade_id/to_grade_id,from_class_id/to_class_id) — the edges are elided for readability; both sides are FKs togrades/classes.payment_lines.movement_idis a plain string link intore_movements.id, not a SQL foreign key — the plugin owns that table, so cross-owner FK integrity is deliberately not declared. Every other FK shown is enforced (_pragma=foreign_keys(1)on the DSN).audit_log.user_idis informational (a plain column, no FK): the audit row must survive the user it describes.settingsandaudit_loghang off no parent — they are singletons per tenant.
Rendered from simple-lms/data-model/index.md · OKF bundle · regenerate with npm run render