Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / internal/modules/recruiting/manager.go |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | internal/modules/recruiting/manager.go |
| Original SHA-256 | 5dfcadd0c0fec7759148b8debf6db5ca183f50049323d0de5d2e0b1876dbabc4 |
| Git blob SHA | 52685efd170f9aa580ea6609f38ae529dc7bde80 |
| Reading-copy SHA-256 | 5dfcadd0c0fec7759148b8debf6db5ca183f50049323d0de5d2e0b1876dbabc4 |
Recovered source transcript
Download complete source text ↓344 lines · 13.0 KB
package recruiting
import (
"context"
"fmt"
"strings"
"time"
"github.com/google/uuid"
"github.com/jackc/pgx/v5"
"github.com/shopspring/decimal"
)
// THE MANAGER'S VIEW, WHICH DID NOT EXIST.
//
// A manager running six recruiters opened this product and got the recruiter dashboard. Identical screen,
// identical numbers, org wide, one window, no comparison, nothing they could act on. Three things were
// missing and all three are reads over data already held:
//
// SCOPE teams shipped and nothing read them, so a manager could ask how the COMPANY was doing and
// never how their own desk was doing.
// COMPARISON every figure was a single window. "Fill rate 62 percent" is not a management fact.
// "62 percent, down from 71" is, and the difference is one extra query.
// CAPACITY the product knows how many open orders each person holds and never said it. The 2026
// benchmark is fourteen requisitions per recruiter, and practitioners put the point where the
// work changes shape at twenty to thirty: above that, intake meetings get skipped, sourcing
// stops in favour of reviewing inbound, and screening goes shallow.
//
// NOTHING HERE IS A NEW TABLE. Capacity is measured against the published benchmark and is NAMED as a
// benchmark rather than a target, because a target belongs with a goals model and there is not one yet.
// Shipping a target without goals behind it would be inventing a number and calling it management.
// Load bands, from the published benchmarks rather than invented. They are the reason the number is worth
// showing at all: a count with no band is trivia.
const (
// LoadBenchmark is the 2026 average requisitions per recruiter.
LoadBenchmark = 14
// LoadStrained is where practitioners say the work changes shape rather than merely gets busier.
LoadStrained = 20
// LoadOverloaded is the upper end of that same range.
LoadOverloaded = 30
)
// RecruiterLoad is one person's open workload.
type RecruiterLoad struct {
UserID string `json:"user_id,omitempty"`
Name string `json:"name"`
// OpenRequisitions is what the band is computed from: orders still open and owned by this person.
OpenRequisitions int `json:"open_requisitions"`
// ActiveApplications is the pipeline underneath those orders, which is what the load actually feels like.
ActiveApplications int `json:"active_applications"`
// Band is one of ok, watch, strained, overloaded. A count without a band is trivia; the band is the part
// a manager acts on.
Band string `json:"band"`
}
func loadBand(open int) string {
switch {
case open >= LoadOverloaded:
return "overloaded"
case open >= LoadStrained:
return "strained"
case open > LoadBenchmark:
return "watch"
default:
return "ok"
}
}
// Comparison is one figure against the same figure in the window immediately before it.
//
// Direction is computed here rather than in the browser so every screen agrees about what "better" means:
// a rising fill rate is better and a rising time to fill is worse, and a component deciding that for itself
// gets it backwards on half the metrics.
type Comparison struct {
Now string `json:"now"`
Previous string `json:"previous"`
// Direction is up, down or flat. It describes the NUMBER, never whether the number is good.
Direction string `json:"direction"`
// Better says whether the movement is in the direction this organization wants, which is metric
// specific and is the thing a screen actually colours on.
Better string `json:"better,omitempty"` // yes | no | flat
}
// ManagerView is what the manager surface adds on top of the existing analytics report.
type ManagerView struct {
// Scope names WHO this answer is about, echoed back so a filtered screen can say so rather than looking
// like the whole company had a quiet month.
Scope string `json:"scope"`
ScopeLabel string `json:"scope_label"`
PeriodDays int `json:"period_days"`
PreviousEnd string `json:"previous_end"`
Placements Comparison `json:"placements"`
Submissions Comparison `json:"submissions"`
FillRate Comparison `json:"fill_rate"`
TimeToFill Comparison `json:"time_to_fill_days"`
Capacity []RecruiterLoad `json:"capacity"`
// Overloaded is the count a manager opens this screen to find, computed here so two panels cannot
// disagree about how many people are underwater.
Overloaded int `json:"overloaded"`
}
// resolveScope turns the caller's request into a set of owner ids and a label.
//
// AN EMPTY SCOPE MEANS THE WHOLE ORGANIZATION, which is what an owner or an administrator wants and is the
// behaviour that existed before this. A team narrows to its members. A user narrows to one person, which is
// the one to one preparation view.
func (s *Service) resolveScope(ctx context.Context, tx pgx.Tx, teamID, owner string) ([]uuid.UUID, string, string, error) {
teamID, owner = strings.TrimSpace(teamID), strings.TrimSpace(owner)
if owner != "" {
id, err := uuid.Parse(owner)
if err != nil {
return nil, "", "", &ValidationError{Field: "owner", Message: "is not an identifier"}
}
var name string
if e := tx.QueryRow(ctx, `SELECT full_name FROM users WHERE id = $1`, id).Scan(&name); e != nil {
return nil, "", "", ErrNotFound
}
return []uuid.UUID{id}, "user", name, nil
}
if teamID != "" {
id, err := uuid.Parse(teamID)
if err != nil {
return nil, "", "", &ValidationError{Field: "team_id", Message: "is not an identifier"}
}
var name string
if e := tx.QueryRow(ctx, `SELECT name FROM teams WHERE id = $1`, id).Scan(&name); e != nil {
return nil, "", "", ErrNotFound
}
rows, e := tx.Query(ctx, `SELECT user_id FROM team_members WHERE team_id = $1`, id)
if e != nil {
return nil, "", "", e
}
defer rows.Close()
var ids []uuid.UUID
for rows.Next() {
var u uuid.UUID
if e := rows.Scan(&u); e != nil {
return nil, "", "", e
}
ids = append(ids, u)
}
if e := rows.Err(); e != nil {
return nil, "", "", e
}
// AN EMPTY TEAM IS NOT THE WHOLE ORGANIZATION. Returning no filter here would silently widen a
// manager's view from their own desk to everybody, which is the opposite of what they asked for and
// is the kind of error nobody notices because the screen fills with numbers.
if len(ids) == 0 {
return []uuid.UUID{uuid.Nil}, "team", name + ", nobody on it yet", nil
}
return ids, "team", name, nil
}
return nil, "organization", "the whole organization", nil
}
// ManagerSummary builds the manager view for one window and scope.
func (s *Service) ManagerSummary(ctx context.Context, sinceRaw, teamID, owner string) (ManagerView, error) {
var out ManagerView
if s.db == nil {
return out, ErrNoDatabase
}
since, err := optTime("since", sinceRaw)
if err != nil {
return out, err
}
if since == nil {
t := time.Now().UTC().AddDate(0, 0, -30)
since = &t
}
now := time.Now().UTC()
days := int(now.Sub(*since).Hours() / 24)
if days < 1 {
days = 1
}
// THE PREVIOUS WINDOW IS THE SAME LENGTH, IMMEDIATELY BEFORE. Comparing a thirty day month against a
// ninety day quarter would produce a decline every time and mean nothing.
prevStart := since.AddDate(0, 0, -days)
out.PeriodDays = days
out.PreviousEnd = since.UTC().Format(time.RFC3339)
err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
ids, scope, label, e := s.resolveScope(ctx, tx, teamID, owner)
if e != nil {
return e
}
out.Scope, out.ScopeLabel = scope, label
nowP, nowS, nowF, nowT, e := s.windowFigures(ctx, tx, *since, now, ids)
if e != nil {
return e
}
prvP, prvS, prvF, prvT, e := s.windowFigures(ctx, tx, prevStart, *since, ids)
if e != nil {
return e
}
out.Placements = compare(nowP, prvP, true)
out.Submissions = compare(nowS, prvS, true)
out.FillRate = compare(nowF, prvF, true)
// TIME TO FILL IS THE ONE WHERE DOWN IS BETTER, which is exactly why Better is computed on the server
// rather than left to a component to guess.
out.TimeToFill = compare(nowT, prvT, false)
caps, e := s.capacity(ctx, tx, ids)
if e != nil {
return e
}
out.Capacity = caps
for _, c := range caps {
if c.Band == "overloaded" || c.Band == "strained" {
out.Overloaded++
}
}
return nil
})
return out, err
}
// windowFigures returns placements, submissions, fill rate and median days to fill for one window and owner
// set, in ONE query, so the four figures cannot describe four slightly different populations.
func (s *Service) windowFigures(ctx context.Context, tx pgx.Tx, from, to time.Time, owners []uuid.UUID) (
placements, submissions, fillRate, ttf decimal.Decimal, err error) {
// A nil owner slice means no filter. The ANY comparison with a NULL array is never true, so the filter is
// applied only when there is one, and the same SQL serves both.
var arr any
if owners != nil {
arr = owners
}
row := tx.QueryRow(ctx, `
WITH scoped_apps AS (
SELECT a.id, a.owner_user_id
FROM applications a
WHERE ($3::uuid[] IS NULL OR a.owner_user_id = ANY($3))
), subs AS (
SELECT count(DISTINCT e.application_id)::int AS n
FROM application_stage_events e
JOIN scoped_apps a ON a.id = e.application_id
WHERE e.to_stage = 'submitted' AND e.occurred_at >= $1 AND e.occurred_at < $2
), plcs AS (
SELECT count(DISTINCT p.id)::int AS n
FROM placements p
JOIN scoped_apps a ON a.id = p.application_id
WHERE p.created_at >= $1 AND p.created_at < $2
), reqs AS (
SELECT count(*) FILTER (WHERE r.status = 'filled')::int AS filled,
count(*) FILTER (WHERE r.status IN ('filled','closed','cancelled'))::int AS resolved,
coalesce(percentile_cont(0.5) WITHIN GROUP (
ORDER BY EXTRACT(EPOCH FROM (r.closed_at - r.opened_at)) / 86400.0
) FILTER (WHERE r.status = 'filled' AND r.closed_at IS NOT NULL), 0)::numeric AS med_days
FROM requisitions r
WHERE r.deleted_at IS NULL AND r.opened_at >= $1 AND r.opened_at < $2
AND ($3::uuid[] IS NULL OR r.owner_user_id = ANY($3))
)
SELECT plcs.n, subs.n,
CASE WHEN reqs.resolved > 0
THEN round(reqs.filled::numeric * 100 / reqs.resolved, 2) ELSE 0 END,
round(reqs.med_days, 1)
FROM plcs, subs, reqs`, from, to, arr)
var p, sub int
var fr, md decimal.Decimal
if e := row.Scan(&p, &sub, &fr, &md); e != nil {
return placements, submissions, fillRate, ttf, fmt.Errorf("recruiting: manager window: %w", e)
}
return decimal.NewFromInt(int64(p)), decimal.NewFromInt(int64(sub)), fr, md, nil
}
// capacity returns the open load per person.
func (s *Service) capacity(ctx context.Context, tx pgx.Tx, owners []uuid.UUID) ([]RecruiterLoad, error) {
var arr any
if owners != nil {
arr = owners
}
// THE PIPELINE COUNT IS AGGREGATED ONCE, NOT PER ROW.
//
// The first version put it in a correlated subquery, which the planner ran once per REQUISITION rather
// than once per owner: a sequential scan of applications with 996 loops on a small staging dataset, and
// the loop count grows with the order book rather than with the headcount. Grouping first and joining
// turns it into one pass over each table.
rows, err := tx.Query(ctx, `
WITH pipeline AS (
SELECT a.owner_user_id AS uid, count(*)::int AS n
FROM applications a
WHERE a.stage NOT IN ('placed','rejected','withdrawn')
GROUP BY a.owner_user_id
), openreqs AS (
SELECT r.owner_user_id AS uid, count(DISTINCT r.id)::int AS n
FROM requisitions r
WHERE r.deleted_at IS NULL AND r.status = 'open'
AND ($1::uuid[] IS NULL OR r.owner_user_id = ANY($1))
GROUP BY r.owner_user_id
)
SELECT coalesce(u.id::text, ''), coalesce(u.full_name, 'Unassigned'),
openreqs.n, coalesce(pipeline.n, 0)
FROM openreqs
LEFT JOIN pipeline ON pipeline.uid IS NOT DISTINCT FROM openreqs.uid
LEFT JOIN users u ON u.id = openreqs.uid
ORDER BY openreqs.n DESC, 2 ASC`, arr)
if err != nil {
return nil, fmt.Errorf("recruiting: capacity: %w", err)
}
defer rows.Close()
out := make([]RecruiterLoad, 0, 16)
for rows.Next() {
var l RecruiterLoad
if e := rows.Scan(&l.UserID, &l.Name, &l.OpenRequisitions, &l.ActiveApplications); e != nil {
return nil, e
}
l.Band = loadBand(l.OpenRequisitions)
out = append(out, l)
}
return out, rows.Err()
}
// compare builds one figure against its predecessor.
//
// A PREVIOUS OF ZERO IS NOT A HUNDRED PERCENT RISE. A desk that placed nobody last month and two people this
// month has not improved by infinity, and rendering a percentage there is the most common way a comparison
// becomes a joke. The direction is still reported, because up from nothing is genuinely up.
func compare(now, prev decimal.Decimal, upIsBetter bool) Comparison {
c := Comparison{Now: now.String(), Previous: prev.String()}
switch {
case now.Equal(prev):
c.Direction, c.Better = "flat", "flat"
case now.GreaterThan(prev):
c.Direction = "up"
c.Better = boolWord(upIsBetter)
default:
c.Direction = "down"
c.Better = boolWord(!upIsBetter)
}
return c
}
func boolWord(b bool) string {
if b {
return "yes"
}
return "no"
}