// Findings: A-04 (relational jobs model), S-01/S-02 (minimised PII, consent, retention), // A-02 (idempotency, dedupe), A-03 (outbox), S-04 (double opt-in), S-08 (moderation) // // PII columns hold ciphertext produced by 08-pii-encryption/field-encryption.ts. // `emailHash` is a keyed HMAC (blind index) so the app can dedupe and look up applicants // without decrypting or storing a plaintext email column. generator client { provider = "prisma-client" output = "./generated" } datasource db { provider = "postgresql" } enum EmploymentType { INTERNSHIP FULL_TIME } enum ApplicationStatus { RECEIVED SCREENING INTERVIEW OFFER REJECTED WITHDRAWN } enum QualificationLevel { SCHOOL_10 SCHOOL_12 DIPLOMA BACHELOR MASTER DOCTORATE PROFESSIONAL OTHER } enum EducationStatus { COMPLETED PURSUING } enum ScoreSystem { CGPA PERCENTAGE } enum OutboxStatus { PENDING SENT FAILED } enum ModerationStatus { PENDING APPROVED REJECTED } model JobPosting { id String @id @default(cuid()) slug String @unique title String summary String team String employmentType EmploymentType location String @default("Remote / Jaipur") isOpen Boolean @default(true) bodyMarkdown String createdAt DateTime @default(now()) updatedAt DateTime @updatedAt applications Application[] @@index([isOpen, employmentType]) } model Application { id String @id @default(cuid()) jobPostingId String jobPosting JobPosting @relation(fields: [jobPostingId], references: [id], onDelete: Restrict) // Encrypted PII (AES-256-GCM, key-versioned). Never selected into public DTOs. fullNameEnc String emailEnc String phoneEnc String emailHash String // HMAC-SHA256(email), for dedupe and lookups phoneIsWhatsApp Boolean @default(true) currentCity String noticePeriodDays Int skills String[] portfolioUrl String linkedinUrl String githubUrl String? statement String status ApplicationStatus @default(RECEIVED) statusChangedAt DateTime @default(now()) firstReviewedAt DateTime? // measures the advertised 48-hour review SLA qualifications Qualification[] consent Consent? createdAt DateTime @default(now()) updatedAt DateTime @updatedAt purgeAfter DateTime // set at insert: createdAt + retention period // One application per person per role; re-applying updates instead of duplicating. @@unique([jobPostingId, emailHash]) @@index([status, createdAt]) @@index([purgeAfter]) } model Qualification { id String @id @default(cuid()) applicationId String application Application @relation(fields: [applicationId], references: [id], onDelete: Cascade) level QualificationLevel degree String specialization String institution String graduationYear Int status EducationStatus scoreSystem ScoreSystem scoreValue Decimal @db.Decimal(5, 2) position Int // order shown in the form @@index([applicationId]) } model Consent { id String @id @default(cuid()) applicationId String @unique application Application @relation(fields: [applicationId], references: [id], onDelete: Cascade) purpose String // e.g. "recruitment:backend-developer-intern" privacyPolicyVersion String // the version the applicant actually saw, e.g. "2026-05-20" grantedAt DateTime @default(now()) ipHash String // HMAC of IP: evidences consent without storing the raw IP withdrawnAt DateTime? } model IdempotencyKey { key String @id // client-generated UUID per submission attempt scope String // e.g. "applications:create" requestHash String // SHA-256 of the body: same key + different body = 422 responseCode Int? responseBody Json? createdAt DateTime @default(now()) expiresAt DateTime @@index([expiresAt]) } model OutboxMessage { id String @id @default(cuid()) kind String // "email.application_received", "email.team_notification", ... payload Json // template data only, never raw free-text from the user status OutboxStatus @default(PENDING) attempts Int @default(0) nextAttemptAt DateTime @default(now()) lastError String? createdAt DateTime @default(now()) sentAt DateTime? @@index([status, nextAttemptAt]) } model Subscriber { id String @id @default(cuid()) emailEnc String emailHash String @unique tokenHash String? @unique expiresAt DateTime? confirmedAt DateTime? createdAt DateTime @default(now()) unsubscribedAt DateTime? } model DiaryEntry { id String @id @default(cuid()) slug String @unique title String excerpt String body String // stored and rendered as plain text, never as HTML persona String // public label, e.g. "3rd Year Student" authorEmailEnc String? // private; must never reach a client component status ModerationStatus @default(PENDING) publishedAt DateTime? createdAt DateTime @default(now()) @@index([status, publishedAt]) }