243 lines
14 KiB
SQL
243 lines
14 KiB
SQL
-- 001_dashboard_rbac_and_enum.sql
|
|
-- Manual one-shot: enum labels, permission tags, analytics_dashboard bundle,
|
|
-- source channels, and backfills. Run in psql against the app DB.
|
|
-- ADD VALUE cannot run inside a transaction that also uses the new labels —
|
|
-- run section 1a with autocommit (psql default outside BEGIN).
|
|
--
|
|
-- Order: (1) alembic upgrade for new tables/columns, (2) this file.
|
|
-- Section 1a (enum) can run before or after alembic.
|
|
|
|
-- =============================================================================
|
|
-- 1a. Extend candidate_application_status (unqualified — matches original migration)
|
|
-- =============================================================================
|
|
ALTER TYPE candidate_application_status ADD VALUE IF NOT EXISTS 'SCREENING';
|
|
ALTER TYPE candidate_application_status ADD VALUE IF NOT EXISTS 'ASSESSMENT';
|
|
ALTER TYPE candidate_application_status ADD VALUE IF NOT EXISTS 'INTERVIEW';
|
|
ALTER TYPE candidate_application_status ADD VALUE IF NOT EXISTS 'OFFER';
|
|
ALTER TYPE candidate_application_status ADD VALUE IF NOT EXISTS 'HIRED';
|
|
|
|
-- =============================================================================
|
|
-- 1b. Seed all 104 permission tags (module x action) idempotently
|
|
-- =============================================================================
|
|
INSERT INTO app.permission_tags
|
|
(tag_name, module, action, description, created_at, updated_at, is_active, is_deleted)
|
|
VALUES
|
|
('dashboard.view', 'dashboard', 'view', NULL, NOW(), NOW(), true, false),
|
|
('dashboard.create', 'dashboard', 'create', NULL, NOW(), NOW(), true, false),
|
|
('dashboard.edit', 'dashboard', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('dashboard.delete', 'dashboard', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('dashboard.approve', 'dashboard', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('dashboard.export', 'dashboard', 'export', NULL, NOW(), NOW(), true, false),
|
|
('dashboard.manage', 'dashboard', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('dashboard.configure', 'dashboard', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('inbox.view', 'inbox', 'view', NULL, NOW(), NOW(), true, false),
|
|
('inbox.create', 'inbox', 'create', NULL, NOW(), NOW(), true, false),
|
|
('inbox.edit', 'inbox', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('inbox.delete', 'inbox', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('inbox.approve', 'inbox', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('inbox.export', 'inbox', 'export', NULL, NOW(), NOW(), true, false),
|
|
('inbox.manage', 'inbox', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('inbox.configure', 'inbox', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('jobs.view', 'jobs', 'view', NULL, NOW(), NOW(), true, false),
|
|
('jobs.create', 'jobs', 'create', NULL, NOW(), NOW(), true, false),
|
|
('jobs.edit', 'jobs', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('jobs.delete', 'jobs', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('jobs.approve', 'jobs', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('jobs.export', 'jobs', 'export', NULL, NOW(), NOW(), true, false),
|
|
('jobs.manage', 'jobs', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('jobs.configure', 'jobs', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('candidates.view', 'candidates', 'view', NULL, NOW(), NOW(), true, false),
|
|
('candidates.create', 'candidates', 'create', NULL, NOW(), NOW(), true, false),
|
|
('candidates.edit', 'candidates', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('candidates.delete', 'candidates', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('candidates.approve', 'candidates', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('candidates.export', 'candidates', 'export', NULL, NOW(), NOW(), true, false),
|
|
('candidates.manage', 'candidates', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('candidates.configure', 'candidates', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('pipeline.view', 'pipeline', 'view', NULL, NOW(), NOW(), true, false),
|
|
('pipeline.create', 'pipeline', 'create', NULL, NOW(), NOW(), true, false),
|
|
('pipeline.edit', 'pipeline', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('pipeline.delete', 'pipeline', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('pipeline.approve', 'pipeline', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('pipeline.export', 'pipeline', 'export', NULL, NOW(), NOW(), true, false),
|
|
('pipeline.manage', 'pipeline', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('pipeline.configure', 'pipeline', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('interviews.view', 'interviews', 'view', NULL, NOW(), NOW(), true, false),
|
|
('interviews.create', 'interviews', 'create', NULL, NOW(), NOW(), true, false),
|
|
('interviews.edit', 'interviews', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('interviews.delete', 'interviews', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('interviews.approve', 'interviews', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('interviews.export', 'interviews', 'export', NULL, NOW(), NOW(), true, false),
|
|
('interviews.manage', 'interviews', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('interviews.configure', 'interviews', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('assessments.view', 'assessments', 'view', NULL, NOW(), NOW(), true, false),
|
|
('assessments.create', 'assessments', 'create', NULL, NOW(), NOW(), true, false),
|
|
('assessments.edit', 'assessments', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('assessments.delete', 'assessments', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('assessments.approve', 'assessments', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('assessments.export', 'assessments', 'export', NULL, NOW(), NOW(), true, false),
|
|
('assessments.manage', 'assessments', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('assessments.configure', 'assessments', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('offers.view', 'offers', 'view', NULL, NOW(), NOW(), true, false),
|
|
('offers.create', 'offers', 'create', NULL, NOW(), NOW(), true, false),
|
|
('offers.edit', 'offers', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('offers.delete', 'offers', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('offers.approve', 'offers', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('offers.export', 'offers', 'export', NULL, NOW(), NOW(), true, false),
|
|
('offers.manage', 'offers', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('offers.configure', 'offers', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('reports.view', 'reports', 'view', NULL, NOW(), NOW(), true, false),
|
|
('reports.create', 'reports', 'create', NULL, NOW(), NOW(), true, false),
|
|
('reports.edit', 'reports', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('reports.delete', 'reports', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('reports.approve', 'reports', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('reports.export', 'reports', 'export', NULL, NOW(), NOW(), true, false),
|
|
('reports.manage', 'reports', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('reports.configure', 'reports', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('analytics.view', 'analytics', 'view', NULL, NOW(), NOW(), true, false),
|
|
('analytics.create', 'analytics', 'create', NULL, NOW(), NOW(), true, false),
|
|
('analytics.edit', 'analytics', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('analytics.delete', 'analytics', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('analytics.approve', 'analytics', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('analytics.export', 'analytics', 'export', NULL, NOW(), NOW(), true, false),
|
|
('analytics.manage', 'analytics', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('analytics.configure', 'analytics', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('job_board.view', 'job_board', 'view', NULL, NOW(), NOW(), true, false),
|
|
('job_board.create', 'job_board', 'create', NULL, NOW(), NOW(), true, false),
|
|
('job_board.edit', 'job_board', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('job_board.delete', 'job_board', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('job_board.approve', 'job_board', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('job_board.export', 'job_board', 'export', NULL, NOW(), NOW(), true, false),
|
|
('job_board.manage', 'job_board', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('job_board.configure', 'job_board', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('settings.view', 'settings', 'view', NULL, NOW(), NOW(), true, false),
|
|
('settings.create', 'settings', 'create', NULL, NOW(), NOW(), true, false),
|
|
('settings.edit', 'settings', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('settings.delete', 'settings', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('settings.approve', 'settings', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('settings.export', 'settings', 'export', NULL, NOW(), NOW(), true, false),
|
|
('settings.manage', 'settings', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('settings.configure', 'settings', 'configure', NULL, NOW(), NOW(), true, false),
|
|
('rbac_users.view', 'rbac_users', 'view', NULL, NOW(), NOW(), true, false),
|
|
('rbac_users.create', 'rbac_users', 'create', NULL, NOW(), NOW(), true, false),
|
|
('rbac_users.edit', 'rbac_users', 'edit', NULL, NOW(), NOW(), true, false),
|
|
('rbac_users.delete', 'rbac_users', 'delete', NULL, NOW(), NOW(), true, false),
|
|
('rbac_users.approve', 'rbac_users', 'approve', NULL, NOW(), NOW(), true, false),
|
|
('rbac_users.export', 'rbac_users', 'export', NULL, NOW(), NOW(), true, false),
|
|
('rbac_users.manage', 'rbac_users', 'manage', NULL, NOW(), NOW(), true, false),
|
|
('rbac_users.configure', 'rbac_users', 'configure', NULL, NOW(), NOW(), true, false)
|
|
ON CONFLICT (tag_name) DO NOTHING;
|
|
|
|
-- Bundle holding dashboard / analytics / offers / interviews.view tags
|
|
INSERT INTO app.permissions (name, description, permission_tags, is_system, created_at, updated_at, is_active, is_deleted)
|
|
SELECT
|
|
'analytics_dashboard',
|
|
'Dashboard KPI tiles, analytics charts, offers, and interview list',
|
|
(
|
|
SELECT COALESCE(jsonb_agg(id ORDER BY id), '[]'::jsonb)
|
|
FROM app.permission_tags
|
|
WHERE is_deleted = false
|
|
AND (
|
|
module IN ('dashboard', 'analytics', 'offers')
|
|
OR tag_name = 'interviews.view'
|
|
)
|
|
),
|
|
true,
|
|
NOW(),
|
|
NOW(),
|
|
true,
|
|
false
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM app.permissions WHERE name = 'analytics_dashboard'
|
|
);
|
|
|
|
-- Append the bundle id to the named system roles (idempotent)
|
|
UPDATE app.roles r
|
|
SET permissions = COALESCE(r.permissions, '[]'::jsonb) || jsonb_build_array(p.id),
|
|
updated_at = NOW()
|
|
FROM app.permissions p
|
|
WHERE p.name = 'analytics_dashboard'
|
|
AND r.role_name IN (
|
|
'system_administrator',
|
|
'hr_administrator',
|
|
'recruiter',
|
|
'hiring_manager',
|
|
'department_head',
|
|
'ceo'
|
|
)
|
|
AND NOT (COALESCE(r.permissions, '[]'::jsonb) @> jsonb_build_array(p.id));
|
|
|
|
-- =============================================================================
|
|
-- 1c. Source channels (eleven BRD channels)
|
|
-- =============================================================================
|
|
INSERT INTO app.source_channels (key, label, is_active, created_at, updated_at)
|
|
VALUES
|
|
('microsoft_outlook', 'Microsoft Outlook', true, NOW(), NOW()),
|
|
('career_portal', 'Career Portal', true, NOW(), NOW()),
|
|
('manual_cv_upload', 'Manual CV Upload', true, NOW(), NOW()),
|
|
('linkedin', 'LinkedIn', true, NOW(), NOW()),
|
|
('indeed', 'Indeed', true, NOW(), NOW()),
|
|
('rozee', 'Rozee', true, NOW(), NOW()),
|
|
('mustakbil', 'Mustakbil', true, NOW(), NOW()),
|
|
('employee_referral', 'Employee Referral', true, NOW(), NOW()),
|
|
('recruitment_agency', 'Recruitment Agency', true, NOW(), NOW()),
|
|
('campus_hiring', 'Campus Hiring', true, NOW(), NOW()),
|
|
('walk_in', 'Walk-in', true, NOW(), NOW())
|
|
ON CONFLICT (key) DO NOTHING;
|
|
|
|
-- =============================================================================
|
|
-- Backfills (require new columns/tables from alembic)
|
|
-- =============================================================================
|
|
|
|
-- Source channel from message_to board tags; default Microsoft Outlook
|
|
UPDATE app.inbox_messages m
|
|
SET source_channel_id = sc.id
|
|
FROM app.source_channels sc
|
|
WHERE m.source_channel_id IS NULL
|
|
AND (
|
|
(LOWER(m.message_to) LIKE '%linkedin%' AND sc.key = 'linkedin')
|
|
OR (LOWER(m.message_to) LIKE '%indeed%' AND sc.key = 'indeed')
|
|
OR (LOWER(m.message_to) LIKE '%rozee%' AND sc.key = 'rozee')
|
|
OR (LOWER(m.message_to) LIKE '%mustakbil%' AND sc.key = 'mustakbil')
|
|
OR (LOWER(m.message_to) LIKE '%referral%' AND sc.key = 'employee_referral')
|
|
OR (LOWER(m.message_to) LIKE '%agency%' AND sc.key = 'recruitment_agency')
|
|
OR (LOWER(m.message_to) LIKE '%campus%' AND sc.key = 'campus_hiring')
|
|
OR (LOWER(m.message_to) LIKE '%portal%' AND sc.key = 'career_portal')
|
|
OR (LOWER(m.message_to) LIKE '%walk%' AND sc.key = 'walk_in')
|
|
OR (LOWER(m.message_to) LIKE '%manual%' AND sc.key = 'manual_cv_upload')
|
|
);
|
|
|
|
UPDATE app.inbox_messages m
|
|
SET source_channel_id = sc.id
|
|
FROM app.source_channels sc
|
|
WHERE m.source_channel_id IS NULL
|
|
AND sc.key = 'microsoft_outlook';
|
|
|
|
-- One open stage-transition row per application (cannot invent history)
|
|
INSERT INTO app.application_stage_transitions
|
|
(id, inbox_id, from_stage, to_stage, valid_from, valid_to, changed_by, actor_kind, change_reason, created_at)
|
|
SELECT
|
|
gen_random_uuid(),
|
|
i.id,
|
|
NULL,
|
|
m.application_status::text,
|
|
COALESCE(i.created_at, NOW()),
|
|
NULL,
|
|
NULL,
|
|
'system',
|
|
'backfill',
|
|
NOW()
|
|
FROM app.inbox i
|
|
JOIN app.inbox_messages m ON m.id = i.message_id
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM app.application_stage_transitions t WHERE t.inbox_id = i.id
|
|
);
|
|
|
|
-- Requisition status from is_active / is_deleted
|
|
UPDATE app.job_posts
|
|
SET requisition_status = CASE
|
|
WHEN is_active AND NOT is_deleted THEN 'open'
|
|
ELSE 'closed'
|
|
END
|
|
WHERE requisition_status IS NULL OR requisition_status = '';
|