Structured CV Tables

11 structured relational tables store all CV data. These tables are the sole source of truth -- there is no legacy fallback or dual-write. The legacy ParsedCV, CVField, CVFieldValue, and CVGroupFields models have been removed from the codebase.

Tables

All tables are linked to the parent CV record via a cvId foreign key. Singular tables (one record per CV) are marked accordingly; the rest are arrays ordered by an explicit order field.

Table	Cardinality	Description
CvProfile	1:1	Personal info (name, email, phone, bio, address, social links)
CvJobPreference	1:1	Salary expectations, job types, work site preference, availability
CvWorkExperience	1:N	Employment history (company, title, dates, technologies, achievements)
CvEducation	1:N	Degrees (institution, field of study, start/end dates, graduation year, GPA, honors)
CvCertification	1:N	Certifications (name, issuing org, dates, credential ID, location, status enum)
CvLanguage	1:N	Language proficiency with optional certification
CvProject	1:N	Portfolio projects with technologies, role, and URL
CvPublication	1:N	Papers and articles with publisher, co-authors, type
CvAward	1:N	Awards and honors with issuing organization
CvVolunteer	1:N	Volunteer work with skills
CvMetadata	1:1	Parsing metadata (confidence, qualityScore, extractionMethod, pageCount, wordCount, sections)

Read Strategy

Both the backend and background-tasks read directly from the structured tables.

Backend (NestJS)

The backend uses cv-structured.dataloader.ts with DataLoader batching to resolve structured CV fields. Field resolvers on the CVType GraphQL type lazily load each structured table only when the client requests it.

CVType
  |-- profile         --> CvProfileLoader.load(cvId)
  |-- jobPreference   --> CvJobPreferenceLoader.load(cvId)
  |-- workExperiences --> CvWorkExperienceLoader.load(cvId)
  |-- educations      --> CvEducationLoader.load(cvId)
  |-- ...             --> (one DataLoader per table)

Key behavior:

• DataLoaders batch multiple cvId lookups into a single SQL query per table.
• Field resolvers return null (singular) or [] (array) when no structured data exists.

Background-Tasks (Python)

The background-tasks service uses cv_query_repository.get_cv_data(pool, cv_id) as the single entry point for reading CV data. This function queries the 11 structured tables for the given cv_id and returns the result directly.

# Simplified flow
async def get_cv_data(pool, cv_id):
    return await _query_structured_tables(pool, cv_id)

Pattern

Always go through the structured table interfaces:

• Backend: Use the GraphQL field resolvers on CVType (backed by DataLoaders).
• Background-tasks: Use cv_query_repository.get_cv_data().

Write Strategy

CV data is written through two mechanisms: bulk write during CV parsing, and typed mutations for user edits.

Typed Mutations (Primary Write Path)

57 GraphQL mutations allow the frontend to edit structured CV data directly, one table at a time — 27 for job seekers (CV ownership) and 30 for company users (write access via talent authorization).

Job Seeker Mutations (27)

These mutations verify CV ownership (cv.userId === currentUser.id):

Pattern	Tables	Operations
1:1 upsert	CvProfile, CvJobPreference	updateMyCvProfile, updateMyCvJobPreference, deleteMyCvJobPreference
1:N CRUD	CvWorkExperience, CvEducation, CvCertification, CvLanguage, CvProject, CvPublication, CvAward, CvVolunteer	addMyCv{Table}, updateMyCv{Table}, removeMyCv{Table}
CVSkill	CVSkill (with Skill find-or-create)	addMyCvSkill, updateMyCvSkill, removeMyCvSkill

Company-Scoped Mutations (30)

These mutations verify company write access via TalentAuthorizationService.checkWriteAccessToCV() — the user must own the CV or have write access to the talent (via company talent pool membership or sharing with write permission):

Pattern	Tables	Operations
1:1 upsert	CvProfile, CvJobPreference	updateCvProfile, updateCvJobPreference, deleteCvJobPreference
1:N CRUD	CvWorkExperience, CvEducation, CvCertification, CvLanguage, CvProject, CvPublication, CvAward, CvVolunteer	addCv{Table}, updateCv{Table}, removeCv{Table}
CVSkill	CVSkill (with Skill find-or-create)	addCvSkill, updateCvSkill, removeCvSkill

info

Company-scoped mutations use the same input types and logic as the job seeker "My" variants. The only difference is the access control check — ownership vs. company write access. Naming convention: "My" mutations (addMyCvEducation) for job seekers, non-"My" mutations (addCvEducation) for company users.

Key Behaviors

• Job seeker mutations verify CV ownership (cv.userId === currentUser.id)
• Company-scoped mutations verify write access via TalentAuthorizationService
• Each mutation runs in a Prisma $transaction
• Every mutation emits cv.updated after the transaction commits via the @EmitCvUpdatedAfter method decorator (see Match Score Invalidation)
• 1:1 mutations use upsert semantics (create if not exists, update if exists)
• 1:N create mutations auto-assign order to the next available position if not provided
• CVSkill mutations find-or-create the Skill record by name (case-insensitive) and upsert the CVSkill link

@EmitCvUpdatedAfter decorator

A single TypeScript method decorator (src/talent-management/cv/decorators/emit-cv-updated.decorator.ts) wraps every mutation method on CvStructuredMutationService and fires cv.updated after the original method resolves successfully. This replaces ~30 inline this.emitCvUpdated(...) calls and guarantees that all 60 mutation methods (job-seeker + company scope, profile / job-pref / work-exp / education / certification / language / project / publication / award / volunteer / skill) emit consistently.

The decorator has three lookup modes for the cvId argument:

kind	When to use	How cvId is resolved
arg	add* methods with signature (cvId, userId, input)	args[argIndex] directly
verifyOwn	Job-seeker update*/remove* methods with signature (id, userId, …)	host.verifyRecordOwnership(table, id, userId)
verifyCompany	Company-scoped update*/remove* methods	host.verifyRecordCompanyAccess(table, id, userId)

Failures inside the lookup or emit step are caught and logged via the host's logger — they never propagate, because the underlying mutation has already committed at that point. If the original method itself throws, the decorator does not emit (correct).

Historical gap (SUP-00015)

Before the decorator was introduced, 21 structured-CV mutation methods on the job-seeker side (addCertification, addLanguage, addProject, addPublication, addAward, addVolunteer and their update*/remove* variants) silently skipped the emit. Editing those sections never invalidated match scores, so users on the dev environment saw match scores stale by 30+ days. The decorator closes that gap by being applied uniformly — see commit 6140173 on aiqlick-backend.

Bulk Write (CV Parsing)

When a CV is initially parsed by the AI extraction pipeline, all 11 structured tables are written.

Backend (NestJS)

cv-structured-write.helper.ts writes to the structured tables during CV extraction and update operations. It receives the parsed CV data, maps it to the 11 table schemas, and performs upserts (insert or update on conflict) within a transaction.

Background-Tasks (Python)

save_parsed_schema() in cv_repository.py calls structured_cv_repository.py to upsert all 11 structured tables within a database transaction.

Write Flow Diagram

Key Files

Backend (NestJS)

File	Purpose
cv.resolver.ts	GraphQL resolver with field resolvers for structured CV types
cv-structured.dataloader.ts	DataLoader batching for all 11 structured tables
cv-structured-write.helper.ts	Writes parsed data to structured tables during CV extraction (bulk upsert)
cv-structured-mutation.resolver.ts	27 typed GraphQL mutations for per-table CV edits
cv-structured-mutation.service.ts	Service layer for typed mutations (ownership checks, transactions, sync)
dto/cv-structured-inputs.ts	20 input types for typed mutations (UpdateCvProfileInput, CreateCvWorkExperienceInput, etc.)
cv-access-control.helper.ts	PII masking logic for public/unauthenticated access

Background-Tasks (Python)

File	Purpose
cv_query_repository.py	Reads from structured tables
structured_cv_repository.py	Writes to all 11 structured tables
cv_repository.py	save_parsed_schema() orchestrates writes to structured tables

PII Masking

CvProfile contains PII fields that must be masked for public or unauthenticated access (external share tokens, public talent pool views). The backend cv-access-control.helper.ts handles this transparently.

Masked Fields

The following fields return null for public/unauthenticated access:

Field	Category
email	Contact information
phone	Contact information
dateOfBirth	Protected characteristic (GDPR)
gender	Protected characteristic (GDPR)
addressStreet, addressCity, addressState, addressZipCode	Detailed address
profileImageUrl	Biometric data risk

Non-Masked Fields

These remain visible in public access: firstName, lastName, fullName, title, location, addressCountry, biography, linkedinUrl, portfolioUrl, currentRole, industries, specializations, yearsOfExperience, willingToRelocate, preferredLocation.

CvJobPreference Masking

Salary-related fields (expectedSalary, salaryCurrency, salaryFrequency) are also masked for public access. Non-sensitive preference fields (desiredJobTitles, preferredJobTypes, preferredWorkSite, availableIn) remain visible.

Detection

A request is considered "public" when:

• The request has no authenticated user.
• The request is marked as public access by middleware (req.isPublicAccess === true).
• The GraphQL query uses an external share resolver (e.g., talentPoolByToken, sharedTalentList, publicCV).

Current Status

The migration from legacy ParsedCV to structured tables is complete. The legacy models (ParsedCV, CVField, CVFieldValue, CVGroupFields) have been removed from the Prisma schema and codebase.

Read Path

All reads go through structured tables in both backend (DataLoader field resolvers) and background-tasks (cv_query_repository). Frontend queries include structured CV fragments (CvStructuredFields).

Write Path

27 typed mutations are available for the frontend to write directly to structured tables (see Structured CV Mutations). CVSkill mutations (addMyCvSkill, updateMyCvSkill, removeMyCvSkill) manage skills on a CV.

note

For historical reference on the frontend migration process, see Frontend Migration: Structured CV Tables.

Application Snapshots

When a job seeker is linked to a job as a candidate, the live CV is cloned into an immutable snapshot and the candidate row points at the snapshot. Snapshots are how the platform freezes the application CV at the moment of application — see Candidate Management — Application CV Snapshot for the user-facing rationale.

The isApplicationSnapshot field

CV.isApplicationSnapshot is a boolean defaulting to false. The clone helper sets it to true on every snapshot row, and three guards in the codebase check it before allowing any write:

Guard	Where	Behavior
verifyCvOwnership	cv-structured-mutation.service.ts	Job-seeker "My" mutations — throws NotFoundException if the CV is a snapshot
verifyRecordOwnership	cv-structured-mutation.service.ts	1:N child mutations (work experience, education, etc.) — throws NotFoundException when the parent CV is a snapshot
hasWriteAccessToCV	talent-authorization.service.ts	Company-scoped mutations — returns false for snapshots before the talent-write fallback

All three return the same NotFoundException (or false) for not-found, not-owned, and snapshot rejections, so callers can't probe for snapshot existence.

What gets cloned

cloneCvAsApplicationSnapshot(prisma, sourceCvId) (in src/talent-management/cv/cv-snapshot.helper.ts) clones:

• The parent CV row, with userId, talentId, and milvusPk set to null and isApplicationSnapshot = true. The S3 fields (url, path, text) are copied verbatim — the underlying file is shared with the live CV.
• All 1:1 children: CvProfile, CvJobPreference, CvMetadata
• All 1:N children: CvWorkExperience, CvEducation, CvCertification, CvLanguage, CvProject, CvPublication, CvAward, CvVolunteer
• CVSkill rows (the Skill master table is shared, only the join rows are cloned)

Every cloned row gets a fresh primary key. The parent CV is created first; all child writes then run in parallel via Promise.all. The caller is responsible for wrapping the call in a $transaction — both applyAsCandidate and addTalentAsCandidate do this so a partial failure cannot leave an orphan snapshot row behind.

Defense in depth

The snapshot's userId = null and talentId = null are not accidental. They mean:

• verifyCvOwnership(cvId, userId) rejects snapshots even before checking the isApplicationSnapshot flag — no userId can ever match null.
• hasWriteAccessToCV skips the talent-write fallback because there is no talentId to look up.
• External share resolvers and PII-masking helpers see no owner, so they treat snapshots as orphan CVs.

This is intentional layering: even if a future change accidentally bypasses the isApplicationSnapshot check, the missing userId/talentId continues to keep snapshots immutable.

Idempotency

The clone helper short-circuits if the source CV is itself already a snapshot (returns the source id unchanged). This makes the backfill script safe to re-run — applying it twice is a no-op on already-snapshotted candidates.