Index Strategy
Complete index reference for all 12 collections. Indexes are designed around the most frequent query patterns.
Plan
{ key: 1 } unique — plan lookup by key
{ isActive: 1 } list active plansOrganization
{ 'members.userId': 1 } auth middleware: find org for logged-in user
{ planKey: 1 } plan queries
{ isActive: 1 } list active orgsUser
{ authId: 1 } unique — auth middleware lookup by Firebase UID
{ email: 1 } unique — email uniqueness + lookup
{ organizationId: 1 } list org membersParticipant
{ email: 1 } unique — primary lookup when creating/finding by recruiter invite
{ authId: 1 } unique sparse — candidate dashboard auth lookup after Firebase login
{ userId: 1 } unique sparse — link to User record if candidate has a platform accountStageTypeConfig
{ key: 1 } unique — lookup by stage type key
{ isActive: 1, order: 1 } GET /v1/stage-types response, sorted by display orderScreeningQuestion
{ contentHash: 1 } unique — dedup on findOrCreate upsert
{ source: 1, category: 1 } grouped chip list for ScreeningQuestionsConfig panel
{ organizationId: 1, isDeleted: 1 } org's custom question library
{ usageCount: -1 } popularity sortDSAProblem
{ source: 1, difficulty: 1, isDeleted: 1 } problem picker: filter by difficulty
{ tags: 1 } problem picker: filter by topic
{ organizationId: 1, isDeleted: 1 } org's custom problem library
{ usageCount: -1 } popularity sortScenarioQuestion
{ applicableStageTypes: 1, scenarioType: 1, isDeleted: 1 } type + scenario filter
{ source: 1, difficulty: 1 } system vs custom + difficulty
{ organizationId: 1, isDeleted: 1 } org's custom scenario library
{ tags: 1 } tag filterInterviewQuestion
{ applicableStageTypes: 1, questionType: 1, isDeleted: 1 } type filter
{ source: 1, category: 1 } system questions by category
{ organizationId: 1, questionType: 1, isDeleted: 1 } org's custom question bank
{ tags: 1 } tag filterJobOpening
{ organizationId: 1, status: 1 } recruiter: list org jobs by status
{ recruiterId: 1, status: 1 } recruiter: list own jobs
{ isDeleted: 1, status: 1 } soft-delete filtering
{ title: 'text', description: 'text' } full-text searchCandidatePipeline
{ jobOpeningId: 1, participantId: 1 } unique — one pipeline per candidate per job
{ jobOpeningId: 1, status: 1 } recruiter: filter candidates by status in a job
{ organizationId: 1, status: 1 } recruiter: org-wide pipeline view
{ participantId: 1 } candidate: find all pipelines (cross-org)
{ 'stageProgression.interviewId': 1 } sparse — navigate from interview → pipeline
{ lastActivityAt: -1 } sort by most recent activity
{ candidateFacingStatus: 1 } candidate dashboard filteringInterview
{ candidatePipelineId: 1, stageId: 1 } Application-level uniqueness check
(not a DB unique constraint — allows re-invite
after status 'declined' or 'cancelled')
{ organizationId: 1, createdAt: 1 } Monthly interview count for usage limit enforcement
{ jobOpeningId: 1, status: 1 } Recruiter: filter interviews for a job
{ hostId: 1, status: 1 } Recruiter: own scheduled interviews
{ participantId: 1, status: 1 } Candidate: own interviews
{ screeningToken: 1 } Unique sparse — screening session auth
{ declineToken: 1 } Unique sparse — decline link auth
{ expiresAt: 1 } Expiry cron job + TTL processing
{ isDeleted: 1 } Soft-delete filtering
{ stageTypeKey: 1, status: 1 } Cross-job analytics queriesNotes on Uniqueness
| Pattern | How enforced |
|---|---|
One pipeline per (jobOpeningId, participantId) | DB unique index on CandidatePipeline |
One active interview per (candidatePipelineId, stageId) | Application-level check (409 Conflict). DB index is non-unique to allow re-invites after decline/cancel. |
Unique screeningToken, declineToken | DB unique sparse index on Interview |
Unique contentHash on questions | DB unique index on ScreeningQuestion.contentHash |
Unique authId on Participant | Sparse unique — null values don't conflict |
Unique authId on User | Regular unique |