Janady13/missionmatch / db/migrations/0095_interview_scheduling.sql

1-- +goose Up 2-- INTERVIEW SCHEDULING. Offering times, a candidate picking one, and a real calendar invite. 3-- 4-- interviews has existed since migration 0048 and it RECORDS an interview: a time, a kind, an outcome. It 5-- has no way to ARRANGE one. Arranging is the part that costs a recruiter an afternoon, because it is four 6-- emails and a phone call per candidate and it is the single most rescheduled event in the process. 7-- 8-- THE CALENDAR INVITE IS AN iCALENDAR ATTACHMENT, NOT A CALENDAR INTEGRATION, and that is a deliberate choice 9-- rather than a shortcut. Integrating with Google Calendar and Microsoft Graph means two vendor specific 10-- OAuth applications, two consent screens, two sets of credentials to hold, and a permanent dependency on 11-- two APIs, all of which cuts against the both clouds no lock in mandate. An iCalendar REQUEST attachment is 12-- the interoperable standard every one of those calendars already accepts, it needs no credentials at all, 13-- and it works for a candidate whose calendar is none of the above. 14-- 15-- AN OFFER IS NOT AN INTERVIEW. Offering three times creates an offer with three slots; the interviews row 16-- is created only when somebody picks one. Creating the interview up front and moving it would mean a 17-- pipeline full of interviews that never happened. 18 19-- ============================================================================ 20-- 1. interview_offers 21-- ============================================================================ 22CREATE TABLE interview_offers ( 23 organization_id uuid NOT NULL REFERENCES organizations(id), 24 id uuid NOT NULL DEFAULT gen_random_uuid(), 25 application_id uuid NOT NULL, 26 -- The public booking key. Opaque and globally unique, never the application id, so a link discloses 27 -- nothing internal and cannot be walked to find other candidates' interviews. 28 public_token text NOT NULL, 29 kind text NOT NULL DEFAULT 'client' 30 CHECK (kind IN ('phone_screen','technical','client','panel','final')), 31 round int NOT NULL DEFAULT 1 CHECK (round BETWEEN 1 AND 20), 32 duration_mins int NOT NULL DEFAULT 45 CHECK (duration_mins BETWEEN 5 AND 480), 33 -- What the candidate is told. Free text because "the third floor reception, ask for Dana" and a video 34 -- link are the same field to the person reading it. 35 location_text text NOT NULL DEFAULT '', 36 interviewer text NOT NULL DEFAULT '', 37 message text NOT NULL DEFAULT '', 38 status text NOT NULL DEFAULT 'open' 39 CHECK (status IN ('open','booked','expired','withdrawn')), 40 expires_at timestamptz NOT NULL, 41 booked_slot_id uuid, 42 interview_id uuid, 43 created_by uuid, 44 created_at timestamptz NOT NULL DEFAULT now(), 45 updated_at timestamptz NOT NULL DEFAULT now(), 46 PRIMARY KEY (organization_id, id), 47 CONSTRAINT uq_interview_offers_token UNIQUE (public_token), 48 CONSTRAINT uq_interview_offers_org_id UNIQUE (organization_id, id), 49 CONSTRAINT fk_interview_offers_app FOREIGN KEY (organization_id, application_id) 50 REFERENCES applications (organization_id, id) ON DELETE CASCADE, 51 CONSTRAINT fk_interview_offers_creator FOREIGN KEY (organization_id, created_by) 52 REFERENCES users (organization_id, id) ON DELETE SET NULL, 53 CONSTRAINT ck_interview_offer_token CHECK (public_token ~ '^[a-z0-9]{16,64}$'), 54 CONSTRAINT ck_interview_offer_sizes CHECK ( 55 length(location_text) <= 500 AND length(interviewer) <= 200 AND length(message) <= 2000), 56 -- A booked offer must name what was booked, or the link between the offer and the interview is a claim 57 -- nobody can check. 58 CONSTRAINT ck_interview_offer_booked CHECK ( 59 status <> 'booked' OR (booked_slot_id IS NOT NULL AND interview_id IS NOT NULL)) 60); 61CREATE INDEX idx_interview_offers_app ON interview_offers (organization_id, application_id, created_at DESC); 62CREATE INDEX idx_interview_offers_open ON interview_offers (organization_id, status, expires_at) 63 WHERE status = 'open'; 64ALTER TABLE interview_offers ENABLE ROW LEVEL SECURITY; 65ALTER TABLE interview_offers FORCE ROW LEVEL SECURITY; 66CREATE POLICY interview_offers_isolation ON interview_offers 67 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 68 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 69-- NO DELETE. What times a candidate was offered and when is part of the record of how they were treated, 70-- which under an Automated Employment Decision Tool is exactly the kind of thing an audit asks about. 71GRANT SELECT, INSERT, UPDATE ON interview_offers TO missionmatch_app; 72 73-- ============================================================================ 74-- 2. interview_offer_slots 75-- ============================================================================ 76CREATE TABLE interview_offer_slots ( 77 organization_id uuid NOT NULL REFERENCES organizations(id), 78 id uuid NOT NULL DEFAULT gen_random_uuid(), 79 offer_id uuid NOT NULL, 80 starts_at timestamptz NOT NULL, 81 ends_at timestamptz NOT NULL, 82 -- A slot a candidate can no longer take, because somebody else booked the interviewer or the recruiter 83 -- withdrew it. Kept rather than deleted so the offer shows what was actually available at the time. 84 available boolean NOT NULL DEFAULT true, 85 PRIMARY KEY (organization_id, id), 86 CONSTRAINT uq_offer_slots_org_id UNIQUE (organization_id, id), 87 CONSTRAINT fk_offer_slots_offer FOREIGN KEY (organization_id, offer_id) 88 REFERENCES interview_offers (organization_id, id) ON DELETE CASCADE, 89 CONSTRAINT ck_offer_slot_window CHECK (ends_at > starts_at) 90); 91CREATE INDEX idx_offer_slots_offer ON interview_offer_slots (organization_id, offer_id, starts_at); 92ALTER TABLE interview_offer_slots ENABLE ROW LEVEL SECURITY; 93ALTER TABLE interview_offer_slots FORCE ROW LEVEL SECURITY; 94CREATE POLICY interview_offer_slots_isolation ON interview_offer_slots 95 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 96 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 97GRANT SELECT, INSERT, UPDATE ON interview_offer_slots TO missionmatch_app; 98 99ALTER TABLE interview_offers ADD CONSTRAINT fk_interview_offers_slot 100 FOREIGN KEY (organization_id, booked_slot_id) 101 REFERENCES interview_offer_slots (organization_id, id) ON DELETE SET NULL; 102 103-- ============================================================================ 104-- 3. the public seam 105-- ============================================================================ 106-- The same shape as the careers surface: a public request has no session, so a SECURITY DEFINER function is 107-- the only way across the line, and it answers only for an OPEN offer that has not expired. It returns what 108-- a candidate needs to choose a time and nothing about the pipeline they are in. 109-- +goose StatementBegin 110CREATE OR REPLACE FUNCTION public_interview_offer(p_token text) 111RETURNS TABLE ( 112 organization_id uuid, offer_id uuid, kind text, duration_mins int, location_text text, 113 interviewer text, message text, expires_at timestamptz, candidate_name text, 114 requisition_title text, organization_name text 115) AS $$ 116 SELECT o.organization_id, o.id, o.kind, o.duration_mins, o.location_text, o.interviewer, o.message, 117 o.expires_at, c.full_name, r.title, org.name 118 FROM interview_offers o 119 JOIN applications a ON a.id = o.application_id 120 JOIN candidates c ON c.id = a.candidate_id 121 JOIN requisitions r ON r.id = a.requisition_id 122 JOIN organizations org ON org.id = o.organization_id 123 WHERE o.public_token = p_token 124 AND o.status = 'open' 125 AND o.expires_at > now(); 126$$ LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public, pg_temp; 127-- +goose StatementEnd 128REVOKE ALL ON FUNCTION public_interview_offer(text) FROM PUBLIC; 129GRANT EXECUTE ON FUNCTION public_interview_offer(text) TO missionmatch_app; 130 131SELECT mm_mirror_staging_role_privileges(); 132 133-- +goose Down 134DROP FUNCTION IF EXISTS public_interview_offer(text); 135ALTER TABLE interview_offers DROP CONSTRAINT IF EXISTS fk_interview_offers_slot; 136DROP TABLE IF EXISTS interview_offer_slots; 137DROP TABLE IF EXISTS interview_offers; 138