503 lines
18 KiB
Go
503 lines
18 KiB
Go
package tools
|
|
|
|
import (
|
|
"context"
|
|
"encoding/json"
|
|
"fmt"
|
|
"time"
|
|
|
|
"github.com/krow/krow-backend/go-api/internal/repo"
|
|
)
|
|
|
|
// The hiring tools: pipeline quality, recent hires, hire performance, roles at
|
|
// risk, and the talent pool.
|
|
//
|
|
// Each reads one resource and goes through authorize(), so the talent scopes
|
|
// differ meaningfully between them and are not restated here: applications
|
|
// scope by the caller's email, postings scope to active roles only, worker
|
|
// profiles scope by user id. That is the policy table's business, and the whole
|
|
// reason these handlers are short.
|
|
|
|
/* ── Candidate quality ──────────────────────────────────────────────────── */
|
|
|
|
// CandidatesQuality reports the applicant pipeline and how strong it is.
|
|
func CandidatesQuality(db repo.Querier) Tool {
|
|
return Tool{
|
|
Name: "candidates_quality",
|
|
Description: "Read the applicant pipeline: how many applications are at each stage, " +
|
|
"the average AI match score, how many are strong versus weak, and how many are " +
|
|
"waiting to be screened. Use for questions about candidate quality, pipeline " +
|
|
"health, and whether there is a screening backlog.",
|
|
InputSchema: periodSchema("How many stages to list. Defaults to all."),
|
|
Effect: EffectRead,
|
|
MaxResultBytes: DefaultMaxResultBytes,
|
|
Handler: func(ctx context.Context, tc Context, inputs json.RawMessage) Result {
|
|
q, denied := authorize(tc, "job-applications")
|
|
if denied != nil {
|
|
return *denied
|
|
}
|
|
in, from, to, bad := decodePeriod(inputs)
|
|
if bad != nil {
|
|
return *bad
|
|
}
|
|
if !from.IsZero() {
|
|
q.gte("created_date", from)
|
|
q.lt("created_date", to)
|
|
}
|
|
|
|
// ai_score 0 is the absence of a score, not a score of zero — the
|
|
// same rule the product states in candidateIntelligence.js ("null
|
|
// rather than zeros ... so an unscreened candidate shows '—' instead
|
|
// of a confident-looking 0"). Counting zeros as scores reported 16
|
|
// weak candidates averaging 28 where the truth was 1 weak and 76.
|
|
var (
|
|
total, strong, weak, unscreened, scored int64
|
|
avgScore *float64
|
|
)
|
|
err := db.QueryRow(ctx, `
|
|
SELECT count(*),
|
|
count(*) FILTER (WHERE ai_score >= 80),
|
|
count(*) FILTER (WHERE ai_score > 0 AND ai_score < 50),
|
|
count(*) FILTER (WHERE status = 'applied'),
|
|
count(*) FILTER (WHERE ai_score > 0),
|
|
avg(ai_score) FILTER (WHERE ai_score > 0)
|
|
FROM job_applications
|
|
WHERE `+q.clause(), q.args...,
|
|
).Scan(&total, &strong, &weak, &unscreened, &scored, &avgScore)
|
|
if err != nil {
|
|
return Failf(CodeFailed, "the applications could not be read")
|
|
}
|
|
|
|
stages, err := groupCount(ctx, db, "job_applications", "status::text", q, in.limitOr(20))
|
|
if err != nil {
|
|
return Failf(CodeFailed, "the applications could not be read")
|
|
}
|
|
|
|
data := map[string]any{
|
|
"period": periodOrAll(in.Period),
|
|
"applications": total,
|
|
"stages": stages,
|
|
"strong": strong,
|
|
"weak": weak,
|
|
"unscreened": unscreened,
|
|
"scored": scored,
|
|
}
|
|
// The average is over the scored ones only, so say how many that is.
|
|
if avgScore != nil {
|
|
data["averageMatchScore"] = int(*avgScore + 0.5)
|
|
data["averageMatchScoreBasis"] = scored
|
|
}
|
|
if total == 0 {
|
|
data["note"] = "No applications match that. This is a real answer, not a failure to look."
|
|
}
|
|
return OK(data)
|
|
},
|
|
}
|
|
}
|
|
|
|
/* ── Recent hires ───────────────────────────────────────────────────────── */
|
|
|
|
// HiresRecent lists who was hired and for what.
|
|
func HiresRecent(db repo.Querier) Tool {
|
|
return Tool{
|
|
Name: "hires_recent",
|
|
Description: "List recent hires: who was hired, for which role, their match score " +
|
|
"and when. Use for questions about who has joined, hiring volume, and what has " +
|
|
"been filled recently.",
|
|
InputSchema: periodSchema("How many hires to list, most recent first. Defaults to 20."),
|
|
Effect: EffectRead,
|
|
MaxResultBytes: DefaultMaxResultBytes,
|
|
Handler: func(ctx context.Context, tc Context, inputs json.RawMessage) Result {
|
|
q, denied := authorize(tc, "job-applications")
|
|
if denied != nil {
|
|
return *denied
|
|
}
|
|
in, from, to, bad := decodePeriod(inputs)
|
|
if bad != nil {
|
|
return *bad
|
|
}
|
|
// A hire is an application that reached one of the two terminal
|
|
// positive states. `assigned` counts: a worker placed on an
|
|
// assignment was hired, whatever the row was last labelled.
|
|
q.raw("status IN ('hired', 'assigned')")
|
|
if !from.IsZero() {
|
|
q.gte("created_date", from)
|
|
q.lt("created_date", to)
|
|
}
|
|
|
|
args := append(append([]any{}, q.args...), in.limitOr(20))
|
|
rows, err := db.Query(ctx, `
|
|
SELECT applicant_name, coalesce(nullif(job_title, ''), 'unspecified'),
|
|
nullif(ai_score, 0), created_date
|
|
FROM job_applications
|
|
WHERE `+q.clause()+`
|
|
ORDER BY created_date DESC
|
|
LIMIT $`+fmt.Sprint(len(args)), args...)
|
|
if err != nil {
|
|
return Failf(CodeFailed, "the applications could not be read")
|
|
}
|
|
defer rows.Close()
|
|
|
|
type hire struct {
|
|
Name string `json:"name"`
|
|
Role string `json:"role"`
|
|
Score *int `json:"matchScore,omitempty"`
|
|
When time.Time `json:"hiredOn"`
|
|
}
|
|
var hires []hire
|
|
for rows.Next() {
|
|
var h hire
|
|
if err := rows.Scan(&h.Name, &h.Role, &h.Score, &h.When); err != nil {
|
|
return Failf(CodeFailed, "the applications could not be read")
|
|
}
|
|
hires = append(hires, h)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return Failf(CodeFailed, "the applications could not be read")
|
|
}
|
|
|
|
data := map[string]any{
|
|
"period": periodOrAll(in.Period),
|
|
"hires": hires,
|
|
// Named for what it is. "count" would read as "hires in this
|
|
// period", which it is not once a limit is applied.
|
|
"listed": len(hires),
|
|
}
|
|
if len(hires) == 0 {
|
|
data["note"] = "No hires match that. This is a real answer, not a failure to look."
|
|
}
|
|
return OK(data)
|
|
},
|
|
}
|
|
}
|
|
|
|
/* ── Hire performance ───────────────────────────────────────────────────── */
|
|
|
|
// HiresPerformance reports how hired workers are performing since joining.
|
|
func HiresPerformance(db repo.Querier) Tool {
|
|
return Tool{
|
|
Name: "hires_performance",
|
|
Description: "Read how hired workers are performing: average Krow score, " +
|
|
"reliability, attendance and client rating across the workforce, plus the " +
|
|
"strongest and weakest performers. Use for questions about whether hires are " +
|
|
"working out and who needs support.",
|
|
InputSchema: periodSchema("How many workers to list at each end. Defaults to 5."),
|
|
Effect: EffectRead,
|
|
MaxResultBytes: DefaultMaxResultBytes,
|
|
Handler: func(ctx context.Context, tc Context, inputs json.RawMessage) Result {
|
|
q, denied := authorize(tc, "worker-profiles")
|
|
if denied != nil {
|
|
return *denied
|
|
}
|
|
in, _, _, bad := decodePeriod(inputs)
|
|
if bad != nil {
|
|
return *bad
|
|
}
|
|
|
|
// Every one of these is 0 for a worker nobody has rated yet — the
|
|
// product renders that as "Not yet scored" (dataResolver.js) and its
|
|
// lowest band starts above 0 (TalentPool.jsx). Averaging the zeros in
|
|
// reported a 1.6-of-5 client rating for a workforce rated 4.7.
|
|
var (
|
|
total, scored int64
|
|
krow, reliability, attendance, perf, ratings *float64
|
|
)
|
|
err := db.QueryRow(ctx, `
|
|
SELECT count(*),
|
|
count(*) FILTER (WHERE krow_score > 0),
|
|
avg(krow_score) FILTER (WHERE krow_score > 0),
|
|
avg(reliability_score) FILTER (WHERE reliability_score > 0),
|
|
avg(attendance_score) FILTER (WHERE attendance_score > 0),
|
|
avg(performance_score) FILTER (WHERE performance_score > 0),
|
|
avg(client_rating) FILTER (WHERE client_rating > 0)
|
|
FROM worker_profiles
|
|
WHERE `+q.clause(), q.args...,
|
|
).Scan(&total, &scored, &krow, &reliability, &attendance, &perf, &ratings)
|
|
if err != nil {
|
|
return Failf(CodeFailed, "the worker profiles could not be read")
|
|
}
|
|
|
|
top, err := workersByScore(ctx, db, q, in.limitOr(5), "DESC")
|
|
if err != nil {
|
|
return Failf(CodeFailed, "the worker profiles could not be read")
|
|
}
|
|
bottom, err := workersByScore(ctx, db, q, in.limitOr(5), "ASC")
|
|
if err != nil {
|
|
return Failf(CodeFailed, "the worker profiles could not be read")
|
|
}
|
|
|
|
data := map[string]any{
|
|
"workers": total,
|
|
"scored": scored,
|
|
"unscored": total - scored,
|
|
"strongest": top,
|
|
"weakest": bottom,
|
|
}
|
|
putAvg(data, "averageKrowScore", krow)
|
|
putAvg(data, "averageReliability", reliability)
|
|
putAvg(data, "averageAttendance", attendance)
|
|
putAvg(data, "averagePerformance", perf)
|
|
if ratings != nil {
|
|
data["averageClientRating"] = round1(*ratings)
|
|
}
|
|
if total == 0 {
|
|
data["note"] = "No worker profiles are visible. This is a real answer, not a failure to look."
|
|
}
|
|
return OK(data)
|
|
},
|
|
}
|
|
}
|
|
|
|
type scoredWorker struct {
|
|
Name string `json:"name"`
|
|
KrowScore *int `json:"krowScore,omitempty"`
|
|
Reliability *int `json:"reliability,omitempty"`
|
|
Attendance *int `json:"attendance,omitempty"`
|
|
}
|
|
|
|
func workersByScore(ctx context.Context, db repo.Querier, q *query, limit int, dir string) ([]scoredWorker, error) {
|
|
// `dir` is never caller input — it is one of two literals chosen here, so
|
|
// there is no path by which an identifier reaches the statement from
|
|
// outside this file.
|
|
if dir != "ASC" {
|
|
dir = "DESC"
|
|
}
|
|
args := append(append([]any{}, q.args...), limit)
|
|
rows, err := db.Query(ctx, `
|
|
SELECT full_name, nullif(krow_score, 0), nullif(reliability_score, 0),
|
|
nullif(attendance_score, 0)
|
|
FROM worker_profiles
|
|
WHERE `+q.clause()+` AND krow_score > 0
|
|
ORDER BY krow_score `+dir+`, full_name ASC
|
|
LIMIT $`+fmt.Sprint(len(args)), args...)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var out []scoredWorker
|
|
for rows.Next() {
|
|
var w scoredWorker
|
|
if err := rows.Scan(&w.Name, &w.KrowScore, &w.Reliability, &w.Attendance); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, w)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
/* ── Positions at risk ──────────────────────────────────────────────────── */
|
|
|
|
// PositionsRisk reports roles that are struggling to fill.
|
|
func PositionsRisk(db repo.Querier) Tool {
|
|
return Tool{
|
|
Name: "positions_risk",
|
|
Description: "Read which open roles are at risk: how many applicants each has, " +
|
|
"how many are strong, how long each has been open, and its priority. Use for " +
|
|
"questions about roles that are hard to fill, urgent openings, and where " +
|
|
"attention is needed.",
|
|
InputSchema: periodSchema("How many roles to list, most at risk first. Defaults to 10."),
|
|
Effect: EffectRead,
|
|
MaxResultBytes: DefaultMaxResultBytes,
|
|
Handler: func(ctx context.Context, tc Context, inputs json.RawMessage) Result {
|
|
q, denied := authorizeAs(tc, "job-postings", "p")
|
|
if denied != nil {
|
|
return *denied
|
|
}
|
|
in, _, _, bad := decodePeriod(inputs)
|
|
if bad != nil {
|
|
return *bad
|
|
}
|
|
q.raw("p.status = 'active'")
|
|
|
|
// Counted before the limit is applied. Reporting len(roles) here
|
|
// made "how many roles are open" mean "how many I chose to show",
|
|
// so workspace_summary and this tool disagreed about the same
|
|
// number on the same data — the exact contradiction a reader would
|
|
// catch and a model would not.
|
|
var openRoles int64
|
|
if err := db.QueryRow(ctx,
|
|
`SELECT count(*) FROM job_postings p WHERE `+q.clause(), q.args...).Scan(&openRoles); err != nil {
|
|
return Failf(CodeFailed, "the job postings could not be read")
|
|
}
|
|
|
|
// The applicant counts are a correlated subquery rather than a join
|
|
// plus a Go-side tally: counting in SQL keeps the count behind the
|
|
// same predicate as the row it belongs to.
|
|
args := append(append([]any{}, q.args...), in.limitOr(10))
|
|
rows, err := db.Query(ctx, `
|
|
SELECT p.title, p.priority::text, p.headcount, p.created_date,
|
|
(SELECT count(*) FROM job_applications a
|
|
WHERE a.job_posting_id = p.id AND a.org_id = p.org_id),
|
|
(SELECT count(*) FROM job_applications a
|
|
WHERE a.job_posting_id = p.id AND a.org_id = p.org_id AND a.ai_score >= 80)
|
|
FROM job_postings p
|
|
WHERE `+q.clause()+`
|
|
ORDER BY p.priority ASC, p.created_date ASC
|
|
LIMIT $`+fmt.Sprint(len(args)), args...)
|
|
if err != nil {
|
|
return Failf(CodeFailed, "the job postings could not be read")
|
|
}
|
|
defer rows.Close()
|
|
|
|
type role struct {
|
|
Title string `json:"title"`
|
|
Priority string `json:"priority"`
|
|
Headcount *int `json:"headcount,omitempty"`
|
|
DaysOpen int `json:"daysOpen"`
|
|
Applicants int64 `json:"applicants"`
|
|
Strong int64 `json:"strongApplicants"`
|
|
Risk string `json:"risk"`
|
|
}
|
|
var roles []role
|
|
now := time.Now()
|
|
for rows.Next() {
|
|
var r role
|
|
var created time.Time
|
|
if err := rows.Scan(&r.Title, &r.Priority, &r.Headcount, &created, &r.Applicants, &r.Strong); err != nil {
|
|
return Failf(CodeFailed, "the job postings could not be read")
|
|
}
|
|
r.DaysOpen = int(now.Sub(created).Hours() / 24)
|
|
r.Risk = riskFor(r.Strong, r.DaysOpen, r.Priority)
|
|
roles = append(roles, r)
|
|
}
|
|
if err := rows.Err(); err != nil {
|
|
return Failf(CodeFailed, "the job postings could not be read")
|
|
}
|
|
|
|
data := map[string]any{"openRoles": openRoles, "roles": roles}
|
|
if int64(len(roles)) < openRoles {
|
|
data["omittedRoles"] = openRoles - int64(len(roles))
|
|
}
|
|
if openRoles == 0 {
|
|
data["note"] = "No roles are open. This is a real answer, not a failure to look."
|
|
}
|
|
return OK(data)
|
|
},
|
|
}
|
|
}
|
|
|
|
// riskFor labels a role.
|
|
//
|
|
// Stated as a rule rather than left to the model, because it is a judgement the
|
|
// product makes consistently — two agents describing the same role differently
|
|
// is worse than a label that is sometimes debatable. The model is free to
|
|
// disagree in prose; the label is what makes lists sortable.
|
|
func riskFor(strong int64, daysOpen int, priority string) string {
|
|
switch {
|
|
case strong == 0 && daysOpen > 14:
|
|
return "high"
|
|
case strong == 0 || (priority == "urgent" && strong < 2):
|
|
return "medium"
|
|
default:
|
|
return "low"
|
|
}
|
|
}
|
|
|
|
/* ── Talent pool ────────────────────────────────────────────────────────── */
|
|
|
|
// TalentPool reports the bench: who is available and how ready.
|
|
func TalentPool(db repo.Querier) Tool {
|
|
return Tool{
|
|
Name: "talent_pool",
|
|
Description: "Read the talent pool: how many workers are on the bench, their " +
|
|
"readiness by score band, average experience, and the most job-ready. Use for " +
|
|
"questions about available talent, bench depth, and who could be placed.",
|
|
InputSchema: periodSchema("How many workers to list, most ready first. Defaults to 10."),
|
|
Effect: EffectRead,
|
|
MaxResultBytes: DefaultMaxResultBytes,
|
|
Handler: func(ctx context.Context, tc Context, inputs json.RawMessage) Result {
|
|
q, denied := authorize(tc, "worker-profiles")
|
|
if denied != nil {
|
|
return *denied
|
|
}
|
|
in, _, _, bad := decodePeriod(inputs)
|
|
if bad != nil {
|
|
return *bad
|
|
}
|
|
|
|
var (
|
|
total, ready, developing, early, unscored int64
|
|
experienced int64
|
|
avgExperience *float64
|
|
)
|
|
err := db.QueryRow(ctx, `
|
|
SELECT count(*),
|
|
count(*) FILTER (WHERE krow_score >= 80),
|
|
count(*) FILTER (WHERE krow_score >= 60 AND krow_score < 80),
|
|
count(*) FILTER (WHERE krow_score > 0 AND krow_score < 60),
|
|
count(*) FILTER (WHERE krow_score = 0),
|
|
count(*) FILTER (WHERE experience_years > 0),
|
|
avg(experience_years) FILTER (WHERE experience_years > 0)
|
|
FROM worker_profiles
|
|
WHERE `+q.clause(), q.args...,
|
|
).Scan(&total, &ready, &developing, &early, &unscored, &experienced, &avgExperience)
|
|
if err != nil {
|
|
return Failf(CodeFailed, "the worker profiles could not be read")
|
|
}
|
|
|
|
top, err := workersByScore(ctx, db, q, in.limitOr(10), "DESC")
|
|
if err != nil {
|
|
return Failf(CodeFailed, "the worker profiles could not be read")
|
|
}
|
|
|
|
data := map[string]any{
|
|
"workers": total,
|
|
"jobReady": ready,
|
|
"developing": developing,
|
|
"early": early,
|
|
"unscored": unscored,
|
|
"mostReady": top,
|
|
}
|
|
// Averaged over the workers who state any experience, so say so
|
|
// rather than letting an unstated 0 read as a first-year worker.
|
|
if avgExperience != nil {
|
|
data["averageExperienceYears"] = round1(*avgExperience)
|
|
data["averageExperienceBasis"] = experienced
|
|
}
|
|
if total == 0 {
|
|
data["note"] = "The talent pool is empty. This is a real answer, not a failure to look."
|
|
}
|
|
return OK(data)
|
|
},
|
|
}
|
|
}
|
|
|
|
/* ── Shared helpers ─────────────────────────────────────────────────────── */
|
|
|
|
type counted struct {
|
|
Value string `json:"value"`
|
|
Count int64 `json:"count"`
|
|
}
|
|
|
|
// groupCount is `GROUP BY one column` behind the caller's predicate.
|
|
//
|
|
// The column name is supplied by this package, never by input — every call site
|
|
// below passes a literal.
|
|
func groupCount(ctx context.Context, db repo.Querier, table, column string, q *query, limit int) ([]counted, error) {
|
|
args := append(append([]any{}, q.args...), limit)
|
|
rows, err := db.Query(ctx,
|
|
`SELECT `+column+`, count(*) FROM `+table+` WHERE `+q.clause()+
|
|
` GROUP BY 1 ORDER BY count(*) DESC, 1 ASC LIMIT $`+fmt.Sprint(len(args)), args...)
|
|
if err != nil {
|
|
return nil, err
|
|
}
|
|
defer rows.Close()
|
|
|
|
var out []counted
|
|
for rows.Next() {
|
|
var c counted
|
|
if err := rows.Scan(&c.Value, &c.Count); err != nil {
|
|
return nil, err
|
|
}
|
|
out = append(out, c)
|
|
}
|
|
return out, rows.Err()
|
|
}
|
|
|
|
func putAvg(data map[string]any, key string, v *float64) {
|
|
if v != nil {
|
|
data[key] = int(*v + 0.5)
|
|
}
|
|
}
|