Janady13/missionmatch / db/migrations/0087_org_settings.sql

1-- +goose Up 2-- PER ORGANIZATION SETTINGS AND PER ORGANIZATION SECRETS. 3-- 4-- WHY THIS EXISTS. Three separate asks land on the same missing thing. Invites had to be delivered by hand 5-- because the platform had no way to hold a customer's mail credentials. Recruiters were doubling work on the 6-- same candidate because nothing recorded who was already on it. Rates were visible to everyone because 7-- sensitivity had nowhere to be configured. All three need a place where a tenant states how it works, and one 8-- of them needs a place where a tenant stores a password. So this is two tables, not one, because a setting 9-- and a secret deserve different handling. 10-- 11-- SECRETS ARE SEALED, NEVER STORED. org_secrets holds AES 256 GCM ciphertext under a key that lives only in 12-- the server env file, carried with a key_version so the key can rotate without a flag day. That is exactly 13-- the mfa_totp pattern from migration 0009, deliberately reused rather than reinvented: one definition of what 14-- encrypted at rest means in this system. NOTHING in this table is a fragment of a plaintext secret. There is 15-- no last_four, no hint, no length column, because each of those is a free guess for anyone who reaches the 16-- row. 17-- 18-- SETTINGS MAY NOT WEAKEN A COMPLIANCE GUARANTEE, and that bound is the reason this table is typed columns 19-- rather than a key value bag. Human approval of every slate member, adverse impact reporting, the audit 20-- trail, and the fixed NIST session timeouts are NOT here and must never be added here. A tenant configures 21-- how its people work. A tenant does not configure whether this is still an auditable Automated Employment 22-- Decision Tool. A key value bag would have made the wrong setting a one line insert; typed columns make it a 23-- migration somebody has to justify. 24-- 25-- POLICIES CARRY NO "TO" CLAUSE. Same reason as migration 0086: a policy naming only missionmatch_app does not 26-- apply to missionmatch_staging_app, and with no applicable policy RLS denies everything, which is what broke 27-- Gate A. An unaddressed policy applies to every role and is correct in every environment. 28 29-- ============================================================================ 30-- 1. org_settings 31-- ============================================================================ 32CREATE TABLE org_settings ( 33 organization_id uuid PRIMARY KEY REFERENCES organizations(id) ON DELETE CASCADE, 34 35 -- ---- outbound email ----------------------------------------------------------------------------- 36 -- Off by default, so an organization that never configures mail keeps exactly the behaviour it has today: 37 -- the invite endpoint returns the one time link and a human delivers it. Turning this on is a deliberate 38 -- act, and it only takes effect once a test send has actually succeeded (see smtp_verified_at). 39 email_delivery_enabled boolean NOT NULL DEFAULT false, 40 email_from_address text, 41 email_from_name text, 42 email_reply_to text, 43 smtp_host text, 44 smtp_port int CHECK (smtp_port IS NULL OR (smtp_port BETWEEN 1 AND 65535)), 45 smtp_username text, 46 -- THERE IS NO PLAINTEXT OPTION. Cleartext SMTP is not offered at any port, because the two things this 47 -- transport carries are an activation credential and a candidate's name, and neither belongs on the wire 48 -- in the clear. starttls upgrades an established session, implicit_tls dials TLS immediately (port 465). 49 smtp_security text NOT NULL DEFAULT 'starttls' 50 CHECK (smtp_security IN ('starttls', 'implicit_tls')), 51 -- Set ONLY by a successful test send and cleared by any settings change that could invalidate it. Mail is 52 -- never attempted against an unverified configuration, so a typo in a host name surfaces when an admin 53 -- presses test, not when a candidate never receives an invite and nobody finds out for a week. 54 smtp_verified_at timestamptz, 55 smtp_last_attempt_at timestamptz, 56 -- The transport's own error text, kept so an admin can self serve a fix. It is a connection level message, 57 -- never a credential, and the application refuses to write it if it would echo the username or password. 58 smtp_last_error text, 59 60 -- ---- who can see what a recruiter is working on ------------------------------------------------- 61 -- The ask this answers is double work: two recruiters calling the same person on the same day makes the 62 -- agency look disorganised to the one human whose opinion decides the placement. organization is the 63 -- default because visibility is the entire point, and the narrower settings exist for firms that run 64 -- competing desks and genuinely do not want a shared pipeline. 65 work_visibility text NOT NULL DEFAULT 'organization' 66 CHECK (work_visibility IN ('private', 'team', 'organization')), 67 -- Presence is the soft version: not a lock, just a note that somebody else is here. It is what stops most 68 -- collisions without stopping anybody from working. 69 show_activity_presence boolean NOT NULL DEFAULT true, 70 -- Claiming is the hard version, for firms that want exclusivity on a candidate while somebody works them. 71 claim_required boolean NOT NULL DEFAULT false, 72 -- A claim nobody releases is a claim that silently owns a candidate forever, which is worse than no 73 -- claiming at all, so claims expire and the default is three working days. 74 claim_expiry_hours int NOT NULL DEFAULT 72 75 CHECK (claim_expiry_hours BETWEEN 1 AND 2160), 76 77 -- ---- field sensitivity -------------------------------------------------------------------------- 78 -- Pay data is the field where a wrong default is a real harm, not an inconvenience. A junior recruiter who 79 -- can see the bill rate can infer the margin, and a candidate who learns the margin is a candidate who has 80 -- learned what the agency makes on them. The industry default is that pay data is management visible, so 81 -- that is the default here. 82 rate_visibility text NOT NULL DEFAULT 'managers' 83 CHECK (rate_visibility IN ('owner', 'managers', 'team', 'organization')), 84 margin_visibility text NOT NULL DEFAULT 'owner' 85 CHECK (margin_visibility IN ('owner', 'managers', 'team', 'organization')), 86 -- Contact details are the other sensitive class and for the opposite reason: they are the asset a leaving 87 -- recruiter walks out with. team by default, so a desk sees its own people and not the whole database. 88 contact_visibility text NOT NULL DEFAULT 'team' 89 CHECK (contact_visibility IN ('owner', 'managers', 'team', 'organization')), 90 91 -- ---- operating defaults ------------------------------------------------------------------------- 92 -- The settings nobody asks for until the day they are wrong. A deadline rendered in the wrong zone is a 93 -- missed submission, and this platform's whole calendar is federal due dates. 94 time_zone text NOT NULL DEFAULT 'America/New_York', 95 invite_expiry_hours int NOT NULL DEFAULT 72 96 CHECK (invite_expiry_hours BETWEEN 1 AND 336), 97 -- The role a new invite proposes. It is a default in the user interface, NOT an authority bound: the 98 -- separation of duties rule that an inviter may only invite strictly below themselves still decides what 99 -- is actually permitted, and this cannot raise it. 100 default_invite_role text NOT NULL DEFAULT 'recruiter', 101 102 updated_at timestamptz NOT NULL DEFAULT now(), 103 updated_by uuid, 104 CONSTRAINT fk_org_settings_updated_by FOREIGN KEY (organization_id, updated_by) 105 REFERENCES users (organization_id, id), 106 -- Delivery cannot be switched on against a configuration that has never worked. The application checks 107 -- this too, and says so in a sentence; the constraint is here because a check that lives only in a handler 108 -- is a check the second caller skips. 109 CONSTRAINT ck_org_settings_email_ready CHECK ( 110 email_delivery_enabled = false 111 OR (smtp_host IS NOT NULL AND smtp_port IS NOT NULL 112 AND email_from_address IS NOT NULL AND smtp_verified_at IS NOT NULL) 113 ) 114); 115ALTER TABLE org_settings ENABLE ROW LEVEL SECURITY; 116ALTER TABLE org_settings FORCE ROW LEVEL SECURITY; 117CREATE POLICY org_settings_isolation ON org_settings 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); 120GRANT SELECT, INSERT, UPDATE ON org_settings TO missionmatch_app; 121 122-- ============================================================================ 123-- 2. org_secrets 124-- ============================================================================ 125-- One sealed value per (organization, name). name is a stable key the application knows, for example 126-- smtp_password today and a source connector credential when in place federation lands, which is the whole 127-- reason this is a general table and not two columns on org_settings. 128CREATE TABLE org_secrets ( 129 organization_id uuid NOT NULL REFERENCES organizations(id) ON DELETE CASCADE, 130 name text NOT NULL CHECK (name ~ '^[a-z][a-z0-9_.]{1,62}$'), 131 ciphertext bytea NOT NULL, 132 nonce bytea NOT NULL, 133 key_version int NOT NULL, 134 created_at timestamptz NOT NULL DEFAULT now(), 135 updated_at timestamptz NOT NULL DEFAULT now(), 136 updated_by uuid, 137 PRIMARY KEY (organization_id, name), 138 CONSTRAINT fk_org_secrets_updated_by FOREIGN KEY (organization_id, updated_by) 139 REFERENCES users (organization_id, id) 140); 141ALTER TABLE org_secrets ENABLE ROW LEVEL SECURITY; 142ALTER TABLE org_secrets FORCE ROW LEVEL SECURITY; 143CREATE POLICY org_secrets_isolation ON org_secrets 144 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 145 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 146-- DELETE is granted here and nowhere else in this schema, and the exception is deliberate. Everywhere else a 147-- removed row is a historical claim that has to stay auditable. A revoked credential is the opposite: leaving 148-- the ciphertext behind after an admin removes it means the value is still one key compromise away from being 149-- read. Removing a secret is recorded in audit_logs by name, never by value. 150GRANT SELECT, INSERT, UPDATE, DELETE ON org_secrets TO missionmatch_app; 151 152-- ============================================================================ 153-- 3. who is working what, the double work answer 154-- ============================================================================ 155-- work_claims records that a named person is on a named record right now. It is one table across entity types 156-- rather than a claimed_by column on each, because the question a recruiter asks is "is anyone on this" and 157-- the answer has to be the same shape for a candidate, a requisition and an opportunity. 158-- 159-- THIS IS NOT A SECOND OWNERSHIP COLUMN. clients and requisitions already carry owner_user_id from migration 160-- 0047, with claim and reassign routes in internal/modules/recruiting/ownership.go, and that column stays 161-- authoritative for whose desk a record belongs to. A claim here is a DIFFERENT and shorter lived fact: not 162-- "this account is mine" but "I am on this right now, do not also call them today". The two coexist, and the 163-- read surface shows both, because a candidate being on Dana's desk and Marcus actively submitting them this 164-- afternoon are both true and a recruiter needs to see each. CANDIDATES are the case that has neither today, 165-- and a candidate called twice in one day by the same agency is the exact complaint this closes. 166-- 167-- It is NOT a lock. released_at and expires_at both exist, and a claim that expires simply stops being a 168-- claim: nothing in the application blocks a second person from working a claimed record unless the 169-- organization has set claim_required, and even then the block is a warning path with an override, because a 170-- staffing firm that cannot reassign a desk at five o'clock on a deadline day is a staffing firm that loses 171-- the award. 172CREATE TABLE work_claims ( 173 organization_id uuid NOT NULL REFERENCES organizations(id), 174 id uuid NOT NULL DEFAULT gen_random_uuid(), 175 entity_type text NOT NULL CHECK (entity_type IN ('candidate', 'requisition', 'opportunity', 'proposal', 'client')), 176 entity_id uuid NOT NULL, 177 user_id uuid NOT NULL, 178 -- What they are doing, so the note a colleague sees is useful rather than merely a name. 179 activity text NOT NULL DEFAULT 'working' 180 CHECK (activity IN ('working', 'screening', 'submitting', 'interviewing', 'negotiating')), 181 note text NOT NULL DEFAULT '', 182 claimed_at timestamptz NOT NULL DEFAULT now(), 183 expires_at timestamptz NOT NULL, 184 released_at timestamptz, 185 released_by uuid, 186 PRIMARY KEY (organization_id, id), 187 CONSTRAINT fk_work_claims_user FOREIGN KEY (organization_id, user_id) 188 REFERENCES users (organization_id, id), 189 CONSTRAINT ck_work_claims_window CHECK (expires_at > claimed_at) 190); 191-- One live claim per record per organization. The partial unique index is what makes "who is on this" a single 192-- row answer rather than a list to reconcile, and it is enforced by the database because two recruiters 193-- pressing claim at the same instant is precisely the case a handler side check loses. 194CREATE UNIQUE INDEX uq_work_claims_live ON work_claims (organization_id, entity_type, entity_id) 195 WHERE released_at IS NULL; 196CREATE INDEX idx_work_claims_user ON work_claims (organization_id, user_id, claimed_at DESC) 197 WHERE released_at IS NULL; 198CREATE INDEX idx_work_claims_expiry ON work_claims (expires_at) 199 WHERE released_at IS NULL; 200ALTER TABLE work_claims ENABLE ROW LEVEL SECURITY; 201ALTER TABLE work_claims FORCE ROW LEVEL SECURITY; 202CREATE POLICY work_claims_isolation ON work_claims 203 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 204 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 205GRANT SELECT, INSERT, UPDATE ON work_claims TO missionmatch_app; 206 207-- ============================================================================ 208-- 4. lightweight presence, the soft half of the same answer 209-- ============================================================================ 210-- work_touches is the "Dana looked at this eleven minutes ago" signal. It is separate from work_claims because 211-- it answers a different question and has a different lifetime: a claim is an intention that lasts days, a 212-- touch is a fact that stops being interesting within the hour. 213-- 214-- IT IS UPSERTED, NEVER APPENDED. One row per person per record, overwritten on each visit. An append only 215-- design here would generate a row per page view per recruiter per candidate, which is millions of rows a 216-- month describing something nobody will ever read twice, and it would put a browsing history of a named 217-- employee in a table that is trivially reportable. This is presence, not surveillance. 218CREATE TABLE work_touches ( 219 organization_id uuid NOT NULL REFERENCES organizations(id), 220 entity_type text NOT NULL CHECK (entity_type IN ('candidate', 'requisition', 'opportunity', 'proposal', 'client')), 221 entity_id uuid NOT NULL, 222 user_id uuid NOT NULL, 223 last_seen_at timestamptz NOT NULL DEFAULT now(), 224 PRIMARY KEY (organization_id, entity_type, entity_id, user_id), 225 CONSTRAINT fk_work_touches_user FOREIGN KEY (organization_id, user_id) 226 REFERENCES users (organization_id, id) 227); 228CREATE INDEX idx_work_touches_recent ON work_touches (organization_id, entity_type, entity_id, last_seen_at DESC); 229ALTER TABLE work_touches ENABLE ROW LEVEL SECURITY; 230ALTER TABLE work_touches FORCE ROW LEVEL SECURITY; 231CREATE POLICY work_touches_isolation ON work_touches 232 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 233 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 234GRANT SELECT, INSERT, UPDATE ON work_touches TO missionmatch_app; 235 236-- ============================================================================ 237-- 5. every existing organization gets its defaults now 238-- ============================================================================ 239-- Backfilled rather than created lazily, so a read never has to decide whether an absent row means defaults or 240-- means something went wrong. Provisioning inserts one too, see below. 241INSERT INTO org_settings (organization_id) 242SELECT id FROM organizations 243ON CONFLICT (organization_id) DO NOTHING; 244 245-- Staging parity, the same call migration 0086 ends with. Roles are cluster wide but privileges are per 246-- database, so the helper is a no op unless this really is the staging database. 247SELECT mm_mirror_staging_role_privileges(); 248 249-- +goose Down 250DROP TABLE IF EXISTS work_touches; 251DROP TABLE IF EXISTS work_claims; 252DROP TABLE IF EXISTS org_secrets; 253DROP TABLE IF EXISTS org_settings; 254