Janady13/missionmatch / db/queries/mission_tags.sql

1-- name: UpsertFederalMissionTag :exec 2-- Idempotent write of a global mission tag from the classification sync pass. ON CONFLICT DO UPDATE so a 3-- re run with the same record and mission code refreshes the mechanism, evidence, and confidence rather 4-- than erroring, which keeps the sync pass safely repeatable. Migrator owned, app SELECT only. 5INSERT INTO federal_mission_tags (record_table, record_id, mission_code, mechanism, evidence, confidence) 6VALUES ( 7 sqlc.arg(record_table), 8 sqlc.arg(record_id), 9 sqlc.arg(mission_code), 10 sqlc.arg(mechanism), 11 sqlc.arg(evidence), 12 sqlc.arg(confidence) 13) 14ON CONFLICT (record_table, record_id, mission_code) DO UPDATE SET 15 mechanism = EXCLUDED.mechanism, 16 evidence = EXCLUDED.evidence, 17 confidence = EXCLUDED.confidence, 18 classified_at = now(); 19 20-- name: UpsertAwardIncumbentRole :exec 21-- Idempotent write of an incumbent labor mix row mined from award and solicitation text. ON CONFLICT DO 22-- UPDATE so a re run refreshes the count and evidence for the same record and labor category. 23INSERT INTO award_incumbent_roles (record_table, record_id, labor_category, role_count, evidence) 24VALUES ( 25 sqlc.arg(record_table), 26 sqlc.arg(record_id), 27 sqlc.arg(labor_category), 28 sqlc.arg(role_count), 29 sqlc.arg(evidence) 30) 31ON CONFLICT (record_table, record_id, labor_category) DO UPDATE SET 32 role_count = EXCLUDED.role_count, 33 evidence = EXCLUDED.evidence, 34 classified_at = now(); 35 36-- name: UpsertCandidateMissionTag :exec 37-- Idempotent write of a tenant candidate mission tag. RLS scopes the write to the caller's org and the 38-- composite FK guarantees the candidate belongs to that org. ON CONFLICT DO UPDATE keeps the sync pass 39-- repeatable, refreshing the mechanism and evidence for an existing candidate and mission code pair. 40INSERT INTO candidate_mission_tags (organization_id, candidate_id, mission_code, mechanism, evidence) 41VALUES ( 42 sqlc.arg(organization_id), 43 sqlc.arg(candidate_id), 44 sqlc.arg(mission_code), 45 sqlc.arg(mechanism), 46 sqlc.arg(evidence) 47) 48ON CONFLICT (candidate_id, mission_code) DO UPDATE SET 49 mechanism = EXCLUDED.mechanism, 50 evidence = EXCLUDED.evidence; 51 52-- name: UpsertRequirementMissionTag :exec 53-- Idempotent write of a tenant requirement mission tag, same isolation guarantees as the candidate path. 54INSERT INTO requirement_mission_tags (organization_id, requirement_id, mission_code, mechanism, evidence) 55VALUES ( 56 sqlc.arg(organization_id), 57 sqlc.arg(requirement_id), 58 sqlc.arg(mission_code), 59 sqlc.arg(mechanism), 60 sqlc.arg(evidence) 61) 62ON CONFLICT (requirement_id, mission_code) DO UPDATE SET 63 mechanism = EXCLUDED.mechanism, 64 evidence = EXCLUDED.evidence; 65 66-- name: ListMissionTagsForRecord :many 67-- All mission tags for one global record, ordered by mission code with the record id as a trailing unique 68-- tiebreaker so the output is byte stable for fixed inputs, the AEDT reproducibility mandate. 69SELECT record_table, record_id, mission_code, mechanism, evidence, confidence, classified_at 70FROM federal_mission_tags 71WHERE record_table = sqlc.arg(record_table) AND record_id = sqlc.arg(record_id) 72ORDER BY mission_code, record_id; 73 74-- name: ListCandidatesByMission :many 75-- Tenant candidates tagged with a given mission code, RLS scoped to the caller's org through the join on 76-- candidate_mission_tags. id is the trailing unique sort key so created_at ties give a total order and 77-- stable LIMIT paging, the AEDT reproducibility mandate. 78SELECT c.* 79FROM candidates c 80JOIN candidate_mission_tags t ON t.candidate_id = c.id 81WHERE c.deleted_at IS NULL AND t.mission_code = sqlc.arg(mission_code) 82ORDER BY c.created_at DESC, c.id 83LIMIT sqlc.arg(row_limit); 84 85-- name: ListOpportunityRecordsForClassify :many 86-- Minimal projection of the global opportunity_records catalog for the missionclass sync pass: the id 87-- carried as text (the record_id federal_mission_tags keys on), plus the title, psc, and naics the 88-- deterministic classifier reads. Ordered by id so the pass iterates byte stably and a re run is 89-- reproducible, the AEDT mandate. Migrator role reads the global catalog directly. 90SELECT id::text AS record_id, title, psc, naics 91FROM opportunity_records 92ORDER BY id; 93 94-- name: ListAwardsForClassify :many 95-- Minimal projection of the global usaspending_awards catalog for the missionclass sync pass: the 96-- generated_internal_id (the record_id the tag and incumbent role tables key on), plus the description, 97-- naics_description, and naics_code the classifier reads. Ordered by generated_internal_id for byte 98-- stable iteration. 99SELECT generated_internal_id AS record_id, description, naics_description, naics_code 100FROM usaspending_awards 101ORDER BY generated_internal_id; 102 103-- name: ListSamForClassify :many 104-- Minimal projection of the cached sam_opportunities catalog for the missionclass sync pass: the 105-- notice_id (the record_id award_incumbent_roles keys on for a SAM notice), plus the description_url and 106-- the resource_links the attachment fetcher needs to mine the incumbent labor mix. Ordered by notice_id 107-- for byte stable iteration. 108SELECT notice_id AS record_id, description_url, resource_links 109FROM sam_opportunities 110ORDER BY notice_id; 111 112-- name: ListIncumbentRolesByRecompete :many 113-- Incumbent labor mix mined from award text, joined to its parent USASpending award so each role carries 114-- the award identity (recipient_name, pop_end_date) and its evidence phrase, the traceability the WS7 115-- acceptance criterion requires. The recompete signal is pop_end_date: awards whose period of performance 116-- is ending are the recompete relevant ones, so rows are ordered by pop_end_date soonest first, then by 117-- the award generated_internal_id and the labor_category as trailing unique tiebreakers for byte stable 118-- output, the AEDT reproducibility mandate. Awards with no pop_end_date sort last. Global reference data, 119-- app SELECT only. The optional pop_end_before bound (NULL means no bound) lets the caller scope to a 120-- recompete window without changing the default which returns the soonest ending awards first. 121SELECT a.generated_internal_id AS record_id, 122 a.recipient_name AS recipient_name, 123 a.awarding_agency AS awarding_agency, 124 a.naics_code AS naics_code, 125 a.award_amount AS award_amount, 126 a.pop_end_date AS pop_end_date, 127 r.labor_category AS labor_category, 128 r.role_count AS role_count, 129 r.evidence AS evidence 130FROM award_incumbent_roles r 131JOIN usaspending_awards a ON a.generated_internal_id = r.record_id 132WHERE r.record_table = 'usaspending_awards' 133 AND (sqlc.narg('pop_end_before')::date IS NULL OR a.pop_end_date <= sqlc.narg('pop_end_before')::date) 134ORDER BY a.pop_end_date ASC NULLS LAST, a.generated_internal_id, r.labor_category 135LIMIT sqlc.arg('row_limit')::int; 136 137-- name: ListCandidatesForMissionTagging :many 138-- Tenant projection the Phase 5a tagging service classifies: each live candidate id, its headline, and the 139-- candidate's skill names joined by single spaces into one text blob. RLS scopes the read to the caller's 140-- org through the candidates row, and the LEFT JOIN keeps a candidate with no skills (the headline still 141-- classifies). headline is coerced to empty text so a NULL never aborts the aggregation. Ordered by id so 142-- the pass iterates byte stably and a re run is reproducible, the AEDT mandate. 143SELECT c.id AS candidate_id, 144 COALESCE(c.headline, '')::text AS headline, 145 COALESCE(string_agg(s.name, ' ' ORDER BY s.name), '')::text AS skill_text 146FROM candidates c 147LEFT JOIN candidate_skills cs ON cs.candidate_id = c.id 148LEFT JOIN skills s ON s.id = cs.skill_id 149WHERE c.deleted_at IS NULL AND c.anonymized_at IS NULL 150GROUP BY c.id, c.headline 151ORDER BY c.id; 152 153-- name: ListRequirementsForMissionTagging :many 154-- Tenant projection the Phase 5a tagging service classifies for requirements: each live requirement id, its 155-- title, its labor_category (coerced to empty when NULL), and the required skill names joined by single 156-- spaces. RLS scopes the read to the caller's org; the LEFT JOIN keeps a requirement with no required 157-- skills (the title and labor category still classify). Ordered by id for byte stable, reproducible 158-- iteration, the AEDT mandate. 159SELECT r.id AS requirement_id, 160 r.title::text AS title, 161 COALESCE(r.labor_category, '')::text AS labor_category, 162 COALESCE(string_agg(s.name, ' ' ORDER BY s.name), '')::text AS skill_text 163FROM requirements r 164LEFT JOIN required_skills rs ON rs.requirement_id = r.id 165LEFT JOIN skills s ON s.id = rs.skill_id 166WHERE r.deleted_at IS NULL 167GROUP BY r.id, r.title, r.labor_category 168ORDER BY r.id; 169 170-- name: ListAllOrganizationIDs :many 171-- Every organization id, ordered by id for a byte stable backfill iteration. Read by the BYPASSRLS 172-- migrator role in the tag-missions backfill ONLY to enumerate which tenant contexts to run; the tag 173-- writes themselves run as the application role inside each org's RunInTenantTx, never the migrator bypass. 174SELECT id FROM organizations ORDER BY id; 175 176-- name: MissionAliasPairs :many 177-- (phrase, code) pairs that seed the missionclass AliasIndex from real rows. Two unioned sources: every 178-- curated alias whose target skill is a category 'mission_area' skill, and every mission_area skill name 179-- itself. The skill name to code link is mission_areas.spine_term = skills.name, because the mission_area 180-- skills were promoted from the mission_spine terms the mission_areas rows carry. Ordered by phrase then 181-- code for a deterministic build. Global reference data, read by the migrator role. 182SELECT a.alias_text AS phrase, m.code AS code 183FROM skill_aliases a 184JOIN skills s ON s.id = a.skill_id AND s.category = 'mission_area' 185JOIN mission_areas m ON m.spine_term = s.name 186UNION 187SELECT s.name AS phrase, m.code AS code 188FROM skills s 189JOIN mission_areas m ON m.spine_term = s.name 190WHERE s.category = 'mission_area' 191ORDER BY phrase, code; 192