db.dbml

Project simple_bank {
  database_type: 'PostgreSQL'
  Note: '''
    # Simple Bank Database
  '''
}

Table users as U {
  username varchar [pk]
  role varchar [not null, default: 'depositor']
  hashed_password varchar [not null]
  full_name varchar [not null]
  email varchar [unique, not null]
  is_email_verified bool [not null, default: false]
  password_changed_at timestamptz [not null, default: '0001-01-01']
  created_at timestamptz [not null, default: `now()`]
}

Table verify_emails {
  id bigserial [pk]
  username varchar [ref: > U.username, not null]
  email varchar [not null]
  secret_code varchar [not null]
  is_used bool [not null, default: false]
  created_at timestamptz [not null, default: `now()`]
  expired_at timestamptz [not null, default: `now() + interval '15 minutes'`]
}

Table accounts as A {
  id bigserial [pk]
  owner varchar [ref: > U.username, not null]
  balance bigint [not null]
  currency varchar [not null]
  created_at timestamptz [not null, default: `now()`]
  
  Indexes {
    owner
    (owner, currency) [unique]
  }
}

Table entries {
  id bigserial [pk]
  account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'can be negative or positive']
  created_at timestamptz [not null, default: `now()`]
  
  Indexes {
    account_id
  }
}

Table transfers {
  id bigserial [pk]
  from_account_id bigint [ref: > A.id, not null]
  to_account_id bigint [ref: > A.id, not null]
  amount bigint [not null, note: 'must be positive']
  created_at timestamptz [not null, default: `now()`]
  
  Indexes {
    from_account_id
    to_account_id
    (from_account_id, to_account_id)
  }
}

Table sessions {
  id uuid [pk]
  username varchar [ref: > U.username, not null]
  refresh_token varchar [not null]
  user_agent varchar [not null]
  client_ip varchar [not null]
  is_blocked boolean [not null, default: false]
  expires_at timestamptz [not null]
  created_at timestamptz [not null, default: `now()`]
}