CV Snapshot Backfill

When the application-CV-snapshot mechanism (see Application CV Snapshot) was introduced, existing Candidate rows were still pointing at live CVs. New applications going forward use the snapshot path automatically, but pre-existing applications need a one-shot migration to retroactively freeze their CVs. That migration is what scripts/backfill-candidate-cv-snapshots.ts does.

This page is the runbook for running it against an environment.

When to run

Run the backfill once per environment after the backend deploy that introduced isApplicationSnapshot. Specifically:

• Dev: already executed during the SUP-00049 rollout (17 candidates).
• Production: must be run manually as part of the production cutover, after the dev → main PR is merged and the production backend redeploy completes. Per the platform's standing rule, prod-touching scripts are never executed automatically.

The script is idempotent — re-running it on an already-backfilled environment is a no-op (each candidate's CV is checked for isApplicationSnapshot and skipped if already frozen).

How it works

Each candidate's snapshot clone and the corresponding Candidate.cvId update share one transaction, so a partial failure cannot leave an orphan snapshot row behind.

Prerequisites

• AWS SSO session (aws sso login --profile Administrator-842697652860)
• session-manager-plugin installed (brew install --cask session-manager-plugin)
• Local checkout of aiqlick-backend on a branch that contains the script (commit b44c052 or later)
• Node 20+ with ts-node available via npx

Procedure

1. Open an SSM tunnel to the target RDS

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"]}'

Production (separate terminal, port 15434):

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 the tunnel running in its own terminal.

2. Dry-run

Always start with a dry-run to confirm the candidate count and inspect the affected rows. The script reads DATABASE_URL from the environment; build it from Secrets Manager and rewrite the host/port to point at the local tunnel:

Dev:

cd aiqlick-backend
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; from urllib.parse import urlparse, urlunparse; u=urlparse(json.load(sys.stdin)['DATABASE_URL']); print(urlunparse(u._replace(netloc=f'{u.username}:{u.password}@localhost:15433')))") \
  npx ts-node scripts/backfill-candidate-cv-snapshots.ts --dry-run

Production — substitute aiqlick-backend/production and localhost:15434:

cd aiqlick-backend
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; from urllib.parse import urlparse, urlunparse; u=urlparse(json.load(sys.stdin)['DATABASE_URL']); print(urlunparse(u._replace(netloc=f'{u.username}:{u.password}@localhost:15434')))") \
  npx ts-node scripts/backfill-candidate-cv-snapshots.ts --dry-run

The dry-run prints one line per candidate it would touch ([DRY] Would snapshot CV ... for candidate ...) and a final count.

3. Real run

Drop the --dry-run flag and re-execute the same command. Output will show one OK line per candidate with the original live CV id and the new snapshot id, followed by a Done. succeeded=N failed=0 summary.

4. Verify idempotency

Re-run the script with --dry-run once more. Expected output: Found 0 candidates that need snapshotting. Nothing to do.

5. Close the tunnel

Stop the SSM session in its terminal (Ctrl+C).

Optional flags

Flag	Purpose
--dry-run	Print which candidates would be migrated, write nothing
--limit N	Process at most N candidates (useful for staged production rollouts)

Failure modes

Symptom	Likely cause	Recovery
prisma.cV.findUnique is not a function	DATABASE_URL not set	Re-export it before running the command
Tunnel keeps disconnecting	SSO session expired	aws sso login --profile Administrator-842697652860 and re-open the tunnel
Per-candidate ERR lines	Source CV missing structured rows or constraint violation	The transaction rolls back; candidate keeps its live CV. Re-running picks them up; investigate the underlying CV before retrying
Done. succeeded=X failed=Y with Y > 0	Some candidates failed mid-run	Script exits with code 1; safe to re-run, idempotent

Background

The script was introduced as part of SUP-00049 (employer could see live updates to a candidate's CV after she edited it on her own side). The mechanism it migrates is documented under Candidate Management — Application CV Snapshot and Structured CV Tables — Application Snapshots.