ERD

Tables (11)

devices

  • id — BIGINT — Primary key
  • institution_id — BIGINT — NOT NULL
  • firmware_version — VARCHAR(100)
  • location — VARCHAR(255)
  • status — VARCHAR(50)
  • created_at — DATETIME — NOT NULL

institutions

  • id — BIGINT — Primary key
  • name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(255)
  • created_at — DATETIME — NOT NULL

payment_histories

  • user_id — BIGINT
  • payment_method_id — BIGINT
  • amount — DECIMAL(10, 2) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • id — BIGINT

payment_methods

  • id — BIGINT — Primary key
  • id (id2) — BIGINT — Foreign key → users.id
  • user_id — BIGINT — NOT NULL
  • method_type — VARCHAR(50) — NOT NULL
  • pg_billing_key — VARCHAR(255) — NOT NULL
  • pg_customer_key — VARCHAR(255) — NOT NULL
  • card_nickname — VARCHAR(100)
  • last_4_digits — CHAR(4)
  • created_at — DATETIME — NOT NULL

permissions

  • id — BIGINT — Primary key
  • name — VARCHAR(100) — NOT NULL

reports

  • id — BIGINT — Primary key
  • reporter_user_id — BIGINT
  • auth_log_id — BIGINT
  • report_type — VARCHAR(100) — NOT NULL
  • description — TEXT
  • status — VARCHAR(50) — NOT NULL
  • created_at — DATETIME — NOT NULL

role_permissions

  • role_id — BIGINT — Primary key — NOT NULL
  • permission_id — BIGINT — Primary key — NOT NULL

roles

  • id — BIGINT — Primary key
  • name — VARCHAR(100) — NOT NULL

user_institution_roles

  • id — BIGINT — Primary key
  • user_id — BIGINT — NOT NULL
  • institution_id — BIGINT
  • role_id — BIGINT — NOT NULL
  • created_at — DATETIME — NOT NULL

user_institutions

  • id — BIGINT — Primary key
  • user_id — BIGINT — NOT NULL
  • institution_id — BIGINT — NOT NULL
  • student_id — VARCHAR(100)
  • created_at — DATETIME — NOT NULL

users

  • id — BIGINT — Primary key
  • email — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone_number — VARCHAR(20)
  • created_at — DATETIME — NOT NULL