Janady13/missionmatch / db/migrations/0002_matching.sql

1-- +goose Up 2-- Matching domain: skills ontology (global), opportunities, candidates, requirements, slates. 3-- 4-- Tenant isolation is defense-in-depth: RLS on organization_id (ENABLE+FORCE, USING+WITH CHECK) PLUS 5-- COMPOSITE foreign keys that carry organization_id, so a child row can NEVER reference a parent in 6-- another tenant, FK validation bypasses RLS, so WITH CHECK alone is necessary-but-not-sufficient 7-- [review RLS-1/INT-4]. Every tenant parent has UNIQUE (organization_id, id) as the composite-FK target. 8-- Clearance/poly/status/band are text + CHECK mirroring the engine enums' DB codes (engine Code()). 9 10-- --------------------------------------------------------------------------- 11-- skills, GLOBAL ontology reference (shared across tenants). No RLS. Partial 12-- HNSW so the index matches the "WHERE embedding IS NOT NULL" query predicate. 13-- --------------------------------------------------------------------------- 14CREATE TABLE skills ( 15 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 16 name text NOT NULL UNIQUE, 17 category text, 18 embedding vector(384), -- MVP self-hosted MiniLM-class dim; config-pinned (STACK_DECISIONS.md §3/§6) 19 created_at timestamptz NOT NULL DEFAULT now() 20); 21CREATE INDEX idx_skills_embedding_hnsw ON skills 22 USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64) 23 WHERE embedding IS NOT NULL; 24GRANT SELECT, INSERT, UPDATE ON skills TO missionmatch_app; 25 26-- --------------------------------------------------------------------------- 27-- opportunities, a proposal/solicitation that groups requirements, so 28-- /proposal-readiness aggregates across a proposal, not a single LCAT [review COMPL-2]. 29-- --------------------------------------------------------------------------- 30CREATE TABLE opportunities ( 31 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 32 organization_id uuid NOT NULL REFERENCES organizations(id), 33 name text NOT NULL, 34 solicitation_number text, 35 customer_agency text, 36 due_date date, 37 status text NOT NULL DEFAULT 'open' 38 CHECK (status IN ('open','submitted','won','lost','no_bid')), 39 created_at timestamptz NOT NULL DEFAULT now(), 40 updated_at timestamptz NOT NULL DEFAULT now(), 41 CONSTRAINT uq_opportunities_org_id UNIQUE (organization_id, id) 42); 43CREATE INDEX idx_opportunities_org ON opportunities (organization_id, created_at DESC); 44ALTER TABLE opportunities ENABLE ROW LEVEL SECURITY; 45ALTER TABLE opportunities FORCE ROW LEVEL SECURITY; 46CREATE POLICY opportunities_tenant_isolation ON opportunities 47 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 48 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 49 50-- --------------------------------------------------------------------------- 51-- candidates, tenant-scoped. NO demographic columns (AEDT). clearance_status is 52-- a stored flag; clearance_expires_at is the source of truth for /clearance-status 53-- and as-of-date active/expired scoring [review COMPL-1]. Partial GIN/HNSW match 54-- the soft-delete query predicate [review PGV-6]. 55-- --------------------------------------------------------------------------- 56CREATE TABLE candidates ( 57 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 58 organization_id uuid NOT NULL REFERENCES organizations(id), 59 full_name text NOT NULL, 60 email text, 61 headline text, 62 clearance_level text NOT NULL DEFAULT 'unknown' 63 CHECK (clearance_level IN ('unknown','none','confidential','secret','top_secret','ts_sci')), 64 clearance_poly text NOT NULL DEFAULT 'none' 65 CHECK (clearance_poly IN ('none','ci','full_scope')), 66 clearance_status text NOT NULL DEFAULT 'unknown' 67 CHECK (clearance_status IN ('unknown','active','expired','inactive')), 68 clearance_granted_at date, 69 clearance_expires_at date, 70 reinvestigation_due_at date, 71 years_experience int NOT NULL DEFAULT 0 CHECK (years_experience >= 0), 72 location text, 73 remote boolean NOT NULL DEFAULT false, 74 relocatable boolean NOT NULL DEFAULT false, 75 available boolean NOT NULL DEFAULT false, 76 resume_embedding vector(384), 77 search_tsv tsvector GENERATED ALWAYS AS ( 78 to_tsvector('english', 79 coalesce(full_name,'') || ' ' || coalesce(headline,'') || ' ' || coalesce(location,'')) 80 ) STORED, 81 created_at timestamptz NOT NULL DEFAULT now(), 82 updated_at timestamptz NOT NULL DEFAULT now(), 83 deleted_at timestamptz, 84 CONSTRAINT uq_candidates_org_id UNIQUE (organization_id, id) 85); 86CREATE INDEX idx_candidates_org ON candidates (organization_id, created_at DESC); 87CREATE INDEX idx_candidates_tsv ON candidates USING gin (search_tsv) WHERE deleted_at IS NULL; 88CREATE INDEX idx_candidates_emb_hnsw ON candidates USING hnsw (resume_embedding vector_cosine_ops) 89 WITH (m = 16, ef_construction = 64) 90 WHERE deleted_at IS NULL AND resume_embedding IS NOT NULL; 91CREATE INDEX idx_candidates_clearance_exp ON candidates (organization_id, clearance_expires_at) 92 WHERE deleted_at IS NULL; 93ALTER TABLE candidates ENABLE ROW LEVEL SECURITY; 94ALTER TABLE candidates FORCE ROW LEVEL SECURITY; 95CREATE POLICY candidates_tenant_isolation ON candidates 96 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 97 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 98 99-- candidate child tables: denormalized org + COMPOSITE FK to the parent (org,id) ---------------- 100CREATE TABLE candidate_skills ( 101 organization_id uuid NOT NULL, 102 candidate_id uuid NOT NULL, 103 skill_id uuid NOT NULL REFERENCES skills(id), 104 PRIMARY KEY (candidate_id, skill_id), 105 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE 106); 107CREATE INDEX idx_cand_skills_org ON candidate_skills (organization_id, candidate_id); 108ALTER TABLE candidate_skills ENABLE ROW LEVEL SECURITY; 109ALTER TABLE candidate_skills FORCE ROW LEVEL SECURITY; 110CREATE POLICY cand_skills_isolation ON candidate_skills 111 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 112 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 113 114CREATE TABLE candidate_certifications ( 115 organization_id uuid NOT NULL, 116 candidate_id uuid NOT NULL, 117 name text NOT NULL, 118 PRIMARY KEY (candidate_id, name), 119 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE 120); 121CREATE INDEX idx_cand_certs_org ON candidate_certifications (organization_id, candidate_id); 122ALTER TABLE candidate_certifications ENABLE ROW LEVEL SECURITY; 123ALTER TABLE candidate_certifications FORCE ROW LEVEL SECURITY; 124CREATE POLICY cand_certs_isolation ON candidate_certifications 125 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 126 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 127 128CREATE TABLE candidate_customer_experience ( 129 organization_id uuid NOT NULL, 130 candidate_id uuid NOT NULL, 131 agency text NOT NULL, 132 PRIMARY KEY (candidate_id, agency), 133 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE 134); 135CREATE INDEX idx_cand_custexp_org ON candidate_customer_experience (organization_id, candidate_id); 136ALTER TABLE candidate_customer_experience ENABLE ROW LEVEL SECURITY; 137ALTER TABLE candidate_customer_experience FORCE ROW LEVEL SECURITY; 138CREATE POLICY cand_custexp_isolation ON candidate_customer_experience 139 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 140 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 141 142-- --------------------------------------------------------------------------- 143-- requirements, tenant-scoped; optionally grouped under an opportunity. 144-- --------------------------------------------------------------------------- 145CREATE TABLE requirements ( 146 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 147 organization_id uuid NOT NULL REFERENCES organizations(id), 148 opportunity_id uuid, 149 title text NOT NULL, 150 customer_agency text, 151 required_level text NOT NULL DEFAULT 'none' 152 CHECK (required_level IN ('unknown','none','confidential','secret','top_secret','ts_sci')), 153 required_poly text NOT NULL DEFAULT 'none' 154 CHECK (required_poly IN ('none','ci','full_scope')), 155 required_years int NOT NULL DEFAULT 0 CHECK (required_years >= 0), 156 work_location text, 157 remote_allowed boolean NOT NULL DEFAULT false, 158 key_personnel boolean NOT NULL DEFAULT false, 159 created_at timestamptz NOT NULL DEFAULT now(), 160 updated_at timestamptz NOT NULL DEFAULT now(), 161 deleted_at timestamptz, 162 CONSTRAINT uq_requirements_org_id UNIQUE (organization_id, id), 163 FOREIGN KEY (organization_id, opportunity_id) REFERENCES opportunities (organization_id, id) ON DELETE SET NULL 164); 165CREATE INDEX idx_requirements_org ON requirements (organization_id, created_at DESC); 166CREATE INDEX idx_requirements_opp ON requirements (organization_id, opportunity_id); 167ALTER TABLE requirements ENABLE ROW LEVEL SECURITY; 168ALTER TABLE requirements FORCE ROW LEVEL SECURITY; 169CREATE POLICY requirements_tenant_isolation ON requirements 170 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 171 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 172 173CREATE TABLE required_skills ( 174 organization_id uuid NOT NULL, 175 requirement_id uuid NOT NULL, 176 skill_id uuid NOT NULL REFERENCES skills(id), 177 mandatory boolean NOT NULL DEFAULT false, 178 PRIMARY KEY (requirement_id, skill_id), 179 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE CASCADE 180); 181CREATE INDEX idx_req_skills_org ON required_skills (organization_id, requirement_id); 182ALTER TABLE required_skills ENABLE ROW LEVEL SECURITY; 183ALTER TABLE required_skills FORCE ROW LEVEL SECURITY; 184CREATE POLICY req_skills_isolation ON required_skills 185 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 186 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 187 188CREATE TABLE required_certifications ( 189 organization_id uuid NOT NULL, 190 requirement_id uuid NOT NULL, 191 name text NOT NULL, 192 PRIMARY KEY (requirement_id, name), 193 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id) ON DELETE CASCADE 194); 195CREATE INDEX idx_req_certs_org ON required_certifications (organization_id, requirement_id); 196ALTER TABLE required_certifications ENABLE ROW LEVEL SECURITY; 197ALTER TABLE required_certifications FORCE ROW LEVEL SECURITY; 198CREATE POLICY req_certs_isolation ON required_certifications 199 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 200 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 201 202-- --------------------------------------------------------------------------- 203-- slates + slate_members. slate_members freezes the explainable match result as 204-- the AEDT decision record. Scores are numeric(7,4) so a perfect 100.0000 fits 205-- (numeric(6,4) maxed at 99.9999 and overflowed) with CHECK 0..100 [review SQLC-1]. 206-- --------------------------------------------------------------------------- 207CREATE TABLE slates ( 208 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), 209 organization_id uuid NOT NULL REFERENCES organizations(id), 210 requirement_id uuid NOT NULL, 211 name text NOT NULL, 212 status text NOT NULL DEFAULT 'draft' 213 CHECK (status IN ('draft','in_review','finalized')), 214 created_by uuid, 215 created_at timestamptz NOT NULL DEFAULT now(), 216 updated_at timestamptz NOT NULL DEFAULT now(), 217 CONSTRAINT uq_slates_org_id UNIQUE (organization_id, id), 218 FOREIGN KEY (organization_id, requirement_id) REFERENCES requirements (organization_id, id), 219 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) 220); 221CREATE INDEX idx_slates_org ON slates (organization_id, created_at DESC); 222CREATE INDEX idx_slates_req ON slates (organization_id, requirement_id); 223ALTER TABLE slates ENABLE ROW LEVEL SECURITY; 224ALTER TABLE slates FORCE ROW LEVEL SECURITY; 225CREATE POLICY slates_tenant_isolation ON slates 226 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 227 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 228 229CREATE TABLE slate_members ( 230 organization_id uuid NOT NULL, 231 slate_id uuid NOT NULL, 232 candidate_id uuid NOT NULL, 233 total_score numeric(7,4) NOT NULL CHECK (total_score BETWEEN 0 AND 100), 234 band text NOT NULL CHECK (band IN ('green','yellow','red')), 235 clearance_score numeric(7,4) NOT NULL CHECK (clearance_score BETWEEN 0 AND 100), 236 skills_score numeric(7,4) NOT NULL CHECK (skills_score BETWEEN 0 AND 100), 237 certs_score numeric(7,4) NOT NULL CHECK (certs_score BETWEEN 0 AND 100), 238 customer_score numeric(7,4) NOT NULL CHECK (customer_score BETWEEN 0 AND 100), 239 experience_score numeric(7,4) NOT NULL CHECK (experience_score BETWEEN 0 AND 100), 240 location_score numeric(7,4) NOT NULL CHECK (location_score BETWEEN 0 AND 100), 241 explanation text NOT NULL, 242 trace jsonb NOT NULL, 243 gap_summary jsonb NOT NULL DEFAULT '[]', 244 decision text NOT NULL DEFAULT 'pending' 245 CHECK (decision IN ('pending','approved','rejected')), -- human-in-the-loop; never auto-reject 246 decided_by uuid, 247 decided_at timestamptz, 248 rank int, 249 created_at timestamptz NOT NULL DEFAULT now(), 250 PRIMARY KEY (slate_id, candidate_id), 251 FOREIGN KEY (organization_id, slate_id) REFERENCES slates (organization_id, id) ON DELETE CASCADE, 252 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id), 253 FOREIGN KEY (organization_id, decided_by) REFERENCES users (organization_id, id) 254); 255CREATE INDEX idx_slate_members_org ON slate_members (organization_id, slate_id); 256ALTER TABLE slate_members ENABLE ROW LEVEL SECURITY; 257ALTER TABLE slate_members FORCE ROW LEVEL SECURITY; 258CREATE POLICY slate_members_isolation ON slate_members 259 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 260 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 261 262GRANT SELECT, INSERT, UPDATE, DELETE ON 263 opportunities, 264 candidates, candidate_skills, candidate_certifications, candidate_customer_experience, 265 requirements, required_skills, required_certifications, 266 slates, slate_members 267 TO missionmatch_app; 268 269-- +goose Down 270DROP TABLE IF EXISTS slate_members; 271DROP TABLE IF EXISTS slates; 272DROP TABLE IF EXISTS required_certifications; 273DROP TABLE IF EXISTS required_skills; 274DROP TABLE IF EXISTS requirements; 275DROP TABLE IF EXISTS candidate_customer_experience; 276DROP TABLE IF EXISTS candidate_certifications; 277DROP TABLE IF EXISTS candidate_skills; 278DROP TABLE IF EXISTS candidates; 279DROP TABLE IF EXISTS opportunities; 280DROP TABLE IF EXISTS skills; 281