581 lines
19 KiB
SQL
581 lines
19 KiB
SQL
-- Complete Database Schema for AeThex Bot
|
|
-- Run this in your Supabase SQL Editor to ensure all tables exist
|
|
|
|
-- ============ CORE TABLES ============
|
|
|
|
-- User Profiles - Global user data
|
|
CREATE TABLE IF NOT EXISTS user_profiles (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
username VARCHAR(255),
|
|
xp INTEGER DEFAULT 0,
|
|
prestige_level INTEGER DEFAULT 0,
|
|
total_xp_earned INTEGER DEFAULT 0,
|
|
daily_streak INTEGER DEFAULT 0,
|
|
last_daily TIMESTAMP WITH TIME ZONE,
|
|
title VARCHAR(255),
|
|
bio TEXT,
|
|
avatar_url VARCHAR(512),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Discord Links - Discord ↔ AeThex account links
|
|
CREATE TABLE IF NOT EXISTS discord_links (
|
|
id SERIAL PRIMARY KEY,
|
|
discord_id VARCHAR(32) UNIQUE NOT NULL,
|
|
user_id UUID REFERENCES user_profiles(id),
|
|
username VARCHAR(255),
|
|
linked_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Discord Verifications - Pending verification codes
|
|
CREATE TABLE IF NOT EXISTS discord_verifications (
|
|
id SERIAL PRIMARY KEY,
|
|
discord_id VARCHAR(32) UNIQUE NOT NULL,
|
|
verification_code VARCHAR(32) NOT NULL,
|
|
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- ============ SERVER CONFIG ============
|
|
|
|
-- Server Config - Per-server bot configuration
|
|
CREATE TABLE IF NOT EXISTS server_config (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) UNIQUE NOT NULL,
|
|
prefix VARCHAR(10) DEFAULT '!',
|
|
verified_role_id VARCHAR(32),
|
|
welcome_channel_id VARCHAR(32),
|
|
welcome_message TEXT,
|
|
goodbye_channel_id VARCHAR(32),
|
|
goodbye_message TEXT,
|
|
log_channel_id VARCHAR(32),
|
|
mod_log_channel_id VARCHAR(32),
|
|
starboard_channel_id VARCHAR(32),
|
|
starboard_threshold INTEGER DEFAULT 3,
|
|
level_up_channel_id VARCHAR(32),
|
|
level_up_message TEXT,
|
|
xp_mode VARCHAR(32) DEFAULT 'standalone',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- XP Config - Per-server XP settings
|
|
CREATE TABLE IF NOT EXISTS xp_config (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) UNIQUE NOT NULL,
|
|
xp_per_message INTEGER DEFAULT 15,
|
|
xp_cooldown INTEGER DEFAULT 60,
|
|
voice_xp_per_minute INTEGER DEFAULT 5,
|
|
reaction_xp INTEGER DEFAULT 5,
|
|
difficulty VARCHAR(32) DEFAULT 'normal',
|
|
bonus_channels JSONB DEFAULT '[]',
|
|
role_multipliers JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- ============ XP & LEVELING ============
|
|
|
|
-- Guild User XP - Per-server XP (standalone mode)
|
|
CREATE TABLE IF NOT EXISTS guild_user_xp (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
xp INTEGER DEFAULT 0,
|
|
level INTEGER DEFAULT 0,
|
|
prestige INTEGER DEFAULT 0,
|
|
messages INTEGER DEFAULT 0,
|
|
voice_minutes INTEGER DEFAULT 0,
|
|
last_xp_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(guild_id, user_id)
|
|
);
|
|
|
|
-- User Stats - Per-server activity statistics
|
|
CREATE TABLE IF NOT EXISTS user_stats (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID REFERENCES user_profiles(id),
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
messages_sent INTEGER DEFAULT 0,
|
|
reactions_given INTEGER DEFAULT 0,
|
|
reactions_received INTEGER DEFAULT 0,
|
|
voice_minutes INTEGER DEFAULT 0,
|
|
commands_used INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(user_id, guild_id)
|
|
);
|
|
|
|
-- Periodic XP - Weekly/monthly XP tracking
|
|
CREATE TABLE IF NOT EXISTS periodic_xp (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID REFERENCES user_profiles(id),
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
period_type VARCHAR(16) NOT NULL, -- 'weekly' or 'monthly'
|
|
period_start DATE NOT NULL,
|
|
xp_earned INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(user_id, guild_id, period_type, period_start)
|
|
);
|
|
|
|
-- Level Roles - Auto-assign roles at levels
|
|
CREATE TABLE IF NOT EXISTS level_roles (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
level INTEGER NOT NULL,
|
|
role_id VARCHAR(32) NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(guild_id, level)
|
|
);
|
|
|
|
-- ============ ACHIEVEMENTS & QUESTS ============
|
|
|
|
-- Achievements - Achievement definitions
|
|
CREATE TABLE IF NOT EXISTS achievements (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
icon VARCHAR(64),
|
|
trigger_type VARCHAR(32) NOT NULL, -- 'level', 'xp', 'messages', 'prestige', etc.
|
|
trigger_value INTEGER NOT NULL,
|
|
xp_reward INTEGER DEFAULT 0,
|
|
role_reward_id VARCHAR(32),
|
|
hidden BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- User Achievements - Earned achievements
|
|
CREATE TABLE IF NOT EXISTS user_achievements (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID REFERENCES user_profiles(id),
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
achievement_id INTEGER REFERENCES achievements(id),
|
|
earned_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(user_id, guild_id, achievement_id)
|
|
);
|
|
|
|
-- Quests - Quest definitions
|
|
CREATE TABLE IF NOT EXISTS quests (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
quest_type VARCHAR(32) DEFAULT 'daily', -- 'daily', 'weekly'
|
|
trigger_type VARCHAR(32) NOT NULL, -- 'messages', 'reactions', 'voice', etc.
|
|
target_value INTEGER NOT NULL,
|
|
xp_reward INTEGER DEFAULT 100,
|
|
active BOOLEAN DEFAULT true,
|
|
starts_at TIMESTAMP WITH TIME ZONE,
|
|
expires_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- User Quests - Quest progress
|
|
CREATE TABLE IF NOT EXISTS user_quests (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID REFERENCES user_profiles(id),
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
quest_id INTEGER REFERENCES quests(id),
|
|
progress INTEGER DEFAULT 0,
|
|
completed BOOLEAN DEFAULT false,
|
|
completed_at TIMESTAMP WITH TIME ZONE,
|
|
started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(user_id, guild_id, quest_id)
|
|
);
|
|
|
|
-- ============ SHOP SYSTEM ============
|
|
|
|
-- Shop Items - Item catalog
|
|
CREATE TABLE IF NOT EXISTS shop_items (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
price INTEGER NOT NULL,
|
|
item_type VARCHAR(32) NOT NULL, -- 'role', 'badge', 'boost', 'custom'
|
|
item_data JSONB DEFAULT '{}',
|
|
stock INTEGER, -- null = unlimited
|
|
enabled BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- User Inventory - Purchased items
|
|
CREATE TABLE IF NOT EXISTS user_inventory (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID REFERENCES user_profiles(id),
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
item_id INTEGER REFERENCES shop_items(id),
|
|
quantity INTEGER DEFAULT 1,
|
|
purchased_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
expires_at TIMESTAMP WITH TIME ZONE
|
|
);
|
|
|
|
-- ============ MODERATION ============
|
|
|
|
-- Warnings - User warnings
|
|
CREATE TABLE IF NOT EXISTS warnings (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
moderator_id VARCHAR(32) NOT NULL,
|
|
reason TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Mod Actions - Moderation action log
|
|
CREATE TABLE IF NOT EXISTS mod_actions (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
moderator_id VARCHAR(32) NOT NULL,
|
|
action VARCHAR(32) NOT NULL, -- 'warn', 'kick', 'ban', 'timeout', 'unban'
|
|
reason TEXT,
|
|
duration INTEGER, -- for timeouts, in seconds
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Automod Config - Auto-moderation settings
|
|
CREATE TABLE IF NOT EXISTS automod_config (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) UNIQUE NOT NULL,
|
|
link_filter BOOLEAN DEFAULT false,
|
|
link_action VARCHAR(32) DEFAULT 'delete',
|
|
spam_filter BOOLEAN DEFAULT false,
|
|
spam_threshold INTEGER DEFAULT 5,
|
|
spam_action VARCHAR(32) DEFAULT 'warn',
|
|
word_filter BOOLEAN DEFAULT false,
|
|
word_list JSONB DEFAULT '[]',
|
|
word_action VARCHAR(32) DEFAULT 'delete',
|
|
invite_filter BOOLEAN DEFAULT false,
|
|
invite_action VARCHAR(32) DEFAULT 'delete',
|
|
mention_limit INTEGER DEFAULT 10,
|
|
mention_action VARCHAR(32) DEFAULT 'warn',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- ============ COMMUNITY FEATURES ============
|
|
|
|
-- Giveaways - Active giveaways
|
|
CREATE TABLE IF NOT EXISTS giveaways (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
channel_id VARCHAR(32) NOT NULL,
|
|
message_id VARCHAR(32) NOT NULL,
|
|
host_id VARCHAR(32) NOT NULL,
|
|
prize VARCHAR(255) NOT NULL,
|
|
winner_count INTEGER DEFAULT 1,
|
|
ends_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
ended BOOLEAN DEFAULT false,
|
|
winners JSONB DEFAULT '[]',
|
|
requirements JSONB DEFAULT '{}',
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Role Panels - Button-based role selection
|
|
CREATE TABLE IF NOT EXISTS role_panels (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
channel_id VARCHAR(32) NOT NULL,
|
|
message_id VARCHAR(32),
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
roles JSONB DEFAULT '[]', -- [{role_id, label, emoji, style}]
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Scheduled Messages - Timed announcements
|
|
CREATE TABLE IF NOT EXISTS scheduled_messages (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
channel_id VARCHAR(32) NOT NULL,
|
|
content TEXT,
|
|
embed JSONB,
|
|
send_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
recurring VARCHAR(32), -- null, 'daily', 'weekly', 'monthly'
|
|
sent BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Tickets - Support tickets
|
|
CREATE TABLE IF NOT EXISTS tickets (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
channel_id VARCHAR(32) NOT NULL,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
status VARCHAR(32) DEFAULT 'open', -- 'open', 'closed'
|
|
subject VARCHAR(255),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
closed_at TIMESTAMP WITH TIME ZONE
|
|
);
|
|
|
|
-- Community Posts - Cross-platform posts
|
|
CREATE TABLE IF NOT EXISTS community_posts (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID REFERENCES user_profiles(id),
|
|
guild_id VARCHAR(32),
|
|
content TEXT NOT NULL,
|
|
likes INTEGER DEFAULT 0,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- ============ FEDERATION ============
|
|
|
|
-- Federation Servers - Member servers
|
|
CREATE TABLE IF NOT EXISTS federation_servers (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) UNIQUE NOT NULL,
|
|
guild_name VARCHAR(255),
|
|
guild_icon VARCHAR(512),
|
|
owner_id VARCHAR(32),
|
|
member_count INTEGER DEFAULT 0,
|
|
status VARCHAR(32) DEFAULT 'pending',
|
|
tier VARCHAR(32) DEFAULT 'free',
|
|
trust_level VARCHAR(32) DEFAULT 'bronze',
|
|
reputation_score INTEGER DEFAULT 0,
|
|
subscription_id VARCHAR(255),
|
|
subscription_status VARCHAR(32),
|
|
invite_code VARCHAR(32),
|
|
description TEXT,
|
|
joined_federation_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Federation Bans - Shared ban list
|
|
CREATE TABLE IF NOT EXISTS federation_bans (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
username VARCHAR(255),
|
|
reason TEXT,
|
|
evidence TEXT,
|
|
severity VARCHAR(32) DEFAULT 'low',
|
|
reported_by VARCHAR(32),
|
|
reported_by_guild_id VARCHAR(32),
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Federation Applications - Join applications
|
|
CREATE TABLE IF NOT EXISTS federation_applications (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) UNIQUE NOT NULL,
|
|
guild_name VARCHAR(255),
|
|
guild_icon VARCHAR(512),
|
|
admin_id VARCHAR(32),
|
|
member_count INTEGER DEFAULT 0,
|
|
reason TEXT,
|
|
description TEXT,
|
|
category VARCHAR(64),
|
|
status VARCHAR(32) DEFAULT 'pending',
|
|
reviewed_by VARCHAR(32),
|
|
reviewed_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Federation Mappings - Cross-server role mappings
|
|
CREATE TABLE IF NOT EXISTS federation_mappings (
|
|
id SERIAL PRIMARY KEY,
|
|
role_id VARCHAR(32) UNIQUE NOT NULL,
|
|
role_name VARCHAR(255),
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
federation_role VARCHAR(64),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Federation Featured - Premium featured slots
|
|
CREATE TABLE IF NOT EXISTS federation_featured (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) UNIQUE NOT NULL,
|
|
subscription_id VARCHAR(255),
|
|
active BOOLEAN DEFAULT true,
|
|
expires_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Federation Reputation - Server reputation metrics
|
|
CREATE TABLE IF NOT EXISTS federation_reputation (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) UNIQUE NOT NULL,
|
|
valid_reports INTEGER DEFAULT 0,
|
|
false_positives INTEGER DEFAULT 0,
|
|
total_reports INTEGER DEFAULT 0,
|
|
last_updated TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Federation Alerts - Alert log
|
|
CREATE TABLE IF NOT EXISTS federation_alerts (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
ban_id INTEGER,
|
|
action_taken VARCHAR(32),
|
|
auto_action BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- ============ MISC FEATURES ============
|
|
|
|
-- Command Cooldowns - Rate limiting
|
|
CREATE TABLE IF NOT EXISTS command_cooldowns (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
command VARCHAR(64) NOT NULL,
|
|
expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
UNIQUE(guild_id, user_id, command)
|
|
);
|
|
|
|
-- Command Logs - Usage tracking
|
|
CREATE TABLE IF NOT EXISTS command_logs (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
command VARCHAR(64) NOT NULL,
|
|
options JSONB,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Server Backups - Backup data
|
|
CREATE TABLE IF NOT EXISTS server_backups (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
name VARCHAR(100),
|
|
description TEXT,
|
|
backup_type VARCHAR(20) DEFAULT 'manual', -- manual, auto
|
|
backup_data JSONB NOT NULL,
|
|
roles_count INTEGER DEFAULT 0,
|
|
channels_count INTEGER DEFAULT 0,
|
|
size_bytes BIGINT DEFAULT 0,
|
|
created_by VARCHAR(32),
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_server_backups_guild ON server_backups(guild_id);
|
|
|
|
-- Backup Settings - Auto-backup config
|
|
CREATE TABLE IF NOT EXISTS backup_settings (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) UNIQUE NOT NULL,
|
|
auto_backup BOOLEAN DEFAULT false,
|
|
backup_interval INTEGER DEFAULT 24, -- hours
|
|
max_backups INTEGER DEFAULT 5,
|
|
last_backup_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Seasonal Events - Holiday/special events
|
|
CREATE TABLE IF NOT EXISTS seasonal_events (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
description TEXT,
|
|
start_date TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
end_date TIMESTAMP WITH TIME ZONE NOT NULL,
|
|
xp_multiplier DECIMAL(3,2) DEFAULT 1.00,
|
|
special_rewards JSONB DEFAULT '{}',
|
|
active BOOLEAN DEFAULT true,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- Discord Role Mappings - Platform role sync
|
|
CREATE TABLE IF NOT EXISTS discord_role_mappings (
|
|
id SERIAL PRIMARY KEY,
|
|
guild_id VARCHAR(32) NOT NULL,
|
|
discord_role_id VARCHAR(32) NOT NULL,
|
|
platform_role VARCHAR(64) NOT NULL,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(guild_id, discord_role_id)
|
|
);
|
|
|
|
-- Foundation Activity - Foundation contributions
|
|
CREATE TABLE IF NOT EXISTS foundation_activity (
|
|
id SERIAL PRIMARY KEY,
|
|
user_id UUID REFERENCES user_profiles(id),
|
|
activity_type VARCHAR(64) NOT NULL,
|
|
amount DECIMAL(12,2),
|
|
description TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- ============ LEADERBOARD VIEWS ============
|
|
|
|
-- Create indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_guild_user_xp_guild ON guild_user_xp(guild_id);
|
|
CREATE INDEX IF NOT EXISTS idx_guild_user_xp_xp ON guild_user_xp(xp DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_user_stats_guild ON user_stats(guild_id);
|
|
CREATE INDEX IF NOT EXISTS idx_periodic_xp_period ON periodic_xp(period_type, period_start);
|
|
CREATE INDEX IF NOT EXISTS idx_warnings_guild ON warnings(guild_id);
|
|
CREATE INDEX IF NOT EXISTS idx_mod_actions_guild ON mod_actions(guild_id);
|
|
-- Partial indexes (run after tables exist if needed)
|
|
-- CREATE INDEX IF NOT EXISTS idx_giveaways_ends ON giveaways(ends_at) WHERE ended = false;
|
|
-- CREATE INDEX IF NOT EXISTS idx_scheduled_messages_send ON scheduled_messages(send_at) WHERE sent = false;
|
|
|
|
-- ============ RLS POLICIES ============
|
|
|
|
-- Enable RLS on all tables
|
|
ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE discord_links ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE discord_verifications ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE server_config ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE xp_config ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE guild_user_xp ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE user_stats ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE periodic_xp ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE level_roles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE achievements ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE user_achievements ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE quests ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE user_quests ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE shop_items ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE user_inventory ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE warnings ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE mod_actions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE automod_config ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE giveaways ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE role_panels ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE scheduled_messages ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE tickets ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE community_posts ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE federation_servers ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE federation_bans ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE federation_applications ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE federation_mappings ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE federation_featured ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE federation_reputation ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE federation_alerts ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE command_cooldowns ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE command_logs ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE server_backups ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE backup_settings ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE seasonal_events ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE discord_role_mappings ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE foundation_activity ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Create permissive policies for service role
|
|
DO $$
|
|
DECLARE
|
|
t text;
|
|
tables text[] := ARRAY[
|
|
'user_profiles', 'discord_links', 'discord_verifications', 'server_config',
|
|
'xp_config', 'guild_user_xp', 'user_stats', 'periodic_xp', 'level_roles',
|
|
'achievements', 'user_achievements', 'quests', 'user_quests', 'shop_items',
|
|
'user_inventory', 'warnings', 'mod_actions', 'automod_config', 'giveaways',
|
|
'role_panels', 'scheduled_messages', 'tickets', 'community_posts',
|
|
'federation_servers', 'federation_bans', 'federation_applications',
|
|
'federation_mappings', 'federation_featured', 'federation_reputation',
|
|
'federation_alerts', 'command_cooldowns', 'command_logs', 'server_backups',
|
|
'backup_settings', 'seasonal_events', 'discord_role_mappings', 'foundation_activity'
|
|
];
|
|
BEGIN
|
|
FOREACH t IN ARRAY tables
|
|
LOOP
|
|
EXECUTE format('DROP POLICY IF EXISTS "Allow all for service role" ON %I', t);
|
|
EXECUTE format('CREATE POLICY "Allow all for service role" ON %I FOR ALL USING (true) WITH CHECK (true)', t);
|
|
END LOOP;
|
|
END $$;
|
|
|
|
-- Grant permissions
|
|
GRANT ALL ON ALL TABLES IN SCHEMA public TO service_role;
|
|
GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO service_role;
|