The database is engineered as a multi-tenant school management system that supports multiple schools in a single database with complete data isolation. Built on PostgreSQL with Prisma ORM, it provides a robust foundation for educational institutions while maintaining strict security boundaries between tenants.
Our database design is guided by fundamental principles that ensure scalability, security, and maintainability:
schoolId field ensuring complete data isolation between schoolsdemo.databayt.org), with optional custom domains via CNAMElang field; translation happens on-demand via Google Translate API with database cachingThe database follows a comprehensive relational design that captures all aspects of school management from core infrastructure to daily operations.
The entity relationship diagram above illustrates the interconnected nature of our school management system, showing how entities relate through foreign key relationships while maintaining multi-tenant isolation through the schoolId field present in every table.
The tenant isolation strategy ensures that each school's data remains completely separate while sharing the same database infrastructure.
model School {
id String @id @default(cuid())
name String
domain String @unique // e.g., "demo"
preferredLanguage String @default("ar") // Admin's chosen content language
logoUrl String?
address String?
phoneNumber String?
email String?
timezone String @default("UTC")
// Subscription/billing
planType String @default("basic")
maxStudents Int @default(100)
maxTeachers Int @default(10)
isActive Boolean @default(true)
}// Correct - Always include schoolId
const students = await prisma.student.findMany({
where: { schoolId: "school_123" },
})
// Wrong - Missing schoolId (security risk)
const students = await prisma.student.findMany()The Prisma directory follows a well-organized structure that supports both development and production scenarios with clear separation of concerns.
prisma/— Database schema and migrationsschema.prisma— Main config with datasource and generatormodels/— Schema files organized by domain (30+ models)school.prisma— School, SchoolYear, Period, Term, YearLevelauth.prisma— User, Account, tokens, 2FAstaff.prisma— Teacher, Department, TeacherDepartmentstudents.prisma— Student, Guardian, StudentGuardian, Emergencysubjects.prisma— Subject, Class, StudentClass, ScoreRangeclassrooms.prisma— Classroom, ClassroomTypeassessments.prisma— Assignment, AssignmentSubmissionattendance.prisma— Attendance with status trackingattendance-enhanced.prisma— Advanced attendance featuresgeo-attendance.prisma— Location-based attendancefinance.prisma— Fees, payments, invoices, receiptssubscription.prisma— Plans, billing, Stripe integrationadmission.prisma— Applications, enrollment workflowexam.prisma— Exams, grades, report cardsqbank.prisma— Question bank, assessmentsquiz-game.prisma— Gamified learningstream.prisma— Live streaming, recordingstimetable.prisma— Schedule, periods, slotsmessages.prisma— Chat, conversations, threadsnotifications.prisma— Push, email, in-app alertsfiles.prisma— File storage, attachmentslibrary.prisma— Books, borrowing, cataloglessons.prisma— Lesson plans, resourcesannouncement.prisma— School-wide announcementsbranding.prisma— Theme, logo, customizationtheme.prisma— UI theme preferenceslegal.prisma— Consent, compliance, GDPRdomain.prisma— Custom domain requestsaudit.prisma— Activity loggingtask.prisma— Task managementschedule.prisma— Event schedulinggenerator/— Database seeding scriptsseed.ts— Main seeding orchestratorseed-demo.ts— Demo data for showcasesseed-community.ts— Community edition dataseed-qbank-simple.ts— Question bank seederseed-modules/— Modular seedersadmission.ts— Admission test datafinance.ts— Financial test dataseed/— Additional seed datafinance.seed.ts— Finance module seedertimetable-enhanced.ts— Timetable seedermigrations/— Auto-generated migration files20250807.../— Initial multi-tenant schema20250810.../— Audit and domain request20250811.../— Stripe billing models20250812.../— Timetable initmigration_lock.toml— Migration provider locksql/— Raw SQL scriptsgeo-spatial-indexes.sql— Geo-location indexesgeo-triggers.sql— Geo-location triggersschema-optimizations.sql— Performance optimizationsREADME.md— Database documentationISSUE.md— Known issues and fixesEach model contains realistic sample data that demonstrates the relationships and data structure of our multi-tenant school management system. The demo school uses Arabic as its primary content language with on-demand translation.
The demo school representing a Sudanese K-12 academy.
Teaching staff with Arabic names and department assignments. 100 teachers across 6 departments.
970 students across KG1-Grade 12 with guardian relationships.
6 academic departments with single-language storage (Arabic primary + lang field).
19 academic subjects within departments, stored in Arabic with on-demand translation.
28 physical classroom locations across 5 buildings.
Multi-tenant user accounts with role-based access control and school isolation.
The subscription system supports tiered pricing plans with flexible discounting capabilities.
model SubscriptionTier {
id String @id @default(cuid())
name String // basic, premium, enterprise
description String
monthlyPrice Int // Price in cents
annualPrice Int // Price in cents
maxStudents Int
maxTeachers Int
maxClasses Int
features String[] // Array of feature identifiers
isActive Boolean @default(true)
// Relationships
subscriptions Subscription[]
discounts Discount[]
}
model Discount {
id String @id @default(cuid())
schoolId String
tierId String
code String @unique
type String // percentage, fixed
value Int // Percentage or fixed amount
description String
validFrom DateTime
validUntil DateTime
maxUses Int?
currentUses Int @default(0)
isActive Boolean @default(true)
// Relationships
school School @relation(fields: [schoolId], references: [id])
subscriptionTier SubscriptionTier @relation(fields: [tierId], references: [id])
appliedDiscounts AppliedDiscount[]
}
model AppliedDiscount {
id String @id @default(cuid())
schoolId String
discountId String
invoiceId String
amount Int // Amount saved in cents
appliedAt DateTime @default(now())
// Relationships
school School @relation(fields: [schoolId], references: [id])
discount Discount @relation(fields: [discountId], references: [id])
invoice Invoice @relation(fields: [invoiceId], references: [id])
}Key Features:
// Create a new subscription tier
const tier = await prisma.subscriptionTier.create({
data: {
name: "premium",
description: "Premium school plan",
monthlyPrice: 19900, // $199.00
annualPrice: 199900, // $1,999.00
maxStudents: 500,
maxTeachers: 50,
maxClasses: 100,
features: ["analytics", "api_access", "priority_support"],
},
})
// Create a discount code
const discount = await prisma.discount.create({
data: {
schoolId: "school_123",
tierId: tier.id,
code: "WELCOME2024",
type: "percentage",
value: 20, // 20% off
description: "New year promotion",
validFrom: new Date(),
validUntil: new Date("2024-12-31"),
maxUses: 100,
},
})The legal system provides comprehensive consent tracking and compliance logging.
model LegalConsent {
id String @id @default(cuid())
schoolId String
userId String
documentType String // terms, privacy, data-processing
documentVersion String // Version consented to
consentType String // explicit, implicit, parental
ipAddress String?
userAgent String?
consentedAt DateTime @default(now())
revokedAt DateTime?
metadata Json?
// Relationships
school School @relation(fields: [schoolId], references: [id])
user User @relation(fields: [userId], references: [id])
@@unique([schoolId, userId, documentType, documentVersion])
}
model LegalDocument {
id String @id @default(cuid())
schoolId String
type String // terms, privacy, data-processing
version String // Semantic version
content String @db.Text
effectiveFrom DateTime
effectiveUntil DateTime?
isActive Boolean @default(true)
requiresExplicit Boolean @default(true)
metadata Json?
// Relationships
school School @relation(fields: [schoolId], references: [id])
}
model ComplianceLog {
id String @id @default(cuid())
schoolId String
eventType String // consent events, document updates
eventData Json
userId String?
timestamp DateTime @default(now())
metadata Json?
// Relationships
school School @relation(fields: [schoolId], references: [id])
user User? @relation(fields: [userId], references: [id])
}Key Features:
// Record user consent
const consent = await prisma.legalConsent.create({
data: {
schoolId: "school_123",
userId: "user_456",
documentType: "terms",
documentVersion: "1.0.0",
consentType: "explicit",
ipAddress: "192.168.1.1",
userAgent: "Mozilla/5.0...",
metadata: {
source: "onboarding",
platform: "web",
},
},
})
// Log compliance event
await prisma.complianceLog.create({
data: {
schoolId: "school_123",
eventType: "consent-given",
eventData: {
documentType: "terms",
version: "1.0.0",
consentId: consent.id,
},
userId: "user_456",
},
})The system supports 8 distinct user roles with varying levels of access and school-specific constraints.
Every database query must include the schoolId to ensure proper data isolation:
// Server action with tenant context
export async function getStudents() {
const schoolId = getSchoolId() // From middleware context
return await prisma.student.findMany({
where: { schoolId },
})
}All unique constraints include schoolId to allow data duplication across schools:
// Teachers can have same email across different schools
@@unique([schoolId, emailAddress])
// Departments can have same names across schools
@@unique([schoolId, departmentName])-- Critical indexes for multi-tenant queries
CREATE INDEX idx_students_school_id ON students(school_id);
CREATE INDEX idx_teachers_school_id ON teachers(school_id);
CREATE INDEX idx_classes_school_id ON classes(school_id);
CREATE INDEX idx_attendance_school_id ON attendance(school_id);schoolId first in WHERE clauses# Database Connection
DATABASE_URL="postgresql://username:password@host:port/database"
# Multi-tenancy Configuration
DEFAULT_SCHOOL_DOMAIN="demo"
ALLOW_SCHOOL_SIGNUP="true"
# Subscription Limits
BASIC_MAX_STUDENTS=100
PREMIUM_MAX_STUDENTS=500
ENTERPRISE_MAX_STUDENTS=2000# Generate Prisma Client
pnpm prisma generate
# Create and apply migrations
pnpm prisma migrate dev --name init
# Deploy to production
pnpm prisma migrate deploy
# Seed sample data
pnpm prisma db seedContent is stored in one language with a lang field. When a user views content in a different locale, translation happens on-demand via Google Translate API with database caching.
model Announcement {
title String?
body String? @db.Text
lang String @default("ar") // "ar" or "en"
}model TranslationCache {
id String @id @default(cuid())
schoolId String
sourceText String @db.Text
sourceLanguage String // "ar" or "en"
targetLanguage String // "ar" or "en"
translatedText String @db.Text
provider String @default("google")
hitCount Int @default(0)
lastAccessedAt DateTime @default(now())
@@unique([schoolId, sourceText, sourceLanguage, targetLanguage])
@@index([schoolId, sourceLanguage, targetLanguage])
@@index([lastAccessedAt])
}import { getDisplayText } from "@/lib/content-display"
// Content stored in Arabic, user viewing in English
const title = await getDisplayText("مرحبا", "ar", "en", schoolId)
// Returns "Hello" (translated and cached)
// Same language - no API call
const title = await getDisplayText("مرحبا", "ar", "ar", schoolId)
// Returns "مرحبا" directlylang FieldThe seeding system creates a single demo school (demo.databayt.org) with a complete K-12 Sudanese school simulation:
This comprehensive database architecture provides the foundation for building scalable, secure, and feature-rich educational management systems.