Janady13/missionmatch / db/queries/candidates.sql

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / db/queries/candidates.sql
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	db/queries/candidates.sql
Original SHA-256	effb27d1f4feb85d06c68cd323926ef938b0fe9e58dd95568fc74406747399a0
Git blob SHA	0c73c29f5500d3acaeb96ba8359ec817359b6b0c
Reading-copy SHA-256	effb27d1f4feb85d06c68cd323926ef938b0fe9e58dd95568fc74406747399a0
Original source ↗
Recovered source transcript

Download complete source text ↓313 lines · 17.0 KB
-- name: CreateCandidate :one
INSERT INTO candidates (
    organization_id, full_name, email, headline,
    clearance_level, clearance_poly, clearance_status,
    years_experience, location, remote, relocatable, available, resume_embedding
) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13)
RETURNING *;

-- name: GetCandidate :one
SELECT * FROM candidates WHERE id = $1 AND deleted_at IS NULL;

-- name: FindCandidateByEmail :one
-- Identity resolution blocking key 1: an exact (case insensitive) email match in the same org. Email is
-- the strongest identity signal, so a sourced lead with a known email merges into the existing person.
SELECT id FROM candidates
WHERE organization_id = $1 AND deleted_at IS NULL AND email IS NOT NULL AND lower(email) = lower($2)
ORDER BY created_at LIMIT 1;

-- name: FindCandidateByNameLocation :one
-- Identity resolution blocking key 2: same normalized full name AND same normalized location in the org.
-- Name alone is too weak (two John Smiths); name plus location is a defensible second signal. The caller
-- only invokes this with a non empty location.
SELECT id FROM candidates
WHERE organization_id = $1 AND deleted_at IS NULL
  AND lower(full_name) = lower($2) AND lower(COALESCE(location, '')) = lower($3)
ORDER BY created_at LIMIT 1;

-- name: LinkSourcedCandidate :exec
-- Identity resolution merge: link a sourced lead to an existing candidate. Sets the external id and
-- backfills headline and years ONLY where the existing row is empty or lower, never clobbering curated
-- bench data. Source is intentionally left unchanged so a verified bench person stays a bench person.
UPDATE candidates SET
  external_id      = COALESCE(external_id, $2),
  headline         = COALESCE(headline, $3),
  years_experience = GREATEST(years_experience, $4),
  updated_at       = now()
WHERE id = $1;

-- name: UpdateCandidateEmbedding :exec
-- Writes a precomputed resume embedding onto a candidate, used by the bench import path AFTER the embed
-- network call has already run in the caller, so the migrator write transaction never holds open across
-- the inference round trip (the embed lock window rule). The vector is composed from name free structured
-- tokens only, never raw resume PII. A candidate with a NULL embedding is left embeddable by the
-- embed-backfill only-missing fallback, so a skipped embed never wedges a row.
UPDATE candidates SET resume_embedding = sqlc.arg(embedding)::vector, updated_at = now() WHERE id = sqlc.arg(id);

-- name: SetCandidateClearanceProvenance :execrows
-- An org FSO records HOW a candidate's clearance is known. RLS scopes this to the caller's own org.
-- verified_at is the date the FSO confirmed it; it is NULL for a self reported indicator. The platform
-- never verifies a clearance itself, it records the org's authoritative assertion.
UPDATE candidates
SET clearance_provenance = $2, clearance_verified_at = $3, updated_at = now()
WHERE id = $1 AND deleted_at IS NULL;

-- name: ListCandidates :many
-- id is a unique trailing sort key so created_at ties give a total order and stable LIMIT/OFFSET paging.
SELECT * FROM candidates
WHERE deleted_at IS NULL
ORDER BY created_at DESC, id DESC
LIMIT $1 OFFSET $2;

-- name: ListCandidatesForIntel :many
-- Available (staffable) candidates first, most recently updated next, so the workforce intelligence
-- surface leads with the talent you can deploy and newly staffed or sourced people are always scored.
-- The trailing id makes the order total, so the same set is selected before the LIMIT cut every run.
SELECT * FROM candidates
WHERE deleted_at IS NULL
ORDER BY available DESC, updated_at DESC, created_at DESC, id
LIMIT $1;

-- name: SoftDeleteCandidate :exec
UPDATE candidates SET deleted_at = now(), updated_at = now() WHERE id = $1;

-- name: AnonymizeCandidate :execrows
-- Right to erasure as anonymize in place: wipe the identifying PII while KEEPING the row so the de
-- identified scored outcomes (slate_members) and the demographic bucket survive for the adverse impact
-- record. Once the name, email, contact, and resume vector are gone the row is no longer personal data,
-- so erasure is satisfied and the compliance history is preserved. Idempotent on anonymized_at.
UPDATE candidates SET
    full_name        = '[erased]',
    email            = NULL,
    headline         = NULL,
    location         = NULL,
    resume_embedding = NULL,
    external_id      = NULL,
    available        = false,
    anonymized_at    = now(),
    updated_at       = now()
WHERE id = $1 AND anonymized_at IS NULL;

-- name: AddCandidateSkill :exec
INSERT INTO candidate_skills (organization_id, candidate_id, skill_id)
VALUES ($1,$2,$3)
ON CONFLICT (candidate_id, skill_id) DO NOTHING;

-- name: ListCandidateSkills :many
SELECT s.id, s.name
FROM candidate_skills cs
JOIN skills s ON s.id = cs.skill_id
WHERE cs.candidate_id = $1
ORDER BY s.name;

-- name: AddCandidateCertification :exec
INSERT INTO candidate_certifications (organization_id, candidate_id, name)
VALUES ($1,$2,$3)
ON CONFLICT (candidate_id, name) DO NOTHING;

-- name: ListCandidateCertifications :many
SELECT name FROM candidate_certifications WHERE candidate_id = $1 ORDER BY name;

-- name: AddCandidateCustomerExperience :exec
INSERT INTO candidate_customer_experience (organization_id, candidate_id, agency)
VALUES ($1,$2,$3)
ON CONFLICT (candidate_id, agency) DO NOTHING;

-- name: ListCandidateCustomerExperience :many
SELECT agency FROM candidate_customer_experience WHERE candidate_id = $1 ORDER BY agency;

-- name: SearchCandidatesHybrid :many
-- Hybrid retrieval: pgvector cosine kNN fused with full-text rank via Reciprocal Rank Fusion (k=60).
-- RLS already scopes both legs to the caller's org. $1=query embedding, $2=limit, $3=text query.
-- The vector arm is MATERIALIZED so the relaxed_order HNSW output is re-sorted by TRUE cosine distance
-- before it is ranked: under hnsw.iterative_scan = relaxed_order the index returns rows slightly out of
-- distance order, and ranking them without the re-sort would mis-rank the nearest neighbors at scale
-- (pgvector 0.8 README; AWS pgvector 0.8 Aurora guidance). Every ORDER BY carries a unique tiebreaker
-- (id) so results are byte-stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs
-- LOCAL hnsw.ef_search and hnsw.iterative_scan first.
WITH vec_raw AS MATERIALIZED (
    SELECT id, (resume_embedding <=> $1::vector) AS distance
    FROM candidates
    WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL
    ORDER BY resume_embedding <=> $1::vector, id
    LIMIT $2
),
vec AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk
    FROM vec_raw
),
fts AS (
    SELECT id, ROW_NUMBER() OVER (
               ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $3)) DESC, id
           ) AS rnk
    FROM candidates
    WHERE deleted_at IS NULL AND search_tsv @@ websearch_to_tsquery('english', $3)
    ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $3)) DESC, id
    LIMIT $2
)
SELECT c.*,
       (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score,
       -- snippet on the hit: the matched text span, over the SAME text the search_tsv is built from
       -- (migration 0002: full_name, headline, location) and the SAME websearch_to_tsquery the full text
       -- leg already binds ($3), so the snippet cites exactly WHY the candidate matched, never a divergent
       -- query. Conservative options keep it short and plain: no markup wrappers, one fragment, a tight
       -- word window. The CASE guard degrades a pure semantic or empty query (an empty tsquery, which
       -- ts_headline would otherwise answer with the unhighlighted document head) to an empty snippet,
       -- which the caller treats as a valid no highlight result, never an error.
       CASE WHEN websearch_to_tsquery('english', $3) = ''::tsquery THEN ''
            ELSE ts_headline('english',
                coalesce(c.full_name,'') || ' ' || coalesce(c.headline,'') || ' ' || coalesce(c.location,''),
                websearch_to_tsquery('english', $3),
                'StartSel="", StopSel="", MaxFragments=1, MaxWords=12, MinWords=4, ShortWord=2, FragmentDelimiter=" "')
       END::text AS snippet
FROM candidates c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN fts ON fts.id = c.id
WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL
ORDER BY rrf_score DESC, c.created_at DESC, c.id
LIMIT $2;

-- name: SearchCandidatesHybridMission :many
-- Mission scoped hybrid retrieval (WS6 Option A): identical to SearchCandidatesHybrid, but BOTH arms are
-- additionally constrained to candidates whose search_tsv matches the mission keyword(s) in mission_query,
-- so a question can be scoped to a mission area (for example SIGINT) using only candidate text match. The
-- search_tsv is built from full_name, headline, and location (migration 0002), NOT the resume body. This is
-- the only mission filter available without structured mission tags: those are WS7 work. It filters on that
-- TEXT, never on a semantic mission alignment, and that limit is documented in docs/AI_RESEARCH.md.
-- RLS still scopes both legs to the caller's org; every ORDER BY keeps the unique id tiebreaker so the
-- output is byte stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs LOCAL
-- hnsw.ef_search and hnsw.iterative_scan first, exactly as for the unscoped query.
WITH vec_raw AS MATERIALIZED (
    SELECT id, (resume_embedding <=> sqlc.arg(query_vector)::vector) AS distance
    FROM candidates
    WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL
      AND search_tsv @@ websearch_to_tsquery('english', sqlc.arg(mission_query))
    ORDER BY resume_embedding <=> sqlc.arg(query_vector)::vector, id
    LIMIT sqlc.arg(row_limit)
),
vec AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk
    FROM vec_raw
),
fts AS (
    SELECT id, ROW_NUMBER() OVER (
               ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', sqlc.arg(text_query))) DESC, id
           ) AS rnk
    FROM candidates
    WHERE deleted_at IS NULL
      AND search_tsv @@ websearch_to_tsquery('english', sqlc.arg(text_query))
      AND search_tsv @@ websearch_to_tsquery('english', sqlc.arg(mission_query))
    ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', sqlc.arg(text_query))) DESC, id
    LIMIT sqlc.arg(row_limit)
)
SELECT c.*,
       (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score,
       -- snippet on the hit: the matched text span, over the SAME text the search_tsv is built from and the
       -- SAME websearch_to_tsquery the full text leg already binds (sqlc.arg(text_query), reused, never a
       -- second divergent tsquery). The mission gate is unchanged, the snippet is purely an added SELECT
       -- expression. The CASE guard degrades an empty text query (an empty tsquery) to an empty snippet, a
       -- valid no highlight result, never an error.
       CASE WHEN websearch_to_tsquery('english', sqlc.arg(text_query)) = ''::tsquery THEN ''
            ELSE ts_headline('english',
                coalesce(c.full_name,'') || ' ' || coalesce(c.headline,'') || ' ' || coalesce(c.location,''),
                websearch_to_tsquery('english', sqlc.arg(text_query)),
                'StartSel="", StopSel="", MaxFragments=1, MaxWords=12, MinWords=4, ShortWord=2, FragmentDelimiter=" "')
       END::text AS snippet
FROM candidates c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN fts ON fts.id = c.id
WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL
ORDER BY rrf_score DESC, c.created_at DESC, c.id
LIMIT sqlc.arg(row_limit);

-- name: SearchCandidatesHybridMissionTagged :many
-- Structured mission scoped hybrid retrieval (WS7 upgrade of the WS6 Option A text filter). It is identical
-- in shape to SearchCandidatesHybridMission, but the mission constraint on BOTH arms is a STRUCTURED
-- predicate on candidate_mission_tags (the deterministic classifier output) instead of the search_tsv
-- mission keyword text match. A candidate is in scope only when a candidate_mission_tags row binds it to the
-- requested mission_code, so the scope is the real, classified mission of the candidate, not a name or
-- headline string coincidence. This lifts the WS6 limit (a candidate whose mission is in the resume body but
-- not in the full_name, headline, or location text is now reachable once the tenant is tagged). The text
-- query ($text_query) still drives the FTS arm and the embedding arm still drives recall, so only the mission
-- gate changed. candidate_mission_tags is RLS scoped to the caller's org, so the EXISTS subquery cannot cross
-- a tenant boundary; the candidates table is RLS scoped too. Every ORDER BY keeps the unique id tiebreaker so
-- the output is byte stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs LOCAL
-- hnsw.ef_search and hnsw.iterative_scan first, exactly as for the unscoped and the WS6 text scoped queries.
WITH vec_raw AS MATERIALIZED (
    SELECT id, (resume_embedding <=> sqlc.arg(query_vector)::vector) AS distance
    FROM candidates
    WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL
      AND EXISTS (SELECT 1 FROM candidate_mission_tags t
                  WHERE t.candidate_id = candidates.id AND t.mission_code = sqlc.arg(mission_code))
    ORDER BY resume_embedding <=> sqlc.arg(query_vector)::vector, id
    LIMIT sqlc.arg(row_limit)
),
vec AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk
    FROM vec_raw
),
fts AS (
    SELECT id, ROW_NUMBER() OVER (
               ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', sqlc.arg(text_query))) DESC, id
           ) AS rnk
    FROM candidates
    WHERE deleted_at IS NULL
      AND search_tsv @@ websearch_to_tsquery('english', sqlc.arg(text_query))
      AND EXISTS (SELECT 1 FROM candidate_mission_tags t
                  WHERE t.candidate_id = candidates.id AND t.mission_code = sqlc.arg(mission_code))
    ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', sqlc.arg(text_query))) DESC, id
    LIMIT sqlc.arg(row_limit)
)
SELECT c.*,
       (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score,
       -- snippet on the hit: the matched text span, over the SAME text the search_tsv is built from and the
       -- SAME websearch_to_tsquery the full text leg already binds (sqlc.arg(text_query), reused). The
       -- structured mission gate is unchanged; only this SELECT expression is added. The CASE guard degrades
       -- an empty text query (an empty tsquery) to an empty snippet, a valid no highlight result, never an
       -- error.
       CASE WHEN websearch_to_tsquery('english', sqlc.arg(text_query)) = ''::tsquery THEN ''
            ELSE ts_headline('english',
                coalesce(c.full_name,'') || ' ' || coalesce(c.headline,'') || ' ' || coalesce(c.location,''),
                websearch_to_tsquery('english', sqlc.arg(text_query)),
                'StartSel="", StopSel="", MaxFragments=1, MaxWords=12, MinWords=4, ShortWord=2, FragmentDelimiter=" "')
       END::text AS snippet
FROM candidates c
LEFT JOIN vec ON vec.id = c.id
LEFT JOIN fts ON fts.id = c.id
WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL
ORDER BY rrf_score DESC, c.created_at DESC, c.id
LIMIT sqlc.arg(row_limit);

-- name: CountCandidateMissionTags :one
-- A tiny RLS scoped probe: how many candidate_mission_tags rows exist for the caller's tenant. The WS7
-- retrieval branch uses it to decide between the structured tagged query (when the tenant has been backfilled
-- by the tagging pass) and the WS6 text filter fallback (when it has not), so a not yet tagged tenant keeps
-- exact WS6 behavior. RLS scopes the count to the caller's org, so it never observes another tenant's tags.
SELECT count(*) FROM candidate_mission_tags;

-- name: ListCandidatesScoped :many
-- RECORD SCOPE, the narrowed variant of ListCandidates. It exists as a SEPARATE query rather than as
-- conditional clauses bolted onto the original, so an organization on the shipped defaults, which is every
-- organization until an owner chooses otherwise, runs exactly the query it ran before this feature existed.
-- Only a customer who has deliberately narrowed scope takes this path.
--
-- owner_ids is the resolved set of owners the caller may see: themselves under own scope, themselves plus
-- every team peer under team scope. include_unowned decides whether a candidate nobody owns is visible, and
-- it defaults true, because an unowned candidate that nobody can see is a candidate nobody will ever work.
--
-- Same total ordering as ListCandidates, so paging behaves identically at either scope.
SELECT * FROM candidates
WHERE deleted_at IS NULL
  AND (owner_user_id = ANY(@owner_ids::uuid[])
       OR (@include_unowned::bool AND owner_user_id IS NULL))
ORDER BY created_at DESC, id DESC
LIMIT $1 OFFSET $2;

-- name: SetCandidateOwner :exec
-- Assigning a candidate to a recruiter's desk. NULL un assigns, returning the candidate to the shared pool,
-- which is a legitimate action rather than an error: an unowned candidate is visible to everyone by default
-- precisely so that a departing recruiter's bench does not become invisible.
UPDATE candidates
SET owner_user_id = $2, updated_at = now()
WHERE id = $1 AND deleted_at IS NULL;