Janady13/missionmatch / internal/platform/database/dbgen/candidates.sql.go

1// Code generated by sqlc. DO NOT EDIT. 2// versions: 3// sqlc v1.31.1 4// source: candidates.sql 5 6package dbgen 7 8import ( 9 "context" 10 "time" 11 12 "github.com/google/uuid" 13 "github.com/jackc/pgx/v5/pgtype" 14 pgvector "github.com/pgvector/pgvector-go" 15) 16 17const addCandidateCertification = `-- name: AddCandidateCertification :exec 18INSERT INTO candidate_certifications (organization_id, candidate_id, name) 19VALUES ($1,$2,$3) 20ON CONFLICT (candidate_id, name) DO NOTHING 21` 22 23type AddCandidateCertificationParams struct { 24 OrganizationID uuid.UUID `json:"organization_id"` 25 CandidateID uuid.UUID `json:"candidate_id"` 26 Name string `json:"name"` 27} 28 29func (q *Queries) AddCandidateCertification(ctx context.Context, arg AddCandidateCertificationParams) error { 30 _, err := q.db.Exec(ctx, addCandidateCertification, arg.OrganizationID, arg.CandidateID, arg.Name) 31 return err 32} 33 34const addCandidateCustomerExperience = `-- name: AddCandidateCustomerExperience :exec 35INSERT INTO candidate_customer_experience (organization_id, candidate_id, agency) 36VALUES ($1,$2,$3) 37ON CONFLICT (candidate_id, agency) DO NOTHING 38` 39 40type AddCandidateCustomerExperienceParams struct { 41 OrganizationID uuid.UUID `json:"organization_id"` 42 CandidateID uuid.UUID `json:"candidate_id"` 43 Agency string `json:"agency"` 44} 45 46func (q *Queries) AddCandidateCustomerExperience(ctx context.Context, arg AddCandidateCustomerExperienceParams) error { 47 _, err := q.db.Exec(ctx, addCandidateCustomerExperience, arg.OrganizationID, arg.CandidateID, arg.Agency) 48 return err 49} 50 51const addCandidateSkill = `-- name: AddCandidateSkill :exec 52INSERT INTO candidate_skills (organization_id, candidate_id, skill_id) 53VALUES ($1,$2,$3) 54ON CONFLICT (candidate_id, skill_id) DO NOTHING 55` 56 57type AddCandidateSkillParams struct { 58 OrganizationID uuid.UUID `json:"organization_id"` 59 CandidateID uuid.UUID `json:"candidate_id"` 60 SkillID uuid.UUID `json:"skill_id"` 61} 62 63func (q *Queries) AddCandidateSkill(ctx context.Context, arg AddCandidateSkillParams) error { 64 _, err := q.db.Exec(ctx, addCandidateSkill, arg.OrganizationID, arg.CandidateID, arg.SkillID) 65 return err 66} 67 68const anonymizeCandidate = `-- name: AnonymizeCandidate :execrows 69UPDATE candidates SET 70 full_name = '[erased]', 71 email = NULL, 72 headline = NULL, 73 location = NULL, 74 resume_embedding = NULL, 75 external_id = NULL, 76 available = false, 77 anonymized_at = now(), 78 updated_at = now() 79WHERE id = $1 AND anonymized_at IS NULL 80` 81 82// Right to erasure as anonymize in place: wipe the identifying PII while KEEPING the row so the de 83// identified scored outcomes (slate_members) and the demographic bucket survive for the adverse impact 84// record. Once the name, email, contact, and resume vector are gone the row is no longer personal data, 85// so erasure is satisfied and the compliance history is preserved. Idempotent on anonymized_at. 86func (q *Queries) AnonymizeCandidate(ctx context.Context, id uuid.UUID) (int64, error) { 87 result, err := q.db.Exec(ctx, anonymizeCandidate, id) 88 if err != nil { 89 return 0, err 90 } 91 return result.RowsAffected(), nil 92} 93 94const countCandidateMissionTags = `-- name: CountCandidateMissionTags :one 95SELECT count(*) FROM candidate_mission_tags 96` 97 98// A tiny RLS scoped probe: how many candidate_mission_tags rows exist for the caller's tenant. The WS7 99// retrieval branch uses it to decide between the structured tagged query (when the tenant has been backfilled 100// by the tagging pass) and the WS6 text filter fallback (when it has not), so a not yet tagged tenant keeps 101// exact WS6 behavior. RLS scopes the count to the caller's org, so it never observes another tenant's tags. 102func (q *Queries) CountCandidateMissionTags(ctx context.Context) (int64, error) { 103 row := q.db.QueryRow(ctx, countCandidateMissionTags) 104 var count int64 105 err := row.Scan(&count) 106 return count, err 107} 108 109const createCandidate = `-- name: CreateCandidate :one 110INSERT INTO candidates ( 111 organization_id, full_name, email, headline, 112 clearance_level, clearance_poly, clearance_status, 113 years_experience, location, remote, relocatable, available, resume_embedding 114) VALUES ($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13) 115RETURNING id, organization_id, full_name, email, headline, clearance_level, clearance_poly, clearance_status, clearance_granted_at, clearance_expires_at, reinvestigation_due_at, years_experience, location, remote, relocatable, available, resume_embedding, search_tsv, created_at, updated_at, deleted_at, source, external_id, anonymized_at, clearance_provenance, clearance_verified_at, clearance_compartments 116` 117 118type CreateCandidateParams struct { 119 OrganizationID uuid.UUID `json:"organization_id"` 120 FullName string `json:"full_name"` 121 Email *string `json:"email"` 122 Headline *string `json:"headline"` 123 ClearanceLevel string `json:"clearance_level"` 124 ClearancePoly string `json:"clearance_poly"` 125 ClearanceStatus string `json:"clearance_status"` 126 YearsExperience int32 `json:"years_experience"` 127 Location *string `json:"location"` 128 Remote bool `json:"remote"` 129 Relocatable bool `json:"relocatable"` 130 Available bool `json:"available"` 131 ResumeEmbedding *pgvector.Vector `json:"resume_embedding"` 132} 133 134func (q *Queries) CreateCandidate(ctx context.Context, arg CreateCandidateParams) (Candidate, error) { 135 row := q.db.QueryRow(ctx, createCandidate, 136 arg.OrganizationID, 137 arg.FullName, 138 arg.Email, 139 arg.Headline, 140 arg.ClearanceLevel, 141 arg.ClearancePoly, 142 arg.ClearanceStatus, 143 arg.YearsExperience, 144 arg.Location, 145 arg.Remote, 146 arg.Relocatable, 147 arg.Available, 148 arg.ResumeEmbedding, 149 ) 150 var i Candidate 151 err := row.Scan( 152 &i.ID, 153 &i.OrganizationID, 154 &i.FullName, 155 &i.Email, 156 &i.Headline, 157 &i.ClearanceLevel, 158 &i.ClearancePoly, 159 &i.ClearanceStatus, 160 &i.ClearanceGrantedAt, 161 &i.ClearanceExpiresAt, 162 &i.ReinvestigationDueAt, 163 &i.YearsExperience, 164 &i.Location, 165 &i.Remote, 166 &i.Relocatable, 167 &i.Available, 168 &i.ResumeEmbedding, 169 &i.SearchTsv, 170 &i.CreatedAt, 171 &i.UpdatedAt, 172 &i.DeletedAt, 173 &i.Source, 174 &i.ExternalID, 175 &i.AnonymizedAt, 176 &i.ClearanceProvenance, 177 &i.ClearanceVerifiedAt, 178 &i.ClearanceCompartments, 179 ) 180 return i, err 181} 182 183const findCandidateByEmail = `-- name: FindCandidateByEmail :one 184SELECT id FROM candidates 185WHERE organization_id = $1 AND deleted_at IS NULL AND email IS NOT NULL AND lower(email) = lower($2) 186ORDER BY created_at LIMIT 1 187` 188 189type FindCandidateByEmailParams struct { 190 OrganizationID uuid.UUID `json:"organization_id"` 191 Lower string `json:"lower"` 192} 193 194// Identity resolution blocking key 1: an exact (case insensitive) email match in the same org. Email is 195// the strongest identity signal, so a sourced lead with a known email merges into the existing person. 196func (q *Queries) FindCandidateByEmail(ctx context.Context, arg FindCandidateByEmailParams) (uuid.UUID, error) { 197 row := q.db.QueryRow(ctx, findCandidateByEmail, arg.OrganizationID, arg.Lower) 198 var id uuid.UUID 199 err := row.Scan(&id) 200 return id, err 201} 202 203const findCandidateByNameLocation = `-- name: FindCandidateByNameLocation :one 204SELECT id FROM candidates 205WHERE organization_id = $1 AND deleted_at IS NULL 206 AND lower(full_name) = lower($2) AND lower(COALESCE(location, '')) = lower($3) 207ORDER BY created_at LIMIT 1 208` 209 210type FindCandidateByNameLocationParams struct { 211 OrganizationID uuid.UUID `json:"organization_id"` 212 Lower string `json:"lower"` 213 Lower_2 string `json:"lower_2"` 214} 215 216// Identity resolution blocking key 2: same normalized full name AND same normalized location in the org. 217// Name alone is too weak (two John Smiths); name plus location is a defensible second signal. The caller 218// only invokes this with a non empty location. 219func (q *Queries) FindCandidateByNameLocation(ctx context.Context, arg FindCandidateByNameLocationParams) (uuid.UUID, error) { 220 row := q.db.QueryRow(ctx, findCandidateByNameLocation, arg.OrganizationID, arg.Lower, arg.Lower_2) 221 var id uuid.UUID 222 err := row.Scan(&id) 223 return id, err 224} 225 226const getCandidate = `-- name: GetCandidate :one 227SELECT id, organization_id, full_name, email, headline, clearance_level, clearance_poly, clearance_status, clearance_granted_at, clearance_expires_at, reinvestigation_due_at, years_experience, location, remote, relocatable, available, resume_embedding, search_tsv, created_at, updated_at, deleted_at, source, external_id, anonymized_at, clearance_provenance, clearance_verified_at, clearance_compartments FROM candidates WHERE id = $1 AND deleted_at IS NULL 228` 229 230func (q *Queries) GetCandidate(ctx context.Context, id uuid.UUID) (Candidate, error) { 231 row := q.db.QueryRow(ctx, getCandidate, id) 232 var i Candidate 233 err := row.Scan( 234 &i.ID, 235 &i.OrganizationID, 236 &i.FullName, 237 &i.Email, 238 &i.Headline, 239 &i.ClearanceLevel, 240 &i.ClearancePoly, 241 &i.ClearanceStatus, 242 &i.ClearanceGrantedAt, 243 &i.ClearanceExpiresAt, 244 &i.ReinvestigationDueAt, 245 &i.YearsExperience, 246 &i.Location, 247 &i.Remote, 248 &i.Relocatable, 249 &i.Available, 250 &i.ResumeEmbedding, 251 &i.SearchTsv, 252 &i.CreatedAt, 253 &i.UpdatedAt, 254 &i.DeletedAt, 255 &i.Source, 256 &i.ExternalID, 257 &i.AnonymizedAt, 258 &i.ClearanceProvenance, 259 &i.ClearanceVerifiedAt, 260 &i.ClearanceCompartments, 261 ) 262 return i, err 263} 264 265const linkSourcedCandidate = `-- name: LinkSourcedCandidate :exec 266UPDATE candidates SET 267 external_id = COALESCE(external_id, $2), 268 headline = COALESCE(headline, $3), 269 years_experience = GREATEST(years_experience, $4), 270 updated_at = now() 271WHERE id = $1 272` 273 274type LinkSourcedCandidateParams struct { 275 ID uuid.UUID `json:"id"` 276 ExternalID *string `json:"external_id"` 277 Headline *string `json:"headline"` 278 YearsExperience int32 `json:"years_experience"` 279} 280 281// Identity resolution merge: link a sourced lead to an existing candidate. Sets the external id and 282// backfills headline and years ONLY where the existing row is empty or lower, never clobbering curated 283// bench data. Source is intentionally left unchanged so a verified bench person stays a bench person. 284func (q *Queries) LinkSourcedCandidate(ctx context.Context, arg LinkSourcedCandidateParams) error { 285 _, err := q.db.Exec(ctx, linkSourcedCandidate, 286 arg.ID, 287 arg.ExternalID, 288 arg.Headline, 289 arg.YearsExperience, 290 ) 291 return err 292} 293 294const listCandidateCertifications = `-- name: ListCandidateCertifications :many 295SELECT name FROM candidate_certifications WHERE candidate_id = $1 ORDER BY name 296` 297 298func (q *Queries) ListCandidateCertifications(ctx context.Context, candidateID uuid.UUID) ([]string, error) { 299 rows, err := q.db.Query(ctx, listCandidateCertifications, candidateID) 300 if err != nil { 301 return nil, err 302 } 303 defer rows.Close() 304 items := []string{} 305 for rows.Next() { 306 var name string 307 if err := rows.Scan(&name); err != nil { 308 return nil, err 309 } 310 items = append(items, name) 311 } 312 if err := rows.Err(); err != nil { 313 return nil, err 314 } 315 return items, nil 316} 317 318const listCandidateCustomerExperience = `-- name: ListCandidateCustomerExperience :many 319SELECT agency FROM candidate_customer_experience WHERE candidate_id = $1 ORDER BY agency 320` 321 322func (q *Queries) ListCandidateCustomerExperience(ctx context.Context, candidateID uuid.UUID) ([]string, error) { 323 rows, err := q.db.Query(ctx, listCandidateCustomerExperience, candidateID) 324 if err != nil { 325 return nil, err 326 } 327 defer rows.Close() 328 items := []string{} 329 for rows.Next() { 330 var agency string 331 if err := rows.Scan(&agency); err != nil { 332 return nil, err 333 } 334 items = append(items, agency) 335 } 336 if err := rows.Err(); err != nil { 337 return nil, err 338 } 339 return items, nil 340} 341 342const listCandidateSkills = `-- name: ListCandidateSkills :many 343SELECT s.id, s.name 344FROM candidate_skills cs 345JOIN skills s ON s.id = cs.skill_id 346WHERE cs.candidate_id = $1 347ORDER BY s.name 348` 349 350type ListCandidateSkillsRow struct { 351 ID uuid.UUID `json:"id"` 352 Name string `json:"name"` 353} 354 355func (q *Queries) ListCandidateSkills(ctx context.Context, candidateID uuid.UUID) ([]ListCandidateSkillsRow, error) { 356 rows, err := q.db.Query(ctx, listCandidateSkills, candidateID) 357 if err != nil { 358 return nil, err 359 } 360 defer rows.Close() 361 items := []ListCandidateSkillsRow{} 362 for rows.Next() { 363 var i ListCandidateSkillsRow 364 if err := rows.Scan(&i.ID, &i.Name); err != nil { 365 return nil, err 366 } 367 items = append(items, i) 368 } 369 if err := rows.Err(); err != nil { 370 return nil, err 371 } 372 return items, nil 373} 374 375const listCandidates = `-- name: ListCandidates :many 376SELECT id, organization_id, full_name, email, headline, clearance_level, clearance_poly, clearance_status, clearance_granted_at, clearance_expires_at, reinvestigation_due_at, years_experience, location, remote, relocatable, available, resume_embedding, search_tsv, created_at, updated_at, deleted_at, source, external_id, anonymized_at, clearance_provenance, clearance_verified_at, clearance_compartments FROM candidates 377WHERE deleted_at IS NULL 378ORDER BY created_at DESC, id DESC 379LIMIT $1 OFFSET $2 380` 381 382type ListCandidatesParams struct { 383 Limit int32 `json:"limit"` 384 Offset int32 `json:"offset"` 385} 386 387// id is a unique trailing sort key so created_at ties give a total order and stable LIMIT/OFFSET paging. 388func (q *Queries) ListCandidates(ctx context.Context, arg ListCandidatesParams) ([]Candidate, error) { 389 rows, err := q.db.Query(ctx, listCandidates, arg.Limit, arg.Offset) 390 if err != nil { 391 return nil, err 392 } 393 defer rows.Close() 394 items := []Candidate{} 395 for rows.Next() { 396 var i Candidate 397 if err := rows.Scan( 398 &i.ID, 399 &i.OrganizationID, 400 &i.FullName, 401 &i.Email, 402 &i.Headline, 403 &i.ClearanceLevel, 404 &i.ClearancePoly, 405 &i.ClearanceStatus, 406 &i.ClearanceGrantedAt, 407 &i.ClearanceExpiresAt, 408 &i.ReinvestigationDueAt, 409 &i.YearsExperience, 410 &i.Location, 411 &i.Remote, 412 &i.Relocatable, 413 &i.Available, 414 &i.ResumeEmbedding, 415 &i.SearchTsv, 416 &i.CreatedAt, 417 &i.UpdatedAt, 418 &i.DeletedAt, 419 &i.Source, 420 &i.ExternalID, 421 &i.AnonymizedAt, 422 &i.ClearanceProvenance, 423 &i.ClearanceVerifiedAt, 424 &i.ClearanceCompartments, 425 ); err != nil { 426 return nil, err 427 } 428 items = append(items, i) 429 } 430 if err := rows.Err(); err != nil { 431 return nil, err 432 } 433 return items, nil 434} 435 436const listCandidatesForIntel = `-- name: ListCandidatesForIntel :many 437SELECT id, organization_id, full_name, email, headline, clearance_level, clearance_poly, clearance_status, clearance_granted_at, clearance_expires_at, reinvestigation_due_at, years_experience, location, remote, relocatable, available, resume_embedding, search_tsv, created_at, updated_at, deleted_at, source, external_id, anonymized_at, clearance_provenance, clearance_verified_at, clearance_compartments FROM candidates 438WHERE deleted_at IS NULL 439ORDER BY available DESC, updated_at DESC, created_at DESC, id 440LIMIT $1 441` 442 443// Available (staffable) candidates first, most recently updated next, so the workforce intelligence 444// surface leads with the talent you can deploy and newly staffed or sourced people are always scored. 445// The trailing id makes the order total, so the same set is selected before the LIMIT cut every run. 446func (q *Queries) ListCandidatesForIntel(ctx context.Context, limit int32) ([]Candidate, error) { 447 rows, err := q.db.Query(ctx, listCandidatesForIntel, limit) 448 if err != nil { 449 return nil, err 450 } 451 defer rows.Close() 452 items := []Candidate{} 453 for rows.Next() { 454 var i Candidate 455 if err := rows.Scan( 456 &i.ID, 457 &i.OrganizationID, 458 &i.FullName, 459 &i.Email, 460 &i.Headline, 461 &i.ClearanceLevel, 462 &i.ClearancePoly, 463 &i.ClearanceStatus, 464 &i.ClearanceGrantedAt, 465 &i.ClearanceExpiresAt, 466 &i.ReinvestigationDueAt, 467 &i.YearsExperience, 468 &i.Location, 469 &i.Remote, 470 &i.Relocatable, 471 &i.Available, 472 &i.ResumeEmbedding, 473 &i.SearchTsv, 474 &i.CreatedAt, 475 &i.UpdatedAt, 476 &i.DeletedAt, 477 &i.Source, 478 &i.ExternalID, 479 &i.AnonymizedAt, 480 &i.ClearanceProvenance, 481 &i.ClearanceVerifiedAt, 482 &i.ClearanceCompartments, 483 ); err != nil { 484 return nil, err 485 } 486 items = append(items, i) 487 } 488 if err := rows.Err(); err != nil { 489 return nil, err 490 } 491 return items, nil 492} 493 494const searchCandidatesHybrid = `-- name: SearchCandidatesHybrid :many 495WITH vec_raw AS MATERIALIZED ( 496 SELECT id, (resume_embedding <=> $1::vector) AS distance 497 FROM candidates 498 WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL 499 ORDER BY resume_embedding <=> $1::vector, id 500 LIMIT $2 501), 502vec AS ( 503 SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk 504 FROM vec_raw 505), 506fts AS ( 507 SELECT id, ROW_NUMBER() OVER ( 508 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $3)) DESC, id 509 ) AS rnk 510 FROM candidates 511 WHERE deleted_at IS NULL AND search_tsv @@ websearch_to_tsquery('english', $3) 512 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $3)) DESC, id 513 LIMIT $2 514) 515SELECT c.id, c.organization_id, c.full_name, c.email, c.headline, c.clearance_level, c.clearance_poly, c.clearance_status, c.clearance_granted_at, c.clearance_expires_at, c.reinvestigation_due_at, c.years_experience, c.location, c.remote, c.relocatable, c.available, c.resume_embedding, c.search_tsv, c.created_at, c.updated_at, c.deleted_at, c.source, c.external_id, c.anonymized_at, c.clearance_provenance, c.clearance_verified_at, c.clearance_compartments, 516 (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score, 517 -- snippet on the hit: the matched text span, over the SAME text the search_tsv is built from 518 -- (migration 0002: full_name, headline, location) and the SAME websearch_to_tsquery the full text 519 -- leg already binds ($3), so the snippet cites exactly WHY the candidate matched, never a divergent 520 -- query. Conservative options keep it short and plain: no markup wrappers, one fragment, a tight 521 -- word window. The CASE guard degrades a pure semantic or empty query (an empty tsquery, which 522 -- ts_headline would otherwise answer with the unhighlighted document head) to an empty snippet, 523 -- which the caller treats as a valid no highlight result, never an error. 524 CASE WHEN websearch_to_tsquery('english', $3) = ''::tsquery THEN '' 525 ELSE ts_headline('english', 526 coalesce(c.full_name,'') || ' ' || coalesce(c.headline,'') || ' ' || coalesce(c.location,''), 527 websearch_to_tsquery('english', $3), 528 'StartSel="", StopSel="", MaxFragments=1, MaxWords=12, MinWords=4, ShortWord=2, FragmentDelimiter=" "') 529 END::text AS snippet 530FROM candidates c 531LEFT JOIN vec ON vec.id = c.id 532LEFT JOIN fts ON fts.id = c.id 533WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL 534ORDER BY rrf_score DESC, c.created_at DESC, c.id 535LIMIT $2 536` 537 538type SearchCandidatesHybridParams struct { 539 Column1 pgvector.Vector `json:"column_1"` 540 Limit int32 `json:"limit"` 541 WebsearchToTsquery string `json:"websearch_to_tsquery"` 542} 543 544type SearchCandidatesHybridRow struct { 545 ID uuid.UUID `json:"id"` 546 OrganizationID uuid.UUID `json:"organization_id"` 547 FullName string `json:"full_name"` 548 Email *string `json:"email"` 549 Headline *string `json:"headline"` 550 ClearanceLevel string `json:"clearance_level"` 551 ClearancePoly string `json:"clearance_poly"` 552 ClearanceStatus string `json:"clearance_status"` 553 ClearanceGrantedAt pgtype.Date `json:"clearance_granted_at"` 554 ClearanceExpiresAt pgtype.Date `json:"clearance_expires_at"` 555 ReinvestigationDueAt pgtype.Date `json:"reinvestigation_due_at"` 556 YearsExperience int32 `json:"years_experience"` 557 Location *string `json:"location"` 558 Remote bool `json:"remote"` 559 Relocatable bool `json:"relocatable"` 560 Available bool `json:"available"` 561 ResumeEmbedding *pgvector.Vector `json:"resume_embedding"` 562 SearchTsv interface{} `json:"search_tsv"` 563 CreatedAt time.Time `json:"created_at"` 564 UpdatedAt time.Time `json:"updated_at"` 565 DeletedAt *time.Time `json:"deleted_at"` 566 Source string `json:"source"` 567 ExternalID *string `json:"external_id"` 568 AnonymizedAt *time.Time `json:"anonymized_at"` 569 ClearanceProvenance string `json:"clearance_provenance"` 570 ClearanceVerifiedAt *time.Time `json:"clearance_verified_at"` 571 ClearanceCompartments []string `json:"clearance_compartments"` 572 RrfScore float64 `json:"rrf_score"` 573 Snippet string `json:"snippet"` 574} 575 576// Hybrid retrieval: pgvector cosine kNN fused with full-text rank via Reciprocal Rank Fusion (k=60). 577// RLS already scopes both legs to the caller's org. $1=query embedding, $2=limit, $3=text query. 578// The vector arm is MATERIALIZED so the relaxed_order HNSW output is re-sorted by TRUE cosine distance 579// before it is ranked: under hnsw.iterative_scan = relaxed_order the index returns rows slightly out of 580// distance order, and ranking them without the re-sort would mis-rank the nearest neighbors at scale 581// (pgvector 0.8 README; AWS pgvector 0.8 Aurora guidance). Every ORDER BY carries a unique tiebreaker 582// (id) so results are byte-stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs 583// LOCAL hnsw.ef_search and hnsw.iterative_scan first. 584func (q *Queries) SearchCandidatesHybrid(ctx context.Context, arg SearchCandidatesHybridParams) ([]SearchCandidatesHybridRow, error) { 585 rows, err := q.db.Query(ctx, searchCandidatesHybrid, arg.Column1, arg.Limit, arg.WebsearchToTsquery) 586 if err != nil { 587 return nil, err 588 } 589 defer rows.Close() 590 items := []SearchCandidatesHybridRow{} 591 for rows.Next() { 592 var i SearchCandidatesHybridRow 593 if err := rows.Scan( 594 &i.ID, 595 &i.OrganizationID, 596 &i.FullName, 597 &i.Email, 598 &i.Headline, 599 &i.ClearanceLevel, 600 &i.ClearancePoly, 601 &i.ClearanceStatus, 602 &i.ClearanceGrantedAt, 603 &i.ClearanceExpiresAt, 604 &i.ReinvestigationDueAt, 605 &i.YearsExperience, 606 &i.Location, 607 &i.Remote, 608 &i.Relocatable, 609 &i.Available, 610 &i.ResumeEmbedding, 611 &i.SearchTsv, 612 &i.CreatedAt, 613 &i.UpdatedAt, 614 &i.DeletedAt, 615 &i.Source, 616 &i.ExternalID, 617 &i.AnonymizedAt, 618 &i.ClearanceProvenance, 619 &i.ClearanceVerifiedAt, 620 &i.ClearanceCompartments, 621 &i.RrfScore, 622 &i.Snippet, 623 ); err != nil { 624 return nil, err 625 } 626 items = append(items, i) 627 } 628 if err := rows.Err(); err != nil { 629 return nil, err 630 } 631 return items, nil 632} 633 634const searchCandidatesHybridMission = `-- name: SearchCandidatesHybridMission :many 635WITH vec_raw AS MATERIALIZED ( 636 SELECT id, (resume_embedding <=> $3::vector) AS distance 637 FROM candidates 638 WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL 639 AND search_tsv @@ websearch_to_tsquery('english', $4) 640 ORDER BY resume_embedding <=> $3::vector, id 641 LIMIT $2 642), 643vec AS ( 644 SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk 645 FROM vec_raw 646), 647fts AS ( 648 SELECT id, ROW_NUMBER() OVER ( 649 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $1)) DESC, id 650 ) AS rnk 651 FROM candidates 652 WHERE deleted_at IS NULL 653 AND search_tsv @@ websearch_to_tsquery('english', $1) 654 AND search_tsv @@ websearch_to_tsquery('english', $4) 655 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $1)) DESC, id 656 LIMIT $2 657) 658SELECT c.id, c.organization_id, c.full_name, c.email, c.headline, c.clearance_level, c.clearance_poly, c.clearance_status, c.clearance_granted_at, c.clearance_expires_at, c.reinvestigation_due_at, c.years_experience, c.location, c.remote, c.relocatable, c.available, c.resume_embedding, c.search_tsv, c.created_at, c.updated_at, c.deleted_at, c.source, c.external_id, c.anonymized_at, c.clearance_provenance, c.clearance_verified_at, c.clearance_compartments, 659 (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score, 660 -- snippet on the hit: the matched text span, over the SAME text the search_tsv is built from and the 661 -- SAME websearch_to_tsquery the full text leg already binds (sqlc.arg(text_query), reused, never a 662 -- second divergent tsquery). The mission gate is unchanged, the snippet is purely an added SELECT 663 -- expression. The CASE guard degrades an empty text query (an empty tsquery) to an empty snippet, a 664 -- valid no highlight result, never an error. 665 CASE WHEN websearch_to_tsquery('english', $1) = ''::tsquery THEN '' 666 ELSE ts_headline('english', 667 coalesce(c.full_name,'') || ' ' || coalesce(c.headline,'') || ' ' || coalesce(c.location,''), 668 websearch_to_tsquery('english', $1), 669 'StartSel="", StopSel="", MaxFragments=1, MaxWords=12, MinWords=4, ShortWord=2, FragmentDelimiter=" "') 670 END::text AS snippet 671FROM candidates c 672LEFT JOIN vec ON vec.id = c.id 673LEFT JOIN fts ON fts.id = c.id 674WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL 675ORDER BY rrf_score DESC, c.created_at DESC, c.id 676LIMIT $2 677` 678 679type SearchCandidatesHybridMissionParams struct { 680 TextQuery string `json:"text_query"` 681 RowLimit int32 `json:"row_limit"` 682 QueryVector pgvector.Vector `json:"query_vector"` 683 MissionQuery string `json:"mission_query"` 684} 685 686type SearchCandidatesHybridMissionRow struct { 687 ID uuid.UUID `json:"id"` 688 OrganizationID uuid.UUID `json:"organization_id"` 689 FullName string `json:"full_name"` 690 Email *string `json:"email"` 691 Headline *string `json:"headline"` 692 ClearanceLevel string `json:"clearance_level"` 693 ClearancePoly string `json:"clearance_poly"` 694 ClearanceStatus string `json:"clearance_status"` 695 ClearanceGrantedAt pgtype.Date `json:"clearance_granted_at"` 696 ClearanceExpiresAt pgtype.Date `json:"clearance_expires_at"` 697 ReinvestigationDueAt pgtype.Date `json:"reinvestigation_due_at"` 698 YearsExperience int32 `json:"years_experience"` 699 Location *string `json:"location"` 700 Remote bool `json:"remote"` 701 Relocatable bool `json:"relocatable"` 702 Available bool `json:"available"` 703 ResumeEmbedding *pgvector.Vector `json:"resume_embedding"` 704 SearchTsv interface{} `json:"search_tsv"` 705 CreatedAt time.Time `json:"created_at"` 706 UpdatedAt time.Time `json:"updated_at"` 707 DeletedAt *time.Time `json:"deleted_at"` 708 Source string `json:"source"` 709 ExternalID *string `json:"external_id"` 710 AnonymizedAt *time.Time `json:"anonymized_at"` 711 ClearanceProvenance string `json:"clearance_provenance"` 712 ClearanceVerifiedAt *time.Time `json:"clearance_verified_at"` 713 ClearanceCompartments []string `json:"clearance_compartments"` 714 RrfScore float64 `json:"rrf_score"` 715 Snippet string `json:"snippet"` 716} 717 718// Mission scoped hybrid retrieval (WS6 Option A): identical to SearchCandidatesHybrid, but BOTH arms are 719// additionally constrained to candidates whose search_tsv matches the mission keyword(s) in mission_query, 720// so a question can be scoped to a mission area (for example SIGINT) using only candidate text match. The 721// search_tsv is built from full_name, headline, and location (migration 0002), NOT the resume body. This is 722// the only mission filter available without structured mission tags: those are WS7 work. It filters on that 723// TEXT, never on a semantic mission alignment, and that limit is documented in docs/AI_RESEARCH.md. 724// RLS still scopes both legs to the caller's org; every ORDER BY keeps the unique id tiebreaker so the 725// output is byte stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs LOCAL 726// hnsw.ef_search and hnsw.iterative_scan first, exactly as for the unscoped query. 727func (q *Queries) SearchCandidatesHybridMission(ctx context.Context, arg SearchCandidatesHybridMissionParams) ([]SearchCandidatesHybridMissionRow, error) { 728 rows, err := q.db.Query(ctx, searchCandidatesHybridMission, 729 arg.TextQuery, 730 arg.RowLimit, 731 arg.QueryVector, 732 arg.MissionQuery, 733 ) 734 if err != nil { 735 return nil, err 736 } 737 defer rows.Close() 738 items := []SearchCandidatesHybridMissionRow{} 739 for rows.Next() { 740 var i SearchCandidatesHybridMissionRow 741 if err := rows.Scan( 742 &i.ID, 743 &i.OrganizationID, 744 &i.FullName, 745 &i.Email, 746 &i.Headline, 747 &i.ClearanceLevel, 748 &i.ClearancePoly, 749 &i.ClearanceStatus, 750 &i.ClearanceGrantedAt, 751 &i.ClearanceExpiresAt, 752 &i.ReinvestigationDueAt, 753 &i.YearsExperience, 754 &i.Location, 755 &i.Remote, 756 &i.Relocatable, 757 &i.Available, 758 &i.ResumeEmbedding, 759 &i.SearchTsv, 760 &i.CreatedAt, 761 &i.UpdatedAt, 762 &i.DeletedAt, 763 &i.Source, 764 &i.ExternalID, 765 &i.AnonymizedAt, 766 &i.ClearanceProvenance, 767 &i.ClearanceVerifiedAt, 768 &i.ClearanceCompartments, 769 &i.RrfScore, 770 &i.Snippet, 771 ); err != nil { 772 return nil, err 773 } 774 items = append(items, i) 775 } 776 if err := rows.Err(); err != nil { 777 return nil, err 778 } 779 return items, nil 780} 781 782const searchCandidatesHybridMissionTagged = `-- name: SearchCandidatesHybridMissionTagged :many 783WITH vec_raw AS MATERIALIZED ( 784 SELECT id, (resume_embedding <=> $3::vector) AS distance 785 FROM candidates 786 WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL 787 AND EXISTS (SELECT 1 FROM candidate_mission_tags t 788 WHERE t.candidate_id = candidates.id AND t.mission_code = $4) 789 ORDER BY resume_embedding <=> $3::vector, id 790 LIMIT $2 791), 792vec AS ( 793 SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk 794 FROM vec_raw 795), 796fts AS ( 797 SELECT id, ROW_NUMBER() OVER ( 798 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $1)) DESC, id 799 ) AS rnk 800 FROM candidates 801 WHERE deleted_at IS NULL 802 AND search_tsv @@ websearch_to_tsquery('english', $1) 803 AND EXISTS (SELECT 1 FROM candidate_mission_tags t 804 WHERE t.candidate_id = candidates.id AND t.mission_code = $4) 805 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $1)) DESC, id 806 LIMIT $2 807) 808SELECT c.id, c.organization_id, c.full_name, c.email, c.headline, c.clearance_level, c.clearance_poly, c.clearance_status, c.clearance_granted_at, c.clearance_expires_at, c.reinvestigation_due_at, c.years_experience, c.location, c.remote, c.relocatable, c.available, c.resume_embedding, c.search_tsv, c.created_at, c.updated_at, c.deleted_at, c.source, c.external_id, c.anonymized_at, c.clearance_provenance, c.clearance_verified_at, c.clearance_compartments, 809 (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score, 810 -- snippet on the hit: the matched text span, over the SAME text the search_tsv is built from and the 811 -- SAME websearch_to_tsquery the full text leg already binds (sqlc.arg(text_query), reused). The 812 -- structured mission gate is unchanged; only this SELECT expression is added. The CASE guard degrades 813 -- an empty text query (an empty tsquery) to an empty snippet, a valid no highlight result, never an 814 -- error. 815 CASE WHEN websearch_to_tsquery('english', $1) = ''::tsquery THEN '' 816 ELSE ts_headline('english', 817 coalesce(c.full_name,'') || ' ' || coalesce(c.headline,'') || ' ' || coalesce(c.location,''), 818 websearch_to_tsquery('english', $1), 819 'StartSel="", StopSel="", MaxFragments=1, MaxWords=12, MinWords=4, ShortWord=2, FragmentDelimiter=" "') 820 END::text AS snippet 821FROM candidates c 822LEFT JOIN vec ON vec.id = c.id 823LEFT JOIN fts ON fts.id = c.id 824WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL 825ORDER BY rrf_score DESC, c.created_at DESC, c.id 826LIMIT $2 827` 828 829type SearchCandidatesHybridMissionTaggedParams struct { 830 TextQuery string `json:"text_query"` 831 RowLimit int32 `json:"row_limit"` 832 QueryVector pgvector.Vector `json:"query_vector"` 833 MissionCode string `json:"mission_code"` 834} 835 836type SearchCandidatesHybridMissionTaggedRow struct { 837 ID uuid.UUID `json:"id"` 838 OrganizationID uuid.UUID `json:"organization_id"` 839 FullName string `json:"full_name"` 840 Email *string `json:"email"` 841 Headline *string `json:"headline"` 842 ClearanceLevel string `json:"clearance_level"` 843 ClearancePoly string `json:"clearance_poly"` 844 ClearanceStatus string `json:"clearance_status"` 845 ClearanceGrantedAt pgtype.Date `json:"clearance_granted_at"` 846 ClearanceExpiresAt pgtype.Date `json:"clearance_expires_at"` 847 ReinvestigationDueAt pgtype.Date `json:"reinvestigation_due_at"` 848 YearsExperience int32 `json:"years_experience"` 849 Location *string `json:"location"` 850 Remote bool `json:"remote"` 851 Relocatable bool `json:"relocatable"` 852 Available bool `json:"available"` 853 ResumeEmbedding *pgvector.Vector `json:"resume_embedding"` 854 SearchTsv interface{} `json:"search_tsv"` 855 CreatedAt time.Time `json:"created_at"` 856 UpdatedAt time.Time `json:"updated_at"` 857 DeletedAt *time.Time `json:"deleted_at"` 858 Source string `json:"source"` 859 ExternalID *string `json:"external_id"` 860 AnonymizedAt *time.Time `json:"anonymized_at"` 861 ClearanceProvenance string `json:"clearance_provenance"` 862 ClearanceVerifiedAt *time.Time `json:"clearance_verified_at"` 863 ClearanceCompartments []string `json:"clearance_compartments"` 864 RrfScore float64 `json:"rrf_score"` 865 Snippet string `json:"snippet"` 866} 867 868// Structured mission scoped hybrid retrieval (WS7 upgrade of the WS6 Option A text filter). It is identical 869// in shape to SearchCandidatesHybridMission, but the mission constraint on BOTH arms is a STRUCTURED 870// predicate on candidate_mission_tags (the deterministic classifier output) instead of the search_tsv 871// mission keyword text match. A candidate is in scope only when a candidate_mission_tags row binds it to the 872// requested mission_code, so the scope is the real, classified mission of the candidate, not a name or 873// headline string coincidence. This lifts the WS6 limit (a candidate whose mission is in the resume body but 874// not in the full_name, headline, or location text is now reachable once the tenant is tagged). The text 875// query ($text_query) still drives the FTS arm and the embedding arm still drives recall, so only the mission 876// gate changed. candidate_mission_tags is RLS scoped to the caller's org, so the EXISTS subquery cannot cross 877// a tenant boundary; the candidates table is RLS scoped too. Every ORDER BY keeps the unique id tiebreaker so 878// the output is byte stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs LOCAL 879// hnsw.ef_search and hnsw.iterative_scan first, exactly as for the unscoped and the WS6 text scoped queries. 880func (q *Queries) SearchCandidatesHybridMissionTagged(ctx context.Context, arg SearchCandidatesHybridMissionTaggedParams) ([]SearchCandidatesHybridMissionTaggedRow, error) { 881 rows, err := q.db.Query(ctx, searchCandidatesHybridMissionTagged, 882 arg.TextQuery, 883 arg.RowLimit, 884 arg.QueryVector, 885 arg.MissionCode, 886 ) 887 if err != nil { 888 return nil, err 889 } 890 defer rows.Close() 891 items := []SearchCandidatesHybridMissionTaggedRow{} 892 for rows.Next() { 893 var i SearchCandidatesHybridMissionTaggedRow 894 if err := rows.Scan( 895 &i.ID, 896 &i.OrganizationID, 897 &i.FullName, 898 &i.Email, 899 &i.Headline, 900 &i.ClearanceLevel, 901 &i.ClearancePoly, 902 &i.ClearanceStatus, 903 &i.ClearanceGrantedAt, 904 &i.ClearanceExpiresAt, 905 &i.ReinvestigationDueAt, 906 &i.YearsExperience, 907 &i.Location, 908 &i.Remote, 909 &i.Relocatable, 910 &i.Available, 911 &i.ResumeEmbedding, 912 &i.SearchTsv, 913 &i.CreatedAt, 914 &i.UpdatedAt, 915 &i.DeletedAt, 916 &i.Source, 917 &i.ExternalID, 918 &i.AnonymizedAt, 919 &i.ClearanceProvenance, 920 &i.ClearanceVerifiedAt, 921 &i.ClearanceCompartments, 922 &i.RrfScore, 923 &i.Snippet, 924 ); err != nil { 925 return nil, err 926 } 927 items = append(items, i) 928 } 929 if err := rows.Err(); err != nil { 930 return nil, err 931 } 932 return items, nil 933} 934 935const setCandidateClearanceProvenance = `-- name: SetCandidateClearanceProvenance :execrows 936UPDATE candidates 937SET clearance_provenance = $2, clearance_verified_at = $3, updated_at = now() 938WHERE id = $1 AND deleted_at IS NULL 939` 940 941type SetCandidateClearanceProvenanceParams struct { 942 ID uuid.UUID `json:"id"` 943 ClearanceProvenance string `json:"clearance_provenance"` 944 ClearanceVerifiedAt *time.Time `json:"clearance_verified_at"` 945} 946 947// An org FSO records HOW a candidate's clearance is known. RLS scopes this to the caller's own org. 948// verified_at is the date the FSO confirmed it; it is NULL for a self reported indicator. The platform 949// never verifies a clearance itself, it records the org's authoritative assertion. 950func (q *Queries) SetCandidateClearanceProvenance(ctx context.Context, arg SetCandidateClearanceProvenanceParams) (int64, error) { 951 result, err := q.db.Exec(ctx, setCandidateClearanceProvenance, arg.ID, arg.ClearanceProvenance, arg.ClearanceVerifiedAt) 952 if err != nil { 953 return 0, err 954 } 955 return result.RowsAffected(), nil 956} 957 958const softDeleteCandidate = `-- name: SoftDeleteCandidate :exec 959UPDATE candidates SET deleted_at = now(), updated_at = now() WHERE id = $1 960` 961 962func (q *Queries) SoftDeleteCandidate(ctx context.Context, id uuid.UUID) error { 963 _, err := q.db.Exec(ctx, softDeleteCandidate, id) 964 return err 965} 966 967const updateCandidateEmbedding = `-- name: UpdateCandidateEmbedding :exec 968UPDATE candidates SET resume_embedding = $1::vector, updated_at = now() WHERE id = $2 969` 970 971type UpdateCandidateEmbeddingParams struct { 972 Embedding pgvector.Vector `json:"embedding"` 973 ID uuid.UUID `json:"id"` 974} 975 976// Writes a precomputed resume embedding onto a candidate, used by the bench import path AFTER the embed 977// network call has already run in the caller, so the migrator write transaction never holds open across 978// the inference round trip (the embed lock window rule). The vector is composed from name free structured 979// tokens only, never raw resume PII. A candidate with a NULL embedding is left embeddable by the 980// embed-backfill only-missing fallback, so a skipped embed never wedges a row. 981func (q *Queries) UpdateCandidateEmbedding(ctx context.Context, arg UpdateCandidateEmbeddingParams) error { 982 _, err := q.db.Exec(ctx, updateCandidateEmbedding, arg.Embedding, arg.ID) 983 return err 984} 985