Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / internal/modules/alerts/repo.go |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | internal/modules/alerts/repo.go |
| Original SHA-256 | 779ee0d0add1a40fac1941da4f66327512f3190875673d6bc59e750ac50149e5 |
| Git blob SHA | d26648c6dc7816b615b62475484207618cb7cf4a |
| Reading-copy SHA-256 | 779ee0d0add1a40fac1941da4f66327512f3190875673d6bc59e750ac50149e5 |
Recovered source transcript
Download complete source text ↓307 lines · 17.7 KB
package alerts
import "strconv"
// repo.go holds the parameterized SQL the service runs under RunInTenantTx (RLS scoped). The statements are
// raw and parameterized rather than sqlc generated so the alert engine is self contained and does not require
// regenerating the dbgen package for one new table; this mirrors the bidoutcomes ledger and the AI graph
// layer, which author their reviewed SQL as static constants. Every statement runs as the NOBYPASSRLS app
// role inside a tenant transaction, so the organization_id is the session GUC and a row in another org is
// never visible or writable (the RLS policy plus the composite indexes both hold). The app role has SELECT,
// INSERT, UPDATE on alerts and no DELETE, so an alert is never destroyed in app code.
// clearanceExpiringSQL is trigger (a): the owner's own candidates whose clearance lapses within the window.
// RLS scopes the read to the caller's bench, the soft delete and anonymize filters exclude an erased
// candidate (an anonymized in place row keeps its candidate id, so without the anonymized_at filter an erased
// person would still surface in the alert title). $1 is the window cutoff date (today plus the window in
// days, computed in Go so the SQL binds a plain date). The days remaining is computed in SQL as the integer
// day difference so the title can state it. Deterministic ORDER BY on the expiry date then the id tiebreaker,
// a total order. Only the owner's own candidate name and the integer days remaining ever leave the read; the
// title is the owner's own data, never another tenant's.
const clearanceExpiringSQL = `
SELECT
c.id::text AS candidate_id,
c.full_name AS full_name,
(c.clearance_expires_at - CURRENT_DATE)::int AS days_remaining
FROM candidates c
WHERE c.deleted_at IS NULL
AND c.anonymized_at IS NULL
AND c.clearance_expires_at IS NOT NULL
AND c.clearance_expires_at <= $1
ORDER BY c.clearance_expires_at ASC, c.id ASC`
// recompeteWinnableSQL is trigger (b), the MARKET EVENT arm: a recently posted federal opportunity in an
// agency the owner already shows interest in, whose period of performance ends inside the forward recompete
// window. opportunity_records is GLOBAL PUBLIC reference data (no organization_id, outside RLS), so reading it
// is fine; the org INTEREST read (a requirement with that customer_agency, or a bid_outcome linked to a
// requirement or opportunity in that agency) is RLS scoped, so a foreign org's interest never widens this
// org's market alerts. The agency match is on the normalized lower(trim(agency)) so a SAM agency string and
// the owner's stored customer_agency match despite casing or padding. $1 is the posted cutoff date (today
// minus the recent window), $2 is the pop end cutoff date (today plus the recompete window), so an
// opportunity qualifies when it was posted on or after $1 and its pop_end_date falls between today and $2.
// A row with a null pop_end_date is excluded (the recompete window is undefined without an end date).
// Deterministic ORDER BY on the posted date descending then the id tiebreaker. The title names only the
// agency and naics the owner already tracks, the owner's own market context, never another tenant's data.
const recompeteWinnableSQL = `
SELECT
opr.id::text AS opportunity_record_id,
opr.agency AS agency,
opr.naics AS naics
FROM opportunity_records opr
WHERE opr.agency <> ''
AND opr.posted_date IS NOT NULL
AND opr.posted_date >= $1
AND opr.pop_end_date IS NOT NULL
AND opr.pop_end_date > CURRENT_DATE
AND opr.pop_end_date <= $2
AND lower(btrim(opr.agency)) IN (
SELECT lower(btrim(r.customer_agency))
FROM requirements r
WHERE r.customer_agency IS NOT NULL
AND r.customer_agency <> ''
AND r.deleted_at IS NULL
UNION
SELECT lower(btrim(ri.customer_agency))
FROM bid_outcomes bo
JOIN requirements ri
ON ri.organization_id = bo.organization_id
AND ri.id = bo.requirement_id
WHERE ri.customer_agency IS NOT NULL
AND ri.customer_agency <> ''
UNION
SELECT lower(btrim(oi.customer_agency))
FROM bid_outcomes bo2
JOIN opportunities oi
ON oi.organization_id = bo2.organization_id
AND oi.id = bo2.opportunity_id
WHERE oi.customer_agency IS NOT NULL
AND oi.customer_agency <> ''
)
ORDER BY opr.posted_date DESC, opr.id ASC`
// gapFillableSQL is trigger (c): an OPEN requirement (not deleted, with no green candidate_evaluation) that
// the bench NOW structurally covers, meaning at least one candidate holds at least the required clearance
// level by the rank ladder AND at least four fifths of the required skills. This is the SAME structural test
// as ai.benchGapForRequirementSQL: the skill fraction is compared in INTEGER arithmetic (held matched times
// five is at least the required total times four, equivalent to held over total at least four fifths) so NO
// float touches the test, and a requirement with no required skills is vacuously met on the skills arm. It is
// a STRUCTURAL coverage test to detect a newly fillable gap, explicitly NOT a match score: the per candidate
// score and ranking remain the frozen engine, which is untouched. RLS scopes the read to the caller's bench
// and requirements. The open predicate mirrors the Command Center and ListOpenRequirements
// (deleted_at IS NULL AND the opportunity is null or open). The no green evaluation predicate is the gap
// condition: a requirement that already has a proposal ready candidate is not an unfilled gap. The EXISTS over
// the bench finds at least one structurally covering candidate, the moment a gap becomes fillable.
// Deterministic ORDER BY on the requirement title then the id tiebreaker. The title names only the owner's own
// requirement, the owner's own data.
const gapFillableSQL = `
SELECT
r.id::text AS requirement_id,
r.title AS title
FROM requirements r
LEFT JOIN opportunities o
ON o.organization_id = r.organization_id
AND o.id = r.opportunity_id
WHERE r.deleted_at IS NULL
AND (o.id IS NULL OR o.status = 'open')
AND NOT EXISTS (
SELECT 1 FROM candidate_evaluations ce
WHERE ce.organization_id = r.organization_id
AND ce.requirement_id = r.id
AND ce.band = 'green'
)
AND EXISTS (
SELECT 1
FROM candidates c
JOIN clearance_levels cl_c ON cl_c.code = c.clearance_level
JOIN clearance_levels cl_r ON cl_r.code = r.required_level
WHERE c.deleted_at IS NULL
AND c.anonymized_at IS NULL
AND cl_c.rank >= cl_r.rank
AND (
(SELECT count(*) FROM required_skills rs_total WHERE rs_total.requirement_id = r.id) = 0
OR
(SELECT count(*) FROM required_skills rs_held
JOIN candidate_skills cs
ON cs.organization_id = c.organization_id
AND cs.candidate_id = c.id
AND cs.skill_id = rs_held.skill_id
WHERE rs_held.requirement_id = r.id) * 5
>=
(SELECT count(*) FROM required_skills rs_req WHERE rs_req.requirement_id = r.id) * 4
)
)
ORDER BY r.title ASC, r.id ASC`
// collisionSQL is trigger (d), CROSS OPPORTUNITY COLLISION DECONFLICTION: a candidate proposed or approved on
// the slates of two or more DISTINCT OPEN opportunities at once, the double commit of a scarce cleared person
// across competing bids. It joins slate_members up to opportunities by the composite org keys (defense in depth
// with RLS), keeps only pending and approved members (a rejected member is out of play) tied to open
// opportunities, and groups to the candidates on more than one distinct opportunity. member_opps collapses to
// one row per (candidate, opportunity) carrying the candidate's best fit score there and whether they are
// approved there; ranked orders each collided candidate's opportunities by best fit then earliest due date then
// id, so pos 1 is the DETERMINISTIC suggested primary (commit them where they fit best, tie broken by the most
// urgent deadline). The final row per candidate carries the opportunity count, the approved count (a candidate
// approved on two or more is truly double committed, the worst case), a scarce cleared flag (a self reported
// TS SCI active indicator, never a verified check, used only for severity), the suggested primary name, and the
// ordered opportunity names for the detail. RLS scopes every table to the caller's org; a standalone
// requirement (null opportunity_id) is excluded by the inner join, never a false collision. Deterministic ORDER
// BY scarce first, then the most contested, then the name, then the id. Only the owner's own candidate and
// opportunity names leave the read, the owner's own data, exactly like the other triggers; the audit stays
// counts only.
const collisionSQL = `
WITH member_opps AS (
SELECT
sm.organization_id AS organization_id,
sm.candidate_id AS candidate_id,
o.id AS opportunity_id,
o.name AS opportunity_name,
o.due_date AS due_date,
max(sm.total_score) AS best_score,
bool_or(sm.decision = 'approved') AS approved_here
FROM slate_members sm
JOIN slates sl ON sl.organization_id = sm.organization_id AND sl.id = sm.slate_id
JOIN requirements r ON r.organization_id = sl.organization_id AND r.id = sl.requirement_id
JOIN opportunities o ON o.organization_id = r.organization_id AND o.id = r.opportunity_id
WHERE sm.decision IN ('pending', 'approved')
AND o.status = 'open'
GROUP BY sm.organization_id, sm.candidate_id, o.id, o.name, o.due_date
),
collided AS (
SELECT organization_id, candidate_id
FROM member_opps
GROUP BY organization_id, candidate_id
HAVING count(*) > 1
),
ranked AS (
SELECT
mo.organization_id, mo.candidate_id, mo.opportunity_id, mo.opportunity_name, mo.approved_here,
row_number() OVER (
PARTITION BY mo.candidate_id
ORDER BY mo.best_score DESC, mo.due_date ASC NULLS LAST, mo.opportunity_id ASC
) AS pos
FROM member_opps mo
JOIN collided cd ON cd.organization_id = mo.organization_id AND cd.candidate_id = mo.candidate_id
)
SELECT
c.id::text AS candidate_id,
c.full_name AS full_name,
(c.clearance_level = 'ts_sci' AND c.clearance_status = 'active') AS scarce_cleared,
count(*)::int AS opp_count,
(count(*) FILTER (WHERE ranked.approved_here))::int AS approved_count,
(array_agg(ranked.opportunity_name ORDER BY ranked.pos))[1] AS suggested_primary,
string_agg(ranked.opportunity_name, ', ' ORDER BY ranked.pos) AS opportunity_names
FROM ranked
JOIN candidates c ON c.organization_id = ranked.organization_id AND c.id = ranked.candidate_id
WHERE c.deleted_at IS NULL AND c.anonymized_at IS NULL
GROUP BY c.id, c.full_name, c.clearance_level, c.clearance_status
ORDER BY scarce_cleared DESC, opp_count DESC, c.full_name ASC, c.id ASC`
// upsertAlertSQL records one alert, or refreshes the existing one keyed on (organization_id, dedupe_key). The
// organization_id is taken from the session GUC (never a caller argument), so RLS WITH CHECK confirms the row
// belongs to the caller; the ON CONFLICT update only ever touches a row already visible to this tenant. A re
// sweep of the same event refreshes the type, severity, subject, title, detail, and updated_at and leaves the
// status untouched (an alert the analyst already read or dismissed stays read or dismissed across sweeps), so
// the sweep is idempotent and never resurfaces a handled alert. created_at is preserved on conflict.
const upsertAlertSQL = `
INSERT INTO alerts
(organization_id, alert_type, severity, subject_type, subject_id, title, detail, dedupe_key)
VALUES
(NULLIF(current_setting('app.current_org_id', true), '')::uuid, $1, $2, $3, $4, $5, $6, $7)
ON CONFLICT (organization_id, dedupe_key) DO UPDATE SET
alert_type = EXCLUDED.alert_type,
severity = EXCLUDED.severity,
subject_type = EXCLUDED.subject_type,
subject_id = EXCLUDED.subject_id,
title = EXCLUDED.title,
detail = EXCLUDED.detail,
updated_at = now()`
// listAlertsSQL lists the caller's alerts newest first. RLS scopes it to the caller's org. The optional status
// filter is applied with a sentinel: when $1 is the empty string every status is returned, otherwise only the
// matching status. Deterministic ORDER BY on created_at descending then the id tiebreaker, a total order so
// the list is byte stable when two alerts share a created_at.
//
// IT IS BOUNDED, AND IT WAS NOT. This query had no LIMIT, which was harmless while the watch loop produced
// tens of alerts and became a defect the moment it produced thousands: a stress run against a firm with fifty
// thousand live assignments returned 19,159 alerts in a SEVEN POINT NINE MEGABYTE response, to a dashboard
// panel that renders six of them. That is a page load a recruiter on a hotel connection would give up on,
// for information they cannot use.
//
// The bound is deliberately generous. The feed is a worklist rather than a page of results, nothing pages
// through it, and a few hundred is far more than anybody reads while still being a response that arrives.
// The newest are kept, which is the ordering the panel already relies on.
const alertListLimit = 500
// Composed from the constant rather than repeating the number, so the bound and the documentation above it
// cannot drift apart. It is a compile time concatenation of a constant this package owns, never caller input.
var listAlertsSQL = `
SELECT
id::text, alert_type, severity, subject_type, COALESCE(subject_id::text, ''), title, detail, status, created_at, updated_at
FROM alerts
WHERE ($1 = '' OR status = $1)
ORDER BY created_at DESC, id ASC
LIMIT ` + strconv.Itoa(alertListLimit)
// setAlertStatusSQL flips one alert's status (the UI mark read or dismiss). RLS scopes the UPDATE, so an id in
// another org is invisible and matches zero rows (the not found path). The status value is validated in Go
// against the closed set before the bind, so a bad value never reaches the DB CHECK.
const setAlertStatusSQL = `
UPDATE alerts SET
status = $2,
updated_at = now()
WHERE id = $1
RETURNING id::text, alert_type, severity, subject_type, COALESCE(subject_id::text, ''), title, detail, status, created_at, updated_at`
// redeploymentDueSQL is trigger (e): the firm's OWN contractors whose assignment ends inside the window.
//
// THE DATA HAS BEEN THERE SINCE MIGRATION 0048 AND NOTHING READ IT. placements carries end_date and status,
// the alert engine watched the market and the bench, and the one event entirely inside the firm's control
// went unwatched. A contractor finishing in thirty days is the warmest and cheapest candidate the firm has,
// already cleared, already known to the client, and already proven on the work.
//
// LIVE ASSIGNMENTS ONLY. pending_start and active are the two states that can still be redeployed from; a
// completed, terminated or fell_off assignment is history and its person is already back on the bench or
// already gone. The partial index added by migration 0099 covers exactly this predicate.
//
// ALREADY REDEPLOYED PEOPLE ARE EXCLUDED, and this is the part that makes the alert trustworthy rather than
// noisy. A recruiter who has ALREADY lined up the next assignment does not need to be chased about it, so a
// candidate holding another live placement that starts on or after this one ends is filtered out. Without it
// the list would nag hardest about the people the desk has already handled, which is how an alert feed gets
// ignored.
//
// The soft delete and anonymize filters mirror the clearance trigger: an erased person keeps their candidate
// id when anonymized in place, so without the anonymized_at filter they would still surface by name.
//
// $1 is the window cutoff date, computed in Go so the SQL binds a plain date. Deterministic ORDER BY on the
// end date then the placement id, a total order. Only the owner's own data leaves the read.
const redeploymentDueSQL = `
SELECT
p.id::text AS placement_id,
c.id::text AS candidate_id,
c.full_name AS full_name,
coalesce(r.title, '') AS role_title,
coalesce(cl.name, '') AS client_name,
(p.end_date - CURRENT_DATE)::int AS days_remaining,
c.clearance_level AS clearance_level
FROM placements p
JOIN applications a ON a.organization_id = p.organization_id AND a.id = p.application_id
JOIN candidates c ON c.organization_id = a.organization_id AND c.id = a.candidate_id
JOIN requisitions r ON r.organization_id = a.organization_id AND r.id = a.requisition_id
LEFT JOIN clients cl ON cl.organization_id = r.organization_id AND cl.id = r.client_id
WHERE p.status IN ('pending_start', 'active')
AND p.end_date IS NOT NULL
AND p.end_date <= $1
AND p.end_date >= CURRENT_DATE
AND c.deleted_at IS NULL
AND c.anonymized_at IS NULL
AND NOT EXISTS (
SELECT 1
FROM placements nxt
JOIN applications na ON na.organization_id = nxt.organization_id AND na.id = nxt.application_id
WHERE nxt.organization_id = p.organization_id
AND na.candidate_id = a.candidate_id
AND nxt.id <> p.id
AND nxt.status IN ('pending_start', 'active')
AND nxt.start_date >= p.end_date)
ORDER BY p.end_date ASC, p.id ASC`