9f912aed24
Added comprehensive authentication and authorization system: Authentication System: - Better Auth integration with session management - Login/logout pages and API routes - Middleware for route protection - Auth utilities and client libraries User Management: - User list, detail, and invite pages - User API endpoints (CRUD operations) - Session management for users - Profile settings page Role-Based Access Control: - Role management pages (list, create, edit) - Permission system with granular controls - Role assignment to users - Role API endpoints Admin Features: - Audit log page for tracking system events - Admin settings page - Audit service for logging user actions Additional Features: - Quotes management pages and components - SalesBldr API integration - Email service for notifications Configuration & Documentation: - Updated docker-compose.yml - MCP server configuration (mcp.json) - CVE-2025-55182 security review documentation - Standards guide and PRD documents - Re-enabling authentication documentation Database Migrations: - 012: Auth tables (users, sessions, accounts, verifications) - 013: Role tables (roles, permissions, role_permissions, user_roles) - 014: Admin settings table UI Updates: - Updated dashboard layout - Enhanced app layout with auth integration
43 lines
2.2 KiB
SQL
43 lines
2.2 KiB
SQL
-- 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;
|