Skip to main content

CV Skills Backfill

Two related bugs (SUP-00177, SUP-00178) caused some CVs to land in the database with CV.text populated but their structured tables (CvProfile, CvWorkExperience, …) and CVSkill rows missing. Both root-cause fixes shipped on 2026-04-29; this script is the one-shot remediation for the CVs already affected.

When to run​

  • A bulk import or a single CV upload finished but the candidate's skills don't show up on the talent profile, in matching results, or in the CV viewer.
  • A monitoring scan finds CVs where CV.text is non-empty and CVSkill for that CV is empty.
  • After deploying any future change to the structured-write pipeline that needs to re-process an existing snapshot of CVs.

The script is idempotent — it filters its target set by "has text and zero CVSkill rows", so re-running on an environment that's already been backfilled is a no-op.

How it works​

No S3 fetch, no OCR — the LLM call works directly off the cached CV.text. Cost is roughly $0.05 per CV at current Bedrock pricing for the Sonnet 4.5 extraction step.

Prerequisites​

  • AWS SSO session (aws sso login --profile Administrator-842697652860)
  • session-manager-plugin installed (brew install --cask session-manager-plugin)
  • Local checkout of background-tasks on a branch that contains the script (commit 500aee9 or later)
  • Python 3.11+ and the project's requirements.txt installed
  • AWS Bedrock credentials available to the process (the ECS bg-tasks task role on dev/prod has them; locally use SSO)

Procedure​

1. Open an SSM tunnel to the target RDS​

Dev (port 15433 via aiqlick-backend-dev):

aws ssm start-session \
  --profile Administrator-842697652860 \
  --region eu-north-1 \
  --target i-0b7d632f4de8a5a9a \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters '{"host":["aiqlick-postgres-dev.cx86go6iuul4.eu-north-1.rds.amazonaws.com"],"portNumber":["5432"],"localPortNumber":["15433"]}'

Prod (port 15434 via aiqlick-backend):

aws ssm start-session \
  --profile Administrator-842697652860 \
  --region eu-north-1 \
  --target i-0b9f31f3236c25b7f \
  --document-name AWS-StartPortForwardingSessionToRemoteHost \
  --parameters '{"host":["aiqlick-postgres.cx86go6iuul4.eu-north-1.rds.amazonaws.com"],"portNumber":["5432"],"localPortNumber":["15434"]}'

Leave that session running in its own terminal.

2. Export DATABASE_URL​

The script reads DATABASE_URL directly. Build it from Secrets Manager:

# Dev
export DATABASE_URL=$(aws secretsmanager get-secret-value \
  --profile Administrator-842697652860 --region eu-north-1 \
  --secret-id aiqlick-backend/development \
  --query 'SecretString' --output text \
  | python3 -c "import sys,json,re; u=json.load(sys.stdin)['DATABASE_URL']; print(re.sub(r'@[^:/]+:\d+/', '@localhost:15433/', u))")

# Prod — same shape, different secret + port
export DATABASE_URL=$(aws secretsmanager get-secret-value \
  --profile Administrator-842697652860 --region eu-north-1 \
  --secret-id aiqlick-backend/production \
  --query 'SecretString' --output text \
  | python3 -c "import sys,json,re; u=json.load(sys.stdin)['DATABASE_URL']; print(re.sub(r'@[^:/]+:\d+/', '@localhost:15434/', u))")

3. Dry-run first​

Always sanity-check the affected set before spending LLM credits:

cd background-tasks
python3 scripts/backfill_cv_skills.py --env prod --dry-run

Output lists the affected cv_ids and their S3 URLs. If the count looks wrong, stop and investigate before proceeding.

4. Process a small batch​

Validate cost + behaviour on a handful of CVs first:

python3 scripts/backfill_cv_skills.py --env prod --limit 5

Verify the per-CV log lines show non-zero skill_rows_after:

[ok] dbb66cf7-9853-4b33-a7a8-d8bf7169acb7 skills=14 (0→14) 6132ms

If a CV reports skills=0 it means the LLM couldn't extract anything from the cached text — that's a parsing-quality issue, not a write failure. The CV is still safe.

5. Run the full backfill​

Once the smoke test looks clean:

python3 scripts/backfill_cv_skills.py --env prod

The script logs ok=N failed=M total=T at the end and exits non-zero if any CV failed.

6. Single-CV mode​

For ad-hoc cleanup of a specific CV that came up in support:

python3 scripts/backfill_cv_skills.py --env prod --cv-id 9d445e6b-5602-46eb-87a2-017c6591a292

What gets written​

For each affected CV the script:

  1. Calls the same CVDataExtractor.extract(text) that the live pipeline uses, so output shape and proficiency normalization are identical to a fresh upload.
  2. Writes through structured_cv_repository.save_structured_cv_data — populates the 11 structured tables. Per-table failures log at ERROR with exc_info=True but don't abort the rest of the CV.
  3. Writes through cv_repository.save_cv_skills — populates CVSkill rows. Per-skill failures are isolated; one bad row no longer kills the whole batch.

The CV row itself is not modified — the script never touches CV.text, CV.url, or any FK from Candidate / Talent.

Verifying success​

After the run, query the same predicate the script uses:

SELECT count(*) FROM "CV" cv
LEFT JOIN (SELECT "cvId", COUNT(*) AS n FROM "CVSkill" GROUP BY "cvId") cs
  ON cs."cvId" = cv.id
WHERE COALESCE(cv."text",'') <> '' AND COALESCE(cs.n, 0) = 0;

Should drop to 0 (or to whatever residual is from CVs whose LLM extraction returned an empty skills list).