44 lines
2.2 KiB
MySQL
44 lines
2.2 KiB
MySQL
|
-- Role and Permission Tables Migration
|
||
|
|
-- Creates tables for custom role management beyond Better Auth's built-in roles
|
||
|
|
|
||
|
|
-- Custom roles table (for user-defined roles beyond the defaults)
|
||
|
|
CREATE TABLE IF NOT EXISTS "role" (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
name TEXT NOT NULL UNIQUE,
|
||
|
|
description TEXT,
|
||
|
|
permissions JSONB NOT NULL DEFAULT '{}',
|
||
|
|
is_system BOOLEAN NOT NULL DEFAULT FALSE,
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
updated_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- User-role assignments (for custom roles)
|
||
|
|
-- Note: Better Auth stores the primary role in the user table
|
||
|
|
-- This table is for additional role assignments if needed
|
||
|
|
CREATE TABLE IF NOT EXISTS "user_role" (
|
||
|
|
id TEXT PRIMARY KEY,
|
||
|
|
user_id TEXT NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
|
||
|
|
role_id TEXT NOT NULL REFERENCES "role"(id) ON DELETE CASCADE,
|
||
|
|
assigned_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
assigned_by TEXT REFERENCES "user"(id) ON DELETE SET NULL,
|
||
|
|
UNIQUE(user_id, role_id)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Indexes
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_role_name ON "role"(name);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_role_user_id ON "user_role"(user_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_user_role_role_id ON "user_role"(role_id);
|
||
|
|
|
||
|
|
-- Insert default system roles
|
||
|
|
INSERT INTO "role" (id, name, description, permissions, is_system) VALUES
|
||
|
|
('role_super_admin', 'super-admin', 'Full system access with all permissions',
|
||
|
|
'{"tickets": ["create", "read", "update", "delete"], "configItems": ["create", "read", "update", "delete"], "admin": ["access"], "users": ["create", "read", "update", "delete", "invite", "ban"], "roles": ["create", "read", "update", "delete"], "auditLog": ["read"], "settings": ["read", "update"]}',
|
||
|
|
TRUE),
|
||
|
|
('role_admin', 'admin', 'Administrative access without role management',
|
||
|
|
'{"tickets": ["create", "read", "update", "delete"], "configItems": ["create", "read", "update", "delete"], "admin": ["access"], "users": ["create", "read", "update", "invite"], "roles": ["read"], "auditLog": ["read"], "settings": ["read"]}',
|
||
|
|
TRUE),
|
||
|
|
('role_user', 'user', 'Standard user access',
|
||
|
|
'{"tickets": ["create", "read", "update"], "configItems": ["read"]}',
|
||
|
|
TRUE)
|
||
|
|
ON CONFLICT (id) DO NOTHING;
|