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	09015496d565a172bcaa15cfe9a8e4b072b13df0
Branch	workstream-b-screenmatch
Path or archive member	db/queries/candidates.sql
Original SHA-256	0629f3658a51a1aa3fabe976d60b07fca6c9d2e4053905731131842385941d7e
Git blob SHA	f947011290af9ff74a3cee4f9338418ccb1cac67
Reading-copy SHA-256	0629f3658a51a1aa3fabe976d60b07fca6c9d2e4053905731131842385941d7e

Original source ↗

Every recorded occurrence

These 23 records contain identical original source bytes. Their paths and revisions remain part of the archive.

Original record	Branch or archive	Revision / identifier	Original location
Janady13/missionmatch / db/queries/candidates.sql	workstream-b-screenmatch	09015496d565a172bcaa15cfe9a8e4b072b13df0	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-a-opportunity-intelligence	1675031db74316e100782d063e9eda1a597fe3a1	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-d-command-center	255e7d1d420abf5e277d0cd8c9b14f6f72b6acb9	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-d-missionmatch	3be46ba89edb737182075a2b320509b42dc916f8	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-e-dataflow-deploy	433815a042f9741411e60fa553fe72143e9b10db	Open original
Janady13/missionmatch / db/queries/candidates.sql	w3-ai-staffing-recommendations	43d56387270ef4da4cabd794d97ca6a49909bc7d	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-e-commercial-stubs	4437da660539284a400ee380b9b427a300c369ee	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-c-slatematch	5cd60c918301b46ecd6ed3e03e20c395da0062e2	Open original
Janady13/missionmatch / db/queries/candidates.sql	ai-intelligence-layer	5d5ddff70393a197c5f54a45280bb5dd42b24755	Open original
Janady13/missionmatch / db/queries/candidates.sql	ruthless-debug-sweep	b7d742f8f032984be3817efffa3eb8b13b651c1f	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-b-sam-contract-awards	d5101cceb98e70000cebb31d3e7ac73d747d3a6d	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-a-shared-core	e0de03ac3a1ec826bd15d0e048e3b02baa23eeff	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-c-slatematch	5cd60c918301b46ecd6ed3e03e20c395da0062e2	Open original
Janady13/missionmatch / db/queries/candidates.sql	w3-ai-staffing-recommendations	43d56387270ef4da4cabd794d97ca6a49909bc7d	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-d-command-center	255e7d1d420abf5e277d0cd8c9b14f6f72b6acb9	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-e-dataflow-deploy	433815a042f9741411e60fa553fe72143e9b10db	Open original
Janady13/missionmatch / db/queries/candidates.sql	ruthless-debug-sweep	b7d742f8f032984be3817efffa3eb8b13b651c1f	Open original
Janady13/missionmatch / db/queries/candidates.sql	ai-intelligence-layer	5d5ddff70393a197c5f54a45280bb5dd42b24755	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-a-opportunity-intelligence	1675031db74316e100782d063e9eda1a597fe3a1	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-b-screenmatch	09015496d565a172bcaa15cfe9a8e4b072b13df0	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-e-commercial-stubs	4437da660539284a400ee380b9b427a300c369ee	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-d-missionmatch	3be46ba89edb737182075a2b320509b42dc916f8	Open original
Janady13/missionmatch / db/queries/candidates.sql	workstream-b-sam-contract-awards	d5101cceb98e70000cebb31d3e7ac73d747d3a6d	Open original

Recovered source transcript

Download complete source text ↓140 lines · 6.1 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: 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.
-- Every ORDER BY carries a unique tiebreaker (id) so results are byte-stable for fixed inputs, the
-- AEDT reproducibility mandate [review SQLC-2/PGV-1/PGV-2]. Caller SETs LOCAL hnsw.ef_search first.
WITH vec AS (
    SELECT id, ROW_NUMBER() OVER (ORDER BY resume_embedding <=> $1::vector, id) AS rnk
    FROM candidates
    WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL
    ORDER BY resume_embedding <=> $1::vector, id
    LIMIT $2
),
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
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;