Database

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.

Core Principles

Our database design is guided by fundamental principles that ensure scalability, security, and maintainability:

• Row-Level Security – Every model includes a schoolId field ensuring complete data isolation between schools
• Shared Schema Architecture – All schools use the same database schema, optimizing resource utilization
• Domain-Based Tenant Identification – Schools are uniquely identified by subdomain (e.g., hogwarts.hogwarts.app), with optional custom domains via CNAME
• Type-Safe Database Operations – Prisma ORM provides end-to-end type safety from database to frontend
• Multi-File Schema Organization – Schema is organized into logical domains for better maintainability

Database Design

The 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.

Multi-Tenant Isolation Strategy

The tenant isolation strategy ensures that each school's data remains completely separate while sharing the same database infrastructure.

Tenant Entity (School Model)

model School {
  id          String  @id @default(cuid())
  name        String
  domain      String  @unique // e.g., "hogwarts"
  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)
}

Data Isolation Example

// 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()

Directory Structure

The Prisma directory follows a well-organized structure that supports both development and production scenarios with clear separation of concerns.

Schema Tables

Each model contains realistic sample data that demonstrates the relationships and data structure of our multi-tenant school management system.

Authentication & User Roles

The system supports 8 distinct user roles with varying levels of access and school-specific constraints:

Security & Data Protection

Automatic Tenant Filtering

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 }
  })
}

Unique Constraints with School Scope

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])

Performance Optimization

Recommended Database Indexes

-- 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);

Query Performance Guidelines

• Always filter by schoolId first in WHERE clauses
• Use composite indexes for frequently queried field combinations
• Implement connection pooling for high-traffic scenarios
• Consider read replicas for reporting and analytics

Deployment Configuration

Environment Variables

# 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

Migration Commands

# 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 seed

Sample Schools & Test Data

The seeding system creates three sample schools with complete datasets:

This comprehensive database architecture provides the foundation for building scalable, secure, and feature-rich educational management systems.