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 517FROM candidates c 518LEFT JOIN vec ON vec.id = c.id 519LEFT JOIN fts ON fts.id = c.id 520WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL 521ORDER BY rrf_score DESC, c.created_at DESC, c.id 522LIMIT $2 523` 524 525type SearchCandidatesHybridParams struct { 526 Column1 pgvector.Vector `json:"column_1"` 527 Limit int32 `json:"limit"` 528 WebsearchToTsquery string `json:"websearch_to_tsquery"` 529} 530 531type SearchCandidatesHybridRow struct { 532 ID uuid.UUID `json:"id"` 533 OrganizationID uuid.UUID `json:"organization_id"` 534 FullName string `json:"full_name"` 535 Email *string `json:"email"` 536 Headline *string `json:"headline"` 537 ClearanceLevel string `json:"clearance_level"` 538 ClearancePoly string `json:"clearance_poly"` 539 ClearanceStatus string `json:"clearance_status"` 540 ClearanceGrantedAt pgtype.Date `json:"clearance_granted_at"` 541 ClearanceExpiresAt pgtype.Date `json:"clearance_expires_at"` 542 ReinvestigationDueAt pgtype.Date `json:"reinvestigation_due_at"` 543 YearsExperience int32 `json:"years_experience"` 544 Location *string `json:"location"` 545 Remote bool `json:"remote"` 546 Relocatable bool `json:"relocatable"` 547 Available bool `json:"available"` 548 ResumeEmbedding *pgvector.Vector `json:"resume_embedding"` 549 SearchTsv interface{} `json:"search_tsv"` 550 CreatedAt time.Time `json:"created_at"` 551 UpdatedAt time.Time `json:"updated_at"` 552 DeletedAt *time.Time `json:"deleted_at"` 553 Source string `json:"source"` 554 ExternalID *string `json:"external_id"` 555 AnonymizedAt *time.Time `json:"anonymized_at"` 556 ClearanceProvenance string `json:"clearance_provenance"` 557 ClearanceVerifiedAt *time.Time `json:"clearance_verified_at"` 558 ClearanceCompartments []string `json:"clearance_compartments"` 559 RrfScore float64 `json:"rrf_score"` 560} 561 562// Hybrid retrieval: pgvector cosine kNN fused with full-text rank via Reciprocal Rank Fusion (k=60). 563// RLS already scopes both legs to the caller's org. $1=query embedding, $2=limit, $3=text query. 564// The vector arm is MATERIALIZED so the relaxed_order HNSW output is re-sorted by TRUE cosine distance 565// before it is ranked: under hnsw.iterative_scan = relaxed_order the index returns rows slightly out of 566// distance order, and ranking them without the re-sort would mis-rank the nearest neighbors at scale 567// (pgvector 0.8 README; AWS pgvector 0.8 Aurora guidance). Every ORDER BY carries a unique tiebreaker 568// (id) so results are byte-stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs 569// LOCAL hnsw.ef_search and hnsw.iterative_scan first. 570func (q *Queries) SearchCandidatesHybrid(ctx context.Context, arg SearchCandidatesHybridParams) ([]SearchCandidatesHybridRow, error) { 571 rows, err := q.db.Query(ctx, searchCandidatesHybrid, arg.Column1, arg.Limit, arg.WebsearchToTsquery) 572 if err != nil { 573 return nil, err 574 } 575 defer rows.Close() 576 items := []SearchCandidatesHybridRow{} 577 for rows.Next() { 578 var i SearchCandidatesHybridRow 579 if err := rows.Scan( 580 &i.ID, 581 &i.OrganizationID, 582 &i.FullName, 583 &i.Email, 584 &i.Headline, 585 &i.ClearanceLevel, 586 &i.ClearancePoly, 587 &i.ClearanceStatus, 588 &i.ClearanceGrantedAt, 589 &i.ClearanceExpiresAt, 590 &i.ReinvestigationDueAt, 591 &i.YearsExperience, 592 &i.Location, 593 &i.Remote, 594 &i.Relocatable, 595 &i.Available, 596 &i.ResumeEmbedding, 597 &i.SearchTsv, 598 &i.CreatedAt, 599 &i.UpdatedAt, 600 &i.DeletedAt, 601 &i.Source, 602 &i.ExternalID, 603 &i.AnonymizedAt, 604 &i.ClearanceProvenance, 605 &i.ClearanceVerifiedAt, 606 &i.ClearanceCompartments, 607 &i.RrfScore, 608 ); err != nil { 609 return nil, err 610 } 611 items = append(items, i) 612 } 613 if err := rows.Err(); err != nil { 614 return nil, err 615 } 616 return items, nil 617} 618 619const searchCandidatesHybridMission = `-- name: SearchCandidatesHybridMission :many 620WITH vec_raw AS MATERIALIZED ( 621 SELECT id, (resume_embedding <=> $2::vector) AS distance 622 FROM candidates 623 WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL 624 AND search_tsv @@ websearch_to_tsquery('english', $3) 625 ORDER BY resume_embedding <=> $2::vector, id 626 LIMIT $1 627), 628vec AS ( 629 SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk 630 FROM vec_raw 631), 632fts AS ( 633 SELECT id, ROW_NUMBER() OVER ( 634 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $4)) DESC, id 635 ) AS rnk 636 FROM candidates 637 WHERE deleted_at IS NULL 638 AND search_tsv @@ websearch_to_tsquery('english', $4) 639 AND search_tsv @@ websearch_to_tsquery('english', $3) 640 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $4)) DESC, id 641 LIMIT $1 642) 643SELECT 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, 644 (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score 645FROM candidates c 646LEFT JOIN vec ON vec.id = c.id 647LEFT JOIN fts ON fts.id = c.id 648WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL 649ORDER BY rrf_score DESC, c.created_at DESC, c.id 650LIMIT $1 651` 652 653type SearchCandidatesHybridMissionParams struct { 654 RowLimit int32 `json:"row_limit"` 655 QueryVector pgvector.Vector `json:"query_vector"` 656 MissionQuery string `json:"mission_query"` 657 TextQuery string `json:"text_query"` 658} 659 660type SearchCandidatesHybridMissionRow struct { 661 ID uuid.UUID `json:"id"` 662 OrganizationID uuid.UUID `json:"organization_id"` 663 FullName string `json:"full_name"` 664 Email *string `json:"email"` 665 Headline *string `json:"headline"` 666 ClearanceLevel string `json:"clearance_level"` 667 ClearancePoly string `json:"clearance_poly"` 668 ClearanceStatus string `json:"clearance_status"` 669 ClearanceGrantedAt pgtype.Date `json:"clearance_granted_at"` 670 ClearanceExpiresAt pgtype.Date `json:"clearance_expires_at"` 671 ReinvestigationDueAt pgtype.Date `json:"reinvestigation_due_at"` 672 YearsExperience int32 `json:"years_experience"` 673 Location *string `json:"location"` 674 Remote bool `json:"remote"` 675 Relocatable bool `json:"relocatable"` 676 Available bool `json:"available"` 677 ResumeEmbedding *pgvector.Vector `json:"resume_embedding"` 678 SearchTsv interface{} `json:"search_tsv"` 679 CreatedAt time.Time `json:"created_at"` 680 UpdatedAt time.Time `json:"updated_at"` 681 DeletedAt *time.Time `json:"deleted_at"` 682 Source string `json:"source"` 683 ExternalID *string `json:"external_id"` 684 AnonymizedAt *time.Time `json:"anonymized_at"` 685 ClearanceProvenance string `json:"clearance_provenance"` 686 ClearanceVerifiedAt *time.Time `json:"clearance_verified_at"` 687 ClearanceCompartments []string `json:"clearance_compartments"` 688 RrfScore float64 `json:"rrf_score"` 689} 690 691// Mission scoped hybrid retrieval (WS6 Option A): identical to SearchCandidatesHybrid, but BOTH arms are 692// additionally constrained to candidates whose search_tsv matches the mission keyword(s) in mission_query, 693// so a question can be scoped to a mission area (for example SIGINT) using only candidate text match. The 694// search_tsv is built from full_name, headline, and location (migration 0002), NOT the resume body. This is 695// the only mission filter available without structured mission tags: those are WS7 work. It filters on that 696// TEXT, never on a semantic mission alignment, and that limit is documented in docs/AI_RESEARCH.md. 697// RLS still scopes both legs to the caller's org; every ORDER BY keeps the unique id tiebreaker so the 698// output is byte stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs LOCAL 699// hnsw.ef_search and hnsw.iterative_scan first, exactly as for the unscoped query. 700func (q *Queries) SearchCandidatesHybridMission(ctx context.Context, arg SearchCandidatesHybridMissionParams) ([]SearchCandidatesHybridMissionRow, error) { 701 rows, err := q.db.Query(ctx, searchCandidatesHybridMission, 702 arg.RowLimit, 703 arg.QueryVector, 704 arg.MissionQuery, 705 arg.TextQuery, 706 ) 707 if err != nil { 708 return nil, err 709 } 710 defer rows.Close() 711 items := []SearchCandidatesHybridMissionRow{} 712 for rows.Next() { 713 var i SearchCandidatesHybridMissionRow 714 if err := rows.Scan( 715 &i.ID, 716 &i.OrganizationID, 717 &i.FullName, 718 &i.Email, 719 &i.Headline, 720 &i.ClearanceLevel, 721 &i.ClearancePoly, 722 &i.ClearanceStatus, 723 &i.ClearanceGrantedAt, 724 &i.ClearanceExpiresAt, 725 &i.ReinvestigationDueAt, 726 &i.YearsExperience, 727 &i.Location, 728 &i.Remote, 729 &i.Relocatable, 730 &i.Available, 731 &i.ResumeEmbedding, 732 &i.SearchTsv, 733 &i.CreatedAt, 734 &i.UpdatedAt, 735 &i.DeletedAt, 736 &i.Source, 737 &i.ExternalID, 738 &i.AnonymizedAt, 739 &i.ClearanceProvenance, 740 &i.ClearanceVerifiedAt, 741 &i.ClearanceCompartments, 742 &i.RrfScore, 743 ); err != nil { 744 return nil, err 745 } 746 items = append(items, i) 747 } 748 if err := rows.Err(); err != nil { 749 return nil, err 750 } 751 return items, nil 752} 753 754const searchCandidatesHybridMissionTagged = `-- name: SearchCandidatesHybridMissionTagged :many 755WITH vec_raw AS MATERIALIZED ( 756 SELECT id, (resume_embedding <=> $2::vector) AS distance 757 FROM candidates 758 WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL 759 AND EXISTS (SELECT 1 FROM candidate_mission_tags t 760 WHERE t.candidate_id = candidates.id AND t.mission_code = $3) 761 ORDER BY resume_embedding <=> $2::vector, id 762 LIMIT $1 763), 764vec AS ( 765 SELECT id, ROW_NUMBER() OVER (ORDER BY distance, id) AS rnk 766 FROM vec_raw 767), 768fts AS ( 769 SELECT id, ROW_NUMBER() OVER ( 770 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $4)) DESC, id 771 ) AS rnk 772 FROM candidates 773 WHERE deleted_at IS NULL 774 AND search_tsv @@ websearch_to_tsquery('english', $4) 775 AND EXISTS (SELECT 1 FROM candidate_mission_tags t 776 WHERE t.candidate_id = candidates.id AND t.mission_code = $3) 777 ORDER BY ts_rank(search_tsv, websearch_to_tsquery('english', $4)) DESC, id 778 LIMIT $1 779) 780SELECT 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, 781 (COALESCE(1.0/(60+vec.rnk), 0) + COALESCE(1.0/(60+fts.rnk), 0))::float8 AS rrf_score 782FROM candidates c 783LEFT JOIN vec ON vec.id = c.id 784LEFT JOIN fts ON fts.id = c.id 785WHERE (vec.id IS NOT NULL OR fts.id IS NOT NULL) AND c.deleted_at IS NULL 786ORDER BY rrf_score DESC, c.created_at DESC, c.id 787LIMIT $1 788` 789 790type SearchCandidatesHybridMissionTaggedParams struct { 791 RowLimit int32 `json:"row_limit"` 792 QueryVector pgvector.Vector `json:"query_vector"` 793 MissionCode string `json:"mission_code"` 794 TextQuery string `json:"text_query"` 795} 796 797type SearchCandidatesHybridMissionTaggedRow struct { 798 ID uuid.UUID `json:"id"` 799 OrganizationID uuid.UUID `json:"organization_id"` 800 FullName string `json:"full_name"` 801 Email *string `json:"email"` 802 Headline *string `json:"headline"` 803 ClearanceLevel string `json:"clearance_level"` 804 ClearancePoly string `json:"clearance_poly"` 805 ClearanceStatus string `json:"clearance_status"` 806 ClearanceGrantedAt pgtype.Date `json:"clearance_granted_at"` 807 ClearanceExpiresAt pgtype.Date `json:"clearance_expires_at"` 808 ReinvestigationDueAt pgtype.Date `json:"reinvestigation_due_at"` 809 YearsExperience int32 `json:"years_experience"` 810 Location *string `json:"location"` 811 Remote bool `json:"remote"` 812 Relocatable bool `json:"relocatable"` 813 Available bool `json:"available"` 814 ResumeEmbedding *pgvector.Vector `json:"resume_embedding"` 815 SearchTsv interface{} `json:"search_tsv"` 816 CreatedAt time.Time `json:"created_at"` 817 UpdatedAt time.Time `json:"updated_at"` 818 DeletedAt *time.Time `json:"deleted_at"` 819 Source string `json:"source"` 820 ExternalID *string `json:"external_id"` 821 AnonymizedAt *time.Time `json:"anonymized_at"` 822 ClearanceProvenance string `json:"clearance_provenance"` 823 ClearanceVerifiedAt *time.Time `json:"clearance_verified_at"` 824 ClearanceCompartments []string `json:"clearance_compartments"` 825 RrfScore float64 `json:"rrf_score"` 826} 827 828// Structured mission scoped hybrid retrieval (WS7 upgrade of the WS6 Option A text filter). It is identical 829// in shape to SearchCandidatesHybridMission, but the mission constraint on BOTH arms is a STRUCTURED 830// predicate on candidate_mission_tags (the deterministic classifier output) instead of the search_tsv 831// mission keyword text match. A candidate is in scope only when a candidate_mission_tags row binds it to the 832// requested mission_code, so the scope is the real, classified mission of the candidate, not a name or 833// headline string coincidence. This lifts the WS6 limit (a candidate whose mission is in the resume body but 834// not in the full_name, headline, or location text is now reachable once the tenant is tagged). The text 835// query ($text_query) still drives the FTS arm and the embedding arm still drives recall, so only the mission 836// gate changed. candidate_mission_tags is RLS scoped to the caller's org, so the EXISTS subquery cannot cross 837// a tenant boundary; the candidates table is RLS scoped too. Every ORDER BY keeps the unique id tiebreaker so 838// the output is byte stable for fixed inputs, the AEDT reproducibility mandate. The caller SETs LOCAL 839// hnsw.ef_search and hnsw.iterative_scan first, exactly as for the unscoped and the WS6 text scoped queries. 840func (q *Queries) SearchCandidatesHybridMissionTagged(ctx context.Context, arg SearchCandidatesHybridMissionTaggedParams) ([]SearchCandidatesHybridMissionTaggedRow, error) { 841 rows, err := q.db.Query(ctx, searchCandidatesHybridMissionTagged, 842 arg.RowLimit, 843 arg.QueryVector, 844 arg.MissionCode, 845 arg.TextQuery, 846 ) 847 if err != nil { 848 return nil, err 849 } 850 defer rows.Close() 851 items := []SearchCandidatesHybridMissionTaggedRow{} 852 for rows.Next() { 853 var i SearchCandidatesHybridMissionTaggedRow 854 if err := rows.Scan( 855 &i.ID, 856 &i.OrganizationID, 857 &i.FullName, 858 &i.Email, 859 &i.Headline, 860 &i.ClearanceLevel, 861 &i.ClearancePoly, 862 &i.ClearanceStatus, 863 &i.ClearanceGrantedAt, 864 &i.ClearanceExpiresAt, 865 &i.ReinvestigationDueAt, 866 &i.YearsExperience, 867 &i.Location, 868 &i.Remote, 869 &i.Relocatable, 870 &i.Available, 871 &i.ResumeEmbedding, 872 &i.SearchTsv, 873 &i.CreatedAt, 874 &i.UpdatedAt, 875 &i.DeletedAt, 876 &i.Source, 877 &i.ExternalID, 878 &i.AnonymizedAt, 879 &i.ClearanceProvenance, 880 &i.ClearanceVerifiedAt, 881 &i.ClearanceCompartments, 882 &i.RrfScore, 883 ); err != nil { 884 return nil, err 885 } 886 items = append(items, i) 887 } 888 if err := rows.Err(); err != nil { 889 return nil, err 890 } 891 return items, nil 892} 893 894const setCandidateClearanceProvenance = `-- name: SetCandidateClearanceProvenance :execrows 895UPDATE candidates 896SET clearance_provenance = $2, clearance_verified_at = $3, updated_at = now() 897WHERE id = $1 AND deleted_at IS NULL 898` 899 900type SetCandidateClearanceProvenanceParams struct { 901 ID uuid.UUID `json:"id"` 902 ClearanceProvenance string `json:"clearance_provenance"` 903 ClearanceVerifiedAt *time.Time `json:"clearance_verified_at"` 904} 905 906// An org FSO records HOW a candidate's clearance is known. RLS scopes this to the caller's own org. 907// verified_at is the date the FSO confirmed it; it is NULL for a self reported indicator. The platform 908// never verifies a clearance itself, it records the org's authoritative assertion. 909func (q *Queries) SetCandidateClearanceProvenance(ctx context.Context, arg SetCandidateClearanceProvenanceParams) (int64, error) { 910 result, err := q.db.Exec(ctx, setCandidateClearanceProvenance, arg.ID, arg.ClearanceProvenance, arg.ClearanceVerifiedAt) 911 if err != nil { 912 return 0, err 913 } 914 return result.RowsAffected(), nil 915} 916 917const softDeleteCandidate = `-- name: SoftDeleteCandidate :exec 918UPDATE candidates SET deleted_at = now(), updated_at = now() WHERE id = $1 919` 920 921func (q *Queries) SoftDeleteCandidate(ctx context.Context, id uuid.UUID) error { 922 _, err := q.db.Exec(ctx, softDeleteCandidate, id) 923 return err 924} 925 926const updateCandidateEmbedding = `-- name: UpdateCandidateEmbedding :exec 927UPDATE candidates SET resume_embedding = $1::vector, updated_at = now() WHERE id = $2 928` 929 930type UpdateCandidateEmbeddingParams struct { 931 Embedding pgvector.Vector `json:"embedding"` 932 ID uuid.UUID `json:"id"` 933} 934 935// Writes a precomputed resume embedding onto a candidate, used by the bench import path AFTER the embed 936// network call has already run in the caller, so the migrator write transaction never holds open across 937// the inference round trip (the embed lock window rule). The vector is composed from name free structured 938// tokens only, never raw resume PII. A candidate with a NULL embedding is left embeddable by the 939// embed-backfill only-missing fallback, so a skipped embed never wedges a row. 940func (q *Queries) UpdateCandidateEmbedding(ctx context.Context, arg UpdateCandidateEmbeddingParams) error { 941 _, err := q.db.Exec(ctx, updateCandidateEmbedding, arg.Embedding, arg.ID) 942 return err 943} 944