103 lines
2.6 KiB
SQL
103 lines
2.6 KiB
SQL
-- PostgreSQL schema for Velox
|
|
-- Use BIGINT for Discord snowflakes, INT for smaller counters, BOOLEAN properly.
|
|
|
|
CREATE TABLE IF NOT EXISTS levels (
|
|
guild BIGINT NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
level INT NOT NULL DEFAULT 0,
|
|
xp BIGINT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (guild, user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS twitch (
|
|
twitch_user TEXT NOT NULL,
|
|
guild_id BIGINT NOT NULL,
|
|
status TEXT NOT NULL DEFAULT 'not live',
|
|
PRIMARY KEY (twitch_user, guild_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS levelsettings (
|
|
guild_id BIGINT PRIMARY KEY,
|
|
levelsys BOOLEAN NOT NULL DEFAULT FALSE,
|
|
role BIGINT,
|
|
levelreq INT,
|
|
message TEXT
|
|
);
|
|
|
|
-- Role rewards per level (supports multiple rewards per guild)
|
|
CREATE TABLE IF NOT EXISTS levelrewards (
|
|
guild_id BIGINT NOT NULL,
|
|
levelreq INT NOT NULL,
|
|
role BIGINT NOT NULL,
|
|
PRIMARY KEY (guild_id, levelreq)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS welcome (
|
|
guild_id BIGINT PRIMARY KEY,
|
|
welcome_channel_id BIGINT,
|
|
welcome_message TEXT,
|
|
welcome_dm TEXT,
|
|
welcome_gif_url TEXT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS levelup (
|
|
guild_id BIGINT PRIMARY KEY,
|
|
levelup_channel_id BIGINT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS twitch_config (
|
|
guild_id BIGINT PRIMARY KEY,
|
|
twitch_channel_id BIGINT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS rps (
|
|
guild_id BIGINT NOT NULL,
|
|
user_id BIGINT NOT NULL,
|
|
score INT NOT NULL DEFAULT 0,
|
|
PRIMARY KEY (guild_id, user_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS defaultrole (
|
|
guild_id BIGINT PRIMARY KEY,
|
|
role_id BIGINT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS logsettings (
|
|
guild_id BIGINT PRIMARY KEY,
|
|
log_channel_id BIGINT,
|
|
is_enabled BOOLEAN NOT NULL DEFAULT FALSE
|
|
);
|
|
|
|
-- Meeting rooms (voice lobby -> auto-created temporary voice channels)
|
|
CREATE TABLE IF NOT EXISTS meeting_settings (
|
|
guild_id BIGINT PRIMARY KEY,
|
|
lobby_channel_id BIGINT
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS meeting_temp_channels (
|
|
guild_id BIGINT NOT NULL,
|
|
channel_id BIGINT NOT NULL,
|
|
created_by BIGINT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
PRIMARY KEY (guild_id, channel_id)
|
|
);
|
|
|
|
-- Projects & members
|
|
CREATE TABLE IF NOT EXISTS projects (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
guild_id BIGINT NOT NULL,
|
|
name TEXT NOT NULL,
|
|
description TEXT,
|
|
is_active BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_by BIGINT NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS project_members (
|
|
project_id BIGINT NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
|
|
user_id BIGINT NOT NULL,
|
|
is_creator BOOLEAN NOT NULL DEFAULT FALSE,
|
|
PRIMARY KEY (project_id, user_id)
|
|
);
|
|
|
|
-- |