Entity Relationships
Visual entity-relationship diagrams for the AIQLick database schema. These diagrams are generated from the Prisma schema (aiqlick-backend/prisma/schema.prisma) and show how models relate across the platform.
- Domain Model — Conceptual overview of core entities and business rules
- Database Schema — Detailed schema reference with field types, constraints, and enums
How to Read These Diagrams
| Symbol | Meaning |
|---|---|
||--|| | One-to-one (exactly one on each side) |
||--o| | One-to-one (optional on right side) |
||--o{ | One-to-many (optional on many side) |
||--|{ | One-to-many (required on many side) |
}o--o{ | Many-to-many |
| PK | Primary key |
| FK | Foreign key |
| UK | Unique constraint |
Platform-Wide Domain Map
How the 15 domains connect at a high level:
Identity & Access
Core authentication, authorization, and multi-tenancy models.
Recruitment Core
Job postings, candidates, pipelines, and matching.
Unique constraints: @@unique([pipelineId, jobId]) on PipelineItem, @@unique([jobId, skillId]) on JobSkillRequirement, @@unique([jobId, cvId]) on MatchScore, @@unique([userId, jobId]) on UserJobMatchScore.
Talent Management
Talent profiles, pools, skills, and activity tracking.
Unique constraints: @@unique([userId, talentPoolId]) on Talent, @@unique([talentPoolId, skillId]) on TalentPoolSkill.
CV System
CV documents, extracted skills, and the 11 structured extraction tables.
The 11 structured tables are the sole source of truth for CV data. All reads and writes go through these tables directly. There is no legacy fallback.
CV Collections
Interview Scheduling
Interview lifecycle, calendar bookings, and self-scheduling slot offers.
Unique constraints: @@unique([bookingId, email]) on InterviewBookingAttendee, @@unique([offerId, email]) on InterviewSlotOfferAttendee.
Meetings & Transcription
Video conferencing, real-time transcription, and AI-generated insights.
Unique constraints: @@unique([meetingId, email]) on MeetingAttendee. Transcription uses @@map("transcriptions") for table name compatibility with background-tasks.
Billing & Credits
Subscription plans, Stripe integration, credit system, and AI operation tracking.
XOR constraint: CreditBalance must have exactly one of companyId (employer) or userId (job seeker) — enforced at the database level.
Promo Codes
Unique constraints: @@unique([promoCodeId, userId, subscriptionId]) on PromoCodeRedemption.
Notification System
Multi-channel notification delivery with per-user and per-company preferences.
Unique constraints: @@unique([userId, companyId]) on CompanySpecificNotificationPreference.
Messaging
Direct and group conversations between users.
Unique constraints: @@unique([conversationId, userId]) on ConversationParticipant.
AI Agent System
Company-scoped AI assistants with RAG, tool execution, and document management.
Unique constraints: @@unique([companyId, name]) on Agent, @@unique([agentId, taskCode]) on AgentTaskConfig, @@unique([companyId, agentId, periodStart, periodEnd]) on AgentTokenUsage.
Sharing & Collaboration
External sharing follows a consistent three-table pattern. Collaboration adds cross-company resource sharing.
Three-Table Sharing Pattern
Every shareable entity uses this pattern:
| Share Type | Table Pattern | Key Fields |
|---|---|---|
| User Share | {Entity}UserShare | userId, permissionLevel |
| Company Share | {Entity}CompanyShare | companyId, sharedById, permissionLevel |
| External Share | {Entity}ExternalShare | token (UK), expiresAt, accessCount, isActive |
Entities with sharing: TalentPool, Pipeline, CvCollection, Talent, SharedTalentList, UserProfile.
Talent Sharing
Collaboration System
User Profile & Job Seeker Sharing
Contacts
Company-scoped contact management with types and notes.
Inbound Email
IMAP-based email ingestion for automated job and CV creation.
Unique constraints: @@unique([address, imapHost, imapPort, imapFolder]) on InboundMailbox, @@unique([mailboxAddress, imapFolder, imapUid]) on InboundEmail, @@unique([inboundEmailId, attemptNumber]) on InboundEmailParseAttempt.
Other Models
System-wide utility models that don't belong to a specific domain.
Complete Model Index
All models organized alphabetically within their domain.
| # | Model | Domain | Primary Relations | Key Constraints |
|---|---|---|---|---|
| 1 | AdminAuditLog | System | User, Company | — |
| 2 | Agent | AI Agent | Company, User | @@unique([companyId, name]) |
| 3 | AgentActivity | AI Agent | Agent | — |
| 4 | AgentBackgroundTask | AI Agent | Company, AgentDocument | — |
| 5 | AgentConversation | AI Agent | Agent, User, Company | — |
| 6 | AgentDatabaseConfig | AI Agent | Agent (1:1) | agentId UK |
| 7 | AgentDocument | AI Agent | Agent, Company, User | — |
| 8 | AgentDocumentChunk | AI Agent | AgentDocument | pgvector 1024d |
| 9 | AgentMessage | AI Agent | AgentConversation, self-ref | — |
| 10 | AgentTaskConfig | AI Agent | Agent | @@unique([agentId, taskCode]) |
| 11 | AgentTokenUsage | AI Agent | Company, Agent | @@unique([companyId, agentId, periodStart, periodEnd]) |
| 12 | AgentToolExecution | AI Agent | AgentConversation, AgentMessage | — |
| 13 | AIOperationLog | Billing | Company, User, CreditTransaction (1:1) | creditTransactionId UK |
| 14 | ApplicationLog | System | User, Company | — |
| 15 | Candidate | Recruitment | User, Job, Talent, CV | — |
| 16 | CandidateNote | Recruitment | Candidate, User | — |
| 17 | CollaborationInvitation | Collaboration | User, Company | token UK |
| 18 | Collaborator | Collaboration | CollaborationInvitation (1:1), User | invitationId UK |
| 19 | CollaboratorJobShare | Collaboration | Collaborator, Job | @@unique([collaboratorId, jobId]) |
| 20 | CollaboratorPipelineShare | Collaboration | Collaborator, Pipeline | @@unique([collaboratorId, pipelineId]) |
| 21 | CollaboratorTalentPoolShare | Collaboration | Collaborator, TalentPool | @@unique([collaboratorId, talentPoolId]) |
| 22 | CollaboratorTalentShare | Collaboration | Collaborator (from/to), Talent | @@unique([fromCollaboratorId, toCollaboratorId, talentId]) |
| 23 | Company | Identity | UserCompanyRole, Job, TalentPool | slug UK |
| 24 | CompanyBillingConfig | Billing | User, Company (1:1) | @@unique([userId, companyId]) |
| 25 | CompanyConnectionRequest | Collaboration | Company | @@unique([fromCompanyId, toCompanyEmail, relationshipType]) |
| 26 | CompanyNotificationPreference | Notifications | Company (1:1) | companyId UK |
| 27 | CompanyRelationship | Collaboration | Company (A, B) | — |
| 28 | CompanyScoringDefault | Recruitment | Company (1:1) | companyId PK |
| 29 | CompanyShareAudit | Collaboration | Company | — |
| 30 | CompanySpecificNotificationPreference | Notifications | User, Company | @@unique([userId, companyId]) |
| 31 | Contact | Contacts | Company, User, UserCompanyRole | — |
| 32 | ContactExternalShare | Contacts | Contact | token UK |
| 33 | ContactNote | Contacts | Contact, User | — |
| 34 | ContactToContactTypes | Contacts | Contact, ContactType | composite PK |
| 35 | ContactType | Contacts | ContactToContactTypes | name UK |
| 36 | Conversation | Messaging | Company | — |
| 37 | ConversationParticipant | Messaging | Conversation, User | @@unique([conversationId, userId]) |
| 38 | CreditBalance | Billing | Company XOR User | companyId UK, userId UK |
| 39 | CreditCostConfig | Billing | — | @@unique([modelId, operationType]) |
| 40 | CreditPack | Billing | CreditPackPurchase | name UK, stripePriceId UK |
| 41 | CreditPackPurchase | Billing | CreditPack, User, Company | stripeCheckoutSessionId UK |
| 42 | CreditTransaction | Billing | CreditBalance, User | — |
| 43 | CV | CV System | Talent, User | milvusPk UK |
| 44 | CvAward | CV System | CV | @@index([cvId]) |
| 45 | CvCertification | CV System | CV | @@index([cvId]) |
| 46 | CvCollection | CV System | Company | — |
| 47 | CvCollectionCompanyShare | CV System | CvCollection, Company | — |
| 48 | CvCollectionExternalShare | CV System | CvCollection | token UK |
| 49 | CvCollectionItem | CV System | CvCollection, CV | composite PK |
| 50 | CvCollectionUserShare | CV System | CvCollection, User | — |
| 51 | CvEducation | CV System | CV | @@index([cvId]) |
| 55 | CvJobPreference | CV System | CV (1:1) | cvId UK |
| 56 | CvLanguage | CV System | CV | @@index([cvId]) |
| 57 | CvMetadata | CV System | CV (1:1) | cvId UK |
| 58 | CvProfile | CV System | CV (1:1) | cvId UK |
| 59 | CvProject | CV System | CV | @@index([cvId]) |
| 60 | CvPublication | CV System | CV | @@index([cvId]) |
| 61 | CVSkill | CV System | CV, Skill | @@unique([cvId, skillId]) |
| 62 | CvVolunteer | CV System | CV | @@index([cvId]) |
| 63 | CvWorkExperience | CV System | CV | @@index([cvId]) |
| 64 | System | — | — | |
| 65 | EmailVerificationToken | Identity | User (1:1) | userId UK, token UK |
| 66 | InboundEmail | Inbound Email | InboundMailbox, Company, Job, Talent, CV | @@unique([mailboxAddress, imapFolder, imapUid]) |
| 67 | InboundEmailAttachment | Inbound Email | InboundEmail | — |
| 68 | InboundEmailParseAttempt | Inbound Email | InboundEmail, Attachment | @@unique([inboundEmailId, attemptNumber]) |
| 69 | InboundMailbox | Inbound Email | InboundEmail | @@unique([address, imapHost, imapPort, imapFolder]) |
| 70 | Interview | Interviews | Candidate, Job | — |
| 71 | InterviewBooking | Interviews | Interview (1:1) | interviewId UK, externalBookingId UK |
| 72 | InterviewBookingAttendee | Interviews | InterviewBooking | rsvpToken UK, @@unique([bookingId, email]) |
| 73 | InterviewSlot | Interviews | InterviewSlotOffer | — |
| 74 | InterviewSlotOffer | Interviews | Candidate, Job, Interview (1:1) | token UK, interviewId UK |
| 75 | InterviewSlotOfferAttendee | Interviews | InterviewSlotOffer | @@unique([offerId, email]) |
| 76 | Invitation | Identity | Company, User, Role | token UK |
| 77 | Invoice | Billing | Subscription, User, Company, PaymentMethod | stripeInvoiceId UK |
| 78 | Job | Recruitment | Company (3 roles), User (4 roles) | — |
| 79 | JobSeekerProfileInvitation | Sharing | Talent, Company | — |
| 80 | JobSkillRequirement | Recruitment | Job, Skill | @@unique([jobId, skillId]) |
| 81 | MatchScore | Recruitment | CV, Job | @@unique([jobId, cvId]) |
| 82 | Meeting | Meetings | User, Company, InterviewBooking (1:1) | roomName UK, interviewBookingId UK |
| 83 | MeetingAttendee | Meetings | Meeting, User | rsvpToken UK, @@unique([meetingId, email]) |
| 84 | MeetingInsight | Meetings | Meeting, Interview | — |
| 85 | Message | Messaging | Conversation, User, self-ref | — |
| 86 | News | System | Company | — |
| 87 | Notification | Notifications | User, Company | 14 composite indexes |
| 88 | NotificationDeliveryLog | Notifications | Notification | — |
| 89 | NotificationTemplate | Notifications | — | name UK |
| 91 | PasswordResetToken | Identity | User (1:1) | userId UK, token UK |
| 92 | PaymentMethod | Billing | User | — |
| 93 | Permission | Identity | Role (M:M) | name UK |
| 94 | Pipeline | Recruitment | Company, User | — |
| 95 | PipelineCompanyShare | Sharing | Pipeline, Company | — |
| 96 | PipelineExternalShare | Sharing | Pipeline | token UK |
| 97 | PipelineItem | Recruitment | Pipeline, Job | @@unique([pipelineId, jobId]) |
| 98 | PipelineScoringConfig | Recruitment | Pipeline (1:1) | pipelineId UK |
| 99 | PipelineUserShare | Sharing | Pipeline, User | — |
| 100 | Plan | Billing | Subscription | name UK, stripePriceId UK |
| 101 | PromoCode | Billing | User, PromoCodePurchase | code UK |
| 102 | PromoCodeInvitation | Billing | PromoCode | token UK |
| 103 | PromoCodePlan | Billing | PromoCodePurchase | name UK, stripePriceId UK |
| 104 | PromoCodePurchase | Billing | PromoCodePlan, User | stripePaymentIntentId UK |
| 105 | PromoCodeRedemption | Billing | PromoCode, User, Subscription, Plan | @@unique([promoCodeId, userId, subscriptionId]) |
| 106 | Role | Identity | Permission (M:M), UserCompanyRole | name UK |
| 107 | SharedProfileList | Sharing | Company, User | — |
| 108 | SharedTalentList | Sharing | Company, User | — |
| 109 | SharedTalentListCompanyShare | Sharing | SharedTalentList, Company | @@unique([sharedListId, companyId]) |
| 110 | SharedTalentListExternalShare | Sharing | SharedTalentList | token UK |
| 111 | SharedTalentListItem | Sharing | SharedTalentList, Talent | composite PK |
| 112 | SharedTalentListUserShare | Sharing | SharedTalentList, User | @@unique([sharedListId, userId]) |
| 113 | Skill | Talent | CVSkill, JobSkillRequirement | name UK |
| 114 | StripeEvent | Billing | — | stripeEventId UK |
| 115 | Subscription | Billing | Plan, Company, User | stripeSubscriptionId UK |
| 116 | SystemSetting | System | User | key UK |
| 117 | Talent | Talent | User, TalentPool, CV | @@unique([userId, talentPoolId]) |
| 118 | TalentActivity | Talent | Talent, Company, User | — |
| 119 | TalentExternalShare | Sharing | Talent | token UK |
| 120 | TalentNote | Talent | Talent, User | — |
| 121 | TalentPool | Talent | Company, User | — |
| 122 | TalentPoolCompanyShare | Sharing | TalentPool, Company | @@unique([talentPoolId, sharedWithCompanyId]) |
| 123 | TalentPoolExternalShare | Sharing | TalentPool | token UK |
| 124 | TalentPoolSkill | Talent | TalentPool, Skill | @@unique([talentPoolId, skillId]) |
| 125 | TalentPoolUserShare | Sharing | TalentPool, User | @@unique([talentPoolId, userId]) |
| 126 | TalentShare | Sharing | Talent, Company, User | @@unique([talentId, sharedWithCompanyId]) |
| 127 | Transcription | Meetings | — (standalone) | @@map("transcriptions") |
| 128 | TwoFactorCode | Identity | User | — |
| 129 | UserCompanyRole | Identity | User, Company, Role, Invitation | invitationId UK |
| 130 | UserJobMatchScore | Recruitment | User, Job | @@unique([userId, jobId]) |
| 131 | UserNotificationPreference | Notifications | User (1:1) | userId UK |
| 132 | UserProfileCompanyShare | Sharing | User, Company, SharedProfileList | @@unique([userId, sharedWithCompanyId]) |
| 133 | UserProfileExternalShare | Sharing | User | token UK |
| 134 | UserProfileUserShare | Sharing | User (2) | @@unique([userId, sharedWithUserId]) |
Cross-Domain Connections
Key paths through the schema that span multiple domains:
Recruitment Pipeline
User → UserCompanyRole → Company → Job → Candidate → Interview → InterviewBooking → Meeting → Transcription → MeetingInsight
↑ ↓
Pipeline → PipelineItem
↑ ↓
TalentPool → Talent → CV → Structured Tables
Billing Flow
Plan → Subscription → Company → CreditBalance → CreditTransaction → AIOperationLog
↑ ↓
CreditPack → CreditPackPurchase Background Tasks
(CV extraction, matching,
agent chat, etc.)
AI Agent Pipeline
Company → Agent → AgentDocument → AgentDocumentChunk (pgvector embeddings)
↓
AgentConversation → AgentMessage → AgentToolExecution
↓ ↓
AgentTokenUsage RAG search / DB query / Talent search
Sharing Web
┌── UserShare
Entity (Talent, ───┼── CompanyShare
TalentPool, └── ExternalShare (token-based, 7-day expiry)
Pipeline, etc.)
┌── CollaboratorPipelineShare
Collaborator ───────┼── CollaboratorTalentShare
├── CollaboratorTalentPoolShare
└── CollaboratorJobShare
Schema Patterns
Recurring patterns used throughout the schema.
UUID Primary Keys
All models use uuid primary keys generated by @default(uuid()). No auto-incrementing integers.
Timestamps
Most models include createdAt DateTime @default(now()) and updatedAt DateTime @updatedAt. Some add deletedAt DateTime? for soft deletion.
External Sharing Token Pattern
All external share tables follow this structure:
token String @unique— double-UUID formatexpiresAt DateTime?— 7-day default expirationaccessCount Int @default(0)— tracks viewsisActive Boolean @default(true)— soft revocationemail String?— optional recipient
Structured CV Tables
CV extraction writes to 11 typed tables (CvProfile, CvWorkExperience, CvEducation, CvCertification, CvLanguage, CvProject, CvPublication, CvAward, CvVolunteer, CvJobPreference, CvMetadata). These are the sole source of truth for all CV data reads and writes.
Composite Primary Keys
Used for join tables: CvCollectionItem, SharedTalentListItem, ContactToContactTypes. Format: @@id([fieldA, fieldB]).
XOR Constraints
CreditBalance enforces exactly one of companyId or userId via database-level check constraints.
Optimistic Locking
InterviewSlotOffer uses a version Int @default(0) field for concurrent update protection.
Table Name Mapping
Transcription uses @@map("transcriptions") for snake_case table naming compatible with background-tasks AsyncPG queries.