// Code generated by sqlc. DO NOT EDIT. // versions: // sqlc v1.31.1 // source: candidates.sql package dbgen import ( "context" "time" "github.com/google/uuid" "github.com/jackc/pgx/v5/pgtype" pgvector "github.com/pgvector/pgvector-go" ) const addCandidateCertification = `-- name: AddCandidateCertification :exec INSERT INTO candidate_certifications (organization_id, candidate_id, name) VALUES ($1,$2,$3) ON CONFLICT (candidate_id, name) DO NOTHING ` type AddCandidateCertificationParams struct { OrganizationID uuid.UUID `json:"organization_id"` CandidateID uuid.UUID `json:"candidate_id"` Name string `json:"name"` } func (q *Queries) AddCandidateCertification(ctx context.Context, arg AddCandidateCertificationParams) error { _, err := q.db.Exec(ctx, addCandidateCertification, arg.OrganizationID, arg.CandidateID, arg.Name) return err } const addCandidateCustomerExperience = `-- name: AddCandidateCustomerExperience :exec INSERT INTO candidate_customer_experience (organization_id, candidate_id, agency) VALUES ($1,$2,$3) ON CONFLICT (candidate_id, agency) DO NOTHING ` type AddCandidateCustomerExperienceParams struct { OrganizationID uuid.UUID `json:"organization_id"` CandidateID uuid.UUID `json:"candidate_id"` Agency string `json:"agency"` } func (q *Queries) AddCandidateCustomerExperience(ctx context.Context, arg AddCandidateCustomerExperienceParams) error { _, err := q.db.Exec(ctx, addCandidateCustomerExperience, arg.OrganizationID, arg.CandidateID, arg.Agency) return err } const addCandidateSkill = `-- 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 ` type AddCandidateSkillParams struct { OrganizationID uuid.UUID `json:"organization_id"` CandidateID uuid.UUID `json:"candidate_id"` SkillID uuid.UUID `json:"skill_id"` } func (q *Queries) AddCandidateSkill(ctx context.Context, arg AddCandidateSkillParams) error { _, err := q.db.Exec(ctx, addCandidateSkill, arg.OrganizationID, arg.CandidateID, arg.SkillID) return err } const anonymizeCandidate = `-- name: AnonymizeCandidate :execrows 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 ` // 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. func (q *Queries) AnonymizeCandidate(ctx context.Context, id uuid.UUID) (int64, error) { result, err := q.db.Exec(ctx, anonymizeCandidate, id) if err != nil { return 0, err } return result.RowsAffected(), nil } const createCandidate = `-- 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 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 ` type CreateCandidateParams struct { OrganizationID uuid.UUID `json:"organization_id"` FullName string `json:"full_name"` Email *string `json:"email"` Headline *string `json:"headline"` ClearanceLevel string `json:"clearance_level"` ClearancePoly string `json:"clearance_poly"` ClearanceStatus string `json:"clearance_status"` YearsExperience int32 `json:"years_experience"` Location *string `json:"location"` Remote bool `json:"remote"` Relocatable bool `json:"relocatable"` Available bool `json:"available"` ResumeEmbedding *pgvector.Vector `json:"resume_embedding"` } func (q *Queries) CreateCandidate(ctx context.Context, arg CreateCandidateParams) (Candidate, error) { row := q.db.QueryRow(ctx, createCandidate, arg.OrganizationID, arg.FullName, arg.Email, arg.Headline, arg.ClearanceLevel, arg.ClearancePoly, arg.ClearanceStatus, arg.YearsExperience, arg.Location, arg.Remote, arg.Relocatable, arg.Available, arg.ResumeEmbedding, ) var i Candidate err := row.Scan( &i.ID, &i.OrganizationID, &i.FullName, &i.Email, &i.Headline, &i.ClearanceLevel, &i.ClearancePoly, &i.ClearanceStatus, &i.ClearanceGrantedAt, &i.ClearanceExpiresAt, &i.ReinvestigationDueAt, &i.YearsExperience, &i.Location, &i.Remote, &i.Relocatable, &i.Available, &i.ResumeEmbedding, &i.SearchTsv, &i.CreatedAt, &i.UpdatedAt, &i.DeletedAt, &i.Source, &i.ExternalID, &i.AnonymizedAt, ) return i, err } const getCandidate = `-- name: GetCandidate :one SELECT 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 FROM candidates WHERE id = $1 AND deleted_at IS NULL ` func (q *Queries) GetCandidate(ctx context.Context, id uuid.UUID) (Candidate, error) { row := q.db.QueryRow(ctx, getCandidate, id) var i Candidate err := row.Scan( &i.ID, &i.OrganizationID, &i.FullName, &i.Email, &i.Headline, &i.ClearanceLevel, &i.ClearancePoly, &i.ClearanceStatus, &i.ClearanceGrantedAt, &i.ClearanceExpiresAt, &i.ReinvestigationDueAt, &i.YearsExperience, &i.Location, &i.Remote, &i.Relocatable, &i.Available, &i.ResumeEmbedding, &i.SearchTsv, &i.CreatedAt, &i.UpdatedAt, &i.DeletedAt, &i.Source, &i.ExternalID, &i.AnonymizedAt, ) return i, err } const listCandidateCertifications = `-- name: ListCandidateCertifications :many SELECT name FROM candidate_certifications WHERE candidate_id = $1 ORDER BY name ` func (q *Queries) ListCandidateCertifications(ctx context.Context, candidateID uuid.UUID) ([]string, error) { rows, err := q.db.Query(ctx, listCandidateCertifications, candidateID) if err != nil { return nil, err } defer rows.Close() items := []string{} for rows.Next() { var name string if err := rows.Scan(&name); err != nil { return nil, err } items = append(items, name) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listCandidateCustomerExperience = `-- name: ListCandidateCustomerExperience :many SELECT agency FROM candidate_customer_experience WHERE candidate_id = $1 ORDER BY agency ` func (q *Queries) ListCandidateCustomerExperience(ctx context.Context, candidateID uuid.UUID) ([]string, error) { rows, err := q.db.Query(ctx, listCandidateCustomerExperience, candidateID) if err != nil { return nil, err } defer rows.Close() items := []string{} for rows.Next() { var agency string if err := rows.Scan(&agency); err != nil { return nil, err } items = append(items, agency) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listCandidateSkills = `-- 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 ` type ListCandidateSkillsRow struct { ID uuid.UUID `json:"id"` Name string `json:"name"` } func (q *Queries) ListCandidateSkills(ctx context.Context, candidateID uuid.UUID) ([]ListCandidateSkillsRow, error) { rows, err := q.db.Query(ctx, listCandidateSkills, candidateID) if err != nil { return nil, err } defer rows.Close() items := []ListCandidateSkillsRow{} for rows.Next() { var i ListCandidateSkillsRow if err := rows.Scan(&i.ID, &i.Name); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listCandidates = `-- name: ListCandidates :many SELECT 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 FROM candidates WHERE deleted_at IS NULL ORDER BY created_at DESC LIMIT $1 OFFSET $2 ` type ListCandidatesParams struct { Limit int32 `json:"limit"` Offset int32 `json:"offset"` } func (q *Queries) ListCandidates(ctx context.Context, arg ListCandidatesParams) ([]Candidate, error) { rows, err := q.db.Query(ctx, listCandidates, arg.Limit, arg.Offset) if err != nil { return nil, err } defer rows.Close() items := []Candidate{} for rows.Next() { var i Candidate if err := rows.Scan( &i.ID, &i.OrganizationID, &i.FullName, &i.Email, &i.Headline, &i.ClearanceLevel, &i.ClearancePoly, &i.ClearanceStatus, &i.ClearanceGrantedAt, &i.ClearanceExpiresAt, &i.ReinvestigationDueAt, &i.YearsExperience, &i.Location, &i.Remote, &i.Relocatable, &i.Available, &i.ResumeEmbedding, &i.SearchTsv, &i.CreatedAt, &i.UpdatedAt, &i.DeletedAt, &i.Source, &i.ExternalID, &i.AnonymizedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listCandidatesForIntel = `-- name: ListCandidatesForIntel :many SELECT 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 FROM candidates WHERE deleted_at IS NULL ORDER BY available DESC, updated_at DESC, created_at DESC LIMIT $1 ` // 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. func (q *Queries) ListCandidatesForIntel(ctx context.Context, limit int32) ([]Candidate, error) { rows, err := q.db.Query(ctx, listCandidatesForIntel, limit) if err != nil { return nil, err } defer rows.Close() items := []Candidate{} for rows.Next() { var i Candidate if err := rows.Scan( &i.ID, &i.OrganizationID, &i.FullName, &i.Email, &i.Headline, &i.ClearanceLevel, &i.ClearancePoly, &i.ClearanceStatus, &i.ClearanceGrantedAt, &i.ClearanceExpiresAt, &i.ReinvestigationDueAt, &i.YearsExperience, &i.Location, &i.Remote, &i.Relocatable, &i.Available, &i.ResumeEmbedding, &i.SearchTsv, &i.CreatedAt, &i.UpdatedAt, &i.DeletedAt, &i.Source, &i.ExternalID, &i.AnonymizedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const searchCandidatesHybrid = `-- name: SearchCandidatesHybrid :many 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.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, (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 ` type SearchCandidatesHybridParams struct { Column1 pgvector.Vector `json:"column_1"` Limit int32 `json:"limit"` WebsearchToTsquery string `json:"websearch_to_tsquery"` } type SearchCandidatesHybridRow struct { ID uuid.UUID `json:"id"` OrganizationID uuid.UUID `json:"organization_id"` FullName string `json:"full_name"` Email *string `json:"email"` Headline *string `json:"headline"` ClearanceLevel string `json:"clearance_level"` ClearancePoly string `json:"clearance_poly"` ClearanceStatus string `json:"clearance_status"` ClearanceGrantedAt pgtype.Date `json:"clearance_granted_at"` ClearanceExpiresAt pgtype.Date `json:"clearance_expires_at"` ReinvestigationDueAt pgtype.Date `json:"reinvestigation_due_at"` YearsExperience int32 `json:"years_experience"` Location *string `json:"location"` Remote bool `json:"remote"` Relocatable bool `json:"relocatable"` Available bool `json:"available"` ResumeEmbedding *pgvector.Vector `json:"resume_embedding"` SearchTsv interface{} `json:"search_tsv"` CreatedAt time.Time `json:"created_at"` UpdatedAt time.Time `json:"updated_at"` DeletedAt *time.Time `json:"deleted_at"` Source string `json:"source"` ExternalID *string `json:"external_id"` AnonymizedAt *time.Time `json:"anonymized_at"` RrfScore float64 `json:"rrf_score"` } // 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. func (q *Queries) SearchCandidatesHybrid(ctx context.Context, arg SearchCandidatesHybridParams) ([]SearchCandidatesHybridRow, error) { rows, err := q.db.Query(ctx, searchCandidatesHybrid, arg.Column1, arg.Limit, arg.WebsearchToTsquery) if err != nil { return nil, err } defer rows.Close() items := []SearchCandidatesHybridRow{} for rows.Next() { var i SearchCandidatesHybridRow if err := rows.Scan( &i.ID, &i.OrganizationID, &i.FullName, &i.Email, &i.Headline, &i.ClearanceLevel, &i.ClearancePoly, &i.ClearanceStatus, &i.ClearanceGrantedAt, &i.ClearanceExpiresAt, &i.ReinvestigationDueAt, &i.YearsExperience, &i.Location, &i.Remote, &i.Relocatable, &i.Available, &i.ResumeEmbedding, &i.SearchTsv, &i.CreatedAt, &i.UpdatedAt, &i.DeletedAt, &i.Source, &i.ExternalID, &i.AnonymizedAt, &i.RrfScore, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const softDeleteCandidate = `-- name: SoftDeleteCandidate :exec UPDATE candidates SET deleted_at = now(), updated_at = now() WHERE id = $1 ` func (q *Queries) SoftDeleteCandidate(ctx context.Context, id uuid.UUID) error { _, err := q.db.Exec(ctx, softDeleteCandidate, id) return err }