model.puml

@startuml

User "1" -- "*" Membership
Membership "*" -- "1" Group
User "1" -- "0..1" PasswordCredential
User "1" -- "*" UserProfileValue
UserProfileValue "*" -- "1" UserProfileField

Role "1" -- "*" RolePermission
RolePermission "*" -- "1" Permission

Realm "*" -- "1" Application

Assign "*" -- "1" Role
Assign "*" -- "1" Realm
Assign "*" -- "1" Group

class User {
  account
  name
  email
}

class Group {
  name
  description
}

class Role {
  name
  description
}

class Permission {
  name
  description
}

class Realm {
  name
  description
  application_id
}

class Application {
  name
  description
  pass_to
}

class Realm {
  name
  description
}

class UserProfileValue {
  user_profile_value_id
  user_profile_field_id
  value
}

class UserProfileField {
  user_profile_field_id
  name
  json_name
  is_required
  is_identity
  regular_expression
  max_length
  min_length
  position
}

class PasswordCredential {
  password
  salt
  createdAt
}

class UserAction {
  actor
  action_id
}
note as N1
SELECT permissions.id
FROM permissions
JOIN role_permissions USING(permission_id)
JOIN roles USING(role_id)
JOIN assigns USING(role_id)
JOIN realms USING(realm_id)
JOIN groups USING(group_id)
JOIN memberships USING(group_id)
JOIN users USING(user_id)
WHERE realms.url like ?
WHERE users.account = ?
end note
@enduml