Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / internal/modules/recruiting/requisition_service.go |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 2178e7298d0e5d9af83afa12402fd587f0ba6ed8 |
| Branch | feature/aioe-upgrade |
| Path or archive member | internal/modules/recruiting/requisition_service.go |
| Original SHA-256 | ca9ccd5ca2e613fafa6738db211aa26ffb560e82f5894bda86f7301c68850df3 |
| Git blob SHA | 81e472bd9384575b3e7f17ac4481e837105f1895 |
| Reading-copy SHA-256 | ca9ccd5ca2e613fafa6738db211aa26ffb560e82f5894bda86f7301c68850df3 |
Recovered source transcript
Download complete source text ↓882 lines · 31.2 KB
package recruiting
import (
"context"
"encoding/json"
"errors"
"strings"
"time"
"github.com/google/uuid"
"github.com/jackc/pgx/v5"
"github.com/shopspring/decimal"
"github.com/missionworkforce/missionmatch/internal/platform/audit"
"github.com/missionworkforce/missionmatch/internal/platform/tenant"
)
const (
// Migration 0047 permits 300 characters in a requisition title, which is wider than the 200 used for
// a person's job title, so it needs its own bound rather than borrowing maxTitle.
maxReqTitle = 300
maxReqDesc = 8000
maxReqNote = 2000
)
var reqPriorities = map[string]bool{"low": true, "normal": true, "high": true, "critical": true}
var reqEmployment = map[string]bool{"contract": true, "contract_to_hire": true, "direct_hire": true}
// Money and the hold accumulator are cast to text in SQL and parsed into decimal in Go, rather than
// scanned as a driver numeric. It removes the pgx numeric type registration from the equation entirely
// and the round trip is exact: Postgres renders numeric as its full decimal representation and
// decimal.NewFromString reads it back without going near a float64.
const reqSelect = `
r.id::text, r.client_id::text, cl.name, r.hiring_manager_id::text, cc.full_name,
r.owner_user_id::text, uo.full_name, r.requirement_id::text,
r.title, r.description, r.labor_category, r.openings, r.priority, r.employment_type, r.status,
r.required_level, r.required_poly, r.work_location, r.remote_allowed,
r.bill_rate_min::text, r.bill_rate_max::text, r.pay_rate_min::text, r.pay_rate_max::text,
r.salary_min::text, r.salary_max::text,
r.opened_at, r.target_fill_date, r.closed_at, r.held_seconds::text, r.held_since,
r.created_at, r.updated_at`
const reqFrom = `
FROM requisitions r
LEFT JOIN clients cl ON cl.organization_id = r.organization_id AND cl.id = r.client_id
LEFT JOIN client_contacts cc ON cc.organization_id = r.organization_id AND cc.id = r.hiring_manager_id
LEFT JOIN users uo ON uo.organization_id = r.organization_id AND uo.id = r.owner_user_id`
type scannable interface{ Scan(dest ...any) error }
// scanRequisition reads one row and computes the three clocks.
//
// The ages are derived here rather than in SQL on purpose. Doing it in Go means the arithmetic lives in
// reqAge, which is a pure function with no database, so every edge case is proved by a unit test instead
// of only being reachable through a query. The database supplies facts; the derivation is testable.
func scanRequisition(row scannable, now time.Time) (Requisition, error) {
var r Requisition
var clientID, clientName, hmID, hmName, ownerID, ownerName, reqID *string
var billMin, billMax, payMin, payMax, salMin, salMax *string
var heldSeconds string
var openedAt, createdAt, updatedAt time.Time
var targetFill, closedAt, heldSince *time.Time
if err := row.Scan(
&r.ID, &clientID, &clientName, &hmID, &hmName,
&ownerID, &ownerName, &reqID,
&r.Title, &r.Description, &r.LaborCategory, &r.Openings, &r.Priority, &r.EmploymentType, &r.Status,
&r.RequiredLevel, &r.RequiredPoly, &r.WorkLocation, &r.RemoteAllowed,
&billMin, &billMax, &payMin, &payMax, &salMin, &salMax,
&openedAt, &targetFill, &closedAt, &heldSeconds, &heldSince,
&createdAt, &updatedAt,
); err != nil {
return Requisition{}, err
}
r.ClientID, r.ClientName = str(clientID), str(clientName)
r.HiringManagerID, r.HiringManagerName = str(hmID), str(hmName)
r.OwnerUserID, r.OwnerName, r.RequirementID = str(ownerID), str(ownerName), str(reqID)
r.BillRateMin, r.BillRateMax = dec(billMin), dec(billMax)
r.PayRateMin, r.PayRateMax = dec(payMin), dec(payMax)
r.SalaryMin, r.SalaryMax = dec(salMin), dec(salMax)
r.OpenedAt, r.CreatedAt, r.UpdatedAt = ts(openedAt), ts(createdAt), ts(updatedAt)
r.TargetFillDate = dateStr(targetFill)
if closedAt != nil {
r.ClosedAt = ts(*closedAt)
}
if heldSince != nil {
r.OnHoldSince = ts(*heldSince)
}
held, err := decimal.NewFromString(heldSeconds)
if err != nil {
return Requisition{}, err
}
elapsed, adjusted := reqAge(openedAt, closedAt, held, heldSince, now)
r.AgeDays = days(elapsed)
r.WorkingDays = days(adjusted)
r.HeldDays = days(elapsed.Sub(adjusted))
if targetFill != nil {
// Whole days between today and the target, computed on dates rather than instants so a target of
// today reads as 0 regardless of the hour, and a passed target reads negative rather than as a
// fraction that rounds to zero and looks on time.
d := int(targetFill.UTC().Truncate(24*time.Hour).Sub(now.UTC().Truncate(24*time.Hour)).Hours() / 24)
r.DaysToTarget = &d
}
return r, nil
}
func str(p *string) string {
if p == nil {
return ""
}
return *p
}
func dec(p *string) *decimal.Decimal {
if p == nil {
return nil
}
d, err := decimal.NewFromString(*p)
if err != nil {
return nil
}
return &d
}
// ---------------------------------------------------------------------------
// Reads
// ---------------------------------------------------------------------------
// ListRequisitions returns job orders, filtered server side.
//
// Every filter is applied in SQL before the limit. Filtering a page after it has been fetched would show
// a recruiter three of their own orders out of a page of fifty and call it their desk, which is the same
// defect the client list had.
func (s *Service) ListRequisitions(ctx context.Context, clientID, status, ownerFilter, search string, limit, offset int) (RequisitionList, error) {
if s.db == nil {
return RequisitionList{}, ErrNoDatabase
}
if limit <= 0 || limit > 200 {
limit = 50
}
if offset < 0 {
offset = 0
}
var where []string
var args []any
add := func(clause string, v any) {
args = append(args, v)
where = append(where, strings.ReplaceAll(clause, "?", "$"+itoa(len(args))))
}
where = append(where, "r.deleted_at IS NULL")
if status != "" {
// "open" in the ordinary sense of the word means work in progress, which includes an order that
// is temporarily paused. A recruiter asking for open roles does not mean to exclude the ones on
// hold, and silently doing so would understate their desk.
switch status {
case "active":
where = append(where, "r.status IN ('open', 'on_hold')")
default:
if !reqStatuses[status] {
return RequisitionList{}, &ValidationError{Field: "status", Message: "is not a permitted status"}
}
add("r.status = ?", status)
}
}
if clientID != "" {
id, err := uuid.Parse(clientID)
if err != nil {
return RequisitionList{}, &ValidationError{Field: "client_id", Message: "must be a client id"}
}
add("r.client_id = ?", id)
}
if search != "" {
// Both placeholders become the same $N and bind to the one argument, which is what is wanted: the
// same search term is tested against two columns.
add("(r.title ILIKE ? OR r.labor_category ILIKE ?)", "%"+search+"%")
}
switch ownerFilter {
case "":
case "me":
actor := actorPtr(ctx)
if actor == nil {
return RequisitionList{}, tenant.ErrNoTenant
}
add("r.owner_user_id = ?", *actor)
case "unassigned":
where = append(where, "r.owner_user_id IS NULL")
default:
id, err := uuid.Parse(ownerFilter)
if err != nil {
// An unrecognised filter is refused rather than ignored. Silently returning everyone's orders
// when the caller asked for one person's is worse than an error, because it looks like an answer.
return RequisitionList{}, &ValidationError{Field: "owner", Message: "must be me, unassigned, or a user id"}
}
add("r.owner_user_id = ?", id)
}
cond := strings.Join(where, " AND ")
out := RequisitionList{Requisitions: []Requisition{}, Limit: limit, Offset: offset}
err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
if err := tx.QueryRow(ctx, `SELECT count(*)`+reqFrom+` WHERE `+cond, args...).Scan(&out.Total); err != nil {
return translatePG(err)
}
// Ordering is total. Priority then age puts the most urgent stale work first, and the id tiebreak
// makes paging deterministic: without it two rows with identical sort keys can swap between pages
// and a record is shown twice or skipped.
q := `SELECT` + reqSelect + reqFrom + ` WHERE ` + cond + `
ORDER BY CASE r.priority WHEN 'critical' THEN 0 WHEN 'high' THEN 1 WHEN 'normal' THEN 2 ELSE 3 END,
r.opened_at ASC, r.id ASC
LIMIT $` + itoa(len(args)+1) + ` OFFSET $` + itoa(len(args)+2)
rows, err := tx.Query(ctx, q, append(args, limit, offset)...)
if err != nil {
return translatePG(err)
}
defer rows.Close()
now := time.Now().UTC()
for rows.Next() {
r, err := scanRequisition(rows, now)
if err != nil {
return translatePG(err)
}
out.Requisitions = append(out.Requisitions, r)
}
return rows.Err()
})
if err != nil {
return RequisitionList{}, err
}
return out, nil
}
// GetRequisition returns one order with its status history.
func (s *Service) GetRequisition(ctx context.Context, id uuid.UUID) (RequisitionDetail, error) {
if s.db == nil {
return RequisitionDetail{}, ErrNoDatabase
}
var out RequisitionDetail
out.Events = []RequisitionEvent{}
err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
now := time.Now().UTC()
row := tx.QueryRow(ctx, `SELECT`+reqSelect+reqFrom+` WHERE r.id = $1 AND r.deleted_at IS NULL`, id)
r, err := scanRequisition(row, now)
if err != nil {
if errors.Is(err, pgx.ErrNoRows) {
return ErrNotFound
}
return translatePG(err)
}
out.Requisition = r
rows, err := tx.Query(ctx, `
SELECT e.id::text, e.from_status, e.to_status, e.seconds_in_from::text, e.note,
e.actor_user_id::text, NULLIF(e.actor_name, ''), e.occurred_at
FROM requisition_events e
WHERE e.requisition_id = $1
ORDER BY e.occurred_at ASC, e.id ASC`, id)
if err != nil {
return translatePG(err)
}
defer rows.Close()
for rows.Next() {
var ev RequisitionEvent
var from, secs, actor, actorName *string
var at time.Time
if err := rows.Scan(&ev.ID, &from, &ev.ToStatus, &secs, &ev.Note, &actor, &actorName, &at); err != nil {
return translatePG(err)
}
ev.FromStatus, ev.ActorUserID, ev.ActorUserName = str(from), str(actor), str(actorName)
ev.OccurredAt = ts(at)
if secs != nil {
if d, e := decimal.NewFromString(*secs); e == nil {
ev.DaysInFrom = days(d)
}
}
out.Events = append(out.Events, ev)
}
return rows.Err()
})
if err != nil {
return RequisitionDetail{}, err
}
return out, nil
}
// ---------------------------------------------------------------------------
// Validation
// ---------------------------------------------------------------------------
// money parses an optional monetary input. An empty string means absent, not zero: a rate that has not
// been agreed and a rate of zero dollars are different facts and conflating them would quietly turn an
// unknown into a giveaway.
func money(field, v string) (*decimal.Decimal, error) {
v = strings.TrimSpace(v)
if v == "" {
return nil, nil
}
d, err := decimal.NewFromString(v)
if err != nil {
return nil, &ValidationError{Field: field, Message: "must be a number"}
}
if d.IsNegative() {
return nil, &ValidationError{Field: field, Message: "cannot be negative"}
}
// numeric(12,4) and numeric(14,2) overflow rather than truncate, so the bound is checked here to
// produce a named field instead of a database error the caller cannot act on.
if d.GreaterThan(decimal.NewFromInt(99999999)) {
return nil, &ValidationError{Field: field, Message: "is larger than this field can hold"}
}
return &d, nil
}
// rangeOK refuses a range whose floor exceeds its ceiling. A partially filled range stays legal, which
// is normal while an order is still being scoped.
func rangeOK(field string, lo, hi *decimal.Decimal) error {
if lo != nil && hi != nil && lo.GreaterThan(*hi) {
return &ValidationError{Field: field + "_min", Message: "cannot be greater than " + field + "_max"}
}
return nil
}
func optDate(field, v string) (*time.Time, error) {
v = strings.TrimSpace(v)
if v == "" {
return nil, nil
}
t, err := time.ParseInLocation(dateLayout, v, time.UTC)
if err != nil {
return nil, &ValidationError{Field: field, Message: "must be a date as YYYY-MM-DD"}
}
return &t, nil
}
func optUUID(field, v string) (*uuid.UUID, error) {
v = strings.TrimSpace(v)
if v == "" {
return nil, nil
}
id, err := uuid.Parse(v)
if err != nil {
return nil, &ValidationError{Field: field, Message: "must be an id"}
}
return &id, nil
}
// ---------------------------------------------------------------------------
// Writes
// ---------------------------------------------------------------------------
// CreateRequisition opens a job order.
//
// The creating recruiter takes ownership. An order that lands in the pool unowned is an order nobody is
// accountable for, and the person who just typed it in is the obvious owner. Releasing it afterwards is
// one click.
func (s *Service) CreateRequisition(ctx context.Context, in CreateRequisitionInput) (Requisition, error) {
if s.db == nil {
return Requisition{}, ErrNoDatabase
}
title, err := clean("title", in.Title, maxReqTitle)
if err != nil {
return Requisition{}, err
}
if title == "" {
return Requisition{}, &ValidationError{Field: "title", Message: "is required"}
}
desc, err := clean("description", in.Description, maxReqDesc)
if err != nil {
return Requisition{}, err
}
if in.Openings == 0 {
in.Openings = 1
}
if in.Openings < 1 || in.Openings > 9999 {
return Requisition{}, &ValidationError{Field: "openings", Message: "must be between 1 and 9999"}
}
if in.Priority == "" {
in.Priority = "normal"
}
if !reqPriorities[in.Priority] {
return Requisition{}, &ValidationError{Field: "priority", Message: "is not a permitted value"}
}
if in.EmploymentType == "" {
in.EmploymentType = "contract"
}
if !reqEmployment[in.EmploymentType] {
return Requisition{}, &ValidationError{Field: "employment_type", Message: "is not a permitted value"}
}
// A new order may only start in draft or open. The other four describe an order that has already
// ended, and creating one directly in a terminal state would demand a closed_at that never had a
// corresponding open, corrupting every duration derived from it.
if in.Status == "" {
in.Status = reqOpen
}
if in.Status != reqDraft && in.Status != reqOpen {
return Requisition{}, &ValidationError{Field: "status", Message: "a new requisition starts as draft or open"}
}
if in.RequiredLevel == "" {
in.RequiredLevel = "none"
}
if in.RequiredPoly == "" {
in.RequiredPoly = "none"
}
clientID, err := optUUID("client_id", in.ClientID)
if err != nil {
return Requisition{}, err
}
hmID, err := optUUID("hiring_manager_id", in.HiringManagerID)
if err != nil {
return Requisition{}, err
}
if hmID != nil && clientID == nil {
return Requisition{}, &ValidationError{
Field: "hiring_manager_id",
Message: "a hiring manager belongs to a client, so name the client too",
}
}
reqID, err := optUUID("requirement_id", in.RequirementID)
if err != nil {
return Requisition{}, err
}
target, err := optDate("target_fill_date", in.TargetFillDate)
if err != nil {
return Requisition{}, err
}
if target != nil && target.Before(time.Now().UTC().Truncate(24*time.Hour)) {
return Requisition{}, &ValidationError{Field: "target_fill_date", Message: "cannot be in the past"}
}
rates, err := parseRates(in.BillRateMin, in.BillRateMax, in.PayRateMin, in.PayRateMax, in.SalaryMin, in.SalaryMax)
if err != nil {
return Requisition{}, err
}
actor := actorPtr(ctx)
if actor == nil {
return Requisition{}, tenant.ErrNoTenant
}
// Scanned as a uuid.UUID, not as text and parsed back. See CreateApplication for the reasoning: a
// database returned id must never be re parsed with uuid.MustParse in a request path, because the
// only way that parse can fail is by panicking on a caller who did nothing wrong.
var newID uuid.UUID
err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
err := tx.QueryRow(ctx, `
INSERT INTO requisitions (
organization_id, client_id, hiring_manager_id, requirement_id, owner_user_id, created_by,
title, description, labor_category, openings, priority, employment_type, status,
required_level, required_poly, work_location, remote_allowed,
bill_rate_min, bill_rate_max, pay_rate_min, pay_rate_max, salary_min, salary_max,
target_fill_date)
VALUES (
NULLIF(current_setting('app.current_org_id', true), '')::uuid,
$1, $2, $3, $4, $4,
$5, $6, $7, $8, $9, $10, $11,
$12, $13, $14, $15,
$16, $17, $18, $19, $20, $21,
$22)
RETURNING id`,
clientID, hmID, reqID, actor,
title, desc, strings.TrimSpace(in.LaborCategory), in.Openings, in.Priority, in.EmploymentType, in.Status,
in.RequiredLevel, in.RequiredPoly, strings.TrimSpace(in.WorkLocation), in.RemoteAllowed,
rates[0], rates[1], rates[2], rates[3], rates[4], rates[5],
target).Scan(&newID)
if err != nil {
return translatePG(err)
}
// The opening event. Every later duration is measured from a predecessor, so the chain needs a
// first link or the first real transition would have nothing to close out.
if err := insertReqEvent(ctx, tx, newID, "", in.Status, nil, "opened", actor); err != nil {
return err
}
nv, _ := json.Marshal(map[string]any{"id": newID.String(), "title": title, "status": in.Status})
return s.audit.OnTx(ctx, tx, audit.Event{
Action: "requisition.created", ActorUserID: actor,
EntityType: "requisition", EntityID: newID.String(), NewValues: nv,
})
})
if err != nil {
return Requisition{}, err
}
// Re read so the response carries the resolved client and owner names and the computed clocks, and
// therefore has exactly the same shape a list or a get returns.
return s.requisitionRow(ctx, newID)
}
func parseRates(bMin, bMax, pMin, pMax, sMin, sMax string) ([6]*decimal.Decimal, error) {
var out [6]*decimal.Decimal
fields := [6]string{"bill_rate_min", "bill_rate_max", "pay_rate_min", "pay_rate_max", "salary_min", "salary_max"}
vals := [6]string{bMin, bMax, pMin, pMax, sMin, sMax}
for i := range vals {
d, err := money(fields[i], vals[i])
if err != nil {
return out, err
}
out[i] = d
}
if err := rangeOK("bill_rate", out[0], out[1]); err != nil {
return out, err
}
if err := rangeOK("pay_rate", out[2], out[3]); err != nil {
return out, err
}
if err := rangeOK("salary", out[4], out[5]); err != nil {
return out, err
}
return out, nil
}
// UpdateRequisition applies a partial update under optimistic concurrency and the ownership lock.
func (s *Service) UpdateRequisition(ctx context.Context, id uuid.UUID, in UpdateRequisitionInput) (Requisition, error) {
if s.db == nil {
return Requisition{}, ErrNoDatabase
}
expected, err := parseToken(in.ExpectedUpdatedAt)
if err != nil {
return Requisition{}, err
}
if in.Title != nil {
t, e := clean("title", *in.Title, maxReqTitle)
if e != nil {
return Requisition{}, e
}
if t == "" {
return Requisition{}, &ValidationError{Field: "title", Message: "is required"}
}
in.Title = &t
}
if in.Openings != nil && (*in.Openings < 1 || *in.Openings > 9999) {
return Requisition{}, &ValidationError{Field: "openings", Message: "must be between 1 and 9999"}
}
if in.Priority != nil && !reqPriorities[*in.Priority] {
return Requisition{}, &ValidationError{Field: "priority", Message: "is not a permitted value"}
}
if in.EmploymentType != nil && !reqEmployment[*in.EmploymentType] {
return Requisition{}, &ValidationError{Field: "employment_type", Message: "is not a permitted value"}
}
var clientID, hmID *uuid.UUID
if in.ClientID != nil {
if clientID, err = optUUID("client_id", *in.ClientID); err != nil {
return Requisition{}, err
}
}
if in.HiringManagerID != nil {
if hmID, err = optUUID("hiring_manager_id", *in.HiringManagerID); err != nil {
return Requisition{}, err
}
}
var target *time.Time
if in.TargetFillDate != nil {
if target, err = optDate("target_fill_date", *in.TargetFillDate); err != nil {
return Requisition{}, err
}
}
// Each rate is validated individually here. A range where only one end is being changed can only be
// checked against the stored other end, so the database CHECK is the authority and translatePG turns
// its failure into a named field.
rate := map[string]*decimal.Decimal{}
for field, v := range map[string]*string{
"bill_rate_min": in.BillRateMin, "bill_rate_max": in.BillRateMax,
"pay_rate_min": in.PayRateMin, "pay_rate_max": in.PayRateMax,
"salary_min": in.SalaryMin, "salary_max": in.SalaryMax,
} {
if v == nil {
continue
}
d, e := money(field, *v)
if e != nil {
return Requisition{}, e
}
rate[field] = d
}
var out Requisition
err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
claim, err := assertCanEditReq(ctx, tx, id)
if err != nil {
return err
}
if claim {
if err := s.setReqOwner(ctx, tx, id, actorPtr(ctx), "requisition.claimed"); err != nil {
return err
}
}
// COALESCE handles present and absent uniformly for the scalar fields. The nullable reference and
// money fields cannot use it, because for those an explicit null is a meaningful instruction to
// clear the value, so each carries a separate boolean saying whether it was supplied at all.
tag, err := tx.Exec(ctx, `
UPDATE requisitions SET
client_id = CASE WHEN $2 THEN $3 ELSE client_id END,
hiring_manager_id = CASE WHEN $4 THEN $5 ELSE hiring_manager_id END,
title = COALESCE($6, title),
description = COALESCE($7, description),
labor_category = COALESCE($8, labor_category),
openings = COALESCE($9, openings),
priority = COALESCE($10, priority),
employment_type = COALESCE($11, employment_type),
required_level = COALESCE($12, required_level),
required_poly = COALESCE($13, required_poly),
work_location = COALESCE($14, work_location),
remote_allowed = COALESCE($15, remote_allowed),
bill_rate_min = CASE WHEN $16 THEN $17 ELSE bill_rate_min END,
bill_rate_max = CASE WHEN $18 THEN $19 ELSE bill_rate_max END,
pay_rate_min = CASE WHEN $20 THEN $21 ELSE pay_rate_min END,
pay_rate_max = CASE WHEN $22 THEN $23 ELSE pay_rate_max END,
salary_min = CASE WHEN $24 THEN $25 ELSE salary_min END,
salary_max = CASE WHEN $26 THEN $27 ELSE salary_max END,
target_fill_date = CASE WHEN $28 THEN $29 ELSE target_fill_date END,
updated_at = now()
WHERE id = $1 AND deleted_at IS NULL AND ($30::timestamptz IS NULL OR updated_at = $30)`,
id,
in.ClientID != nil, clientID,
in.HiringManagerID != nil, hmID,
in.Title, in.Description, in.LaborCategory, in.Openings, in.Priority, in.EmploymentType,
in.RequiredLevel, in.RequiredPoly, in.WorkLocation, in.RemoteAllowed,
in.BillRateMin != nil, rate["bill_rate_min"],
in.BillRateMax != nil, rate["bill_rate_max"],
in.PayRateMin != nil, rate["pay_rate_min"],
in.PayRateMax != nil, rate["pay_rate_max"],
in.SalaryMin != nil, rate["salary_min"],
in.SalaryMax != nil, rate["salary_max"],
in.TargetFillDate != nil, target,
tokenOrNil(expected, claim))
if err != nil {
return translatePG(err)
}
if tag.RowsAffected() == 0 {
var exists bool
if e := tx.QueryRow(ctx, `SELECT true FROM requisitions WHERE id = $1 AND deleted_at IS NULL`, id).Scan(&exists); e != nil {
return ErrNotFound
}
return ErrConflict
}
nv, _ := json.Marshal(in)
return s.audit.OnTx(ctx, tx, audit.Event{
Action: "requisition.updated", ActorUserID: actorPtr(ctx),
EntityType: "requisition", EntityID: id.String(), NewValues: nv,
})
})
if err != nil {
return Requisition{}, err
}
out, err = s.requisitionRow(ctx, id)
return out, err
}
// ChangeStatus moves a job order through its lifecycle.
//
// This is the only path that writes status, and it does five things in one transaction: locks the row,
// consults the transition graph, moves the clocks the transition implies, records the event with the
// time spent in the state being left, and audits it. Splitting any of that out would allow a crash to
// leave the status changed with the clock unmoved, which would corrupt every duration derived from it
// with no way to detect it afterwards.
func (s *Service) ChangeStatus(ctx context.Context, id uuid.UUID, in ChangeStatusInput) (Requisition, error) {
if s.db == nil {
return Requisition{}, ErrNoDatabase
}
expected, err := parseToken(in.ExpectedUpdatedAt)
if err != nil {
return Requisition{}, err
}
note, err := clean("note", in.Note, maxReqNote)
if err != nil {
return Requisition{}, err
}
err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
claim, err := assertCanEditReq(ctx, tx, id)
if err != nil {
return err
}
if claim {
if err := s.setReqOwner(ctx, tx, id, actorPtr(ctx), "requisition.claimed"); err != nil {
return err
}
}
// The row is already locked FOR UPDATE by assertCanEditReq, so the status read here cannot be
// stale and two concurrent transitions are serialized rather than both computing from the same
// starting state.
var from string
var updatedAt time.Time
if err := tx.QueryRow(ctx,
`SELECT status, updated_at FROM requisitions WHERE id = $1 AND deleted_at IS NULL`, id).
Scan(&from, &updatedAt); err != nil {
if errors.Is(err, pgx.ErrNoRows) {
return ErrNotFound
}
return translatePG(err)
}
if !claim && !expected.IsZero() && !updatedAt.Equal(expected) {
return ErrConflict
}
effect, err := transitionFor(from, in.Status)
if err != nil {
return err
}
if effect.NoOp {
return nil
}
// One statement moves status and all three clocks together, so no intermediate state exists in
// which the database constraints could be violated. seconds_in_from is captured from the same
// now() the update uses, so the event and the row cannot disagree about when this happened.
var secondsInFrom string
err = tx.QueryRow(ctx, `
UPDATE requisitions SET
status = $2,
closed_at = CASE WHEN $3 THEN now() WHEN $4 THEN NULL ELSE closed_at END,
held_seconds = CASE WHEN $5 AND held_since IS NOT NULL
THEN held_seconds + EXTRACT(EPOCH FROM (now() - held_since))
ELSE held_seconds END,
held_since = CASE WHEN $6 THEN now() WHEN $5 THEN NULL ELSE held_since END,
updated_at = now()
WHERE id = $1 AND deleted_at IS NULL
RETURNING EXTRACT(EPOCH FROM (now() - COALESCE(
(SELECT max(occurred_at) FROM requisition_events WHERE requisition_id = $1), created_at)))::text`,
id, in.Status, effect.SetClosedAt, effect.ClearClosedAt, effect.SettleHold, effect.StartHold).
Scan(&secondsInFrom)
if err != nil {
return translatePG(err)
}
if err := insertReqEvent(ctx, tx, id, from, in.Status, &secondsInFrom, note, actorPtr(ctx)); err != nil {
return err
}
nv, _ := json.Marshal(map[string]string{"from": from, "to": in.Status, "note": note})
return s.audit.OnTx(ctx, tx, audit.Event{
Action: "requisition.status_changed", ActorUserID: actorPtr(ctx),
EntityType: "requisition", EntityID: id.String(), NewValues: nv,
})
})
if err != nil {
return Requisition{}, err
}
return s.requisitionRow(ctx, id)
}
// insertReqEvent appends to the history. from empty means this is the first link in the chain.
func insertReqEvent(ctx context.Context, tx pgx.Tx, id uuid.UUID, from, to string, seconds *string, note string, actor *uuid.UUID) error {
var fromArg any
if from != "" {
fromArg = from
}
var secArg any
if from != "" && seconds != nil {
secArg = *seconds
}
// actor_name is resolved and stored at write time, not joined at read time. The foreign key nulls
// actor_user_id when a recruiter is deleted, so a joined name would turn their entire history into
// "unknown" the day they leave. What the history should record is who acted then.
_, err := tx.Exec(ctx, `
INSERT INTO requisition_events
(organization_id, requisition_id, from_status, to_status, seconds_in_from, note,
actor_user_id, actor_name)
VALUES (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1, $2, $3, $4::numeric, $5, $6,
COALESCE((SELECT full_name FROM users WHERE id = $6), ''))`,
id, fromArg, to, secArg, note, actor)
if err != nil {
return translatePG(err)
}
return nil
}
// requisitionRow re reads a single order so every write returns the same shape a read does.
func (s *Service) requisitionRow(ctx context.Context, id uuid.UUID) (Requisition, error) {
var out Requisition
err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
row := tx.QueryRow(ctx, `SELECT`+reqSelect+reqFrom+` WHERE r.id = $1 AND r.deleted_at IS NULL`, id)
r, err := scanRequisition(row, time.Now().UTC())
if err != nil {
if errors.Is(err, pgx.ErrNoRows) {
return ErrNotFound
}
return translatePG(err)
}
out = r
return nil
})
return out, err
}
// ---------------------------------------------------------------------------
// Ownership, the same rule clients use
// ---------------------------------------------------------------------------
func loadReqOwner(ctx context.Context, tx pgx.Tx, id uuid.UUID) (owner, error) {
o := owner{entity: "job order"}
var name *string
err := tx.QueryRow(ctx, `
SELECT r.owner_user_id, u.full_name
FROM requisitions r
LEFT JOIN users u ON u.organization_id = r.organization_id AND u.id = r.owner_user_id
WHERE r.id = $1 AND r.deleted_at IS NULL
FOR UPDATE OF r`, id).Scan(&o.userID, &name)
if err != nil {
if errors.Is(err, pgx.ErrNoRows) {
return owner{}, ErrNotFound
}
return owner{}, translatePG(err)
}
if name != nil {
o.name = *name
}
return o, nil
}
func assertCanEditReq(ctx context.Context, tx pgx.Tx, id uuid.UUID) (bool, error) {
o, err := loadReqOwner(ctx, tx, id)
if err != nil {
return false, err
}
actor := actorPtr(ctx)
if actor == nil {
return false, tenant.ErrNoTenant
}
return ownershipDecision(o, actor, canOverride(ctx))
}
func (s *Service) setReqOwner(ctx context.Context, tx pgx.Tx, id uuid.UUID, to *uuid.UUID, action string) error {
if _, err := tx.Exec(ctx,
`UPDATE requisitions SET owner_user_id = $2, updated_at = now() WHERE id = $1`, id, to); err != nil {
return translatePG(err)
}
return s.auditOwnership(ctx, tx, "requisition", id, to, action)
}
// ClaimRequisition assigns an unclaimed job order to the calling recruiter.
func (s *Service) ClaimRequisition(ctx context.Context, id uuid.UUID) (Requisition, error) {
if s.db == nil {
return Requisition{}, ErrNoDatabase
}
actor := actorPtr(ctx)
if actor == nil {
return Requisition{}, tenant.ErrNoTenant
}
err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
o, err := loadReqOwner(ctx, tx, id)
if err != nil {
return err
}
if o.userID != nil {
if *o.userID == *actor {
return nil // already held by the caller
}
if !canOverride(ctx) {
return &ErrNotOwner{OwnerUserID: o.userID.String(), OwnerName: o.name, Entity: o.entity}
}
}
return s.setReqOwner(ctx, tx, id, actor, "requisition.claimed")
})
if err != nil {
return Requisition{}, err
}
return s.requisitionRow(ctx, id)
}
// ReleaseRequisition returns a job order to the unassigned pool.
func (s *Service) ReleaseRequisition(ctx context.Context, id uuid.UUID) (Requisition, error) {
if s.db == nil {
return Requisition{}, ErrNoDatabase
}
err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
if _, err := assertCanEditReq(ctx, tx, id); err != nil {
return err
}
return s.setReqOwner(ctx, tx, id, nil, "requisition.released")
})
if err != nil {
return Requisition{}, err
}
return s.requisitionRow(ctx, id)
}