45 lines
2.2 KiB
SQL
45 lines
2.2 KiB
SQL
CREATE TABLE channels (
|
||
id BYTEA NOT NULL PRIMARY KEY CHECK(length(id) = 26),
|
||
guild_id BYTEA CHECK(length(guild_id) = 26), -- NULL for DMs & group DMs
|
||
parent_id BYTEA CHECK(length(parent_id) = 26), -- for thread categories / channel groups
|
||
|
||
-- Core identity & type
|
||
name TEXT NOT NULL CHECK (LENGTH(name) >= 1 AND LENGTH(name) <= 100),
|
||
channel_type INTEGER NOT NULL DEFAULT 0, -- 0 = text, 1 = voice, 2 = category, 5 = news, etc.
|
||
|
||
-- Permissions & visibility
|
||
position INTEGER NOT NULL DEFAULT 0,
|
||
permission_overwrites JSONB NOT NULL DEFAULT '[]'::jsonb, -- array of overwrites
|
||
rate_limit_per_user INTEGER NOT NULL DEFAULT 0, -- slowmode in seconds (0–21600)
|
||
|
||
-- NSFW & visibility flags
|
||
nsfw BOOLEAN NOT NULL DEFAULT FALSE,
|
||
loud BOOLEAN NOT NULL DEFAULT FALSE, -- voice channel: triggers notifications
|
||
|
||
-- Thread-specific fields (for threads spawned from messages)
|
||
thread_metadata JSONB, -- { "archived": bool, "auto_archive_duration": int, "archive_timestamp": timestamptz, "locked": bool }
|
||
member_count INTEGER NOT NULL DEFAULT 0, -- approximate member count (not real-time)
|
||
message_count INTEGER NOT NULL DEFAULT 0, -- cached message count (for UI previews)
|
||
thread_owner_id BYTEA CHECK(length(thread_owner_id) = 26),
|
||
|
||
-- System metadata
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
|
||
-- Topic (for news, text channels)
|
||
topic TEXT CHECK (LENGTH(COALESCE(topic, '')) <= 1024),
|
||
|
||
-- Voice-specific
|
||
user_limit INTEGER NOT NULL DEFAULT 0, -- 0 = no limit
|
||
region TEXT -- voice region (e.g., "us-west"), NULL = automatic
|
||
);
|
||
|
||
-- Critical indexes for common operations
|
||
CREATE INDEX idx_channels_guild_id ON channels (guild_id) WHERE guild_id IS NOT NULL;
|
||
CREATE INDEX idx_channels_guild_id_position ON channels (guild_id, position);
|
||
CREATE INDEX idx_channels_parent_id ON channels (parent_id) WHERE parent_id IS NOT NULL;
|
||
CREATE INDEX idx_channels_channel_type ON channels (channel_type);
|
||
CREATE UNIQUE INDEX uidx_channels_channel_pos ON channels (guild_id, position);
|
||
|
||
-- Optional: cover index for message listing (join + sort)
|
||
CREATE INDEX idx_channels_guild_id_created_at ON channels (guild_id, created_at) WHERE guild_id IS NOT NULL; |