Janady13/missionmatch / db/migrations/0091_job_postings_and_inbound.sql

1-- +goose Up 2-- THE INBOUND PATH. A public job posting and a way for a person to apply to it. 3-- 4-- Until now every candidate had to be imported or typed in by a recruiter. There was no careers page, no 5-- posting, and no apply route, which means the product could only ever hold people somebody had already 6-- found. That is the difference between a database and a pipeline. 7-- 8-- TWO PROBLEMS MAKE THIS DIFFERENT FROM EVERY OTHER SURFACE HERE, and both shape the schema. 9-- 10-- ONE. A PUBLIC REQUEST HAS NO SESSION, so it has no organization, so row level security correctly denies it 11-- everything. The resolution is a SECURITY DEFINER function that turns an opaque public token into an 12-- organization id, and nothing else. The application role cannot read job_postings directly across tenants; 13-- it asks the function, and the function answers only for a posting that is actually PUBLISHED. That is the 14-- same shape provision_org already uses and it keeps the app role NOBYPASSRLS. 15-- 16-- TWO. AN INBOUND SUBMISSION IS NOT A CANDIDATE YET, and conflating them would be both a compliance mistake 17-- and an abuse hole. Under the OFCCP Internet Applicant rule an applicant is somebody who expressed interest 18-- AND whom the contractor then CONSIDERED for a particular position. Those are two separate events and a 19-- federal contractor has to be able to show both. So a submission lands in job_applications as what the 20-- person actually sent, and a recruiter converting it into a candidate is a distinct, recorded act. It also 21-- means spam never becomes a candidate row. 22 23-- ============================================================================ 24-- 1. job_postings 25-- ============================================================================ 26CREATE TABLE job_postings ( 27 organization_id uuid NOT NULL REFERENCES organizations(id), 28 id uuid NOT NULL DEFAULT gen_random_uuid(), 29 requisition_id uuid NOT NULL, 30 -- The URL key. Opaque and globally unique, never the requisition id, so a public link discloses nothing 31 -- about internal identifiers and cannot be walked to find other postings. 32 public_token text NOT NULL, 33 -- THE PUBLIC COPY IS ITS OWN TEXT, NOT A JOIN TO THE REQUISITION, and this is the load bearing decision 34 -- in this table. A requisition carries bill rate, pay rate, the client name and the internal description. 35 -- Rendering a public page from it would put the agency's margin and its customer's identity on the open 36 -- internet, and the failure mode is silent: nobody notices until a competitor reads it. Publishing is 37 -- therefore an act of WRITING a public description, and a field that is not on this row cannot leak. 38 title text NOT NULL, 39 summary text NOT NULL DEFAULT '', 40 responsibilities text NOT NULL DEFAULT '', 41 qualifications text NOT NULL DEFAULT '', 42 location_text text NOT NULL DEFAULT '', 43 remote_allowed boolean NOT NULL DEFAULT false, 44 employment_type text NOT NULL DEFAULT 'contract', 45 -- The clearance a reader is told about. It is DISPLAY TEXT, deliberately, not a foreign key to the code 46 -- table, because what a posting says publicly ("TS/SCI with polygraph preferred") is a sentence and not 47 -- an enum, and forcing it through the enum would either flatten the meaning or leak the internal code. 48 clearance_text text NOT NULL DEFAULT '', 49 -- Pay may be published, and in several jurisdictions must be. It is a SEPARATE public field rather than 50 -- the requisition's rate, so publishing a range is a deliberate act and never an accident of a join. 51 pay_range_text text NOT NULL DEFAULT '', 52 status text NOT NULL DEFAULT 'draft' 53 CHECK (status IN ('draft', 'published', 'closed')), 54 published_at timestamptz, 55 closes_at timestamptz, 56 created_by uuid, 57 created_at timestamptz NOT NULL DEFAULT now(), 58 updated_at timestamptz NOT NULL DEFAULT now(), 59 PRIMARY KEY (organization_id, id), 60 CONSTRAINT uq_job_postings_token UNIQUE (public_token), 61 CONSTRAINT uq_job_postings_org_id UNIQUE (organization_id, id), 62 CONSTRAINT fk_job_postings_req FOREIGN KEY (organization_id, requisition_id) 63 REFERENCES requisitions (organization_id, id) ON DELETE CASCADE, 64 CONSTRAINT fk_job_postings_creator FOREIGN KEY (organization_id, created_by) 65 REFERENCES users (organization_id, id) ON DELETE SET NULL, 66 CONSTRAINT ck_job_posting_title CHECK (length(btrim(title)) BETWEEN 1 AND 300), 67 CONSTRAINT ck_job_posting_token CHECK (public_token ~ '^[a-z0-9]{16,64}$'), 68 -- A published posting must have been published at a time. Without this a row can claim to be live with 69 -- no record of when it went live, which is the first question in any hiring audit. 70 CONSTRAINT ck_job_posting_published CHECK (status <> 'published' OR published_at IS NOT NULL) 71); 72CREATE INDEX idx_job_postings_org_status ON job_postings (organization_id, status, published_at DESC); 73ALTER TABLE job_postings ENABLE ROW LEVEL SECURITY; 74ALTER TABLE job_postings FORCE ROW LEVEL SECURITY; 75CREATE POLICY job_postings_isolation ON job_postings 76 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 77 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 78-- NO DELETE. A posting is CLOSED. What a job advertisement said, and when, is exactly the record an OFCCP 79-- audit asks for, and a posting that can be deleted is a posting that cannot be produced. 80GRANT SELECT, INSERT, UPDATE ON job_postings TO missionmatch_app; 81 82-- ============================================================================ 83-- 2. job_applications, what a person actually sent 84-- ============================================================================ 85CREATE TABLE job_applications ( 86 organization_id uuid NOT NULL REFERENCES organizations(id), 87 id uuid NOT NULL DEFAULT gen_random_uuid(), 88 posting_id uuid NOT NULL, 89 -- Verbatim, as submitted. These are NOT normalised into candidate fields, because the compliance 90 -- question is what the person said, not what the system made of it. 91 full_name text NOT NULL, 92 email text NOT NULL, 93 phone text NOT NULL DEFAULT '', 94 location_text text NOT NULL DEFAULT '', 95 cover_note text NOT NULL DEFAULT '', 96 -- Self reported and shown as an INDICATOR, never as verified. The real check is the hiring client's FSO 97 -- through DISS at onboarding, which is the rule stated wherever clearance appears in this product. 98 clearance_self_reported text NOT NULL DEFAULT '', 99 -- The resume, if one was attached. Stored the same way every other document is. 100 resume_storage_key text NOT NULL DEFAULT '', 101 resume_file_name text NOT NULL DEFAULT '', 102 resume_media_type text NOT NULL DEFAULT '', 103 resume_sha256 text NOT NULL DEFAULT '', 104 -- THE COMPLIANCE HALF. Under the Internet Applicant rule an applicant is somebody who expressed interest 105 -- AND was then considered for the position. These two columns record the second event separately from 106 -- the first, so a contractor can show which submissions became considerations and which did not, and 107 -- why. 108 status text NOT NULL DEFAULT 'received' 109 CHECK (status IN ('received','considered','converted','rejected','withdrawn','spam')), 110 status_reason text NOT NULL DEFAULT '', 111 reviewed_by uuid, 112 reviewed_at timestamptz, 113 -- Set when a recruiter turns this submission into a real candidate. Nullable, because most submissions 114 -- never become one and pretending otherwise would inflate the bench with people nobody looked at. 115 candidate_id uuid, 116 -- Where the submission came from, so source reporting is possible. Free text because a job board's name 117 -- is not a closed set, bounded so it cannot become a payload. 118 source text NOT NULL DEFAULT 'careers_page', 119 -- The consent the person gave at submission time, recorded as text and a timestamp rather than a bare 120 -- boolean, because "they ticked a box" is not a defensible answer and the wording changes over time. 121 consent_text text NOT NULL DEFAULT '', 122 consented_at timestamptz, 123 submitted_ip inet, 124 created_at timestamptz NOT NULL DEFAULT now(), 125 PRIMARY KEY (organization_id, id), 126 CONSTRAINT fk_job_apps_posting FOREIGN KEY (organization_id, posting_id) 127 REFERENCES job_postings (organization_id, id) ON DELETE CASCADE, 128 CONSTRAINT fk_job_apps_candidate FOREIGN KEY (organization_id, candidate_id) 129 REFERENCES candidates (organization_id, id) ON DELETE SET NULL, 130 CONSTRAINT fk_job_apps_reviewer FOREIGN KEY (organization_id, reviewed_by) 131 REFERENCES users (organization_id, id) ON DELETE SET NULL, 132 CONSTRAINT ck_job_app_name CHECK (length(btrim(full_name)) BETWEEN 1 AND 200), 133 CONSTRAINT ck_job_app_email CHECK (length(email) BETWEEN 6 AND 320 AND position('@' in email) > 1), 134 CONSTRAINT ck_job_app_sizes CHECK ( 135 length(cover_note) <= 5000 AND length(source) <= 120 AND length(consent_text) <= 2000), 136 -- A converted submission must name the candidate it became, or the link between the application and the 137 -- person is a claim nobody can check. 138 CONSTRAINT ck_job_app_converted CHECK (status <> 'converted' OR candidate_id IS NOT NULL) 139); 140CREATE INDEX idx_job_apps_posting ON job_applications (organization_id, posting_id, created_at DESC); 141CREATE INDEX idx_job_apps_status ON job_applications (organization_id, status, created_at DESC); 142-- One person may not flood one posting with the same address. A partial unique on the live statuses, so a 143-- rejected applicant can legitimately apply again to a later posting and a re application to the SAME posting 144-- is refused politely rather than silently duplicated. 145CREATE UNIQUE INDEX uq_job_apps_once ON job_applications (organization_id, posting_id, lower(email)) 146 WHERE status IN ('received', 'considered', 'converted'); 147ALTER TABLE job_applications ENABLE ROW LEVEL SECURITY; 148ALTER TABLE job_applications FORCE ROW LEVEL SECURITY; 149CREATE POLICY job_applications_isolation ON job_applications 150 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 151 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 152-- NO DELETE. An applicant flow log with holes in it is not an applicant flow log. Spam is marked spam. 153GRANT SELECT, INSERT, UPDATE ON job_applications TO missionmatch_app; 154 155-- ============================================================================ 156-- 3. the public seam 157-- ============================================================================ 158-- A public request carries no session, so it has no organization, so RLS denies it everything. These two 159-- SECURITY DEFINER functions are the ONLY way across that line, and they are deliberately narrow: they answer 160-- for PUBLISHED postings only, they return the public copy and nothing from the requisition, and they take a 161-- token or a slug rather than an organization id so a caller can never name a tenant. 162-- 163-- The app role gets EXECUTE. It still cannot SELECT job_postings for another tenant, because the policy above 164-- is unchanged; it can only ask these questions. 165 166-- +goose StatementBegin 167CREATE OR REPLACE FUNCTION public_posting_by_token(p_token text) 168RETURNS TABLE ( 169 organization_id uuid, posting_id uuid, title text, summary text, responsibilities text, 170 qualifications text, location_text text, remote_allowed boolean, employment_type text, 171 clearance_text text, pay_range_text text, published_at timestamptz, closes_at timestamptz, 172 organization_name text 173) AS $$ 174 SELECT p.organization_id, p.id, p.title, p.summary, p.responsibilities, p.qualifications, 175 p.location_text, p.remote_allowed, p.employment_type, p.clearance_text, p.pay_range_text, 176 p.published_at, p.closes_at, o.name 177 FROM job_postings p 178 JOIN organizations o ON o.id = p.organization_id 179 WHERE p.public_token = p_token 180 AND p.status = 'published' 181 -- A closed date in the past means the posting is gone from the public internet even if somebody forgot 182 -- to close it by hand, which is what stops a stale advertisement collecting applications for a role that 183 -- was filled in March. 184 AND (p.closes_at IS NULL OR p.closes_at > now()); 185$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, pg_temp; 186-- +goose StatementEnd 187REVOKE ALL ON FUNCTION public_posting_by_token(text) FROM PUBLIC; 188GRANT EXECUTE ON FUNCTION public_posting_by_token(text) TO missionmatch_app; 189 190-- +goose StatementBegin 191-- The careers page: every live posting for one organization, found by its slug. 192CREATE OR REPLACE FUNCTION public_postings_by_slug(p_slug text) 193RETURNS TABLE ( 194 organization_id uuid, posting_id uuid, public_token text, title text, summary text, 195 location_text text, remote_allowed boolean, employment_type text, clearance_text text, 196 published_at timestamptz, organization_name text 197) AS $$ 198 SELECT p.organization_id, p.id, p.public_token, p.title, p.summary, p.location_text, 199 p.remote_allowed, p.employment_type, p.clearance_text, p.published_at, o.name 200 FROM job_postings p 201 JOIN organizations o ON o.id = p.organization_id 202 WHERE lower(o.slug) = lower(p_slug) 203 AND o.status = 'active' 204 AND p.status = 'published' 205 AND (p.closes_at IS NULL OR p.closes_at > now()) 206 ORDER BY p.published_at DESC 207 LIMIT 200; 208$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, pg_temp; 209-- +goose StatementEnd 210REVOKE ALL ON FUNCTION public_postings_by_slug(text) FROM PUBLIC; 211GRANT EXECUTE ON FUNCTION public_postings_by_slug(text) TO missionmatch_app; 212 213SELECT mm_mirror_staging_role_privileges(); 214 215-- +goose Down 216DROP FUNCTION IF EXISTS public_postings_by_slug(text); 217DROP FUNCTION IF EXISTS public_posting_by_token(text); 218DROP TABLE IF EXISTS job_applications; 219DROP TABLE IF EXISTS job_postings; 220