Janady13/missionmatch / internal/modules/alerts/repo.go

1package alerts 2 3// repo.go holds the parameterized SQL the service runs under RunInTenantTx (RLS scoped). The statements are 4// raw and parameterized rather than sqlc generated so the alert engine is self contained and does not require 5// regenerating the dbgen package for one new table; this mirrors the bidoutcomes ledger and the AI graph 6// layer, which author their reviewed SQL as static constants. Every statement runs as the NOBYPASSRLS app 7// role inside a tenant transaction, so the organization_id is the session GUC and a row in another org is 8// never visible or writable (the RLS policy plus the composite indexes both hold). The app role has SELECT, 9// INSERT, UPDATE on alerts and no DELETE, so an alert is never destroyed in app code. 10 11// clearanceExpiringSQL is trigger (a): the owner's own candidates whose clearance lapses within the window. 12// RLS scopes the read to the caller's bench, the soft delete and anonymize filters exclude an erased 13// candidate (an anonymized in place row keeps its candidate id, so without the anonymized_at filter an erased 14// person would still surface in the alert title). $1 is the window cutoff date (today plus the window in 15// days, computed in Go so the SQL binds a plain date). The days remaining is computed in SQL as the integer 16// day difference so the title can state it. Deterministic ORDER BY on the expiry date then the id tiebreaker, 17// a total order. Only the owner's own candidate name and the integer days remaining ever leave the read; the 18// title is the owner's own data, never another tenant's. 19const clearanceExpiringSQL = ` 20SELECT 21 c.id::text AS candidate_id, 22 c.full_name AS full_name, 23 (c.clearance_expires_at - CURRENT_DATE)::int AS days_remaining 24FROM candidates c 25WHERE c.deleted_at IS NULL 26 AND c.anonymized_at IS NULL 27 AND c.clearance_expires_at IS NOT NULL 28 AND c.clearance_expires_at <= $1 29ORDER BY c.clearance_expires_at ASC, c.id ASC` 30 31// recompeteWinnableSQL is trigger (b), the MARKET EVENT arm: a recently posted federal opportunity in an 32// agency the owner already shows interest in, whose period of performance ends inside the forward recompete 33// window. opportunity_records is GLOBAL PUBLIC reference data (no organization_id, outside RLS), so reading it 34// is fine; the org INTEREST read (a requirement with that customer_agency, or a bid_outcome linked to a 35// requirement or opportunity in that agency) is RLS scoped, so a foreign org's interest never widens this 36// org's market alerts. The agency match is on the normalized lower(trim(agency)) so a SAM agency string and 37// the owner's stored customer_agency match despite casing or padding. $1 is the posted cutoff date (today 38// minus the recent window), $2 is the pop end cutoff date (today plus the recompete window), so an 39// opportunity qualifies when it was posted on or after $1 and its pop_end_date falls between today and $2. 40// A row with a null pop_end_date is excluded (the recompete window is undefined without an end date). 41// Deterministic ORDER BY on the posted date descending then the id tiebreaker. The title names only the 42// agency and naics the owner already tracks, the owner's own market context, never another tenant's data. 43const recompeteWinnableSQL = ` 44SELECT 45 opr.id::text AS opportunity_record_id, 46 opr.agency AS agency, 47 opr.naics AS naics 48FROM opportunity_records opr 49WHERE opr.agency <> '' 50 AND opr.posted_date IS NOT NULL 51 AND opr.posted_date >= $1 52 AND opr.pop_end_date IS NOT NULL 53 AND opr.pop_end_date > CURRENT_DATE 54 AND opr.pop_end_date <= $2 55 AND lower(btrim(opr.agency)) IN ( 56 SELECT lower(btrim(r.customer_agency)) 57 FROM requirements r 58 WHERE r.customer_agency IS NOT NULL 59 AND r.customer_agency <> '' 60 AND r.deleted_at IS NULL 61 UNION 62 SELECT lower(btrim(ri.customer_agency)) 63 FROM bid_outcomes bo 64 JOIN requirements ri 65 ON ri.organization_id = bo.organization_id 66 AND ri.id = bo.requirement_id 67 WHERE ri.customer_agency IS NOT NULL 68 AND ri.customer_agency <> '' 69 UNION 70 SELECT lower(btrim(oi.customer_agency)) 71 FROM bid_outcomes bo2 72 JOIN opportunities oi 73 ON oi.organization_id = bo2.organization_id 74 AND oi.id = bo2.opportunity_id 75 WHERE oi.customer_agency IS NOT NULL 76 AND oi.customer_agency <> '' 77 ) 78ORDER BY opr.posted_date DESC, opr.id ASC` 79 80// gapFillableSQL is trigger (c): an OPEN requirement (not deleted, with no green candidate_evaluation) that 81// the bench NOW structurally covers, meaning at least one candidate holds at least the required clearance 82// level by the rank ladder AND at least four fifths of the required skills. This is the SAME structural test 83// as ai.benchGapForRequirementSQL: the skill fraction is compared in INTEGER arithmetic (held matched times 84// five is at least the required total times four, equivalent to held over total at least four fifths) so NO 85// float touches the test, and a requirement with no required skills is vacuously met on the skills arm. It is 86// a STRUCTURAL coverage test to detect a newly fillable gap, explicitly NOT a match score: the per candidate 87// score and ranking remain the frozen engine, which is untouched. RLS scopes the read to the caller's bench 88// and requirements. The open predicate mirrors the Command Center and ListOpenRequirements 89// (deleted_at IS NULL AND the opportunity is null or open). The no green evaluation predicate is the gap 90// condition: a requirement that already has a proposal ready candidate is not an unfilled gap. The EXISTS over 91// the bench finds at least one structurally covering candidate, the moment a gap becomes fillable. 92// Deterministic ORDER BY on the requirement title then the id tiebreaker. The title names only the owner's own 93// requirement, the owner's own data. 94const gapFillableSQL = ` 95SELECT 96 r.id::text AS requirement_id, 97 r.title AS title 98FROM requirements r 99LEFT JOIN opportunities o 100 ON o.organization_id = r.organization_id 101 AND o.id = r.opportunity_id 102WHERE r.deleted_at IS NULL 103 AND (o.id IS NULL OR o.status = 'open') 104 AND NOT EXISTS ( 105 SELECT 1 FROM candidate_evaluations ce 106 WHERE ce.organization_id = r.organization_id 107 AND ce.requirement_id = r.id 108 AND ce.band = 'green' 109 ) 110 AND EXISTS ( 111 SELECT 1 112 FROM candidates c 113 JOIN clearance_levels cl_c ON cl_c.code = c.clearance_level 114 JOIN clearance_levels cl_r ON cl_r.code = r.required_level 115 WHERE c.deleted_at IS NULL 116 AND c.anonymized_at IS NULL 117 AND cl_c.rank >= cl_r.rank 118 AND ( 119 (SELECT count(*) FROM required_skills rs_total WHERE rs_total.requirement_id = r.id) = 0 120 OR 121 (SELECT count(*) FROM required_skills rs_held 122 JOIN candidate_skills cs 123 ON cs.organization_id = c.organization_id 124 AND cs.candidate_id = c.id 125 AND cs.skill_id = rs_held.skill_id 126 WHERE rs_held.requirement_id = r.id) * 5 127 >= 128 (SELECT count(*) FROM required_skills rs_req WHERE rs_req.requirement_id = r.id) * 4 129 ) 130 ) 131ORDER BY r.title ASC, r.id ASC` 132 133// collisionSQL is trigger (d), CROSS OPPORTUNITY COLLISION DECONFLICTION: a candidate proposed or approved on 134// the slates of two or more DISTINCT OPEN opportunities at once, the double commit of a scarce cleared person 135// across competing bids. It joins slate_members up to opportunities by the composite org keys (defense in depth 136// with RLS), keeps only pending and approved members (a rejected member is out of play) tied to open 137// opportunities, and groups to the candidates on more than one distinct opportunity. member_opps collapses to 138// one row per (candidate, opportunity) carrying the candidate's best fit score there and whether they are 139// approved there; ranked orders each collided candidate's opportunities by best fit then earliest due date then 140// id, so pos 1 is the DETERMINISTIC suggested primary (commit them where they fit best, tie broken by the most 141// urgent deadline). The final row per candidate carries the opportunity count, the approved count (a candidate 142// approved on two or more is truly double committed, the worst case), a scarce cleared flag (a self reported 143// TS SCI active indicator, never a verified check, used only for severity), the suggested primary name, and the 144// ordered opportunity names for the detail. RLS scopes every table to the caller's org; a standalone 145// requirement (null opportunity_id) is excluded by the inner join, never a false collision. Deterministic ORDER 146// BY scarce first, then the most contested, then the name, then the id. Only the owner's own candidate and 147// opportunity names leave the read, the owner's own data, exactly like the other triggers; the audit stays 148// counts only. 149const collisionSQL = ` 150WITH member_opps AS ( 151 SELECT 152 sm.organization_id AS organization_id, 153 sm.candidate_id AS candidate_id, 154 o.id AS opportunity_id, 155 o.name AS opportunity_name, 156 o.due_date AS due_date, 157 max(sm.total_score) AS best_score, 158 bool_or(sm.decision = 'approved') AS approved_here 159 FROM slate_members sm 160 JOIN slates sl ON sl.organization_id = sm.organization_id AND sl.id = sm.slate_id 161 JOIN requirements r ON r.organization_id = sl.organization_id AND r.id = sl.requirement_id 162 JOIN opportunities o ON o.organization_id = r.organization_id AND o.id = r.opportunity_id 163 WHERE sm.decision IN ('pending', 'approved') 164 AND o.status = 'open' 165 GROUP BY sm.organization_id, sm.candidate_id, o.id, o.name, o.due_date 166), 167collided AS ( 168 SELECT organization_id, candidate_id 169 FROM member_opps 170 GROUP BY organization_id, candidate_id 171 HAVING count(*) > 1 172), 173ranked AS ( 174 SELECT 175 mo.organization_id, mo.candidate_id, mo.opportunity_id, mo.opportunity_name, mo.approved_here, 176 row_number() OVER ( 177 PARTITION BY mo.candidate_id 178 ORDER BY mo.best_score DESC, mo.due_date ASC NULLS LAST, mo.opportunity_id ASC 179 ) AS pos 180 FROM member_opps mo 181 JOIN collided cd ON cd.organization_id = mo.organization_id AND cd.candidate_id = mo.candidate_id 182) 183SELECT 184 c.id::text AS candidate_id, 185 c.full_name AS full_name, 186 (c.clearance_level = 'ts_sci' AND c.clearance_status = 'active') AS scarce_cleared, 187 count(*)::int AS opp_count, 188 (count(*) FILTER (WHERE ranked.approved_here))::int AS approved_count, 189 (array_agg(ranked.opportunity_name ORDER BY ranked.pos))[1] AS suggested_primary, 190 string_agg(ranked.opportunity_name, ', ' ORDER BY ranked.pos) AS opportunity_names 191FROM ranked 192JOIN candidates c ON c.organization_id = ranked.organization_id AND c.id = ranked.candidate_id 193WHERE c.deleted_at IS NULL AND c.anonymized_at IS NULL 194GROUP BY c.id, c.full_name, c.clearance_level, c.clearance_status 195ORDER BY scarce_cleared DESC, opp_count DESC, c.full_name ASC, c.id ASC` 196 197// upsertAlertSQL records one alert, or refreshes the existing one keyed on (organization_id, dedupe_key). The 198// organization_id is taken from the session GUC (never a caller argument), so RLS WITH CHECK confirms the row 199// belongs to the caller; the ON CONFLICT update only ever touches a row already visible to this tenant. A re 200// sweep of the same event refreshes the type, severity, subject, title, detail, and updated_at and leaves the 201// status untouched (an alert the analyst already read or dismissed stays read or dismissed across sweeps), so 202// the sweep is idempotent and never resurfaces a handled alert. created_at is preserved on conflict. 203const upsertAlertSQL = ` 204INSERT INTO alerts 205 (organization_id, alert_type, severity, subject_type, subject_id, title, detail, dedupe_key) 206VALUES 207 (NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1, $2, $3, $4, $5, $6, $7) 208ON CONFLICT (organization_id, dedupe_key) DO UPDATE SET 209 alert_type = EXCLUDED.alert_type, 210 severity = EXCLUDED.severity, 211 subject_type = EXCLUDED.subject_type, 212 subject_id = EXCLUDED.subject_id, 213 title = EXCLUDED.title, 214 detail = EXCLUDED.detail, 215 updated_at = now()` 216 217// listAlertsSQL lists the caller's alerts newest first. RLS scopes it to the caller's org. The optional status 218// filter is applied with a sentinel: when $1 is the empty string every status is returned, otherwise only the 219// matching status. Deterministic ORDER BY on created_at descending then the id tiebreaker, a total order so 220// the list is byte stable when two alerts share a created_at. 221const listAlertsSQL = ` 222SELECT 223 id::text, alert_type, severity, subject_type, COALESCE(subject_id::text, ''), title, detail, status, created_at, updated_at 224FROM alerts 225WHERE ($1 = '' OR status = $1) 226ORDER BY created_at DESC, id ASC` 227 228// setAlertStatusSQL flips one alert's status (the UI mark read or dismiss). RLS scopes the UPDATE, so an id in 229// another org is invisible and matches zero rows (the not found path). The status value is validated in Go 230// against the closed set before the bind, so a bad value never reaches the DB CHECK. 231const setAlertStatusSQL = ` 232UPDATE alerts SET 233 status = $2, 234 updated_at = now() 235WHERE id = $1 236RETURNING id::text, alert_type, severity, subject_type, COALESCE(subject_id::text, ''), title, detail, status, created_at, updated_at` 237