Janady13/missionmatch / db/migrations/0048_recruiting_pipeline.sql

1-- +goose Up 2-- Phase 10, part 2 of 3. The pipeline: applications, their stage history, and the three events that 3-- close the funnel. 4-- 5-- The central design decision here is that STAGE HISTORY IS THE SOURCE OF TRUTH and the current stage is 6-- a cache maintained by trigger. Every duration and conversion metric in this domain has a definitional 7-- fork in it: time to fill can be measured to offer acceptance or to start date, fill rate can be 8-- measured in period or by cohort, source can be attributed first touch or last touch. Every one of 9-- those is recoverable from a timestamped event stream and unrecoverable from a rollup column. So the 10-- events are append only, and applications.stage is derived from them and cannot be written directly. 11 12-- --------------------------------------------------------------------------- 13-- rm_stage_ord: the canonical stage ladder as an ordinal, so set based SQL can compare progress without 14-- restating the vocabulary. IMMUTABLE and PARALLEL SAFE, pure, reads no table, so it is RLS neutral and 15-- usable in an index. Terminal dispositions return negative values: they are outcomes, not rungs, and 16-- must never sort as though they were further along than an offer. 17-- --------------------------------------------------------------------------- 18-- +goose StatementBegin 19CREATE OR REPLACE FUNCTION rm_stage_ord(stage text) RETURNS int AS $$ 20BEGIN 21 RETURN CASE stage 22 WHEN 'sourced' THEN 1 23 WHEN 'screened' THEN 2 24 WHEN 'submitted' THEN 3 25 WHEN 'interviewing' THEN 4 26 WHEN 'offered' THEN 5 27 WHEN 'placed' THEN 6 28 WHEN 'rejected' THEN -1 29 WHEN 'withdrawn' THEN -2 30 ELSE 0 31 END; 32END; 33$$ LANGUAGE plpgsql IMMUTABLE PARALLEL SAFE; 34-- +goose StatementEnd 35GRANT EXECUTE ON FUNCTION rm_stage_ord(text) TO missionmatch_app; 36 37-- --------------------------------------------------------------------------- 38-- applications: one candidate considered for one requisition. The junction the product has never had. 39-- A candidate may hold many applications and a requisition many candidates, so the pair is the key. 40-- 41-- stage and stage_changed_at are maintained ONLY by the trigger below. The application role is granted 42-- UPDATE so it can edit the fields it owns; correctness of stage does not rely on that restraint, 43-- because any direct write is overwritten by the next event and every event is recorded. 44-- --------------------------------------------------------------------------- 45CREATE TABLE applications ( 46 organization_id uuid NOT NULL REFERENCES organizations(id), 47 id uuid NOT NULL DEFAULT gen_random_uuid(), 48 requisition_id uuid NOT NULL, 49 candidate_id uuid NOT NULL, 50 stage text NOT NULL DEFAULT 'sourced' 51 CHECK (stage IN ('sourced','screened','submitted','interviewing','offered','placed','rejected','withdrawn')), 52 stage_changed_at timestamptz NOT NULL DEFAULT now(), 53 -- Why the candidate left the process. Required exactly when the stage is terminal, so a rejection 54 -- can never be recorded without a reason and an active application can never carry a stale one. 55 disposition_reason text, 56 -- Attribution. first_touch is set once at creation and never rewritten; last_touch is the channel 57 -- that produced THIS engagement. Storing one mutable column is the flaw behind most unusable source 58 -- reporting, because a later touch silently erases how the relationship actually started. 59 source_first_touch text NOT NULL DEFAULT '', 60 source_last_touch text NOT NULL DEFAULT '', 61 owner_user_id uuid, 62 created_by uuid, 63 created_at timestamptz NOT NULL DEFAULT now(), 64 updated_at timestamptz NOT NULL DEFAULT now(), 65 PRIMARY KEY (organization_id, id), 66 UNIQUE (organization_id, requisition_id, candidate_id), 67 FOREIGN KEY (organization_id, requisition_id) REFERENCES requisitions (organization_id, id) ON DELETE CASCADE, 68 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE, 69 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL, 70 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL, 71 CONSTRAINT applications_disposition_chk CHECK ( 72 (stage IN ('rejected','withdrawn') AND disposition_reason IS NOT NULL AND length(btrim(disposition_reason)) > 0) 73 OR (stage NOT IN ('rejected','withdrawn') AND disposition_reason IS NULL)) 74); 75CREATE INDEX idx_applications_req ON applications (organization_id, requisition_id, stage); 76CREATE INDEX idx_applications_candidate ON applications (organization_id, candidate_id); 77CREATE INDEX idx_applications_owner ON applications (organization_id, owner_user_id); 78CREATE INDEX idx_applications_active ON applications (organization_id, stage_changed_at DESC) 79 WHERE stage NOT IN ('placed','rejected','withdrawn'); 80 81ALTER TABLE applications ENABLE ROW LEVEL SECURITY; 82ALTER TABLE applications FORCE ROW LEVEL SECURITY; 83CREATE POLICY applications_isolation ON applications 84 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 85 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 86GRANT SELECT, INSERT, UPDATE, DELETE ON applications TO missionmatch_app; 87 88-- --------------------------------------------------------------------------- 89-- application_stage_events: append only transition history. This table is the reason every metric in 90-- phase 17 is computable and auditable. UPDATE, DELETE and TRUNCATE are refused by trigger and revoked 91-- from the application role, exactly as audit_logs is protected. 92-- 93-- occurred_at is separate from created_at so a backdated import records when the transition actually 94-- happened while still recording when it was entered. 95-- --------------------------------------------------------------------------- 96CREATE TABLE application_stage_events ( 97 organization_id uuid NOT NULL REFERENCES organizations(id), 98 id bigint GENERATED ALWAYS AS IDENTITY, 99 application_id uuid NOT NULL, 100 from_stage text, 101 to_stage text NOT NULL 102 CHECK (to_stage IN ('sourced','screened','submitted','interviewing','offered','placed','rejected','withdrawn')), 103 reason text NOT NULL DEFAULT '', 104 actor_user_id uuid, 105 occurred_at timestamptz NOT NULL DEFAULT now(), 106 created_at timestamptz NOT NULL DEFAULT now(), 107 PRIMARY KEY (organization_id, id), 108 FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE, 109 FOREIGN KEY (organization_id, actor_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL, 110 CONSTRAINT application_stage_events_no_self_chk CHECK (from_stage IS NULL OR from_stage <> to_stage) 111); 112CREATE INDEX idx_ase_application ON application_stage_events (organization_id, application_id, occurred_at); 113CREATE INDEX idx_ase_to_stage ON application_stage_events (organization_id, to_stage, occurred_at DESC); 114 115ALTER TABLE application_stage_events ENABLE ROW LEVEL SECURITY; 116ALTER TABLE application_stage_events FORCE ROW LEVEL SECURITY; 117CREATE POLICY ase_isolation ON application_stage_events 118 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 119 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 120 121CREATE TRIGGER application_stage_events_append_only 122 BEFORE UPDATE OR DELETE ON application_stage_events 123 FOR EACH ROW EXECUTE FUNCTION forbid_mutation(); 124CREATE TRIGGER application_stage_events_no_truncate 125 BEFORE TRUNCATE ON application_stage_events 126 FOR EACH STATEMENT EXECUTE FUNCTION forbid_mutation(); 127 128GRANT SELECT, INSERT ON application_stage_events TO missionmatch_app; 129REVOKE UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON application_stage_events FROM missionmatch_app; 130 131-- --------------------------------------------------------------------------- 132-- The stage cache. An inserted event advances applications.stage, but only when the event is at least as 133-- recent as the stage already recorded. Without that guard a backdated import would drag a live 134-- application backwards to a stage it left months ago. 135-- --------------------------------------------------------------------------- 136-- +goose StatementBegin 137CREATE OR REPLACE FUNCTION rm_apply_stage_event() RETURNS trigger AS $$ 138BEGIN 139 UPDATE applications 140 SET stage = NEW.to_stage, 141 stage_changed_at = NEW.occurred_at, 142 disposition_reason = CASE 143 WHEN NEW.to_stage IN ('rejected','withdrawn') 144 THEN NULLIF(btrim(NEW.reason), '') 145 ELSE NULL 146 END, 147 updated_at = now() 148 WHERE organization_id = NEW.organization_id 149 AND id = NEW.application_id 150 AND NEW.occurred_at >= stage_changed_at; 151 RETURN NULL; 152END; 153$$ LANGUAGE plpgsql; 154-- +goose StatementEnd 155CREATE TRIGGER application_stage_events_sync 156 AFTER INSERT ON application_stage_events 157 FOR EACH ROW EXECUTE FUNCTION rm_apply_stage_event(); 158 159-- --------------------------------------------------------------------------- 160-- interviews, offers and placements all hang off an APPLICATION rather than a candidate, so every one of 161-- them is anchored to a specific job. A candidate interviewing for two roles has two interviews, and 162-- neither metric can borrow the other's timeline. 163-- --------------------------------------------------------------------------- 164CREATE TABLE interviews ( 165 organization_id uuid NOT NULL REFERENCES organizations(id), 166 id uuid NOT NULL DEFAULT gen_random_uuid(), 167 application_id uuid NOT NULL, 168 round int NOT NULL DEFAULT 1 CHECK (round BETWEEN 1 AND 20), 169 kind text NOT NULL DEFAULT 'client' 170 CHECK (kind IN ('phone_screen','technical','client','panel','final')), 171 scheduled_at timestamptz, 172 completed_at timestamptz, 173 outcome text NOT NULL DEFAULT 'scheduled' 174 CHECK (outcome IN ('scheduled','passed','failed','no_show','cancelled','rescheduled')), 175 interviewer text NOT NULL DEFAULT '', 176 notes text NOT NULL DEFAULT '', 177 created_by uuid, 178 created_at timestamptz NOT NULL DEFAULT now(), 179 updated_at timestamptz NOT NULL DEFAULT now(), 180 PRIMARY KEY (organization_id, id), 181 UNIQUE (organization_id, application_id, round), 182 FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE, 183 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL, 184 -- A concluded interview must say when it concluded, and cannot conclude before it was scheduled. 185 CONSTRAINT interviews_completed_chk CHECK ( 186 (outcome IN ('passed','failed','no_show') AND completed_at IS NOT NULL) 187 OR outcome NOT IN ('passed','failed','no_show')), 188 CONSTRAINT interviews_order_chk CHECK ( 189 completed_at IS NULL OR scheduled_at IS NULL OR completed_at >= scheduled_at) 190); 191CREATE INDEX idx_interviews_application ON interviews (organization_id, application_id, round); 192CREATE INDEX idx_interviews_upcoming ON interviews (organization_id, scheduled_at) 193 WHERE outcome = 'scheduled'; 194 195ALTER TABLE interviews ENABLE ROW LEVEL SECURITY; 196ALTER TABLE interviews FORCE ROW LEVEL SECURITY; 197CREATE POLICY interviews_isolation ON interviews 198 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 199 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 200GRANT SELECT, INSERT, UPDATE, DELETE ON interviews TO missionmatch_app; 201 202-- offers. Contract and permanent carry different money and must never be averaged together: a direct 203-- hire fee is the whole gross profit while a contract spread is an hourly delta, so a blended margin 204-- percentage moves with mix alone and means nothing. 205CREATE TABLE offers ( 206 organization_id uuid NOT NULL REFERENCES organizations(id), 207 id uuid NOT NULL DEFAULT gen_random_uuid(), 208 application_id uuid NOT NULL, 209 status text NOT NULL DEFAULT 'extended' 210 CHECK (status IN ('extended','accepted','declined','rescinded','expired')), 211 employment_type text NOT NULL DEFAULT 'contract' 212 CHECK (employment_type IN ('contract','contract_to_hire','direct_hire')), 213 bill_rate numeric(12,4) CHECK (bill_rate IS NULL OR bill_rate >= 0), 214 pay_rate numeric(12,4) CHECK (pay_rate IS NULL OR pay_rate >= 0), 215 salary numeric(14,2) CHECK (salary IS NULL OR salary >= 0), 216 fee_pct numeric(6,4) CHECK (fee_pct IS NULL OR (fee_pct >= 0 AND fee_pct <= 1)), 217 extended_at timestamptz NOT NULL DEFAULT now(), 218 responded_at timestamptz, 219 expires_at timestamptz, 220 decline_reason text NOT NULL DEFAULT '', 221 created_by uuid, 222 created_at timestamptz NOT NULL DEFAULT now(), 223 updated_at timestamptz NOT NULL DEFAULT now(), 224 PRIMARY KEY (organization_id, id), 225 FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE, 226 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL, 227 CONSTRAINT offers_responded_chk CHECK ( 228 (status IN ('accepted','declined','rescinded') AND responded_at IS NOT NULL) 229 OR status NOT IN ('accepted','declined','rescinded')), 230 CONSTRAINT offers_order_chk CHECK (responded_at IS NULL OR responded_at >= extended_at) 231); 232-- At most one live offer per application. Two simultaneous outstanding offers for the same seat is not a 233-- state the business can be in, and allowing it would double count the offer stage. 234CREATE UNIQUE INDEX uq_offers_one_live ON offers (organization_id, application_id) 235 WHERE status = 'extended'; 236CREATE INDEX idx_offers_application ON offers (organization_id, application_id, extended_at DESC); 237CREATE INDEX idx_offers_open ON offers (organization_id, expires_at) WHERE status = 'extended'; 238 239ALTER TABLE offers ENABLE ROW LEVEL SECURITY; 240ALTER TABLE offers FORCE ROW LEVEL SECURITY; 241CREATE POLICY offers_isolation ON offers 242 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 243 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 244GRANT SELECT, INSERT, UPDATE, DELETE ON offers TO missionmatch_app; 245 246-- placements. start_date is the second time to fill clock and the revenue start. fell_off is separated 247-- from terminated because a fall off inside the guarantee period is a commercial event with a clawback, 248-- while a later termination is not. 249CREATE TABLE placements ( 250 organization_id uuid NOT NULL REFERENCES organizations(id), 251 id uuid NOT NULL DEFAULT gen_random_uuid(), 252 application_id uuid NOT NULL, 253 offer_id uuid, 254 status text NOT NULL DEFAULT 'pending_start' 255 CHECK (status IN ('pending_start','active','completed','terminated','fell_off')), 256 employment_type text NOT NULL DEFAULT 'contract' 257 CHECK (employment_type IN ('contract','contract_to_hire','direct_hire')), 258 start_date date NOT NULL, 259 end_date date, 260 bill_rate numeric(12,4) CHECK (bill_rate IS NULL OR bill_rate >= 0), 261 pay_rate numeric(12,4) CHECK (pay_rate IS NULL OR pay_rate >= 0), 262 salary numeric(14,2) CHECK (salary IS NULL OR salary >= 0), 263 fee_amount numeric(14,2) CHECK (fee_amount IS NULL OR fee_amount >= 0), 264 guarantee_days int NOT NULL DEFAULT 90 CHECK (guarantee_days >= 0), 265 created_by uuid, 266 created_at timestamptz NOT NULL DEFAULT now(), 267 updated_at timestamptz NOT NULL DEFAULT now(), 268 PRIMARY KEY (organization_id, id), 269 FOREIGN KEY (organization_id, application_id) REFERENCES applications (organization_id, id) ON DELETE CASCADE, 270 FOREIGN KEY (organization_id, offer_id) REFERENCES offers (organization_id, id) ON DELETE SET NULL, 271 FOREIGN KEY (organization_id, created_by) REFERENCES users (organization_id, id) ON DELETE SET NULL, 272 CONSTRAINT placements_dates_chk CHECK (end_date IS NULL OR end_date >= start_date), 273 CONSTRAINT placements_ended_chk CHECK ( 274 (status IN ('completed','terminated','fell_off') AND end_date IS NOT NULL) 275 OR status NOT IN ('completed','terminated','fell_off')), 276 -- A contract placement is priced by rate, a direct hire by salary and fee. Requiring the right shape 277 -- stops a margin calculation silently reading a NULL as zero. 278 CONSTRAINT placements_pricing_chk CHECK ( 279 (employment_type = 'direct_hire' AND (salary IS NOT NULL OR fee_amount IS NOT NULL)) 280 OR (employment_type <> 'direct_hire' AND bill_rate IS NOT NULL AND pay_rate IS NOT NULL)), 281 -- Selling below cost is a data entry error, not a business decision worth recording silently. 282 CONSTRAINT placements_margin_chk CHECK ( 283 bill_rate IS NULL OR pay_rate IS NULL OR bill_rate >= pay_rate) 284); 285-- One live placement per application. A seat cannot be filled twice by the same person on the same order. 286CREATE UNIQUE INDEX uq_placements_one_live ON placements (organization_id, application_id) 287 WHERE status IN ('pending_start','active'); 288CREATE INDEX idx_placements_application ON placements (organization_id, application_id); 289CREATE INDEX idx_placements_active ON placements (organization_id, start_date DESC) 290 WHERE status IN ('pending_start','active'); 291 292ALTER TABLE placements ENABLE ROW LEVEL SECURITY; 293ALTER TABLE placements FORCE ROW LEVEL SECURITY; 294CREATE POLICY placements_isolation ON placements 295 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 296 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 297GRANT SELECT, INSERT, UPDATE, DELETE ON placements TO missionmatch_app; 298 299-- +goose Down 300DROP TABLE IF EXISTS placements; 301DROP TABLE IF EXISTS offers; 302DROP TABLE IF EXISTS interviews; 303DROP TRIGGER IF EXISTS application_stage_events_sync ON application_stage_events; 304DROP FUNCTION IF EXISTS rm_apply_stage_event(); 305DROP TABLE IF EXISTS application_stage_events; 306DROP TABLE IF EXISTS applications; 307DROP FUNCTION IF EXISTS rm_stage_ord(text); 308