Evan Carroll
6fb90e42c3
Now we have a concept of an avatar at the server, realm, and scene level
and we have the groundwork for a realm store. New uesrs no longer props,
they get a default avatar. New system supports gender
{male,female,neutral} and {child,adult}.
802 lines
34 KiB
PL/PgSQL
802 lines
34 KiB
PL/PgSQL
-- Chattyness Auth Schema Tables
|
|
-- PostgreSQL 18
|
|
--
|
|
-- User authentication, accounts, and identity management
|
|
|
|
\set ON_ERROR_STOP on
|
|
|
|
BEGIN;
|
|
|
|
-- =============================================================================
|
|
-- User Accounts
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
username auth.username NOT NULL,
|
|
email auth.email,
|
|
password_hash TEXT,
|
|
auth_provider auth.auth_provider NOT NULL DEFAULT 'local',
|
|
oauth_id TEXT,
|
|
|
|
display_name public.display_name NOT NULL,
|
|
bio TEXT,
|
|
avatar_url public.url,
|
|
|
|
-- User preferences for default avatar selection
|
|
birthday DATE,
|
|
gender_preference auth.gender_preference NOT NULL DEFAULT 'gender_neutral',
|
|
age_category auth.age_category NOT NULL DEFAULT 'adult',
|
|
|
|
reputation_tier server.reputation_tier NOT NULL DEFAULT 'member',
|
|
reputation_promoted_at TIMESTAMPTZ,
|
|
|
|
status auth.account_status NOT NULL DEFAULT 'active',
|
|
email_verified BOOLEAN NOT NULL DEFAULT false,
|
|
email_verified_at TIMESTAMPTZ,
|
|
force_pw_reset BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
last_seen_at TIMESTAMPTZ,
|
|
total_time_online_seconds BIGINT NOT NULL DEFAULT 0,
|
|
|
|
script_state JSONB NOT NULL DEFAULT '{}',
|
|
|
|
-- User tags for feature gating and access control
|
|
tags auth.user_tag[] NOT NULL DEFAULT '{}',
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_auth_users_username UNIQUE (username),
|
|
CONSTRAINT uq_auth_users_email UNIQUE (email),
|
|
CONSTRAINT uq_auth_users_oauth UNIQUE (auth_provider, oauth_id),
|
|
CONSTRAINT chk_auth_users_password_or_oauth_or_guest
|
|
CHECK (password_hash IS NOT NULL OR oauth_id IS NOT NULL OR 'guest' = ANY(tags))
|
|
);
|
|
|
|
COMMENT ON TABLE auth.users IS 'User accounts and authentication';
|
|
|
|
CREATE INDEX idx_auth_users_email ON auth.users (lower(email));
|
|
CREATE INDEX idx_auth_users_status ON auth.users (status);
|
|
CREATE INDEX idx_auth_users_reputation ON auth.users (reputation_tier);
|
|
CREATE INDEX idx_auth_users_last_seen ON auth.users (last_seen_at DESC NULLS LAST);
|
|
CREATE INDEX idx_auth_users_tags ON auth.users USING GIN (tags);
|
|
|
|
-- =============================================================================
|
|
-- User Sessions
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
token_hash TEXT NOT NULL,
|
|
user_agent TEXT,
|
|
ip_address INET,
|
|
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
last_activity_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_auth_sessions_token UNIQUE (token_hash)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.sessions IS 'Active user sessions';
|
|
|
|
CREATE INDEX idx_auth_sessions_user ON auth.sessions (user_id);
|
|
CREATE INDEX idx_auth_sessions_expires ON auth.sessions (expires_at);
|
|
|
|
-- =============================================================================
|
|
-- Tower Sessions (tower-sessions crate)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.tower_sessions (
|
|
id TEXT PRIMARY KEY,
|
|
data BYTEA NOT NULL,
|
|
expiry_date TIMESTAMPTZ NOT NULL
|
|
);
|
|
|
|
COMMENT ON TABLE auth.tower_sessions IS 'Session storage for tower-sessions crate';
|
|
|
|
CREATE INDEX idx_auth_tower_sessions_expiry ON auth.tower_sessions (expiry_date);
|
|
|
|
-- =============================================================================
|
|
-- Friends List
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.friendships (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
friend_a UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
friend_b UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
initiated_by UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
is_accepted BOOLEAN NOT NULL DEFAULT false,
|
|
accepted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_auth_friendships UNIQUE (friend_a, friend_b),
|
|
CONSTRAINT chk_auth_friendships_ordered CHECK (friend_a < friend_b),
|
|
CONSTRAINT chk_auth_friendships_initiator CHECK (initiated_by = friend_a OR initiated_by = friend_b)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.friendships IS 'Friend relationships (normalized: friend_a < friend_b)';
|
|
|
|
CREATE INDEX idx_auth_friendships_friend_a ON auth.friendships (friend_a);
|
|
CREATE INDEX idx_auth_friendships_friend_b ON auth.friendships (friend_b);
|
|
CREATE INDEX idx_auth_friendships_pending ON auth.friendships (is_accepted) WHERE is_accepted = false;
|
|
|
|
-- =============================================================================
|
|
-- Block List
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.blocks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
blocker_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
blocked_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
reason TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_auth_blocks UNIQUE (blocker_id, blocked_id),
|
|
CONSTRAINT chk_auth_blocks_not_self CHECK (blocker_id != blocked_id)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.blocks IS 'User block list';
|
|
|
|
CREATE INDEX idx_auth_blocks_blocker ON auth.blocks (blocker_id);
|
|
CREATE INDEX idx_auth_blocks_blocked ON auth.blocks (blocked_id);
|
|
|
|
-- =============================================================================
|
|
-- Mute List
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.mutes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
muter_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
muted_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
expires_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_auth_mutes UNIQUE (muter_id, muted_id),
|
|
CONSTRAINT chk_auth_mutes_not_self CHECK (muter_id != muted_id)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.mutes IS 'User mute list';
|
|
|
|
CREATE INDEX idx_auth_mutes_muter ON auth.mutes (muter_id);
|
|
CREATE INDEX idx_auth_mutes_muted ON auth.mutes (muted_id);
|
|
|
|
-- =============================================================================
|
|
-- Password Reset Tokens
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.password_resets (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
token_hash TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
used_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_auth_password_resets_token UNIQUE (token_hash)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.password_resets IS 'Password reset tokens';
|
|
|
|
CREATE INDEX idx_auth_password_resets_user ON auth.password_resets (user_id);
|
|
CREATE INDEX idx_auth_password_resets_expires ON auth.password_resets (expires_at) WHERE used_at IS NULL;
|
|
|
|
-- =============================================================================
|
|
-- Email Verification Tokens
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.email_verifications (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
email auth.email NOT NULL,
|
|
token_hash TEXT NOT NULL,
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
verified_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_auth_email_verifications_token UNIQUE (token_hash)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.email_verifications IS 'Email verification tokens';
|
|
|
|
CREATE INDEX idx_auth_email_verifications_user ON auth.email_verifications (user_id);
|
|
|
|
-- =============================================================================
|
|
-- User Scripts
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.user_scripts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
name public.nonempty_text NOT NULL,
|
|
slug public.slug NOT NULL,
|
|
description TEXT,
|
|
|
|
source TEXT NOT NULL,
|
|
config JSONB NOT NULL DEFAULT '{}',
|
|
state JSONB NOT NULL DEFAULT '{}',
|
|
|
|
is_enabled BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_auth_user_scripts_slug UNIQUE (user_id, slug)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.user_scripts IS 'Per-user Rhai scripts';
|
|
|
|
CREATE INDEX idx_auth_user_scripts_user ON auth.user_scripts (user_id);
|
|
CREATE INDEX idx_auth_user_scripts_enabled ON auth.user_scripts (user_id, is_enabled) WHERE is_enabled = true;
|
|
|
|
-- =============================================================================
|
|
-- Server Staff (created here since it references auth.users)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.staff (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
role server.server_role NOT NULL,
|
|
appointed_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
appointed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_server_staff_user UNIQUE (user_id)
|
|
);
|
|
|
|
COMMENT ON TABLE server.staff IS 'Server-level administrative staff';
|
|
|
|
-- =============================================================================
|
|
-- Server Prop Library (Global Props)
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.props (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
name public.nonempty_text NOT NULL,
|
|
slug public.slug NOT NULL,
|
|
description TEXT,
|
|
tags TEXT[] NOT NULL DEFAULT '{}',
|
|
|
|
asset_path public.asset_path NOT NULL,
|
|
thumbnail_path public.asset_path,
|
|
|
|
-- Default avatar positioning (content layer OR emotion layer, mutually exclusive)
|
|
default_layer server.avatar_layer,
|
|
default_emotion server.emotion_state,
|
|
default_position SMALLINT CHECK (default_position IS NULL OR default_position BETWEEN 0 AND 8),
|
|
|
|
is_unique BOOLEAN NOT NULL DEFAULT false,
|
|
is_transferable BOOLEAN NOT NULL DEFAULT true,
|
|
is_portable BOOLEAN NOT NULL DEFAULT true,
|
|
is_droppable BOOLEAN NOT NULL DEFAULT true,
|
|
is_public BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
available_from TIMESTAMPTZ,
|
|
available_until TIMESTAMPTZ,
|
|
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_server_props_slug UNIQUE (slug),
|
|
CONSTRAINT chk_server_props_availability CHECK (
|
|
available_from IS NULL OR available_until IS NULL OR available_from < available_until
|
|
),
|
|
-- Props can be: non-avatar (all NULL), content layer, OR emotion layer (mutually exclusive)
|
|
CONSTRAINT chk_server_props_positioning CHECK (
|
|
-- Nothing set (non-avatar prop)
|
|
(default_layer IS NULL AND default_emotion IS NULL AND default_position IS NULL) OR
|
|
-- Content layer prop (skin/clothes/accessories at position 0-8)
|
|
(default_layer IS NOT NULL AND default_emotion IS NULL AND default_position IS NOT NULL) OR
|
|
-- Emotion layer prop (neutral/happy/sad/etc at position 0-8)
|
|
(default_layer IS NULL AND default_emotion IS NOT NULL AND default_position IS NOT NULL)
|
|
)
|
|
);
|
|
|
|
COMMENT ON TABLE server.props IS 'Global prop library (64x64 pixels, center-anchored)';
|
|
|
|
CREATE INDEX idx_server_props_tags ON server.props USING GIN (tags);
|
|
CREATE INDEX idx_server_props_active ON server.props (is_active) WHERE is_active = true;
|
|
CREATE INDEX idx_server_props_public ON server.props (is_public) WHERE is_public = true;
|
|
|
|
COMMENT ON COLUMN server.props.is_public IS
|
|
'When true, prop appears in the public Server inventory tab. Uses filtered index idx_server_props_public.';
|
|
|
|
-- =============================================================================
|
|
-- Audio Library
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.audio (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
name public.nonempty_text NOT NULL,
|
|
slug public.slug NOT NULL,
|
|
description TEXT,
|
|
category server.audio_category NOT NULL,
|
|
tags TEXT[] NOT NULL DEFAULT '{}',
|
|
|
|
asset_path public.asset_path NOT NULL,
|
|
duration_seconds REAL NOT NULL CHECK (duration_seconds > 0),
|
|
is_loopable BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_server_audio_slug UNIQUE (slug)
|
|
);
|
|
|
|
COMMENT ON TABLE server.audio IS 'Global audio library';
|
|
|
|
CREATE INDEX idx_server_audio_category ON server.audio (category);
|
|
CREATE INDEX idx_server_audio_tags ON server.audio USING GIN (tags);
|
|
|
|
-- =============================================================================
|
|
-- Reserved Names
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.reserved_names (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
name TEXT NOT NULL,
|
|
name_type server.reserved_name_type NOT NULL,
|
|
reason TEXT,
|
|
|
|
reserved_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE server.reserved_names IS 'Names reserved from use by users';
|
|
|
|
CREATE UNIQUE INDEX uq_server_reserved_names ON server.reserved_names (lower(name), name_type);
|
|
|
|
-- =============================================================================
|
|
-- Server Scripts
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.scripts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
name public.nonempty_text NOT NULL,
|
|
slug public.slug NOT NULL,
|
|
description TEXT,
|
|
|
|
source TEXT NOT NULL,
|
|
config JSONB NOT NULL DEFAULT '{}',
|
|
state JSONB NOT NULL DEFAULT '{}',
|
|
|
|
is_enabled BOOLEAN NOT NULL DEFAULT true,
|
|
run_on_user_login BOOLEAN NOT NULL DEFAULT false,
|
|
run_on_user_logout BOOLEAN NOT NULL DEFAULT false,
|
|
run_on_registration BOOLEAN NOT NULL DEFAULT false,
|
|
run_on_server_shutdown BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_server_scripts_slug UNIQUE (slug)
|
|
);
|
|
|
|
COMMENT ON TABLE server.scripts IS 'Server-wide Rhai scripts';
|
|
|
|
CREATE INDEX idx_server_scripts_enabled ON server.scripts (is_enabled) WHERE is_enabled = true;
|
|
|
|
-- =============================================================================
|
|
-- User Inventory (moved from props.inventory)
|
|
-- =============================================================================
|
|
-- User inventory stores props owned by users (worn on avatar or in bag).
|
|
-- Inventory items reference a source: server prop, realm prop, or user upload.
|
|
-- The denormalized prop_name/prop_asset_path are cached at acquisition time.
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.inventory (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
-- Source of the prop (at least one required)
|
|
server_prop_id UUID REFERENCES server.props(id) ON DELETE SET NULL,
|
|
realm_prop_id UUID, -- FK added in 030_realm.sql
|
|
upload_id UUID, -- Future: user uploads
|
|
|
|
-- Denormalized display info (cached at acquisition)
|
|
prop_name TEXT NOT NULL,
|
|
prop_asset_path public.asset_path NOT NULL,
|
|
layer server.avatar_layer,
|
|
position SMALLINT CHECK (position IS NULL OR position BETWEEN 0 AND 8),
|
|
|
|
-- Provenance chain for tracking history
|
|
provenance JSONB NOT NULL DEFAULT '[]',
|
|
origin server.prop_origin NOT NULL,
|
|
|
|
-- Behavioral flags (cached from source at acquisition)
|
|
is_transferable BOOLEAN NOT NULL DEFAULT true,
|
|
is_portable BOOLEAN NOT NULL DEFAULT true,
|
|
is_droppable BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
-- At least one source must be present
|
|
CONSTRAINT chk_auth_inventory_has_source CHECK (
|
|
server_prop_id IS NOT NULL OR realm_prop_id IS NOT NULL OR upload_id IS NOT NULL
|
|
)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.inventory IS 'User-owned props (denormalized for performance)';
|
|
COMMENT ON COLUMN auth.inventory.provenance IS 'Array of {from_user, timestamp, method} objects';
|
|
|
|
CREATE INDEX idx_auth_inventory_user ON auth.inventory (user_id);
|
|
CREATE INDEX idx_auth_inventory_server_prop ON auth.inventory (server_prop_id)
|
|
WHERE server_prop_id IS NOT NULL;
|
|
CREATE INDEX idx_auth_inventory_realm_prop ON auth.inventory (realm_prop_id)
|
|
WHERE realm_prop_id IS NOT NULL;
|
|
|
|
-- =============================================================================
|
|
-- User Avatars (moved from props.avatars)
|
|
-- =============================================================================
|
|
-- Avatar configurations per user (up to 10 slots: 0-9).
|
|
-- Uses 15 columns per layer, 135 total for the 9-position grid system.
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.avatars (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
slot_number SMALLINT NOT NULL CHECK (slot_number >= 0 AND slot_number <= 9),
|
|
|
|
name public.display_name,
|
|
is_default BOOLEAN NOT NULL DEFAULT false,
|
|
last_emotion SMALLINT NOT NULL DEFAULT 0 CHECK (last_emotion >= 0 AND last_emotion <= 11),
|
|
|
|
-- Content layers: skin (3), clothes (3), accessories (3) = 9 layers x 9 positions = 81 potential slots
|
|
-- But we use 3 layers x 9 positions = 27 content slots
|
|
l_skin_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_skin_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_skin_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_skin_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_skin_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_skin_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_skin_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_skin_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_skin_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
l_clothes_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_clothes_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_clothes_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_clothes_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_clothes_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_clothes_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_clothes_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_clothes_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_clothes_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
l_accessories_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_accessories_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_accessories_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_accessories_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_accessories_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_accessories_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_accessories_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_accessories_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
l_accessories_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
-- Emotion layers: 12 emotions x 9 positions = 108 slots
|
|
e_neutral_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_neutral_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_neutral_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_neutral_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_neutral_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_neutral_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_neutral_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_neutral_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_neutral_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_happy_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_happy_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_happy_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_happy_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_happy_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_happy_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_happy_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_happy_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_happy_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_sad_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sad_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sad_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sad_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sad_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sad_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sad_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sad_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sad_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_angry_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_angry_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_angry_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_angry_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_angry_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_angry_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_angry_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_angry_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_angry_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_surprised_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_surprised_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_surprised_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_surprised_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_surprised_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_surprised_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_surprised_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_surprised_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_surprised_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_thinking_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_thinking_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_thinking_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_thinking_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_thinking_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_thinking_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_thinking_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_thinking_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_thinking_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_laughing_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_laughing_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_laughing_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_laughing_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_laughing_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_laughing_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_laughing_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_laughing_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_laughing_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_crying_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_crying_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_crying_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_crying_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_crying_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_crying_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_crying_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_crying_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_crying_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_love_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_love_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_love_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_love_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_love_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_love_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_love_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_love_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_love_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_confused_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_confused_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_confused_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_confused_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_confused_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_confused_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_confused_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_confused_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_confused_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_sleeping_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sleeping_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sleeping_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sleeping_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sleeping_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sleeping_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sleeping_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sleeping_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_sleeping_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
e_wink_0 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_wink_1 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_wink_2 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_wink_3 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_wink_4 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_wink_5 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_wink_6 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_wink_7 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
e_wink_8 UUID REFERENCES auth.inventory(id) ON DELETE SET NULL,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_auth_avatars_slot UNIQUE (user_id, slot_number)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.avatars IS 'User avatar configurations with 135 prop slots';
|
|
|
|
CREATE INDEX idx_auth_avatars_user ON auth.avatars (user_id);
|
|
CREATE INDEX idx_auth_avatars_default ON auth.avatars (user_id, is_default) WHERE is_default = true;
|
|
|
|
-- =============================================================================
|
|
-- Active Avatars (moved from props.active_avatars)
|
|
-- =============================================================================
|
|
-- Tracks which avatar a user is currently using in each realm.
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE auth.active_avatars (
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
realm_id UUID NOT NULL, -- FK added in 030_realm.sql after realm.realms exists
|
|
avatar_id UUID REFERENCES auth.avatars(id) ON DELETE SET NULL,
|
|
|
|
-- User-selected avatars from avatar stores (lower priority than custom avatar)
|
|
selected_server_avatar_id UUID, -- FK added in 025_server_avatars.sql
|
|
selected_realm_avatar_id UUID, -- FK added in 035_realm_avatars.sql
|
|
|
|
current_emotion server.emotion_state NOT NULL DEFAULT 'happy',
|
|
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
PRIMARY KEY (user_id, realm_id)
|
|
);
|
|
|
|
COMMENT ON TABLE auth.active_avatars IS 'Current avatar per user per realm';
|
|
COMMENT ON COLUMN auth.active_avatars.avatar_id IS
|
|
'User custom avatar (highest priority, nullable for users without custom avatars)';
|
|
COMMENT ON COLUMN auth.active_avatars.selected_server_avatar_id IS
|
|
'User-selected server avatar (from avatar store), lower priority than custom avatar';
|
|
COMMENT ON COLUMN auth.active_avatars.selected_realm_avatar_id IS
|
|
'User-selected realm avatar (from avatar store), higher priority than server selection';
|
|
|
|
-- =============================================================================
|
|
-- Server-Level Moderation: IP Bans
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.ip_bans (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
ip_address INET NOT NULL,
|
|
ip_range CIDR, -- Optional CIDR range for subnet bans
|
|
|
|
reason TEXT NOT NULL,
|
|
evidence JSONB NOT NULL DEFAULT '[]',
|
|
|
|
banned_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
expires_at TIMESTAMPTZ, -- NULL = permanent
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
|
|
CONSTRAINT uq_server_ip_bans_address UNIQUE (ip_address)
|
|
);
|
|
|
|
COMMENT ON TABLE server.ip_bans IS 'Server-wide IP address bans';
|
|
|
|
CREATE INDEX idx_server_ip_bans_range ON server.ip_bans USING GIST (ip_range inet_ops)
|
|
WHERE ip_range IS NOT NULL;
|
|
CREATE INDEX idx_server_ip_bans_expires ON server.ip_bans (expires_at)
|
|
WHERE expires_at IS NOT NULL;
|
|
|
|
-- =============================================================================
|
|
-- Server-Level Moderation: User Bans
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.bans (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
reason TEXT NOT NULL,
|
|
evidence JSONB NOT NULL DEFAULT '[]',
|
|
|
|
banned_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
expires_at TIMESTAMPTZ, -- NULL = permanent
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
unbanned_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
unbanned_at TIMESTAMPTZ,
|
|
unban_reason TEXT,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE server.bans IS 'Server-wide user bans';
|
|
|
|
CREATE INDEX idx_server_bans_user ON server.bans (user_id);
|
|
CREATE INDEX idx_server_bans_active ON server.bans (user_id, is_active) WHERE is_active = true;
|
|
CREATE INDEX idx_server_bans_expires ON server.bans (expires_at) WHERE expires_at IS NOT NULL AND is_active = true;
|
|
|
|
-- =============================================================================
|
|
-- Server-Level Moderation: Server Mutes
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.mutes (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
|
|
reason TEXT NOT NULL,
|
|
|
|
muted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
expires_at TIMESTAMPTZ, -- NULL = permanent
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
unmuted_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
unmuted_at TIMESTAMPTZ,
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE server.mutes IS 'Server-wide user mutes (cannot send messages anywhere)';
|
|
|
|
CREATE INDEX idx_server_mutes_user ON server.mutes (user_id);
|
|
CREATE INDEX idx_server_mutes_active ON server.mutes (user_id, is_active) WHERE is_active = true;
|
|
|
|
-- =============================================================================
|
|
-- Server-Level Moderation: Content Filters
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.content_filters (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
pattern TEXT NOT NULL,
|
|
is_regex BOOLEAN NOT NULL DEFAULT false,
|
|
is_case_sensitive BOOLEAN NOT NULL DEFAULT false,
|
|
|
|
action server.filter_action NOT NULL DEFAULT 'block',
|
|
replacement TEXT,
|
|
|
|
reason TEXT,
|
|
|
|
is_active BOOLEAN NOT NULL DEFAULT true,
|
|
|
|
created_by UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE server.content_filters IS 'Server-wide content filtering rules';
|
|
|
|
CREATE INDEX idx_server_content_filters_active ON server.content_filters (is_active) WHERE is_active = true;
|
|
|
|
-- =============================================================================
|
|
-- Server-Level Moderation: Action Log
|
|
-- =============================================================================
|
|
|
|
CREATE TABLE server.moderation_actions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
|
|
action_type server.action_type NOT NULL,
|
|
|
|
target_user_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
moderator_id UUID REFERENCES auth.users(id) ON DELETE SET NULL,
|
|
|
|
reason TEXT NOT NULL,
|
|
evidence JSONB NOT NULL DEFAULT '[]',
|
|
metadata JSONB NOT NULL DEFAULT '{}',
|
|
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
COMMENT ON TABLE server.moderation_actions IS 'Log of server-level moderation actions';
|
|
|
|
CREATE INDEX idx_server_moderation_actions_target ON server.moderation_actions (target_user_id);
|
|
CREATE INDEX idx_server_moderation_actions_moderator ON server.moderation_actions (moderator_id);
|
|
CREATE INDEX idx_server_moderation_actions_type ON server.moderation_actions (action_type);
|
|
CREATE INDEX idx_server_moderation_actions_created ON server.moderation_actions (created_at DESC);
|
|
|
|
COMMIT;
|