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` (
  -- Twitch ID
  `id` VARCHAR(255) NOT NULL UNIQUE PRIMARY KEY,

  -- Used for the user's canonical stream URL: <overrustle.com/username>
  `username` VARCHAR(255) NOT NULL UNIQUE,

  -- 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`)
);

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` (
  `id` INTEGER PRIMARY KEY,
  `channel` VARCHAR(255) NOT NULL,
  `service` VARCHAR(255) NOT NULL,
  `overrustle_id` VARCHAR(255) REFERENCES `users` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  `thumbnail` VARCHAR(255),
  `live` TINYINT(1) DEFAULT 0,
  `viewers` INTEGER DEFAULT 0,
  `created_at` DATETIME NOT NULL,
  `updated_at` DATETIME NOT NULL,
  UNIQUE (`id`),
  UNIQUE (`channel`, `service`)
);