-- Merge reporter + supervisor + capa_owner into one role: ssa (Site Safety Assistant). -- Run by hand on the production DB with the owner's go-ahead. Take a pg_dump first. -- This migration drops all RLS policies, migrates the enum type, recreates auth_user_role(), -- then re-applies all policies in a single transaction to avoid access gaps. -- After this, users must log out and in again (role is in the JWT cookie). -- ============================================================================ -- STEP 1: Add new 'ssa' value to enum (must be separate transaction) -- ============================================================================ BEGIN; ALTER TYPE user_role ADD VALUE IF NOT EXISTS 'ssa'; COMMIT; -- ============================================================================ -- STEP 2: Full migration in one transaction (no RLS gaps) -- ============================================================================ BEGIN; -- Update all old role names to 'ssa' UPDATE users SET role = 'ssa' WHERE role IN ('reporter', 'supervisor', 'capa_owner'); -- Drop all existing RLS policies (safe: RLS still ON during migration) DO $$ DECLARE r record; BEGIN FOR r IN SELECT schemaname, tablename, policyname FROM pg_policies WHERE schemaname = 'public' LOOP EXECUTE format('DROP POLICY %I ON %I.%I', r.policyname, r.schemaname, r.tablename); END LOOP; END $$; -- Recreate enum type with final values only ALTER TYPE user_role RENAME TO user_role_old; CREATE TYPE user_role AS ENUM ('ssa', 'hse', 'management', 'admin'); -- Alter column to new enum type ALTER TABLE users ALTER COLUMN role DROP DEFAULT; ALTER TABLE users ALTER COLUMN role TYPE user_role USING role::text::user_role; ALTER TABLE users ALTER COLUMN role SET DEFAULT 'ssa'; -- Drop and recreate auth_user_role() to return new enum type DROP FUNCTION IF EXISTS public.auth_user_role(); CREATE OR REPLACE FUNCTION public.auth_user_role() RETURNS user_role LANGUAGE sql SECURITY DEFINER STABLE SET search_path = public AS $$ SELECT role FROM public.users WHERE id = app_current_user_id() $$; -- Clean up old enum type DROP TYPE user_role_old; -- ============================================================================ -- STEP 3: Re-apply all RLS policies (same as db/rls.sql) -- ============================================================================ -- sites CREATE POLICY "sites_read" ON sites FOR SELECT USING (active = true); CREATE POLICY "sites_admin_all" ON sites FOR ALL USING (auth_user_role() = 'admin'); -- zones CREATE POLICY "zones_read" ON zones FOR SELECT USING (true); CREATE POLICY "zones_admin_all" ON zones FOR ALL USING (auth_user_role() = 'admin'); -- trucks CREATE POLICY "trucks_read" ON trucks FOR SELECT USING (true); CREATE POLICY "trucks_admin_all" ON trucks FOR ALL USING (auth_user_role() = 'admin'); -- users CREATE POLICY "users_read_own" ON users FOR SELECT USING (id = app_current_user_id()); CREATE POLICY "users_read_elevated" ON users FOR SELECT USING (auth_user_role() IN ('hse', 'admin', 'management', 'ssa')); CREATE POLICY "users_update_own" ON users FOR UPDATE USING (id = app_current_user_id()) WITH CHECK ( id = app_current_user_id() AND role = (SELECT role FROM public.users WHERE id = app_current_user_id()) ); CREATE POLICY "users_admin_all" ON users FOR ALL USING (auth_user_role() = 'admin'); -- incidents CREATE POLICY "incidents_insert" ON incidents FOR INSERT WITH CHECK (reported_by = app_current_user_id()); CREATE POLICY "incidents_read_reporter" ON incidents FOR SELECT USING (reported_by = app_current_user_id()); CREATE POLICY "incidents_read_ssa" ON incidents FOR SELECT USING (auth_user_role() = 'ssa' AND site_id = auth_user_site_id()); CREATE POLICY "incidents_read_elevated" ON incidents FOR SELECT USING (auth_user_role() IN ('hse', 'admin', 'management')); CREATE POLICY "incidents_update_elevated" ON incidents FOR UPDATE USING (auth_user_role() IN ('hse', 'admin', 'ssa')); -- evidence_files CREATE POLICY "evidence_insert" ON evidence_files FOR INSERT WITH CHECK (uploaded_by = app_current_user_id()); CREATE POLICY "evidence_read_uploader" ON evidence_files FOR SELECT USING (uploaded_by = app_current_user_id()); CREATE POLICY "evidence_read_elevated" ON evidence_files FOR SELECT USING (auth_user_role() IN ('hse', 'admin', 'management', 'ssa')); -- investigations CREATE POLICY "investigations_hse_admin" ON investigations FOR ALL USING (auth_user_role() IN ('hse', 'admin')); CREATE POLICY "investigations_read_ssa" ON investigations FOR SELECT USING (auth_user_role() = 'ssa'); -- capa_actions CREATE POLICY "capa_read_owner" ON capa_actions FOR SELECT USING (owner_user_id = app_current_user_id() OR department = auth_user_department()); CREATE POLICY "capa_update_owner" ON capa_actions FOR UPDATE USING (owner_user_id = app_current_user_id()); CREATE POLICY "capa_read_elevated" ON capa_actions FOR SELECT USING (auth_user_role() IN ('hse', 'admin', 'management', 'ssa')); CREATE POLICY "capa_hse_admin_all" ON capa_actions FOR ALL USING (auth_user_role() IN ('hse', 'admin')); -- dosh_reports CREATE POLICY "dosh_hse_admin" ON dosh_reports FOR ALL USING (auth_user_role() IN ('hse', 'admin')); -- notifications_log CREATE POLICY "notifications_insert_elevated" ON notifications_log FOR INSERT WITH CHECK (auth_user_role() IN ('hse', 'admin', 'ssa')); CREATE POLICY "notifications_read_own" ON notifications_log FOR SELECT USING (recipient_user_id = app_current_user_id()); CREATE POLICY "notifications_read_admin" ON notifications_log FOR SELECT USING (auth_user_role() = 'admin'); CREATE POLICY "notifications_update_own" ON notifications_log FOR UPDATE USING (recipient_user_id = app_current_user_id()) WITH CHECK (recipient_user_id = app_current_user_id()); -- audit_log CREATE POLICY "audit_read_elevated" ON audit_log FOR SELECT USING (auth_user_role() IN ('hse', 'admin')); -- app_settings CREATE POLICY "admin_select_settings" ON app_settings FOR SELECT USING (auth_user_role() = 'admin'); CREATE POLICY "admin_update_settings" ON app_settings FOR ALL USING (auth_user_role() = 'admin'); -- incident_addenda CREATE POLICY "addenda_read" ON incident_addenda FOR SELECT USING ( auth_user_role() IN ('hse', 'admin', 'ssa', 'management') OR EXISTS ( SELECT 1 FROM incidents i WHERE i.id = incident_id AND i.reported_by = app_current_user_id() ) ); CREATE POLICY "addenda_insert" ON incident_addenda FOR INSERT WITH CHECK ( author = app_current_user_id() AND auth_user_role() IN ('hse', 'admin', 'ssa') ); COMMIT; -- ============================================================================ -- After migration succeeds: -- 1. Verify all users have role = 'ssa' (no 'reporter', 'supervisor', 'capa_owner') -- 2. Ask users to log out and in again (clears old JWT with old role) -- 3. Deploy the new code (app/api, auth checks, RLS already match) -- ============================================================================