System Architecture & Multi-Tenancy
Detailed technical documentation of our single-database multi-tenant engine, Spatie teams RBAC, and offline desktop sync pipeline.
1. Single-DB Multi-Tenancy
Every tenant-owned table implements a non-nullable school_id foreign key indexed with composite keys. All Eloquent models inherit the BelongsToSchool trait which applies the global SchoolScope.
2. Spatie Teams RBAC
Role-based permissions are segmented with team_foreign_key = school_id. Super Admin operates as a global authority via Gate::before, while School Admins customize granular roles strictly within their tenant.
3. Offline-First Delta Sync
The .NET 8 WPF desktop fee counter caches student records and fee rules in local SQLite. When offline, billing continues uninterrupted with UUID idempotency keys and reconciles upon connection restoration.
Why Single Database with Row-Level Isolation?
| Feature / Metric | Our Choice: Single DB with school_id | Database-per-Tenant (stancl/tenancy) |
|---|---|---|
| Operational Overhead | Low (Unified migration runner, instant CI/CD) | High (Migrating 500+ databases sequentially) |
| Infrastructure & Hosting Cost | Minimal (Connection pool scales to 1,000+ schools) | Heavy (Excessive idle RAM & socket overhead) |
| Platform Analytics | Native SQL queries across tenants for Super Admin | Requires distributed querying or complex data pipeline |
| Leakage Protection | Enforced via Global Scopes & 17 Automated Tests | Enforced at physical connection boundary |
Role & Permission Hierarchy
└── School Admin / Principal [Tenant Context: team_id = school_id]
├── Vice Principal & Exam Controller
├── Teachers & Class Teachers
├── Accountant & Fee Counter Operator
├── Front Office / Receptionist
├── Librarian, Transport Manager & Hostel Warden
├── HR & Payroll Officer, Store Keeper
└── Students & Parents (Portal / Mobile Clients)