Infra: Database Schema

Full Postgres 16 schema. All tables have tenant_id for multitenancy. Use Prisma migrations to manage schema changes.

Prisma schema

// packages/db/prisma/schema.prisma
 
generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
// ─────────────────────────────────────────────
// TENANCY
// ─────────────────────────────────────────────
 
model Tenant {
  id          String   @id @default(uuid())
  slug        String   @unique          // "ashoka"
  name        String                   // "Ashoka University"
  logoUrl     String?
  jwtSecret   String                   // per-tenant signing secret
  config      Json     @default("{}")  // TenantConfig shape
  isActive    Boolean  @default(true)
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
 
  users               User[]
  students            Student[]
  placementCycles     PlacementCycle[]
  jobs                Job[]
  companies           Company[]
  events              Event[]
  segmentations       Segmentation[]
  apiKeys             ApiKey[]
  notifications       Notification[]
 
  @@map("tenants")
}
 
// ─────────────────────────────────────────────
// AUTH + USERS
// ─────────────────────────────────────────────
 
model User {
  id         String    @id @default(uuid())
  tenantId   String
  email      String
  name       String
  avatarUrl  String?
  role       UserRole  @default(student)
  isActive   Boolean   @default(true)
  lastLoginAt DateTime?
  createdAt  DateTime  @default(now())
  updatedAt  DateTime  @updatedAt
 
  tenant     Tenant    @relation(fields: [tenantId], references: [id])
  student    Student?
  adminProfile AdminProfile?
 
  @@unique([tenantId, email])
  @@index([tenantId])
  @@map("users")
}
 
enum UserRole {
  super_admin
  admin_l1
  admin_l2
  verifier
  student
}
 
model ApiKey {
  id          String    @id @default(uuid())
  tenantId    String
  name        String    // human label: "Ashoka ERP Integration"
  keyHash     String    // bcrypt hash of the actual key
  keyPrefix   String    // first 8 chars for display: "pk_live_abcd..."
  permissions String[]  // ["students:read", "jobs:read"]
  expiresAt   DateTime?
  lastUsedAt  DateTime?
  revokedAt   DateTime?
  createdBy   String    // userId
  createdAt   DateTime  @default(now())
 
  tenant      Tenant    @relation(fields: [tenantId], references: [id])
 
  @@index([tenantId])
  @@index([keyHash])
  @@map("api_keys")
}
 
// ─────────────────────────────────────────────
// STUDENTS
// ─────────────────────────────────────────────
 
model Student {
  id          String   @id @default(uuid())
  tenantId    String
  userId      String   @unique
  
  // Academic
  rollNumber  String?
  batchYear   Int
  major       String
  minor       String?
  cgpa        Float?
  cgpaScale   Float    @default(10.0)
  category    String?  // general, obc, sc, st, ews
  
  // Custom columns (labels stored in tenant config)
  c1          Boolean  @default(false)
  c2          Boolean  @default(false)
  c3          Boolean  @default(false)
  c4          Boolean  @default(false)
  c5          Boolean  @default(false)
  
  profileVerified  Boolean  @default(false)
  isActive         Boolean  @default(true)
  
  // Debarment
  isDebarred       Boolean  @default(false)
  debarredUntil    DateTime?
  debarReason      String?
  
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
 
  tenant      Tenant   @relation(fields: [tenantId], references: [id])
  user        User     @relation(fields: [userId], references: [id])
  
  profileSections     ProfileSection[]
  resumes             Resume[]
  applications        Application[]
  cycleEnrollments    CycleEnrollment[]
  eventAttendances    EventAttendance[]
  verifierAssignments VerifierAssignment[]
  aiSummary           StudentAiSummary?
 
  @@index([tenantId])
  @@index([tenantId, batchYear])
  @@index([tenantId, major])
  @@map("students")
}
 
model ProfileSection {
  id           String              @id @default(uuid())
  tenantId     String
  studentId    String
  sectionType  ProfileSectionType
  data         Json                // section-specific JSON
  verificationStatus VerificationStatus @default(unverified)
  verifiedAt   DateTime?
  verifiedBy   String?
  createdAt    DateTime            @default(now())
  updatedAt    DateTime            @updatedAt
 
  student      Student  @relation(fields: [studentId], references: [id])
  documents    ProofDocument[]
  verificationRequests VerificationRequest[]
 
  @@index([tenantId, studentId])
  @@map("profile_sections")
}
 
enum ProfileSectionType {
  personal
  education
  experience
  projects
  skills
  achievements
  certifications
}
 
enum VerificationStatus {
  unverified
  pending
  verified
  rejected
}
 
model ProofDocument {
  id               String   @id @default(uuid())
  tenantId         String
  profileSectionId String
  fileName         String
  s3Key            String
  mimeType         String
  uploadedAt       DateTime @default(now())
 
  profileSection   ProfileSection @relation(fields: [profileSectionId], references: [id])
 
  @@index([tenantId, profileSectionId])
  @@map("proof_documents")
}
 
model AdminProfile {
  id         String   @id @default(uuid())
  tenantId   String
  userId     String   @unique
  department String?
  
  user       User     @relation(fields: [userId], references: [id])
  assignedCycles CycleAdmin[]
  assignedStudents VerifierAssignment[]
 
  @@index([tenantId])
  @@map("admin_profiles")
}
 
model VerifierAssignment {
  id         String   @id @default(uuid())
  tenantId   String
  verifierId String   // adminProfileId
  studentId  String
  assignedAt DateTime @default(now())
  assignedBy String   // userId
 
  verifier   AdminProfile @relation(fields: [verifierId], references: [id])
  student    Student      @relation(fields: [studentId], references: [id])
 
  @@unique([tenantId, verifierId, studentId])
  @@index([tenantId, verifierId])
  @@map("verifier_assignments")
}
 
// ─────────────────────────────────────────────
// PLACEMENT CYCLES
// ─────────────────────────────────────────────
 
model PlacementCycle {
  id                      String        @id @default(uuid())
  tenantId                String
  name                    String
  batchYear               Int
  type                    CycleType
  status                  CycleStatus   @default(draft)
  enrollmentOpen          Boolean       @default(false)
  eligibilitySegmentationId String?
  config                  Json          @default("{}")
  createdBy               String
  createdAt               DateTime      @default(now())
  updatedAt               DateTime      @updatedAt
 
  tenant                  Tenant        @relation(fields: [tenantId], references: [id])
  eligibilitySegmentation Segmentation? @relation(fields: [eligibilitySegmentationId], references: [id])
  jobs                    Job[]
  enrollments             CycleEnrollment[]
  admins                  CycleAdmin[]
 
  @@index([tenantId])
  @@map("placement_cycles")
}
 
enum CycleType {
  placement
  internship
}
 
enum CycleStatus {
  draft
  active
  closed
}
 
model CycleEnrollment {
  id         String   @id @default(uuid())
  tenantId   String
  cycleId    String
  studentId  String
  enrolledAt DateTime @default(now())
  enrolledBy String?  // null = self-enrolled
  
  cycle      PlacementCycle @relation(fields: [cycleId], references: [id])
  student    Student        @relation(fields: [studentId], references: [id])
 
  @@unique([tenantId, cycleId, studentId])
  @@index([tenantId, cycleId])
  @@map("cycle_enrollments")
}
 
model CycleAdmin {
  id         String   @id @default(uuid())
  tenantId   String
  cycleId    String
  adminId    String   // adminProfileId
  assignedAt DateTime @default(now())
 
  cycle      PlacementCycle @relation(fields: [cycleId], references: [id])
  admin      AdminProfile   @relation(fields: [adminId], references: [id])
 
  @@unique([tenantId, cycleId, adminId])
  @@map("cycle_admins")
}
 
// ─────────────────────────────────────────────
// JOBS + APPLICATIONS
// ─────────────────────────────────────────────
 
model Job {
  id                        String     @id @default(uuid())
  tenantId                  String
  cycleId                   String
  companyId                 String
  
  title                     String
  description               String
  location                  String?
  sector                    String?
  ctc                       Float?     // LPA
  ctcBreakdown              String?
  jobType                   JobType
  
  eligibilitySegmentationId String?
  acceptingApplications     Boolean    @default(false)
  deadline                  DateTime?
  
  requireResume             Boolean    @default(true)
  requireCoverLetter        Boolean    @default(false)
  requireVideo              Boolean    @default(false)
  
  status                    JobStatus  @default(draft)
  createdBy                 String
  createdAt                 DateTime   @default(now())
  updatedAt                 DateTime   @updatedAt
 
  tenant                    Tenant     @relation(fields: [tenantId], references: [id])
  cycle                     PlacementCycle @relation(fields: [cycleId], references: [id])
  company                   Company    @relation(fields: [companyId], references: [id])
  eligibilitySegmentation   Segmentation? @relation(fields: [eligibilitySegmentationId], references: [id])
  applications              Application[]
 
  @@index([tenantId])
  @@index([tenantId, cycleId])
  @@map("jobs")
}
 
enum JobType {
  fulltime
  internship
  ppo
}
 
enum JobStatus {
  draft
  published
  closed
}
 
model Application {
  id            String            @id @default(uuid())
  tenantId      String
  jobId         String
  studentId     String
  resumeId      String
  coverLetter   String?
  videoS3Key    String?
  
  status        ApplicationStatus @default(submitted)
  statusHistory Json              @default("[]")   // Array of { status, changedAt, changedBy, note }
  
  selfDebarred  Boolean           @default(false)
  autoDebarred  Boolean           @default(false)
  debarredUntil DateTime?
  
  submittedAt   DateTime          @default(now())
  updatedAt     DateTime          @updatedAt
 
  tenant        Tenant            @relation(fields: [tenantId], references: [id])
  job           Job               @relation(fields: [jobId], references: [id])
  student       Student           @relation(fields: [studentId], references: [id])
  resume        Resume            @relation(fields: [resumeId], references: [id])
 
  @@unique([tenantId, jobId, studentId])
  @@index([tenantId, studentId])
  @@index([tenantId, jobId])
  @@map("applications")
}
 
enum ApplicationStatus {
  submitted
  shortlisted
  rejected
  offered
  accepted
  withdrawn
}
 
// ─────────────────────────────────────────────
// RESUMES
// ─────────────────────────────────────────────
 
model Resume {
  id                    String   @id @default(uuid())
  tenantId              String
  studentId             String
  name                  String   // "Resume v1", "McKinsey variant"
  data                  Json     // Full resume JSON (see product/resume.md for schema)
  isAiVariant           Boolean  @default(false)
  baseResumeId          String?  // if this is a variant of another resume
  unverifiedChangeKeys  String[] // JSON paths of unverified edits: ["experience.0.bullets.2"]
  createdAt             DateTime @default(now())
  updatedAt             DateTime @updatedAt
 
  tenant       Tenant   @relation(fields: [tenantId], references: [id])
  student      Student  @relation(fields: [studentId], references: [id])
  applications Application[]
  verificationRequests VerificationRequest[]
 
  @@index([tenantId, studentId])
  @@map("resumes")
}
 
model VerificationRequest {
  id               String             @id @default(uuid())
  tenantId         String
  studentId        String
  resumeId         String?
  profileSectionId String?
  changeKeys       String[]           // which keys/bullets need verification
  status           VerificationStatus @default(pending)
  assignedTo       String?            // verifier userId
  verifierNote     String?
  resolvedAt       DateTime?
  resolvedBy       String?
  createdAt        DateTime           @default(now())
 
  @@index([tenantId, assignedTo])
  @@index([tenantId, status])
  @@map("verification_requests")
}
 
// ─────────────────────────────────────────────
// SEGMENTATION
// ─────────────────────────────────────────────
 
model Segmentation {
  id         String   @id @default(uuid())
  tenantId   String
  name       String?  // null = ad-hoc, not saved
  definition Json     // SegmentationDefinition shape
  createdBy  String
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt
 
  tenant     Tenant   @relation(fields: [tenantId], references: [id])
  cyclesEligibility  PlacementCycle[]
  jobsEligibility    Job[]
 
  @@index([tenantId])
  @@map("segmentations")
}
 
// ─────────────────────────────────────────────
// EVENTS
// ─────────────────────────────────────────────
 
model Event {
  id             String      @id @default(uuid())
  tenantId       String
  title          String
  companyId      String?
  type           EventType
  date           DateTime
  location       String?
  isOnline       Boolean     @default(false)
  meetingUrl     String?
  linkedJobId    String?
  attendanceMode AttendanceMode @default(manual)
  qrToken        String?
  qrTokenExpiresAt DateTime?
  createdBy      String
  createdAt      DateTime    @default(now())
  updatedAt      DateTime    @updatedAt
 
  tenant         Tenant      @relation(fields: [tenantId], references: [id])
  attendances    EventAttendance[]
  exceptionRequests EventExceptionRequest[]
 
  @@index([tenantId])
  @@map("events")
}
 
enum EventType {
  ppt
  test
  workshop
  info_session
}
 
enum AttendanceMode {
  qr
  manual
  both
}
 
model EventAttendance {
  id          String   @id @default(uuid())
  tenantId    String
  eventId     String
  studentId   String
  markedAt    DateTime @default(now())
  markedBy    String?  // null = self check-in via QR
  method      String   // "qr" or "manual"
 
  event       Event    @relation(fields: [eventId], references: [id])
  student     Student  @relation(fields: [studentId], references: [id])
 
  @@unique([tenantId, eventId, studentId])
  @@index([tenantId, eventId])
  @@map("event_attendances")
}
 
model EventExceptionRequest {
  id         String   @id @default(uuid())
  tenantId   String
  eventId    String
  studentId  String
  reason     String
  status     String   @default("pending")  // pending, approved, rejected
  reviewedBy String?
  reviewNote String?
  createdAt  DateTime @default(now())
 
  event      Event    @relation(fields: [eventId], references: [id])
 
  @@index([tenantId, eventId])
  @@map("event_exception_requests")
}
 
// ─────────────────────────────────────────────
// COMPANIES
// ─────────────────────────────────────────────
 
model Company {
  id          String   @id @default(uuid())
  tenantId    String?  // null = master list (shared across tenants)
  name        String
  sector      String?
  website     String?
  logoUrl     String?
  isApproved  Boolean  @default(true)  // false for student-suggested, pending admin approval
  suggestedBy String?  // studentId if student-suggested
  createdAt   DateTime @default(now())
 
  tenant      Tenant?  @relation(fields: [tenantId], references: [id])
  jobs        Job[]
 
  @@index([tenantId])
  @@index([name])
  @@map("companies")
}
 
// ─────────────────────────────────────────────
// NOTIFICATIONS (in-app)
// ─────────────────────────────────────────────
 
model Notification {
  id         String   @id @default(uuid())
  tenantId   String
  userId     String
  title      String
  body       String
  link       String?
  type       String   @default("info")
  read       Boolean  @default(false)
  createdAt  DateTime @default(now())
 
  tenant     Tenant   @relation(fields: [tenantId], references: [id])
 
  @@index([tenantId, userId, read])
  @@map("notifications")
}
 
// ─────────────────────────────────────────────
// AI
// ─────────────────────────────────────────────
 
model StudentAiSummary {
  id          String   @id @default(uuid())
  tenantId    String
  studentId   String   @unique
  summary     String
  generatedAt DateTime @default(now())
 
  student     Student  @relation(fields: [studentId], references: [id])
 
  @@index([tenantId])
  @@map("student_ai_summaries")
}

Key indexes

Beyond what Prisma creates for @id and @unique, always add:

-- Multitenancy — on every frequently queried table
CREATE INDEX idx_students_tenant ON students(tenant_id);
CREATE INDEX idx_jobs_tenant_cycle ON jobs(tenant_id, cycle_id);
CREATE INDEX idx_applications_tenant_student ON applications(tenant_id, student_id);
CREATE INDEX idx_applications_tenant_job ON applications(tenant_id, job_id);
 
-- Full-text search on students
CREATE INDEX idx_students_fts ON students USING gin(
  to_tsvector('english', coalesce(name, '') || ' ' || coalesce(email, '') || ' ' || coalesce(roll_number, ''))
);
 
-- Trigram on job title + company name for ILIKE search
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_jobs_title_trgm ON jobs USING gin(title gin_trgm_ops);

Row Level Security (second layer of defence)

ALTER TABLE students ENABLE ROW LEVEL SECURITY;
ALTER TABLE jobs ENABLE ROW LEVEL SECURITY;
ALTER TABLE applications ENABLE ROW LEVEL SECURITY;
-- (enable on all tables)
 
-- Policy: app_user can only see rows for their tenant
CREATE POLICY tenant_isolation ON students
  USING (tenant_id = current_setting('app.tenant_id')::uuid);
 
-- Set this at connection time in Prisma:
-- SET app.tenant_id = 'uuid';

This is a safety net. The Prisma middleware already filters by tenant_id — RLS catches any case where the middleware is bypassed.

Seed data

// packages/db/prisma/seed.ts
 
async function main() {
  // Create Ashoka tenant
  const tenant = await prisma.tenant.create({
    data: {
      slug: 'ashoka',
      name: 'Ashoka University',
      jwtSecret: 'dev-secret-ashoka-change-in-prod',
      config: {
        featureFlags: { aiResume: true, publicApi: true },
        domainRules: {
          allowedDomains: ['ashoka.edu.in'],
          matchers: [
            { contains: '_ug25', role: 'student' },
            { contains: '_ug2024', role: 'student' },
          ],
          allowAnyFromDomain: false,
        },
        customColumns: {
          c1Label: 'CPP attended',
          c2Label: 'IPP attended',
          c3Label: '', c4Label: '', c5Label: '',
        },
      },
    },
  });
 
  // Create super admin user
  await prisma.user.create({
    data: {
      tenantId: tenant.id,
      email: 'admin@ashoka.edu.in',
      name: 'Placement Admin',
      role: 'super_admin',
    },
  });
 
  // Seed master company list (top 100 Indian companies)
  await prisma.company.createMany({
    data: masterCompanies.map(c => ({ ...c, tenantId: null, isApproved: true })),
  });
}