Database Design — Overview

Date: 2026-02-27 Status: Active design — Phase 0 implements this schema Scope: Full schema for all 6 stage types, org-level feature gating, unified candidate pipeline, and candidate-facing dashboard

---

Why This Design?

What Exists (Current State)

Collection	Problems
User	No org model — just an organizationName string. No team concept.
JobOpening	Stages are one-size-fits-all. Only Automated call screening has any config beyond name/type.
Interview	round is free-text. isAutomated is a single boolean trying to describe 6 different stage types. No stage-type-specific storage.
Participant	No link to pipeline state per job.
Question	Screening questions only. No DSA problems, scenario questions, or behavioral questions.

What's Broken

• Frontend hardcoding: STAGE_TYPES array and STAGE_FIELD_CONFIG in frontend code — every new stage type requires a frontend deploy.
• No candidate pipeline state: To know where a candidate is, you must infer from Interview.result across multiple records. No single source of truth.
• No feature gating: Any recruiter can use any stage type — monetization is impossible to enforce.
• Naive duplicate check: Interview.findOne({ jobOpeningId, participantId, status: !CANCELLED }) incorrectly blocks the same candidate from being in multiple rounds of the same job.

---

Collections

#	Collection	Purpose
1	Plan	System-seeded pricing tiers
2	Organization	Recruiter team, billing, usage limits
3	User	Recruiter accounts (Firebase auth)
4	Participant	Candidate identity — recruiter-created, candidate-claimed
5	StageTypeConfig	System-seeded — UI shape for all 6 stage types
6	ScreeningQuestion	Q&A questions for automated screening
7	DSAProblem	Coding problems with hidden test cases
8	ScenarioQuestion	Open-ended scenarios for AI-assisted rounds
9	InterviewQuestion	Private interviewer question bank
10	JobOpening	Job listings with typed per-stage config
11	CandidatePipeline	Single source of truth for candidate state per job
12	Interview	One session per stage — all runtime data

---

Design Goals

1. Backend drives the UI — StageTypeConfig tells the frontend what stage types exist and what they look like. No hardcoding anywhere.
2. Per-stage polymorphic config — Each stage carries only the config relevant to its type.
3. Unified candidate pipeline — CandidatePipeline is the single source of truth.
4. Polymorphic question bank — Separate collections per domain, fully typed.
5. Org-level usage gating — All stage types available on all plans; plans differentiate via numeric limits.
6. Stage-type-specific interview data — Screening responses, DSA submissions, AI reports, live feedback stored in type-safe sub-documents.
7. Per-candidate stage overrides — Recruiters can customize questions/settings per candidate before invite. Overrides stored sparsely on Interview; if absent, Interview inherits JobOpening config at runtime.
8. Candidate dashboard via Firebase auth — First-login hook links authId to existing Participant record, giving access to all pipeline history across orgs.
9. Soft privacy boundary — Candidates see own answers, a single aggregate score, and coarse-grained statuses. Internal AI analysis, recruiter notes, and criteria scores are never exposed.