Janady13/missionmatch / db/migrations/0089_candidate_documents_and_messaging.sql

1-- +goose Up 2-- THE TWO PREREQUISITES A RECRUITING PRODUCT CANNOT SHIP WITHOUT, closed before any screen is drawn. 3-- 4-- A route inventory against the established products in this market found the codebase strong on the 5-- recruiting spine, clients through requisitions through applications through offers through placements, 6-- with activity logging, tasks, dispositions, alerts and an audit trail. Two holes made it unusable in 7-- practice, and both change what a screen has to show, which is why they land before the frontend rather 8-- than after it. 9-- 10-- 1. A RESUME COULD NOT BE ATTACHED TO A CANDIDATE. opportunity_documents existed and nothing equivalent 11-- did for the record type the whole product is about. 12-- 2. NOTHING COULD BE SENT TO A CANDIDATE. The only two send calls in the codebase were the account invite 13-- and the settings test message. A recruiter's day is correspondence, and there was no way to write any. 14-- 15-- SENDING REUSES THE ORGANIZATION'S OWN MAIL SERVER from migration 0087, so a candidate hears from 16-- talent@theiragency.com and not from a domain they were trained to distrust, and it passes their own SPF 17-- and DKIM because it genuinely is them. 18 19-- ============================================================================ 20-- 1. candidate_documents 21-- ============================================================================ 22-- Deliberately the same shape as opportunity_documents: bytes in object storage under storage_key, this row 23-- is metadata and lifecycle, sha256 gives duplicate detection, and a superseded document keeps is_current 24-- false rather than being deleted so the history of what was sent to a client survives. 25-- 26-- IT IS NOT MERELY "THE RESUME". A cleared candidate's file is a package: the resume the agency formatted, 27-- the original the candidate sent, a right to represent, a certification, a reference. document_type is the 28-- closed vocabulary for that, and it exists because "attachment" as the only answer is how a compliance 29-- request six months later becomes an afternoon of opening files to see what they are. 30CREATE TABLE candidate_documents ( 31 organization_id uuid NOT NULL REFERENCES organizations(id), 32 id uuid NOT NULL DEFAULT gen_random_uuid(), 33 candidate_id uuid NOT NULL, 34 -- clearance_evidence IS A LABEL, NEVER A VERIFICATION. Storing a document a candidate supplied does not 35 -- make a clearance verified: the real check remains the hiring client's FSO through DISS at onboarding, 36 -- and the product says so wherever clearance appears. The value exists so the file can be FOUND, never 37 -- so it can be treated as proof. 38 document_type text NOT NULL DEFAULT 'resume' 39 CHECK (document_type IN ('resume','resume_original','resume_formatted', 40 'cover_letter','certification','clearance_evidence','right_to_represent', 41 'reference','work_sample','onboarding','other')), 42 file_name text NOT NULL DEFAULT '', 43 media_type text NOT NULL DEFAULT '', 44 storage_key text NOT NULL DEFAULT '', 45 size_bytes bigint NOT NULL DEFAULT 0, 46 sha256 text NOT NULL DEFAULT '', 47 uploaded_by_user_id uuid, 48 extraction_status text NOT NULL DEFAULT 'pending' 49 CHECK (extraction_status IN ('pending','running','done','failed','skipped')), 50 extraction_error_code text NOT NULL DEFAULT '', 51 supersedes_document_id uuid, 52 is_current boolean NOT NULL DEFAULT true, 53 created_at timestamptz NOT NULL DEFAULT now(), 54 PRIMARY KEY (id), 55 CONSTRAINT uq_cand_docs_org_id UNIQUE (organization_id, id), 56 FOREIGN KEY (organization_id, candidate_id) REFERENCES candidates (organization_id, id) ON DELETE CASCADE, 57 FOREIGN KEY (organization_id, uploaded_by_user_id) REFERENCES users (organization_id, id) 58 ON DELETE SET NULL (uploaded_by_user_id), 59 FOREIGN KEY (organization_id, supersedes_document_id) REFERENCES candidate_documents (organization_id, id) 60 ON DELETE SET NULL (supersedes_document_id) 61); 62CREATE INDEX idx_cand_docs_org_cand ON candidate_documents (organization_id, candidate_id, is_current); 63-- The same bytes are not accepted twice as a current document for one candidate. Recruiters re upload the 64-- same resume constantly, and without this a candidate's file becomes eleven copies of one document. 65CREATE UNIQUE INDEX uq_cand_docs_sha ON candidate_documents (organization_id, candidate_id, sha256) 66 WHERE is_current AND sha256 <> ''; 67ALTER TABLE candidate_documents ENABLE ROW LEVEL SECURITY; 68ALTER TABLE candidate_documents FORCE ROW LEVEL SECURITY; 69CREATE POLICY candidate_documents_isolation ON candidate_documents 70 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 71 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 72GRANT SELECT, INSERT, UPDATE ON candidate_documents TO missionmatch_app; 73 74-- ============================================================================ 75-- 2. message_templates 76-- ============================================================================ 77-- The reason a recruiter sends forty messages a day and each one still reads like a person wrote it. 78-- Placeholders are resolved SERVER SIDE from the record, so a template cannot address somebody by the wrong 79-- name, which is the single most expensive mistake in outreach. 80CREATE TABLE message_templates ( 81 organization_id uuid NOT NULL REFERENCES organizations(id), 82 id uuid NOT NULL DEFAULT gen_random_uuid(), 83 name text NOT NULL, 84 -- Which kind of record this template is written for, so the interface offers the right ones and the 85 -- placeholder set can be validated against what that record actually has. 86 audience text NOT NULL DEFAULT 'candidate' 87 CHECK (audience IN ('candidate','contact')), 88 subject text NOT NULL DEFAULT '', 89 body text NOT NULL DEFAULT '', 90 status text NOT NULL DEFAULT 'active' CHECK (status IN ('active','archived')), 91 created_by uuid, 92 created_at timestamptz NOT NULL DEFAULT now(), 93 updated_at timestamptz NOT NULL DEFAULT now(), 94 PRIMARY KEY (organization_id, id), 95 CONSTRAINT fk_msg_templates_creator FOREIGN KEY (organization_id, created_by) 96 REFERENCES users (organization_id, id) ON DELETE SET NULL, 97 CONSTRAINT ck_msg_template_name CHECK (length(btrim(name)) BETWEEN 1 AND 120), 98 CONSTRAINT ck_msg_template_size CHECK (length(subject) <= 300 AND length(body) <= 20000) 99); 100CREATE UNIQUE INDEX uq_msg_templates_name ON message_templates (organization_id, lower(btrim(name))) 101 WHERE status = 'active'; 102ALTER TABLE message_templates ENABLE ROW LEVEL SECURITY; 103ALTER TABLE message_templates FORCE ROW LEVEL SECURITY; 104CREATE POLICY message_templates_isolation ON message_templates 105 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 106 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 107GRANT SELECT, INSERT, UPDATE ON message_templates TO missionmatch_app; 108 109-- ============================================================================ 110-- 3. messages, the record of what was actually sent 111-- ============================================================================ 112-- APPEND ONLY IN PRACTICE: no DELETE grant. What an agency told a candidate is a fact about the engagement, 113-- and under an Automated Employment Decision Tool it is part of the record of how a person was treated. A 114-- message that can be deleted is a message that cannot be produced in an audit. 115-- 116-- THE BODY IS RETAINED. That is a deliberate choice with a cost: it is candidate correspondence, so it is 117-- sensitive, it sits inside forced row level security, and it is covered by the same retention rule as every 118-- other decision record. The alternative, storing only that a message was sent, makes the trail useless 119-- exactly when somebody needs it. 120CREATE TABLE messages ( 121 organization_id uuid NOT NULL REFERENCES organizations(id), 122 id uuid NOT NULL DEFAULT gen_random_uuid(), 123 -- Who it went to. Exactly one of candidate_id or contact_id is set, enforced below, because a message is 124 -- to a person and a message addressed to two different kinds of person at once is a bug. 125 candidate_id uuid, 126 contact_id uuid, 127 -- Optional context: the requisition or application this correspondence belongs to, so a candidate's 128 -- history can be read per role rather than as one undifferentiated stream. 129 requisition_id uuid, 130 application_id uuid, 131 channel text NOT NULL DEFAULT 'email' CHECK (channel IN ('email')), 132 direction text NOT NULL DEFAULT 'outbound' CHECK (direction IN ('outbound')), 133 to_address text NOT NULL, 134 from_address text NOT NULL DEFAULT '', 135 subject text NOT NULL DEFAULT '', 136 body text NOT NULL DEFAULT '', 137 template_id uuid, 138 sent_by_user_id uuid, 139 status text NOT NULL DEFAULT 'queued' 140 CHECK (status IN ('queued','sent','failed')), 141 -- The transport's own message when a send fails, scrubbed of credentials before it is written, so a 142 -- recruiter can see that a mailbox bounced without reading a server log. 143 error_text text NOT NULL DEFAULT '', 144 created_at timestamptz NOT NULL DEFAULT now(), 145 sent_at timestamptz, 146 PRIMARY KEY (organization_id, id), 147 CONSTRAINT fk_messages_candidate FOREIGN KEY (organization_id, candidate_id) 148 REFERENCES candidates (organization_id, id) ON DELETE CASCADE, 149 CONSTRAINT fk_messages_contact FOREIGN KEY (organization_id, contact_id) 150 REFERENCES client_contacts (organization_id, id) ON DELETE CASCADE, 151 CONSTRAINT fk_messages_template FOREIGN KEY (organization_id, template_id) 152 REFERENCES message_templates (organization_id, id) ON DELETE SET NULL, 153 CONSTRAINT fk_messages_sender FOREIGN KEY (organization_id, sent_by_user_id) 154 REFERENCES users (organization_id, id) ON DELETE SET NULL, 155 CONSTRAINT ck_messages_one_recipient CHECK ( 156 (candidate_id IS NOT NULL AND contact_id IS NULL) 157 OR (candidate_id IS NULL AND contact_id IS NOT NULL)), 158 CONSTRAINT ck_messages_sent_has_time CHECK (status <> 'sent' OR sent_at IS NOT NULL) 159); 160CREATE INDEX idx_messages_candidate ON messages (organization_id, candidate_id, created_at DESC) 161 WHERE candidate_id IS NOT NULL; 162CREATE INDEX idx_messages_contact ON messages (organization_id, contact_id, created_at DESC) 163 WHERE contact_id IS NOT NULL; 164CREATE INDEX idx_messages_sender ON messages (organization_id, sent_by_user_id, created_at DESC); 165ALTER TABLE messages ENABLE ROW LEVEL SECURITY; 166ALTER TABLE messages FORCE ROW LEVEL SECURITY; 167CREATE POLICY messages_isolation ON messages 168 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 169 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 170GRANT SELECT, INSERT, UPDATE ON messages TO missionmatch_app; 171 172SELECT mm_mirror_staging_role_privileges(); 173 174-- +goose Down 175DROP TABLE IF EXISTS messages; 176DROP TABLE IF EXISTS message_templates; 177DROP TABLE IF EXISTS candidate_documents; 178