Database

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

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

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

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

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

Copy// 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"
  }
})

Copy// Server action with tenant context
export async function getStudents() {
  const schoolId = getSchoolId() // From middleware context
 
  return await prisma.student.findMany({
    where: { schoolId }
  })
}

Copy// Teachers can have same email across different schools
@@unique([schoolId, emailAddress])
 
// Departments can have same names across schools
@@unique([schoolId, departmentName])

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

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

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