Janady13/missionmatch / internal/platform/database/dbgen/staffing.sql.go

1// Code generated by sqlc. DO NOT EDIT. 2// versions: 3// sqlc v1.31.1 4// source: staffing.sql 5 6package dbgen 7 8import ( 9 "context" 10 "time" 11 12 "github.com/google/uuid" 13 "github.com/shopspring/decimal" 14) 15 16const createRecommendationRun = `-- name: CreateRecommendationRun :one 17 18INSERT INTO recommendation_runs (organization_id, view, scope_ref, model_version, parameters, created_by) 19VALUES ($1, $2, $3, $4, $5, $6) 20RETURNING id, organization_id, view, scope_ref, model_version, parameters, created_by, created_at 21` 22 23type CreateRecommendationRunParams struct { 24 OrganizationID uuid.UUID `json:"organization_id"` 25 View string `json:"view"` 26 ScopeRef *uuid.UUID `json:"scope_ref"` 27 ModelVersion string `json:"model_version"` 28 Parameters []byte `json:"parameters"` 29 CreatedBy *uuid.UUID `json:"created_by"` 30} 31 32// W3 AI Staffing Recommendations queries. Reads are RLS scoped by the app.current_org_id policy, so a 33// bare SELECT only ever sees the caller's org; writes pass organization_id explicitly and the RLS WITH 34// CHECK validates it matches the session org. No query here names the eeo schema, and none can: the 35// generated code is built only from public schema queries. 36func (q *Queries) CreateRecommendationRun(ctx context.Context, arg CreateRecommendationRunParams) (RecommendationRun, error) { 37 row := q.db.QueryRow(ctx, createRecommendationRun, 38 arg.OrganizationID, 39 arg.View, 40 arg.ScopeRef, 41 arg.ModelVersion, 42 arg.Parameters, 43 arg.CreatedBy, 44 ) 45 var i RecommendationRun 46 err := row.Scan( 47 &i.ID, 48 &i.OrganizationID, 49 &i.View, 50 &i.ScopeRef, 51 &i.ModelVersion, 52 &i.Parameters, 53 &i.CreatedBy, 54 &i.CreatedAt, 55 ) 56 return i, err 57} 58 59const createStaffingRecommendation = `-- name: CreateStaffingRecommendation :one 60INSERT INTO staffing_recommendations ( 61 organization_id, run_id, requirement_id, candidate_id, action, rank, confidence, 62 value_score, rationale, rationale_source, evidence 63) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11) 64RETURNING 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 65` 66 67type CreateStaffingRecommendationParams struct { 68 OrganizationID uuid.UUID `json:"organization_id"` 69 RunID uuid.UUID `json:"run_id"` 70 RequirementID uuid.UUID `json:"requirement_id"` 71 CandidateID *uuid.UUID `json:"candidate_id"` 72 Action string `json:"action"` 73 Rank int32 `json:"rank"` 74 Confidence decimal.Decimal `json:"confidence"` 75 ValueScore *int64 `json:"value_score"` 76 Rationale string `json:"rationale"` 77 RationaleSource string `json:"rationale_source"` 78 Evidence []byte `json:"evidence"` 79} 80 81func (q *Queries) CreateStaffingRecommendation(ctx context.Context, arg CreateStaffingRecommendationParams) (StaffingRecommendation, error) { 82 row := q.db.QueryRow(ctx, createStaffingRecommendation, 83 arg.OrganizationID, 84 arg.RunID, 85 arg.RequirementID, 86 arg.CandidateID, 87 arg.Action, 88 arg.Rank, 89 arg.Confidence, 90 arg.ValueScore, 91 arg.Rationale, 92 arg.RationaleSource, 93 arg.Evidence, 94 ) 95 var i StaffingRecommendation 96 err := row.Scan( 97 &i.ID, 98 &i.OrganizationID, 99 &i.RunID, 100 &i.RequirementID, 101 &i.CandidateID, 102 &i.Action, 103 &i.Rank, 104 &i.Confidence, 105 &i.ValueScore, 106 &i.Rationale, 107 &i.RationaleSource, 108 &i.Evidence, 109 &i.Status, 110 &i.DecidedBy, 111 &i.DecidedAt, 112 &i.DecisionNote, 113 &i.CreatedAt, 114 ) 115 return i, err 116} 117 118const deleteRecommendationsForCandidate = `-- name: DeleteRecommendationsForCandidate :exec 119DELETE FROM staffing_recommendations WHERE candidate_id = $1 120` 121 122// Right to erasure: when a candidate is anonymized or sourced-erased, their recommendation rows are 123// removed (anonymize in place does not delete the candidate row, so this is explicit, mirroring the 124// ai_memory cleanup). 125func (q *Queries) DeleteRecommendationsForCandidate(ctx context.Context, candidateID *uuid.UUID) error { 126 _, err := q.db.Exec(ctx, deleteRecommendationsForCandidate, candidateID) 127 return err 128} 129 130const getRequirementWithValue = `-- name: GetRequirementWithValue :one 131SELECT 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, o.value AS opportunity_value, o.value_source AS opportunity_value_source, o.status AS opportunity_status 132FROM requirements r 133LEFT JOIN opportunities o ON o.id = r.opportunity_id AND o.organization_id = r.organization_id 134WHERE r.id = $1 135` 136 137type GetRequirementWithValueRow struct { 138 ID uuid.UUID `json:"id"` 139 OrganizationID uuid.UUID `json:"organization_id"` 140 OpportunityID *uuid.UUID `json:"opportunity_id"` 141 Title string `json:"title"` 142 CustomerAgency *string `json:"customer_agency"` 143 RequiredLevel string `json:"required_level"` 144 RequiredPoly string `json:"required_poly"` 145 RequiredYears int32 `json:"required_years"` 146 WorkLocation *string `json:"work_location"` 147 RemoteAllowed bool `json:"remote_allowed"` 148 KeyPersonnel bool `json:"key_personnel"` 149 CreatedAt time.Time `json:"created_at"` 150 UpdatedAt time.Time `json:"updated_at"` 151 DeletedAt *time.Time `json:"deleted_at"` 152 LaborCategory *string `json:"labor_category"` 153 Quantity int32 `json:"quantity"` 154 OpportunityValue *decimal.Decimal `json:"opportunity_value"` 155 OpportunityValueSource *string `json:"opportunity_value_source"` 156 OpportunityStatus *string `json:"opportunity_status"` 157} 158 159// One requirement with its backing opportunity value, provenance, and status, for the gap view. The 160// service uses deleted_at and the opportunity status to 404 a requirement that is not a real open gap. 161func (q *Queries) GetRequirementWithValue(ctx context.Context, id uuid.UUID) (GetRequirementWithValueRow, error) { 162 row := q.db.QueryRow(ctx, getRequirementWithValue, id) 163 var i GetRequirementWithValueRow 164 err := row.Scan( 165 &i.ID, 166 &i.OrganizationID, 167 &i.OpportunityID, 168 &i.Title, 169 &i.CustomerAgency, 170 &i.RequiredLevel, 171 &i.RequiredPoly, 172 &i.RequiredYears, 173 &i.WorkLocation, 174 &i.RemoteAllowed, 175 &i.KeyPersonnel, 176 &i.CreatedAt, 177 &i.UpdatedAt, 178 &i.DeletedAt, 179 &i.LaborCategory, 180 &i.Quantity, 181 &i.OpportunityValue, 182 &i.OpportunityValueSource, 183 &i.OpportunityStatus, 184 ) 185 return i, err 186} 187 188const getStaffingRecommendationForUpdate = `-- name: GetStaffingRecommendationForUpdate :one 189SELECT 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 190` 191 192// Locks the row for the decision path so two concurrent decisions on the same recommendation serialize; 193// the service checks existence (else 404) and status (else 409) under this lock before deciding. 194func (q *Queries) GetStaffingRecommendationForUpdate(ctx context.Context, id uuid.UUID) (StaffingRecommendation, error) { 195 row := q.db.QueryRow(ctx, getStaffingRecommendationForUpdate, id) 196 var i StaffingRecommendation 197 err := row.Scan( 198 &i.ID, 199 &i.OrganizationID, 200 &i.RunID, 201 &i.RequirementID, 202 &i.CandidateID, 203 &i.Action, 204 &i.Rank, 205 &i.Confidence, 206 &i.ValueScore, 207 &i.Rationale, 208 &i.RationaleSource, 209 &i.Evidence, 210 &i.Status, 211 &i.DecidedBy, 212 &i.DecidedAt, 213 &i.DecisionNote, 214 &i.CreatedAt, 215 ) 216 return i, err 217} 218 219const listCommitments = `-- name: ListCommitments :many 220SELECT sm.candidate_id AS candidate_id, sl.requirement_id AS requirement_id, 'slate'::text AS source 221FROM slate_members sm 222JOIN slates sl ON sl.id = sm.slate_id AND sl.organization_id = sm.organization_id 223WHERE sm.decision = 'approved' 224UNION 225SELECT sr.candidate_id AS candidate_id, sr.requirement_id AS requirement_id, 'provisional'::text AS source 226FROM staffing_recommendations sr 227WHERE sr.action = 'assign' AND sr.status = 'approved' AND sr.candidate_id IS NOT NULL 228` 229 230type ListCommitmentsRow struct { 231 CandidateID uuid.UUID `json:"candidate_id"` 232 RequirementID uuid.UUID `json:"requirement_id"` 233 Source string `json:"source"` 234} 235 236// A candidate is committed to a requirement when they are an approved slate member (the source of truth) 237// or have an approved W3 ASSIGN that has not yet flowed to a slate (provisional commitment). The gap view 238// reads this so a committed candidate is classified REASSIGN, never a duplicate ASSIGN. 239func (q *Queries) ListCommitments(ctx context.Context) ([]ListCommitmentsRow, error) { 240 rows, err := q.db.Query(ctx, listCommitments) 241 if err != nil { 242 return nil, err 243 } 244 defer rows.Close() 245 items := []ListCommitmentsRow{} 246 for rows.Next() { 247 var i ListCommitmentsRow 248 if err := rows.Scan(&i.CandidateID, &i.RequirementID, &i.Source); err != nil { 249 return nil, err 250 } 251 items = append(items, i) 252 } 253 if err := rows.Err(); err != nil { 254 return nil, err 255 } 256 return items, nil 257} 258 259const listOpenRequirementsWithValue = `-- name: ListOpenRequirementsWithValue :many 260SELECT 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, o.value AS opportunity_value, o.value_source AS opportunity_value_source 261FROM requirements r 262LEFT JOIN opportunities o ON o.id = r.opportunity_id AND o.organization_id = r.organization_id 263WHERE r.deleted_at IS NULL 264 AND (r.opportunity_id IS NULL OR o.status = 'open') 265ORDER BY r.id 266LIMIT $1 OFFSET $2 267` 268 269type ListOpenRequirementsWithValueParams struct { 270 Limit int32 `json:"limit"` 271 Offset int32 `json:"offset"` 272} 273 274type ListOpenRequirementsWithValueRow struct { 275 ID uuid.UUID `json:"id"` 276 OrganizationID uuid.UUID `json:"organization_id"` 277 OpportunityID *uuid.UUID `json:"opportunity_id"` 278 Title string `json:"title"` 279 CustomerAgency *string `json:"customer_agency"` 280 RequiredLevel string `json:"required_level"` 281 RequiredPoly string `json:"required_poly"` 282 RequiredYears int32 `json:"required_years"` 283 WorkLocation *string `json:"work_location"` 284 RemoteAllowed bool `json:"remote_allowed"` 285 KeyPersonnel bool `json:"key_personnel"` 286 CreatedAt time.Time `json:"created_at"` 287 UpdatedAt time.Time `json:"updated_at"` 288 DeletedAt *time.Time `json:"deleted_at"` 289 LaborCategory *string `json:"labor_category"` 290 Quantity int32 `json:"quantity"` 291 OpportunityValue *decimal.Decimal `json:"opportunity_value"` 292 OpportunityValueSource *string `json:"opportunity_value_source"` 293} 294 295// Open requirements with their backing opportunity dollar value and its provenance, for the portfolio 296// value function. A standalone requirement (no opportunity) has a null value and is handled by the 297// documented value fallback in the optimizer. 298func (q *Queries) ListOpenRequirementsWithValue(ctx context.Context, arg ListOpenRequirementsWithValueParams) ([]ListOpenRequirementsWithValueRow, error) { 299 rows, err := q.db.Query(ctx, listOpenRequirementsWithValue, arg.Limit, arg.Offset) 300 if err != nil { 301 return nil, err 302 } 303 defer rows.Close() 304 items := []ListOpenRequirementsWithValueRow{} 305 for rows.Next() { 306 var i ListOpenRequirementsWithValueRow 307 if err := rows.Scan( 308 &i.ID, 309 &i.OrganizationID, 310 &i.OpportunityID, 311 &i.Title, 312 &i.CustomerAgency, 313 &i.RequiredLevel, 314 &i.RequiredPoly, 315 &i.RequiredYears, 316 &i.WorkLocation, 317 &i.RemoteAllowed, 318 &i.KeyPersonnel, 319 &i.CreatedAt, 320 &i.UpdatedAt, 321 &i.DeletedAt, 322 &i.LaborCategory, 323 &i.Quantity, 324 &i.OpportunityValue, 325 &i.OpportunityValueSource, 326 ); err != nil { 327 return nil, err 328 } 329 items = append(items, i) 330 } 331 if err := rows.Err(); err != nil { 332 return nil, err 333 } 334 return items, nil 335} 336 337const listProactiveRecommendations = `-- name: ListProactiveRecommendations :many 338SELECT 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 339JOIN recommendation_runs rr ON rr.id = sr.run_id AND rr.organization_id = sr.organization_id 340WHERE rr.view = 'proactive' AND sr.status = 'pending' 341ORDER BY sr.candidate_id, sr.rank, sr.id 342LIMIT $1 OFFSET $2 343` 344 345type ListProactiveRecommendationsParams struct { 346 Limit int32 `json:"limit"` 347 Offset int32 `json:"offset"` 348} 349 350// The current bench watch: pending recommendations from proactive runs, capped and paginated. 351func (q *Queries) ListProactiveRecommendations(ctx context.Context, arg ListProactiveRecommendationsParams) ([]StaffingRecommendation, error) { 352 rows, err := q.db.Query(ctx, listProactiveRecommendations, arg.Limit, arg.Offset) 353 if err != nil { 354 return nil, err 355 } 356 defer rows.Close() 357 items := []StaffingRecommendation{} 358 for rows.Next() { 359 var i StaffingRecommendation 360 if err := rows.Scan( 361 &i.ID, 362 &i.OrganizationID, 363 &i.RunID, 364 &i.RequirementID, 365 &i.CandidateID, 366 &i.Action, 367 &i.Rank, 368 &i.Confidence, 369 &i.ValueScore, 370 &i.Rationale, 371 &i.RationaleSource, 372 &i.Evidence, 373 &i.Status, 374 &i.DecidedBy, 375 &i.DecidedAt, 376 &i.DecisionNote, 377 &i.CreatedAt, 378 ); err != nil { 379 return nil, err 380 } 381 items = append(items, i) 382 } 383 if err := rows.Err(); err != nil { 384 return nil, err 385 } 386 return items, nil 387} 388 389const markRecommendationDecision = `-- name: MarkRecommendationDecision :one 390UPDATE staffing_recommendations 391SET status = $2, decided_by = $3, decided_at = now(), decision_note = $4 392WHERE id = $1 AND status = 'pending' 393RETURNING 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 394` 395 396type MarkRecommendationDecisionParams struct { 397 ID uuid.UUID `json:"id"` 398 Status string `json:"status"` 399 DecidedBy *uuid.UUID `json:"decided_by"` 400 DecisionNote *string `json:"decision_note"` 401} 402 403// Status guarded: only a pending row can be decided, so a row that was concurrently superseded or 404// decided returns no row and the service surfaces 409 rather than overwriting a dead or decided row. 405func (q *Queries) MarkRecommendationDecision(ctx context.Context, arg MarkRecommendationDecisionParams) (StaffingRecommendation, error) { 406 row := q.db.QueryRow(ctx, markRecommendationDecision, 407 arg.ID, 408 arg.Status, 409 arg.DecidedBy, 410 arg.DecisionNote, 411 ) 412 var i StaffingRecommendation 413 err := row.Scan( 414 &i.ID, 415 &i.OrganizationID, 416 &i.RunID, 417 &i.RequirementID, 418 &i.CandidateID, 419 &i.Action, 420 &i.Rank, 421 &i.Confidence, 422 &i.ValueScore, 423 &i.Rationale, 424 &i.RationaleSource, 425 &i.Evidence, 426 &i.Status, 427 &i.DecidedBy, 428 &i.DecidedAt, 429 &i.DecisionNote, 430 &i.CreatedAt, 431 ) 432 return i, err 433} 434 435const supersedePendingGap = `-- name: SupersedePendingGap :exec 436UPDATE staffing_recommendations sr 437SET status = 'superseded' 438FROM recommendation_runs rr 439WHERE sr.run_id = rr.id 440 AND sr.organization_id = rr.organization_id 441 AND rr.view = 'gap' 442 AND sr.requirement_id = $1 443 AND sr.status = 'pending' 444` 445 446// A fresh gap run for a requirement supersedes ONLY the prior pending gap rows for that requirement. 447// Decided rows (approved, rejected, dismissed) are immutable and are never touched. 448func (q *Queries) SupersedePendingGap(ctx context.Context, requirementID uuid.UUID) error { 449 _, err := q.db.Exec(ctx, supersedePendingGap, requirementID) 450 return err 451} 452 453const supersedePendingPortfolio = `-- name: SupersedePendingPortfolio :exec 454UPDATE staffing_recommendations sr 455SET status = 'superseded' 456FROM recommendation_runs rr 457WHERE sr.run_id = rr.id 458 AND sr.organization_id = rr.organization_id 459 AND rr.view = 'portfolio' 460 AND sr.status = 'pending' 461` 462 463// A fresh portfolio run supersedes ONLY the prior pending portfolio rows for the org. Decided rows are 464// immutable. 465func (q *Queries) SupersedePendingPortfolio(ctx context.Context) error { 466 _, err := q.db.Exec(ctx, supersedePendingPortfolio) 467 return err 468} 469 470const supersedePendingProactive = `-- name: SupersedePendingProactive :exec 471UPDATE staffing_recommendations sr 472SET status = 'superseded' 473FROM recommendation_runs rr 474WHERE sr.run_id = rr.id 475 AND sr.organization_id = rr.organization_id 476 AND rr.view = 'proactive' 477 AND sr.candidate_id = $1 478 AND sr.status = 'pending' 479` 480 481// A fresh proactive recompute for one candidate supersedes ONLY that candidate's prior pending proactive 482// rows. Decided rows are immutable. 483func (q *Queries) SupersedePendingProactive(ctx context.Context, candidateID *uuid.UUID) error { 484 _, err := q.db.Exec(ctx, supersedePendingProactive, candidateID) 485 return err 486} 487