Janady13/missionmatch / db/queries/teaching.sql

1-- name: ListSlateDecisionEvidenceForOrg :many 2-- The WS8 in tenant teaching read. It returns ONLY the contents free craft evidence the producer folds 3-- into scrub.TeachingSignal counts: the reviewer decision on each slate member, the stored sub scores that 4-- reveal a clearance gap (clearance_score) or an experience shortfall (experience_score), the frozen gap 5-- summary, and the matched skills evidence (per skill similarity and whether the match was exact or a 6-- semantic substitution) from the candidate's persisted evaluation. It carries NO name, NO email, NO free 7-- text resume, only the decision label, the scores, and the jsonb match evidence the engine itself froze. 8-- 9-- Both slate_members and candidate_evaluations are RLS scoped, and the producer runs this inside 10-- RunInTenantTx on the app pool, so the join is scoped to the caller's org by Postgres Row Level Security 11-- and can never read another tenant's decisions. candidate_evaluations is LEFT joined on the pair 12-- (organization_id, candidate_id, requirement_id): a slate member that was never screened through 13-- screenMATCH has no stored evaluation, so the matched skills evidence is then NULL and the producer folds 14-- only what slate_members alone supports (the clearance and experience patterns), never fabricating a 15-- skill substitution it cannot see. 16SELECT 17 sm.candidate_id, 18 sm.decision, 19 sm.clearance_score, 20 sm.experience_score, 21 sm.gap_summary, 22 s.requirement_id, 23 r.required_level, 24 ce.matched_skills 25FROM slate_members sm 26JOIN slates s 27 ON s.organization_id = sm.organization_id 28 AND s.id = sm.slate_id 29JOIN requirements r 30 ON r.organization_id = s.organization_id 31 AND r.id = s.requirement_id 32LEFT JOIN candidate_evaluations ce 33 ON ce.organization_id = sm.organization_id 34 AND ce.candidate_id = sm.candidate_id 35 AND ce.requirement_id = s.requirement_id 36ORDER BY sm.slate_id, sm.candidate_id; 37 38-- name: UpsertTeachingSignalAggregate :exec 39-- Persist one folded per tenant craft aggregate into the global teaching_signal_aggregates table. This is 40-- a GLOBAL migrator owned table (app has SELECT only), so the producer runs this through the migrator pool 41-- seam, never the app role. The unique key is (coalesce(tenant_id, the nil uuid), pattern, band, outcome, 42-- model_version, window_start), so a rerun over the same window for the same tenant refreshes the counts in 43-- place rather than inserting a duplicate, and a forked engine model version lands as a distinct row rather 44-- than silently overwriting a prior version's aggregate. The live in tenant rows always carry tenant_id set 45-- and dp_applied false; the flag on cross tenant path writes tenant_id NULL, dp_applied true rows later. 46INSERT INTO teaching_signal_aggregates ( 47 tenant_id, dp_applied, pattern, band, outcome, model_version, 48 observations, successes, window_start, window_end 49) VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10) 50ON CONFLICT ( 51 coalesce(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid), 52 pattern, band, outcome, model_version, window_start 53) DO UPDATE SET 54 observations = EXCLUDED.observations, 55 successes = EXCLUDED.successes, 56 window_end = EXCLUDED.window_end, 57 dp_applied = EXCLUDED.dp_applied; 58 59-- name: ListTeachingAggregatesForTenant :many 60-- The cross cutting GOVERNANCE CONFIDENCE the proposal generator reads: this tenant's own folded craft counts 61-- for the live in tenant rows (dp_applied false). The generator uses these to confirm a craft pattern is 62-- reliably approved at a band before it ever proposes a curated edge, so the specific recurring pair (read 63-- from the tenant's own evaluations) is only proposed when the contents free aggregate also supports it. 64-- These rows carry NO PII and no candidate id, only the counts. Migrator pool, the global table; the tenant 65-- is passed explicitly because this read is governance confidence, not the RLS scoped evaluation scan. 66SELECT id, tenant_id, dp_applied, pattern, band, outcome, model_version, observations, successes, 67 window_start, window_end, created_at 68 FROM teaching_signal_aggregates 69 WHERE tenant_id = $1 70 AND dp_applied = false 71 AND model_version = $2; 72 73-- name: GetLatestDpBudgetLedger :one 74-- The hard spend gate read. It returns the most recent ledger row for one (model_version, tenant_id) budget 75-- key, which carries the running accumulated RDP after every release composed so far. The gate adds the 76-- candidate release's per order RDP to this accumulation, reconverts to epsilon at the configured delta, and 77-- refuses the release (writing nothing) if it would exceed the budget. coalesce maps a NULL tenant_id (the 78-- cross tenant cohort) onto the fixed nil uuid so the cross tenant releases share one budget key space, the 79-- same convention the aggregates table uses. Ordered released_at then id descending so the newest composed 80-- accumulation is row one; zero rows means this is the first release on a fresh budget. dp_budget_ledger is a 81-- global migrator owned table the app role can only SELECT, so this read runs through the migrator pool. 82SELECT id, model_version, tenant_id, rdp_orders, accumulated_rdp, epsilon_at_delta, delta, 83 contributing_tenant_count, released_at 84 FROM dp_budget_ledger 85 WHERE model_version = $1 86 AND coalesce(tenant_id, '00000000-0000-0000-0000-000000000000'::uuid) 87 = coalesce(sqlc.narg('tenant_id')::uuid, '00000000-0000-0000-0000-000000000000'::uuid) 88 ORDER BY released_at DESC, id DESC 89 LIMIT 1; 90 91-- name: InsertDpBudgetLedger :one 92-- Record one differential privacy release in the append only budget ledger. The gate inserts this ONLY after 93-- it has confirmed the release stays within budget, so a persisted row is by construction an in budget, 94-- already audited release. rdp_orders is the jsonb map of Renyi order to the per order accumulated RDP at 95-- this release; accumulated_rdp is the running RDP over the worst case (max over the grid converted) used for 96-- the human readable summary; epsilon_at_delta is the converted epsilon (the min over orders) at the target 97-- delta; delta is that target; contributing_tenant_count is the number of live non test distinct 98-- organizations in the cohort, sourced from the production flag, never a literal. The spent epsilon columns 99-- are numeric so the audit record is bit stable while the grid math ran in float64. dp_budget_ledger is a 100-- global migrator owned table the app role can only SELECT, so this write runs through the migrator pool. 101INSERT INTO dp_budget_ledger ( 102 model_version, tenant_id, rdp_orders, accumulated_rdp, epsilon_at_delta, delta, 103 contributing_tenant_count 104) VALUES ($1, $2, $3, $4, $5, $6, $7) 105RETURNING id, model_version, tenant_id, rdp_orders, accumulated_rdp, epsilon_at_delta, delta, 106 contributing_tenant_count, released_at; 107 108-- name: ResolveSkillIDByName :one 109-- Resolve a skill name to its global skills.id, lower cased and trimmed, so a matched_skills entry that 110-- carries a candidate or required skill NAME but no id can still be turned into the id the proposal and the 111-- staged edge need. The skills table is global reference data the app role may SELECT; the proposal generator 112-- runs this through the same RLS scoped app tx that read the org's evaluations (the read is the only app 113-- pool step, the proposal WRITE goes through the migrator pool). 114SELECT id FROM skills WHERE lower(name) = lower(trim($1)); 115 116-- name: GetActiveProposalForPair :one 117-- Idempotency guard for the proposal generator: returns the existing proposal for a (from_skill_id, 118-- to_skill_id, relation) triple that is still pending or already approved, so the generator never creates a 119-- duplicate suggestion for a pair a human is already deciding or has already decided. A rejected proposal is 120-- intentionally NOT matched here, so a human who rejected a pair can see it proposed again if the evidence 121-- recurs (the human stays in control either way). Migrator pool, the global table. 122SELECT id, kind, status, from_skill_id, to_skill_id, relation, proposed_strength 123 FROM curation_proposals 124 WHERE from_skill_id = $1 125 AND to_skill_id = $2 126 AND relation = $3 127 AND status IN ('pending', 'approved') 128 LIMIT 1; 129 130-- name: InsertCurationProposal :one 131-- Create a pending curation proposal. Never auto applied and never auto rejected: status defaults to pending, 132-- approver and approved_at stay NULL until a human acts. Migrator pool only, because curation_proposals is a 133-- global migrator owned table the app role can only SELECT. 134INSERT INTO curation_proposals ( 135 kind, from_skill_id, to_skill_id, relation, proposed_strength, 136 evidence_observations, evidence_successes, model_version 137) VALUES ($1, $2, $3, $4, $5, $6, $7, $8) 138RETURNING id, kind, from_skill_id, to_skill_id, relation, proposed_strength, 139 evidence_observations, evidence_successes, model_version, status, approver, approved_at, created_at; 140 141-- name: ListPendingProposals :many 142-- The /compliance surface of proposals awaiting the first human step. Migrator pool, the global table, gated 143-- behind the compliance or operator role at the handler. Ordered oldest first so the queue is a stable list. 144SELECT id, kind, from_skill_id, to_skill_id, relation, proposed_strength, 145 evidence_observations, evidence_successes, model_version, status, approver, approved_at, created_at 146 FROM curation_proposals 147 WHERE status = 'pending' 148 ORDER BY created_at, id; 149 150-- name: GetProposal :one 151-- Read one proposal by id for the approve and verify handlers. Migrator pool, the global table. 152SELECT id, kind, from_skill_id, to_skill_id, relation, proposed_strength, 153 evidence_observations, evidence_successes, model_version, status, approver, approved_at, created_at 154 FROM curation_proposals 155 WHERE id = $1; 156 157-- name: ApproveProposalTx :one 158-- The first of two human steps. Set status to approved and record the approver and approved_at, but ONLY when 159-- the proposal is still pending, so a double submit or a concurrent approval cannot move an already decided 160-- proposal. Returns the row only on a real transition; zero rows means it was not pending. Migrator pool. 161UPDATE curation_proposals 162 SET status = 'approved', approver = $2, approved_at = now() 163 WHERE id = $1 164 AND status = 'pending' 165RETURNING id, kind, from_skill_id, to_skill_id, relation, proposed_strength, 166 evidence_observations, evidence_successes, model_version, status, approver, approved_at, created_at; 167 168-- name: StageSubstitutesForEdge :one 169-- Insert the staged, INERT skill_relationships edge that an approval creates. verified_at is left NULL, so the 170-- resolver (which loads only verified_at IS NOT NULL edges) ignores it and no score changes. A SECOND distinct 171-- human action later sets verified_at to activate it. On a conflicting (from, to, relation) that already 172-- exists, do nothing and return the existing row, so an approval is idempotent against a manually seeded edge. 173-- Migrator pool only (skill_relationships is global, the app role has SELECT only). 174INSERT INTO skill_relationships (from_skill_id, to_skill_id, relation, strength, source, verified_at) 175VALUES ($1, $2, 'substitutes_for', $3, 'ws8_curation', NULL) 176ON CONFLICT (from_skill_id, to_skill_id, relation) DO UPDATE SET source = skill_relationships.source 177RETURNING id, from_skill_id, to_skill_id, relation, strength, verified_at; 178 179-- name: VerifyStagedEdge :one 180-- The second of two human steps. Activate a staged edge by setting verified_at and recording the verifier, 181-- ONLY when it is still unverified, so a double submit cannot re activate or change the verifier of an already 182-- live edge. The resolver reads verified_at IS NOT NULL edges, so this single write is what makes the edge 183-- live across tenants. The engine math is byte identical: only the resolver reference set grew. Migrator pool. 184UPDATE skill_relationships 185 SET verified_at = now(), verifier = $2 186 WHERE id = $1 187 AND verified_at IS NULL 188RETURNING id, from_skill_id, to_skill_id, relation, strength, verified_at, verifier; 189