Database schema

@startuml

hide empty methods

left to right direction
!procedure $schema($name, $slug)
package "$name" as $slug <<Rectangle>>
!endprocedure

!procedure $table($name, $slug)
entity "<b>$name</b>" as $slug << (T, Orange) table >>
!endprocedure

!procedure $view($name, $slug)
entity "<b>$name</b>" as $slug << (V, Aquamarine) view >>
!endprocedure

!procedure $pk($name)
<color:#GoldenRod><&key></color> <b>$name</b>
!endprocedure

!procedure $fk($name)
<color:#Silver><&key></color> $name
!endprocedure

!procedure $column($name)
{field} <color:#White><&media-record></color> $name
!endprocedure


$schema("{tenant}_mod-roles-keycloak", "mod_roles_keycloak") {
  $table("capability", "capability") {
    $pk("id"): uuid
    $column("name"): text
    $column("description"): text
    $column("resource"): text
    $column("action"): capability_action
    $column("type"): capability_type
    $column("application_id"): text
    $column("folio_permission"): text
    $column("created_by"): uuid
    $column("created_date"): timestamp
    $column("updated_by"): uuid
    $column("updated_date"): timestamp
  }

  $table("capability_endpoint", "capability_endpoint") {
    $pk("capability_id"): uuid
    $pk("path"): text
    $pk("method"): http_method
  }

  $table("capability_set", "capability_set") {
    $pk("id"): uuid
    $column("name"): text
    $column("description"): text
    $column("resource"): text
    $column("created_by"): uuid
    $column("created_date"): timestamp
    $column("updated_by"): uuid
    $column("updated_date"): timestamp
    $column("action"): capability_action
    $column("type"): capability_type
    $column("application_id"): text
    $column("folio_permission"): text
  }

  $table("capability_set_capability", "capability_set_capability") {
    $pk("capability_set_id"): uuid
    $pk("capability_id"): uuid
  }

  $table("permission", "permission") {
    $pk("id"): uuid
    $column("name"): text
    $column("display_name"): text
    $column("description"): text
    $column("visible"): boolean
    $column("replaces"): text[]
    $column("sub_permissions"): text[]
  }

  $table("policy", "policy") {
    $pk("id"): uuid
    $column("name"): text
    $column("description"): text
    $column("type"): policy_type
    $column("repeat"): boolean
    $column("start"): timestamp
    $column("expires"): timestamp
    $column("day_of_month_start"): integer
    $column("day_of_month_end"): integer
    $column("month_start"): integer
    $column("month_end"): integer
    $column("hour_start"): integer
    $column("hour_end"): integer
    $column("minute_start"): integer
    $column("minute_end"): integer
    $column("logic"): logic_type
    $column("created_date"): timestamp
    $column("created_by"): uuid
    $column("updated_date"): timestamp
    $column("updated_by"): uuid
  }

  $table("policy_roles", "policy_roles") {
    $column("policy_id"): uuid
    $column("role_id"): uuid
    $column("required"): boolean
  }

  $table("policy_users", "policy_users") {
    $column("policy_id"): uuid
    $column("user_id"): uuid
  }

  $table("role", "role") {
    $pk("id"): uuid
    $column("name"): text
    $column("description"): text
    $column("created_by"): uuid
    $column("created_date"): timestamp
    $column("updated_by"): uuid
    $column("updated_date"): timestamp
  }

  $table("role_capability", "role_capability") {
    $pk("role_id"): uuid
    $pk("capability_id"): uuid
    $column("created_by"): uuid
    $column("created_date"): timestamp
    $column("updated_by"): uuid
    $column("updated_date"): timestamp
  }

  $table("role_capability_set", "role_capability_set") {
    $pk("role_id"): uuid
    $pk("capability_set_id"): uuid
    $column("created_by"): uuid
    $column("created_date"): timestamp
    $column("updated_by"): uuid
    $column("updated_date"): timestamp
  }

  $table("role_loadable", "role_loadable") {
    $pk("id"): uuid
    $column("type"): role_loadable_type
  }

  $table("role_loadable_permission", "role_loadable_permission") {
    $pk("role_loadable_id"): uuid
    $pk("folio_permission"): varchar255
    $column("capability_id"): uuid
    $column("capability_set_id"): uuid
    $column("created_by"): uuid
    $column("created_date"): timestamp
    $column("updated_by"): uuid
    $column("updated_date"): timestamp
  }

  $table("user_capability", "user_capability") {
    $pk("user_id"): uuid
    $pk("capability_id"): uuid
    $column("created_by"): uuid
    $column("created_date"): timestamp
    $column("updated_by"): uuid
    $column("updated_date"): timestamp
  }

  $table("user_capability_set", "user_capability_set") {
    $pk("user_id"): uuid
    $pk("capability_set_id"): uuid
    $column("created_by"): uuid
    $column("created_date"): timestamp
    $column("updated_by"): uuid
    $column("updated_date"): timestamp
  }

  $table("user_role", "user_role") {
    $pk("user_id"): uuid
    $pk("role_id"): uuid
    $column("created_by"): uuid
    $column("created_date"): timestamp
    $column("updated_by"): uuid
    $column("updated_date"): timestamp
  }
}

mod_roles_keycloak.capability_endpoint::capability_id                 }o--||    mod_roles_keycloak.capability::id
mod_roles_keycloak.capability_set_capability::capability_id           ||--||    mod_roles_keycloak.capability::id
mod_roles_keycloak.capability_set_capability::capability_set_id       ||--||    mod_roles_keycloak.capability_set::id
mod_roles_keycloak.policy_roles::policy_id                            }o--||    mod_roles_keycloak.policy::id
mod_roles_keycloak.policy_roles::role_id                              }o--||    mod_roles_keycloak.role::id
mod_roles_keycloak.policy_users::policy_id                            }o--||    mod_roles_keycloak.policy::id
mod_roles_keycloak.role_capability::capability_id                     }o--||    mod_roles_keycloak.capability::id
mod_roles_keycloak.role_capability::role_id                           }o--||    mod_roles_keycloak.role::id
mod_roles_keycloak.role_capability_set::capability_set_id             }o--||    mod_roles_keycloak.capability_set::id
mod_roles_keycloak.role_capability_set::role_id                       }o--||    mod_roles_keycloak.role::id
mod_roles_keycloak.role_loadable::id                                  }o--||    mod_roles_keycloak.role::id
mod_roles_keycloak.role_loadable_permission::capability_id            }o--||    mod_roles_keycloak.capability::id
mod_roles_keycloak.role_loadable_permission::capability_set_id        }o--||    mod_roles_keycloak.capability_set::id
mod_roles_keycloak.role_loadable_permission::role_loadable_id         }o--||    mod_roles_keycloak.role_loadable::id
mod_roles_keycloak.user_capability::capability_id                     }o--||    mod_roles_keycloak.capability::id
mod_roles_keycloak.user_capability_set::capability_set_id             }o--||    mod_roles_keycloak.capability_set::id
mod_roles_keycloak.user_role::role_id                                 }o--||    mod_roles_keycloak.role::id

@enduml