31 lines
1.4 KiB
SQL
31 lines
1.4 KiB
SQL
-- 029_cv_bank_profile.sql
|
|
-- Structured profile fields for banked CVs. Until now a bank row carried only
|
|
-- full_text, so the bank was write-only: you could store a CV but not search
|
|
-- or rank it. The employment agent already extracts these on upload; its
|
|
-- output was being discarded.
|
|
--
|
|
-- bank_reason records WHY the CV is held (speculative / referral); bank_expires_at
|
|
-- gives the retention policy something to enforce.
|
|
-- Applied at startup by alembic_setup.run_manual_sql().
|
|
|
|
ALTER TABLE app.manual_upload_candidate
|
|
ADD COLUMN IF NOT EXISTS skills JSONB NOT NULL DEFAULT '[]'::jsonb,
|
|
ADD COLUMN IF NOT EXISTS years_experience INTEGER,
|
|
ADD COLUMN IF NOT EXISTS education TEXT NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS bank_reason TEXT NOT NULL DEFAULT '',
|
|
ADD COLUMN IF NOT EXISTS bank_expires_at TIMESTAMPTZ;
|
|
|
|
-- Containment queries ("has React") need GIN; a btree on a JSONB array is useless.
|
|
CREATE INDEX IF NOT EXISTS ix_manual_upload_candidate_skills
|
|
ON app.manual_upload_candidate USING GIN (skills);
|
|
|
|
CREATE INDEX IF NOT EXISTS ix_manual_upload_candidate_years_experience
|
|
ON app.manual_upload_candidate (years_experience);
|
|
|
|
-- Rows banked before this migration were all speculative uploads: the only
|
|
-- writer of apply_via='cv_bank' is POST /candidate/cv-bank/upload.
|
|
UPDATE app.manual_upload_candidate
|
|
SET bank_reason = 'speculative'
|
|
WHERE apply_via = 'cv_bank'
|
|
AND COALESCE(TRIM(bank_reason), '') = '';
|