164 lines
6.5 KiB
PL/PgSQL
164 lines
6.5 KiB
PL/PgSQL
-- Users Table Migration
|
|
-- Core user authentication and profile data
|
|
|
|
-- Create users table
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email VARCHAR(255) NOT NULL UNIQUE,
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
username VARCHAR(50) UNIQUE,
|
|
display_name VARCHAR(100),
|
|
avatar_url TEXT,
|
|
verified_domain VARCHAR(255),
|
|
is_premium BOOLEAN DEFAULT false,
|
|
premium_tier VARCHAR(20) CHECK (premium_tier IN ('free', 'premium', 'enterprise')) DEFAULT 'free',
|
|
premium_expires_at TIMESTAMP,
|
|
status VARCHAR(20) CHECK (status IN ('online', 'away', 'dnd', 'offline')) DEFAULT 'offline',
|
|
custom_status VARCHAR(128),
|
|
last_login TIMESTAMP,
|
|
email_verified BOOLEAN DEFAULT false,
|
|
email_verified_at TIMESTAMP,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
-- Indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_users_email ON users(email);
|
|
CREATE INDEX IF NOT EXISTS idx_users_username ON users(username);
|
|
CREATE INDEX IF NOT EXISTS idx_users_verified_domain ON users(verified_domain) WHERE verified_domain IS NOT NULL;
|
|
CREATE INDEX IF NOT EXISTS idx_users_status ON users(status);
|
|
|
|
-- Function to update updated_at timestamp
|
|
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ language 'plpgsql';
|
|
|
|
-- Trigger to auto-update updated_at
|
|
DROP TRIGGER IF EXISTS update_users_updated_at ON users;
|
|
CREATE TRIGGER update_users_updated_at
|
|
BEFORE UPDATE ON users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at_column();
|
|
|
|
-- Friends/Relationships table
|
|
CREATE TABLE IF NOT EXISTS user_relationships (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
target_user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
relationship_type VARCHAR(20) CHECK (relationship_type IN ('friend', 'pending', 'blocked')) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW(),
|
|
CONSTRAINT unique_relationship UNIQUE(user_id, target_user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_relationships_user_id ON user_relationships(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_user_relationships_target_user_id ON user_relationships(target_user_id);
|
|
|
|
-- Servers/Guilds table
|
|
CREATE TABLE IF NOT EXISTS servers (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
icon_url TEXT,
|
|
banner_url TEXT,
|
|
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
invite_code VARCHAR(20) UNIQUE,
|
|
is_public BOOLEAN DEFAULT false,
|
|
member_count INTEGER DEFAULT 1,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_servers_owner_id ON servers(owner_id);
|
|
CREATE INDEX IF NOT EXISTS idx_servers_invite_code ON servers(invite_code);
|
|
|
|
-- Server Members table
|
|
CREATE TABLE IF NOT EXISTS server_members (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
server_id UUID NOT NULL REFERENCES servers(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
nickname VARCHAR(100),
|
|
role VARCHAR(20) CHECK (role IN ('owner', 'admin', 'moderator', 'member')) DEFAULT 'member',
|
|
joined_at TIMESTAMP DEFAULT NOW(),
|
|
CONSTRAINT unique_server_member UNIQUE(server_id, user_id)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_server_members_server_id ON server_members(server_id);
|
|
CREATE INDEX IF NOT EXISTS idx_server_members_user_id ON server_members(user_id);
|
|
|
|
-- Channels table
|
|
CREATE TABLE IF NOT EXISTS channels (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
server_id UUID REFERENCES servers(id) ON DELETE CASCADE,
|
|
name VARCHAR(100) NOT NULL,
|
|
description TEXT,
|
|
channel_type VARCHAR(20) CHECK (channel_type IN ('text', 'voice', 'announcement', 'stage')) DEFAULT 'text',
|
|
position INTEGER DEFAULT 0,
|
|
is_private BOOLEAN DEFAULT false,
|
|
slowmode_seconds INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_channels_server_id ON channels(server_id);
|
|
|
|
-- Messages table
|
|
CREATE TABLE IF NOT EXISTS messages (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
channel_id UUID REFERENCES channels(id) ON DELETE CASCADE,
|
|
conversation_id UUID,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
content TEXT NOT NULL,
|
|
message_type VARCHAR(20) CHECK (message_type IN ('text', 'image', 'file', 'system')) DEFAULT 'text',
|
|
attachments JSONB DEFAULT '[]',
|
|
is_edited BOOLEAN DEFAULT false,
|
|
is_pinned BOOLEAN DEFAULT false,
|
|
reply_to_id UUID REFERENCES messages(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
updated_at TIMESTAMP DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_messages_channel_id ON messages(channel_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_conversation_id ON messages(conversation_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_user_id ON messages(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_messages_created_at ON messages(created_at DESC);
|
|
|
|
-- Message Reactions table
|
|
CREATE TABLE IF NOT EXISTS message_reactions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
message_id UUID NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
emoji VARCHAR(50) NOT NULL,
|
|
created_at TIMESTAMP DEFAULT NOW(),
|
|
CONSTRAINT unique_reaction UNIQUE(message_id, user_id, emoji)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_message_reactions_message_id ON message_reactions(message_id);
|
|
|
|
-- Voice Channel Sessions table
|
|
CREATE TABLE IF NOT EXISTS voice_sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
channel_id UUID NOT NULL REFERENCES channels(id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
joined_at TIMESTAMP DEFAULT NOW(),
|
|
left_at TIMESTAMP,
|
|
is_muted BOOLEAN DEFAULT false,
|
|
is_deafened BOOLEAN DEFAULT false,
|
|
is_streaming BOOLEAN DEFAULT false,
|
|
is_video_on BOOLEAN DEFAULT false
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_voice_sessions_channel_id ON voice_sessions(channel_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_sessions_user_id ON voice_sessions(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_voice_sessions_active ON voice_sessions(channel_id) WHERE left_at IS NULL;
|
|
|
|
-- Comments
|
|
COMMENT ON TABLE users IS 'Core user accounts for AeThex Connect';
|
|
COMMENT ON TABLE servers IS 'Discord-style servers/guilds';
|
|
COMMENT ON TABLE channels IS 'Text and voice channels within servers';
|
|
COMMENT ON TABLE messages IS 'Chat messages in channels or DMs';
|
|
COMMENT ON TABLE voice_sessions IS 'Active voice channel participants';
|