Database schema

Welcome to the keizer-auth db-schema design!

copy the below table schema code and paste in dbdiagram.io to visualize it.

Table organizations {
  id uuid [pk, default: `gen_random_uuid()`]
  name varchar [not null]
  slug varchar [not null, unique]
  settings jsonb
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  updated_at timestamp [default: `CURRENT_TIMESTAMP`]
}

Table users {
  id uuid [pk, default: `gen_random_uuid()`]
  org_id uuid [ref: > organizations.id, not null]
  email varchar [not null]
  username varchar
  password_hash varchar [not null]
  full_name varchar
  is_active boolean [default: true]
  is_verified boolean [default: false]
  metadata jsonb
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  updated_at timestamp [default: `CURRENT_TIMESTAMP`]
  last_login timestamp
  
  indexes {
    (org_id, email) [unique]
    (org_id, username) [unique]
  }
}

Table roles {
  id uuid [pk, default: `gen_random_uuid()`]
  org_id uuid [ref: > organizations.id, not null]
  name varchar [not null]
  description text
  is_system_role boolean [default: false]
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (org_id, name) [unique]
  }
}

Table user_roles {
  user_id uuid [ref: > users.id, not null]
  role_id uuid [ref: > roles.id, not null]
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (user_id, role_id) [pk]
  }
}

Table permissions {
  id uuid [pk, default: `gen_random_uuid()`]
  org_id uuid [ref: > organizations.id, not null]
  name varchar [not null]
  description text
  resource varchar [not null]
  action varchar [not null]
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (org_id, name) [unique]
  }
}

Table role_permissions {
  role_id uuid [ref: > roles.id, not null]
  permission_id uuid [ref: > permissions.id, not null]
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (role_id, permission_id) [pk]
  }
}

Table sessions {
  id uuid [pk, default: `gen_random_uuid()`]
  user_id uuid [ref: > users.id]
  token varchar [not null, unique]
  ip_address inet
  user_agent text
  is_valid boolean [default: true]
  expires_at timestamp [not null]
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  last_used_at timestamp [default: `CURRENT_TIMESTAMP`]
}

Table verification_tokens {
  id uuid [pk, default: `gen_random_uuid()`]
  org_id uuid [ref: > organizations.id]
  user_id uuid [ref: > users.id]
  token varchar [not null, unique]
  type varchar [not null, note: 'email_verification, password_reset, etc']
  expires_at timestamp [not null]
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  used_at timestamp
}

Table oauth_providers {
  id uuid [pk, default: `gen_random_uuid()`]
  org_id uuid [ref: > organizations.id]
  name varchar [not null]
  client_id varchar [not null]
  client_secret varchar [not null]
  settings jsonb
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (org_id, name) [unique]
  }
}

Table oauth_accounts {
  id uuid [pk, default: `gen_random_uuid()`]
  user_id uuid [ref: > users.id]
  provider_id uuid [ref: > oauth_providers.id]
  provider_user_id varchar [not null]
  access_token text
  refresh_token text
  expires_at timestamp
  created_at timestamp [default: `CURRENT_TIMESTAMP`]
  
  indexes {
    (provider_id, provider_user_id) [unique]
  }
}