Database Schema
AIQLick uses PostgreSQL 16 with Prisma 6 ORM. The schema is defined in aiqlick-backend/prisma/schema.prisma and contains 135 models and 79 enums organized across 15 domains.
For visual ER diagrams of every model and relationship, see Entity Relationships.
Both the NestJS backend (Prisma ORM) and Python background-tasks (raw AsyncPG) share the same database. Schema changes are made exclusively in the backend repo via Prisma. Background-tasks must manually update its SQL queries to match.
Extensions: pgvector (for AI Agent RAG embeddings)
Schema Statistics
| Metric | Count |
|---|---|
| Models | 135 |
| Enums | 79 |
| Composite indexes | 100+ |
| Unique constraints | 40+ |
| External sharing tables | 7 |
| Audit/activity log tables | 4 |
Domain Overview
| Domain | Models | Key Tables |
|---|---|---|
| User Management | 6 | User, TwoFactorCode |
| Company Management | 5 | Company, UserCompanyRole, CompanyRelationship |
| RBAC & Invitations | 5 | Role (8 roles), Permission (39 module permissions), UserRole (global), UserCompanyRole (company-scoped), Invitation |
| Talent Management | 15 | Talent, TalentPool, TalentShare, TalentActivity, TalentNote |
| CV System | 15 | CV, CVSkill, CvProfile, CvWorkExperience, CvEducation, CvCollection |
| Job & Matching | 4 | Job, JobSkillRequirement, MatchScore |
| Recruitment Pipeline | 8 | Candidate, Pipeline, PipelineItem |
| Interview Scheduling | 6 | Interview, InterviewBooking, InterviewSlotOffer |
| Meeting System | 4 | Meeting, MeetingAttendee, Transcription, MeetingInsight |
| Connections & Collaboration | 10 | Contact, Collaborator, CollaborationInvitation |
| Payment & Billing | 8 | Plan, Subscription, CreditBalance, Invoice |
| Credit System | 5 | CreditTransaction, CreditPack, AIOperationLog |
| Notification System | 6 | Notification, NotificationTemplate, NotificationDeliveryLog |
| AI Agent System | 11 | Agent, AgentConversation, AgentDocument, AgentDocumentChunk |
| Inbound Email | 4 | InboundEmail, InboundMailbox, InboundEmailAttachment |
Identity & Access
User
Core identity table for all platform users. Key fields: email (unique), selectedCompanyId (active company context), viewMode (EMPLOYER/JOB_SEEKER), isSuperAdmin, isActive, isVerified, activeCvId, stripeCustomerId.
Relations: UserCompanyRole (multi-company), Talent, CV, Subscription, CreditBalance, AgentConversation, Notification.
Company
Organization entity and primary multi-tenancy boundary. Key fields: companyName, slug (unique), companyType[], industryDomain[], size, status.
Relations: UserCompanyRole, Job (3 roles: owner, hiring, supplier), TalentPool, Pipeline, Subscription, CreditBalance, Agent.
UserCompanyRole
RBAC bridge: User + Company + Role. Links users to companies with specific roles. Optional invitationId for tracking how the assignment was created.
Role
8 named roles in 3 tiers: Admin (Super Admin, Company Admin), Member (Internal Sales, Recruiter/HR), Viewer (External Sales, Economy, Auditor/Viewer), plus a support role for ticket management. Assigned to users per-company via UserCompanyRole or globally via UserRole.
Permission
39 feature-module permissions across 13 modules (Opportunities, CV Database, Task Board, Contact List, Statistics, Share CV List, Share Job List, Network Expansion, Grow Company, Subscriptions, Projects, Tracking System, Economy), each with READ, WRITE, and DELETE levels. Production and development RDS both link these permissions to roles via _RolePermissions.
Authorization currently relies on role name checks (@HasRole decorator and roles.ts tier functions) rather than resolver-level permission checks. The @HasPermission decorator is defined but unused in application resolvers. See Roles & Permissions for the RDS-verified matrix.
Invitation
Company team invitations with token-based acceptance. Status: PENDING, ACCEPTED, REJECTED, CANCELLED.
Recruitment
Job
Job posting with three company roles: companyId (owner), hiringCompanyId (client), suppliedById (supplier). Status tracks lifecycle: NEW -> IN_PROGRESS -> PENDING_FEEDBACK -> CLOSED. Supports interviewSettings (JSON) for default interview configuration.
Candidate
Talent applied to a specific job. Tracks pipeline status: NEW -> IN_REVIEW -> INTERVIEW -> APPROVED/REJECTED/WITHDRAWN. Links to userId (who added), jobId, talentId, and optional cvId. The cvId points at an immutable application snapshot of the CV (cloned at apply time), not the talent's live CV — see Application CV Snapshot.
Pipeline / PipelineItem
Kanban-style workflow boards. Pipelines are company-owned with types (ACTIVE/PRIVATE/ARCHIVED). Items link jobs to pipelines with @@unique([pipelineId, jobId]).
Talent / TalentPool
Talent profiles stored in company-owned pools. @@unique([userId, talentPoolId]) ensures one talent per user per pool. Pools have status (ACTIVE/INACTIVE/ARCHIVED/DRAFT) and public visibility toggle.
TalentNote
Internal notes attached directly to talent profiles, independent of job candidacy. Each note has a talentId, authorId, optional title, and required content. Only the original author can edit or delete their notes. Cascade-deletes with the parent talent.
MatchScore / UserJobMatchScore
Two match-score tables with distinct lifecycles, both written by the unified MatchingService._score_pair() engine:
| Table | Key | Lifecycle | Read By |
|---|---|---|---|
UserJobMatchScore | @@unique([userId, jobId]) | Live. Invalidated on CV or job change, regenerated by the 5-second background loop | Job-seeker dashboards, recommended-talents grids |
MatchScore | @@unique([jobId, cvId]) | Snapshot. Frozen to the Candidate's (usually snapshotted) CV; immutable for the life of that CV | Candidate.matchReport, Candidate.matchScore (employer view) |
Both tables are indexed on [jobId, score DESC] for ranked retrieval and store score + details (JSON with basicScores, detailedReport, and the raw unified-engine breakdown). The legacy qualifications snapshot column on UserJobMatchScore was dropped in SUP-00019 — the matching engine now reads CV data directly from the 11 structured CV tables at scoring time.
See Matching Engine — Candidate Match Snapshots for the dual-write flow and the candidate.created event chain that populates MatchScore.
CV System
CV / Structured CV Tables
CVs stored in S3 with extracted text. AI extraction writes parsed data into 11 normalized tables: CvProfile (personal info), CvJobPreference (salary, job types), CvWorkExperience, CvEducation, CvCertification, CvLanguage, CvProject, CvPublication, CvAward, CvVolunteer, and CvMetadata (parsing quality). Skills are stored in CVSkill (join table with Skill). The isApplicationSnapshot boolean marks a CV as a frozen clone created when a candidate is added to a job — see Structured CV Tables — Application Snapshots.
CvCollection
Named collections of CVs per company. Items use composite PK @@id([CvCollectionId, cvId]). Three-way sharing (user, company, external token).
Interview & Meetings
Interview / InterviewBooking
Interviews linked to candidates and jobs. Status: PENDING -> SCHEDULED -> IN_PROGRESS -> COMPLETED/EVALUATED. Bookings integrate with Cal.com via externalBookingId. Reminder fields prevent duplicate sends at 24h/6h/1h.
Meeting / MeetingAttendee
Generic meetings with Jitsi integration. roomName (unique) identifies the Jitsi room. Attendees track join/leave times and RSVP via unique tokens.
Transcription
Real-time speech-to-text segments written by background-tasks via Jigasi/Whisper. meetingId correlates with Meeting.roomName. Table mapped to transcriptions (snake_case) for background-tasks compatibility.
MeetingInsight
AI-generated meeting analysis with versioning for live updates. Contains structured JSON fields: skillsAssessment, communicationAnalysis, keyTopicsSummary, redFlagsAndConcerns, plus a fullReport in markdown.
Billing & Credits
Plan
Subscription tier with feature limits. Uses convention: -1 = unlimited, 0 = disabled, >0 = specific limit. Monthly limits (containing "PerMonth") reset on the 1st of each month UTC. Key limits: maxJobs, maxTalentPools, maxCvParsesPerMonth, maxAgentMessagesPerMonth, creditsPerMonth. The billingMode field (PREPAID default or POSTPAID) determines whether the plan uses the standard credit system or enterprise usage-based invoicing.
Subscription
Company-based subscription to a Plan. Synced with Stripe via stripeSubscriptionId. Status lifecycle: INCOMPLETE -> ACTIVE -> CANCELED/PAST_DUE/UNPAID/PAUSED.
CreditBalance
Credit account scoped to either a company (employer mode) or user (job seeker mode). XOR constraint enforced at DB level: exactly one of companyId/userId must be non-null. Tracks balance, totalEarned, totalConsumed.
CreditTransaction
Immutable credit ledger. Types: PLAN_ALLOCATION, PACK_PURCHASE, CONSUMPTION, MANUAL_ADJUSTMENT, INITIAL_GRANT, REFUND. Each entry records amount (positive/negative) and balanceAfter snapshot.
AIOperationLog
Tracks all LLM operations with token counts and credit cost. Types: CV_EXTRACTION, JOB_PARSING, MEETING_INSIGHT, MATCHING, TEXT_REWRITE, AGENT_CHAT. Written by background-tasks only.
AI Agent System
Agent
Company-scoped AI assistant configuration. @@unique([companyId, name]). Configures LLM provider/model, temperature, RAG settings (ragEnabled, ragTopK, ragMinScore), enabled tools, and rate limits.
AgentConversation / AgentMessage
Chat sessions between users and agents. Messages have roles (SYSTEM/USER/ASSISTANT/TOOL) and processing status (PENDING -> STREAMING -> COMPLETED). Token usage tracked per message and per conversation.
AgentDocument / AgentDocumentChunk
Documents uploaded for RAG. Chunks store vector embeddings using pgvector (1024 dimensions, Titan Embed v2). Processing pipeline: PENDING -> CHUNKING -> EMBEDDING -> COMPLETED. Vector operations use raw SQL via AsyncPG.
Notification System
Notification
Individual notification records with 30+ types across 7 categories (RECRUITMENT, COLLABORATION, TALENT_MANAGEMENT, USER_COMPANY, BILLING, MATCHING, SYSTEM). Priority levels: LOW, NORMAL, HIGH, URGENT. Multi-channel delivery: IN_APP, EMAIL, PUSH, SMS.
NotificationDeliveryLog
Per-channel delivery tracking. Status: PENDING -> SENT -> DELIVERED/FAILED/BOUNCED/RETRY.
External Sharing Pattern
All externally shareable entities follow a consistent three-table pattern:
| Share Type | Pattern | Key Fields |
|---|---|---|
| User Share | {Entity}UserShare | userId, permissionLevel |
| Company Share | {Entity}CompanyShare | companyId, sharedById, permissionLevel |
| External Share | {Entity}ExternalShare | token (unique), expiresAt, accessCount, isActive |
Entities with external sharing: TalentPool, Pipeline, CvCollection, Talent, SharedTalentList, UserProfile, SharedProfileList.
External tokens use double-UUID format with 7-day default expiration.
Key Enums
Recruitment & Jobs
| Enum | Values |
|---|---|
| JobStatus | NEW, IN_PROGRESS, PENDING_FEEDBACK, CLOSED, FUTURE_REQUESTS, DRAFT |
| CandidateStatus | NEW, IN_REVIEW, INTERVIEW, APPROVED, REJECTED, WITHDRAWN |
| InterviewStatus | PENDING, SCHEDULED, COMPLETED, CANCELLED, NO_SHOW, IN_PROGRESS, EVALUATED |
| MeetingStatus | SCHEDULED, IN_PROGRESS, COMPLETED, CANCELLED, NO_SHOW |
| TalentPoolStatus | ACTIVE, INACTIVE, ARCHIVED, DRAFT |
Billing & Credits
| Enum | Values |
|---|---|
| SubscriptionStatus | INCOMPLETE, INCOMPLETE_EXPIRED, TRIALING, ACTIVE, PAST_DUE, CANCELED, UNPAID, PAUSED, PENDING_APPROVAL |
| InvoiceStatus | PENDING, PAID, FAILED, REFUNDED, PARTIALLY_REFUNDED, OVERDUE |
| BillingMode | PREPAID, POSTPAID |
| CreditTransactionType | PLAN_ALLOCATION, PACK_PURCHASE, CONSUMPTION, MANUAL_ADJUSTMENT, INITIAL_GRANT, REFUND, TRANSFER |
| AIOperationType | CV_EXTRACTION, JOB_PARSING, MEETING_INSIGHT, MATCHING, TEXT_REWRITE, AGENT_CHAT |
| AIOperationStatus | SUCCESS, FAILED, INSUFFICIENT_CREDITS, PLAN_LIMIT_EXCEEDED, SUBSCRIPTION_INACTIVE, RATE_LIMITED |
User & Identity
| Enum | Values |
|---|---|
| ViewMode | EMPLOYER, JOB_SEEKER |
| InvitationStatus | PENDING, ACCEPTED, REJECTED, CANCELLED |
| PermissionLevel | READ_ONLY, EDIT, ADMIN |
AI Agent
| Enum | Values |
|---|---|
| AgentType | RECRUITMENT_ASSISTANT, TALENT_SEARCH, JOB_MATCHING, INTERVIEW_PREP, DATA_ANALYST, CUSTOM |
| AgentProcessingStatus | PENDING, PROCESSING, STREAMING, COMPLETED, FAILED, CANCELLED |
| AgentDocumentStatus | PENDING, PROCESSING, CHUNKING, EMBEDDING, COMPLETED, FAILED |
| AgentToolType | DATABASE_QUERY, RAG_SEARCH, WEB_SEARCH, TALENT_SEARCH, JOB_SEARCH, CV_ANALYSIS, CALENDAR_CHECK, EMAIL_DRAFT, CUSTOM |
Notifications
| Enum | Values |
|---|---|
| NotificationCategory | RECRUITMENT, COLLABORATION, TALENT_MANAGEMENT, USER_COMPANY, BILLING, MATCHING, SYSTEM |
| NotificationPriority | LOW, NORMAL, HIGH, URGENT |
| NotificationChannel | IN_APP, EMAIL, PUSH, SMS |
| DeliveryStatus | PENDING, SENT, DELIVERED, FAILED, BOUNCED, RETRY |