Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / internal/modules/commandcenter/service_queries.go |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 39df94f92b2a33f852481fe1954dabed841524bb |
| Branch | w0-aedt-compliance |
| Path or archive member | internal/modules/commandcenter/service_queries.go |
| Original SHA-256 | a77d5250c7d6784969ab38e444b57667b450bca32b82e62efb1f85d5fa0e6a46 |
| Git blob SHA | 14346a34d5c084532c3424084b4f75873a5d5097 |
| Reading-copy SHA-256 | a77d5250c7d6784969ab38e444b57667b450bca32b82e62efb1f85d5fa0e6a46 |
Recovered source transcript
Download complete source text ↓316 lines · 12.8 KB
package commandcenter
import (
"context"
"fmt"
"github.com/jackc/pgx/v5"
"github.com/shopspring/decimal"
)
var polyLabels = map[string]string{"full_scope": "Full-Scope", "ci": "CI", "none": "None"}
func polyLabel(code string) string {
if l, ok := polyLabels[code]; ok {
return l
}
return "None"
}
// buildTemps materializes the per-org RLS-scoped cand/active_req/qual sets once per request so the
// set-cover isn't recomputed for every panel. ON COMMIT DROP cleans them up.
func buildTemps(ctx context.Context, tx pgx.Tx, asOf string) error {
// Defense in depth: confirm the tenant GUC is set (RLS active) before any aggregate runs, so a
// wrong pool/role fails closed here rather than silently returning a wrong scope [review RLS-2].
var guc string
if err := tx.QueryRow(ctx, `SELECT COALESCE(current_setting('app.current_org_id', true),'')`).Scan(&guc); err != nil {
return fmt.Errorf("tenant guc check: %w", err)
}
if guc == "" {
return fmt.Errorf("commandcenter: tenant GUC not set — refusing to aggregate")
}
if _, err := tx.Exec(ctx, `
CREATE TEMP TABLE cc_cand ON COMMIT DROP AS
SELECT id, full_name, location, available, years_experience,
clearance_level, clearance_poly, clearance_status, clearance_expires_at, reinvestigation_due_at,
mm_lvl_ord(clearance_level) AS lvl, mm_poly_ord(clearance_poly) AS poly,
(clearance_status='active' AND (clearance_expires_at IS NULL OR clearance_expires_at >= $1::date)) AS effective
FROM candidates WHERE deleted_at IS NULL`, asOf); err != nil {
return fmt.Errorf("cc_cand: %w", err)
}
if _, err := tx.Exec(ctx, `
CREATE TEMP TABLE cc_active_req ON COMMIT DROP AS
SELECT r.id, r.opportunity_id, r.labor_category, COALESCE(r.quantity,1) AS quantity, r.key_personnel,
r.required_years, r.required_level, mm_lvl_ord(r.required_level) AS rlvl, mm_poly_ord(r.required_poly) AS rpoly
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')`); err != nil {
return fmt.Errorf("cc_active_req: %w", err)
}
if _, err := tx.Exec(ctx, `
CREATE TEMP TABLE cc_qual ON COMMIT DROP AS
WITH mand AS (SELECT requirement_id, array_agg(skill_id) AS skills FROM required_skills WHERE mandatory GROUP BY requirement_id)
SELECT ar.id AS req_id, count(DISTINCT c.id) AS n
FROM cc_active_req ar
JOIN cc_cand c ON c.available
AND (ar.rlvl < 2 OR (c.effective AND c.lvl >= ar.rlvl AND c.poly >= ar.rpoly))
AND c.years_experience >= ar.required_years
AND NOT EXISTS (
SELECT 1 FROM unnest(COALESCE((SELECT skills FROM mand WHERE mand.requirement_id=ar.id),'{}'::uuid[])) AS rsk
WHERE NOT EXISTS (SELECT 1 FROM candidate_skills cs WHERE cs.candidate_id=c.id AND cs.skill_id=rsk)
)
GROUP BY ar.id`); err != nil {
return fmt.Errorf("cc_qual: %w", err)
}
_, err := tx.Exec(ctx, `CREATE INDEX ON cc_qual (req_id)`)
return err
}
// drivers computes the four "inventory coverage" drivers (a requirement is COVERED iff >=1 qualified
// available candidate exists — coverability, not assignment). The fifth driver, proposal_readiness, is
// derived from the assignment in the service (proposalFromAssignment).
func (s *Service) drivers(ctx context.Context, tx pgx.Tx) (map[string]decimal.Decimal, decimal.Decimal, error) {
d := map[string]decimal.Decimal{}
var denom, covered int64
if err := tx.QueryRow(ctx, `
WITH clreq AS (SELECT rlvl, rpoly FROM cc_active_req WHERE rlvl>=2)
SELECT count(*), count(*) FILTER (WHERE EXISTS (
SELECT 1 FROM cc_cand c WHERE c.available AND c.effective AND c.lvl>=clreq.rlvl AND c.poly>=clreq.rpoly))
FROM clreq`).Scan(&denom, &covered); err != nil {
return nil, decimal.Zero, fmt.Errorf("clearance cov: %w", err)
}
d["clearance_coverage"] = pct(covered, denom)
if err := tx.QueryRow(ctx, `
WITH skreq AS (SELECT ar.id FROM cc_active_req ar WHERE EXISTS (SELECT 1 FROM required_skills rs WHERE rs.requirement_id=ar.id AND rs.mandatory))
SELECT count(*), count(*) FILTER (WHERE COALESCE(q.n,0)>0) FROM skreq LEFT JOIN cc_qual q ON q.req_id=skreq.id`).
Scan(&denom, &covered); err != nil {
return nil, decimal.Zero, fmt.Errorf("skill cov: %w", err)
}
d["skill_coverage"] = pct(covered, denom)
if err := tx.QueryRow(ctx, `
WITH reqcov AS (SELECT COALESCE(NULLIF(trim(ar.labor_category),''),'Uncategorized') AS lcat, bool_or(COALESCE(q.n,0)>0) AS covered
FROM cc_active_req ar LEFT JOIN cc_qual q ON q.req_id=ar.id
WHERE ar.labor_category IS NOT NULL AND trim(ar.labor_category)<>'' GROUP BY 1)
SELECT count(*), count(*) FILTER (WHERE covered) FROM reqcov`).Scan(&denom, &covered); err != nil {
return nil, decimal.Zero, fmt.Errorf("lcat cov: %w", err)
}
d["labor_category_coverage"] = pct(covered, denom)
var availNum, clearedDen int64
if err := tx.QueryRow(ctx, `SELECT count(*) FILTER (WHERE available AND effective AND lvl>=2), count(*) FILTER (WHERE effective AND lvl>=2) FROM cc_cand`).
Scan(&availNum, &clearedDen); err != nil {
return nil, decimal.Zero, fmt.Errorf("avail cov: %w", err)
}
d["availability_coverage"] = pct(availNum, clearedDen)
var reqDen, reqCov int64
if err := tx.QueryRow(ctx, `SELECT count(*), count(*) FILTER (WHERE COALESCE(q.n,0)>0) FROM cc_active_req ar LEFT JOIN cc_qual q ON q.req_id=ar.id`).
Scan(&reqDen, &reqCov); err != nil {
return nil, decimal.Zero, fmt.Errorf("avail req cov: %w", err)
}
return d, pct(reqCov, reqDen), nil
}
func (s *Service) clearance(ctx context.Context, tx pgx.Tx, asOf string) (ClearanceIntel, error) {
ci := ClearanceIntel{}
var sec, secA, ts, tsA, tsci, tsciA, ciN, ciA, fs, fsA int64
if err := tx.QueryRow(ctx, `SELECT
count(*) FILTER (WHERE effective AND lvl=3), count(*) FILTER (WHERE effective AND lvl=3 AND available),
count(*) FILTER (WHERE effective AND lvl=4), count(*) FILTER (WHERE effective AND lvl=4 AND available),
count(*) FILTER (WHERE effective AND lvl=5), count(*) FILTER (WHERE effective AND lvl=5 AND available),
count(*) FILTER (WHERE effective AND poly>=1), count(*) FILTER (WHERE effective AND poly>=1 AND available),
count(*) FILTER (WHERE effective AND poly=2), count(*) FILTER (WHERE effective AND poly=2 AND available)
FROM cc_cand`).Scan(&sec, &secA, &ts, &tsA, &tsci, &tsciA, &ciN, &ciA, &fs, &fsA); err != nil {
return ci, fmt.Errorf("clearance inventory: %w", err)
}
ci.InventoryByLevel = []LevelCount{
{"secret", "Secret", int(sec), int(secA)},
{"top_secret", "Top Secret", int(ts), int(tsA)},
{"ts_sci", "TS/SCI", int(tsci), int(tsciA)},
}
ci.InventoryByPoly = []PolyCount{
{"ci", "CI-Poly", int(ciN), int(ciA)},
{"full_scope", "FS-Poly", int(fs), int(fsA)},
}
var now, d30, d60, d90 int64
if err := tx.QueryRow(ctx, `SELECT
count(*) FILTER (WHERE available AND effective),
count(*) FILTER (WHERE available AND effective AND (clearance_expires_at IS NULL OR clearance_expires_at >= $1::date + 30)),
count(*) FILTER (WHERE available AND effective AND (clearance_expires_at IS NULL OR clearance_expires_at >= $1::date + 60)),
count(*) FILTER (WHERE available AND effective AND (clearance_expires_at IS NULL OR clearance_expires_at >= $1::date + 90))
FROM cc_cand`, asOf).Scan(&now, &d30, &d60, &d90); err != nil {
return ci, fmt.Errorf("avail windows: %w", err)
}
ci.AvailabilityWindows = AvailWindows{int(now), int(d30), int(d60), int(d90)}
var expired, due30, due60, due90, reinv int64
if err := tx.QueryRow(ctx, `SELECT
count(*) FILTER (WHERE clearance_expires_at < $1::date),
count(*) FILTER (WHERE clearance_expires_at >= $1::date AND clearance_expires_at < $1::date + 30),
count(*) FILTER (WHERE clearance_expires_at >= $1::date AND clearance_expires_at < $1::date + 60),
count(*) FILTER (WHERE clearance_expires_at >= $1::date AND clearance_expires_at < $1::date + 90),
count(*) FILTER (WHERE reinvestigation_due_at < $1::date)
FROM candidates WHERE deleted_at IS NULL AND clearance_status='active'`, asOf).
Scan(&expired, &due30, &due60, &due90, &reinv); err != nil {
return ci, fmt.Errorf("expiring counts: %w", err)
}
alerts := []ExpiryAlert{}
arows, err := tx.Query(ctx, `SELECT id::text, full_name, clearance_level, (clearance_expires_at - $1::date)
FROM candidates WHERE deleted_at IS NULL AND clearance_status='active' AND clearance_expires_at IS NOT NULL
AND clearance_expires_at >= $1::date AND clearance_expires_at < $1::date + 90
ORDER BY clearance_expires_at, id LIMIT 50`, asOf)
if err != nil {
return ci, fmt.Errorf("expiry alerts: %w", err)
}
for arows.Next() {
var a ExpiryAlert
var days int
if err := arows.Scan(&a.CandidateID, &a.FullName, &a.ClearanceLevel, &days); err != nil {
arows.Close()
return ci, err
}
a.DaysUntilExpiry = days
switch {
case days < 30:
a.Severity = "critical"
case days < 60:
a.Severity = "warning"
default:
a.Severity = "info"
}
alerts = append(alerts, a)
}
arows.Close()
if err := arows.Err(); err != nil {
return ci, err
}
ci.Expiring = ExpiringSummary{int(expired), int(due30), int(due60), int(due90), int(reinv), alerts}
return ci, nil
}
func (s *Service) winImpact(ctx context.Context, tx pgx.Tx) (WinImpact, error) {
const where = `c.available AND c.effective AND c.lvl=5
AND (c.poly>=1 OR EXISTS (SELECT 1 FROM candidate_customer_experience cce WHERE cce.candidate_id=c.id))
AND EXISTS (SELECT 1 FROM candidate_skills cs WHERE cs.candidate_id=c.id)`
wi := WinImpact{Top: []WinCandidate{}}
var count int64
if err := tx.QueryRow(ctx, `SELECT count(*) FROM cc_cand c WHERE `+where).Scan(&count); err != nil {
return wi, fmt.Errorf("win impact count: %w", err)
}
wi.HighImpactCount = int(count)
rows, err := tx.Query(ctx, `SELECT c.id::text, c.full_name, c.clearance_level, c.clearance_poly, c.years_experience
FROM cc_cand c WHERE `+where+` ORDER BY c.poly DESC, c.years_experience DESC, c.full_name, c.id LIMIT 10`)
if err != nil {
return wi, fmt.Errorf("win impact top: %w", err)
}
for rows.Next() {
var wc WinCandidate
var poly string
var years int
if err := rows.Scan(&wc.CandidateID, &wc.FullName, &wc.ClearanceLevel, &poly, &years); err != nil {
rows.Close()
return wi, err
}
wc.Poly = polyLabel(poly)
wc.YearsExperience = years
wi.Top = append(wi.Top, wc)
}
rows.Close()
return wi, rows.Err()
}
func (s *Service) heatMap(ctx context.Context, tx pgx.Tx) ([]HeatMetro, error) {
rows, err := tx.Query(ctx, `
SELECT COALESCE(NULLIF(trim(location),''),'Unknown'),
count(*), count(*) FILTER (WHERE available), count(*) FILTER (WHERE effective AND lvl>=2)
FROM cc_cand GROUP BY 1 ORDER BY 2 DESC, 1 LIMIT 25`)
if err != nil {
return nil, fmt.Errorf("heat map: %w", err)
}
out := []HeatMetro{}
for rows.Next() {
var m HeatMetro
var total, avail, cleared int64
if err := rows.Scan(&m.Metro, &total, &avail, &cleared); err != nil {
rows.Close()
return nil, err
}
m.Total, m.Available, m.Cleared = int(total), int(avail), int(cleared)
out = append(out, m)
}
rows.Close()
return out, rows.Err()
}
// ---------- pure Go assembly ----------
func buildActionCenter(suggestReq *string, opps []Opportunity, openNoSlate, reqsUnscreened int) ActionCenter {
var suggestOpp *string
for i := range opps {
if opps[i].StaffingRiskCode == "high" { // opps already sorted by value desc
id := opps[i].OpportunityID
suggestOpp = &id
break
}
}
return ActionCenter{
SuggestedRequirementID: suggestReq, SuggestedOpportunityID: suggestOpp,
OpenOpportunitiesWithoutSlate: openNoSlate, RequirementsUnscreened: reqsUnscreened,
Launch: []Launch{
{"slateMATCH", "generate_slate", "Generate Slate", "/slates/generate"},
{"screenMATCH", "screen_candidate", "Screen Candidate", "/match"},
{"missionMATCH", "open_mission", "Refresh", "/api/command-center"},
},
}
}
func buildRecommendations(v View) []Recommendation {
recs := []Recommendation{}
for i, g := range v.StaffingGaps {
if i >= 4 {
break
}
sev := "medium"
if g.BlocksKeyPersonnel || g.Unmet >= 5 {
sev = "high"
}
msg := fmt.Sprintf("Need %d more %s %s", g.Unmet, g.RequiredLevelLabel, pluralize(g.LaborCategory, int64(g.Unmet)))
if len(g.ImpactedOpportunities) > 0 {
msg += " for " + g.ImpactedOpportunities[0]
}
msg += "."
recs = append(recs, Recommendation{
Severity: sev, Message: msg, ActionTarget: "slate", RelatedIDs: []string{},
SourceRef: "gap:" + g.LaborCategory + ":" + g.RequiredLevel,
})
}
if v.ClearanceIntelligence.Expiring.Due30 > 0 {
recs = append(recs, Recommendation{
Severity: "medium",
Message: fmt.Sprintf("%d clearances expire within 30 days — schedule reinvestigations.", v.ClearanceIntelligence.Expiring.Due30),
ActionTarget: "mission", RelatedIDs: []string{}, SourceRef: "expiry:due_30",
})
}
return recs
}
func pluralize(s string, n int64) string {
if n == 1 {
return s
}
return s + "s"
}
func kpClause(blocks bool) string {
if blocks {
return "; blocks key personnel"
}
return ""
}