# DS 25 — Cerberus

Roles and permissions model. Only applies if the instance has `cerberus_enabled = true` in FeatureFlags (DS 1). Otherwise, permissions are in Capabilities/UserCapabilities in DS 1.

## Main tables

### permissions

Catalog of all available permissions in the system.

| Column | Description |
|--------|-------------|
| `id` | uuid PK |
| `code` | Permission code (e.g.: `can_edit_users`) |
| `title` | Human-readable name |
| `description` | Description |
| `type` | Permission type |
| `module` | Which module it belongs to |
| `origin` | Permission origin |
| `availability` | Availability |
| `allowed_role_types` | ARRAY — role types that can have this permission |
| `deleted_at` | Soft delete |

### roles

Role definitions per instance.

| Column | Description |
|--------|-------------|
| `id` | uuid PK |
| `instance_id` | FK to Instances |
| `name` | Role name (e.g.: "HR Admin") |
| `description` | Description |
| `type` | Role type |
| `state` | State (e.g.: active) |
| `assignment_rules` | jsonb — automatic assignment rules |
| `item_ids_assignment_rules` | ARRAY — segmentation item IDs for assignment |
| `user_ids_assignment_rules` | ARRAY — user IDs with direct assignment |
| `specific_permissions_target_rules` | jsonb — specific permission target rules |
| `deleted_at` | Soft delete |
| `created_by` / `updated_by` | Who created/modified |

### role_permissions

Permission-to-role assignment.

| Column | Description |
|--------|-------------|
| `role_id` | uuid FK to roles |
| `permission_id` | uuid FK to permissions |

### user_roles

User-to-role assignment.

| Column | Description |
|--------|-------------|
| `user_id` | FK to Users (DS 1) |
| `role_id` | uuid FK to roles |

## Useful queries

### Instance roles with user count

```sql
SELECT r.name, r.type, r.state,
  COUNT(ur.user_id) AS assigned_users
FROM roles r
LEFT JOIN user_roles ur ON ur.role_id = r.id
WHERE r.instance_id = :instance_id AND r.deleted_at IS NULL
GROUP BY r.id, r.name, r.type, r.state
ORDER BY assigned_users DESC
```

### Specific user's permissions

```sql
SELECT r.name AS role, p.code AS permission, p.module
FROM user_roles ur
JOIN roles r ON r.id = ur.role_id
JOIN role_permissions rp ON rp.role_id = r.id
JOIN permissions p ON p.id = rp.permission_id
WHERE ur.user_id = :user_id
  AND r.deleted_at IS NULL
ORDER BY r.name, p.module, p.code
```

### Users with privileged permissions (admins)

"Admin" in Cerberus = users with roles that have management permissions (`can_edit_users`, `can_manage_*`, etc.):

```sql
SELECT DISTINCT ur.user_id, r.name AS role
FROM user_roles ur
JOIN roles r ON r.id = ur.role_id
JOIN role_permissions rp ON rp.role_id = r.id
JOIN permissions p ON p.id = rp.permission_id
WHERE r.instance_id = :instance_id
  AND r.deleted_at IS NULL
  AND (p.code LIKE 'can_edit_%' OR p.code LIKE 'can_manage_%' OR p.code = 'can_create_users')
ORDER BY r.name, ur.user_id
```

## Audit tables

- `roles_aud` / `role_permissions_aud`: change history for roles and permissions (audit trail)
- `revinfo`: metadata for each audit revision
