25 lines
1.0 KiB
SQL
25 lines
1.0 KiB
SQL
-- 026: The organisation runs four staff roles — system_administrator,
|
|
-- recruiter, hiring_manager, department_head. Soft-delete the unused seeded
|
|
-- staff roles (hr_administrator, interviewer, ceo) so they stop appearing in
|
|
-- the Access Control screen and every role picker (listings filter on
|
|
-- is_deleted).
|
|
--
|
|
-- NOT touched:
|
|
-- * candidate — not a staff role: every applicant account is a role-8 user
|
|
-- and the Candidates screen is keyed to it.
|
|
-- * any role that still has live members — pruning a role out from under a
|
|
-- user would strand their permissions; such a role keeps working until
|
|
-- the members are reassigned by hand, and this migration (idempotent)
|
|
-- picks it up on a later boot.
|
|
UPDATE app.roles r
|
|
SET is_deleted = TRUE,
|
|
is_active = FALSE,
|
|
updated_at = NOW()
|
|
WHERE r.role_name IN ('hr_administrator', 'interviewer', 'ceo')
|
|
AND r.is_deleted = FALSE
|
|
AND NOT EXISTS (
|
|
SELECT 1 FROM app.users u
|
|
WHERE u.role_id = r.id
|
|
AND COALESCE(u.is_deleted, FALSE) = FALSE
|
|
);
|