[DM-02] Users table (hierarchical roles)

{
  "cost": {
    "input_tokens": 20,
    "estimated_usd": 0.042495,
    "output_tokens": 2829
  },
  "model": "claude-sonnet-4-20250514",
  "status": "success",
  "transport": "anthropic_api",
  "latency_ms": 33832,
  "response_text": "I'll help you create a Users table with hierarchical roles. Here's a comprehensive database design:\n\n## Database Schema\n\n```sql\n-- Users table with hierarchical roles\nCREATE TABLE users (\n    user_id SERIAL PRIMARY KEY,\n    username VARCHAR(50) UNIQUE NOT NULL,\n    email VARCHAR(255) UNIQUE NOT NULL,\n    password_hash VARCHAR(255) NOT NULL,\n    first_name VARCHAR(100) NOT NULL,\n    last_name VARCHAR(100) NOT NULL,\n    role_id INTEGER NOT NULL,\n    manager_id INTEGER,\n    department VARCHAR(100),\n    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended')),\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    last_login TIMESTAMP,\n    \n    CONSTRAINT fk_user_role FOREIGN KEY (role_id) REFERENCES roles(role_id),\n    CONSTRAINT fk_user_manager FOREIGN KEY (manager_id) REFERENCES users(user_id)\n);\n\n-- Roles table with hierarchy\nCREATE TABLE roles (\n    role_id SERIAL PRIMARY KEY,\n    role_name VARCHAR(50) UNIQUE NOT NULL,\n    role_description TEXT,\n    parent_role_id INTEGER,\n    hierarchy_level INTEGER NOT NULL DEFAULT 0,\n    is_active BOOLEAN DEFAULT TRUE,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    \n    CONSTRAINT fk_parent_role FOREIGN KEY (parent_role_id) REFERENCES roles(role_id)\n);\n\n-- Permissions table\nCREATE TABLE permissions (\n    permission_id SERIAL PRIMARY KEY,\n    permission_name VARCHAR(100) UNIQUE NOT NULL,\n    permission_description TEXT,\n    resource VARCHAR(50) NOT NULL,\n    action VARCHAR(50) NOT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Role permissions junction table\nCREATE TABLE role_permissions (\n    role_id INTEGER NOT NULL,\n    permission_id INTEGER NOT NULL,\n    granted_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    granted_by INTEGER,\n    \n    PRIMARY KEY (role_id, permission_id),\n    CONSTRAINT fk_role_perm_role FOREIGN KEY (role_id) REFERENCES roles(role_id) ON DELETE CASCADE,\n    CONSTRAINT fk_role_perm_permission FOREIGN KEY (permission_id) REFERENCES permissions(permission_id) ON DELETE CASCADE,\n    CONSTRAINT fk_granted_by FOREIGN KEY (granted_by) REFERENCES users(user_id)\n);\n\n-- User sessions table\nCREATE TABLE user_sessions (\n    session_id VARCHAR(255) PRIMARY KEY,\n    user_id INTEGER NOT NULL,\n    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,\n    expires_at TIMESTAMP NOT NULL,\n    ip_address INET,\n    user_agent TEXT,\n    \n    CONSTRAINT fk_session_user FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE\n);\n```\n\n## Indexes for Performance\n\n```sql\n-- Indexes for better query performance\nCREATE INDEX idx_users_role_id ON users(role_id);\nCREATE INDEX idx_users_manager_id ON users(manager_id);\nCREATE INDEX idx_users_email ON users(email);\nCREATE INDEX idx_users_status ON users(status);\nCREATE INDEX idx_roles_parent_id ON roles(parent_role_id);\nCREATE INDEX idx_roles_hierarchy_level ON roles(hierarchy_level);\nCREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id);\nCREATE INDEX idx_user_sessions_expires_at ON user_sessions(expires_at);\n```\n\n## Sample Data\n\n```sql\n-- Insert sample roles (hierarchical)\nINSERT INTO roles (role_name, role_description, parent_role_id, hierarchy_level) VALUES\n('Super Admin', 'System administrator with full access', NULL, 0),\n('Company Admin', 'Company-wide administrator', 1, 1),\n('Department Manager', 'Manages a specific department', 2, 2),\n('Team Lead', 'Leads a team within a department', 3, 3),\n('Senior Employee', 'Experienced employee with additional privileges', 4, 4),\n('Employee', 'Standard employee role', 5, 5),\n('Intern', 'Temporary employee with limited access', 6, 6);\n\n-- Insert sample permissions\nINSERT INTO permissions (permission_name, permission_description, resource, action) VALUES\n('user.create', 'Create new users', 'user', 'create'),\n('user.read', 'View user information', 'user', 'read'),\n('user.update', 'Update user information', 'user', 'update'),\n('user.delete', 'Delete users', 'user', 'delete'),\n('role.manage', 'Manage roles and permissions', 'role', 'manage'),\n('report.view', 'View reports', 'report', 'read'),\n('report.create', 'Create reports', 'report', 'create'),\n('system.admin', 'System administration', 'system', 'admin');\n\n-- Assign permissions to roles (hierarchical inheritance)\nINSERT INTO role_permissions (role_id, permission_id) VALUES\n-- Super Admin gets all permissions\n(1, 1), (1, 2), (1, 3), (1, 4), (1, 5), (1, 6), (1, 7), (1, 8),\n-- Company Admin\n(2, 1), (2, 2), (2, 3), (2, 6), (2, 7),\n-- Department Manager\n(3, 2), (3, 3), (3, 6), (3, 7),\n-- Team Lead\n(4, 2), (4, 6), (4, 7),\n-- Senior Employee\n(5, 2), (5, 6),\n-- Employee\n(6, 2), (6, 6),\n-- Intern\n(7, 2);\n\n-- Insert sample users\nINSERT INTO users (username, email, password_hash, first_name, last_name, role_id, manager_id, department) VALUES\n('admin', 'admin@company.com', '$2b$12$hash1', 'System', 'Administrator', 1, NULL, 'IT'),\n('john.doe', 'john.doe@company.com', '$2b$12$hash2', 'John', 'Doe', 2, 1, 'IT'),\n('jane.smith', 'jane.smith@company.com', '$2b$12$hash3', 'Jane', 'Smith', 3, 2, 'Sales'),\n('mike.wilson', 'mike.wilson@company.com', '$2b$12$hash4', 'Mike', 'Wilson', 4, 3, 'Sales'),\n('sarah.brown', 'sarah.brown@company.com', '$2b$12$hash5', 'Sarah', 'Brown', 5, 4, 'Sales'),\n('bob.johnson', 'bob.johnson@company.com', '$2b$12$hash6', 'Bob', 'Johnson', 6, 5, 'Sales');\n```\n\n## Useful Queries\n\n```sql\n-- 1. Get user with role information\nSELECT \n    u.user_id,\n    u.username,\n    u.email,\n    u.first_name,\n    u.last_name,\n    r.role_name,\n    r.hierarchy_level,\n    m.username as manager_username\nFROM users u\nJOIN roles r ON u.role_id = r.role_id\nLEFT JOIN users m ON u.manager_id = m.user_id\nWHERE u.status = 'active';\n\n-- 2. Get all subordinates of a user (recursive)\nWITH RECURSIVE subordinates AS (\n    -- Base case: direct reports\n    SELECT user_id, username, first_name, last_name, manager_id, 1 as level\n    FROM users \n    WHERE manager_id = $1  -- Replace $1 with manager's user_id\n    \n    UNION ALL\n    \n    -- Recursive case: reports of reports\n    SELECT u.user_id, u.username, u.first_name, u.last_name, u.manager_id, s.level + 1\n    FROM users u\n    INNER JOIN subordinates s ON u.manager_id = s.user_id\n)\nSELECT * FROM subordinates ORDER BY level, username;\n\n-- 3. Get user permissions (including inherited from role hierarchy)\nWITH RECURSIVE role_hierarchy AS (\n    -- Start with user's role\n    SELECT r.role_id, r.role_name, r.parent_role_id, 0 as level\n    FROM roles r\n    JOIN users u ON u.role_id = r.role_id\n    WHERE u.user_id = $1  -- Replace $1 with user_id\n    \n    UNION ALL\n    \n    -- Include parent roles\n    SELECT r.role_id, r.role_name, r.parent_role_id, rh.level + 1\n    FROM roles r\n    JOIN role_hierarchy rh ON r.role_id = rh.parent_role_id\n)\nSELECT DISTINCT p.permission_name, p.resource, p.action\nFROM role_hierarchy rh\nJOIN role_permissions rp ON rh.role_id = rp.role_id\nJOIN permissions p ON rp.permission_id = p.permission_id\nORDER BY p.resource, p.action;\n\n-- 4. Get organizational chart\nWITH RECURSIVE org_chart AS (\n    -- Start with top-level managers (no manager)\n    SELECT user_id, username, first_name, last_name, manager_id, \n           CAST(username AS VARCHAR(1000)) as path, 0 as level\n    FROM users \n    WHERE manager_id IS NULL\n    \n    UNION ALL\n    \n    -- Add direct reports\n    SELECT u.user_id, u.username, u.first_name, u.last_name, u.manager_id,\n           CAST(oc.path || ' -> ' || u.username AS VARCHAR(1000)), oc.level + 1\n    FROM users u\n    JOIN org_chart oc ON u.manager_id = oc.user_id\n)\nSELECT user_id, username, first_name, last_name, level, path\nFROM org_chart \nORDER BY level, username;\n\n-- 5. Check if user has specific permission\nSELECT EXISTS(\n    WITH RECURSIVE role_hierarchy AS (\n        SELECT r.role_id FROM roles r\n        JOIN users u ON u.role_id = r.role_id\n        WHERE u.user_id = $1  -- user_id parameter\n        \n        UNION ALL\n        \n        SELECT r.role_id FROM roles r\n        JOIN role_hierarchy rh ON r.role_id = rh.parent_role_id\n    )\n    SELECT 1 FROM role_hierarchy rh\n    JOIN role_permissions rp ON rh.role_id = rp.role_id\n    JOIN permissions p ON rp.permission_id = p.permission_id\n    WHERE p.permission_name = $2  -- permission_name parameter\n) as has_permission;\n```\n\n## Key Features\n\n1. **Hierarchical Roles**: Roles can inherit from parent roles\n2. **Management Hierarchy**: Users can have managers, creating org structure\n3. **Flexible Permissions**: Fine-grained permission system\n4. **Role Inheritance**: Users inherit permissions from role hierarchy\n5. **Session Management**: Track user sessions and login activity\n6. **Audit Trail**: Track when permissions were granted and by whom\n\nThis design allows for complex organizational structures while maintaining flexibility and security through the hierarchical role and permission system."
}