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

1// Code generated by sqlc. DO NOT EDIT. 2// versions: 3// sqlc v1.31.1 4// source: mission_tags.sql 5 6package dbgen 7 8import ( 9 "context" 10 11 "github.com/google/uuid" 12 "github.com/jackc/pgx/v5/pgtype" 13 "github.com/shopspring/decimal" 14) 15 16const listAllOrganizationIDs = `-- name: ListAllOrganizationIDs :many 17SELECT id FROM organizations ORDER BY id 18` 19 20// Every organization id, ordered by id for a byte stable backfill iteration. Read by the BYPASSRLS 21// migrator role in the tag-missions backfill ONLY to enumerate which tenant contexts to run; the tag 22// writes themselves run as the application role inside each org's RunInTenantTx, never the migrator bypass. 23func (q *Queries) ListAllOrganizationIDs(ctx context.Context) ([]uuid.UUID, error) { 24 rows, err := q.db.Query(ctx, listAllOrganizationIDs) 25 if err != nil { 26 return nil, err 27 } 28 defer rows.Close() 29 items := []uuid.UUID{} 30 for rows.Next() { 31 var id uuid.UUID 32 if err := rows.Scan(&id); err != nil { 33 return nil, err 34 } 35 items = append(items, id) 36 } 37 if err := rows.Err(); err != nil { 38 return nil, err 39 } 40 return items, nil 41} 42 43const listAwardsForClassify = `-- name: ListAwardsForClassify :many 44SELECT generated_internal_id AS record_id, description, naics_description, naics_code 45FROM usaspending_awards 46ORDER BY generated_internal_id 47` 48 49type ListAwardsForClassifyRow struct { 50 RecordID string `json:"record_id"` 51 Description string `json:"description"` 52 NaicsDescription string `json:"naics_description"` 53 NaicsCode string `json:"naics_code"` 54} 55 56// Minimal projection of the global usaspending_awards catalog for the missionclass sync pass: the 57// generated_internal_id (the record_id the tag and incumbent role tables key on), plus the description, 58// naics_description, and naics_code the classifier reads. Ordered by generated_internal_id for byte 59// stable iteration. 60func (q *Queries) ListAwardsForClassify(ctx context.Context) ([]ListAwardsForClassifyRow, error) { 61 rows, err := q.db.Query(ctx, listAwardsForClassify) 62 if err != nil { 63 return nil, err 64 } 65 defer rows.Close() 66 items := []ListAwardsForClassifyRow{} 67 for rows.Next() { 68 var i ListAwardsForClassifyRow 69 if err := rows.Scan( 70 &i.RecordID, 71 &i.Description, 72 &i.NaicsDescription, 73 &i.NaicsCode, 74 ); err != nil { 75 return nil, err 76 } 77 items = append(items, i) 78 } 79 if err := rows.Err(); err != nil { 80 return nil, err 81 } 82 return items, nil 83} 84 85const listCandidatesByMission = `-- name: ListCandidatesByMission :many 86SELECT c.id, c.organization_id, c.full_name, c.email, c.headline, c.clearance_level, c.clearance_poly, c.clearance_status, c.clearance_granted_at, c.clearance_expires_at, c.reinvestigation_due_at, c.years_experience, c.location, c.remote, c.relocatable, c.available, c.resume_embedding, c.search_tsv, c.created_at, c.updated_at, c.deleted_at, c.source, c.external_id, c.anonymized_at, c.clearance_provenance, c.clearance_verified_at, c.clearance_compartments, c.source_first_touch, c.source_last_touch, c.source_detail 87FROM candidates c 88JOIN candidate_mission_tags t ON t.candidate_id = c.id 89WHERE c.deleted_at IS NULL AND t.mission_code = $1 90ORDER BY c.created_at DESC, c.id 91LIMIT $2 92` 93 94type ListCandidatesByMissionParams struct { 95 MissionCode string `json:"mission_code"` 96 RowLimit int32 `json:"row_limit"` 97} 98 99// Tenant candidates tagged with a given mission code, RLS scoped to the caller's org through the join on 100// candidate_mission_tags. id is the trailing unique sort key so created_at ties give a total order and 101// stable LIMIT paging, the AEDT reproducibility mandate. 102func (q *Queries) ListCandidatesByMission(ctx context.Context, arg ListCandidatesByMissionParams) ([]Candidate, error) { 103 rows, err := q.db.Query(ctx, listCandidatesByMission, arg.MissionCode, arg.RowLimit) 104 if err != nil { 105 return nil, err 106 } 107 defer rows.Close() 108 items := []Candidate{} 109 for rows.Next() { 110 var i Candidate 111 if err := rows.Scan( 112 &i.ID, 113 &i.OrganizationID, 114 &i.FullName, 115 &i.Email, 116 &i.Headline, 117 &i.ClearanceLevel, 118 &i.ClearancePoly, 119 &i.ClearanceStatus, 120 &i.ClearanceGrantedAt, 121 &i.ClearanceExpiresAt, 122 &i.ReinvestigationDueAt, 123 &i.YearsExperience, 124 &i.Location, 125 &i.Remote, 126 &i.Relocatable, 127 &i.Available, 128 &i.ResumeEmbedding, 129 &i.SearchTsv, 130 &i.CreatedAt, 131 &i.UpdatedAt, 132 &i.DeletedAt, 133 &i.Source, 134 &i.ExternalID, 135 &i.AnonymizedAt, 136 &i.ClearanceProvenance, 137 &i.ClearanceVerifiedAt, 138 &i.ClearanceCompartments, 139 &i.SourceFirstTouch, 140 &i.SourceLastTouch, 141 &i.SourceDetail, 142 ); err != nil { 143 return nil, err 144 } 145 items = append(items, i) 146 } 147 if err := rows.Err(); err != nil { 148 return nil, err 149 } 150 return items, nil 151} 152 153const listCandidatesForMissionTagging = `-- name: ListCandidatesForMissionTagging :many 154SELECT c.id AS candidate_id, 155 COALESCE(c.headline, '')::text AS headline, 156 COALESCE(string_agg(s.name, ' ' ORDER BY s.name), '')::text AS skill_text 157FROM candidates c 158LEFT JOIN candidate_skills cs ON cs.candidate_id = c.id 159LEFT JOIN skills s ON s.id = cs.skill_id 160WHERE c.deleted_at IS NULL AND c.anonymized_at IS NULL 161GROUP BY c.id, c.headline 162ORDER BY c.id 163` 164 165type ListCandidatesForMissionTaggingRow struct { 166 CandidateID uuid.UUID `json:"candidate_id"` 167 Headline string `json:"headline"` 168 SkillText string `json:"skill_text"` 169} 170 171// Tenant projection the Phase 5a tagging service classifies: each live candidate id, its headline, and the 172// candidate's skill names joined by single spaces into one text blob. RLS scopes the read to the caller's 173// org through the candidates row, and the LEFT JOIN keeps a candidate with no skills (the headline still 174// classifies). headline is coerced to empty text so a NULL never aborts the aggregation. Ordered by id so 175// the pass iterates byte stably and a re run is reproducible, the AEDT mandate. 176func (q *Queries) ListCandidatesForMissionTagging(ctx context.Context) ([]ListCandidatesForMissionTaggingRow, error) { 177 rows, err := q.db.Query(ctx, listCandidatesForMissionTagging) 178 if err != nil { 179 return nil, err 180 } 181 defer rows.Close() 182 items := []ListCandidatesForMissionTaggingRow{} 183 for rows.Next() { 184 var i ListCandidatesForMissionTaggingRow 185 if err := rows.Scan(&i.CandidateID, &i.Headline, &i.SkillText); err != nil { 186 return nil, err 187 } 188 items = append(items, i) 189 } 190 if err := rows.Err(); err != nil { 191 return nil, err 192 } 193 return items, nil 194} 195 196const listIncumbentRolesByRecompete = `-- name: ListIncumbentRolesByRecompete :many 197SELECT a.generated_internal_id AS record_id, 198 a.recipient_name AS recipient_name, 199 a.awarding_agency AS awarding_agency, 200 a.naics_code AS naics_code, 201 a.award_amount AS award_amount, 202 a.pop_end_date AS pop_end_date, 203 r.labor_category AS labor_category, 204 r.role_count AS role_count, 205 r.evidence AS evidence 206FROM award_incumbent_roles r 207JOIN usaspending_awards a ON a.generated_internal_id = r.record_id 208WHERE r.record_table = 'usaspending_awards' 209 AND ($1::date IS NULL OR a.pop_end_date <= $1::date) 210ORDER BY a.pop_end_date ASC NULLS LAST, a.generated_internal_id, r.labor_category 211LIMIT $2::int 212` 213 214type ListIncumbentRolesByRecompeteParams struct { 215 PopEndBefore pgtype.Date `json:"pop_end_before"` 216 RowLimit int32 `json:"row_limit"` 217} 218 219type ListIncumbentRolesByRecompeteRow struct { 220 RecordID string `json:"record_id"` 221 RecipientName string `json:"recipient_name"` 222 AwardingAgency string `json:"awarding_agency"` 223 NaicsCode string `json:"naics_code"` 224 AwardAmount *decimal.Decimal `json:"award_amount"` 225 PopEndDate pgtype.Date `json:"pop_end_date"` 226 LaborCategory string `json:"labor_category"` 227 RoleCount int32 `json:"role_count"` 228 Evidence string `json:"evidence"` 229} 230 231// Incumbent labor mix mined from award text, joined to its parent USASpending award so each role carries 232// the award identity (recipient_name, pop_end_date) and its evidence phrase, the traceability the WS7 233// acceptance criterion requires. The recompete signal is pop_end_date: awards whose period of performance 234// is ending are the recompete relevant ones, so rows are ordered by pop_end_date soonest first, then by 235// the award generated_internal_id and the labor_category as trailing unique tiebreakers for byte stable 236// output, the AEDT reproducibility mandate. Awards with no pop_end_date sort last. Global reference data, 237// app SELECT only. The optional pop_end_before bound (NULL means no bound) lets the caller scope to a 238// recompete window without changing the default which returns the soonest ending awards first. 239func (q *Queries) ListIncumbentRolesByRecompete(ctx context.Context, arg ListIncumbentRolesByRecompeteParams) ([]ListIncumbentRolesByRecompeteRow, error) { 240 rows, err := q.db.Query(ctx, listIncumbentRolesByRecompete, arg.PopEndBefore, arg.RowLimit) 241 if err != nil { 242 return nil, err 243 } 244 defer rows.Close() 245 items := []ListIncumbentRolesByRecompeteRow{} 246 for rows.Next() { 247 var i ListIncumbentRolesByRecompeteRow 248 if err := rows.Scan( 249 &i.RecordID, 250 &i.RecipientName, 251 &i.AwardingAgency, 252 &i.NaicsCode, 253 &i.AwardAmount, 254 &i.PopEndDate, 255 &i.LaborCategory, 256 &i.RoleCount, 257 &i.Evidence, 258 ); err != nil { 259 return nil, err 260 } 261 items = append(items, i) 262 } 263 if err := rows.Err(); err != nil { 264 return nil, err 265 } 266 return items, nil 267} 268 269const listMissionTagsForRecord = `-- name: ListMissionTagsForRecord :many 270SELECT record_table, record_id, mission_code, mechanism, evidence, confidence, classified_at 271FROM federal_mission_tags 272WHERE record_table = $1 AND record_id = $2 273ORDER BY mission_code, record_id 274` 275 276type ListMissionTagsForRecordParams struct { 277 RecordTable string `json:"record_table"` 278 RecordID string `json:"record_id"` 279} 280 281// All mission tags for one global record, ordered by mission code with the record id as a trailing unique 282// tiebreaker so the output is byte stable for fixed inputs, the AEDT reproducibility mandate. 283func (q *Queries) ListMissionTagsForRecord(ctx context.Context, arg ListMissionTagsForRecordParams) ([]FederalMissionTag, error) { 284 rows, err := q.db.Query(ctx, listMissionTagsForRecord, arg.RecordTable, arg.RecordID) 285 if err != nil { 286 return nil, err 287 } 288 defer rows.Close() 289 items := []FederalMissionTag{} 290 for rows.Next() { 291 var i FederalMissionTag 292 if err := rows.Scan( 293 &i.RecordTable, 294 &i.RecordID, 295 &i.MissionCode, 296 &i.Mechanism, 297 &i.Evidence, 298 &i.Confidence, 299 &i.ClassifiedAt, 300 ); err != nil { 301 return nil, err 302 } 303 items = append(items, i) 304 } 305 if err := rows.Err(); err != nil { 306 return nil, err 307 } 308 return items, nil 309} 310 311const listOpportunityRecordsForClassify = `-- name: ListOpportunityRecordsForClassify :many 312SELECT id::text AS record_id, title, psc, naics 313FROM opportunity_records 314ORDER BY id 315` 316 317type ListOpportunityRecordsForClassifyRow struct { 318 RecordID string `json:"record_id"` 319 Title string `json:"title"` 320 Psc string `json:"psc"` 321 Naics string `json:"naics"` 322} 323 324// Minimal projection of the global opportunity_records catalog for the missionclass sync pass: the id 325// carried as text (the record_id federal_mission_tags keys on), plus the title, psc, and naics the 326// deterministic classifier reads. Ordered by id so the pass iterates byte stably and a re run is 327// reproducible, the AEDT mandate. Migrator role reads the global catalog directly. 328func (q *Queries) ListOpportunityRecordsForClassify(ctx context.Context) ([]ListOpportunityRecordsForClassifyRow, error) { 329 rows, err := q.db.Query(ctx, listOpportunityRecordsForClassify) 330 if err != nil { 331 return nil, err 332 } 333 defer rows.Close() 334 items := []ListOpportunityRecordsForClassifyRow{} 335 for rows.Next() { 336 var i ListOpportunityRecordsForClassifyRow 337 if err := rows.Scan( 338 &i.RecordID, 339 &i.Title, 340 &i.Psc, 341 &i.Naics, 342 ); err != nil { 343 return nil, err 344 } 345 items = append(items, i) 346 } 347 if err := rows.Err(); err != nil { 348 return nil, err 349 } 350 return items, nil 351} 352 353const listRequirementsForMissionTagging = `-- name: ListRequirementsForMissionTagging :many 354SELECT r.id AS requirement_id, 355 r.title::text AS title, 356 COALESCE(r.labor_category, '')::text AS labor_category, 357 COALESCE(string_agg(s.name, ' ' ORDER BY s.name), '')::text AS skill_text 358FROM requirements r 359LEFT JOIN required_skills rs ON rs.requirement_id = r.id 360LEFT JOIN skills s ON s.id = rs.skill_id 361WHERE r.deleted_at IS NULL 362GROUP BY r.id, r.title, r.labor_category 363ORDER BY r.id 364` 365 366type ListRequirementsForMissionTaggingRow struct { 367 RequirementID uuid.UUID `json:"requirement_id"` 368 Title string `json:"title"` 369 LaborCategory string `json:"labor_category"` 370 SkillText string `json:"skill_text"` 371} 372 373// Tenant projection the Phase 5a tagging service classifies for requirements: each live requirement id, its 374// title, its labor_category (coerced to empty when NULL), and the required skill names joined by single 375// spaces. RLS scopes the read to the caller's org; the LEFT JOIN keeps a requirement with no required 376// skills (the title and labor category still classify). Ordered by id for byte stable, reproducible 377// iteration, the AEDT mandate. 378func (q *Queries) ListRequirementsForMissionTagging(ctx context.Context) ([]ListRequirementsForMissionTaggingRow, error) { 379 rows, err := q.db.Query(ctx, listRequirementsForMissionTagging) 380 if err != nil { 381 return nil, err 382 } 383 defer rows.Close() 384 items := []ListRequirementsForMissionTaggingRow{} 385 for rows.Next() { 386 var i ListRequirementsForMissionTaggingRow 387 if err := rows.Scan( 388 &i.RequirementID, 389 &i.Title, 390 &i.LaborCategory, 391 &i.SkillText, 392 ); err != nil { 393 return nil, err 394 } 395 items = append(items, i) 396 } 397 if err := rows.Err(); err != nil { 398 return nil, err 399 } 400 return items, nil 401} 402 403const listSamForClassify = `-- name: ListSamForClassify :many 404SELECT notice_id AS record_id, description_url, resource_links 405FROM sam_opportunities 406ORDER BY notice_id 407` 408 409type ListSamForClassifyRow struct { 410 RecordID string `json:"record_id"` 411 DescriptionUrl string `json:"description_url"` 412 ResourceLinks []byte `json:"resource_links"` 413} 414 415// Minimal projection of the cached sam_opportunities catalog for the missionclass sync pass: the 416// notice_id (the record_id award_incumbent_roles keys on for a SAM notice), plus the description_url and 417// the resource_links the attachment fetcher needs to mine the incumbent labor mix. Ordered by notice_id 418// for byte stable iteration. 419func (q *Queries) ListSamForClassify(ctx context.Context) ([]ListSamForClassifyRow, error) { 420 rows, err := q.db.Query(ctx, listSamForClassify) 421 if err != nil { 422 return nil, err 423 } 424 defer rows.Close() 425 items := []ListSamForClassifyRow{} 426 for rows.Next() { 427 var i ListSamForClassifyRow 428 if err := rows.Scan(&i.RecordID, &i.DescriptionUrl, &i.ResourceLinks); err != nil { 429 return nil, err 430 } 431 items = append(items, i) 432 } 433 if err := rows.Err(); err != nil { 434 return nil, err 435 } 436 return items, nil 437} 438 439const missionAliasPairs = `-- name: MissionAliasPairs :many 440SELECT a.alias_text AS phrase, m.code AS code 441FROM skill_aliases a 442JOIN skills s ON s.id = a.skill_id AND s.category = 'mission_area' 443JOIN mission_areas m ON m.spine_term = s.name 444UNION 445SELECT s.name AS phrase, m.code AS code 446FROM skills s 447JOIN mission_areas m ON m.spine_term = s.name 448WHERE s.category = 'mission_area' 449ORDER BY phrase, code 450` 451 452type MissionAliasPairsRow struct { 453 Phrase string `json:"phrase"` 454 Code string `json:"code"` 455} 456 457// (phrase, code) pairs that seed the missionclass AliasIndex from real rows. Two unioned sources: every 458// curated alias whose target skill is a category 'mission_area' skill, and every mission_area skill name 459// itself. The skill name to code link is mission_areas.spine_term = skills.name, because the mission_area 460// skills were promoted from the mission_spine terms the mission_areas rows carry. Ordered by phrase then 461// code for a deterministic build. Global reference data, read by the migrator role. 462func (q *Queries) MissionAliasPairs(ctx context.Context) ([]MissionAliasPairsRow, error) { 463 rows, err := q.db.Query(ctx, missionAliasPairs) 464 if err != nil { 465 return nil, err 466 } 467 defer rows.Close() 468 items := []MissionAliasPairsRow{} 469 for rows.Next() { 470 var i MissionAliasPairsRow 471 if err := rows.Scan(&i.Phrase, &i.Code); err != nil { 472 return nil, err 473 } 474 items = append(items, i) 475 } 476 if err := rows.Err(); err != nil { 477 return nil, err 478 } 479 return items, nil 480} 481 482const upsertAwardIncumbentRole = `-- name: UpsertAwardIncumbentRole :exec 483INSERT INTO award_incumbent_roles (record_table, record_id, labor_category, role_count, evidence) 484VALUES ( 485 $1, 486 $2, 487 $3, 488 $4, 489 $5 490) 491ON CONFLICT (record_table, record_id, labor_category) DO UPDATE SET 492 role_count = EXCLUDED.role_count, 493 evidence = EXCLUDED.evidence, 494 classified_at = now() 495` 496 497type UpsertAwardIncumbentRoleParams struct { 498 RecordTable string `json:"record_table"` 499 RecordID string `json:"record_id"` 500 LaborCategory string `json:"labor_category"` 501 RoleCount int32 `json:"role_count"` 502 Evidence string `json:"evidence"` 503} 504 505// Idempotent write of an incumbent labor mix row mined from award and solicitation text. ON CONFLICT DO 506// UPDATE so a re run refreshes the count and evidence for the same record and labor category. 507func (q *Queries) UpsertAwardIncumbentRole(ctx context.Context, arg UpsertAwardIncumbentRoleParams) error { 508 _, err := q.db.Exec(ctx, upsertAwardIncumbentRole, 509 arg.RecordTable, 510 arg.RecordID, 511 arg.LaborCategory, 512 arg.RoleCount, 513 arg.Evidence, 514 ) 515 return err 516} 517 518const upsertCandidateMissionTag = `-- name: UpsertCandidateMissionTag :exec 519INSERT INTO candidate_mission_tags (organization_id, candidate_id, mission_code, mechanism, evidence) 520VALUES ( 521 $1, 522 $2, 523 $3, 524 $4, 525 $5 526) 527ON CONFLICT (candidate_id, mission_code) DO UPDATE SET 528 mechanism = EXCLUDED.mechanism, 529 evidence = EXCLUDED.evidence 530` 531 532type UpsertCandidateMissionTagParams struct { 533 OrganizationID uuid.UUID `json:"organization_id"` 534 CandidateID uuid.UUID `json:"candidate_id"` 535 MissionCode string `json:"mission_code"` 536 Mechanism string `json:"mechanism"` 537 Evidence string `json:"evidence"` 538} 539 540// Idempotent write of a tenant candidate mission tag. RLS scopes the write to the caller's org and the 541// composite FK guarantees the candidate belongs to that org. ON CONFLICT DO UPDATE keeps the sync pass 542// repeatable, refreshing the mechanism and evidence for an existing candidate and mission code pair. 543func (q *Queries) UpsertCandidateMissionTag(ctx context.Context, arg UpsertCandidateMissionTagParams) error { 544 _, err := q.db.Exec(ctx, upsertCandidateMissionTag, 545 arg.OrganizationID, 546 arg.CandidateID, 547 arg.MissionCode, 548 arg.Mechanism, 549 arg.Evidence, 550 ) 551 return err 552} 553 554const upsertFederalMissionTag = `-- name: UpsertFederalMissionTag :exec 555INSERT INTO federal_mission_tags (record_table, record_id, mission_code, mechanism, evidence, confidence) 556VALUES ( 557 $1, 558 $2, 559 $3, 560 $4, 561 $5, 562 $6 563) 564ON CONFLICT (record_table, record_id, mission_code) DO UPDATE SET 565 mechanism = EXCLUDED.mechanism, 566 evidence = EXCLUDED.evidence, 567 confidence = EXCLUDED.confidence, 568 classified_at = now() 569` 570 571type UpsertFederalMissionTagParams struct { 572 RecordTable string `json:"record_table"` 573 RecordID string `json:"record_id"` 574 MissionCode string `json:"mission_code"` 575 Mechanism string `json:"mechanism"` 576 Evidence string `json:"evidence"` 577 Confidence decimal.Decimal `json:"confidence"` 578} 579 580// Idempotent write of a global mission tag from the classification sync pass. ON CONFLICT DO UPDATE so a 581// re run with the same record and mission code refreshes the mechanism, evidence, and confidence rather 582// than erroring, which keeps the sync pass safely repeatable. Migrator owned, app SELECT only. 583func (q *Queries) UpsertFederalMissionTag(ctx context.Context, arg UpsertFederalMissionTagParams) error { 584 _, err := q.db.Exec(ctx, upsertFederalMissionTag, 585 arg.RecordTable, 586 arg.RecordID, 587 arg.MissionCode, 588 arg.Mechanism, 589 arg.Evidence, 590 arg.Confidence, 591 ) 592 return err 593} 594 595const upsertRequirementMissionTag = `-- name: UpsertRequirementMissionTag :exec 596INSERT INTO requirement_mission_tags (organization_id, requirement_id, mission_code, mechanism, evidence) 597VALUES ( 598 $1, 599 $2, 600 $3, 601 $4, 602 $5 603) 604ON CONFLICT (requirement_id, mission_code) DO UPDATE SET 605 mechanism = EXCLUDED.mechanism, 606 evidence = EXCLUDED.evidence 607` 608 609type UpsertRequirementMissionTagParams struct { 610 OrganizationID uuid.UUID `json:"organization_id"` 611 RequirementID uuid.UUID `json:"requirement_id"` 612 MissionCode string `json:"mission_code"` 613 Mechanism string `json:"mechanism"` 614 Evidence string `json:"evidence"` 615} 616 617// Idempotent write of a tenant requirement mission tag, same isolation guarantees as the candidate path. 618func (q *Queries) UpsertRequirementMissionTag(ctx context.Context, arg UpsertRequirementMissionTagParams) error { 619 _, err := q.db.Exec(ctx, upsertRequirementMissionTag, 620 arg.OrganizationID, 621 arg.RequirementID, 622 arg.MissionCode, 623 arg.Mechanism, 624 arg.Evidence, 625 ) 626 return err 627} 628