coworkunion/chattyness
SHA256
1
0
Fork 0
Code Issues Pull requests Projects Releases Packages Wiki Activity Actions
chattyness/db/schema/tables/020_auth.sql

831 lines
36 KiB
PL/PgSQL
Raw Blame History

-- 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),
-- Default scale factor for dropped props (10% - 1000%)
default_scale REAL NOT NULL DEFAULT 1.0 CHECK (default_scale >= 0.1 AND default_scale <= 10.0),
-- Optional JSON Schema for validating state structure
state_schema JSONB,
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)';
COMMENT ON COLUMN server.props.state_schema IS 'Optional JSON Schema defining valid state structure for this prop';
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,
-- Public state columns (persist through transfer)
server_state JSONB NOT NULL DEFAULT '{}',
realm_state JSONB NOT NULL DEFAULT '{}',
user_state JSONB NOT NULL DEFAULT '{}',
-- Private state columns (cleared on transfer)
server_private_state JSONB NOT NULL DEFAULT '{}',
realm_private_state JSONB NOT NULL DEFAULT '{}',
user_private_state JSONB NOT NULL DEFAULT '{}',
acquired_at TIMESTAMPTZ NOT NULL DEFAULT now(),
updated_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';
COMMENT ON COLUMN auth.inventory.server_state IS 'Public state visible to everyone, persists through transfer';
COMMENT ON COLUMN auth.inventory.realm_state IS 'Public state visible to realm members, persists through transfer';
COMMENT ON COLUMN auth.inventory.user_state IS 'Public state visible to item inspectors, persists through transfer';
COMMENT ON COLUMN auth.inventory.server_private_state IS 'Private owner state, cleared on transfer';
COMMENT ON COLUMN auth.inventory.realm_private_state IS 'Private owner state, cleared on transfer';
COMMENT ON COLUMN auth.inventory.user_private_state IS 'Private owner state, cleared on transfer';
COMMENT ON COLUMN auth.inventory.updated_at IS 'Timestamp of last modification (auto-updated by trigger)';
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;
CREATE INDEX idx_inventory_server_state ON auth.inventory USING GIN (server_state)
WHERE server_state != '{}';
CREATE INDEX idx_inventory_realm_state ON auth.inventory USING GIN (realm_state)
WHERE realm_state != '{}';
-- =============================================================================
-- 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;
Reference in a new issue View git blame Copy permalink