// Code generated by sqlc. DO NOT EDIT. // versions: // sqlc v1.31.1 // source: staffing.sql package dbgen import ( "context" "time" "github.com/google/uuid" "github.com/shopspring/decimal" ) const createRecommendationRun = `-- name: CreateRecommendationRun :one INSERT INTO recommendation_runs (organization_id, view, scope_ref, model_version, parameters, created_by) VALUES ($1, $2, $3, $4, $5, $6) RETURNING id, organization_id, view, scope_ref, model_version, parameters, created_by, created_at ` type CreateRecommendationRunParams struct { OrganizationID uuid.UUID `json:"organization_id"` View string `json:"view"` ScopeRef *uuid.UUID `json:"scope_ref"` ModelVersion string `json:"model_version"` Parameters []byte `json:"parameters"` CreatedBy *uuid.UUID `json:"created_by"` } // W3 AI Staffing Recommendations queries. Reads are RLS scoped by the app.current_org_id policy, so a // bare SELECT only ever sees the caller's org; writes pass organization_id explicitly and the RLS WITH // CHECK validates it matches the session org. No query here names the eeo schema, and none can: the // generated code is built only from public schema queries. func (q *Queries) CreateRecommendationRun(ctx context.Context, arg CreateRecommendationRunParams) (RecommendationRun, error) { row := q.db.QueryRow(ctx, createRecommendationRun, arg.OrganizationID, arg.View, arg.ScopeRef, arg.ModelVersion, arg.Parameters, arg.CreatedBy, ) var i RecommendationRun err := row.Scan( &i.ID, &i.OrganizationID, &i.View, &i.ScopeRef, &i.ModelVersion, &i.Parameters, &i.CreatedBy, &i.CreatedAt, ) return i, err } const createStaffingRecommendation = `-- name: CreateStaffingRecommendation :one INSERT INTO staffing_recommendations ( organization_id, run_id, requirement_id, candidate_id, action, rank, confidence, value_score, rationale, rationale_source, evidence ) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) RETURNING id, organization_id, run_id, requirement_id, candidate_id, action, rank, confidence, value_score, rationale, rationale_source, evidence, status, decided_by, decided_at, decision_note, created_at ` type CreateStaffingRecommendationParams struct { OrganizationID uuid.UUID `json:"organization_id"` RunID uuid.UUID `json:"run_id"` RequirementID uuid.UUID `json:"requirement_id"` CandidateID *uuid.UUID `json:"candidate_id"` Action string `json:"action"` Rank int32 `json:"rank"` Confidence decimal.Decimal `json:"confidence"` ValueScore *int64 `json:"value_score"` Rationale string `json:"rationale"` RationaleSource string `json:"rationale_source"` Evidence []byte `json:"evidence"` } func (q *Queries) CreateStaffingRecommendation(ctx context.Context, arg CreateStaffingRecommendationParams) (StaffingRecommendation, error) { row := q.db.QueryRow(ctx, createStaffingRecommendation, arg.OrganizationID, arg.RunID, arg.RequirementID, arg.CandidateID, arg.Action, arg.Rank, arg.Confidence, arg.ValueScore, arg.Rationale, arg.RationaleSource, arg.Evidence, ) var i StaffingRecommendation err := row.Scan( &i.ID, &i.OrganizationID, &i.RunID, &i.RequirementID, &i.CandidateID, &i.Action, &i.Rank, &i.Confidence, &i.ValueScore, &i.Rationale, &i.RationaleSource, &i.Evidence, &i.Status, &i.DecidedBy, &i.DecidedAt, &i.DecisionNote, &i.CreatedAt, ) return i, err } const deleteRecommendationsForCandidate = `-- name: DeleteRecommendationsForCandidate :exec DELETE FROM staffing_recommendations WHERE candidate_id = $1 ` // Right to erasure: when a candidate is anonymized or sourced-erased, their recommendation rows are // removed (anonymize in place does not delete the candidate row, so this is explicit, mirroring the // ai_memory cleanup). func (q *Queries) DeleteRecommendationsForCandidate(ctx context.Context, candidateID *uuid.UUID) error { _, err := q.db.Exec(ctx, deleteRecommendationsForCandidate, candidateID) return err } const getRequirementWithValue = `-- name: GetRequirementWithValue :one SELECT r.id, r.organization_id, r.opportunity_id, r.title, r.customer_agency, r.required_level, r.required_poly, r.required_years, r.work_location, r.remote_allowed, r.key_personnel, r.created_at, r.updated_at, r.deleted_at, r.labor_category, r.quantity, r.required_compartments, o.value AS opportunity_value, o.value_source AS opportunity_value_source, o.status AS opportunity_status FROM requirements r LEFT JOIN opportunities o ON o.id = r.opportunity_id AND o.organization_id = r.organization_id WHERE r.id = $1 ` type GetRequirementWithValueRow struct { ID uuid.UUID `json:"id"` OrganizationID uuid.UUID `json:"organization_id"` OpportunityID *uuid.UUID `json:"opportunity_id"` Title string `json:"title"` CustomerAgency *string `json:"customer_agency"` RequiredLevel string `json:"required_level"` RequiredPoly string `json:"required_poly"` RequiredYears int32 `json:"required_years"` WorkLocation *string `json:"work_location"` RemoteAllowed bool `json:"remote_allowed"` KeyPersonnel bool `json:"key_personnel"` CreatedAt time.Time `json:"created_at"` UpdatedAt time.Time `json:"updated_at"` DeletedAt *time.Time `json:"deleted_at"` LaborCategory *string `json:"labor_category"` Quantity int32 `json:"quantity"` RequiredCompartments []string `json:"required_compartments"` OpportunityValue *decimal.Decimal `json:"opportunity_value"` OpportunityValueSource *string `json:"opportunity_value_source"` OpportunityStatus *string `json:"opportunity_status"` } // One requirement with its backing opportunity value, provenance, and status, for the gap view. The // service uses deleted_at and the opportunity status to 404 a requirement that is not a real open gap. func (q *Queries) GetRequirementWithValue(ctx context.Context, id uuid.UUID) (GetRequirementWithValueRow, error) { row := q.db.QueryRow(ctx, getRequirementWithValue, id) var i GetRequirementWithValueRow err := row.Scan( &i.ID, &i.OrganizationID, &i.OpportunityID, &i.Title, &i.CustomerAgency, &i.RequiredLevel, &i.RequiredPoly, &i.RequiredYears, &i.WorkLocation, &i.RemoteAllowed, &i.KeyPersonnel, &i.CreatedAt, &i.UpdatedAt, &i.DeletedAt, &i.LaborCategory, &i.Quantity, &i.RequiredCompartments, &i.OpportunityValue, &i.OpportunityValueSource, &i.OpportunityStatus, ) return i, err } const getStaffingRecommendationForUpdate = `-- name: GetStaffingRecommendationForUpdate :one SELECT id, organization_id, run_id, requirement_id, candidate_id, action, rank, confidence, value_score, rationale, rationale_source, evidence, status, decided_by, decided_at, decision_note, created_at FROM staffing_recommendations WHERE id = $1 FOR UPDATE ` // Locks the row for the decision path so two concurrent decisions on the same recommendation serialize; // the service checks existence (else 404) and status (else 409) under this lock before deciding. func (q *Queries) GetStaffingRecommendationForUpdate(ctx context.Context, id uuid.UUID) (StaffingRecommendation, error) { row := q.db.QueryRow(ctx, getStaffingRecommendationForUpdate, id) var i StaffingRecommendation err := row.Scan( &i.ID, &i.OrganizationID, &i.RunID, &i.RequirementID, &i.CandidateID, &i.Action, &i.Rank, &i.Confidence, &i.ValueScore, &i.Rationale, &i.RationaleSource, &i.Evidence, &i.Status, &i.DecidedBy, &i.DecidedAt, &i.DecisionNote, &i.CreatedAt, ) return i, err } const listCommitments = `-- name: ListCommitments :many SELECT sm.candidate_id AS candidate_id, sl.requirement_id AS requirement_id, 'slate'::text AS source FROM slate_members sm JOIN slates sl ON sl.id = sm.slate_id AND sl.organization_id = sm.organization_id WHERE sm.decision = 'approved' UNION SELECT sr.candidate_id AS candidate_id, sr.requirement_id AS requirement_id, 'provisional'::text AS source FROM staffing_recommendations sr WHERE sr.action = 'assign' AND sr.status = 'approved' AND sr.candidate_id IS NOT NULL ` type ListCommitmentsRow struct { CandidateID uuid.UUID `json:"candidate_id"` RequirementID uuid.UUID `json:"requirement_id"` Source string `json:"source"` } // A candidate is committed to a requirement when they are an approved slate member (the source of truth) // or have an approved W3 ASSIGN that has not yet flowed to a slate (provisional commitment). The gap view // reads this so a committed candidate is classified REASSIGN, never a duplicate ASSIGN. func (q *Queries) ListCommitments(ctx context.Context) ([]ListCommitmentsRow, error) { rows, err := q.db.Query(ctx, listCommitments) if err != nil { return nil, err } defer rows.Close() items := []ListCommitmentsRow{} for rows.Next() { var i ListCommitmentsRow if err := rows.Scan(&i.CandidateID, &i.RequirementID, &i.Source); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listOpenRequirementsWithValue = `-- name: ListOpenRequirementsWithValue :many SELECT r.id, r.organization_id, r.opportunity_id, r.title, r.customer_agency, r.required_level, r.required_poly, r.required_years, r.work_location, r.remote_allowed, r.key_personnel, r.created_at, r.updated_at, r.deleted_at, r.labor_category, r.quantity, r.required_compartments, o.value AS opportunity_value, o.value_source AS opportunity_value_source FROM requirements r LEFT JOIN opportunities o ON o.id = r.opportunity_id AND o.organization_id = r.organization_id WHERE r.deleted_at IS NULL AND (r.opportunity_id IS NULL OR o.status = 'open') ORDER BY r.id LIMIT $1 OFFSET $2 ` type ListOpenRequirementsWithValueParams struct { Limit int32 `json:"limit"` Offset int32 `json:"offset"` } type ListOpenRequirementsWithValueRow struct { ID uuid.UUID `json:"id"` OrganizationID uuid.UUID `json:"organization_id"` OpportunityID *uuid.UUID `json:"opportunity_id"` Title string `json:"title"` CustomerAgency *string `json:"customer_agency"` RequiredLevel string `json:"required_level"` RequiredPoly string `json:"required_poly"` RequiredYears int32 `json:"required_years"` WorkLocation *string `json:"work_location"` RemoteAllowed bool `json:"remote_allowed"` KeyPersonnel bool `json:"key_personnel"` CreatedAt time.Time `json:"created_at"` UpdatedAt time.Time `json:"updated_at"` DeletedAt *time.Time `json:"deleted_at"` LaborCategory *string `json:"labor_category"` Quantity int32 `json:"quantity"` RequiredCompartments []string `json:"required_compartments"` OpportunityValue *decimal.Decimal `json:"opportunity_value"` OpportunityValueSource *string `json:"opportunity_value_source"` } // Open requirements with their backing opportunity dollar value and its provenance, for the portfolio // value function. A standalone requirement (no opportunity) has a null value and is handled by the // documented value fallback in the optimizer. func (q *Queries) ListOpenRequirementsWithValue(ctx context.Context, arg ListOpenRequirementsWithValueParams) ([]ListOpenRequirementsWithValueRow, error) { rows, err := q.db.Query(ctx, listOpenRequirementsWithValue, arg.Limit, arg.Offset) if err != nil { return nil, err } defer rows.Close() items := []ListOpenRequirementsWithValueRow{} for rows.Next() { var i ListOpenRequirementsWithValueRow if err := rows.Scan( &i.ID, &i.OrganizationID, &i.OpportunityID, &i.Title, &i.CustomerAgency, &i.RequiredLevel, &i.RequiredPoly, &i.RequiredYears, &i.WorkLocation, &i.RemoteAllowed, &i.KeyPersonnel, &i.CreatedAt, &i.UpdatedAt, &i.DeletedAt, &i.LaborCategory, &i.Quantity, &i.RequiredCompartments, &i.OpportunityValue, &i.OpportunityValueSource, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const listProactiveRecommendations = `-- name: ListProactiveRecommendations :many SELECT sr.id, sr.organization_id, sr.run_id, sr.requirement_id, sr.candidate_id, sr.action, sr.rank, sr.confidence, sr.value_score, sr.rationale, sr.rationale_source, sr.evidence, sr.status, sr.decided_by, sr.decided_at, sr.decision_note, sr.created_at FROM staffing_recommendations sr JOIN recommendation_runs rr ON rr.id = sr.run_id AND rr.organization_id = sr.organization_id WHERE rr.view = 'proactive' AND sr.status = 'pending' ORDER BY sr.candidate_id, sr.rank, sr.id LIMIT $1 OFFSET $2 ` type ListProactiveRecommendationsParams struct { Limit int32 `json:"limit"` Offset int32 `json:"offset"` } // The current bench watch: pending recommendations from proactive runs, capped and paginated. func (q *Queries) ListProactiveRecommendations(ctx context.Context, arg ListProactiveRecommendationsParams) ([]StaffingRecommendation, error) { rows, err := q.db.Query(ctx, listProactiveRecommendations, arg.Limit, arg.Offset) if err != nil { return nil, err } defer rows.Close() items := []StaffingRecommendation{} for rows.Next() { var i StaffingRecommendation if err := rows.Scan( &i.ID, &i.OrganizationID, &i.RunID, &i.RequirementID, &i.CandidateID, &i.Action, &i.Rank, &i.Confidence, &i.ValueScore, &i.Rationale, &i.RationaleSource, &i.Evidence, &i.Status, &i.DecidedBy, &i.DecidedAt, &i.DecisionNote, &i.CreatedAt, ); err != nil { return nil, err } items = append(items, i) } if err := rows.Err(); err != nil { return nil, err } return items, nil } const markRecommendationDecision = `-- name: MarkRecommendationDecision :one UPDATE staffing_recommendations SET status = $2, decided_by = $3, decided_at = now(), decision_note = $4 WHERE id = $1 AND status = 'pending' RETURNING id, organization_id, run_id, requirement_id, candidate_id, action, rank, confidence, value_score, rationale, rationale_source, evidence, status, decided_by, decided_at, decision_note, created_at ` type MarkRecommendationDecisionParams struct { ID uuid.UUID `json:"id"` Status string `json:"status"` DecidedBy *uuid.UUID `json:"decided_by"` DecisionNote *string `json:"decision_note"` } // Status guarded: only a pending row can be decided, so a row that was concurrently superseded or // decided returns no row and the service surfaces 409 rather than overwriting a dead or decided row. func (q *Queries) MarkRecommendationDecision(ctx context.Context, arg MarkRecommendationDecisionParams) (StaffingRecommendation, error) { row := q.db.QueryRow(ctx, markRecommendationDecision, arg.ID, arg.Status, arg.DecidedBy, arg.DecisionNote, ) var i StaffingRecommendation err := row.Scan( &i.ID, &i.OrganizationID, &i.RunID, &i.RequirementID, &i.CandidateID, &i.Action, &i.Rank, &i.Confidence, &i.ValueScore, &i.Rationale, &i.RationaleSource, &i.Evidence, &i.Status, &i.DecidedBy, &i.DecidedAt, &i.DecisionNote, &i.CreatedAt, ) return i, err } const supersedePendingGap = `-- name: SupersedePendingGap :exec UPDATE staffing_recommendations sr SET status = 'superseded' FROM recommendation_runs rr WHERE sr.run_id = rr.id AND sr.organization_id = rr.organization_id AND rr.view = 'gap' AND sr.requirement_id = $1 AND sr.status = 'pending' ` // A fresh gap run for a requirement supersedes ONLY the prior pending gap rows for that requirement. // Decided rows (approved, rejected, dismissed) are immutable and are never touched. func (q *Queries) SupersedePendingGap(ctx context.Context, requirementID uuid.UUID) error { _, err := q.db.Exec(ctx, supersedePendingGap, requirementID) return err } const supersedePendingPortfolio = `-- name: SupersedePendingPortfolio :exec UPDATE staffing_recommendations sr SET status = 'superseded' FROM recommendation_runs rr WHERE sr.run_id = rr.id AND sr.organization_id = rr.organization_id AND rr.view = 'portfolio' AND sr.status = 'pending' ` // A fresh portfolio run supersedes ONLY the prior pending portfolio rows for the org. Decided rows are // immutable. func (q *Queries) SupersedePendingPortfolio(ctx context.Context) error { _, err := q.db.Exec(ctx, supersedePendingPortfolio) return err } const supersedePendingProactive = `-- name: SupersedePendingProactive :exec UPDATE staffing_recommendations sr SET status = 'superseded' FROM recommendation_runs rr WHERE sr.run_id = rr.id AND sr.organization_id = rr.organization_id AND rr.view = 'proactive' AND sr.candidate_id = $1 AND sr.status = 'pending' ` // A fresh proactive recompute for one candidate supersedes ONLY that candidate's prior pending proactive // rows. Decided rows are immutable. func (q *Queries) SupersedePendingProactive(ctx context.Context, candidateID *uuid.UUID) error { _, err := q.db.Exec(ctx, supersedePendingProactive, candidateID) return err }