Janady13/missionmatch / internal/modules/matching/candidate_search.go

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / internal/modules/matching/candidate_search.go
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	720d47fae476b0d38477ae4401697d372b1ea2bc
Branch	feature/opportunity-to-outcome
Path or archive member	internal/modules/matching/candidate_search.go
Original SHA-256	870adebf0f84ea5c7a44af2d5cb90a5ce42514fa1efcd0a2901e4327c9177327
Git blob SHA	afe80625f222fdb9b046b92a73ae32567cc9df84
Reading-copy SHA-256	870adebf0f84ea5c7a44af2d5cb90a5ce42514fa1efcd0a2901e4327c9177327
Original source ↗
Recovered source transcript

Download complete source text ↓468 lines · 19.8 KB
package matching

// CANDIDATE SEARCH. The question a recruiter asks all day and this product could not be asked.
//
// GET /candidates took a limit and an offset and nothing else, so "TS/SCI cleared network engineers in
// Maryland who are available" had no route. The indexes to answer it have existed since migration 0002, a GIN
// index over search_tsv and an HNSW index over resume_embedding, and nothing was mounted on top of them.
//
// EVERY USER VALUE IS A BOUND PARAMETER. The WHERE clause is composed from a validated struct, and no caller
// supplied string is ever interpolated into SQL. The only things interpolated are column names and operators
// chosen by this file from closed sets, which is the same rule the record scope filter follows.
//
// IT RESPECTS RECORD SCOPE. A search that ignored the scope resolved in migration 0088 would be a hole
// straight through it: a recruiter narrowed to their own records could simply search for everyone else's.
// The owner predicate is appended by the same orgsettings.Filter every list uses, so the two cannot drift.

import (
	"context"
	"encoding/json"
	"fmt"
	"strings"
	"time"

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"

	"github.com/missionworkforce/missionmatch/internal/modules/matching/engine"
	"github.com/missionworkforce/missionmatch/internal/modules/matching/intelligence"
	"github.com/missionworkforce/missionmatch/internal/platform/database/dbgen"
)

// CandidateQuery is the closed filter vocabulary. It is what a saved search stores and what the search route
// accepts, deliberately the same type, so a saved search cannot express something the live search cannot run.
type CandidateQuery struct {
	// Text runs against the existing full text index. It is passed to a parser that treats it as WORDS, never
	// as query syntax, so a recruiter typing an ampersand or a colon gets results rather than an error.
	Text string `json:"text,omitempty"`
	// ClearanceLevels is an OR within the field: any of these levels matches. It is not a hierarchy here,
	// deliberately. The scoring engine encodes that TS/SCI satisfies Secret; a SEARCH is a filter, and a
	// recruiter asking for Secret holders usually means people whose clearance IS Secret.
	ClearanceLevels []string `json:"clearance_levels,omitempty"`
	ClearanceStatus []string `json:"clearance_status,omitempty"`
	// Skills matches candidates holding ALL of these, because a recruiter listing three skills means a person
	// with all three. Any of them is what the text field is for.
	Skills        []string `json:"skills,omitempty"`
	Location      string   `json:"location,omitempty"`
	Available     *bool    `json:"available,omitempty"`
	Remote        *bool    `json:"remote,omitempty"`
	Relocatable   *bool    `json:"relocatable,omitempty"`
	MinYears      *int     `json:"min_years,omitempty"`
	MaxYears      *int     `json:"max_years,omitempty"`
	OwnerUserID   string   `json:"owner_user_id,omitempty"`
	Unowned       *bool    `json:"unowned,omitempty"`
	ListID        string   `json:"list_id,omitempty"`
	ExcludeListID string   `json:"exclude_list_id,omitempty"`
	// ClearanceExpiringDays finds people whose clearance lapses within N days, which is the one search that
	// is genuinely specific to this market and the reason the clearance_expires index exists.
	ClearanceExpiringDays *int `json:"clearance_expiring_days,omitempty"`

	// AccessPostures filters on the break in access posture, which is the question a cleared desk actually
	// asks: show me the people I can still place without waiting on an investigation. An empty list means no
	// filter, matching every other list field here.
	AccessPostures []string `json:"access_postures,omitempty"`

	Limit  int `json:"limit,omitempty"`
	Offset int `json:"offset,omitempty"`
}

// CandidateSearchRow is one line of a result list, and it is a DELIBERATE SUBSET of the candidate row.
//
// The search used to return dbgen.Candidate verbatim, which was wrong twice. It shipped resume_embedding, a
// vector of several hundred floats, and search_tsv, the lexeme index, on EVERY row of a page that can hold
// two hundred: megabytes of payload that no interface can render and no caller asked for. And it shipped
// email unconditionally, which is the field the contact sensitivity setting exists to govern.
//
// A named result type is what makes the second one fixable. As long as the response was the table row, every
// column added to candidates was published to the wire the day the migration ran, and nobody had to decide.
type CandidateSearchRow struct {
	ID              uuid.UUID  `json:"id"`
	FullName        string     `json:"full_name"`
	Headline        *string    `json:"headline,omitempty"`
	ClearanceLevel  string     `json:"clearance_level"`
	ClearancePoly   string     `json:"clearance_poly,omitempty"`
	ClearanceStatus string     `json:"clearance_status"`
	YearsExperience int32      `json:"years_experience"`
	Location        *string    `json:"location,omitempty"`
	Remote          bool       `json:"remote"`
	Relocatable     bool       `json:"relocatable"`
	Available       bool       `json:"available"`
	Source          string     `json:"source"`
	OwnerUserID     *uuid.UUID `json:"owner_user_id,omitempty"`
	// Email is omitted entirely, not blanked, when the caller may not see contact details. An empty string
	// would read as a candidate with no email address on file, which is a different and false statement.
	Email *string `json:"email,omitempty"`
	// ContactRedacted says why the email is absent, so an interface can tell "hidden from you" apart from
	// "never captured" and say so instead of showing an unexplained gap.
	ContactRedacted bool `json:"contact_redacted,omitempty"`

	// Access is the break in access posture: whether this cleared person can still be reinstated on a new
	// sponsor's paperwork, or has passed the twenty four month line and now needs an investigation. It is on
	// the SEARCH ROW rather than only the detail screen on purpose, because it is the fact that decides
	// whether a name in a result list is worth calling, and a recruiter should not have to open twenty records
	// to find out which three are placeable this month.
	Access *intelligence.AccessIntel `json:"access,omitempty"`
}

// CandidateSearchResult carries the page plus the total, because a recruiter needs to know whether the
// answer is nine people or nine hundred before deciding what to do with it.
type CandidateSearchResult struct {
	Candidates []CandidateSearchRow `json:"candidates"`
	Total      int                  `json:"total"`
	Limit      int                  `json:"limit"`
	Offset     int                  `json:"offset"`
	// ScopeApplied reports that record scope narrowed this answer, so a short result set can be explained
	// rather than mistaken for an empty bench.
	ScopeApplied bool `json:"scope_applied"`
}

// searchRow projects a stored candidate onto the wire shape, applying field sensitivity as it goes.
//
// The redaction happens HERE, at the single point where a stored row becomes a response, rather than in the
// handler. A handler that forgot to call a redact function would ship the emails and pass every test that
// only checked the search filters, which is exactly how the original defect survived.
func searchRow(c dbgen.Candidate, canSeeContacts bool, asOf time.Time) CandidateSearchRow {
	row := CandidateSearchRow{
		ID:              c.ID,
		FullName:        c.FullName,
		Headline:        c.Headline,
		ClearanceLevel:  c.ClearanceLevel,
		ClearancePoly:   c.ClearancePoly,
		ClearanceStatus: c.ClearanceStatus,
		YearsExperience: c.YearsExperience,
		Location:        c.Location,
		Remote:          c.Remote,
		Relocatable:     c.Relocatable,
		Available:       c.Available,
		Source:          c.Source,
		OwnerUserID:     c.OwnerUserID,
	}
	// Derived from the row already in hand, so no second query and no second source of truth. The pure
	// function is the same one the candidate record uses, which is what stops a list and a detail screen
	// disagreeing about whether somebody is placeable.
	intel := intelligence.Access(accessFactsFrom(c), asOf)
	row.Access = &intel
	if canSeeContacts {
		row.Email = c.Email
		return row
	}
	// Only claim something was hidden when there was something to hide. Saying "redacted" about a candidate
	// with no email on file would send a recruiter looking for an address that does not exist.
	row.ContactRedacted = c.Email != nil && *c.Email != ""
	return row
}

const (
	maxSearchLimit     = 200
	defaultSearchLimit = 50
	maxSearchTerms     = 24
)

// Normalize bounds and cleans a query. It is called on the way in from a request AND on the way out of a
// saved search, because stored criteria are user input that has been sitting in a table and must not be
// trusted any more than a fresh request.
func (q *CandidateQuery) Normalize() {
	q.Text = strings.TrimSpace(q.Text)
	if len(q.Text) > 400 {
		q.Text = q.Text[:400]
	}
	q.Location = strings.TrimSpace(q.Location)
	if len(q.Location) > 120 {
		q.Location = q.Location[:120]
	}
	// Translated to the STORED CODES, not merely trimmed. See canonicalClearanceLevels for why.
	q.ClearanceLevels = canonicalClearanceLevels(boundList(q.ClearanceLevels))
	q.ClearanceStatus = canonicalClearanceStatus(boundList(q.ClearanceStatus))
	q.Skills = boundList(q.Skills)
	// Validated against the closed set, so an unknown posture is dropped rather than silently matching
	// nothing, which is what an unrecognised value in a filter list otherwise does.
	q.AccessPostures = ValidAccessPostures(boundList(q.AccessPostures))
	if q.Limit <= 0 {
		q.Limit = defaultSearchLimit
	}
	if q.Limit > maxSearchLimit {
		q.Limit = maxSearchLimit
	}
	if q.Offset < 0 {
		q.Offset = 0
	}
	if q.MinYears != nil && *q.MinYears < 0 {
		zero := 0
		q.MinYears = &zero
	}
	if q.ClearanceExpiringDays != nil && *q.ClearanceExpiringDays < 0 {
		q.ClearanceExpiringDays = nil
	}
}

func boundList(in []string) []string {
	out := make([]string, 0, len(in))
	seen := map[string]bool{}
	for _, v := range in {
		v = strings.TrimSpace(v)
		if v == "" || len(v) > 120 || seen[strings.ToLower(v)] {
			continue
		}
		seen[strings.ToLower(v)] = true
		out = append(out, v)
		if len(out) == maxSearchTerms {
			break
		}
	}
	return out
}

// SearchCandidates runs a filtered search.
func (s *Service) SearchCandidates(ctx context.Context, q CandidateQuery) (CandidateSearchResult, error) {
	out := CandidateSearchResult{Candidates: []CandidateSearchRow{}}
	if s.db == nil {
		return out, ErrNoDatabase
	}
	q.Normalize()
	out.Limit, out.Offset = q.Limit, q.Offset

	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		where := []string{"c.deleted_at IS NULL"}
		args := []any{}
		add := func(clause string, vals ...any) {
			args = append(args, vals...)
			where = append(where, clause)
		}

		if q.Text != "" {
			// websearch_to_tsquery treats the input as WORDS AND PHRASES, not as query syntax. plainto_ and
			// to_tsquery would either drop operators silently or raise a syntax error on an apostrophe, and a
			// recruiter typing O'Brien should get O'Brien rather than a 500.
			add(fmt.Sprintf("c.search_tsv @@ websearch_to_tsquery('english', $%d)", len(args)+1), q.Text)
		}
		if len(q.ClearanceLevels) > 0 {
			add(fmt.Sprintf("c.clearance_level = ANY($%d)", len(args)+1), q.ClearanceLevels)
		}
		if len(q.ClearanceStatus) > 0 {
			add(fmt.Sprintf("c.clearance_status = ANY($%d)", len(args)+1), q.ClearanceStatus)
		}
		if q.Location != "" {
			// A prefix and substring match, because "Columbia, MD" and "Columbia MD" and "columbia" are the
			// same intent and a recruiter should not have to guess the stored format.
			add(fmt.Sprintf("c.location ILIKE '%%' || $%d || '%%'", len(args)+1), q.Location)
		}
		if q.Available != nil {
			add(fmt.Sprintf("c.available = $%d", len(args)+1), *q.Available)
		}
		if q.Remote != nil {
			add(fmt.Sprintf("c.remote = $%d", len(args)+1), *q.Remote)
		}
		if q.Relocatable != nil {
			add(fmt.Sprintf("c.relocatable = $%d", len(args)+1), *q.Relocatable)
		}
		if q.MinYears != nil {
			add(fmt.Sprintf("c.years_experience >= $%d", len(args)+1), *q.MinYears)
		}
		if q.MaxYears != nil {
			add(fmt.Sprintf("c.years_experience <= $%d", len(args)+1), *q.MaxYears)
		}
		// THE POSTURE FILTER NARROWS IN SQL AND CONFIRMS IN GO. The posture is derived, not stored, so it
		// cannot be compared directly in a WHERE clause without duplicating the rule in two languages, which
		// is how two answers to one question start. The SQL narrows to the date range the requested postures
		// could possibly fall in, and the projection below drops anything the pure function disagrees with.
		if len(q.AccessPostures) > 0 {
			add(accessPostureSQL(q.AccessPostures, len(args)+1))
		}
		if q.ClearanceExpiringDays != nil {
			add(fmt.Sprintf("c.clearance_expires_at IS NOT NULL AND c.clearance_expires_at <= now() + make_interval(days => $%d)",
				len(args)+1), *q.ClearanceExpiringDays)
		}
		if id, ok := parseUUID(q.OwnerUserID); ok {
			add(fmt.Sprintf("c.owner_user_id = $%d", len(args)+1), id)
		}
		if q.Unowned != nil && *q.Unowned {
			where = append(where, "c.owner_user_id IS NULL")
		}
		if id, ok := parseUUID(q.ListID); ok {
			add(fmt.Sprintf("EXISTS (SELECT 1 FROM candidate_list_members m WHERE m.candidate_id = c.id AND m.list_id = $%d)",
				len(args)+1), id)
		}
		if id, ok := parseUUID(q.ExcludeListID); ok {
			// The one that makes a do not contact list useful: everyone matching, EXCEPT the people who asked
			// not to be approached.
			add(fmt.Sprintf("NOT EXISTS (SELECT 1 FROM candidate_list_members m WHERE m.candidate_id = c.id AND m.list_id = $%d)",
				len(args)+1), id)
		}
		// SKILLS MATCH ALL, one EXISTS per skill, because a recruiter naming three skills means a person with
		// all three. Bounded to maxSearchTerms by Normalize, so this cannot become an unbounded join.
		for _, sk := range q.Skills {
			add(fmt.Sprintf(`EXISTS (SELECT 1 FROM candidate_skills cs JOIN skills sk ON sk.id = cs.skill_id
			                          WHERE cs.candidate_id = c.id AND lower(sk.name) = lower($%d))`,
				len(args)+1), sk)
		}

		// RECORD SCOPE, appended by the SAME filter every list uses. Without this a recruiter narrowed to
		// their own records could search for everybody else's, which would be a hole straight through
		// migration 0088.
		if s.scope != nil {
			f, e := s.scope.ResolveScopeTx(ctx, tx)
			if e != nil {
				return e
			}
			if !f.Unrestricted() {
				clause, sargs, se := f.SQL("owner_user_id", len(args)+1)
				if se != nil {
					return se
				}
				if clause != "" {
					// The column is qualified for this query's alias. f.SQL validated the name; this only
					// prefixes it.
					where = append(where, strings.ReplaceAll(clause, "owner_user_id", "c.owner_user_id"))
					args = append(args, sargs...)
					out.ScopeApplied = true
				}
			}
		}

		clause := strings.Join(where, " AND ")
		if e := tx.QueryRow(ctx, `SELECT count(*)::int FROM candidates c WHERE `+clause, args...).Scan(&out.Total); e != nil {
			return fmt.Errorf("matching: count candidates: %w", e)
		}
		// The trailing id makes the order total, so paging is stable across pages. Without it two candidates
		// created in the same millisecond can swap between page one and page two and a recruiter sees one of
		// them twice and the other never.
		rows, e := tx.Query(ctx, `SELECT c.* FROM candidates c WHERE `+clause+
			fmt.Sprintf(" ORDER BY c.created_at DESC, c.id DESC LIMIT $%d OFFSET $%d", len(args)+1, len(args)+2),
			append(args, q.Limit, q.Offset)...)
		if e != nil {
			return fmt.Errorf("matching: search candidates: %w", e)
		}
		defer rows.Close()
		list, e := pgx.CollectRows(rows, pgx.RowToStructByName[dbgen.Candidate])
		if e != nil {
			return fmt.Errorf("matching: read candidates: %w", e)
		}
		// FIELD SENSITIVITY, resolved once per search rather than once per row, and inside the same
		// transaction so the settings read is the same tenant context as the candidates read.
		canSeeContacts := s.scope.ResolvePolicyTx(ctx, tx).CanSeeContacts
		asOf := s.clock().UTC()
		out.Candidates = make([]CandidateSearchRow, 0, len(list))
		for _, c := range list {
			row := searchRow(c, canSeeContacts, asOf)
			// The exact half of the posture filter. The SQL narrowed loosely on dates; the pure function owns
			// the decision, and anything it disagrees with is dropped here rather than the rule being written
			// twice in two languages.
			if row.Access != nil && !keepPosture(q.AccessPostures, row.Access.Posture) {
				continue
			}
			out.Candidates = append(out.Candidates, row)
		}
		return nil
	})
	return out, err
}

func parseUUID(s string) (uuid.UUID, bool) {
	s = strings.TrimSpace(s)
	if s == "" {
		return uuid.Nil, false
	}
	id, err := uuid.Parse(s)
	if err != nil {
		return uuid.Nil, false
	}
	return id, true
}

// MarshalCriteria and UnmarshalCriteria are the saved search boundary. Stored criteria are re validated
// through Normalize on the way out, because a blob that has been sitting in a table is still user input.
func MarshalCriteria(q CandidateQuery) ([]byte, error) {
	q.Limit, q.Offset = 0, 0 // paging is per run, never part of what was saved
	return json.Marshal(q)
}

func UnmarshalCriteria(b []byte) (CandidateQuery, error) {
	var q CandidateQuery
	if len(b) == 0 {
		return q, nil
	}
	if err := json.Unmarshal(b, &q); err != nil {
		return CandidateQuery{}, fmt.Errorf("matching: stored search criteria are unreadable: %w", err)
	}
	q.Normalize()
	return q, nil
}

// CLEARANCE INPUT IS TRANSLATED TO THE STORED CODE, and this is the whole reason the first version of this
// search silently returned nothing.
//
// The persisted value is the CODE, ts_sci, and the human label is TS/SCI. codes.go says so in as many words
// and calls persisting the label rather than the code "a real bug class". The search then compared raw user
// input against the column, so a recruiter typing exactly what the interface shows them got an empty bench
// and no error, which is the worst possible failure for a search: it looks like an answer.
//
// Both spellings are accepted, deliberately. A user interface sends the code; a person types the label; an
// imported saved search may carry either. All three must work, and anything unrecognised is DROPPED rather
// than passed through, because an unmatchable term would silently empty the result set again.
func canonicalClearanceLevels(in []string) []string {
	out := make([]string, 0, len(in))
	seen := map[string]bool{}
	for _, raw := range in {
		code, ok := clearanceLevelCode(raw)
		if !ok || seen[code] {
			continue
		}
		seen[code] = true
		out = append(out, code)
	}
	return out
}

func clearanceLevelCode(raw string) (string, bool) {
	v := strings.ToLower(strings.TrimSpace(raw))
	if v == "" {
		return "", false
	}
	// Already a code.
	if _, err := engine.ParseClearanceLevel(v); err == nil {
		return v, true
	}
	// A human label, matched against the engine's OWN table so the two cannot drift.
	for _, lvl := range []engine.ClearanceLevel{
		engine.ClearanceUnknown, engine.ClearanceNone, engine.ClearanceConfidential,
		engine.ClearanceSecret, engine.ClearanceTopSecret, engine.ClearanceTSSCI,
	} {
		if strings.ToLower(lvl.String()) == v {
			return lvl.Code(), true
		}
	}
	// The spellings people actually type that are neither the code nor the canonical label.
	switch v {
	case "ts/sci", "ts sci", "tssci", "top secret/sci", "top secret sci":
		return engine.ClearanceTSSCI.Code(), true
	case "ts", "top secret":
		return engine.ClearanceTopSecret.Code(), true
	case "s":
		return engine.ClearanceSecret.Code(), true
	case "c":
		return engine.ClearanceConfidential.Code(), true
	}
	return "", false
}

// canonicalClearanceStatus does the same for the status field, which has the same code and label split.
func canonicalClearanceStatus(in []string) []string {
	out := make([]string, 0, len(in))
	seen := map[string]bool{}
	for _, raw := range in {
		v := strings.ToLower(strings.TrimSpace(raw))
		if v == "" {
			continue
		}
		if _, err := engine.ParseClearanceStatus(v); err != nil {
			continue
		}
		if seen[v] {
			continue
		}
		seen[v] = true
		out = append(out, v)
	}
	return out
}