Database

This page describes the database schema used by the Rustla2 backend.

Users

The "users" table contains all of the registered users.

CREATE TABLE IF NOT EXISTS `users` (
  -- User ID
  `id` CHAR(36) NOT NULL,

  -- Twitch ID
  `twitch_id` UNSIGNED BIGINT NOT NULL,

  -- The display name used in chat
  `name` VARCHAR(32) NOT NULL,

  -- Used for the user's canonical stream URL: <strims.gg/stream_path>
  `stream_path` VARCHAR(255) NOT NULL,

  -- The streaming service to use for this user's stream
  `service` VARCHAR(255) NOT NULL,

  -- This user's channel name within the above service
  `channel` VARCHAR(255) NOT NULL,

  -- The last IP address that this user logged in from.
  `last_ip` VARCHAR(255) NOT NULL,

  -- The date of last login for this user.
  `last_seen` DATETIME NOT NULL,

  -- 1 if the user prefers the chat on the left side of the screen, otherwise 0
  `left_chat` TINYINT(1) DEFAULT 0,

  -- 1 if the user is banned, otherwise 0
  `is_banned` TINYINT(1) NOT NULL DEFAULT 0,

  -- The reason for this user's ban, if any
  `ban_reason` VARCHAR(255),

  -- When this user was created
  `created_at` DATETIME NOT NULL,

  -- The last time this user was updated.
  `updated_at` DATETIME NOT NULL,

  -- 1 if the user is an admin, otherwise 0
  `is_admin` TINYINT(1) DEFAULT 0,

  UNIQUE (`id`),
  UNIQUE (`twitch_id`),
  UNIQUE (`stream_path`),
  UNIQUE (`name`)
);

Streams

Contains the streams which will be displayed on the front page. That is, any stream which has at least one viewer.

CREATE TABLE IF NOT EXISTS `streams` (
  -- Stream ID
  `id` INTEGER PRIMARY KEY,

  -- The streaming service to use for this stream
  `service` VARCHAR(255) NOT NULL,

  -- This user's channel name within the above service
  `channel` VARCHAR(255) NOT NULL,

  -- Used for the user's canonical stream URL: <strims.gg/username>
  `path` VARCHAR(255) REFERENCES `users` (`stream_path`) ON DELETE SET NULL ON UPDATE CASCADE,

  -- Used by an admin to hide a stream from the front page
  `hidden` TINYINT(1) DEFAULT 0,

  -- Used by an admin to set a stream as afk
  `afk` TINYINT(1) DEFAULT 0,

  -- Used by an admin to promote a stream on the front page
  `promoted` TINYINT(1) DEFAULT 0,

  -- Title returned by the streaming service
  `title` VARCHAR(255) NOT NULL,

  -- Thumbnail image URL provided by the streaming service
  `thumbnail` VARCHAR(255),

  -- 1 if reported online by the streaming service
  `live` TINYINT(1) DEFAULT 0,

  -- Number of viewers reported by the streaming service
  `viewers` INTEGER DEFAULT 0,

  -- When this stream was first accessed
  `created_at` DATETIME NOT NULL,

  -- The last time this stream was updated
  `updated_at` DATETIME NOT NULL,

  UNIQUE (`id`),
  UNIQUE (`channel`, `service`, `path`)
);