Janady13/missionmatch / db/migrations/0090_saved_searches_and_lists.sql

1-- +goose Up 2-- SEARCH, SAVED SEARCHES, AND LISTS. The recruiter's actual working surface. 3-- 4-- The gap was worse than the research suggested. There were no saved searches, and there was no SEARCH: the 5-- candidates route took a limit and an offset and nothing else, so "TS/SCI cleared network engineers in 6-- Maryland who are available" was not a question the product could be asked. The indexes to answer it have 7-- existed since migration 0002, a GIN index over search_tsv and an HNSW index over resume_embedding, and 8-- nothing was mounted on top of them. 9-- 10-- So this migration adds the two things a search becomes useful WITH, and the search itself is built on the 11-- indexes that were already there. 12-- 13-- saved_searches a named set of filters, re run on demand. The question you ask every morning. 14-- candidate_lists a named STATIC set of people. A shortlist, a bench for a bid, a do not contact roster. 15-- 16-- THE TWO ARE DIFFERENT AND BOTH ARE NEEDED, which is why this is not one table with a flag. A saved search 17-- is a QUESTION and its answer changes as the bench changes; a list is an ANSWER somebody committed to and it 18-- must not change underneath them. An agency that shortlists nine people for a bid needs those nine to still 19-- be those nine next week. 20 21-- ============================================================================ 22-- 1. saved_searches 23-- ============================================================================ 24-- The criteria are JSONB rather than typed columns, and that is the opposite of the choice made for 25-- org_settings in migration 0087. The reasoning differs because the risk differs. A setting decides who may 26-- see pay data, so a key value bag would make the wrong setting a one line insert; a saved search is a 27-- person's own query with no security semantics, and the filter vocabulary genuinely grows. What protects 28-- this is that the DATABASE NEVER INTERPRETS THE JSON: it is validated back into a typed struct in Go on 29-- every execution and every field becomes a bound parameter, so a stored criteria blob is never a query. 30CREATE TABLE saved_searches ( 31 organization_id uuid NOT NULL REFERENCES organizations(id), 32 id uuid NOT NULL DEFAULT gen_random_uuid(), 33 name text NOT NULL, 34 -- owner_user_id is who it belongs to. A PRIVATE search is visible only to them; a SHARED one is visible 35 -- to the organization. Shared is not the default, because a half finished search somebody was 36 -- experimenting with should not appear on a colleague's screen. 37 owner_user_id uuid NOT NULL, 38 visibility text NOT NULL DEFAULT 'private' CHECK (visibility IN ('private', 'shared')), 39 criteria jsonb NOT NULL DEFAULT '{}'::jsonb, 40 -- Pinning is what makes this a working surface rather than a filing cabinet: the three searches a 41 -- recruiter runs every morning sit at the top and the other forty do not. 42 pinned boolean NOT NULL DEFAULT false, 43 last_run_at timestamptz, 44 created_at timestamptz NOT NULL DEFAULT now(), 45 updated_at timestamptz NOT NULL DEFAULT now(), 46 PRIMARY KEY (organization_id, id), 47 CONSTRAINT fk_saved_searches_owner FOREIGN KEY (organization_id, owner_user_id) 48 REFERENCES users (organization_id, id) ON DELETE CASCADE, 49 CONSTRAINT ck_saved_search_name CHECK (length(btrim(name)) BETWEEN 1 AND 120), 50 -- A criteria blob is bounded so a saved search cannot become a place to store arbitrary data. 51 CONSTRAINT ck_saved_search_size CHECK (length(criteria::text) <= 8000) 52); 53-- One person may not have two searches by the same name. Two people may. 54CREATE UNIQUE INDEX uq_saved_searches_name ON saved_searches (organization_id, owner_user_id, lower(btrim(name))); 55CREATE INDEX idx_saved_searches_visible ON saved_searches (organization_id, visibility, pinned DESC, lower(name)); 56ALTER TABLE saved_searches ENABLE ROW LEVEL SECURITY; 57ALTER TABLE saved_searches FORCE ROW LEVEL SECURITY; 58CREATE POLICY saved_searches_isolation ON saved_searches 59 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 60 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 61-- DELETE is granted. A saved search is a person's own working tool, not a historical claim about anybody, so 62-- deleting one destroys no evidence. This is the same reasoning recorded for team_members. 63GRANT SELECT, INSERT, UPDATE, DELETE ON saved_searches TO missionmatch_app; 64 65-- ============================================================================ 66-- 2. candidate_lists 67-- ============================================================================ 68CREATE TABLE candidate_lists ( 69 organization_id uuid NOT NULL REFERENCES organizations(id), 70 id uuid NOT NULL DEFAULT gen_random_uuid(), 71 name text NOT NULL, 72 description text NOT NULL DEFAULT '', 73 owner_user_id uuid NOT NULL, 74 visibility text NOT NULL DEFAULT 'shared' CHECK (visibility IN ('private', 'shared')), 75 -- purpose is a label, and it matters for one value in particular. A do_not_contact list is a compliance 76 -- artefact: a person who asked not to be approached again. Naming the purpose means the interface can 77 -- treat that list differently from a shortlist rather than trusting somebody to read the title. 78 purpose text NOT NULL DEFAULT 'shortlist' 79 CHECK (purpose IN ('shortlist','bench','pipeline','do_not_contact','other')), 80 status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')), 81 created_at timestamptz NOT NULL DEFAULT now(), 82 updated_at timestamptz NOT NULL DEFAULT now(), 83 PRIMARY KEY (organization_id, id), 84 CONSTRAINT uq_candidate_lists_org_id UNIQUE (organization_id, id), 85 CONSTRAINT fk_candidate_lists_owner FOREIGN KEY (organization_id, owner_user_id) 86 REFERENCES users (organization_id, id) ON DELETE CASCADE, 87 CONSTRAINT ck_candidate_list_name CHECK (length(btrim(name)) BETWEEN 1 AND 120) 88); 89CREATE UNIQUE INDEX uq_candidate_lists_name ON candidate_lists (organization_id, lower(btrim(name))) 90 WHERE status = 'active'; 91ALTER TABLE candidate_lists ENABLE ROW LEVEL SECURITY; 92ALTER TABLE candidate_lists FORCE ROW LEVEL SECURITY; 93CREATE POLICY candidate_lists_isolation ON candidate_lists 94 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 95 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 96-- NO DELETE. A list is ARCHIVED, because "who did we shortlist for that bid in March" is a question somebody 97-- will ask in June, and because a do_not_contact list that can be deleted is a compliance record that can be 98-- deleted. 99GRANT SELECT, INSERT, UPDATE ON candidate_lists TO missionmatch_app; 100 101-- ============================================================================ 102-- 3. candidate_list_members 103-- ============================================================================ 104CREATE TABLE candidate_list_members ( 105 organization_id uuid NOT NULL REFERENCES organizations(id), 106 list_id uuid NOT NULL, 107 candidate_id uuid NOT NULL, 108 -- Why this person is on this list. One line, written when they were added, readable by whoever picks the 109 -- list up later. A shortlist with no reasons is a list somebody has to rebuild from memory. 110 note text NOT NULL DEFAULT '', 111 added_by uuid, 112 added_at timestamptz NOT NULL DEFAULT now(), 113 PRIMARY KEY (organization_id, list_id, candidate_id), 114 CONSTRAINT fk_list_members_list FOREIGN KEY (organization_id, list_id) 115 REFERENCES candidate_lists (organization_id, id) ON DELETE CASCADE, 116 CONSTRAINT fk_list_members_candidate FOREIGN KEY (organization_id, candidate_id) 117 REFERENCES candidates (organization_id, id) ON DELETE CASCADE, 118 CONSTRAINT fk_list_members_adder FOREIGN KEY (organization_id, added_by) 119 REFERENCES users (organization_id, id) ON DELETE SET NULL, 120 CONSTRAINT ck_list_member_note CHECK (length(note) <= 500) 121); 122-- The reverse lookup: which lists is this person on. Asked on every candidate screen, so it gets its own 123-- index rather than a sequential scan of the membership table. 124CREATE INDEX idx_list_members_candidate ON candidate_list_members (organization_id, candidate_id); 125CREATE INDEX idx_list_members_list ON candidate_list_members (organization_id, list_id, added_at DESC); 126ALTER TABLE candidate_list_members ENABLE ROW LEVEL SECURITY; 127ALTER TABLE candidate_list_members FORCE ROW LEVEL SECURITY; 128CREATE POLICY candidate_list_members_isolation ON candidate_list_members 129 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 130 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 131-- DELETE is granted here, unlike the list itself. Removing somebody from a shortlist is a correction, not the 132-- destruction of a record, and the alternative is shortlists that only ever grow. 133GRANT SELECT, INSERT, UPDATE, DELETE ON candidate_list_members TO missionmatch_app; 134 135-- ============================================================================ 136-- 4. the indexes search needs that did not exist 137-- ============================================================================ 138-- The GIN index over search_tsv and the HNSW index over resume_embedding have been there since 0002. These 139-- are the STRUCTURED filters a recruiter combines with the text: clearance and availability together are the 140-- first question asked on a cleared desk, and years of experience is the usual third. 141CREATE INDEX idx_candidates_clearance_avail ON candidates (organization_id, clearance_level, available) 142 WHERE deleted_at IS NULL; 143CREATE INDEX idx_candidates_years ON candidates (organization_id, years_experience) 144 WHERE deleted_at IS NULL; 145 146SELECT mm_mirror_staging_role_privileges(); 147 148-- +goose Down 149DROP INDEX IF EXISTS idx_candidates_years; 150DROP INDEX IF EXISTS idx_candidates_clearance_avail; 151DROP TABLE IF EXISTS candidate_list_members; 152DROP TABLE IF EXISTS candidate_lists; 153DROP TABLE IF EXISTS saved_searches; 154