Janady13/missionmatch / internal/modules/recruiting/placement_service.go

Source identity

Field	Recorded value
Source type	GitHub source
Original record	Janady13/missionmatch / internal/modules/recruiting/placement_service.go
Date	Not specified in source
Repository	Janady13/missionmatch
Revision	2178e7298d0e5d9af83afa12402fd587f0ba6ed8
Branch	feature/aioe-upgrade
Path or archive member	internal/modules/recruiting/placement_service.go
Original SHA-256	42d79c81643c493b2f90d606b3403417c08731bdbeb65a07f964dc9c58b215ab
Git blob SHA	ced7b5ae45ccafdc29553a36c27ec15063f62b40
Reading-copy SHA-256	42d79c81643c493b2f90d606b3403417c08731bdbeb65a07f964dc9c58b215ab
Original source ↗
Recovered source transcript

Download complete source text ↓814 lines · 30.1 KB
package recruiting

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

	"github.com/google/uuid"
	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/shopspring/decimal"

	"github.com/missionworkforce/missionmatch/internal/platform/audit"
	"github.com/missionworkforce/missionmatch/internal/platform/tenant"
)

// ---------------------------------------------------------------------------
// Interviews
// ---------------------------------------------------------------------------

const ivSelect = `iv.id::text, iv.application_id::text, c.full_name, iv.round, iv.kind,
	iv.scheduled_at, iv.completed_at, iv.outcome, iv.interviewer, iv.notes, iv.created_at, iv.updated_at`

const ivFrom = `FROM interviews iv
	JOIN applications a ON a.organization_id = iv.organization_id AND a.id = iv.application_id
	JOIN candidates   c ON c.organization_id = a.organization_id  AND c.id = a.candidate_id`

func scanInterview(row scannable) (Interview, error) {
	var v Interview
	var name *string
	var sched, done *time.Time
	var created, updated time.Time
	if err := row.Scan(&v.ID, &v.ApplicationID, &name, &v.Round, &v.Kind,
		&sched, &done, &v.Outcome, &v.Interviewer, &v.Notes, &created, &updated); err != nil {
		return Interview{}, err
	}
	v.CandidateName = str(name)
	if sched != nil {
		v.ScheduledAt = ts(*sched)
	}
	if done != nil {
		v.CompletedAt = ts(*done)
	}
	v.CreatedAt, v.UpdatedAt = ts(created), ts(updated)
	return v, nil
}

// ScheduleInterview books a round against an application.
//
// The round number is unique per application in the database, so two recruiters booking "round 2" at the
// same instant produce one row and one clean conflict rather than two rounds both called the second.
func (s *Service) ScheduleInterview(ctx context.Context, appID uuid.UUID, in CreateInterviewInput) (Interview, error) {
	if s.db == nil {
		return Interview{}, ErrNoDatabase
	}
	if in.Round == 0 {
		in.Round = 1
	}
	if !interviewRoundOK(in.Round) {
		return Interview{}, &ValidationError{Field: "round", Message: "must be between 1 and 20"}
	}
	if in.Kind == "" {
		in.Kind = "client"
	}
	if !interviewKinds[in.Kind] {
		return Interview{}, &ValidationError{Field: "kind", Message: "is not a permitted interview type"}
	}
	sched, err := optTime("scheduled_at", in.ScheduledAt)
	if err != nil {
		return Interview{}, err
	}
	who, err := clean("interviewer", in.Interviewer, maxName)
	if err != nil {
		return Interview{}, err
	}
	notes, err := clean("notes", in.Notes, maxReqDesc)
	if err != nil {
		return Interview{}, err
	}
	actor := actorPtr(ctx)

	// Scanned as a uuid.UUID rather than as text and parsed back with uuid.MustParse. See
	// CreateApplication for the reasoning: a database returned id has no legitimate failure mode, so the
	// only thing that parse could ever do is panic in a request path.
	var id uuid.UUID
	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		e := tx.QueryRow(ctx, `
			INSERT INTO interviews (organization_id, application_id, round, kind, scheduled_at,
			                        interviewer, notes, created_by)
			VALUES (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1,$2,$3,$4,$5,$6,$7)
			RETURNING id`, appID, in.Round, in.Kind, sched, who, notes, actor).Scan(&id)
		if e != nil {
			return translateAssignmentErr(e)
		}
		nv, _ := json.Marshal(map[string]any{"application_id": appID.String(), "round": in.Round, "kind": in.Kind})
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "interview.scheduled", ActorUserID: actor,
			EntityType: "interview", EntityID: id.String(), NewValues: nv,
		})
	})
	if err != nil {
		return Interview{}, err
	}
	return s.interviewRow(ctx, id)
}

// UpdateInterview records the outcome. The database requires a completed_at for a concluded outcome and
// forbids concluding before the interview was scheduled, so this validates the vocabulary and lets the
// constraints decide the rest.
func (s *Service) UpdateInterview(ctx context.Context, id uuid.UUID, in UpdateInterviewInput) (Interview, error) {
	if s.db == nil {
		return Interview{}, ErrNoDatabase
	}
	expected, err := parseToken(in.ExpectedUpdatedAt)
	if err != nil {
		return Interview{}, err
	}
	if in.Kind != nil && !interviewKinds[*in.Kind] {
		return Interview{}, &ValidationError{Field: "kind", Message: "is not a permitted interview type"}
	}
	if in.Outcome != nil && !interviewOutcomes[*in.Outcome] {
		return Interview{}, &ValidationError{Field: "outcome", Message: "is not a permitted outcome"}
	}
	// A concluded outcome needs a time. Asking here produces a named field instead of a constraint name.
	if in.Outcome != nil && interviewConcluded(*in.Outcome) && in.CompletedAt == nil {
		return Interview{}, &ValidationError{Field: "completed_at",
			Message: "say when the interview concluded"}
	}
	var sched, done *time.Time
	if in.ScheduledAt != nil {
		if sched, err = optTime("scheduled_at", *in.ScheduledAt); err != nil {
			return Interview{}, err
		}
	}
	if in.CompletedAt != nil {
		if done, err = optTime("completed_at", *in.CompletedAt); err != nil {
			return Interview{}, err
		}
	}

	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		tag, e := tx.Exec(ctx, `
			UPDATE interviews SET
			  kind         = COALESCE($2, kind),
			  scheduled_at = CASE WHEN $3 THEN $4 ELSE scheduled_at END,
			  completed_at = CASE WHEN $5 THEN $6 ELSE completed_at END,
			  outcome      = COALESCE($7, outcome),
			  interviewer  = COALESCE($8, interviewer),
			  notes        = COALESCE($9, notes),
			  updated_at   = now()
			WHERE id = $1 AND ($10::timestamptz IS NULL OR updated_at = $10)`,
			id, in.Kind, in.ScheduledAt != nil, sched, in.CompletedAt != nil, done,
			in.Outcome, in.Interviewer, in.Notes, tokenOrNil(expected, false))
		if e != nil {
			return translateAssignmentErr(e)
		}
		if tag.RowsAffected() == 0 {
			var exists bool
			if q := tx.QueryRow(ctx, `SELECT true FROM interviews WHERE id = $1`, id).Scan(&exists); q != nil {
				return ErrNotFound
			}
			return ErrConflict
		}
		nv, _ := json.Marshal(in)
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "interview.updated", ActorUserID: actorPtr(ctx),
			EntityType: "interview", EntityID: id.String(), NewValues: nv,
		})
	})
	if err != nil {
		return Interview{}, err
	}
	return s.interviewRow(ctx, id)
}

func (s *Service) interviewRow(ctx context.Context, id uuid.UUID) (Interview, error) {
	var out Interview
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		v, e := scanInterview(tx.QueryRow(ctx, `SELECT `+ivSelect+` `+ivFrom+` WHERE iv.id = $1`, id))
		if e != nil {
			if errors.Is(e, pgx.ErrNoRows) {
				return ErrNotFound
			}
			return translatePG(e)
		}
		out = v
		return nil
	})
	return out, err
}

// ---------------------------------------------------------------------------
// Offers
// ---------------------------------------------------------------------------

const ofSelect = `o.id::text, o.application_id::text, c.full_name, o.status, o.employment_type,
	o.bill_rate::text, o.pay_rate::text, o.salary::text, o.fee_pct::text,
	o.extended_at, o.responded_at, o.expires_at, o.decline_reason, o.created_at, o.updated_at`

const ofFrom = `FROM offers o
	JOIN applications a ON a.organization_id = o.organization_id AND a.id = o.application_id
	JOIN candidates   c ON c.organization_id = a.organization_id AND c.id = a.candidate_id`

func scanOffer(row scannable) (Offer, error) {
	var o Offer
	var name, bill, pay, sal, fee *string
	var extended, created, updated time.Time
	var responded, expires *time.Time
	if err := row.Scan(&o.ID, &o.ApplicationID, &name, &o.Status, &o.EmploymentType,
		&bill, &pay, &sal, &fee, &extended, &responded, &expires, &o.DeclineReason,
		&created, &updated); err != nil {
		return Offer{}, err
	}
	o.CandidateName = str(name)
	o.BillRate, o.PayRate, o.Salary, o.FeePct = dec(bill), dec(pay), dec(sal), dec(fee)
	o.Margin = marginOf(o.EmploymentType, o.BillRate, o.PayRate, o.Salary, o.FeePct, nil)
	o.ExtendedAt, o.CreatedAt, o.UpdatedAt = ts(extended), ts(created), ts(updated)
	if responded != nil {
		o.RespondedAt = ts(*responded)
	}
	if expires != nil {
		o.ExpiresAt = ts(*expires)
	}
	o.NextStatuses = offerTransitions[o.Status]
	if o.NextStatuses == nil {
		o.NextStatuses = []string{}
	}
	return o, nil
}

// ExtendOffer records terms offered to a candidate.
//
// Only one offer may be live per application, enforced by a partial unique index, so two recruiters
// extending at once produce one offer and one conflict rather than two contradictory sets of terms.
func (s *Service) ExtendOffer(ctx context.Context, appID uuid.UUID, in CreateOfferInput) (Offer, error) {
	if s.db == nil {
		return Offer{}, ErrNoDatabase
	}
	if in.EmploymentType == "" {
		in.EmploymentType = "contract"
	}
	if !reqEmployment[in.EmploymentType] {
		return Offer{}, &ValidationError{Field: "employment_type", Message: "is not a permitted value"}
	}
	bill, err := money("bill_rate", in.BillRate)
	if err != nil {
		return Offer{}, err
	}
	pay, err := money("pay_rate", in.PayRate)
	if err != nil {
		return Offer{}, err
	}
	sal, err := money("salary", in.Salary)
	if err != nil {
		return Offer{}, err
	}
	var feePct *decimal.Decimal
	if strings.TrimSpace(in.FeePct) != "" {
		f, e := decimal.NewFromString(strings.TrimSpace(in.FeePct))
		if e != nil {
			return Offer{}, &ValidationError{Field: "fee_pct", Message: "must be a number"}
		}
		// Stored as a fraction so 0.20 is twenty percent. Accepting 20 here and storing it would make a
		// 150000 salary earn a three million dollar fee.
		if f.IsNegative() || f.GreaterThan(decimal.NewFromInt(1)) {
			return Offer{}, &ValidationError{Field: "fee_pct",
				Message: "is a fraction between 0 and 1, so twenty percent is 0.20"}
		}
		feePct = &f
	}
	// The pricing shape must match the employment type. The database enforces it too, but a named field
	// here beats a constraint name.
	if in.EmploymentType == "direct_hire" {
		if sal == nil && feePct == nil {
			return Offer{}, &ValidationError{Field: "salary",
				Message: "a direct hire offer needs a salary or a fee percentage"}
		}
	} else if bill == nil || pay == nil {
		return Offer{}, &ValidationError{Field: "bill_rate",
			Message: "a contract offer needs both a bill rate and a pay rate"}
	}
	if bill != nil && pay != nil && bill.LessThan(*pay) {
		return Offer{}, &ValidationError{Field: "bill_rate", Message: "cannot be below the pay rate"}
	}
	expires, err := optTime("expires_at", in.ExpiresAt)
	if err != nil {
		return Offer{}, err
	}
	actor := actorPtr(ctx)

	// Scanned as a uuid.UUID rather than as text and parsed back with uuid.MustParse. See
	// CreateApplication for the reasoning: a database returned id has no legitimate failure mode, so the
	// only thing that parse could ever do is panic in a request path.
	var id uuid.UUID
	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		e := tx.QueryRow(ctx, `
			INSERT INTO offers (organization_id, application_id, employment_type,
			                    bill_rate, pay_rate, salary, fee_pct, expires_at, created_by)
			VALUES (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1,$2,$3,$4,$5,$6,$7,$8)
			RETURNING id`, appID, in.EmploymentType, bill, pay, sal, feePct, expires, actor).Scan(&id)
		if e != nil {
			return translateAssignmentErr(e)
		}
		nv, _ := json.Marshal(map[string]any{"application_id": appID.String(), "employment_type": in.EmploymentType})
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "offer.extended", ActorUserID: actor,
			EntityType: "offer", EntityID: id.String(), NewValues: nv,
		})
	})
	if err != nil {
		return Offer{}, err
	}
	return s.offerRow(ctx, id)
}

// RespondToOffer moves an offer through its lifecycle. The legal moves are enforced by a trigger, so a
// declined offer cannot later be accepted and the history keeps both when a role is re offered.
func (s *Service) RespondToOffer(ctx context.Context, id uuid.UUID, in OfferStatusInput) (Offer, error) {
	if s.db == nil {
		return Offer{}, ErrNoDatabase
	}
	if !offerStatuses[in.Status] {
		return Offer{}, &ValidationError{Field: "status", Message: "is not a permitted offer status"}
	}
	if in.ExpectedStatus != "" && !offerCanMove(in.ExpectedStatus, in.Status) {
		return Offer{}, &ValidationError{Field: "status",
			Message: "an offer cannot move from " + in.ExpectedStatus + " to " + in.Status}
	}
	reason, err := clean("decline_reason", in.DeclineReason, maxReqNote)
	if err != nil {
		return Offer{}, err
	}
	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		// responded_at is set from the database clock for the statuses that require it, so two nodes
		// cannot disagree about when a candidate answered.
		tag, e := tx.Exec(ctx, `
			UPDATE offers SET
			  status         = $2,
			  responded_at   = CASE WHEN $2 IN ('accepted','declined','rescinded')
			                        THEN COALESCE(responded_at, now()) ELSE responded_at END,
			  decline_reason = CASE WHEN $2 = 'declined' THEN $3 ELSE decline_reason END,
			  updated_at     = now()
			WHERE id = $1 AND ($4::text = '' OR status = $4)`, id, in.Status, reason, in.ExpectedStatus)
		if e != nil {
			return translateAssignmentErr(e)
		}
		if tag.RowsAffected() == 0 {
			var exists bool
			if q := tx.QueryRow(ctx, `SELECT true FROM offers WHERE id = $1`, id).Scan(&exists); q != nil {
				return ErrNotFound
			}
			return ErrConflict
		}
		nv, _ := json.Marshal(map[string]string{"status": in.Status})
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "offer." + in.Status, ActorUserID: actorPtr(ctx),
			EntityType: "offer", EntityID: id.String(), NewValues: nv,
		})
	})
	if err != nil {
		return Offer{}, err
	}
	return s.offerRow(ctx, id)
}

func (s *Service) offerRow(ctx context.Context, id uuid.UUID) (Offer, error) {
	var out Offer
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		o, e := scanOffer(tx.QueryRow(ctx, `SELECT `+ofSelect+` `+ofFrom+` WHERE o.id = $1`, id))
		if e != nil {
			if errors.Is(e, pgx.ErrNoRows) {
				return ErrNotFound
			}
			return translatePG(e)
		}
		out = o
		return nil
	})
	return out, err
}

// ---------------------------------------------------------------------------
// Placements
// ---------------------------------------------------------------------------

const plSelect = `p.id::text, p.application_id::text, p.offer_id::text, a.candidate_id::text, c.full_name,
	a.requisition_id::text, r.title, cl.name, p.status, p.employment_type, p.start_date, p.end_date,
	p.bill_rate::text, p.pay_rate::text, p.salary::text, p.fee_amount::text, p.guarantee_days,
	p.created_at, p.updated_at`

const plFrom = `FROM placements p
	JOIN applications  a  ON a.organization_id  = p.organization_id AND a.id = p.application_id
	JOIN candidates    c  ON c.organization_id  = a.organization_id AND c.id = a.candidate_id
	JOIN requisitions  r  ON r.organization_id  = a.organization_id AND r.id = a.requisition_id
	LEFT JOIN clients  cl ON cl.organization_id = r.organization_id AND cl.id = r.client_id`

func scanPlacement(row scannable, today time.Time) (Placement, error) {
	var p Placement
	var offerID, candID, name, reqID, reqTitle, clientName *string
	var bill, pay, sal, fee *string
	var start time.Time
	var end *time.Time
	var guarantee int
	var created, updated time.Time
	if err := row.Scan(&p.ID, &p.ApplicationID, &offerID, &candID, &name, &reqID, &reqTitle, &clientName,
		&p.Status, &p.EmploymentType, &start, &end, &bill, &pay, &sal, &fee, &guarantee,
		&created, &updated); err != nil {
		return Placement{}, err
	}
	p.OfferID, p.CandidateID, p.CandidateName = str(offerID), str(candID), str(name)
	p.RequisitionID, p.RequisitionTitle, p.ClientName = str(reqID), str(reqTitle), str(clientName)
	p.BillRate, p.PayRate, p.Salary, p.FeeAmount = dec(bill), dec(pay), dec(sal), dec(fee)
	p.Margin = marginOf(p.EmploymentType, p.BillRate, p.PayRate, p.Salary, nil, p.FeeAmount)
	p.Guarantee = guaranteeOf(guarantee, start, end, p.Status, today)
	p.StartDate = start.UTC().Format(dateLayout)
	if end != nil {
		p.EndDate = end.UTC().Format(dateLayout)
	}
	p.Live = !placementEnded(p.Status)
	p.CreatedAt, p.UpdatedAt = ts(created), ts(updated)
	return p, nil
}

// CreatePlacement starts an assignment.
//
// This moves the application to stage placed and inserts the placement in ONE transaction, in that
// order, because the two are the same fact recorded twice and migration 0055 refuses to let them
// disagree. The stage move goes through the event log, so it is subject to the seat cap: placing into a
// requisition whose openings are full is refused here rather than discovered in a report later.
func (s *Service) CreatePlacement(ctx context.Context, appID uuid.UUID, in CreatePlacementInput) (Placement, error) {
	if s.db == nil {
		return Placement{}, ErrNoDatabase
	}
	if in.EmploymentType == "" {
		in.EmploymentType = "contract"
	}
	if !reqEmployment[in.EmploymentType] {
		return Placement{}, &ValidationError{Field: "employment_type", Message: "is not a permitted value"}
	}
	start, err := optDate("start_date", in.StartDate)
	if err != nil {
		return Placement{}, err
	}
	if start == nil {
		return Placement{}, &ValidationError{Field: "start_date", Message: "is required"}
	}
	bill, err := money("bill_rate", in.BillRate)
	if err != nil {
		return Placement{}, err
	}
	pay, err := money("pay_rate", in.PayRate)
	if err != nil {
		return Placement{}, err
	}
	sal, err := money("salary", in.Salary)
	if err != nil {
		return Placement{}, err
	}
	feeAmt, err := money("fee_amount", in.FeeAmount)
	if err != nil {
		return Placement{}, err
	}
	if in.EmploymentType == "direct_hire" {
		if sal == nil && feeAmt == nil {
			return Placement{}, &ValidationError{Field: "salary",
				Message: "a direct hire placement needs a salary or a fee amount"}
		}
	} else if bill == nil || pay == nil {
		return Placement{}, &ValidationError{Field: "bill_rate",
			Message: "a contract placement needs both a bill rate and a pay rate"}
	}
	if bill != nil && pay != nil && bill.LessThan(*pay) {
		return Placement{}, &ValidationError{Field: "bill_rate", Message: "cannot be below the pay rate"}
	}
	guarantee := 90
	if in.GuaranteeDays != nil {
		if *in.GuaranteeDays < 0 || *in.GuaranteeDays > 3650 {
			return Placement{}, &ValidationError{Field: "guarantee_days", Message: "must be between 0 and 3650"}
		}
		guarantee = *in.GuaranteeDays
	}
	var offerID *uuid.UUID
	if offerID, err = optUUID("offer_id", in.OfferID); err != nil {
		return Placement{}, err
	}
	actor := actorPtr(ctx)
	if actor == nil {
		return Placement{}, tenant.ErrNoTenant
	}

	// Scanned as a uuid.UUID rather than as text and parsed back with uuid.MustParse. This one mattered
	// most of the three: the parse sat AFTER the commit, so a panic there would destroy the response for a
	// placement, a stage move, and an audit row that are already durably written, leaving the caller to
	// retry work the database has already accepted.
	var id uuid.UUID
	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		var stage string
		if e := tx.QueryRow(ctx, `SELECT stage FROM applications WHERE id = $1`, appID).Scan(&stage); e != nil {
			if errors.Is(e, pgx.ErrNoRows) {
				return ErrNotFound
			}
			return translatePG(e)
		}
		// Move to placed first. The event log is the only way stage changes, and it enforces the seat cap
		// under a row lock, so a full requisition is refused before any money is recorded.
		if stage != stagePlaced {
			if e := insertStageEvent(ctx, tx, appID, stage, stagePlaced, "", "placement created", actor, false); e != nil {
				return e
			}
		}
		e := tx.QueryRow(ctx, `
			INSERT INTO placements (organization_id, application_id, offer_id, employment_type,
			                        start_date, bill_rate, pay_rate, salary, fee_amount,
			                        guarantee_days, created_by)
			VALUES (NULLIF(current_setting('app.current_org_id', true), '')::uuid,
			        $1,$2,$3,$4,$5,$6,$7,$8,$9,$10)
			RETURNING id`,
			appID, offerID, in.EmploymentType, start, bill, pay, sal, feeAmt, guarantee, actor).Scan(&id)
		if e != nil {
			return translateAssignmentErr(e)
		}
		nv, _ := json.Marshal(map[string]any{
			"application_id": appID.String(), "start_date": in.StartDate, "employment_type": in.EmploymentType})
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "placement.created", ActorUserID: actor,
			EntityType: "placement", EntityID: id.String(), NewValues: nv,
		})
	})
	if err != nil {
		return Placement{}, err
	}
	return s.placementRow(ctx, id)
}

// EndPlacement closes an assignment and, where the outcome calls for it, moves the candidate out of the
// pipeline in the same transaction.
//
// The order is forced by migration 0055: the application cannot leave stage placed while the placement
// is live, so the placement is ended first and the stage moved second. Doing it the other way is refused
// by the database, which is the point.
func (s *Service) EndPlacement(ctx context.Context, id uuid.UUID, in EndPlacementInput) (Placement, error) {
	if s.db == nil {
		return Placement{}, ErrNoDatabase
	}
	if !placementStatuses[in.Status] || !placementEnded(in.Status) {
		return Placement{}, &ValidationError{Field: "status",
			Message: "ending an assignment means completed, terminated or fell_off"}
	}
	end, err := optDate("end_date", in.EndDate)
	if err != nil {
		return Placement{}, err
	}
	if end == nil {
		return Placement{}, &ValidationError{Field: "end_date", Message: "is required to end an assignment"}
	}
	code := strings.TrimSpace(in.DispositionCode)
	if code != "" {
		if e := validDisposition(code, stageRejected); e != nil {
			if e2 := validDisposition(code, stageWithdrawn); e2 != nil {
				return Placement{}, e
			}
		}
	}
	reason, err := clean("reason", in.Reason, maxReqNote)
	if err != nil {
		return Placement{}, err
	}
	actor := actorPtr(ctx)

	err = s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		var appID uuid.UUID
		var curStatus string
		if e := tx.QueryRow(ctx,
			`SELECT application_id, status FROM placements WHERE id = $1 FOR UPDATE`, id).
			Scan(&appID, &curStatus); e != nil {
			if errors.Is(e, pgx.ErrNoRows) {
				return ErrNotFound
			}
			return translatePG(e)
		}
		if placementEnded(curStatus) {
			return &ValidationError{Field: "status", Message: "this assignment has already ended"}
		}
		if _, e := tx.Exec(ctx,
			`UPDATE placements SET status = $2, end_date = $3, updated_at = now() WHERE id = $1`,
			id, in.Status, end); e != nil {
			return translateAssignmentErr(e)
		}
		// Now the stage may move, because no live placement stands in the way.
		if code != "" {
			to := stageRejected
			if dispositionCodes[code] == stageWithdrawn {
				to = stageWithdrawn
			}
			if e := insertStageEvent(ctx, tx, appID, stagePlaced, to, code, reason, actor, false); e != nil {
				return e
			}
		}
		nv, _ := json.Marshal(map[string]string{"status": in.Status, "end_date": in.EndDate, "code": code})
		return s.audit.OnTx(ctx, tx, audit.Event{
			Action: "placement.ended", ActorUserID: actor,
			EntityType: "placement", EntityID: id.String(), NewValues: nv,
		})
	})
	if err != nil {
		return Placement{}, err
	}
	return s.placementRow(ctx, id)
}

func (s *Service) placementRow(ctx context.Context, id uuid.UUID) (Placement, error) {
	var out Placement
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		p, e := scanPlacement(tx.QueryRow(ctx, `SELECT `+plSelect+` `+plFrom+` WHERE p.id = $1`, id),
			time.Now().UTC())
		if e != nil {
			if errors.Is(e, pgx.ErrNoRows) {
				return ErrNotFound
			}
			return translatePG(e)
		}
		out = p
		return nil
	})
	return out, err
}

// ListPlacements returns assignments, live ones first.
func (s *Service) ListPlacements(ctx context.Context, status string, limit, offset int) (PlacementList, error) {
	if s.db == nil {
		return PlacementList{}, ErrNoDatabase
	}
	if limit <= 0 || limit > 200 {
		limit = 50
	}
	if offset < 0 {
		offset = 0
	}
	where, args := "true", []any{}
	switch status {
	case "":
	case "live":
		where = "p.status IN ('pending_start','active')"
	case "ended":
		where = "p.status IN ('completed','terminated','fell_off')"
	default:
		if !placementStatuses[status] {
			return PlacementList{}, &ValidationError{Field: "status", Message: "is not a permitted placement status"}
		}
		args = append(args, status)
		where = "p.status = $1"
	}

	out := PlacementList{Placements: []Placement{}, Limit: limit, Offset: offset}
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if e := tx.QueryRow(ctx, `SELECT count(*) `+plFrom+` WHERE `+where, args...).Scan(&out.Total); e != nil {
			return translatePG(e)
		}
		rows, e := tx.Query(ctx, `SELECT `+plSelect+` `+plFrom+` WHERE `+where+`
			ORDER BY (p.status IN ('pending_start','active')) DESC, p.start_date DESC, p.id ASC
			LIMIT $`+itoa(len(args)+1)+` OFFSET $`+itoa(len(args)+2), append(args, limit, offset)...)
		if e != nil {
			return translatePG(e)
		}
		defer rows.Close()
		today := time.Now().UTC()
		for rows.Next() {
			p, e := scanPlacement(rows, today)
			if e != nil {
				return translatePG(e)
			}
			out.Placements = append(out.Placements, p)
		}
		return rows.Err()
	})
	if err != nil {
		return PlacementList{}, err
	}
	return out, nil
}

// GetAssignment returns everything hanging off one application in a single read.
func (s *Service) GetAssignment(ctx context.Context, appID uuid.UUID) (Assignment, error) {
	if s.db == nil {
		return Assignment{}, ErrNoDatabase
	}
	out := Assignment{
		ApplicationID: appID.String(),
		Interviews:    []Interview{}, Offers: []Offer{}, Placements: []Placement{},
	}
	err := s.db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error {
		if e := tx.QueryRow(ctx, `
			SELECT c.full_name, a.stage FROM applications a
			  JOIN candidates c ON c.organization_id = a.organization_id AND c.id = a.candidate_id
			 WHERE a.id = $1`, appID).Scan(&out.CandidateName, &out.Stage); e != nil {
			if errors.Is(e, pgx.ErrNoRows) {
				return ErrNotFound
			}
			return translatePG(e)
		}
		out.StageLabel = stageLabels[out.Stage]

		rows, e := tx.Query(ctx, `SELECT `+ivSelect+` `+ivFrom+` WHERE iv.application_id = $1 ORDER BY iv.round`, appID)
		if e != nil {
			return translatePG(e)
		}
		for rows.Next() {
			v, e := scanInterview(rows)
			if e != nil {
				rows.Close()
				return translatePG(e)
			}
			out.Interviews = append(out.Interviews, v)
		}
		rows.Close()
		if e := rows.Err(); e != nil {
			return e
		}

		rows, e = tx.Query(ctx, `SELECT `+ofSelect+` `+ofFrom+` WHERE o.application_id = $1 ORDER BY o.extended_at DESC`, appID)
		if e != nil {
			return translatePG(e)
		}
		for rows.Next() {
			o, e := scanOffer(rows)
			if e != nil {
				rows.Close()
				return translatePG(e)
			}
			out.Offers = append(out.Offers, o)
		}
		rows.Close()
		if e := rows.Err(); e != nil {
			return e
		}

		today := time.Now().UTC()
		rows, e = tx.Query(ctx, `SELECT `+plSelect+` `+plFrom+` WHERE p.application_id = $1 ORDER BY p.start_date DESC`, appID)
		if e != nil {
			return translatePG(e)
		}
		defer rows.Close()
		for rows.Next() {
			p, e := scanPlacement(rows, today)
			if e != nil {
				return translatePG(e)
			}
			out.Placements = append(out.Placements, p)
		}
		return rows.Err()
	})
	if err != nil {
		return Assignment{}, err
	}
	return out, nil
}

// translateAssignmentErr maps the constraints and triggers added in 0048 and 0055 onto typed errors.
// These messages are load bearing: they are the only place several of these rules can be enforced
// without a race, so their failures have to reach the caller as something actionable.
func translateAssignmentErr(err error) error {
	var pg *pgconn.PgError
	if !errors.As(err, &pg) {
		return translatePG(err)
	}
	switch {
	case pg.Code == "23505" && strings.Contains(pg.ConstraintName, "uq_offers_one_live"):
		return &ValidationError{Field: "status",
			Message: "this candidate already has a live offer, so withdraw or resolve it first"}
	case pg.Code == "23505" && strings.Contains(pg.ConstraintName, "uq_offers_one_accepted"):
		return &ValidationError{Field: "status", Message: "this candidate already has an accepted offer"}
	case pg.Code == "23505" && strings.Contains(pg.ConstraintName, "uq_placements_one_live"):
		return &ValidationError{Field: "application_id", Message: "this candidate already has a live assignment"}
	case pg.Code == "23505" && strings.Contains(pg.ConstraintName, "interviews_organization_id_application_id_round"):
		return &ValidationError{Field: "round", Message: "that interview round already exists"}
	case pg.Code == "23503" && strings.Contains(pg.ConstraintName, "placements_offer_fkey"):
		return &ValidationError{Field: "offer_id", Message: "that offer belongs to a different candidate"}
	case pg.Code == "23514" && strings.Contains(pg.Message, "live placement requires"):
		return &ValidationError{Field: "application_id", Message: pg.Message}
	case pg.Code == "23514" && strings.Contains(pg.Message, "live placement: end it"):
		return &ValidationError{Field: "status", Message: pg.Message}
	case pg.Code == "23514" && strings.Contains(pg.Message, "offer cannot move"):
		return &ValidationError{Field: "status", Message: pg.Message}
	case pg.Code == "23514" && strings.Contains(pg.ConstraintName, "offers_pricing_chk"):
		return &ValidationError{Field: "bill_rate", Message: "an extended offer must carry its terms"}
	case pg.Code == "23514" && strings.Contains(pg.ConstraintName, "placements_pricing_chk"):
		return &ValidationError{Field: "bill_rate", Message: "the pricing does not match the employment type"}
	case pg.Code == "23514" && strings.Contains(pg.ConstraintName, "margin_chk"):
		return &ValidationError{Field: "bill_rate", Message: "cannot be below the pay rate"}
	case pg.Code == "23514" && strings.Contains(pg.ConstraintName, "interviews_completed_chk"):
		return &ValidationError{Field: "completed_at", Message: "say when the interview concluded"}
	case pg.Code == "23514" && strings.Contains(pg.ConstraintName, "interviews_order_chk"):
		return &ValidationError{Field: "completed_at", Message: "cannot be before the interview was scheduled"}
	case pg.Code == "23514" && strings.Contains(pg.ConstraintName, "placements_dates_chk"):
		return &ValidationError{Field: "end_date", Message: "cannot be before the start date"}
	}
	return translateStageErr(err)
}

// optTime parses an optional RFC3339 instant.
func optTime(field, v string) (*time.Time, error) {
	v = strings.TrimSpace(v)
	if v == "" {
		return nil, nil
	}
	t, err := time.Parse(time.RFC3339, v)
	if err != nil {
		return nil, &ValidationError{Field: field, Message: "must be a timestamp like 2026-08-01T14:00:00Z"}
	}
	return &t, nil
}