Janady13/missionmatch / db/migrations/0088_teams_and_record_scope.sql

1-- +goose Up 2-- ACCESS LEVELS, THE THIRD AXIS. Directive: build access out to what business and industry standard are. 3-- 4-- A recruiting platform's access model has three axes and this schema had one and a half. 5-- 6-- 1. WHAT ACTIONS may I take. The capability matrix, six roles and two dozen capabilities. Already correct. 7-- 2. WHICH FIELDS may I see on a record I can read. Field sensitivity, migration 0087. Done. 8-- 3. WHICH RECORDS may I see at all. own, my team's, or the whole organization. THIS IS THE MISSING ONE, 9-- and it is the axis every established product in this market has: Bullhorn, Greenhouse, Lever and 10-- Salesforce all express it as owner, team and organization. 11-- 12-- Two things were missing under it. There was no notion of a TEAM, so "my team's records" could not be 13-- expressed, and CANDIDATES HAD NO OWNER, so the single most important record type in the product could not 14-- be scoped at all. Both are added here. 15-- 16-- THE DEFAULT IS ORGANIZATION, WHICH IS EXACTLY TODAY'S BEHAVIOUR. Nothing narrows until an owner chooses to 17-- narrow it. A migration that silently hid records from people who could see them yesterday would be a 18-- support incident on the morning it shipped, not a feature. 19-- 20-- OWNER AND ADMIN ARE ALWAYS ORGANIZATION WIDE AND THAT IS NOT CONFIGURABLE. Somebody has to be able to see 21-- everything, and it is the person accountable for the account. A product that lets an administrator lock 22-- themselves out of their own data is a product that generates a support call it cannot resolve. 23 24-- ============================================================================ 25-- 1. teams, the unit a desk belongs to 26-- ============================================================================ 27CREATE TABLE teams ( 28 organization_id uuid NOT NULL REFERENCES organizations(id), 29 id uuid NOT NULL DEFAULT gen_random_uuid(), 30 name text NOT NULL, 31 -- The lead is a convenience for display and for routing, NOT an authority. Authority comes from the role 32 -- on the ladder, so making somebody a team lead does not quietly promote them. 33 lead_user_id uuid, 34 status text NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'archived')), 35 created_at timestamptz NOT NULL DEFAULT now(), 36 updated_at timestamptz NOT NULL DEFAULT now(), 37 PRIMARY KEY (organization_id, id), 38 CONSTRAINT fk_teams_lead FOREIGN KEY (organization_id, lead_user_id) 39 REFERENCES users (organization_id, id) ON DELETE SET NULL, 40 CONSTRAINT ck_teams_name CHECK (length(btrim(name)) BETWEEN 1 AND 120) 41); 42-- Archived teams keep their name out of the way without deleting the history of who was on which desk. 43CREATE UNIQUE INDEX uq_teams_name ON teams (organization_id, lower(btrim(name))) WHERE status = 'active'; 44ALTER TABLE teams ENABLE ROW LEVEL SECURITY; 45ALTER TABLE teams FORCE ROW LEVEL SECURITY; 46CREATE POLICY teams_isolation ON teams 47 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 48 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 49GRANT SELECT, INSERT, UPDATE ON teams TO missionmatch_app; 50 51-- ============================================================================ 52-- 2. team_members 53-- ============================================================================ 54-- A person may belong to MORE THAN ONE team, deliberately. Agencies run people across desks, and a model 55-- that forces one team per person makes the common case a lie. Team scope therefore means the union of every 56-- team the caller belongs to. 57CREATE TABLE team_members ( 58 organization_id uuid NOT NULL REFERENCES organizations(id), 59 team_id uuid NOT NULL, 60 user_id uuid NOT NULL, 61 added_at timestamptz NOT NULL DEFAULT now(), 62 added_by uuid, 63 PRIMARY KEY (organization_id, team_id, user_id), 64 CONSTRAINT fk_team_members_team FOREIGN KEY (organization_id, team_id) 65 REFERENCES teams (organization_id, id) ON DELETE CASCADE, 66 CONSTRAINT fk_team_members_user FOREIGN KEY (organization_id, user_id) 67 REFERENCES users (organization_id, id) ON DELETE CASCADE, 68 CONSTRAINT fk_team_members_adder FOREIGN KEY (organization_id, added_by) 69 REFERENCES users (organization_id, id) ON DELETE SET NULL 70); 71CREATE INDEX idx_team_members_user ON team_members (organization_id, user_id); 72ALTER TABLE team_members ENABLE ROW LEVEL SECURITY; 73ALTER TABLE team_members FORCE ROW LEVEL SECURITY; 74CREATE POLICY team_members_isolation ON team_members 75 USING (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid) 76 WITH CHECK (organization_id = NULLIF(current_setting('app.current_org_id', true), '')::uuid); 77-- DELETE is granted because removing somebody from a desk is not a historical claim, it is a correction, and 78-- the audit trail records the change. This matches the reasoning already recorded for org_secrets. 79GRANT SELECT, INSERT, UPDATE, DELETE ON team_members TO missionmatch_app; 80 81-- ============================================================================ 82-- 3. candidates get an owner 83-- ============================================================================ 84-- The gap that made record scope impossible for the record type that matters most. NULLABLE, because the 85-- entire existing bench is unowned and inventing an owner for it would be a fabrication. An unowned candidate 86-- is visible at every scope, which is the correct reading: nobody has claimed responsibility for them, so 87-- nobody is excluded from picking them up. 88ALTER TABLE candidates ADD COLUMN owner_user_id uuid; 89ALTER TABLE candidates ADD CONSTRAINT fk_candidates_owner 90 FOREIGN KEY (organization_id, owner_user_id) REFERENCES users (organization_id, id) ON DELETE SET NULL; 91CREATE INDEX idx_candidates_owner ON candidates (organization_id, owner_user_id) 92 WHERE owner_user_id IS NOT NULL; 93 94-- ============================================================================ 95-- 4. the scope settings 96-- ============================================================================ 97-- TWO settings rather than one per role, because the ladder already has the answer for the other four. 98-- Owner and admin are organization wide and not configurable. Manager either sees their team or everything. 99-- Everyone below manager shares one setting, since recruiter, member and viewer are all "staff on a desk" 100-- from a data scoping point of view even though their capabilities differ sharply. 101ALTER TABLE org_settings ADD COLUMN staff_record_scope text NOT NULL DEFAULT 'organization' 102 CHECK (staff_record_scope IN ('own', 'team', 'organization')); 103ALTER TABLE org_settings ADD COLUMN manager_record_scope text NOT NULL DEFAULT 'organization' 104 CHECK (manager_record_scope IN ('team', 'organization')); 105-- Whether a record with no owner is visible to everyone. Default true, because an unowned candidate that 106-- nobody can see is a candidate nobody will ever work, which is worse than one everybody can see. 107ALTER TABLE org_settings ADD COLUMN unowned_records_visible boolean NOT NULL DEFAULT true; 108 109-- ============================================================================ 110-- 5. the resolver, in the database, so one definition serves every caller 111-- ============================================================================ 112-- +goose StatementBegin 113-- mm_team_peer_ids returns every user who shares at least one team with the given user, INCLUDING that user. 114-- Including them matters: a person on no team must still see their own records under team scope, or joining 115-- an organization before being assigned a desk would make your own work disappear. 116CREATE OR REPLACE FUNCTION mm_team_peer_ids(p_user uuid) RETURNS TABLE (user_id uuid) AS $$ 117 SELECT p_user 118 UNION 119 SELECT tm2.user_id 120 FROM team_members tm1 121 JOIN team_members tm2 122 ON tm2.organization_id = tm1.organization_id 123 AND tm2.team_id = tm1.team_id 124 WHERE tm1.user_id = p_user; 125$$ LANGUAGE sql STABLE; 126-- +goose StatementEnd 127GRANT EXECUTE ON FUNCTION mm_team_peer_ids(uuid) TO missionmatch_app; 128 129SELECT mm_mirror_staging_role_privileges(); 130 131-- +goose Down 132DROP FUNCTION IF EXISTS mm_team_peer_ids(uuid); 133ALTER TABLE org_settings DROP COLUMN IF EXISTS unowned_records_visible; 134ALTER TABLE org_settings DROP COLUMN IF EXISTS manager_record_scope; 135ALTER TABLE org_settings DROP COLUMN IF EXISTS staff_record_scope; 136DROP INDEX IF EXISTS idx_candidates_owner; 137ALTER TABLE candidates DROP CONSTRAINT IF EXISTS fk_candidates_owner; 138ALTER TABLE candidates DROP COLUMN IF EXISTS owner_user_id; 139DROP TABLE IF EXISTS team_members; 140DROP TABLE IF EXISTS teams; 141