Source identity
| Field | Recorded value |
|---|---|
| Source type | GitHub source |
| Original record | Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql |
| Date | Not specified in source |
| Repository | Janady13/missionmatch |
| Revision | 720d47fae476b0d38477ae4401697d372b1ea2bc |
| Branch | feature/opportunity-to-outcome |
| Path or archive member | db/migrations/0018_staffing_recommendations.sql |
| Original SHA-256 | d55a39ac24bca801ade8a6803c9a0ec16dc95adcea0f8400b0f2cf1e2791a2b2 |
| Git blob SHA | 45eee8a571e2db13372b662b8a8fdef3b71cc283 |
| Reading-copy SHA-256 | d55a39ac24bca801ade8a6803c9a0ec16dc95adcea0f8400b0f2cf1e2791a2b2 |
Every recorded occurrence
These 24 records contain identical original source bytes. Their paths and revisions remain part of the archive.
| Original record | Branch or archive | Revision / identifier | Original location |
|---|---|---|---|
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | feature/opportunity-to-outcome | 720d47fae476b0d38477ae4401697d372b1ea2bc | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | per-tenant-routing | 9bde0f9a74b89d01980fa658e5253acda7528d0c | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | collision-deconfliction | 4ee5a09972608ed89f8377c6665542d5f450f0a6 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | ws6-retrieval | 6c56e29231ec05735927fa265c97d517499dfeaf | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | m5-price-to-win | 41000ad30fff4129fdaf5ec84e15ba14c27d4890 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | ws1-ontology-sync | b596f449a32a1016fbd403c36505cc00a1ac9ee6 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | master | 1e9249840a4302ec82e123b9c9cb6f75badea3e4 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | ws8-teaching-loop | c823f0fb6bb2aba0f39001eb6de96f4000d9321f | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | w3-ai-staffing-recommendations | 43d56387270ef4da4cabd794d97ca6a49909bc7d | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | mag-substrate | b82c7100a492dec67dfd1eda6b3f53def859be35 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | ws3-clearance | 4b8e7ce594c068621867f9c9deed0ec64ae6c553 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | phase2-eval-fairness | d361af254701975fe239927656ababb56374655e | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | time-to-fill-survival | b66e1e505863d4f17741a4b9ede73509e45bd6c6 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | graphrag-foundation | b0ddeb9f633fd138d09309d1a17b7d13bc3a67f7 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | deploy-and-ops | bdacd9d0b4d5998d25fc74bf4007f4c3ec538c7a | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | ws9-bench-ingestion | f585a9ef32fa554663ce1fba9a414a54b6e49cf0 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | ws2-alias-relationship | e4dada4601c6eb101830f9d223a9655dfa120616 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | presentation-readiness | eb574acaa29d71ce4d0f852572e87c275c79fdb7 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | vendor-intelligence | fe9796a19c261951d3f2565206c0c1b5ac294465 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | ws7-federal-signal | 6a9f6ce9cfb13345ffb47770d4960e7c2d9c17c2 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | ai-brain-live | aac32fc49c878cc5bca099459307611562d0bac1 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | phase2-connectors | e038d4c687aec16f8bd9bc4d907eea14d91f8704 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | feature/aioe-upgrade | 2178e7298d0e5d9af83afa12402fd587f0ba6ed8 | Open original |
| Janady13/missionmatch / db/migrations/0018_staffing_recommendations.sql | capture-readiness | bae6af7cb2997e819174a9e9cfff5e16067a1975 | Open original |
Recovered source transcript
Download complete source text ↓88 lines · 6.3 KB
-- +goose Up
-- W3 AI Staffing Recommendations. Two tables that turn the frozen engine scores into explained,
-- human decided staffing actions. recommendation_runs groups one generation (a gap solve, a portfolio
-- optimize, or a proactive recompute) and records the exact parameters used, so a run is reproducible
-- and auditable. staffing_recommendations is one recommended action per row, always tied to a frozen
-- candidate_evaluations score (never recomputed), classified deterministically (assign, reassign,
-- reskill, source), ranked and confidence scored deterministically, and decided by a human (never auto
-- rejected). The model only writes the rationale prose behind the faithfulness gate; nothing here is a
-- model decision. Both tables are RLS forced, org scoped, and candidate scoped for the right to erasure.
-- This is migration 0018 on the v17 head; the matching eeo set is unchanged. No eeo column is named
-- here, and none can be: the recommendation core reads only scores, skills, clearance, availability,
-- and assignment, never a protected attribute.
CREATE TABLE recommendation_runs (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL,
view text NOT NULL CHECK (view IN ('gap', 'portfolio', 'proactive')),
scope_ref uuid, -- requirement id for a gap run, candidate id for a proactive run, null for portfolio
model_version text NOT NULL, -- the frozen engine version plus the W3 logic version
parameters jsonb NOT NULL DEFAULT '{}', -- the staffing floor, adjacency threshold, value fallback, and optimizer envelope actually used
created_by uuid,
created_at timestamptz NOT NULL DEFAULT now(),
-- tenant composite key so child rows can reference (organization_id, id)
UNIQUE (organization_id, id),
-- the creator is a user in the same org; a user delete nulls the column rather than orphaning the run
FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL
);
CREATE INDEX idx_recommendation_runs_org_created ON recommendation_runs (organization_id, created_at DESC);
CREATE TABLE staffing_recommendations (
id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id uuid NOT NULL,
run_id uuid NOT NULL,
requirement_id uuid NOT NULL,
candidate_id uuid, -- null for a source recommendation (a gap coverage note that names no candidate)
action text NOT NULL CHECK (action IN ('assign', 'reassign', 'reskill', 'source', 'team')),
rank integer NOT NULL,
confidence numeric(5,4) NOT NULL CHECK (confidence BETWEEN 0 AND 1),
value_score bigint, -- the int64 scaled optimizer objective value, portfolio runs only
rationale text NOT NULL,
rationale_source text NOT NULL CHECK (rationale_source IN ('ai_faithful', 'deterministic')),
evidence jsonb NOT NULL DEFAULT '{}',-- reskill adjacency facts, reassign source requirement, portfolio counterfactual, faithfulness fallback reason
status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'dismissed', 'superseded')),
decided_by uuid,
decided_at timestamptz,
decision_note text,
created_at timestamptz NOT NULL DEFAULT now(),
UNIQUE (organization_id, id),
-- the run is in the same org; deleting a run removes its recommendations
FOREIGN KEY (organization_id, run_id) REFERENCES recommendation_runs (organization_id, id) ON DELETE CASCADE,
-- the gap requirement is in the same org; a hard delete removes its recommendations
FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE CASCADE,
-- The frozen engine score. candidate_evaluations is keyed by (organization_id, candidate_id,
-- requirement_id), so this composite foreign key IS the engine evaluation reference: every non source
-- recommendation points at a real, frozen evaluation row. Default MATCH SIMPLE means a source row
-- (candidate_id null) skips this check, which is correct since a source note has no candidate and no
-- evaluation. A candidate hard delete cascades the evaluation, which cascades the recommendation.
FOREIGN KEY (organization_id, candidate_id, requirement_id)
REFERENCES candidate_evaluations (organization_id, candidate_id, requirement_id) ON DELETE CASCADE,
-- the human decider is a user in the same org; a user delete nulls the column
FOREIGN KEY (organization_id, decided_by) REFERENCES users (organization_id, id) ON DELETE SET NULL
);
CREATE INDEX idx_staffing_recommendations_run ON staffing_recommendations (organization_id, run_id);
CREATE INDEX idx_staffing_recommendations_req ON staffing_recommendations (organization_id, requirement_id);
CREATE INDEX idx_staffing_recommendations_cand ON staffing_recommendations (organization_id, candidate_id);
CREATE INDEX idx_staffing_recommendations_status ON staffing_recommendations (organization_id, status);
ALTER TABLE recommendation_runs ENABLE ROW LEVEL SECURITY;
ALTER TABLE recommendation_runs FORCE ROW LEVEL SECURITY;
CREATE POLICY recommendation_runs_isolation ON recommendation_runs
USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);
ALTER TABLE staffing_recommendations ENABLE ROW LEVEL SECURITY;
ALTER TABLE staffing_recommendations FORCE ROW LEVEL SECURITY;
CREATE POLICY staffing_recommendations_isolation ON staffing_recommendations
USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid)
WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid);
-- The app role creates runs and recommendations, supersedes and decides recommendations, and deletes a
-- candidate's recommendations on erasure. Runs are immutable once written, so no update or delete there.
GRANT SELECT, INSERT ON recommendation_runs TO missionmatch_app;
GRANT SELECT, INSERT, UPDATE, DELETE ON staffing_recommendations TO missionmatch_app;
-- +goose Down
DROP TABLE IF EXISTS staffing_recommendations;
DROP TABLE IF EXISTS recommendation_runs;