Janady13/missionmatch / internal/modules/ai/graph_skill.go

1package ai 2 3// G3 skill and role adjacency graph. This file adds the curated, multi hop skill traversal as FIXED, 4// named, parameterized QueryIntents. It is the deterministic counterpart to the live single hop 5// SkillAdjacency in scaffolds.go: where SkillAdjacency reads the semantic embedding neighborhood, this 6// walks the CURATED skill_relationships graph (the WS2 editorially reviewed substitutes_for and 7// broader_than edges) and the ontology_crosswalk taxonomy, returning structure (the reachable skills, 8// the accumulated curated strength, and the evidence path) that a caller routes to the frozen engine. 9// The graph NEVER computes a match score, a band, or any employment decision number; it returns the 10// adjacency a downstream scoring call may consult, exactly as the curated relationship layer already 11// feeds the engine's partial credit. The model never authors this SQL: the recursive CTE text is static, 12// reviewed, registered in the closed Registry, asserted by ValidateRegistry, and re guarded by 13// GuardReadOnlyQuery on every execution. 14// 15// Recursive determinism (the AEDT reproducibility mandate). Both traversals carry, by construction: 16// - a hard depth cap, bound as a parameter (default 3 hops), enforced in the recursive arm, 17// - a cycle guard, a visited skill id array carried per row plus UNION (not UNION ALL), so a cycle in 18// the curated graph can never loop and the walk always terminates, 19// - a unique id tiebreaker on every ORDER BY, so the same inputs yield byte identical rows. 20// 21// The accumulated curated strength is carried through the recursion as Postgres numeric and is scanned 22// into shopspring/decimal in Go, NEVER float64: a coverage or strength figure shown as evidence is a 23// decision adjacent number and the platform's decimal rule applies. Cosine and embedding are NOT used 24// here; this is the curated graph, not the semantic matcher (which lives in SkillAdjacency). 25 26import ( 27 "context" 28 "errors" 29 30 "github.com/jackc/pgx/v5" 31 "github.com/shopspring/decimal" 32 33 "github.com/missionworkforce/missionmatch/internal/platform/tenant" 34) 35 36// IntentSkillSubstitutionClosure is the registered name of the transitive substitution closure intent. 37const IntentSkillSubstitutionClosure = "skill_substitution_closure" 38 39// IntentSkillGapBridge is the registered name of the gap bridge intent. 40const IntentSkillGapBridge = "skill_gap_bridge" 41 42// DefaultGraphDepth is the default hard depth cap for the recursive walks when a caller does not bind one. 43const DefaultGraphDepth = 3 44 45// maxGraphDepth bounds the depth a caller may request, so an oversized depth cannot turn a bounded walk 46// into an unbounded one. The curated graph is shallow; three to five hops is the meaningful range. 47const maxGraphDepth = 5 48 49// skillSubstitutionClosureSQL is the FIXED recursive CTE for the substitution and broadening closure. It 50// starts from a skill id ($1) and walks skill_relationships over the curated substitutes_for and 51// broader_than edges (verified_at IS NOT NULL), to a bound depth ($2). 52// 53// Determinism, line by line: 54// - the base arm seeds depth 1, the starting accumulated strength 1.0 (numeric), and the visited path 55// array with the start id, so the first hop's strength is the edge strength itself, 56// - the recursive arm multiplies the carried numeric acc_strength by the next edge strength (a 57// multiplicative transitive confidence that stays inside 0 to 1), appends the next skill id to the 58// visited path, and admits a hop only when c.depth < $2 (the hard depth cap) AND the next skill id is 59// NOT already in the visited path (the cycle guard), so a cycle cannot loop, 60// - UNION (not UNION ALL) is the additional duplicate defense the mandate requires, 61// - the final SELECT joins skills for the readable name and orders by depth, then acc_strength 62// descending, then the skill_id tiebreaker, then the full visited path, so the order is total even 63// when two distinct evidence paths reach the same skill at the same depth and strength, and the 64// output is byte stable on a re run. 65// 66// acc_strength is Postgres numeric end to end and is scanned into decimal.Decimal in Go, never float64. 67const skillSubstitutionClosureSQL = ` 68WITH RECURSIVE closure AS ( 69 SELECT 70 sr.to_skill_id AS skill_id, 71 1 AS depth, 72 sr.strength::numeric AS acc_strength, 73 ARRAY[sr.from_skill_id, sr.to_skill_id] AS path, 74 ARRAY[sr.relation] AS relations 75 FROM skill_relationships sr 76 WHERE sr.from_skill_id = $1 77 AND sr.verified_at IS NOT NULL 78 AND sr.relation IN ('substitutes_for', 'broader_than') 79 UNION 80 SELECT 81 sr.to_skill_id, 82 c.depth + 1, 83 c.acc_strength * sr.strength, 84 c.path || sr.to_skill_id, 85 c.relations || sr.relation 86 FROM skill_relationships sr 87 JOIN closure c ON sr.from_skill_id = c.skill_id 88 WHERE sr.verified_at IS NOT NULL 89 AND sr.relation IN ('substitutes_for', 'broader_than') 90 AND c.depth < $2 91 AND NOT (sr.to_skill_id = ANY(c.path)) 92) 93SELECT 94 c.skill_id, 95 s.name AS skill_name, 96 c.depth, 97 c.acc_strength, 98 c.relations AS evidence_relations, 99 c.path AS evidence_path 100FROM closure c 101JOIN skills s ON s.id = c.skill_id 102ORDER BY c.depth, c.acc_strength DESC, c.skill_id, c.path` 103 104// skillGapBridgeSQL is the FIXED bridge intent. Given a required skill id ($1) that is not exactly held, 105// it returns the nearest curated skills one or two hops away (the same bounded substitution and 106// broadening closure, depth $2) UNIONed with any taxonomy or certification bridge from ontology_crosswalk 107// that maps to the required skill's source soc_code, each row labeled with its bridge_kind and carrying 108// the evidence path. The curated graph hops carry their accumulated numeric strength; the crosswalk hop 109// is a categorical taxonomy link, so it carries the neutral strength 1.0 (an exact crosswalk equivalence) 110// and a one element relation chain naming the crosswalk relation. Both arms are read only over 111// allowlisted GLOBAL reference tables. 112// 113// The recursive determinism is identical to the closure: depth cap $2, the visited path cycle guard, 114// UNION not UNION ALL, and the skill_id tiebreaker followed by the bridge_kind and the full evidence 115// path on the final ORDER BY, so the order is total. acc_strength stays numeric. 116const skillGapBridgeSQL = ` 117WITH RECURSIVE closure AS ( 118 SELECT 119 sr.to_skill_id AS skill_id, 120 1 AS depth, 121 sr.strength::numeric AS acc_strength, 122 ARRAY[sr.from_skill_id, sr.to_skill_id] AS path, 123 ARRAY[sr.relation] AS relations 124 FROM skill_relationships sr 125 WHERE sr.from_skill_id = $1 126 AND sr.verified_at IS NOT NULL 127 AND sr.relation IN ('substitutes_for', 'broader_than') 128 UNION 129 SELECT 130 sr.to_skill_id, 131 c.depth + 1, 132 c.acc_strength * sr.strength, 133 c.path || sr.to_skill_id, 134 c.relations || sr.relation 135 FROM skill_relationships sr 136 JOIN closure c ON sr.from_skill_id = c.skill_id 137 WHERE sr.verified_at IS NOT NULL 138 AND sr.relation IN ('substitutes_for', 'broader_than') 139 AND c.depth < $2 140 AND NOT (sr.to_skill_id = ANY(c.path)) 141) 142SELECT 143 c.skill_id, 144 s.name AS skill_name, 145 c.depth, 146 c.acc_strength, 147 'curated_relationship' AS bridge_kind, 148 c.relations AS evidence_relations, 149 c.path AS evidence_path 150FROM closure c 151JOIN skills s ON s.id = c.skill_id 152UNION 153SELECT 154 b.id AS skill_id, 155 b.name AS skill_name, 156 1 AS depth, 157 1.0 AS acc_strength, 158 'ontology_crosswalk' AS bridge_kind, 159 ARRAY[ox.relation] AS evidence_relations, 160 ARRAY[$1::uuid, b.id] AS evidence_path 161FROM skills req 162JOIN ontology_crosswalk ox ON ox.from_code = req.soc_code AND req.soc_code <> '' 163JOIN skills b ON b.soc_code = ox.to_code AND b.soc_code <> '' AND b.id <> req.id 164WHERE req.id = $1 165ORDER BY depth, acc_strength DESC, skill_id, bridge_kind, evidence_path` 166 167// The two intents above are registered in the closed Registry in tag.go (the FIXED entries 168// skill_substitution_closure and skill_gap_bridge), so ValidateRegistry asserts them at boot (read only, 169// eeo free, allowlisted) and ExecuteIntent runs them through the same RunInTenantTx, guard, and audit 170// posture as every other TAG intent. The recursive SQL stays here, next to the typed read helpers, and 171// the Registry entries reference these constants by name. 172 173// ClampGraphDepth normalizes a requested depth to the bounded, deterministic range: a non positive or 174// missing depth becomes DefaultGraphDepth, and a depth above maxGraphDepth is capped, so the walk is 175// always bounded no matter what a caller binds. 176func ClampGraphDepth(depth int) int { 177 if depth <= 0 { 178 return DefaultGraphDepth 179 } 180 if depth > maxGraphDepth { 181 return maxGraphDepth 182 } 183 return depth 184} 185 186// SkillGraphNode is one reachable skill from a curated traversal: the skill, the hop depth at which it was 187// reached, the accumulated curated strength along the path (decimal, never float), the bridge kind that 188// reached it, and the evidence path (the ordered relation chain and the ordered skill id chain). It is 189// pure structure: there is no score, no band, and no employment decision number on it. 190type SkillGraphNode struct { 191 SkillID string `json:"skill_id"` 192 SkillName string `json:"skill_name"` 193 Depth int `json:"depth"` 194 Strength decimal.Decimal `json:"strength"` 195 BridgeKind string `json:"bridge_kind,omitempty"` 196 Relations []string `json:"relations"` 197 Path []string `json:"path"` 198} 199 200// SkillSubstitutionClosure runs the registered skill_substitution_closure intent and scans the result into 201// typed nodes, carrying the accumulated curated strength as decimal.Decimal (the numeric column is scanned 202// directly into decimal, never through float64). It is the typed read for callers that want the curated 203// neighborhood as structure; the generic ExecuteIntent path remains available for the narration layer. It 204// opens the same RLS scoped, app role transaction as every other read; the tables are GLOBAL reference, so 205// the walk is correct without any tenant data, and the uniform tenant posture gives one connection, audit, 206// and timeout discipline. 207func SkillSubstitutionClosure(ctx context.Context, db *tenant.DB, startSkillID string, depth int) ([]SkillGraphNode, error) { 208 if db == nil { 209 return nil, errors.New("ai: no database") 210 } 211 var out []SkillGraphNode 212 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 213 var e error 214 out, e = SkillSubstitutionClosureOnTx(ctx, tx, startSkillID, depth) 215 return e 216 }) 217 return out, err 218} 219 220// SkillSubstitutionClosureOnTx is SkillSubstitutionClosure on a caller supplied transaction, so a module 221// already inside a tenant transaction reuses the curated walk without nesting a transaction. It re guards 222// the static SQL and scans the numeric strength into decimal. 223func SkillSubstitutionClosureOnTx(ctx context.Context, tx pgx.Tx, startSkillID string, depth int) ([]SkillGraphNode, error) { 224 if err := GuardReadOnlyQuery(skillSubstitutionClosureSQL, ReadAllowlist); err != nil { 225 return nil, err 226 } 227 rows, err := tx.Query(ctx, skillSubstitutionClosureSQL, startSkillID, ClampGraphDepth(depth)) 228 if err != nil { 229 return nil, err 230 } 231 defer rows.Close() 232 var out []SkillGraphNode 233 for rows.Next() { 234 var n SkillGraphNode 235 if err := rows.Scan(&n.SkillID, &n.SkillName, &n.Depth, &n.Strength, &n.Relations, &n.Path); err != nil { 236 return nil, err 237 } 238 out = append(out, n) 239 } 240 return out, rows.Err() 241} 242 243// SkillGapBridge runs the registered skill_gap_bridge intent and scans the result into typed nodes. Like 244// the closure, the accumulated curated strength is carried as decimal.Decimal, never float64. 245func SkillGapBridge(ctx context.Context, db *tenant.DB, requiredSkillID string, depth int) ([]SkillGraphNode, error) { 246 if db == nil { 247 return nil, errors.New("ai: no database") 248 } 249 var out []SkillGraphNode 250 err := db.RunInTenantTx(ctx, func(ctx context.Context, tx pgx.Tx) error { 251 var e error 252 out, e = SkillGapBridgeOnTx(ctx, tx, requiredSkillID, depth) 253 return e 254 }) 255 return out, err 256} 257 258// SkillGapBridgeOnTx is SkillGapBridge on a caller supplied transaction. It re guards the static SQL and 259// scans the numeric strength into decimal. 260func SkillGapBridgeOnTx(ctx context.Context, tx pgx.Tx, requiredSkillID string, depth int) ([]SkillGraphNode, error) { 261 if err := GuardReadOnlyQuery(skillGapBridgeSQL, ReadAllowlist); err != nil { 262 return nil, err 263 } 264 rows, err := tx.Query(ctx, skillGapBridgeSQL, requiredSkillID, ClampGraphDepth(depth)) 265 if err != nil { 266 return nil, err 267 } 268 defer rows.Close() 269 var out []SkillGraphNode 270 for rows.Next() { 271 var n SkillGraphNode 272 if err := rows.Scan(&n.SkillID, &n.SkillName, &n.Depth, &n.Strength, &n.BridgeKind, &n.Relations, &n.Path); err != nil { 273 return nil, err 274 } 275 out = append(out, n) 276 } 277 return out, rows.Err() 278} 279